How to Calculate the Right innodb_buffer_pool_size for PrestaShop on MySQL 8.0

Make PrestaShop faster by configuring MySQL’s buffer pool for your specific store size and hosting capacity.

1. General Guideline

Dedicated MySQL server: Set the buffer pool to 70–80% of system RAM.
Shared server (e.g. with Plesk): Set it to 50–60% to leave room for PHP-FPM, Apache/Nginx, etc.

2. Measure InnoDB Size

Estimate the size of InnoDB tables and indexes to decide buffer pool size.

SQL Command:

SELECT 
  ROUND(SUM(data_length + index_length) / 1024 / 1024) AS total_mb 
FROM 
  information_schema.tables 
WHERE 
  engine = 'InnoDB';

Where to run it:

  • ✅ phpMyAdmin: use the SQL tab
  • ✅ PrestaShop SQL Manager: paste into a new SQL query
  • ✅ SSH: login to MySQL CLI and run command

Add ~20% to the result for safety buffer.

3. Sample Calculation

If the previous command returns 4500 MB (≈ 4.5 GB):

  • Recommended buffer pool size: 6G
  • Edit your my.cnf or my.ini config file:
innodb_buffer_pool_size = 6G

Where to set it:

  • ✅ SSH or Hosting Panel: edit config file via terminal or file manager
  • 🔄 Restart MySQL after making changes

4. Buffer Pool Instances

Enable multiple buffer pool instances to improve concurrency when total size exceeds 8GB:

innodb_buffer_pool_instances = 2  # Use 4 for 16GB+

Where to set it:

  • ✅ In your MySQL config file under [mysqld]
  • 🔄 Restart MySQL after applying changes

5. Monitor Efficiency

Check how well your buffer pool is performing.

Command 1:

SHOW ENGINE INNODB STATUS;

Command 2:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';

Where to run them:

  • ✅ SSH: via MySQL CLI
  • ✅ phpMyAdmin: SQL tab
  • 🚫 Not available via PrestaShop SQL Manager

Hit ratio formula:

1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

A value above 99% is ideal.

6. Final Tips

  • Use MySQLTuner or Percona Toolkit (via SSH) for ongoing tuning
  • Test all changes on a staging server before production
  • Restart MySQL after changing my.cnf values

Need help with performance tuning? Contact PrestaHeroes — we help ecommerce stores grow through expert optimization.