If your MySQL server feels slow, chances are your hot data or indexes don’t fit in memory, or your logging is forcing too many disk syncs. This beginner-friendly guide shows how to size InnoDB’s cache based on what you actually read, which other caches matter, and how binary logging affects performance.

1) Right-size the InnoDB buffer pool using index size and hot data
Goal: fit your “working set” in memory. That working set is:

• Index pages of the most-read tables
• Data pages for the most-read rows
• Plus some headroom for change buffering, temp pages, and concurrency

Step A: Measure index sizes

• Per table:
  SELECT table_schema, table_name,
ROUND(index_length/1024/1024, 1) AS index_mb,
ROUND(data_length/1024/1024, 1) AS data_mb
FROM information_schema.tables
WHERE engine = 'InnoDB'
AND table_schema NOT IN ('mysql','sys','performance_schema','information_schema')
ORDER BY index_length DESC;
• Per schema:
  SELECT table_schema,
ROUND(SUM(index_length)/1024/1024, 1) AS total_index_mb,
ROUND(SUM(data_length)/1024/1024, 1) AS total_data_mb
FROM information_schema.tables
WHERE engine = 'InnoDB'
GROUP BY table_schema
ORDER BY total_index_mb DESC;

Step B: Find your “most-read” tables
If you have the sys schema (MySQL 5.7+/8.0):

• Top tables by reads:
  SELECT table_schema, table_name, rows_fetched
FROM sys.schema_table_statistics
ORDER BY rows_fetched DESC
LIMIT 20;

Alternative with performance_schema:

• Top tables by read time:
  SELECT OBJECT_SCHEMA AS table_schema, OBJECT_NAME AS table_name, SUM_TIMER_READ
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
ORDER BY SUM_TIMER_READ DESC
LIMIT 20;

Step C: Estimate the “hot set”
Join the hot tables from Step B to information_schema.tables to sum index and data sizes for just those hot tables:

WITH hot AS (
SELECT s.table_schema, s.table_name
FROM sys.schema_table_statistics AS s
ORDER BY s.rows_fetched DESC
LIMIT 20
)
SELECT
ROUND(SUM(t.index_length)/1024/1024/1024, 1) AS hot_index_gb,
ROUND(SUM(t.data_length)/1024/1024/1024, 1) AS hot_data_gb
FROM information_schema.tables t
JOIN hot h
ON t.table_schema = h.table_schema AND t.table_name = h.table_name
WHERE t.engine = 'InnoDB';

This gives a practical target for what should be memory-resident most of the time.

Step D: Choose a buffer pool size

• Dedicated DB server: make innodb_buffer_pool_size roughly 60–75% of RAM.
• If RAM is limited, aim to fit at least the hot indexes plus a healthy portion of hot data. A simple rule: target 1.2x to 1.5x the hot_index + hot_data estimate, capped by the 60–75% guideline.
• Example: hot_index = 20 GB and hot_data = 15 GB → target 42–52 GB; with a 64 GB server, set innodb_buffer_pool_size to ~48 GB.

Also configure:

• innodb_buffer_pool_instances: 4–8 instances for pools > 8–16 GB (MySQL 8.0 auto-scales, but setting 4–8 avoids contention on large pools).
• Enable warmup:
• innodb_buffer_pool_dump_at_shutdown = ON
• innodb_buffer_pool_load_at_startup = ON

Step E: Verify it worked

• Buffer pool hit ratio (aim for > 99% on read-heavy workloads):
  SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
  Hit ratio ≈ 1 – (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
• Check disk reads dropping after increasing the pool.
• Use sys.schema_table_statistics_with_buffer to see buffer hit rates by table.

Notes and caveats:

• information_schema sizes are estimates; compressed and dynamic row formats vary.
• Growth and workload change over time—revisit quarterly.

2) Other caching settings that help
Beyond the buffer pool, these settings reduce CPU and I/O churn:

