MySQL performance tuning on Ubuntu: innodb_buffer_pool_size and more

Where the MySQL config is and how to apply changes

On Ubuntu the server settings live in /etc/mysql/mysql.conf.d/mysqld.cnf, in the [mysqld] section. Some lines there are commented out examples. Edit the file, check it, restart MySQL and look at the value the server really uses:

The config check prints nothing if the options are fine and names the wrong line otherwise. If MySQL doesn’t start after a restart, the reason is in its log:

Many settings can also be changed without a restart with SET GLOBAL (until the next restart) or SET PERSIST. SET PERSIST writes the value to /var/lib/mysql/mysqld-auto.cnf, which wins over mysqld.cnf, so remember where you set it.

How to choose innodb_buffer_pool_size

The more of your data and indexes fit in the buffer pool, the less MySQL reads from disk. The MySQL manual says up to 80% of memory is often given to it on a dedicated database server. With innodb_dedicated_server MySQL picks 75% itself on servers with more than 4 GB. Starting points:

• A server only for MySQL: 70–75% of RAM, for example 12G on 16 GB.
• MySQL next to the site (Nginx, PHP-FPM, Node.js): what is left after the app, often 40–50% of RAM, for example 2G on a 4 GB server.
• Never more than the data needs: if the whole database is 1 GB, a 1–2 GB buffer pool is enough.

To see how big your data is, run:

MySQL rounds the value to a multiple of 128 MB times the number of buffer pool instances, so the result can be a little larger than you wrote. On a running server it can also be changed without a restart: sudo mysql -e "SET GLOBAL innodb_buffer_pool_size = 2147483648;" After a day of normal work, check how often MySQL had to go to disk:

Innodb_buffer_pool_reads counts reads from disk, Innodb_buffer_pool_read_requests all reads. If the first is more than about 1% of the second, the buffer pool is probably too small for your data.

Redo log size: innodb_redo_log_capacity

The redo log records changes before InnoDB writes them to the tables. Since MySQL 8.0.30 its size is innodb_redo_log_capacity, 100 MB by default, which replaced innodb_log_file_size and innodb_log_files_in_group from older guides. With many writes, a small redo log makes InnoDB flush changes to disk too often. A larger one evens out writes, but recovery after a crash takes longer. For a busy server, start with 1–2 GB:

It can be changed without a restart too: sudo mysql -e "SET GLOBAL innodb_redo_log_capacity = 2147483648;" The current size: sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_capacity_resized';" MySQL needs some time to grow or shrink the files.

max_connections and the “Too many connections” error

ERROR 1040 (HY000): Too many connections means all connections are taken. The limit is max_connections, 151 by default. MySQL keeps one more for an account with CONNECTION_ADMIN, so mysql run with sudo usually still gets in. Look at the peak and the current number first:

Often the reason isn’t the limit but the app: connections that are never closed, or many PHP-FPM processes that each open one. The limit you need is about the sum of all workers that talk to the database: pm.max_children of every PHP-FPM pool, Node.js pool sizes, cron jobs, plus a margin. Each connection takes memory for its own buffers, so don’t set thousands “just in case”.

How to turn on the slow query log

Most speed is won not by settings but by fixing slow queries, usually with the right index. The slow query log writes down every query that runs longer than long_query_time seconds (10 by default). Ubuntu’s mysqld.cnf already has these lines commented out: remove the # in front of them and set the time you need:

mysqldumpslow groups the same queries and sorts them by total time, so the first ones cost you most. Run each of them with EXPLAIN in front to see whether it uses an index. To turn the log on without a restart, until the next one: sudo mysql -e "SET GLOBAL slow_query_log = 'ON';"

Temporary tables, and the query cache that is gone

For GROUP BY, DISTINCT and some sorts MySQL builds an internal temporary table. In MySQL 8.0 it lives in memory up to tmp_table_size (16 MB by default), and bigger ones go to disk. Compare the counters:

If Created_tmp_disk_tables is a large share of Created_tmp_tables, raise tmp_table_size and max_heap_table_size together, for example to 64M. The limit applies to each table, and several queries can build one at the same time, so raise it in steps.

The query cache was removed in MySQL 8.0. Old guides suggest query_cache_size and query_cache_type. In 8.0 these lines are unknown, MySQL won’t start, and error.log says unknown variable. Remove them. Cache results in the app instead, for example in Redis: how to install Redis on Ubuntu.

Hints from mysqltuner

mysqltuner is a script from Ubuntu’s repository that reads the server’s counters and suggests changes. Run it after the server has worked under normal load for at least a day, otherwise the counters say little:

Read its suggestions as hints, not orders. Check each one against this guide and the MySQL manual, change one setting at a time and compare SHOW GLOBAL STATUS before and after.