Re: [HACKERS] [Testperf-general] pg_autovacuum w/ dbt2
Mark Wong wrote: On Fri, Jan 07, 2005 at 10:13:52AM -0800, Josh Berkus wrote: Mark, All default parameters. Matthew also recommended using the vacuum_delay setting so I was about to try that. Interesting ... the default parameters are quite conservative, running only when the table has doubled in new rows. So if those spikes are vacuums, then the DBT2 test is updating or adding 100% of the rows in the orders table every 6-8 minutes?Does that seem right? Some hand-wavey math here: We start with 18,000,000 rows in the orders table. Based one 3281 transactions per minute for just the new-order transaction: 288 transactions per minute for delivery New-order inserts 3281 rows per minute (not counting the 1% of rollbacks.) Delivery updates 288 rows per minute in orders. So no, we're not change that many rows per minute... Except the thing pg_autovacuum is actually doing is Analyzing the district table (4800 rows, takes ~15 sec) and vacuuming the warehouse table (66,000, takes ~50sec) which takes about 50 seconds. Since the default vacuum sleep time is 5 minutes, that would explain the dip every 6 minutes. Matthew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [Testperf-general] pg_autovacuum w/ dbt2
On Fri, Jan 07, 2005 at 10:13:52AM -0800, Josh Berkus wrote: > Mark, > > > All default parameters. Matthew also recommended using the > > vacuum_delay setting so I was about to try that. > > Interesting ... the default parameters are quite conservative, running only > when the table has doubled in new rows. So if those spikes are vacuums, > then the DBT2 test is updating or adding 100% of the rows in the orders table > every 6-8 minutes?Does that seem right? > Some hand-wavey math here: We start with 18,000,000 rows in the orders table. Based one 3281 transactions per minute for just the new-order transaction: 288 transactions per minute for delivery New-order inserts 3281 rows per minute (not counting the 1% of rollbacks.) Delivery updates 288 rows per minute in orders. So no, we're not change that many rows per minute... Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [Testperf-general] pg_autovacuum w/ dbt2
Mark, > All default parameters. Matthew also recommended using the > vacuum_delay setting so I was about to try that. Interesting ... the default parameters are quite conservative, running only when the table has doubled in new rows. So if those spikes are vacuums, then the DBT2 test is updating or adding 100% of the rows in the orders table every 6-8 minutes?Does that seem right? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [Testperf-general] pg_autovacuum w/ dbt2
On Fri, Jan 07, 2005 at 09:58:47AM -0800, Josh Berkus wrote: > Mark, > > > No manual vacuum commands before. The decline in performance has been > > pretty consistent in all my previous tests and people have told me on > > many occasions that the decline in performance was probably because I > > was never using vacuum... > > Hmmm ... what autovacuum params are you using? From the look of the graphs, > VACUUM is running every 6 minutes or so, and having a huge impact on > performance when it does run. Give me a bit of time to crunch the database > log, and we'll see which table vacuums were taking the most time. > > Can you re-run this test with a fairly generous vacuum_delay setting? Given > that the performance drops seem to correspond to wait times, I think this > could mean a big difference. > All default parameters. Matthew also recommended using the vacuum_delay setting so I was about to try that. Mark ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [Testperf-general] pg_autovacuum w/ dbt2
Mark, > No manual vacuum commands before. The decline in performance has been > pretty consistent in all my previous tests and people have told me on > many occasions that the decline in performance was probably because I > was never using vacuum... Hmmm ... what autovacuum params are you using? From the look of the graphs, VACUUM is running every 6 minutes or so, and having a huge impact on performance when it does run. Give me a bit of time to crunch the database log, and we'll see which table vacuums were taking the most time. Can you re-run this test with a fairly generous vacuum_delay setting? Given that the performance drops seem to correspond to wait times, I think this could mean a big difference. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [Testperf-general] pg_autovacuum w/ dbt2
Ok, but what I'm curious to do is see if you run the non-pg_autovacuum test for a "long time" (4 hours? more?) when does it get slower that running with pg_autovacuum. And, can you demonstrate that running the tests with pg_autovacuum for a long time (say 4 hours) that the performance stays steady. Also, I would very much like to see this test run with pg_autovacuum and it's vacuum delay settings enabled. Matthew ps: I know time is limited and these tests take a lot of time to run, so please take my requests with a grain of salt, all I'm saying is that I think these would be interesting results to see. Mark Wong wrote: Yeah, same hardware and database configuration. No manual vacuum commands before. The decline in performance has been pretty consistent in all my previous tests and people have told me on many occasions that the decline in performance was probably because I was never using vacuum... Mark On Fri, Jan 07, 2005 at 08:48:52AM -0500, Matthew T. O'Connor wrote: I'm curious, the original run you posted with 3825 NOTPM is still 17% faster than the latest pg_autovacuum run which shows 3280 NOTPM. Is this on the same hardware? Also, did the original non-pg_autovacuum run any manual vacuum commands? Also, does the non-pg_autovacuum run start slowing down after a while? The graphs look like there is a slight decline in performance as time goes on, what happens if you double the length of the test? Thanks for doing the testing! Matthew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [Testperf-general] pg_autovacuum w/ dbt2
Yeah, same hardware and database configuration. No manual vacuum commands before. The decline in performance has been pretty consistent in all my previous tests and people have told me on many occasions that the decline in performance was probably because I was never using vacuum... Mark On Fri, Jan 07, 2005 at 08:48:52AM -0500, Matthew T. O'Connor wrote: > I'm curious, the original run you posted with 3825 NOTPM is still 17% > faster than the latest pg_autovacuum run which shows 3280 NOTPM. Is > this on the same hardware? Also, did the original non-pg_autovacuum run > any manual vacuum commands? Also, does the non-pg_autovacuum run start > slowing down after a while? The graphs look like there is a slight > decline in performance as time goes on, what happens if you double the > length of the test? > > Thanks for doing the testing! > > Matthew > > > Mark Wong wrote: > > >I apologize for the significant delay, here's a link to results to a > >test with 8.0rc3: > > http://www.osdl.org/projects/dbt2dev/results/dev4-010/236/ > > > >These are the same parameters with as run 215, listed below with the > >but with --enable-debug --enable-cassert. I also ran pg_autovacuum > >with -d4, where the output can be seen here: > > http://www.osdl.org/projects/dbt2dev/results/dev4-010/236/dbt2.out > > > >I, uh, wasn't able to reproduce the previous errors after repairing my > >filesystems after a power outage. So I figure that might be good news. > >The performance is up from run 215 with the errors, so I'll continue > >with trying to tune some of the pg_autovacuum values. > > > >Mark > > > > > >On Tue, Dec 21, 2004 at 09:41:31AM -0800, Mark Wong wrote: > > > > > >>After all this time I finally got around to vacuuming the database > >>with dbt2 with pg_autovacuum. :) > >>http://www.osdl.org/projects/dbt2dev/results/dev4-010/215/ > >> > >>Doesn't look so good though, probably because I'm not using optimal > >>settings with pg_autovacuum. So far I have only tried the default > >>settings (running without any arguments, except -D). > >> > >>The only thing that's peculiar is a number of unexpected rollbacks > >>across all of the transactions. I suspect it was something to do with > >>these messages coming from pg_autovacuum: > >> > >>[2004-12-20 15:48:18 PST] ERROR: Can not refresh statistics information > >>from the database dbt2. > >>[2004-12-20 15:48:18 PST] The error is [ERROR: failed to re-find > >>parent key in "pk_district" > >>] > >> > >>This is with 8.0rc1. I can get rc2 installed since it just came out. > >>So let me know what I can try and what not. > >> > >>Mark > >> > >> > > > > > > -- Mark Wong - - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503) 906-1932 (office) (503) 626-2436 (fax) http://developer.osdl.org/markw/ ---(end of broadcast)--- TIP 3: 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] [Testperf-general] pg_autovacuum w/ dbt2
I'm curious, the original run you posted with 3825 NOTPM is still 17% faster than the latest pg_autovacuum run which shows 3280 NOTPM. Is this on the same hardware? Also, did the original non-pg_autovacuum run any manual vacuum commands? Also, does the non-pg_autovacuum run start slowing down after a while? The graphs look like there is a slight decline in performance as time goes on, what happens if you double the length of the test? Thanks for doing the testing! Matthew Mark Wong wrote: I apologize for the significant delay, here's a link to results to a test with 8.0rc3: http://www.osdl.org/projects/dbt2dev/results/dev4-010/236/ These are the same parameters with as run 215, listed below with the but with --enable-debug --enable-cassert. I also ran pg_autovacuum with -d4, where the output can be seen here: http://www.osdl.org/projects/dbt2dev/results/dev4-010/236/dbt2.out I, uh, wasn't able to reproduce the previous errors after repairing my filesystems after a power outage. So I figure that might be good news. The performance is up from run 215 with the errors, so I'll continue with trying to tune some of the pg_autovacuum values. Mark On Tue, Dec 21, 2004 at 09:41:31AM -0800, Mark Wong wrote: After all this time I finally got around to vacuuming the database with dbt2 with pg_autovacuum. :) http://www.osdl.org/projects/dbt2dev/results/dev4-010/215/ Doesn't look so good though, probably because I'm not using optimal settings with pg_autovacuum. So far I have only tried the default settings (running without any arguments, except -D). The only thing that's peculiar is a number of unexpected rollbacks across all of the transactions. I suspect it was something to do with these messages coming from pg_autovacuum: [2004-12-20 15:48:18 PST] ERROR: Can not refresh statistics information from the database dbt2. [2004-12-20 15:48:18 PST] The error is [ERROR: failed to re-find parent key in "pk_district" ] This is with 8.0rc1. I can get rc2 installed since it just came out. So let me know what I can try and what not. Mark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [Testperf-general] pg_autovacuum w/ dbt2
I apologize for the significant delay, here's a link to results to a test with 8.0rc3: http://www.osdl.org/projects/dbt2dev/results/dev4-010/236/ These are the same parameters with as run 215, listed below with the but with --enable-debug --enable-cassert. I also ran pg_autovacuum with -d4, where the output can be seen here: http://www.osdl.org/projects/dbt2dev/results/dev4-010/236/dbt2.out I, uh, wasn't able to reproduce the previous errors after repairing my filesystems after a power outage. So I figure that might be good news. The performance is up from run 215 with the errors, so I'll continue with trying to tune some of the pg_autovacuum values. Mark On Tue, Dec 21, 2004 at 09:41:31AM -0800, Mark Wong wrote: > After all this time I finally got around to vacuuming the database > with dbt2 with pg_autovacuum. :) > http://www.osdl.org/projects/dbt2dev/results/dev4-010/215/ > > Doesn't look so good though, probably because I'm not using optimal > settings with pg_autovacuum. So far I have only tried the default > settings (running without any arguments, except -D). > > The only thing that's peculiar is a number of unexpected rollbacks > across all of the transactions. I suspect it was something to do with > these messages coming from pg_autovacuum: > > [2004-12-20 15:48:18 PST] ERROR: Can not refresh statistics information > from the database dbt2. > [2004-12-20 15:48:18 PST] The error is [ERROR: failed to re-find > parent key in "pk_district" > ] > > This is with 8.0rc1. I can get rc2 installed since it just came out. > So let me know what I can try and what not. > > Mark ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
