Re: [HACKERS] HOT pgbench results
On 8/14/07, ITAGAKI Takahiro <[EMAIL PROTECTED]> wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > > > I ran some CPU intensive pgbench tests on HOT. Results are not > > surprising, HOT makes practically no difference on the total transaction > > rate, but reduces the need to vacuum: > > > > unpatched HOT > > tps 3680 3790 > > WAL written(MB) 5386 4804 > > checkpoints 109 > > autovacuums 116 43 > > autoanalyzes139 60 > > I also ran pgbench with/without HOT using a bit different configurations > (pgbench -s10 -c10 -t50). There were 10% performance win on HOT, > although the test was CPU intensive and with FILLFACTOR=100%. I'm curious why I am seeing results so different from everybody else (I had almost double tps with HOT). Are you running fsync on/off? Any other changes to postgresql.conf? merlin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] HOT pgbench results
Thanks for the testing, ITAGAKI Takahiro wrote: > I gathered oprofile logs. There were 4 HOT-related functions, that didn't > appear in the unpatched test. But it is probably not so serious. > - heap_page_prune 1.84% > - PageRepairFragmentation 0.94% > - pg_qsort 0.44% (called from PageRepairFragmentation) That's expected. Those functions are involved in removing the dead HOT tuples, replacing VACUUMs. Maybe we could make them cheaper, but it's not too bad as it is. > On the other hand, the number of _bt_compare and _bt_checkkeys were > reduced by HOT, because we avoid the most part of index insertions. > It looks like LWLockAcquire/Release were also reduced, but I cannot > assure it is a benefits of HOT or a measurement deviation. It could very well be real. Because of the reduction of index insertions, there's less locking of the index pages. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] HOT pgbench results
Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > I ran some CPU intensive pgbench tests on HOT. Results are not > surprising, HOT makes practically no difference on the total transaction > rate, but reduces the need to vacuum: > > unpatched HOT > tps 3680 3790 > WAL written(MB) 5386 4804 > checkpoints 109 > autovacuums 116 43 > autoanalyzes139 60 I also ran pgbench with/without HOT using a bit different configurations (pgbench -s10 -c10 -t50). There were 10% performance win on HOT, although the test was CPU intensive and with FILLFACTOR=100%. unpatched HOT tps 3366 3634 WAL written(MB) 4969 4374 checkpoints 9 8 autovacuums 126 42 autoanalyzes146 59 I gathered oprofile logs. There were 4 HOT-related functions, that didn't appear in the unpatched test. But it is probably not so serious. - heap_page_prune 1.84% - PageRepairFragmentation 0.94% - pg_qsort 0.44% (called from PageRepairFragmentation) On the other hand, the number of _bt_compare and _bt_checkkeys were reduced by HOT, because we avoid the most part of index insertions. It looks like LWLockAcquire/Release were also reduced, but I cannot assure it is a benefits of HOT or a measurement deviation. unpatched HOT % symbol name 4.08674.2314AllocSetAlloc 2.78392.8022base_yyparse 1.8392heap_page_prune 1.84591.6659SearchCatCache 1.74051.6087MemoryContextAllocZeroAligned 1.69361.5743hash_search_with_hash_value 1.06721.1822base_yylex 1.24301.1570XLogInsert 0.9356PageRepairFragmentation 1.35490.8911LWLockAcquire 1.09770.8663LWLockRelease 0.80180.7284nocachegetattr 0.75680.7124FunctionCall2 0.52640.6795ScanKeywordLookup 0.71150.6462hash_any 0.73990.5963AllocSetFree 0.66500.5925GetSnapshotData 0.55360.5789MemoryContextAlloc 0.56430.5547hash_seq_search 0.46600.5005expression_tree_walker 0.52930.4777ExecInitExpr 0.4381pg_qsort 0.43760.4321hash_uint32 0.41600.4268expression_tree_mutator 0.43220.4183LockAcquire 0.69330.3911_bt_compare 0.52700.3828PinBuffer 0.40250.3798fmgr_info_cxt_security 0.44580.3758MemoryContextAllocZero 0.5101 _bt_checkkeys Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] HOT pgbench results
On 8/8/07, Merlin Moncure <[EMAIL PROTECTED]> wrote: > On 8/7/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > > I ran some CPU intensive pgbench tests on HOT. Results are not > > surprising, HOT makes practically no difference on the total transaction > > rate, but reduces the need to vacuum: > > > > unpatched HOT > > tps 36803790 > > WAL written(MB) 53864804 > > checkpoints 10 9 > > autovacuums 116 43 > > autoanalyzes139 60 > > Here are some more results...all stock except for partial writes, 24 > segments (fsync on). hardware is four 15k sas in a raid 10. I am > seeing very good results in other real wold scenarios outside of > pgbenchanyone is interested drop me a line. Note I cut the > transaction runs down to 100k from 1M. > > *** HOT *** > [EMAIL PROTECTED] root]$ time pgbench -c 5 -t 10 > starting vacuum...end. > transaction type: TPC-B (sort of) > scaling factor: 10 > number of clients: 5 > number of transactions per client: 10 > number of transactions actually processed: 50/50 > tps = 1156.605130 (including connections establishing) > tps = 1156.637464 (excluding connections establishing) > > real7m12.311s > user0m26.784s > sys 0m25.429s > > *** cvs, HOT *** > [EMAIL PROTECTED] pgsql]$ time pgbench -c 5 -t 10 > starting vacuum...end. > transaction type: TPC-B (sort of) > scaling factor: 10 > number of clients: 5 > number of transactions per client: 10 > number of transactions actually processed: 50/50 > tps = 630.510918 (including connections establishing) > tps = 630.520485 (excluding connections establishing) > > real13m13.019s > user0m27.278s > sys 0m26.092s oops! second case was w/o HOT patch applied (but we knew that) :D merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] HOT pgbench results
On Tue, 2007-08-07 at 20:27 +0100, Gregory Stark wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > > On Tue, 2007-08-07 at 13:16 +0100, Heikki Linnakangas wrote: > >> I ran some CPU intensive pgbench tests on HOT. Results are not > >> surprising, HOT makes practically no difference on the total transaction > >> rate, but reduces the need to vacuum: > >> ... > > Nor would I expect anything else, on this test. > > I think the surprising thing was that it wasn't slower due to the extra cpu > spent pruning tuples. ...balanced by the extra time spent adding new blocks and doing block-spanning updates without HOT. For CPU bound situations, the real-world difference lies in the logical I/O we avoid by not doing index insertions. Larger tables have deeper index trees, so cause more block accesses to locate the block into which to insert. Small tables with few indexes aren't a real test of that, even if it does illustrate the basic CPU balance that HOT now offers in its latest incarnation (well done Heikki and Pavan). -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] HOT pgbench results
On 8/7/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > I ran some CPU intensive pgbench tests on HOT. Results are not > surprising, HOT makes practically no difference on the total transaction > rate, but reduces the need to vacuum: > > unpatched HOT > tps 36803790 > WAL written(MB) 53864804 > checkpoints 10 9 > autovacuums 116 43 > autoanalyzes139 60 Here are some more results...all stock except for partial writes, 24 segments (fsync on). hardware is four 15k sas in a raid 10. I am seeing very good results in other real wold scenarios outside of pgbenchanyone is interested drop me a line. Note I cut the transaction runs down to 100k from 1M. *** HOT *** [EMAIL PROTECTED] root]$ time pgbench -c 5 -t 10 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 10 number of transactions actually processed: 50/50 tps = 1156.605130 (including connections establishing) tps = 1156.637464 (excluding connections establishing) real7m12.311s user0m26.784s sys 0m25.429s *** cvs, HOT *** [EMAIL PROTECTED] pgsql]$ time pgbench -c 5 -t 10 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 10 number of transactions actually processed: 50/50 tps = 630.510918 (including connections establishing) tps = 630.520485 (excluding connections establishing) real13m13.019s user0m27.278s sys 0m26.092s ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] HOT pgbench results
"Simon Riggs" <[EMAIL PROTECTED]> writes: > On Tue, 2007-08-07 at 13:16 +0100, Heikki Linnakangas wrote: >> I ran some CPU intensive pgbench tests on HOT. Results are not >> surprising, HOT makes practically no difference on the total transaction >> rate, but reduces the need to vacuum: >> ... > Nor would I expect anything else, on this test. I think the surprising thing was that it wasn't slower due to the extra cpu spent pruning tuples. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] HOT pgbench results
On Tue, 2007-08-07 at 13:16 +0100, Heikki Linnakangas wrote: > I ran some CPU intensive pgbench tests on HOT. Results are not > surprising, HOT makes practically no difference on the total transaction > rate, but reduces the need to vacuum: > > unpatched HOT > tps 36803790 > WAL written(MB) 53864804 > checkpoints 10 9 > autovacuums 116 43 > autoanalyzes 139 60 Nor would I expect anything else, on this test. The pgbench database has 4 tables, of which 3 have one index and 1 has no indexes at all. A table without indexes is uncommon and most major entities such as accounts have 2-3 indexes if not more. So I would be inclined to add a PK to HISTORY and add two additional indexes to ACCOUNTS and then repeat the test to see what difference it makes. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] HOT pgbench results
On Tue, 2007-08-07 at 15:40 +0100, Heikki Linnakangas wrote: > Tom Lane wrote: > > Heikki Linnakangas <[EMAIL PROTECTED]> writes: > >>unpatched HOT > >> autovacuums116 43 > >> autoanalyzes 139 60 > > > >> HOT greatly reduces the number of vacuums needed. That's good, that's > >> where the gains in throughput in longer I/O bound runs comes from. > > > > But surely failing to auto-analyze after a HOT update is a bad thing. > > Hmm, I suppose. I don't think we've spend any time thinking about how to > factor in HOT updates into the autovacuum and autoanalyze formulas yet. > I'd argue that HOT updates are not as significant as cold ones from > statistics point of view, though, because they don't change indexed > columns. HOT-updated fields are not likely used as primary search quals. I agree with that thought, but the changes to unindexed fields are just as important for selectivity calculations so we should ANALYZE just as frequently. ANALYZE is cheap, so we aren't saving anything by avoiding them. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] HOT pgbench results
Heikki Linnakangas wrote: Tom Lane wrote: Heikki Linnakangas <[EMAIL PROTECTED]> writes: HOT greatly reduces the number of vacuums needed. That's good, that's where the gains in throughput in longer I/O bound runs comes from. But surely failing to auto-analyze after a HOT update is a bad thing. Hmm, I suppose. I don't think we've spend any time thinking about how to factor in HOT updates into the autovacuum and autoanalyze formulas yet. I'd argue that HOT updates are not as significant as cold ones from statistics point of view, though, because they don't change indexed columns. HOT-updated fields are not likely used as primary search quals. Even for fields that are used in primary searches, HOT updates avoid changing the disk block layout, and as reading from the disk is usually the most expensive operation, the decisions shouldn't change much before and after a HOT update compared to before and after a regular update. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>
Re: [HACKERS] HOT pgbench results
> > unpatched HOT > > autovacuums 116 43 > > autoanalyzes139 60 > > > HOT greatly reduces the number of vacuums needed. That's > good, that's > > where the gains in throughput in longer I/O bound runs comes from. > > But surely failing to auto-analyze after a HOT update is a bad thing. Well, the definition is that no index columns changed, so this seems debateable. It seems for OLTP you should not need an analyze, but for DSS filtering or joining on non indexed columns you would. And that would also only be relevant if you created custom statistics on non indexed columns. Andreas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] HOT pgbench results
Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: >> unpatched HOT >> autovacuums 116 43 >> autoanalyzes 139 60 > >> HOT greatly reduces the number of vacuums needed. That's good, that's >> where the gains in throughput in longer I/O bound runs comes from. > > But surely failing to auto-analyze after a HOT update is a bad thing. Hmm, I suppose. I don't think we've spend any time thinking about how to factor in HOT updates into the autovacuum and autoanalyze formulas yet. I'd argue that HOT updates are not as significant as cold ones from statistics point of view, though, because they don't change indexed columns. HOT-updated fields are not likely used as primary search quals. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] HOT pgbench results
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > unpatched HOT > autovacuums 116 43 > autoanalyzes 139 60 > HOT greatly reduces the number of vacuums needed. That's good, that's > where the gains in throughput in longer I/O bound runs comes from. But surely failing to auto-analyze after a HOT update is a bad thing. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] HOT pgbench results
I ran some CPU intensive pgbench tests on HOT. Results are not surprising, HOT makes practically no difference on the total transaction rate, but reduces the need to vacuum: unpatched HOT tps 36803790 WAL written(MB) 53864804 checkpoints 10 9 autovacuums 116 43 autoanalyzes139 60 I believe the small gain in tps is due to the reduction in WAL volume. WAL is checksummed, and calculating the CRC uses some CPU. The tps difference is almost within the margin of error, though. HOT greatly reduces the number of vacuums needed. That's good, that's where the gains in throughput in longer I/O bound runs comes from. The tests were run with fsync=off, with following commands: pgbench -i -s 10 postgres pgbench -c 5 -t 100 postgres -l The version used was CVS HEAD, with Simple-HOT-v2.patch applied in the HOT run. The cluster was initdb'd and created from scratch before each test run. Attached is the full postgresql.conf and test script used. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com pbench-runner.tar.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
