Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 writting 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)

share|improve this question
    
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'. Is this increasing? –  Derek Downey May 17 '12 at 15:45
    
It's currently at 57 and stable. –  Mathieu Longtin May 17 '12 at 15:53
    
How much memory does your server have? And how much memory is the MySQL process using? –  ypercube May 17 '12 at 16:22
    
32GB for the server, mysql is using about 22GB. –  Mathieu Longtin May 17 '12 at 16:23
add comment

1 Answer

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.

share|improve this answer
    
But why does purging result in writing if the pages are not dirty? There is no inserts/updates/deletes happening right now on that server. –  Mathieu Longtin May 17 '12 at 15:57
    
In the second paragraph, I mentioned that the MVCC is happening with reads. Writes are still mandatory to support the ACID compliance requirements of InnoDB. Check out the link I mentioned in the second paragraph for further detail. –  RolandoMySQLDBA May 17 '12 at 16:05
    
The data is not being modified, with MVCC you don't increase the timestamp of a row when reading, just when writing. And this system is 90% inserts, 10% updates, so there can't be that much MVCC cleanup. –  Mathieu Longtin May 17 '12 at 16:35
    
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 dba.stackexchange.com/a/17434/877) –  RolandoMySQLDBA May 17 '12 at 17:41
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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