MySQL Config Calculator

Enter your server's RAM, cores, connection count and workload and get a my.cnf tuned for it. The calculator sizes innodb_buffer_pool_size and its instance count, redo log capacity, the per-session sort and join buffers, I/O capacity for your storage type, and the table and thread caches — then adds up the worst case so you can see whether the configuration actually fits in memory. Output as a my.cnf block or as SET GLOBAL statements, with static variables called out separately. Everything is computed in your browser; nothing is uploaded.

RAM (GB)vCPU coresmax_connectionsTablesMySQL versionWorkloadStorageHostOutput

Generated configuration

# my.cnf — generated by Chat2DB for MySQL 8.4
# 16 GB RAM · 8 vCPU · 200 connections · web workload · NVMe storage · dedicated host
# Review before applying to production. Most InnoDB sizes below need a restart.

[mysqld]

# --- InnoDB memory ---
innodb_buffer_pool_size = 11520M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 2M
join_buffer_size = 1M
read_rnd_buffer_size = 512K
read_buffer_size = 256K
max_allowed_packet = 64M

# --- Redo log and durability ---
innodb_redo_log_capacity = 2880M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_doublewrite = ON
innodb_file_per_table = ON

# --- Storage I/O ---
innodb_flush_method = O_DIRECT
innodb_io_capacity = 4000
innodb_io_capacity_max = 12000
innodb_flush_neighbors = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_page_cleaners = 8
innodb_adaptive_flushing = ON

# --- Connections and caches ---
max_connections = 200
thread_cache_size = 16
table_open_cache = 2000
table_definition_cache = 1400
open_files_limit = 65535
innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 20

# --- Observability ---
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = OFF
innodb_stats_persistent = ON
innodb_stats_persistent_sample_pages = 20
performance_schema = ON
binlog_expire_logs_seconds = 604800

# --- Character set ---
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci

Memory budget

Buffer pool          11.25 GB
Per-connection       6.8 MB × 200 conns = 1.32 GB
Server overhead      ~0.50 GB (dictionary, performance_schema, thread stacks)
Estimated peak       13.07 GB of 16.0 GB RAM (82%)

What each setting does

InnoDB memory

innodb_buffer_pool_size = 11520M — 70% of RAM. The single most important MySQL setting: it caches both data and index pages, so a working set that fits here is served without touching disk.
innodb_buffer_pool_instances = 8 — Splits the pool into 8 independently latched regions (~1.4 GB each), reducing mutex contention on multi-core hosts.
innodb_buffer_pool_chunk_size = 128M — Granularity of online buffer pool resizing. buffer_pool_size must be a multiple of chunk_size × instances or MySQL rounds it up silently.
tmp_table_size = 256M — Maximum size of an internal in-memory temporary table before it spills to disk. Allocated per statement, not globally.
max_heap_table_size = 256M — Must match tmp_table_size — MySQL uses the smaller of the two when deciding whether an internal temp table stays in memory.
sort_buffer_size = 2M — Per-session buffer for ORDER BY and GROUP BY without an index. Large values hurt: the buffer is allocated in full for every sort.
join_buffer_size = 1M — Per-join-of-a-kind buffer for joins that cannot use an index. Fix the missing index rather than raising this.
read_rnd_buffer_size = 512K — Buffer used when reading rows in sorted order after a sort, per session.
read_buffer_size = 256K — Per-session buffer for sequential table scans on MyISAM and for some internal operations.
max_allowed_packet = 64M — Largest single packet or row the server accepts. Raise it if you store large BLOBs or run big multi-row INSERTs.

Redo log and durability

innodb_redo_log_capacity = 2880M — Total redo log size (MySQL 8.0.30+). Bigger means fewer checkpoints and smoother write throughput, at the cost of longer crash recovery.
innodb_log_buffer_size = 32M — In-memory staging area for redo records. Raise it if you run large transactions so they do not force a log flush mid-transaction.
innodb_flush_log_at_trx_commit = 1 — 1 = fsync on every commit. This is the only fully ACID-durable setting and the correct default for transactional workloads.
sync_binlog = 1 — 1 keeps the binary log in sync with InnoDB on every commit, which replication and point-in-time recovery depend on.
innodb_doublewrite = ON — Protects against torn pages. Leave it on unless your storage guarantees atomic page writes.
innodb_file_per_table = ON — One .ibd file per table, so DROP TABLE and OPTIMIZE TABLE actually return space to the filesystem.

Storage I/O

