Wednesday, May 12, 2010

OLTP Benchmark - Cheats

I am tired of not making any real progress on improving my TPM so it is now time to cheat. I call the next 3 things cheats because they are not something I would do in a production environment or they artificially improve the benchmark TPMs. People who do benchmarks for a living would probably consider this just part of playing the benchmarking game, so lets play.

Cheat #1 - Buffered Logging

I can't turn off logging all together but I can improve performance by using buffered logging instead of unbuffered logging. The downside to using buffered logging in a production environment is the possibility of the user committing a transaction, that commit being written to the logical log buffer, a response being sent back to the user saying the transaction was committed followed by an engine crash before the logical log buffer is flushed to disk which will cause that transaction to be rolled back (even tho the user was told it was comitted) when the engine is restarted and recovery happens.

The upside to buffered logging is it is faster. Changing LOGBUFF to 256 and turning on buffered logging for the ptpcc database improved my TPM to 6785, an improvement of almost 10%.

Cheat #2 - Those Pesky Checkpoints

A funny thing happens around 11.5 minutes into my 15 minute benchmark, my performance falls off a cliff for about 1 to 2 minutes while I flush all my data to disk during a checkpoint. It is nice to see processing not stop all together as it would in the days of blocking checkpoints (I want to marry non blocking checkpoints because I love them so much) but it would be nicer to not see them at all.

If we're just playing the benchmarking game all we need to do is make it through the 15 minute benchmark time without doing any writes (foreground, LRU or chunk). So far I don't have any foreground or LRU writes so I should just have to avoid any checkpoints during my run.

I chose to change the RTO_SERVER_RESTART ONCONFIG from 120 to 1800. This parameter tells the engine to fire a checkpoint when the time to recover from a failure reaches 30 minutes. Not too practical for a real world production OLTP engine, but great for a 15 minute benchmark.

I suppose I could also have disabled auto checkpoints and simply set the old school CKPTINTVL ONCONFIG parameter to 1800. I will do this if RTO_SERVER_RESTART starts firing checkpoints during our 15 minute run.

TPM with no checkpoints = 7032 an improvement of 3.6% and I broke the 7000 TPM barrier!

Cheat #3 - Prime the buffer cache

After the last benchmark run I checked the last few lines of onstat -P to see how many of my 1.5 million buffers were actually filled with data. Turns out that 45.5% of my buffers had gone unused. This is just a factor of my benchmark time being short and not having enough time to populate the buffers with read in pages. I'm going to take advantage of all this space by prepopulating my buffers with the data I think the benchmark will need, specifically the customer and stock data pages.

If I run the following 3 SQLs before I run my benchmark I should populate the buffers with the data I want and hopefully get a better read cache hit rate.

select {+full (oorder)} count(distinct o_c_id) from oorder;
select {+full (customer)} count(distinct c_zip) from customer;
select {+full (stock)} count(distinct s_quantity) from stock;

Doing this increased my read cache hit % up to 99.81 from around 98.5 for a new TPM of 7307.

This also reopened an area I can tune. Now that the oorder, customer and stock table a fully cached before the benchmark and we are no longer doing any writes during the benchmark my I/O to physical disks has changed a lot.

dbspace4 had 0 I/Os during the last benchmark and dbspace3 had just a handful of I/Os. I should redistribute my data and indexes to take advantage of this.


  1. Hi Andrew,

    Very funny and interesting this tests!

    I believed you have a lot of tests in mind... anyway, here some suggestions, what I think is very interesting to see the results.

    Active the compression, What I read in your previous posts, don't appear necessary because the database isn't big , but in performance perspective will run faster in this situation?

    Communications tricks... OPTOFC, OPTMSG, FET_BUF_SIZE, IFX_NETBUF_SIZE, IFX_NETBUF_PVTPOOL_SIZE , sqlhosts b=.... probably almost will don't have effect, because a lot of them depends how the code was wrote, but.. if don't try, never will discovery... and off course the basic.. more socs threads..

    On more comment, the test "Spreading out the I/O" will be interesting before you grow the buffers... (simulating a machine with limited memory, but with good storage and bad dbspaces/disk layouts).

  2. Hello Andrew.
    I'm reading these posts with great interest. But this one was special. It' really tells us a lot about "benchmarking". At least on it's technical side. Now imagine how fun it should be for the ones holding the source code :)

    I think I saw somewhere that this benchmark was available as a virtual machine. I'm I right?

  3. Cesar,

    You are correct, I do have a lot of things in mind including a lot, but not all, of the things you suggested.

    OPTOFC and compression are definitely on the short list of things to try as soon as I exhaust the easy ONCONFIG changes and disk I/O balancing tricks I want to do.

    Interestingly enough I have tested increasing soc threads and I didn't see a noticeable improvement but I plan on testing that again and writing about it.

    If you have any other ideas, I'm all ears. I've just scratched the surface on things to do. I haven't even looked at optimizing individual SQLs that the benchmark runs yet. I am concerned that I won't be able to improve the TPM much more but that won't stop me from trying.

  4. Fernando,

    I am not sure if the benchmark is available on a virtual machine. I know for the IIUG contest benchmark Lester used a version of the Java benchmarksql ported by AGS on a real (not VM) server running the Informix Developer Edition.

    The version of the benchmark I'm running here is one I wrote in Python based off the original benchmarksql in Java.

    It is really funny how much of a game benchmarking is and how far from reality the results you see on really are. If you have access to the engine source code (and the OS and the storage for that matter) you can obviously add hooks just to improve your benchmark results and I imagine there is a lot of this going on.

    With that said, I still think benchmarks are important. Well, let me clarify. I don't think the benchmark results themselves are really important. I think it is important for any database engine that wants to seriously compete to release a benchmark. I hope to see one on Informix soon as was promised in Kansas a few weeks ago.

  5. Here's a non-cheaty thing you can try ... revert back to the pre-cheat model and set OPT_OFC. ;o)

    And I don't really have a problem with buffered logging, as long as your log buffer isn't too big.