2

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)

4
  • SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'. Is this increasing? May 17, 2012 at 15:45
  • It's currently at 57 and stable. May 17, 2012 at 15:53
  • How much memory does your server have? And how much memory is the MySQL process using? May 17, 2012 at 16:22
  • 32GB for the server, mysql is using about 22GB. May 17, 2012 at 16:23

1 Answer 1

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.

7
  • Is it possible that double write catchup is causing this? It looks like around 75 pages/second are double written (according to innodb_dblwr_pages_written). May 17, 2012 at 17:35
  • Absolutely. That's what I was hinting at in the other link where I said : "In simple terms, InnoDB records what data looks like before transactions attempt to change them. Where does that get recorded? In the system tablespace file, better known as ibdata1. That requires disk I/O." The double buffer is contained within ibdata1 : (See 2nd paragraph under InnoDB Heading in dba.stackexchange.com/a/17434/877) May 17, 2012 at 17:41
  • Correction: Double write buffer is for changes-to-be-applied. Those changes are flushed periodically. May 17, 2012 at 17:42
  • So is there a way to tell how much data is left to be flushed from the double buffer to the main data files? May 17, 2012 at 18:10
  • Not sure because that depends on how good or bad InnoDB does disk flushing ( See innodb_flush_method ( dev.mysql.com/doc/refman/5.0/en/… )) May 17, 2012 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.