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)

Mathieu Longtin
  • 273
  • 2
  • 12

1 Answers1

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.

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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). – Mathieu Longtin May 17 '12 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 http://dba.stackexchange.com/a/17434/877) – RolandoMySQLDBA May 17 '12 at 17:41
  • Correction: Double write buffer is for changes-to-be-applied. Those changes are flushed periodically. – RolandoMySQLDBA May 17 '12 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? – Mathieu Longtin May 17 '12 at 18:10
  • Not sure because that depends on how good or bad InnoDB does disk flushing ( See innodb_flush_method ( http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_method )) – RolandoMySQLDBA May 17 '12 at 18:17
  • Please note in the Documentation on innodb_flush_method, setting it wrong value can adversely affect SELECTs. – RolandoMySQLDBA May 17 '12 at 18:22
  • I wrote about innodb_flush_method last year ( http://dba.stackexchange.com/a/1575/877 ). Morgan Tocker (works for Percona), who was my instructor for two classes, commented on my answer and said "Worth pointing out: O_DIRECT is only used on the table-space files, not on the logs. Also - whether O_DIRECT is going to be useful or not depends on the hardware. I linked to an open documentation bug as a comment to the author's question". Thus, if you are using innodb_file_per_table, DEPENDING ON YOUR HARDWARE, Flushing the double write buffer depends on .ibd files being flushed as well. – RolandoMySQLDBA May 17 '12 at 18:32