After publishing in May a benchmark report about InnoDB Dirty pages & Log size impact I received several very interesting comments, and one of them pointed to the purge lag problem and InnoDB innodb_max_purge_lag parameter created specially for such purpose! So, I was very curious to know how it may help me in my workload...

The following study is about how innodb_max_purge_lag is working now and what may be done to make things better :-))

I'm not pretending here on any absolute truth :-) My goal is to understand what's going on with purge on my workload and see if there is something else possible to improve..

Benchmark scenario

My benchmark scenario will be exactly the same as …

Currently several probe InnoDB code improvements were done by our MySQL Team. I was happy to test them with db_STRESS workloads but on Solaris/SPARC server (M5000). Then discussing with Mikael  I was surprised he saw much less improvement from the latest probe builds on his Linux/AMD64 box... And it was unclear why the performance improvement gap is more important on SPARC: due SPARC itself? due Solaris? due a test case?.. To bring more lights and understand better what's going differently on an AMD box I've preferred to avoid to change too many things on the same time :-) So, once one of the latest 32cores AMD server (X4600-M2) was available, I was curious to test it under Solaris10 and connected to the same storage box as M5000 before. And here are my results...

My intention is to replay exactly the same tests as previously on M5000 but on the newest X4600 (8CPU AMD …

Following my previous post (where I've also told about a significant performance gain by using a bigger InnoDB log size ), I'd like to present you some results obtained on the Read+Write workload and log size equal to 1024MB. I've labeled it on graphs as "FF" (Fast & Furious :-)) as within a such configuration MySQL server may go very fast until it'll meet a "furious flushing" to free some space within a log file... :-)

However, the speed-up is quite important, so if you don't worry too much if time to time your production activity may have a short drop on performance - you have to consider this option as one of the first to test to improve your throughput! :-)

As you may see from the following graphs, most of engines performing with 1024M redo log even better than previously tested MySQL 5.4 …

The beginning of this story is coming from the first weeks of the last year - seeking for the most optimal MySQL config parameters I've discovered a strange thing:

• my dirty pages percentage setting 15 was completely ignored by InnoDB during my tests!...
• once the test workload was finished it still took 30 minutes yet to flush dirty pages!...

I've told about a lot of people on that time, but everybody were just surprised and did not really believe such case is possible... Agree, it's hard to believe ;-)

So, to refresh the story, let me first to show you the following picture:

As you may see here the dirty pages percentage is higher than 15% for sure :-) As well you may see the 30 minutes of dirty pages flushing once the workload is finished (too much according to me on the idle server! - and probably recovery process will take even more time if my server will crash?...)

…

Last month when I've published my results obtained with PostgreSQL 8.3.7 on db_STRESS benchmark I was surprised they were much lower comparing to MySQL, as well some signs alarmed me there is something goes wrong with PostgreSQL... So, once it was possible, I took my time and prepared another testing on the same M5000 server I published MySQL results last week.

I would say I discovered a lot of new things benchmarking PostgreSQL ! I'm not kidding :-) And without going too much in detail, the main gain seems to me of MySQL over PostgreSQL was a lower cost on executing a single query. The query in question was the second SELECT in db_STRESS.

SELECT-2 execution time:

• MySQL 5.4: 0.44ms
• PostgreSQL 8.4: 1.3ms
• PostgreSQL 8.4 prepared statement: 0.98ms

Again, if PostgreSQL being slower on a single query scaled much more far on say 24 cores - it'll be not really a problem. But …

Here are my new db_STRESS results obtained on the latest Sun M5000 server (SPARC64-VII 8CPU quad-core / bi-thread 2400Mhz). I was very curious to retest the "official" MySQL 5.4 on this machine as well other versions and InnoDB engines. But on the same time the new XtraDB version 5 was announced. As well Google shipped their v3 patch. How to resist? :-) (I'll try to be short :-))

MySQL 5.0 & 5.1

There is no more performance gap between 5.0 and 5.1 as I observed before , both versions are having near the same performance and both are way slower comparing to others :-) So, honestly, if you have any performance problem - why do not migrate to 5.4, XtraDB, or at least the latest InnoDB plugin?..

XtraDB Performance Improvements

I was very pleasantly surprised by performance improvements done in XtraDB-5! …

In case you also want to replay the same dbSTRESS test scenarios I've posted results yesterday, I've added into the test kit a shell script which may be used as a Reference Scenario.

More details on how to use it is here .
As well dbSTRESS kit and User Guide are available for download from http://dimitrik.free.fr

Forget to say, I've also tested PostgreSQL 8.3.7 during the last benchmark serie with dbSTRESS!

A big surprise - if two years ago on the same workload PostgreSQL was two times faster (see: http://dimitrik.free.fr/db_STRESS_BMK_Part2_ZFS.html ), now it's MySQL 5.4 outperforming PostgreSQL!

• Read-Only workload: MySQL is near two times faster now! (13.500 TPS vs ~7.000 TPS for PostgreSQL)
  
  
• Read+Write workload: MySQL performs as well or better (7.000-8.000 TPS vs 6.000-7.000 TPS for PostgreSQL)

For more details: http://dimitrik.free.fr/db_STRESS_MySQL_540_and_others_Apr2009.html#note_5443  

As you may already know, today Sun announces MySQL 5.4. It's a big step forward and I hope you'll have enough time today to follow all blog posts and other interesting related publications! :-)

I leave my colleagues to tell you what is new in 5.4 and what will come with next releases! (start with Mikael's and Allan's blogs). As well you will not have a whole story if you miss Tim's, Neel's, Richard's, Vince's, David's and many other valuable blogs posts!

From my part I want to present you some "unofficial" dbSTRESS …

As you may already know, today Sun announces MySQL 5.4. It's a big step forward and I hope you'll have enough time today to follow all blog posts and other interesting related publications! :-)

I leave my colleagues to tell you what is new in 5.4 and what will come with next releases! (start with Mikael's and Allan's blogs). As well you will not have a whole story if you miss Tim's, Neel's, Richard's, Vince's, David's and many other valuable blogs posts!

From my part I want to present you some "unofficial" dbSTRESS …