Tune MySQL for Ecommerce: The 2025 Performance Playbook

By PrestaHeroes

Executive Summary: Most ecommerce sites run MySQL/MariaDB with near-default settings built for small blogs, not busy stores. Those defaults throttle performance—raising CPU, slowing queries, and making category/search pages feel heavy. This guide shows how to tune MySQL safely, verify results with phpMyAdmin, and adopt a “tune first, analyze later” mindset.

PrestaHeroes Tip: If your hosting plan doesn’t include NVMe-SSD storage, consider upgrading on your next renewal. It reduces database I/O latency during traffic spikes and indexing.

⚠️ Note — Always Back Up First

Before editing any MySQL/MariaDB configuration, make a backup of your current settings so you can roll back:

sudo cp /etc/my.cnf /etc/my.cnf.backup-$(date +%F)

After saving changes, restart MySQL/MariaDB so they take effect.

How to Restart (Plesk vs cPanel)

Plesk: Tools & Settings → Services Management → restart MariaDB/MySQL Server. (Plesk’s SSH Terminal extension also lets you run commands in-browser.)

cPanel/WHM: WHM → Restart Services → SQL Server (MySQL). Or via SSH:

sudo systemctl restart mysql

or

sudo systemctl restart mariadb

1) Core MySQL Configuration Tuning

Safe, measurable settings that work well on MySQL 8 / MariaDB 10.6+.

Memory & Buffers

innodb_buffer_pool_size = 8G innodb_buffer_pool_instances = 2

PrestaHeroes recommends: 70–80% of RAM for DB-only servers; 50–60% if shared with PHP/HTTP. Right-sizing cuts disk reads and slashes response time.

Redo Logs (Write Stability)

innodb_log_file_size = 1024M innodb_log_files_in_group = 2

~2 GB total redo capacity reduces checkpoint thrash and smooths write spikes.

NVMe SSD / I/O Optimization

innodb_flush_method = O_DIRECT innodb_flush_neighbors = 0 innodb_io_capacity = 1000 innodb_io_capacity_max = 2000

Temporary Tables (Great for Faceted/Filtered Lists)

tmp_table_size = 256M max_heap_table_size = 256M

Connection & Cache Efficiency

max_connections = 150 table_open_cache = 6000 table_definition_cache = 3000 open_files_limit = 20000

Maintenance & Cleanliness

performance_schema = OFF innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci

Example my.cnf (10 GB RAM MariaDB 10.6 VPS)

[mysqld] innodb_buffer_pool_size = 8G innodb_buffer_pool_instances = 2 innodb_log_file_size = 1024M innodb_log_files_in_group = 2 innodb_flush_method = O_DIRECT innodb_flush_neighbors = 0 innodb_io_capacity = 1000 innodb_io_capacity_max = 2000 tmp_table_size = 256M max_heap_table_size = 256M max_connections = 150 table_open_cache = 6000 table_definition_cache = 3000 open_files_limit = 20000 performance_schema = OFF innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci

Remember to restart the database service so these settings apply.

2) Verify Results — Use the Slow Query Log After Tuning

The slow log confirms improvements; enabling it first just records pain from a misconfigured server.

phpMyAdmin Method (Recommended)

  1. Open phpMyAdmin, select your store database.
  2. SQL tab → run:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; SET GLOBAL log_output = 'TABLE';
  1. Let normal traffic run for a few hours.
  2. Open database mysql → table slow_log → sort by query_time.

Quick summary:

SELECT sql_text, query_time FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

Root Method (Only if Needed)

Back up first:

sudo cp /etc/my.cnf /etc/my.cnf.backup-$(date +%F)
sudo nano /etc/my.cnf

Add under [mysqld]:

slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2 log_output = FILE

Restart and verify:

sudo systemctl restart mariadb

or

sudo systemctl restart mysql
SHOW VARIABLES LIKE 'slow_query%';

3) Ecommerce-Specific Tips

  • Layered/Faceted navigation: limit high-cardinality filters, avoid dynamic counts where possible, and rebuild indexes after changes.
  • Robots: block layered URL parameters (e.g., ?q=) to reduce crawler load.
  • Optimize Tables: in phpMyAdmin, select all tables → “Optimize table”.
  • Live monitoring: run SHOW FULL PROCESSLIST; to spot repetitive or stuck queries.

4) Confirm Performance Gains

In phpMyAdmin → SQL:

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

If disk reads are < 1% of requests, your buffer pool is sized well—hallmark of a tuned DB.

Conclusion — Tune First, Analyze Later

Logs diagnose, but configuration heals. Winning ecommerce sites start with a tuned database foundation. Tune first, verify later—and when upgrading hosting, choose a provider that supports Plesk (for control) and NVMe (for speed).

About PrestaHeroes

PrestaHeroes is a specialized ecommerce performance agency focused on hosting optimization, module development, and speed consulting. Need expert help tuning your database or diagnosing bottlenecks? Contact PrestaHeroes for a professional MySQL optimization audit.