Tuesday, May 11, 2010

OLTP Benchmark - FILLFACTOR


In my ongoing quest to minimize I/O I will next try to reduce the number of index pages I have. 4 tables in the benchmark get rows inserted: history, order_line, oorder and new_order and the history table doesn't have any indexes on it.

This means the remaining tables have no inserts and I can set FILLFACTOR to 100 and put as much index data on a page that will fit and hopefully reduce my I/O.

For the 3 tables that currently do have indexes and are inserted into I will start by changing the FILLFACTOR from the default of 80 to 90 and see what happens. Over the course of the benchmark I increase the number of rows in these tables by 5%, so a more optimal FILLFACTOR may be 95%. I'll try that second and then try a FILLFACTOR of 100% for all tables just for fun.

FILLFACTOR 90% for my 3 insert tables - TPM 6190, meh, not much improvement over 6187.

FILLFACTOR 95% - TPM 6100

FILLFACTOR 100% - TPM 6165

I think it is interesting that FILLFACTOR 100% outperformed FILLFACTOR 95%, other than that nothing of any real excitement going on here.

Back to the drawing board.

2 comments:

  1. Hi Andrew,
    Did you test with low fillfactor? lets says 50%.
    Where at the moment of the insert always will be a room for the key avoiding split leafs...

    General speaking , not focused for this situation (fillfactor). Do you know if in this tests, how much update/delete have?
    The btscanner is active? with the default parameters? Maybe it can start running with the 5k threshold and impact in the performance...

    Cesar

    ReplyDelete
  2. Cesar,

    I just tested with a FILLFACTOR of 50% on my 3 insert tables and I did not see an improvement.

    The benchmark performs about 800K inserts and only 60K deletes during the 15 minute run.

    I plan on testing btscanner settings eventually.

    ReplyDelete