From: | Benedikt Grundmann <bgrundmann(at)janestreet(dot)com> |
---|---|
To: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to introspect autovacuum analyze settings |
Date: | 2016-11-21 15:05:19 |
Message-ID: | CADbMkNMud-CO7qjW-R3r0at0o3VUOZT09fmkmuMPSG=cYSEU1A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 21 November 2016 at 14:57, Guillaume Lelarge <guillaume(at)lelarge(dot)info>
wrote:
> 2016-11-21 14:44 GMT+01:00 Benedikt Grundmann <bgrundmann(at)janestreet(dot)com>:
>
>> Hello all,
>>
>> I have a quick question. I feel like somewhere in section 23.1.6 there
>> should be the answer but I couldn't find it yet. Namely how can I query
>> the database for total number of tuples inserted, updated, or deleted since
>> the last ANALYZE? pg_stat_user_tables.n_tup_{ins,upd,del,hot_upd} seems
>> to not reset after an analyze[1]. But clearly the database has that
>> knowledge somewhere persistent because otherwise how could autovacuum do
>> its thing.
>>
>> Rationale for the question. I have the strong suspicion that for some of
>> our bigger tables autovacuum *analyze *is not hitting frequently enough
>> (even so we already much more aggressive settings than the default). So I
>> want to set some custom settings for those tables. But rather than doing
>> it manually for the one table I found I would much rather write a query
>> (ideally one taking any existing per table settings into account) that
>> tells me likely candidates for tweaking. But so far I fail to even get the
>> relevant data.
>>
>>
> What you are looking for is the n_mod_since_analyze column of the
> pg_stat_user_tables view. It's available since the 9.4 release. If you have
> an earlier version, you need this extension: http://pgxn.org/dist/mods_
> since_analyze/1.0.0/
>
>
Ah excellent. Thanks. Sigh updating to 9.x with x > 2 has been a long
time coming.
> --
> Guillaume.
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-11-21 15:11:41 | Re: Postgresql 9.5 and Shell scripts/variables vs. C programming/defining a value to be used |
Previous Message | Guillaume Lelarge | 2016-11-21 14:57:17 | Re: How to introspect autovacuum analyze settings |