Re: [HACKERS] Displaying accumulated autovacuum cost

2012-02-22 Thread Robert Haas
On Tue, Feb 21, 2012 at 11:55 PM, Greg Smith g...@2ndquadrant.com wrote: I just took this for spin.  Everything I tried worked, docs built and read fine.  The description of how dirty differs from written is a bit cryptic, but I don't see an easy way to do better without a whole new section on

Re: [HACKERS] Displaying accumulated autovacuum cost

2012-02-21 Thread Dimitri Fontaine
Fujii Masao masao.fu...@gmail.com writes: Though I'm not familiar with CREATE EXTENSION. Why did you exclude 1.0.sql from DATA? In hstore/Makefile, 1.0.sql is included. You think we should prevent old version (i.e., 1.0) of pg_stat_statements from being used in 9.2? I'm not sure.  My

Re: [HACKERS] Displaying accumulated autovacuum cost

2012-02-21 Thread Greg Smith
On 02/21/2012 04:44 PM, Dimitri Fontaine wrote: The solution would be to be able to create hstore 1.1 from 1.0 automatically and I sent over a very simple patch to do that, albeit after the deadline for the current CF (that's why it's not listed). Maybe that's simple enough to be considered?

Re: [HACKERS] Displaying accumulated autovacuum cost

2012-02-21 Thread Greg Smith
I just took this for spin. Everything I tried worked, docs built and read fine. The description of how dirty differs from written is a bit cryptic, but I don't see an easy way to do better without a whole new section on that topic. Once the extension upgrade questions are sorted out, I'd

Re: [HACKERS] Displaying accumulated autovacuum cost

2012-02-20 Thread Robert Haas
On Mon, Feb 20, 2012 at 2:49 AM, Fujii Masao masao.fu...@gmail.com wrote: In pg_stat_statements--1.0--1.1.sql, we should complain if script is sourced in psql, as follows?    \echo Use ALTER EXTENSION pg_stat_statements UPDATE TO '1.1' to load this file. \quit Yeah, maybe. I don't know if

Re: [HACKERS] Displaying accumulated autovacuum cost

2012-02-20 Thread Fujii Masao
On Mon, Feb 20, 2012 at 11:00 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Feb 20, 2012 at 2:49 AM, Fujii Masao masao.fu...@gmail.com wrote: +DATA = pg_stat_statements--1.1.sql pg_stat_statements--1.0--1.1.sql \ +       pg_stat_statements--unpackaged--1.0.sql Though I'm not familiar

Re: [HACKERS] Displaying accumulated autovacuum cost

2012-02-19 Thread Fujii Masao
On Sat, Feb 18, 2012 at 2:16 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Feb 17, 2012 at 5:04 AM, Fujii Masao masao.fu...@gmail.com wrote: Here are review comments: The document about EXPLAIN needs to be updated. You forgot to add the long-integer-valued property of

Re: [HACKERS] Displaying accumulated autovacuum cost

2012-02-17 Thread Fujii Masao
On Sat, Nov 26, 2011 at 10:10 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Nov 25, 2011 at 11:39 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: I'm going to push this now anyway, thanks. This patch adds a count of the number of buffers dirtied to VACUUM, but it strikes me that

Re: [HACKERS] Displaying accumulated autovacuum cost

