why is mysql still writing data two hours after all transactions have stopped

Question

I have a mysql server used mostly as data warehouse. It loads about 4-5GB of data per day (actual database size, not the size of the source data).

Lately, I noticed something peculiar. After I stopped the loading process, and nothing else modifies the database, mysql keeps writing data at a rate of 5MB/s for over two hours.

This particular install uses 20GB of innodb_buffer_pool, so even if it was flushing all pages in memory, it would take about 70 minutes to do that.

Additional background information

This server is being mirrored, so it keeps the transaction log.

innodb_buffer_pool_pages_dirty keeps yoyo-ing between 60 and 10.

innodb_rows_(deleted|inserted|updated) is stable, so I'm pretty sure no modification is happening.

I have confirmed that it is indeed mysql doing the writting using /proc/sys/vm/block_dump (see http://www.xaprb.com/blog/2009/08/23/how-to-find-per-process-io-statistics-on-linux/)

This last run, the server has only been up for two days, so it's highly unlikely that it has that many reads backlogged.

This is mysql 5.0 (yes it's old)

Answer 1

InnoDB has the habit of purging about 100 MB every 10 seconds from the InnoDB Buffer pool. That's by design. The idea behind InnoDB transactional model is to delay writes as much as possible.

If you have major spikes in reads, or sustained moderate reads going on, there are still writes occurring because of MVCC. I wrote about this back on May 3, 2012 ( Which is faster, InnoDB or MyISAM? )

You can still see evidence of this in a special way.

Try one of the following two(2) options:

OPTION #1

SET GLOBAL innodb_max_dirty_pages_pct = 0;

The default for innodb_max_dirty_pages_pct is 90 in MySQL 5.0. Set it to zero(0) and flush the Buffer Pool. After it is flushed, set it back to 90. The dirty pages will just pile up again. Delayed writes of changed blocks with just go back to the 100MB/10sec flush pattern all over again.

OPTION #2

SET GLOBAL innodb_max_dirty_pages_pct = 0;

Leave the innodb_max_dirty_pages_pct indefinitely. You will see higher-than-normal writes of the dirty pages.

Regardless which option you choose, if innodb_buffer_pool_pages_dirty conitnues that same pattern and rate of change, without the presence of INSERTs/UPDATEs/DELETES, only SELECTs can be source of this behavior.

You may want to experiment with setting innodb_log_buffer_size (default is 8MB). A larger log buffer saves on disk I/O, but be careful with large COMMITs.