Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-20 Thread Kevin Grittner
Jeff Janes jeff.ja...@gmail.com wrote: Alvaro Herrera alvhe...@2ndquadrant.com wrote: So here's v11.  I intend to commit this shortly.  (I wanted to get it out before lunch, but I introduced a silly bug that took me a bit to fix.) On Windows with Mingw I get this: pgstat.c:4389:8:

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-20 Thread Alvaro Herrera
Jeff Janes escribió: On Mon, Feb 18, 2013 at 7:50 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: So here's v11. I intend to commit this shortly. (I wanted to get it out before lunch, but I introduced a silly bug that took me a bit to fix.) On Windows with Mingw I get this:

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-19 Thread Tomas Vondra
Dne 19.02.2013 05:46, Alvaro Herrera napsal: Alvaro Herrera wrote: I have pushed it now. Further testing, of course, is always welcome. Mastodon failed: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodondt=2013-02-19%2000%3A00%3A01 probably worth investigating a bit; we might

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-19 Thread Tom Lane
Tomas Vondra t...@fuzzy.cz writes: Dne 19.02.2013 05:46, Alvaro Herrera napsal: Mastodon failed: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodondt=2013-02-19%2000%3A00%3A01 probably worth investigating a bit; we might have broken something. Hmmm, interesting. A single

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-19 Thread Tomas Vondra
Dne 19.02.2013 11:27, Tom Lane napsal: Tomas Vondra t...@fuzzy.cz writes: Dne 19.02.2013 05:46, Alvaro Herrera napsal: Mastodon failed: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodondt=2013-02-19%2000%3A00%3A01 probably worth investigating a bit; we might have broken

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-19 Thread Tomas Vondra
On 19.2.2013 11:27, Tom Lane wrote: Tomas Vondra t...@fuzzy.cz writes: Dne 19.02.2013 05:46, Alvaro Herrera napsal: Mastodon failed: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodondt=2013-02-19%2000%3A00%3A01 probably worth investigating a bit; we might have broken something.

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-19 Thread Alvaro Herrera
Tomas Vondra wrote: AFAIK the stats remain the same within a transaction, and as a function runs within a transaction, it will either get new data on the first iteration, or it will run all 300 of them. I've checked several buildfarm members and I'm yet to see a single duration between 12ms

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-19 Thread Tomas Vondra
On 19.2.2013 23:31, Alvaro Herrera wrote: Tomas Vondra wrote: AFAIK the stats remain the same within a transaction, and as a function runs within a transaction, it will either get new data on the first iteration, or it will run all 300 of them. I've checked several buildfarm members and I'm

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-19 Thread Jeff Janes
On Mon, Feb 18, 2013 at 7:50 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: So here's v11. I intend to commit this shortly. (I wanted to get it out before lunch, but I introduced a silly bug that took me a bit to fix.) On Windows with Mingw I get this: pgstat.c:4389:8: warning:

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-18 Thread Alvaro Herrera
Tomas Vondra wrote: So, here's v10 of the patch (based on the v9+v9a), that implements the approach described above. It turned out to be much easier than I expected (basically just a rewrite of the pgstat_read_db_statsfile_timestamp() function. Thanks. I'm giving this another look now. I

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-18 Thread Tomas Vondra
On 18.2.2013 16:50, Alvaro Herrera wrote: Tomas Vondra wrote: So, here's v10 of the patch (based on the v9+v9a), that implements the approach described above. It turned out to be much easier than I expected (basically just a rewrite of the pgstat_read_db_statsfile_timestamp() function.

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-18 Thread Alvaro Herrera
Tomas Vondra wrote: On 18.2.2013 16:50, Alvaro Herrera wrote: Also, it seems to me that the new pgstat_db_requested() logic is slightly bogus (in the inefficient sense, not the incorrect sense): we should be comparing the timestamp of the request vs. what's already on disk instead of

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-18 Thread Alvaro Herrera
Alvaro Herrera wrote: I have pushed it now. Further testing, of course, is always welcome. Mastodon failed: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodondt=2013-02-19%2000%3A00%3A01 probably worth investigating a bit; we might have broken something. -- Álvaro Herrera

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-17 Thread Tomas Vondra
On 17.2.2013 06:46, Alvaro Herrera wrote: Tomas Vondra wrote: I've been thinking about this (actually I had a really weird dream about it this night) and I think it might work like this: (1) check the timestamp of the global file - if it's too old, we need to send an inquiry or wait a

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-16 Thread Tomas Vondra
On 15.2.2013 01:02, Tomas Vondra wrote: On 14.2.2013 22:24, Alvaro Herrera wrote: Alvaro Herrera escribió: Here's a ninth version of this patch. (version 8 went unpublished). I have simplified a lot of things and improved some comments; I think I understand much of it now. I think this

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-16 Thread Alvaro Herrera
Tomas Vondra wrote: I've been thinking about this (actually I had a really weird dream about it this night) and I think it might work like this: (1) check the timestamp of the global file - if it's too old, we need to send an inquiry or wait a bit longer (2) if it's new enough, we

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-15 Thread Alvaro Herrera
Tomas Vondra escribió: On 14.2.2013 20:23, Alvaro Herrera wrote: The problem here is that creating these dummy entries will cause a difference in autovacuum behavior. Autovacuum will skip processing databases with no pgstat entry, and the intended reason is that if there's no pgstat

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-15 Thread Tomas Vondra
On 15.2.2013 16:38, Alvaro Herrera wrote: Tomas Vondra escribió: On 14.2.2013 20:23, Alvaro Herrera wrote: The problem here is that creating these dummy entries will cause a difference in autovacuum behavior. Autovacuum will skip processing databases with no pgstat entry, and the

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-14 Thread Alvaro Herrera
Alvaro Herrera escribió: Hm, and I now also realize another bug in this patch: the global stats only include database entries for requested databases; but perhaps the existing files can serve later requestors just fine for databases that already had files; so the global stats file should

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-14 Thread Alvaro Herrera
Here's a ninth version of this patch. (version 8 went unpublished). I have simplified a lot of things and improved some comments; I think I understand much of it now. I think this patch is fairly close to committable, but one issue remains, which is this bit in pgstat_write_statsfiles():

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-14 Thread Josh Berkus
I saw discussion about this on this thread, but I'm not able to figure out what the answer is: how does this work with moving the stats file, for example to a RAMdisk? Specifically, if the user sets stats_temp_directory, does it continue to work the way it does now? -- Josh Berkus PostgreSQL

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-14 Thread Alvaro Herrera
Josh Berkus wrote: I saw discussion about this on this thread, but I'm not able to figure out what the answer is: how does this work with moving the stats file, for example to a RAMdisk? Specifically, if the user sets stats_temp_directory, does it continue to work the way it does now? Of

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-14 Thread Alvaro Herrera
Alvaro Herrera escribió: Here's a ninth version of this patch. (version 8 went unpublished). I have simplified a lot of things and improved some comments; I think I understand much of it now. I think this patch is fairly close to committable, but one issue remains, which is this bit in

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-14 Thread Alvaro Herrera
Alvaro Herrera escribió: Here's a ninth version of this patch. (version 8 went unpublished). I have simplified a lot of things and improved some comments; I think I understand much of it now. I think this patch is fairly close to committable, but one issue remains, which is this bit in

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-14 Thread Tomas Vondra
On 14.2.2013 20:43, Josh Berkus wrote: I saw discussion about this on this thread, but I'm not able to figure out what the answer is: how does this work with moving the stats file, for example to a RAMdisk? Specifically, if the user sets stats_temp_directory, does it continue to work the way

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-14 Thread Tomas Vondra
First of all, big thanks for working on this patch and not only identifying the issues but actually fixing them. On 14.2.2013 20:23, Alvaro Herrera wrote: Here's a ninth version of this patch. (version 8 went unpublished). I have simplified a lot of things and improved some comments; I think

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-14 Thread Tomas Vondra
On 14.2.2013 22:24, Alvaro Herrera wrote: Alvaro Herrera escribió: Here's a ninth version of this patch. (version 8 went unpublished). I have simplified a lot of things and improved some comments; I think I understand much of it now. I think this patch is fairly close to committable, but

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-14 Thread Alvaro Herrera
Tomas Vondra escribió: I don't see how that changes the autovacuum behavior. Can you explain that a bit more? It might be that I'm all wet on this. I'll poke at it some more. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-13 Thread Alvaro Herrera
Here's an updated version of this patch that takes care of the issues I reported previously: no more repalloc() of the requests array; it's now an slist, which makes the code much more natural IMV. And no more messing around with doing sprintf to create a separate sprintf pattern for the per-db

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-09 Thread Jeff Janes
On Tue, Feb 5, 2013 at 2:31 PM, Tomas Vondra t...@fuzzy.cz wrote: We do not already have this. There is no relevant spec. I can't see how this could need pg_dump support (but what about pg_upgrade?) pg_dump - no pg_upgrage - IMHO it should create the pg_stat directory. I don't think it

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-06 Thread Tom Lane
Pavel Stehule pavel.steh...@gmail.com writes: Nice. Another interesting numbers would be device utilization, average I/O speed and required space (which should be ~2x the pgstat.stat size without the patch). this point is important - with large warehouse with lot of databases and tables you

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-06 Thread Alvaro Herrera
Tom Lane escribió: Pavel Stehule pavel.steh...@gmail.com writes: Nice. Another interesting numbers would be device utilization, average I/O speed and required space (which should be ~2x the pgstat.stat size without the patch). this point is important - with large warehouse with lot of

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-06 Thread Pavel Stehule
2013/2/6 Alvaro Herrera alvhe...@2ndquadrant.com: Tom Lane escribió: Pavel Stehule pavel.steh...@gmail.com writes: Nice. Another interesting numbers would be device utilization, average I/O speed and required space (which should be ~2x the pgstat.stat size without the patch). this point

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-06 Thread Tomas Vondra
Dne 06.02.2013 16:53, Alvaro Herrera napsal: Tom Lane escribió: Pavel Stehule pavel.steh...@gmail.com writes: Nice. Another interesting numbers would be device utilization, average I/O speed and required space (which should be ~2x the pgstat.stat size without the patch). this point is

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-06 Thread Jeff Janes
On Tue, Feb 5, 2013 at 2:31 PM, Tomas Vondra t...@fuzzy.cz wrote: On 5.2.2013 19:23, Jeff Janes wrote: If I shutdown the server and blow away the stats with rm data/pg_stat/*, it recovers gracefully when I start it back up. If a do rm -r data/pg_stat then it has problems the next time I shut

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-06 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes: On Tue, Feb 5, 2013 at 2:31 PM, Tomas Vondra t...@fuzzy.cz wrote: U, what you mean by catalog bump? There is a catalog number in src/include/catalog/catversion.h, which when changed forces one to redo initdb. Formally I guess it is only for system

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-06 Thread Tomas Vondra
On 7.2.2013 00:40, Tom Lane wrote: Jeff Janes jeff.ja...@gmail.com writes: On Tue, Feb 5, 2013 at 2:31 PM, Tomas Vondra t...@fuzzy.cz wrote: U, what you mean by catalog bump? There is a catalog number in src/include/catalog/catversion.h, which when changed forces one to redo initdb.

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-05 Thread Jeff Janes
On Sun, Feb 3, 2013 at 4:51 PM, Tomas Vondra t...@fuzzy.cz wrote: LOG: last_statwrite 11133-08-28 19:22:31.711744+02 is later than collector's time 2013-02-04 00:54:21.113439+01 for db 19093 WARNING: pgstat wait timeout LOG: last_statwrite 39681-12-23 18:48:48.9093+01 is later than

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-05 Thread Tomas Vondra
On 5.2.2013 19:23, Jeff Janes wrote: On Sun, Feb 3, 2013 at 4:51 PM, Tomas Vondra t...@fuzzy.cz wrote: LOG: last_statwrite 11133-08-28 19:22:31.711744+02 is later than collector's time 2013-02-04 00:54:21.113439+01 for db 19093 WARNING: pgstat wait timeout LOG: last_statwrite 39681-12-23

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-05 Thread Pavel Stehule
with the patch: vacuumdb -areal6m41.306s idle steady state: 7.86% user, 5.00% system 0.09% iowait 87% idle, Nice. Another interesting numbers would be device utilization, average I/O speed and required space (which should be ~2x the pgstat.stat size without the patch). this

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-03 Thread Jeff Janes
On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra t...@fuzzy.cz wrote: On 3.1.2013 20:33, Magnus Hagander wrote: Yeah, +1 for a separate directory not in global. OK, I moved the files from global/stat to stat. Why stat rather than pg_stat? The existence of global and base as exceptions already

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-03 Thread Jeff Janes
On Sat, Feb 2, 2013 at 2:33 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra t...@fuzzy.cz wrote: On 3.1.2013 20:33, Magnus Hagander wrote: Yeah, +1 for a separate directory not in global. OK, I moved the files from global/stat to stat. This has a

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-03 Thread Tomas Vondra
On 3.2.2013 20:46, Jeff Janes wrote: On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra t...@fuzzy.cz wrote: On 3.1.2013 20:33, Magnus Hagander wrote: Yeah, +1 for a separate directory not in global. OK, I moved the files from global/stat to stat. Why stat rather than pg_stat? The existence

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-03 Thread Tomas Vondra
On 2.2.2013 23:33, Jeff Janes wrote: On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra t...@fuzzy.cz wrote: On 3.1.2013 20:33, Magnus Hagander wrote: Yeah, +1 for a separate directory not in global. OK, I moved the files from global/stat to stat. This has a warning: pgstat.c:5132: warning:

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-03 Thread Tomas Vondra
On 3.2.2013 21:54, Jeff Janes wrote: On Sat, Feb 2, 2013 at 2:33 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra t...@fuzzy.cz wrote: On 3.1.2013 20:33, Magnus Hagander wrote: Yeah, +1 for a separate directory not in global. OK, I moved the files from

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-03 Thread Tomas Vondra
On 1.2.2013 17:19, Alvaro Herrera wrote: Tomas Vondra wrote: diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index be3adf1..4ec485e 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -64,10 +64,14 @@ /* --

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-02 Thread Jeff Janes
On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra t...@fuzzy.cz wrote: On 3.1.2013 20:33, Magnus Hagander wrote: Yeah, +1 for a separate directory not in global. OK, I moved the files from global/stat to stat. This has a warning: pgstat.c:5132: warning: 'pgstat_write_statsfile_needed' was used

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-02 Thread Tomáš Vondra
: pgsql-hackers@postgresql.org Předmět: Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra t...@fuzzy.cz wrote: On 3.1.2013 20:33, Magnus Hagander wrote: Yeah, +1 for a separate directory not in global. OK, I

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-02-01 Thread Alvaro Herrera
Tomas Vondra wrote: diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index be3adf1..4ec485e 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -64,10 +64,14 @@ /* -- * Paths for the statistics files (relative to

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-01-05 Thread Tomas Vondra
On 3.1.2013 20:33, Magnus Hagander wrote: On Thu, Jan 3, 2013 at 8:31 PM, Tomas Vondra t...@fuzzy.cz wrote: On 3.1.2013 18:47, Heikki Linnakangas wrote: How about creating the new directory as a direct subdir of $PGDATA, rather than buried in global? global is supposed to contain data related

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-01-03 Thread Heikki Linnakangas
On 03.01.2013 01:15, Tomas Vondra wrote: 2) a new global/stat directory -- The pgstat.stat file was originally saved into the global directory, but with so many files that would get rather messy so I've created a new global/stat directory and all the files are stored

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-01-03 Thread Tomas Vondra
On 3.1.2013 18:47, Heikki Linnakangas wrote: On 03.01.2013 01:15, Tomas Vondra wrote: 2) a new global/stat directory -- The pgstat.stat file was originally saved into the global directory, but with so many files that would get rather messy so I've created a new

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

2013-01-03 Thread Magnus Hagander
On Thu, Jan 3, 2013 at 8:31 PM, Tomas Vondra t...@fuzzy.cz wrote: On 3.1.2013 18:47, Heikki Linnakangas wrote: On 03.01.2013 01:15, Tomas Vondra wrote: 2) a new global/stat directory -- The pgstat.stat file was originally saved into the global directory, but with

Re: [HACKERS] autovacuum stress-testing our system

2012-12-02 Thread Tomas Vondra
On 21.11.2012 19:02, Robert Haas wrote: On Sun, Nov 18, 2012 at 5:49 PM, Tomas Vondra t...@fuzzy.cz wrote: The two main changes are these: (1) The stats file is split into a common db file, containing all the DB Entries, and per-database files with tables/functions. The common file

Re: [HACKERS] autovacuum stress-testing our system

2012-11-21 Thread Robert Haas
On Sun, Nov 18, 2012 at 5:49 PM, Tomas Vondra t...@fuzzy.cz wrote: The two main changes are these: (1) The stats file is split into a common db file, containing all the DB Entries, and per-database files with tables/functions. The common file is still called pgstat.stat, the per-db

Re: [HACKERS] autovacuum stress-testing our system

2012-11-18 Thread Tomas Vondra
Hi! On 26.9.2012 19:18, Jeff Janes wrote: On Wed, Sep 26, 2012 at 9:29 AM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Excerpts from Euler Taveira's message of mié sep 26 11:53:27 -0300 2012: On 26-09-2012 09:43, Tomas Vondra wrote: 5) splitting the

Re: [HACKERS] autovacuum stress-testing our system

2012-11-18 Thread Tomas Vondra
On 26.9.2012 18:29, Tom Lane wrote: What seems to me like it could help more is fixing things so that the autovac launcher needn't even launch a child process for databases that haven't had any updates lately. I'm not sure how to do that, but it probably involves getting the stats collector

Re: [HACKERS] autovacuum stress-testing our system

2012-09-27 Thread Simon Riggs
On 26 September 2012 15:47, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Really, as far as autovacuum is concerned, it would be much more useful to be able to reliably detect that a table has been recently vacuumed, without having to request a 10ms-recent pgstat snapshot. That would

Re: [HACKERS] autovacuum stress-testing our system

2012-09-27 Thread Alvaro Herrera
Excerpts from Simon Riggs's message of jue sep 27 06:51:28 -0300 2012: On 26 September 2012 15:47, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Really, as far as autovacuum is concerned, it would be much more useful to be able to reliably detect that a table has been recently vacuumed,

Re: [HACKERS] autovacuum stress-testing our system

2012-09-27 Thread Simon Riggs
On 27 September 2012 15:57, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Excerpts from Simon Riggs's message of jue sep 27 06:51:28 -0300 2012: On 26 September 2012 15:47, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Really, as far as autovacuum is concerned, it would be much more useful

[HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Tomas Vondra
Hi, I've been struggling with autovacuum generating a lot of I/O and CPU on some of our systems - after a night spent analyzing this behavior, I believe the current autovacuum accidentally behaves a bit like a stress-test in some corner cases (but I may be seriously wrong, after all it was a

Re: [HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Alvaro Herrera
Really, as far as autovacuum is concerned, it would be much more useful to be able to reliably detect that a table has been recently vacuumed, without having to request a 10ms-recent pgstat snapshot. That would greatly reduce the amount of time autovac spends on pgstat requests. -- Álvaro

Re: [HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Jeff Janes
On Wed, Sep 26, 2012 at 5:43 AM, Tomas Vondra t...@fuzzy.cz wrote: First - our system really is not a common one - we do have ~1000 of databases of various size, each containing up to several thousands of tables (several user-defined tables, the rest serve as caches for a reporting

Re: [HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Euler Taveira
On 26-09-2012 09:43, Tomas Vondra wrote: I've been struggling with autovacuum generating a lot of I/O and CPU on some of our systems - after a night spent analyzing this behavior, I believe the current autovacuum accidentally behaves a bit like a stress-test in some corner cases (but I may

Re: [HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Tomas Vondra
Dne 26.09.2012 16:51, Jeff Janes napsal: On Wed, Sep 26, 2012 at 5:43 AM, Tomas Vondra t...@fuzzy.cz wrote: First - our system really is not a common one - we do have ~1000 of databases of various size, each containing up to several thousands of tables (several user-defined tables, the rest

Re: [HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Alvaro Herrera
Excerpts from Euler Taveira's message of mié sep 26 11:53:27 -0300 2012: On 26-09-2012 09:43, Tomas Vondra wrote: 5) splitting the single stat file into multiple pieces - e.g. per database, written separately, so that the autovacuum workers don't need to read all the data even for

Re: [HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Alvaro Herrera
Excerpts from Tomas Vondra's message of mié sep 26 12:25:58 -0300 2012: Dne 26.09.2012 16:51, Jeff Janes napsal: I think forking it off to to another value would be better. If you are an autovacuum worker which is just starting up and so getting its initial stats, you can tolerate a stats

Re: [HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Tomas Vondra
Dne 26.09.2012 17:29, Alvaro Herrera napsal: Excerpts from Tomas Vondra's message of mié sep 26 12:25:58 -0300 2012: Dne 26.09.2012 16:51, Jeff Janes napsal: I think forking it off to to another value would be better. If you are an autovacuum worker which is just starting up and so

Re: [HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Jeff Janes
On Wed, Sep 26, 2012 at 8:25 AM, Tomas Vondra t...@fuzzy.cz wrote: Dne 26.09.2012 16:51, Jeff Janes napsal: What is generating the endless stream you are seeing is that you have 1000 databases so if naptime is one minute you are vacuuming 16 per second. Since every database gets a new

Re: [HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Tom Lane
Alvaro Herrera alvhe...@2ndquadrant.com writes: Excerpts from Euler Taveira's message of mié sep 26 11:53:27 -0300 2012: On 26-09-2012 09:43, Tomas Vondra wrote: 5) splitting the single stat file into multiple pieces - e.g. per database, written separately, so that the autovacuum workers

Re: [HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Jeff Janes
On Wed, Sep 26, 2012 at 9:29 AM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Excerpts from Euler Taveira's message of mié sep 26 11:53:27 -0300 2012: On 26-09-2012 09:43, Tomas Vondra wrote: 5) splitting the single stat file into multiple pieces - e.g.

Re: [HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Tomas Vondra
On 26.9.2012 18:29, Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Excerpts from Euler Taveira's message of miĂŠ sep 26 11:53:27 -0300 2012: On 26-09-2012 09:43, Tomas Vondra wrote: 5) splitting the single stat file into multiple pieces - e.g. per database, written

Re: [HACKERS] autovacuum stress-testing our system

2012-09-26 Thread Tomas Vondra
On 26.9.2012 18:14, Jeff Janes wrote: On Wed, Sep 26, 2012 at 8:25 AM, Tomas Vondra t...@fuzzy.cz wrote: Dne 26.09.2012 16:51, Jeff Janes napsal: What is generating the endless stream you are seeing is that you have 1000 databases so if naptime is one minute you are vacuuming 16 per second.