HOT - preliminary results

Hi All,

Here are some preliminary numbers with the HOT 4.0 patch that I sent
out earlier today. These are only indicative results and should not be
used to judge the performance of HOT in general. I have intentionally
used the setup favorable to HOT. The goal here is to point out the best
usage of HOT so that we get some early feedback about its usefulness.
We still need to run several benchmark tests to see where it would
be useful and where it would add unnecessary overhead without any
real gains. This would also require lot of tuning and would be heavily
dependent on the community feedback/suggestions.

I used a modified pgbench to test HOT with just accounts and history
tables. The only operations are UPDATE on the accounts and INSERT
into the history table. So basically I removed other UPDATEs and SELECT
statements from the pgbench tpc_b tests.

The machine has 2 GB RAM, but the shared_buffers are set to 128MB
to make the tests IO bound. fsync is turned on and autovacuum
is enabled with a naptime of 60 seconds and scale factor of 0.2.

We had runs with 90 scaling factor, 90 clients and 50000 txns / client.
The "accounts" table is created with a fillfactor of 90 so that there is
free space available for initial HOT UPDATEs.

Here are the results with current CVS HEAD.

transaction type: TPC-B (sort of)
scaling factor: 90
number of clients: 90
number of transactions per client: 50000
number of transactions actually processed: 4500000/4500000
tps = 1007.451264 (including connections establishing)
tps = 1007.512019 (excluding connections establishing)
---------------------------------------------------------------------------------

In the same setup and with the same fillfactor, HOT gave us the
following results:

transaction type: TPC-B (sort of)
scaling factor: 90
number of clients: 90
number of transactions per client: 50000
number of transactions actually processed: 4500000/4500000
tps = 2006.098739 (including connections establishing)
tps = 2006.361857 (excluding connections establishing)
---------------------------------------------------------------------------------

Thats a good jump of 100% in terms of tps. A more detail analysis
shows that HOT helps keep the size of the "accounts" table and
the index almost constant.

With CVS HEAD, the accounts relation and the index grows
considerably at the end of the test.

accounts 157895 (initial size) 49284 (increase)
accounts_pkey 19709 (initial size) 19705 (increase)

Whereas HOT keeps the table sizes stable.

accounts 157895 (initial size) 43 (increase)
accounts_pkey 19709 (initial size) 0 (increase)

This easily explains the significant jump in the tps. Of course, things
might
not always work in favor of HOT. Few things that can easily dampen the
performance that come to my mind are:

- Index key column UPDATEs

HOT works on the premise that index column does not change often.
If that is not the case, HOT is not used and might put unnecessary
overhead in the execution path.

- Often change in tuple size between UPDATEs

This may limit our ability to reuse the heap-only and dead root tuples.
It may also lead to tuple level fragmentation when we reuse a larger
dead tuple to store a smaller new tuple. Thankfully it would be much
easier to correct row-level fragmentation without a VACUUM-strength
lock.

- Long running transactions

This can lead to very long HOT-update chains. Still my guess is
it won't be much worse than the current behavior.

- HOT-updates on very small tables

We prune the HOT-update chain in the SELECT path. This requires
releasing the SHARE lock and acquiring EXCLUSIVE lock on the page.
I am wondering for very small tables, can that be point of contention ?
Also, if the small tables can always fit completely in the buffer pool and
can be vacuumed very frequently, HOT may not very effective. I ran
the above mentioned tests with normal pgbench and HOT boosts
tps from 976 to 1024, though it shows good value in keeping the
small table size stable.

With CVS HEAD:

accounts 157895 (initial size) 41157 (increase)
accounts_pkey 19709 (initial size) 19705 (increase)

tellers 5 (initial size) 2017 (increase)
tellers_pkey 4 (initial size) 537 (increase)

branches 1 (initial size) 256 (increase)
branches_pkey 2 (initial size) 605 (increase)

With HOT:

accounts 157895 (initial size) 39 (increase)
accounts_pkey 19709 (initial size) 0 (increase)

tellers 5 (initial size) 87 (increase)
tellers_pkey 4 (initial size) 3 (increase)

branches 1 (initial size) 66 (increase)
branches_pkey 2 (initial size) 0 (increase)

I would like to emphasis again that we still need many more
tests, in different environments and setups, to measure performance
impact of HOT, good or bad. But these tests should be a good starting
point to believe that HOT is working on the expected lines.

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com