Re: 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

On 3/1/07, Pavan Deolasee <pavan(dot)deolasee(at)enterprisedb(dot)com> wrote:

>
> accounts 157895 (initial size) 49284 (increase)
> accounts_pkey 19709 (initial size) 19705 (increase)
>
>
Just to clarify, the relation size and increase is in number of blocks.

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com

> > accounts 157895 (initial size) 49284 (increase)
> > accounts_pkey 19709 (initial size) 19705 (increase)
> >
> >
> Just to clarify, the relation size and increase is in number
> of blocks.

The numbers are quite impressive :-) Have you removed the selects on
accounts too ?
Seems that should also show improvements.

Do you prune chains during update also ? This seems important for a
scenario where only few selects but many updates happen.

Andreas

On 3/1/07, Pavan Deolasee <pavan(dot)deolasee(at)enterprisedb(dot)com> wrote:
>
> 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 tested HOT patch (currently 3.2) over a wide variety pf pgbench runs
and found there to significant improvements (5-40%) in most cases.
Generally, I ran fsync=off and stock fillfactor. I'm also a huge
believer in HOT once all the kinks get worked out. I had some wierd
glitches in earlier versions of the patch which I could not quite
figure out and may have been some problems on my end...the new version
seems pretty solid except for one possible problem...at one point when
I dropped then later added the index on 'abalance', I got spammed
'WARNING: found a HOT-updated tuple' from psql prompt.

I was also curious about the 'worst case' of HOT so I added an index
on abalance and did some runs. The runs are not quite long enough to
remove all volatility from the results but they should be close:

**** index on abalance ****
[root(at)mernix ~]# pgbench -c1 -t100000 -p 5000 [HOT]
tps = 448.104436 (excluding connections establishing)

[root(at)mernix ~]# pgbench -c1 -t100000
tps = 448.120719 (excluding connections establishing)

[root(at)mernix ~]# pgbench -c10 -t10000 -p 5000 [HOT]
tps = 363.444429 (excluding connections establishing) run #1
tps = 415.854569 (excluding connections establishing) run #2

[root(at)mernix ~]# pgbench -c10 -t10000
tps = 416.659906 (excluding connections establishing)

**** no index on abalance ****
[root(at)mernix ~]# pgbench -c10 -t10000 -p 5000 [HOT]
tps = 659.870628 (excluding connections establishing)

[root(at)mernix ~]# pgbench -c10 -t10000
tps = 321.889840 (excluding connections establishing)

platform is intel pentium D 3ghz, 2xsata 7200rpm software raid0,
redhat fc4. This is my dev box which I usually run fsync=off to get
comparable performance with production systems and caching raid
controller.

merlin

Merlin Moncure wrote:
> On 3/1/07, Pavan Deolasee <pavan(dot)deolasee(at)enterprisedb(dot)com> wrote:
>>
> seems pretty solid except for one possible problem...at one point when
> I dropped then later added the index on 'abalance', I got spammed
> 'WARNING: found a HOT-updated tuple' from psql prompt.

Thats intentional. We don't yet support CREATE INDEX on a HOT-updated
table. This is one of the major unfinished TODO items before we can
consider patch feature complete. I have left the warning to catch
this case till then.

Thanks,
Pavan

On Thu, 2007-03-01 at 22:35 +0530, Pavan Deolasee wrote:
> Merlin Moncure wrote:
> > On 3/1/07, Pavan Deolasee <pavan(dot)deolasee(at)enterprisedb(dot)com> wrote:
> >>
> > seems pretty solid except for one possible problem...at one point when
> > I dropped then later added the index on 'abalance', I got spammed
> > 'WARNING: found a HOT-updated tuple' from psql prompt.
>
> Thats intentional. We don't yet support CREATE INDEX on a HOT-updated
> table. This is one of the major unfinished TODO items before we can
> consider patch feature complete. I have left the warning to catch
> this case till then.

CREATE INDEX and VACUUM FULL will require changes. Proposals for VACUUM
FULL have been posted, CREATE INDEX should be there tomorrow.

CLUSTER does not need changes for HOT, as things stand currently, mainly
because its MVCC behaviour is broken.

I've not looked in detail yet at the various ALTER TABLE modes, but will
do so in the next day or so.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Zeugswetter Andreas ADI SD wrote:
>>> accounts 157895 (initial size) 49284 (increase)
>>> accounts_pkey 19709 (initial size) 19705 (increase)
>>>
>>>
>> Just to clarify, the relation size and increase is in number
>> of blocks.
>
> The numbers are quite impressive :-) Have you removed the selects on
> accounts too ?

Yes. In the first set of results, SELECT on accounts is removed.

> Seems that should also show improvements.
>
> Do you prune chains during update also ?

Yes, we do prune the chains during UPDATE, but only when we
run out of free space and LP_DELETEd items on the page.
And we prune all chains on the page in that case.

Thanks,
Pavan

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
>

