DB index, and table bloat

[illume]

Current situation

There are issues with database index and table size bloat. Leading to service slowdown, and the database running out of resources (memory) and downtime.

What we know

• Without the REINDEX, The index are bloating from 2x to 7x the size it should be over several days. Note, no autovaccuum or autoanalyze ran.
• autovacuum is not running (most likely because it's based on table size, rather than number of rows changed). Meaning garbage collection can't happen.
• autoanalyze is not running(samesies as autovacuum). Meaning query statistics are not updated.
• Cleanup script was running on boot, but didn't trigger a REINDEX after the script was changed.
• The cleanup script with REINDEX was making several ccnew invalid transient index which were using up a lot of resources. After removing the REINDEX and VACUUM ANALYZE from the cleanup script, no new ccnew index were made. This confirms the REINDEX was creating them. https://www.postgresql.org/docs/13/sql-reindex.html
• PostgreSQL 13 is available on RDS (we are using 12). This has index size improvements via deduplication.
• PostgreSQL 14 is not on AWS yet. This has autovacuum improvements on delete.
• There are index size improvements on a staging server when it was updated to pg 13.13 from 12.x and REINDEX'd. See pg 13 release notes. "Users upgrading with pg_upgrade will need to use REINDEX to make an existing index use this feature."
• auto vacuum and auto analyze threshold changes are enough to stop the table bloat, and significantly reduce the index bloat (from 10x to 2x).

What we don't know.

• Something else may be creating invvalid *ccnew index (I don't think we have logging to find out what did this)
  logging says the creation script ran, but with the REINDEX commented out. It could be that a REINDEX was still running from before the script stopped. Because reindexing concurrently can run for quite a long time.
• We don't know for sure what is blocking REINDEX on some tables. Possibly the vacuum analyze ran in the older script... or something else. We could confirm if the VACUUM ANALYZE is stopping the REINDEX by running the original cleanup script on a staging server. [ED: this does not reproduce the issue]
• We can monitor the progress of a REINDEX with https://www.postgresql.org/docs/current/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING
  • pg_stat_progress_create_index view and columns (lockers_total, lockers_done and current_locker_pid)
    select pid,
    usename,
    pg_blocking_pids(pid) as blocked_by,
    query as blocked_query
    from pg_stat_activity
    where cardinality(pg_blocking_pids(pid)) > 0;
• We haven't looked at if there are row based locks held. https://www.postgresql.org/docs/9.6/pgrowlocks.html Probably not necessary until we have confirmed the other issues.
• If there are app db connection leaks, or long running transactions locking rows/tables/index. Need to deploy the nebraska memory leak statistics PR backend: Expose db connection metrics #511

Things we could do

• cleanup script was running on boot
• reindex and vacuum commented out and reordered to put ANALYZE at top.
• Merge pin PostgreSQL version to 13.3, rather than latest. So we know which version we are testing. Makefile, backend: Fix pg-version pin #506
• Merge the DB API fixes PR: Db API fixes #500 Even though we are not yet sure that the app is blocking the REINDEX from running, they are none the less problems.
• Check the index size on pg 13.x upgraded from 12.x after applying a REINDEX to tables. [ED: this gives significant index size savings on several index after upgrading to 13 and running a REINDEX].
• Create migration to adjust the autovacuum/autoanalyze on affected tables to be row based rather than percentage based. This would then mean other nebraska databases are covered to also have autovacuum and autoanalyze run. Test on a staging server. Why a migration script? Because the issue would affect all Nebraska users. DB Autovacuum, and Autoanalyze thresholds #508
• We could confirm if the VACUUM ANALYZE is stopping the REINDEX by running the original cleanup script on staging data(make sure data is actually being deleted). We run the check_postgres commands before and after the script is run. Also monitor the pg_stat_progress_create_index table during the REINDEX. [ED: it does not cause this issue except when run against a server with traffic]
• Try to reproduce generating index bloat, and getting the AUTO VACUUM and AUTO ANALYZE to run with changed thresholds. Perhaps use updateservicectl locally to generate traffic and monitor for bloat with check_postgres --action bloat.
• Log app level connection pool stats. This will let us know if there are app db connection leaks, or long running connections blocking operations. backend: Expose db connection metrics #511
• An alert for when there is index bloat, or when autoanalyze and autoanalyze is not being run, and also for when there are invalid transient index (ccnew etc). Perhaps using check_postgres.
• deploy app db connection stats and analyze results

Autovacuum and Index bloat articles

Some resources about index bloat and adjusting Auto Vacuum settings.

• https://www.citusdata.com/blog/2016/11/04/autovacuum-not-the-enemy/
• https://www.2ndquadrant.com/en/blog/autovacuum-tuning-basics/
• https://klotzandrew.com/blog/posgres-per-table-autovacuum-management
• https://askdba.org/weblog/2019/04/identifying-postgresql-bloat-and-fixing-it-without-downtime/
• https://sites.google.com/a/gautherot.net/postgresql-tips/vacuum
• https://www.postgresql.org/docs/13/routine-vacuuming.html
• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html
• https://www.postgresql.org/message-id/5684310F.6060509%40joeconway.com (100,000 rows)

check_postgres

check_postgres can be used to check the status of the database index bloat and when garbage collection is happening (or not).

sudo apt-get install check-postgres

check_postgres --dbuser=postgres --dbpass=nebraska --host=localhost --port=5432 --dbname=nebraska --action=bloat 

check_postgres --dbuser=postgres --dbpass=nebraska --host=localhost --port=5432 --dbname=nebraska --action=last_autovacuum 

check_postgres --dbuser=postgres --dbpass=nebraska --host=localhost --port=5432 --dbname=nebraska --action=last_autoanalyze 

[illume]

Thanks to Santosh, I think we figured out some good tuning values. #508
Ultimately we'd need to automate the process based on the rate of table change. Some months and tables have seen 5x growth in number of rows changed, and it's possible even higher growth could happen in other instances and months.

[illume]

Index size is decreased significantly with PostgreSQL 13.x+ when tables are REINDEXd compared to 12.x because it enables the deduplication feature. Some index are one third or one half the size compared to pg 12.x (some don't decrease at all).

[illume]

To reproduce table bloat I tried this command to generate load:

ulimit -n 4096
watch -n 14 ./bin/updateservicectl --server http://localhost:8000 instance fake --clients-per-app 4000 --app-id e96281a6-d1af-4bde-9a0a-97b76e56dc57 --group-id stable

Then these commands to check for bloat and last vacuum status:

watch -n 7 "check_postgres --action=bloat --dbuser=postgres --dbpass=nebraska --host=localhost --port=5432 --dbname=nebraska && check_postgres --action=last_autovacuum --dbuser=postgres --dbpass=nebraska --host=localhost --port=5432 --dbname=nebraska && psql postgres://postgres:nebraska@localhost:5432/nebraska -c \"select indexname, pg_relation_size(indexname::regclass) as size from pg_indexes where tablename not like 'pg%' order by size desc limit 15;\""

This way we can both reproduce index bloat, and then test our solutions.

[illume]

Turns out the cleanup script was already tried on the staging server by Santosh, and the ccnew issue can not be reproduced with the cleanup script. Since we know that the cleanup script isn't causing ccnew by itself (because you ran it on staging and it didn't) - it is likely some interaction with the app and the queries it's running.

[illume]

instance_application is the table that gets dead tuples because it's often being updated during operation. instance_application_application_id_group_id_last_check_for_idx seems to be the largest index. With the auto vacuum threshold changes the table manages to grow. Additionally, the other tables that grow (instance_status_history, event, instance, activity) need to be analyzed more frequently so that query statistics are ok for the query planner. These tables get items deleted when a cleanup script is run only - that's when they need the auto vacuum and auto analyze run.

The instance_application_application_id_group_id_last_check_for_idx index does get index bloat even when auto vacuum is running regularly. Therefore a REINDEX should be run occasionally during a maintenance window. However, if the auto analyze is run frequently, the affect on query performance is not as bad. Additionally, because the table bloat is almost entirely removed the affect on query performance is not as bad.

Latest script to watch pg table/index size values.

watch -n 7 "check_postgres --action=bloat --dbuser=postgres --dbpass=nebraska --host=localhost --port=5433 --dbname=nebraska && check_postgres --action=last_autovacuum --dbuser=postgres --dbpass=nebraska --host=localhost --port=5433 --dbname=nebraska ; psql postgres://postgres:nebraska@localhost:5433/nebraska -c \"select indexname, pg_relation_size(indexname::regclass) as size from pg_indexes where tablename not like 'pg%' order by size desc limit 8;\" && psql postgres://postgres:nebraska@localhost:5433/nebraska -c \"select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables order by last_autoanalyze limit 5;\" ; psql postgres://postgres:nebraska@localhost:5433/nebraska -c \"SELECT relname, n_dead_tup, n_live_tup, (n_dead_tup/ n_live_tup) AS DeadTuplesRatio, last_vacuum, last_autovacuum FROM pg_catalog.pg_stat_all_tables WHERE relname in ('instance_status_history', 'event', 'instance_application', 'instance', 'activity') order by n_dead_tup DESC;\""

[illume]

Ran a test with the thresholds in #508 and the index bloat didn't grow anywhere near as much as without it, but the index instance_application_application_id_group_id_last_check_for_idx (2.4GB/wasting 1.2GB) did still grow to 2.1x bloat when instance_status_history was up to 17.5 million rows. Compared to 10x index bloat or more seen without the threshold changes. Ubuntu LTS, pg 12.9.

[joaquimrocha]

@illume , should we close this now that we have added the sql mention to the docs?

[illume]

Yeah, I think it can be closed.

The documentation change about the db settings which is enough I guess. It would have been nice to automate that, but I think it's maybe a bit risky to apply for everyone.