Re: [Proposal] More Vacuum Statistics

Hello,

>Maybe, For DBAs,
>It might be better to show vacuum progress in pg_stat_activity.
>(if we'd do, add a free-style column like "progress" ?) This column might also be able to use for other long time commands like ANALYZE, CREATE/RE INDEX and COPY. To realize this feature, we certainly need to properly change pgstat_report_activity, use it more and add a new track-activity parameter
Very similar idea was proposed in the following
http://www.postgresql.org/message-id/1284756643.25048.42.camel@vanquo.pezone.net

IIUC, problem with showing progress in pg_stat_activity is that it introduces compulsary progress calculation overhead in core for every command.
As work units of each command varies, common infrastructure might not be able to represent every command progress effectively.
An architecture which will display progress only on users demand for each command separately will be more efficient.
So, suggestion was rather to have a detailed progress report including "remaining time" for a command on users demand.

FWIW, I am working on designing an approach to report VACUUM progress stats for which I will be posting a detailed proposal.
The use case is reporting progress for long running VACUUMs. The approach involves using hooks to extract VACUUM progress statistics .
The progress can be displayed using psql view (ex. pg_stat_maintenance).

Thank you,
Rahila Syed

-----Original Message-----
From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Naoya Anzai
Sent: Tuesday, June 16, 2015 8:41 AM
To: Tomas Vondra
Cc: pgsql-hackers(at)postgresql(dot)org; Akio Iwaasa; bench(dot)coffee(at)gmail(dot)com; Tom Lane; Jeff Janes; Jim Nasby; Andres Freund; Alvaro Herrera
Subject: Re: [HACKERS] [Proposal] More Vacuum Statistics

Hi,

Thank you for comments. and Sorry for my late response.

>> ====================
>> pg_stat_vacuum view
>> ====================
>>
>> I understand it is not good to simply add more counters in
>> pg_stat_*_tables. For now, I'd like to suggest an extension which can
>> confirm vacuum statistics like pg_stat_statements.
>>
>> Similar feature has been already provided by pg_statsinfo package.
>> But it is a full-stack package for PG-stats and it needs to redesign
>> pg_log and design a repository database for introduce.
>> And it is not a core-extension for PostgreSQL.
>> (I don't intend to hate pg_statsinfo,
>> I think this package is a very convinient tool)
>>
>> Everyone will be able to do more easily tuning of VACUUM.
>> That's all I want.
>
>I'm still wondering whether these stats will really make the tuning any
>easier. What I do right now is looking at pg_stat_all_tables.n_deat_tup
>and if it exceeds some threshold, it's a sign that vacuum may need a
>bit of tuning. Sometimes it really requires tuning vacuum itself, but
>more often than not it's due to something else (a large bulk delete,
>autovacuum getting stuck on another table, ...). I don't see how the
>new stats would make this any easier.
>
>Can you give some examples on how the new stats might be used (and
>where the current stats are insufficient)? What use cases do you
>imagine for those stats?

pg_stat_vacuum can keep histories of vacuum statistics for each tables/indices into shared memory.(They are not only last vacuum.
This is already able to confirm using pg_stat_all_tables.) It makes easier analysis of vacuum histories because this view can sort or aggregate or filter.

My use cases for those stats are following.

- examine TRANSITION of vacuum execution time on any table (you can predict the future vacuum execution time)
- examine EXECUTION INTERVAL of vacuum for each table (if too frequent, it should make vacuum-threshold tuning to up)
- examine REST of dead-tuples just after vacuum (if dead-tuples remain, it may be due to any idle in transaction sessions)

>
>It might help differentiate the autovacuum activity from the rest of
>the system (e.g. there's a lot of I/O going on - how much of that is
>coming from autovacuum workers?). This would however require a more
>fine-grained reporting, because often the vacuums run for a very long
>time, especially on very large tables (which is exactly the case when
>this might be handy) - I just had a VACUUM that ran for 12 hours. These
>jobs should report the stats incrementally, not just once at the very
>end, because that makes it rather useless IMNSHO.

+1

Certainly, VACUUM have often much execution time, I just had too.
At present, we cannot predict when this vacuum finishes, what this vacuum is doing now, and whether this vacuum have any problem or not.

Maybe, For DBAs,
It might be better to show vacuum progress in pg_stat_activity.
(if we'd do, add a free-style column like "progress" ?) This column might also be able to use for other long time commands like ANALYZE, CREATE/RE INDEX and COPY. To realize this feature, we certainly need to properly change pgstat_report_activity, use it more and add a new track-activity parameter.

Regards,

Anzai Naoya
---
Naoya Anzai
Engineering Department
NEC Solution Inovetors, Ltd.
E-Mail: nao-anzai(at)xc(dot)jp(dot)nec(dot)com
---

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

______________________________________________________________________
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.