Обсуждение: Index degradation
Over a period of some years, particularly in tables whose contents periodically get swapped out entirely (i.e. lots of dead rows), indexes slowly degrade, pushing query latency up. Is there a way to detect this condition short of just monitoring statement latency, so as to know when to REINDEX / build new indexes? -- Alex Balashov | Principal | Evariste Systems LLC Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free) Web: http://www.evaristesys.com/, http://www.csrpswitch.com/
This sounds like index bloat, and yes you can monitor for it. I've written some blog posts both on detection and cleanup procedures as well as been working on a tool to make monitoring for it easier.
Also, to help mitigate this problem in the future, you can try and tune autovacuum to run on said tables so that space is marked as reusable on a regular enough basis that new inserts/updates can make use of it more efficiently. The following blog post talks about how to tune autovacuum for wrap-around, but this is applicable to tuning it to avoid bloat as well. Note this only really helps with gradual changes to tables. Sudden large purging of a table with deletes, or mass updates, are going to leave more empty space that autovacuum can really be tuned for. In such cases, you should always follow up said operations with a manual vacuum and bloat cleanup as needed.
Hope that helps!
On Thu, Nov 7, 2019 at 2:33 PM Alex Balashov <abalashov@evaristesys.com> wrote:
Over a period of some years, particularly in tables whose contents
periodically get swapped out entirely (i.e. lots of dead rows), indexes
slowly degrade, pushing query latency up.
Is there a way to detect this condition short of just monitoring
statement latency, so as to know when to REINDEX / build new indexes?
--
Alex Balashov | Principal | Evariste Systems LLC
Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
Web: http://www.evaristesys.com/, http://www.csrpswitch.com/
Hi Guy's
I need small help on PostgreSQL testing of HA
I am trying to run pgbench but once the primary goes down and secondary comes primary , I want to know know how I can configute pgbench to retry and autoconnect the pgbench workload which I have been running to new Primary .
the idea is to test the Retry feature of pgbench if it exists .
Is pgbeach has Retry fetaure ? if yes then what is parameter for retry needs commands .
can you please help and provide the direction and link
Thanks
Pradeep
From: Alex Balashov <abalashov@evaristesys.com>
Sent: Thursday, November 7, 2019 7:33 PM
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Index degradation
Sent: Thursday, November 7, 2019 7:33 PM
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Index degradation
Over a period of some years, particularly in tables whose contents
periodically get swapped out entirely (i.e. lots of dead rows), indexes
slowly degrade, pushing query latency up.
Is there a way to detect this condition short of just monitoring
statement latency, so as to know when to REINDEX / build new indexes?
--
Alex Balashov | Principal | Evariste Systems LLC
Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
Web: http://www.evaristesys.com/, http://www.csrpswitch.com/
periodically get swapped out entirely (i.e. lots of dead rows), indexes
slowly degrade, pushing query latency up.
Is there a way to detect this condition short of just monitoring
statement latency, so as to know when to REINDEX / build new indexes?
--
Alex Balashov | Principal | Evariste Systems LLC
Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
Web: http://www.evaristesys.com/, http://www.csrpswitch.com/