127x without changing a line of code

What I found

In August an occupancy pipeline broke and I spent some days inside it. Most of that is another story. One side finding was the database: a MySQL server with about 2 GB of data that is read all day, and innodb_buffer_pool_size was 128 MB, the value that comes with the install. This is very common. The server works, nobody complains, and the defaults stay there for years.

With the pool that small most reads went to disk, around 12 million disk reads per day. A query the team used every day took about seven minutes and was hitting the timeout.

Check yours

Two queries tell you if you are in the same place. The first one is the size of the data per schema, the second is how often InnoDB had to go to disk:

SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024) AS mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY mb DESC;

SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS pool_mb,
       1 - r.v / q.v AS hit_ratio
FROM (SELECT VARIABLE_VALUE AS v FROM performance_schema.global_status
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') r,
     (SELECT VARIABLE_VALUE AS v FROM performance_schema.global_status
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') q;

If the pool is much smaller than what you read every day, start there. The counters are cumulative since the last restart, so look at them twice with some minutes between if you want the current rate.

Three waves

I changed six settings, and for me the order matters more than the values because each group has a different owner of the risk.

-- wave 1: online, nothing to lose
SET PERSIST innodb_buffer_pool_size  = 4294967296;  -- 128 MB -> 4 GB, two days later 8 GB
SET PERSIST innodb_redo_log_capacity = 1073741824;  -- 100 MB -> 1 GB (8.0.30+)
SET PERSIST innodb_io_capacity       = 1000;        -- default 200
SET PERSIST innodb_io_capacity_max   = 4000;        -- default 2000

-- wave 2: trades durability for speed, needs a business ok
SET PERSIST innodb_flush_log_at_trx_commit = 2;     -- default 1
SET PERSIST sync_binlog                    = 100;   -- default 1

-- wave 3: only applies on restart
SET PERSIST_ONLY innodb_flush_method = 'O_DIRECT';

Wave one has no downside on a server with RAM to spare. The buffer pool resizes online since 5.7, it takes some time in the background, and you can follow it in Innodb_buffer_pool_resize_status.

Wave two is a different conversation. With innodb_flush_log_at_trx_commit = 2 the commit writes the log to the operating system and the flush to disk happens about once per second. If only mysqld crashes you lose nothing, but if the machine loses power you can lose up to around one second of commits. sync_binlog = 100 is the same idea for the binary log, up to 100 transactions. Here the tables are rebuilt by the pipeline on every cycle, so a lost second costs one cycle. Even so, this kind of decision is not for who operates the database to take alone, I asked the people who answer for the business and they said yes. The server was doing about 267 small transactions per second in autocommit, and fsyncs went from ~500/s to ~4/s.

Last comes O_DIRECT, so InnoDB stops caching the same pages twice (in the pool and in the OS page cache). It needs a restart, so I left it armed with PERSIST_ONLY and waited for a window.

The restart

Sunday, past midnight, with the pipeline cron paused. MySQL came back in 11 seconds and came back read-only. I expected that one, it was in the checklist: some persisted settings from an old replication setup (super_read_only and group replication on boot) were still in the config. Before any restart it's worth to look at what is persisted:

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.persisted_variables;

I had the two commands ready, SET GLOBAL super_read_only = OFF and SET GLOBAL read_only = OFF, and writes were paused for about 50 seconds. I did not remove those persisted settings because they belong to the infra team, so I wrote down that any future restart does the same thing and how to get out of it.

The honest number

Seven minutes to 3.3 seconds is 127x. But in the same days I removed around 1.66 million garbage rows the broken pipeline had left, and that table became 40% smaller. I can't split exactly how much came from the tuning and how much from the cleanup. The other numbers are only from the tuning: hit ratio 99.99%, the disk went from millions of reads per day to almost only writes, and the server took around 3,900 inserts per second where before the ceiling was somewhere between 500 and 1,000.

These settings didn't come from the documentation. They came from the years I took care of around 60 servers of our own, and I dictated them to the agent, that ran and checked each one. In that time I used to give the pool something like 75% of the RAM. Here I stopped at 8 GB because it was already enough for this data.

Filed under the problem it solves

• mysql
• innodb
• buffer pool
• performance tuning
• durability
• operations