On 3/2/07, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>
> Just for curiosity, I would like to ask you why you need to modify
> pgbench. pgbench can accept custom SQL scripts...
>
>
Oh yes, there was no real need to modify pgbench.

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com

Simon Riggs wrote:
> On Thu, 2007-03-01 at 22:35 +0530, Pavan Deolasee wrote:
> > Merlin Moncure wrote:
> > > On 3/1/07, Pavan Deolasee <pavan(dot)deolasee(at)enterprisedb(dot)com> wrote:
> > >>
> > > seems pretty solid except for one possible problem...at one point when
> > > I dropped then later added the index on 'abalance', I got spammed
> > > 'WARNING: found a HOT-updated tuple' from psql prompt.
> >
> > Thats intentional. We don't yet support CREATE INDEX on a HOT-updated
> > table. This is one of the major unfinished TODO items before we can
> > consider patch feature complete. I have left the warning to catch
> > this case till then.
>
> CREATE INDEX and VACUUM FULL will require changes. Proposals for VACUUM
> FULL have been posted, CREATE INDEX should be there tomorrow.
>
> CLUSTER does not need changes for HOT, as things stand currently, mainly
> because its MVCC behaviour is broken.
------------------------

That's oddly discouraging. :-)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Pavan Deolasee wrote:
> Zeugswetter Andreas ADI SD wrote:
> >>> accounts 157895 (initial size) 49284 (increase)
> >>> accounts_pkey 19709 (initial size) 19705 (increase)
> >>>
> >>>
> >> Just to clarify, the relation size and increase is in number
> >> of blocks.
> >
> > The numbers are quite impressive :-) Have you removed the selects on
> > accounts too ?
>
> Yes. In the first set of results, SELECT on accounts is removed.
>
> > Seems that should also show improvements.
> >
> > Do you prune chains during update also ?
>
> Yes, we do prune the chains during UPDATE, but only when we
> run out of free space and LP_DELETEd items on the page.
> And we prune all chains on the page in that case.

Yep, that seems the most efficient approach.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Tatsuo Ishii wrote:
> 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!

Yep, I share your enthusiasm.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian escribió:
> Simon Riggs wrote:
> > On Thu, 2007-03-01 at 22:35 +0530, Pavan Deolasee wrote:
> > > Merlin Moncure wrote:
> > > > On 3/1/07, Pavan Deolasee <pavan(dot)deolasee(at)enterprisedb(dot)com> wrote:
> > > >>
> > > > seems pretty solid except for one possible problem...at one point when
> > > > I dropped then later added the index on 'abalance', I got spammed
> > > > 'WARNING: found a HOT-updated tuple' from psql prompt.
> > >
> > > Thats intentional. We don't yet support CREATE INDEX on a HOT-updated
> > > table. This is one of the major unfinished TODO items before we can
> > > consider patch feature complete. I have left the warning to catch
> > > this case till then.
> >
> > CREATE INDEX and VACUUM FULL will require changes. Proposals for VACUUM
> > FULL have been posted, CREATE INDEX should be there tomorrow.
> >
> > CLUSTER does not need changes for HOT, as things stand currently, mainly
> > because its MVCC behaviour is broken.
> ------------------------
>
> That's oddly discouraging. :-)

Apparently no one has been bothered enough to fix CLUSTER.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Fri, 2007-03-02 at 18:37 -0300, Alvaro Herrera wrote:
> Bruce Momjian escribió:
> > Simon Riggs wrote:
> > > CLUSTER does not need changes for HOT, as things stand currently, mainly
> > > because its MVCC behaviour is broken.
> > ------------------------
> >
> > That's oddly discouraging. :-)
>
> Apparently no one has been bothered enough to fix CLUSTER.

We learned from Csaba just the other day that this is a backdoor used on
production systems. I've not seen anyone admit it before, even though
I've seen it discussed.

People know that CLUSTER works better than VACUUM FULL and they use
that. The reason it hasn't been fixed is because its useful, I observe.

The oddly discouraging bit is that VACUUM FULL is only second best at
the thing its designed to achieve (compaction). Thats why I'm not
enthralled by the prospect of adding code to make VACUUM FULL work with
HOT - ISTM a good opportunity to make it work better.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Simon Riggs escribió:
> On Fri, 2007-03-02 at 18:37 -0300, Alvaro Herrera wrote:
> > Bruce Momjian escribió:
> > > Simon Riggs wrote:
> > > > CLUSTER does not need changes for HOT, as things stand currently, mainly
> > > > because its MVCC behaviour is broken.
> > > ------------------------
> > >
> > > That's oddly discouraging. :-)
> >
> > Apparently no one has been bothered enough to fix CLUSTER.
>
> We learned from Csaba just the other day that this is a backdoor used on
> production systems. I've not seen anyone admit it before, even though
> I've seen it discussed.

Yeah, I had just read that and was about to mention it, but I think
(some of?) these cases would be solved by HOT. So that usage of CLUSTER
would go away altogether, allowing us to fix the MVCC issue ... which
would require HOT to work nicely :-(

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support