• InnoDB redo log buffer
• innodb_log_buffer_size: if you run large or bursty transactions, increase to 64–256 MB to reduce log writes mid-transaction.
• Adaptive Hash Index (AHI)
• innodb_adaptive_hash_index = ON by default. It can speed up repeated equality lookups on the same index keys. In highly concurrent or non-uniform workloads it can add contention; test ON vs OFF with real workload.
• Change buffering (secondary index change cache)
• innodb_change_buffering = all (default) helps insert/update workloads on secondary indexes by deferring disk IO. For read-mostly workloads, leave it ON; for write-heavy with large memory you can test off, but most benefit from leaving it on.
• Table and definition cache
• table_open_cache and table_definition_cache: prevent re-opening tables. If Opened_tables or Opened_table_definitions grows quickly under steady load, raise these. Typical ranges: thousands to tens of thousands on busy servers.
• table_open_cache_instances: multiple instances reduce lock contention on the cache.
• Thread cache
• thread_cache_size: if Threads_created increases rapidly, raise this so connections reuse threads.
• OS caching and double buffering
• innodb_flush_method = O_DIRECT (Linux) to avoid double-buffering in the OS page cache, letting the buffer pool be the primary cache.
• Put data, redo logs, and binlogs on fast, separate storage if possible.
• Query cache (only MySQL 5.7 and earlier)
• Disable it (query_cache_type=0, query_cache_size=0). It harms concurrency; MySQL 8.0 removed it.
• Application-level caching
• Use connection pooling and prepared statements in your driver.
• Cache hot result sets in your app or a cache layer (Redis/Memcached) to avoid unnecessary reads.

3) Binary logging and performance: what to know
What binary logging does:

• Records every committed transaction in the binary log for replication and point-in-time recovery (PITR).
• Adds CPU work to format events and extra I/O to persist them. The fsync frequency is controlled by sync_binlog.

Performance impact:

• Additional write and fsync on each commit (especially with sync_binlog = 1).
• Extra memory for per-session binlog caches; potential temp files if caches overflow.
• Overhead varies widely by workload; OLTP with many small commits feels it most.

If you keep binlog enabled (recommended for most production):

• Keep durability but reduce overhead
• sync_binlog = 1 for maximum safety; consider values > 1 (e.g., 100) to batch fsyncs if you can risk losing up to N transactions on OS crash.
• Pair with innodb_flush_log_at_trx_commit:
  • 1: safest (fsync redo on every commit).
  • 2: fsync redo once per second; may lose up to 1 second of data on crash.
• binlog_row_image = MINIMAL to log only changed columns, shrinking binlog size.
• binlog_transaction_compression = ON (MySQL 8.0) to compress row events.
• binlog_cache_size and binlog_stmt_cache_size large enough to keep most transactions in memory (e.g., 1–16 MB) to avoid temp files.
• Place binlogs on fast storage separate from data/redo to isolate I/O.
• Consider group-commit tuning:
  • binlog_group_commit_sync_delay (microseconds) and binlog_group_commit_sync_no_delay_count can batch commits, trading tiny latency for throughput.

Advantages of disabling binary logging:

• Lower transaction latency and higher throughput on write-heavy OLTP by removing binlog writes and fsyncs.
• Less disk usage and IO on the server.
• Less CPU spent formatting and compressing binlog events.
• Smaller memory footprint per connection (binlog caches).

Disadvantages of disabling binary logging:

• You lose replication. You cannot create replicas or use Group Replication/InnoDB Cluster/async replicas later without reconfiguring and taking a fresh snapshot.
• You lose point-in-time recovery. Backups can only restore to the snapshot time; no replay of changes since the last backup.
• You lose certain auditing/troubleshooting options that rely on reading binlogs.
• GTIDs cannot be used; some tooling and operational patterns depend on them.

When is disabling reasonable?

• Single-instance dev/test or ephemeral environments.
• Simple read-mostly systems where you accept snapshot-only backups and can rebuild from source of truth.
• Even then, consider keeping binlog ON and tuning sync_binlog for a safer middle ground.

Practical checklist

• Measure hot tables and estimate hot indexes + hot data.
• Size innodb_buffer_pool_size to fit the hot set, within 60–75% of RAM on a dedicated server.
• Enable buffer pool dump/load to warm caches on restart.
• Tune table_open_cache, thread_cache_size, and innodb_log_buffer_size based on observed metrics.
• Use O_DIRECT to avoid double buffering.
• Keep binlog ON for most production setups; tune sync_binlog, row image, and compression; put binlogs on fast storage.
• Consider disabling binlog only when you explicitly do not need replication or PITR.

Always make one change at a time, monitor with real workload, and compare throughput, latency, and disk IO before and after.