Identifying cause of high I/O

I'm trying to diagnose the bottleneck for high-write I/O activity on a percona master (with replication to a slave). The pattern of behaviour is roughly cyclic and regular: 60 mins of 75-90% write-based IO utilisation followed by 10 mins of ~25%. Occasionally this spikes to such a point that the fronting webservice is unresponsive for a couple of minutes.

Doing a SHOW PROCESSLIST shows the replication process and then a number of idle threads (though occasionally there are 1-2 COMMITs which complete by the time I reissue the process list command) but nothing indicating a specific active query.

Innodb buffer status variables:

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_%';
+-----------------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------------+-----------+
| Innodb_buffer_pool_pages_data | 1678714 |
| Innodb_buffer_pool_pages_dirty | 718 |
| Innodb_buffer_pool_pages_flushed | 195847729 |
| Innodb_buffer_pool_pages_LRU_flushed | 0 |
| Innodb_buffer_pool_pages_free | 5299854 |
| Innodb_buffer_pool_pages_made_not_young | 0 |
| Innodb_buffer_pool_pages_made_young | 59369 |
| Innodb_buffer_pool_pages_misc | 99319 |
| Innodb_buffer_pool_pages_old | 619661 |
| Innodb_buffer_pool_pages_total | 7077887 |
+-----------------------------------------+-----------+
10 rows in set (0.00 sec)


SHOW ENGINE INNODB STATUS
givesBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000.

The mysqld section from my.cnf is

```
[mysqld]

user = mysql
default-storage-engine = InnoDB
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid

key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

max-allowed-packet = 16M
max-co

This kind of sounds like poor flushing behavior, ideally you'd be seeing consistent IO not stretches of 90 and 25.

• What is the adaptive flushing method in use (or is it off)? keep_average?

• Is flushing neighbor pages enabled? Sometimes this helps, sometimes this really isn't efficient to do. Generally, its good on spinning disks so probably not the issue here

• I would say the buffer pool is too large if its 108G / 128G, but I assume this was benchmarked at some level

• What are the dirty pages?

• innodb_write_io_threads?

• innodb_read_io_threads?

• innodb_io_capacity?

Might be a big help to do this:

• innodb_stats_on_metadata = 0

http://www.percona.com/blog/2013/12/03/innodb_stats_on_metadata-slow-queries-information_schema/

StackExchange Database Administrators Q#77404, answer score: 4