2012-02-17 Thread Robert Haas
On Fri, Feb 17, 2012 at 5:04 AM, Fujii Masao masao.fu...@gmail.com wrote: Here are review comments: The document about EXPLAIN needs to be updated. You forgot to add the long-integer-valued property of shared/local_blks_dirtied. So when I ran EXPLAIN and used json as a format, no

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-11-28 Thread Greg Smith
On 11/25/2011 08:39 AM, Alvaro Herrera wrote: I was about to commit this when I noticed that the avg values may not be all that representative of reality after all; consider that it's computed across the whole duration of the vacuuming operation, including the index scans ... it'd be possibly

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-11-28 Thread Greg Smith
On 11/25/2011 05:10 PM, Robert Haas wrote: This patch adds a count of the number of buffers dirtied to VACUUM, but it strikes me that it would be useful to add similar tracking to pgBufferUsage. Attached is a patch for that. You can see the new counters through pg_stat_statements or with

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-11-25 Thread Alvaro Herrera
Excerpts from Greg Smith's message of mié nov 16 04:37:43 -0300 2011: On 10/05/2011 03:02 AM, Greg Smith wrote: Presumably you meant to ask if this makes sense to show when cost accounting isn't enabled, because the code doesn't do that right now. No cost accounting, no buffer

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-11-25 Thread Robert Haas
On Fri, Nov 25, 2011 at 11:39 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: I'm going to push this now anyway, thanks. This patch adds a count of the number of buffers dirtied to VACUUM, but it strikes me that it would be useful to add similar tracking to pgBufferUsage. Attached is a

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-11-15 Thread Greg Smith
On 10/05/2011 03:02 AM, Greg Smith wrote: Presumably you meant to ask if this makes sense to show when cost accounting isn't enabled, because the code doesn't do that right now. No cost accounting, no buffer usage/write rate data as this was submitted. This is done in the attached update.

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-10-05 Thread Greg Smith
On 09/26/2011 05:58 AM, Shigeru Hanada wrote: * Local variables added by the patch (secs, usecs, write_rate and endtime) can be moved into narrower scope. * Initializing starttime to zero seems unnecessary. Setting starttime to 0 is already in the code; the change made to that line was to

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-10-05 Thread Greg Smith
On 09/29/2011 10:40 AM, Alvaro Herrera wrote: I reviewed this patch. My question for you is: does it make sense to enable to reporting of write rate even when vacuum cost accounting is enabled? In my opinion it would be useful to do so. If you agree, please submit an updated patch.

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-10-05 Thread Alvaro Herrera
Excerpts from Greg Smith's message of mié oct 05 04:02:12 -0300 2011: On 09/29/2011 10:40 AM, Alvaro Herrera wrote: I reviewed this patch. My question for you is: does it make sense to enable to reporting of write rate even when vacuum cost accounting is enabled? In my opinion it would

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-09-29 Thread Alvaro Herrera
I reviewed this patch. My question for you is: does it make sense to enable to reporting of write rate even when vacuum cost accounting is enabled? In my opinion it would be useful to do so. If you agree, please submit an updated patch. -- Álvaro Herrera alvhe...@commandprompt.com The

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-09-26 Thread Shigeru Hanada
Hi Greg, (2011/08/28 18:54), Greg Smith wrote: Updated patch cleans up two diff mistakes made when backing out the progress report feature. The tip-off I screwed up should have been the absurdly high write rate shown. The usleep was accidentally deleted, so it was running without cost

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-29 Thread Robert Haas
On Sun, Aug 28, 2011 at 5:54 AM, Greg Smith g...@2ndquadrant.com wrote: Updated patch cleans up two diff mistakes made when backing out the progress report feature.  The tip-off I screwed up should have been the absurdly high write rate shown.  The usleep was accidentally deleted, so it was

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-29 Thread Greg Smith
On 08/29/2011 11:03 AM, Robert Haas wrote: Instead of doing this only when vacuum costing is active, could we drive it off of the pgBufferUsage stuff (maybe with a few tweaks...) and do it unconditionally? Sure. I've wondered about an ever larger refactoring, to reorient vacuum costing

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-28 Thread Greg Smith
Attached patch includes math is hard reworking, so it displays the average write rate in the log output automatically: LOG: automatic vacuum of table pgbench.public.pgbench_accounts: index scans: 1 pages: 0 removed, 163935 remain tuples: 200 removed, 4625165 remain buffer

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-28 Thread Greg Smith
Updated patch cleans up two diff mistakes made when backing out the progress report feature. The tip-off I screwed up should have been the absurdly high write rate shown. The usleep was accidentally deleted, so it was running without cost limits even applying. Here's a good one instead:

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-22 Thread Jim Nasby
On Aug 18, 2011, at 10:41 AM, Greg Smith wrote: that was all they got. I'm going to add directly computing the write MB/s figure from the dirty data written too, since that ends up being the thing that I keep deriving by hand anyway. I know folks have talked about progress, but I haven't

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-22 Thread Greg Smith
On 08/22/2011 05:54 PM, Jim Nasby wrote: I know folks have talked about progress, but I haven't seen anything specific... could you add info about what table/index vacuum is working on, and how far along it is? I realize that's not very close to an actual % completion, but it's far better than

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Magnus Hagander
On Thu, Aug 18, 2011 at 03:23, Greg Smith g...@2ndquadrant.com wrote: On 08/17/2011 07:42 PM, Euler Taveira de Oliveira wrote: I don't like exposing this information only on title processes. It would be difficult for client apps (for example, PGAdmin) to track this kind of information and it

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Dimitri Fontaine
Magnus Hagander mag...@hagander.net writes: Also, unrelated to that, wouldn't this information be interesting for non-autovacuum queries as well? I was about to say that I would like to see it for normal queries too, but I guess we already have it: = explain (analyze, buffers, costs off)

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Euler Taveira de Oliveira
Em 18-08-2011 03:39, Magnus Hagander escreveu: Also, unrelated to that, wouldn't this information be interesting for non-autovacuum queries as well? Yes, it would. AFAICS, the patch will display that message in process titles. However, analyze code also uses the vacuum_delay_point(). How do

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes: On Thu, Aug 18, 2011 at 03:23, Greg Smith g...@2ndquadrant.com wrote: On 08/17/2011 07:42 PM, Euler Taveira de Oliveira wrote: I don't like exposing this information only on title processes. I tend to build the simplest possible thing that is useful

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Robert Haas
On Wed, Aug 17, 2011 at 9:23 PM, Greg Smith g...@2ndquadrant.com wrote: On 08/17/2011 07:42 PM, Euler Taveira de Oliveira wrote: I don't like exposing this information only on title processes. It would be difficult for client apps (for example, PGAdmin) to track this kind of information and

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Peter Eisentraut
On tor, 2011-08-18 at 08:39 +0200, Magnus Hagander wrote: Also, unrelated to that, wouldn't this information be interesting for non-autovacuum queries as well? Last year we were discussing some details on progress reporting, and some people suggested that instead of printing a single

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Robert Haas
On Thu, Aug 18, 2011 at 10:54 AM, Peter Eisentraut pete...@gmx.net wrote: On tor, 2011-08-18 at 08:39 +0200, Magnus Hagander wrote: Also, unrelated to that, wouldn't this information be interesting for non-autovacuum queries as well? Last year we were discussing some details on progress

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Magnus Hagander
On Thu, Aug 18, 2011 at 17:13, Robert Haas robertmh...@gmail.com wrote: On Thu, Aug 18, 2011 at 10:54 AM, Peter Eisentraut pete...@gmx.net wrote: On tor, 2011-08-18 at 08:39 +0200, Magnus Hagander wrote: Also, unrelated to that, wouldn't this information be interesting for non-autovacuum

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Robert Haas
On Thu, Aug 18, 2011 at 11:14 AM, Magnus Hagander mag...@hagander.net wrote: On Thu, Aug 18, 2011 at 17:13, Robert Haas robertmh...@gmail.com wrote: On Thu, Aug 18, 2011 at 10:54 AM, Peter Eisentraut pete...@gmx.net wrote: On tor, 2011-08-18 at 08:39 +0200, Magnus Hagander wrote: Also,

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Magnus Hagander
On Thu, Aug 18, 2011 at 17:23, Robert Haas robertmh...@gmail.com wrote: On Thu, Aug 18, 2011 at 11:14 AM, Magnus Hagander mag...@hagander.net wrote: On Thu, Aug 18, 2011 at 17:13, Robert Haas robertmh...@gmail.com wrote: On Thu, Aug 18, 2011 at 10:54 AM, Peter Eisentraut pete...@gmx.net wrote:

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Greg Smith
On 08/18/2011 10:12 AM, Robert Haas wrote: Perhaps a reasonable way to break up the patch would be: - Part 1: Gather the information and display it in the log_autovacuum_min_duration output. - Part 2: Add the ability to see the information incrementally (via some mechanism yet to be agreed

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Greg Smith
On 08/18/2011 10:54 AM, Peter Eisentraut wrote: So how about adding a column to pg_stat_activity, progress_metrics or something like that, and add that information there. Adding a field here (I'd go for the simpler progress) and updating it regularly would be a reasonable way to go here.

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Magnus Hagander
On Thu, Aug 18, 2011 at 17:54, Greg Smith g...@2ndquadrant.com wrote: On 08/18/2011 10:54 AM, Peter Eisentraut wrote: So how about adding a column to pg_stat_activity, progress_metrics or something like that, and add that information there. Adding a field here (I'd go for the simpler

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Robert Haas
On Thu, Aug 18, 2011 at 11:41 AM, Greg Smith g...@2ndquadrant.com wrote: On 08/18/2011 10:12 AM, Robert Haas wrote: Perhaps a reasonable way to break up the patch would be: - Part 1: Gather the information and display it in the log_autovacuum_min_duration output. - Part 2: Add the ability to

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-18 Thread Euler Taveira de Oliveira
Em 18-08-2011 12:54, Greg Smith escreveu: I was hoping to eventually take the useful summary bits at the end, the totals, and save those into statistics somewhere each time a VACUUM of either sort finishes. It would fit with the information shown in pg_stat_tables, but that's obviously getting

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-17 Thread Euler Taveira de Oliveira
Em 17-08-2011 18:04, Greg Smith escreveu: Attached is a patch that tracks and displays the accumulated cost when autovacuum is running. Code by Noah Misch and myself. I hope this idea will bring a formal process to vacuum tuning, which is currently too hard to do. I was about to add without...

Re: [HACKERS] Displaying accumulated autovacuum cost

2011-08-17 Thread Greg Smith
On 08/17/2011 07:42 PM, Euler Taveira de Oliveira wrote: I don't like exposing this information only on title processes. It would be difficult for client apps (for example, PGAdmin) to track this kind of information and it is restricted to local access. I'm not objecting to display this