Re: Vacuum Issues
| От | Keith |
|---|---|
| Тема | Re: Vacuum Issues |
| Дата | |
| Msg-id | CAHw75vuwkDOFsWhGNnMvtuJQofxjQoF9278BML6G1DHQ+px60g@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Vacuum Issues (Jerry Sievers <gsievers19@comcast.net>) |
| Список | pgsql-admin |
On Thu, Mar 26, 2020 at 8:05 PM Jerry Sievers <gsievers19@comcast.net> wrote:
Darron Harrison <darron@realtyserver.com> writes:
> I am currently seeing three WAL sender processes on the master, and
> no stale replication connections on the slaves.
>
> To clarify, are you saying there should be two WAL sender processes
> for each slave for a total of six?
>
Silly question...
Have you verified that autovac is actually running? launcher still
alive, not malconfig'd as to do nothing... etc?
Silly question #2; supposing autovac is trying to do as intended, have
you inspected the logs to insure that it's not erroring out on one or
more tables, catalogs, indexes... etc?
I've reade most of this thread and not seeing any indication if the
problem is confined to just one object or several.
FWIW
Also, to clarify, are you seeing actual dead rows not being recycled or just free space not being recovered? These are two completely different things. Actual dead tuples will show up for tables in the pg_stat_all_tables system catalog under the n_dead_tup column. Note also that this is just an estimate since the last time an analyze was run on that table, also available from the same system catalog via the last_analyze or last_autoanalyze columns. I'd recommend running an ANALYZE on the table(s) in question and checking this catalog again.
For actual data on dead rows or whether this is just free space, you can use the pgstattuple contrib module: https://www.postgresql.org/docs/current/pgstattuple.html
For large tables, this can take a while to run because it is gathering the actual statistics of used space, dead rows and free space, not just the estimates that analyze does.
If it's actual dead tuples and vacuum is not clearing it up, you are running an early version of 9.2 which is 20 release behind the latest (9.2.24). There could very well be a bug if you've exhausted all other possible reasons for the dead tuples sticking around. If you're not able to upgrade to a more recent major version, I would highly recommend at least upgrading to the latest 9.2.
If it's just free space (also called bloat), you will have to perform a VACUUM FULL to full return the disk space to the operating system. This will lock the table for the duration and completely rewrite it and all its indexes. However, once complete, if the table was bloated you should see your backups return to their normal runtimes.
I've also written another tool for making bloat monitoring easier if you find this is a frequent problem: https://github.com/keithf4/pg_bloat_check
Keith
В списке pgsql-admin по дате отправления: