Re: HOT - preliminary results - Mailing list pgsql-hackers

Just for curiosity, I would like to ask you why you need to modify
pgbench. pgbench can accept custom SQL scripts...

P.S. HOT seems to be one of the greatest enhancements since PostgreSQL
was born!
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> 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
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>