MySQL Forums
Forum List  »  Newbie

high iops with innodb_flush_log_at_trx_commit = 0
Posted by: Eric Anopolsky
Date: May 09, 2020 06:04PM

Good evening,

Please be advised I would appreciate some assistance configuring mysql to consume less of my slow disk's "iops budget" while still executing queries quickly for clients.

There is only one application using this mysql instance, and it is no big deal to lose 30 seconds of committed transactions in case of a crash or power failure because this is a personal project rather than something serious. I thought I could achieve the desired results by adding the following lines to mysqld.cnf:

innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_timeout = 30
innodb_log_buffer_size = 512000000

However, after doing so, I am still seeing mysqld writing a lot of data to disk, and it is not clear why. Monitoring the system for a 60 second period with mysqld running revealed the following:

1. The mysqld general log for that minute has 183 lines. Filtering out log entries that consist only of BEGIN or COMMIT statements, only 51 queries were executed during that minute. None of the queries look like they should be performance hogs (e.g. single record updates, single record inserts, no complicated joins).

2. During that minute, the average iops for the entire system was 61.75. There were no disk reads at all, and 330MB were written to disk at an average rate of about 5.5MB/sec.

3. During that minute, mysqld requested that the kernel sync a file to disk (i.e. called fsync() ) 271 times. Here is the breakdown per file:

76 /var/lib/mysql/#ib_16384_0.dblwr
15 /var/lib/mysql/ibdata1
12 /var/lib/mysql/ib_logfile0
35 /var/lib/mysql/ib_logfile1
4 /var/lib/mysql/icinga2/icinga_hoststatus.ibd
6 /var/lib/mysql/icinga2/icinga_programstatus.ibd
6 /var/lib/mysql/icinga2/icinga_runtimevariables.ibd
19 /var/lib/mysql/icinga2/icinga_servicestatus.ibd
20 /var/lib/mysql/mysql.ibd
40 /var/lib/mysql/undo_001
38 /var/lib/mysql/undo_002

For comparison, I stopped mysqld as well as the client application, sync'd the disks, and measured disk io for another 60 seconds. During that time, the average iops for the entire system was 1.08. There were no disk reads at all, and only 9.9MB were written to disk at an average rate of about 165KB/sec. I'm pretty sure the client application isn't causing a lot of disk writes on its own because iotop normally shows that only mysqld is spending a lot of time waiting for disk IO to complete.

What am I missing?

Thanks,
Eric

Options: ReplyQuote


Subject
Written By
Posted
high iops with innodb_flush_log_at_trx_commit = 0
May 09, 2020 06:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.