Re: HOT - preliminary results

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