• innodb_buffer_pool_size — 65% of RAM, critical for performance.
• innodb_log_file_size — 512M balances write speed and recovery time.
• innodb_flush_log_at_trx_commit=2 — trade-off between performance and durability.
• innodb_flush_method=O_DIRECT — bypasses OS cache, reducing I/O load.

We design indexes using EXPLAIN ANALYZE. Example covering index for filtering by category and price:

CREATE INDEX idx_products_listing ON products(category_id, is_active, price, id, name) WHERE deleted_at IS NULL; 

The Importance of InnoDB Buffer Pool Tuning

The buffer pool is the most common bottleneck. If it's smaller than 60% of available RAM, the disk subsystem works overtime. With proper sizing (5 GB out of 8 GB) and 4 instances we achieve up to 1.4 times read performance improvement. It's also important to use O_DIRECT — it eliminates double buffering by the operating system.

Setting Up Replication and ProxySQL

For growing load projects, primary-replica replication with ProxySQL is mandatory. We configure GTID — it simplifies failover and master promotion. ProxySQL configuration for routing SELECT to replicas and INSERT/UPDATE to primary:

mysql_servers = ( { address="10.0.0.1", port=3306, hostgroup=0, max_connections=100 }, { address="10.0.0.2", port=3306, hostgroup=1, max_connections=100 }, { address="10.0.0.3", port=3306, hostgroup=1, max_connections=100 } ) mysql_query_rules = ( { rule_id=1, active=1, match_pattern="^SELECT", destination_hostgroup=1, apply=1 }, { rule_id=2, active=1, match_digest="^SELECT.*FOR UPDATE", destination_hostgroup=0, apply=1 } ) 

Configuring replication on the slave:

CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='replicator', MASTER_PASSWORD='repl_password', MASTER_AUTO_POSITION=1; START SLAVE; 

Backup and Monitoring

We use Percona XtraBackup for physical backups without table locking:

xtrabackup --backup --target-dir=/backup/full xtrabackup --prepare --target-dir=/backup/full 

Automate via cron: daily full backup at 2:00 AM, incremental every 6 hours. Keep for 7 days. This covers 99% of recovery scenarios.

After tuning, it's essential to monitor key metrics: InnoDB status, slow query log, number of open connections. We set up alerts when thresholds are exceeded. This prevents performance degradation before users notice problems.

What's Included in the Database Tuning Service

• Audit of the current database state and bottleneck identification
• Optimal DBMS and version selection
• Configuration tuning for your hardware
• Index and query optimization
• Replication and ProxySQL setup (if needed)
• Backup implementation using Percona XtraBackup
• Monitoring and alerting setup
• Configuration documentation and team training
• One month of post-deployment support
• Guaranteed performance improvement or money back

Work Process

1. Analysis — collect metrics, identify bottlenecks.
2. Design — choose DBMS, version, replication scheme.
3. Implementation — configuration, indexes, backup setup.
4. Testing — load testing, replication verification.
5. Deploy — apply to production with minimal downtime.

Timelines and Costs

• Installation, hardening, load tuning: 1 day.
• Replication with ProxySQL: 1–2 days.
• Data migration from another DBMS: 2–5 days.

Typical tuning project costs from $1,500 to $5,000 depending on complexity. Cost is finalized after an audit. For example, one client reduced monthly cloud infrastructure expenses by $2,000 after replication tuning and query optimization. Another client saved over $1,500 per month by fixing suboptimal indexes. Proper database tuning can cut your hosting costs by 30–40%, potentially saving thousands of dollars annually. Request a consultation to learn how database optimization can save your budget.

Common Mistakes When Configuring Yourself

Comparison of MySQL 8.0 and MariaDB 11.x

Source: our own load testing on 50 projects

We configure your database end-to-end — from server to monitoring. Contact us for a consultation on database performance optimization.