innodb_flush_method = O_DIRECT — Bypasses the OS page cache for data files so pages are not cached twice. Use fsync on Windows or when running on ZFS.
innodb_io_capacity = 4000 — Baseline IOPS budget for background flushing, sized for NVMe.
innodb_io_capacity_max = 12000 — Ceiling InnoDB may use when it is falling behind on flushing. Keep it well under what the device can actually sustain.
innodb_flush_neighbors = 0 — 0 — on SSD/NVMe there is no seek penalty, so flushing neighbours just writes pages that were not dirty enough to need it.
innodb_read_io_threads = 4 — Background threads for read-ahead and prefetch requests.
innodb_write_io_threads = 4 — Background threads servicing write requests from the buffer pool.
innodb_page_cleaners = 8 — Flush threads. Matching the buffer pool instance count lets each instance be cleaned in parallel.
innodb_adaptive_flushing = ON — Adjusts the flush rate from the redo generation rate instead of flushing in bursts at checkpoint time.

Connections and caches

max_connections = 200 — Hard cap on concurrent client connections. Each one costs memory and a thread — pool in the application rather than raising this.
thread_cache_size = 16 — Threads kept alive for reuse after a client disconnects, avoiding thread-creation cost on churny connection patterns.
table_open_cache = 2000 — Open table handles cached across sessions, sized for roughly 500 tables.
table_definition_cache = 1400 — Cached .frm/data-dictionary definitions. Too low and MySQL re-reads table metadata constantly.
open_files_limit = 65535 — File descriptor ceiling. The OS limit (LimitNOFILE in systemd) must be at least this high or MySQL silently caps itself.
innodb_thread_concurrency = 0 — 0 = let InnoDB manage concurrency itself. Only set a limit if you have measured thrashing above a specific thread count.
innodb_lock_wait_timeout = 20 — Seconds a transaction waits for a row lock before rolling back its statement. Short timeouts surface lock contention as errors instead of stalls.

Observability

slow_query_log = ON — You cannot tune what you cannot see. The slow log is the cheapest source of truth about which statements actually hurt.
slow_query_log_file = /var/log/mysql/slow.log — Point this at a path the mysql user can write and that is rotated.
long_query_time = 1 — Threshold in seconds. Start here, then lower it once the obvious offenders are fixed.
log_queries_not_using_indexes = OFF — Leave off in production: small lookup tables legitimately full-scan and will flood the log.
innodb_stats_persistent = ON — Keeps index statistics across restarts so the optimizer does not re-plan differently after every bounce.
innodb_stats_persistent_sample_pages = 20 — Pages sampled when recomputing statistics. Higher is more accurate and slower to collect.
performance_schema = ON — Costs a few percent of throughput and is the only way to get statement and wait instrumentation. Keep it on.
binlog_expire_logs_seconds = 604800 — Seven days of binary logs — enough for point-in-time recovery without filling the disk.

Character set

character_set_server = utf8mb4 — Real 4-byte UTF-8. The legacy utf8 alias stores only 3 bytes and cannot hold emoji or many CJK characters.
collation_server = utf8mb4_0900_ai_ci — MySQL 8's default Unicode 9.0 collation. Use utf8mb4_bin only if you need byte-exact comparison.

Do more than mysql config calculator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the server's RAM, vCPU count and how many tables the instance holds, then pick the workload that matches your database.
Set max_connections to what your application pool actually opens, and choose your storage type so the I/O capacity values match the device.
Copy the result into /etc/mysql/my.cnf (or a file under conf.d), restart MySQL, and check the memory budget panel before rolling it out.

Frequently asked questions

How big should innodb_buffer_pool_size be?

On a dedicated database server, 70% of RAM is the usual starting point, which is what this calculator uses above 8 GB. The buffer pool caches both data and index pages, so the goal is for your working set — not your whole database — to fit inside it. On a host shared with an application server, drop to roughly 45% and leave the rest for everything else. Whatever you choose, make it a multiple of innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances, or InnoDB will round the value up without telling you.

Why are sort_buffer_size and join_buffer_size so small?

They are per-session allocations, not a shared pool. MySQL allocates the full sort_buffer_size for every sort that cannot use an index, and join_buffer_size for every join that has no usable index — so with 500 connections an 8 MB sort buffer is a potential 4 GB of memory. Large values also make small sorts slower, because MySQL has to initialise the whole buffer. If a query needs a big sort buffer to perform, the real fix is almost always an index.

Do I need to restart MySQL after changing my.cnf?

Some settings only. innodb_buffer_pool_instances, innodb_log_file_size, innodb_flush_method, the I/O thread counts, performance_schema and the character set defaults are static and need a restart. innodb_buffer_pool_size, innodb_io_capacity, max_connections, the session buffers and the slow log settings are dynamic and can be changed with SET GLOBAL — switch the output format above to generate those statements. Note that SET GLOBAL does not survive a restart, so write the values into my.cnf as well. To inspect the running values and compare them across servers, connect with Chat2DB at https://chat2db.ai/download or https://app.chat2db.ai.