Thursday, November 5, 2009
InnoDB plugin gets better again
Forgive me for being a shill, but InnoDB appears to have added a feature for the next release of the InnoDB plugin that prevents the buffer pool from getting wiped out by a full table scan. Many people have requested this. The documentation is excellent. I have tested it and not only did it work as advertised, but it didn't degrade performance on OLTP workloads. This fixes bug 45015 and is a nice feature to have when you occasionally use mysqldump to copy a table from a busy OLTP server. Now is a good time to evaluate MySQL 5.1 with the InnoDB plugin.
Subscribe to:
Post Comments (Atom)
Posts
Thanks, Mark. We don't mind you being a "shill". ;-) We are glad to be able to respond to user requests like this one, with a clever approach that allows you to manage the server behavior better to get improved performance with complex workload (mysqldump during OLTP).
ReplyDeleteCan you please share more details on the performance you observed, including the workload, configuration and settings you tried for the new parameters old-blocks-pct and old-blocks-time? If you've run a number of tests with different conditions, and different settings, we'd love to see that info!
The effectiveness of this new feature will depend on the workload, the hw and sw configuration and the precise settings of the parameters. In general, when we introduce a feature like this, we'd like to be able to provide more advice to users on how to set such parameters, not merely describe how they work.
Thanks ...
I will publish results in a few days.
ReplyDeleteThere are times when it is desirable to fill the buffer pool with a FTS.
ReplyDeleteWill there be a mechanism to "pre-load" the contents of a table into the buffer pool?
swanhart: When the buffer pool isn't full, there are no evictions and it won't actually matter. If it is full, then you can do it via setting innodb_old_blocks_time=0 and then do your FTS.
ReplyDeleteThis is mentioned in the documentation linked:
==
This strategy is unnecessary if your intent is to fill the buffer pool with a table's content. For example, you might perform a table or index scan at server startup or during benchmarking or testing specifically to “warm up” the buffer pool. In this case, leaving innodb_old_blocks_time set to 0 accomplishes the goal of loading the scanned blocks into the new sublist.
==
Performance test #1 -- sequentially scan two tables that don't fit in the buffer pool together, rescan, rescan. For my test the scan time was 50 minutes with innodb_old_block_pct=10 and 80 minutes with the LRU option disabled. This is expected as a prefix of the first table will remain in the 'new' section of the LRU and the new section is 90% of the buffer pool. With the feature enabled, nothing is reused from the buffer pool.
ReplyDeletewhat version of mysql will contain this great fix? I have a fever for it!
ReplyDelete5.1.41
ReplyDelete