Jeff Janes escribió:
> On Mon, Feb 18, 2013 at 7:50 AM, Alvaro Herrera
> 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
Jeff Janes wrote:
> Alvaro Herrera 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: variable 'found' set but not u
On Mon, Feb 18, 2013 at 7:50 AM, Alvaro Herrera
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: variable 'found' set but not
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
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 12m
On 19.2.2013 11:27, Tom Lane wrote:
> Tomas Vondra writes:
>> Dne 19.02.2013 05:46, Alvaro Herrera napsal:
>>> Mastodon failed:
>>> http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodon&dt=2013-02-19%2000%3A00%3A01
>>>
>>> probably worth investigating a bit; we might have broken somethin
Dne 19.02.2013 11:27, Tom Lane napsal:
Tomas Vondra writes:
Dne 19.02.2013 05:46, Alvaro Herrera napsal:
Mastodon failed:
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodon&dt=2013-02-19%2000%3A00%3A01
probably worth investigating a bit; we might have broken something.
Hmmm,
Tomas Vondra writes:
> Dne 19.02.2013 05:46, Alvaro Herrera napsal:
>> Mastodon failed:
>> http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodon&dt=2013-02-19%2000%3A00%3A01
>>
>> probably worth investigating a bit; we might have broken something.
> Hmmm, interesting. A single Windows
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=mastodon&dt=2013-02-19%2000%3A00%3A01
probably worth investigating a bit; we might
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=mastodon&dt=2013-02-19%2000%3A00%3A01
probably worth investigating a bit; we might have broken something.
--
Álvaro 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 i
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() fu
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
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 inquir
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
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 thi
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, an
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 p
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 & Servi
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
>> committabl
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
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 w
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
>
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
>
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
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 Exp
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():
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 c
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 st
On Tue, Feb 5, 2013 at 2:31 PM, Tomas Vondra 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 could
On 7.2.2013 00:40, Tom Lane wrote:
> Jeff Janes writes:
>> On Tue, Feb 5, 2013 at 2:31 PM, Tomas Vondra 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 i
Jeff Janes writes:
> On Tue, Feb 5, 2013 at 2:31 PM, Tomas Vondra 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 catalog changes, but I th
On Tue, Feb 5, 2013 at 2:31 PM, Tomas Vondra 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 i
Dne 06.02.2013 16:53, Alvaro Herrera napsal:
Tom Lane escribió:
Pavel Stehule 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 l
2013/2/6 Alvaro Herrera :
> Tom Lane escribió:
>> Pavel Stehule 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 w
Tom Lane escribió:
> Pavel Stehule 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
>
Pavel Stehule 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 have move stat f
>>
>> 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)
On 5.2.2013 19:23, Jeff Janes wrote:
> On Sun, Feb 3, 2013 at 4:51 PM, Tomas Vondra 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:
On Sun, Feb 3, 2013 at 4:51 PM, Tomas Vondra 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
> collector's tim
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 @@
>>
>>
On 3.2.2013 21:54, Jeff Janes wrote:
> On Sat, Feb 2, 2013 at 2:33 PM, Jeff Janes wrote:
>> On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra 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"
On 2.2.2013 23:33, Jeff Janes wrote:
> On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra 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: wa
On 3.2.2013 20:46, Jeff Janes wrote:
> On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra 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
On Sat, Feb 2, 2013 at 2:33 PM, Jeff Janes wrote:
> On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra 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:
>
> pgsta
On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra 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 alre
ql.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 wrote:
> On 3.1.2013 20:33, Magnus Hagander wrote:
>>
>> Yeah, +1 for a separate directory not in global.
>
> OK, I m
On Sat, Jan 5, 2013 at 8:03 PM, Tomas Vondra 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 with
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 (rela
On 3.1.2013 20:33, Magnus Hagander wrote:
> On Thu, Jan 3, 2013 at 8:31 PM, Tomas Vondra 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
On Thu, Jan 3, 2013 at 8:31 PM, Tomas Vondra 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,
>>> bu
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 crea
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 store
Hi,
attached is a version of the patch that I believe is ready for the
commitfest. As the patch was discussed over a large number of messages,
I've prepared a brief summary for those who did not follow the thread.
Issue
=
The patch aims to improve the situation in deployments with many tabl
On 21.11.2012 19:02, Robert Haas wrote:
> On Sun, Nov 18, 2012 at 5:49 PM, Tomas Vondra 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
On Sun, Nov 18, 2012 at 5:49 PM, Tomas Vondra 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 files h
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 collecto
Hi!
On 26.9.2012 19:18, Jeff Janes wrote:
> On Wed, Sep 26, 2012 at 9:29 AM, Tom Lane wrote:
>> Alvaro Herrera 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
On 27 September 2012 15:57, Alvaro Herrera wrote:
> Excerpts from Simon Riggs's message of jue sep 27 06:51:28 -0300 2012:
>> On 26 September 2012 15:47, Alvaro Herrera wrote:
>> >
>> > Really, as far as autovacuum is concerned, it would be much more useful
>> > to be able to reliably detect that
Excerpts from Simon Riggs's message of jue sep 27 06:51:28 -0300 2012:
> On 26 September 2012 15:47, Alvaro Herrera 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 t
On 26 September 2012 15:47, Alvaro Herrera 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
> greatly reduce the amount
On 26.9.2012 18:14, Jeff Janes wrote:
> On Wed, Sep 26, 2012 at 8:25 AM, Tomas Vondra 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
>>> secon
On 26.9.2012 18:29, Tom Lane wrote:
> Alvaro Herrera 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 t
On Wed, Sep 26, 2012 at 9:29 AM, Tom Lane wrote:
> Alvaro Herrera 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 separate
Alvaro Herrera 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 don't need to read a
On Wed, Sep 26, 2012 at 8:25 AM, Tomas Vondra 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 process, t
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 getti
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
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
Dne 26.09.2012 16:51, Jeff Janes napsal:
On Wed, Sep 26, 2012 at 5:43 AM, Tomas Vondra 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 ca
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
On Wed, Sep 26, 2012 at 5:43 AM, Tomas Vondra 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 application - ye
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 Herre
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
74 matches
Mail list logo
