From: | Aliza Abulafia <Aliza(dot)Abulafia(at)Amdocs(dot)com> |
---|---|
To: | "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Cc: | "Eli Cohen (ELCOHEN)" <Eli(dot)Cohen(at)amdocs(dot)com> |
Subject: | postgresql11 space reuse under high delete/update rate |
Date: | 2019-03-04 10:53:51 |
Message-ID: | AM6PR06MB4567867B0AF45B47BD2785E1E6710@AM6PR06MB4567.eurprd06.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi
we are evaluating postgresql 11.1 for our productions.
Having a system with 4251 updates per second, ~1000 delete per second and ~3221 inserts per second and 1billion transaction per day.
we face a challenge where PostgreSQL does not reuse its (delete/update) space , and tables constantly increase size.
we configured aggressive Autovacuum settings to avoid the wraparound situation. also tried adding periodic execution of vacuum analyze and vaccum -
and still there is no space reuse. (only vacuum full or pg_repack release space to operating system - but this is not a reuse)
following are our vacuum settings :
autovacuum | on
vacuum_cost_limit | 6000
autovacuum_analyze_threshold | 50
autovacuum_vacuum_threshold | 50
autovacuum_vacuum_cost_delay | 5
autovacuum_max_workers | 32
autovacuum_freeze_max_age | 2000000
autovacuum_multixact_freeze_max_age | 2000000
vacuum_freeze_table_age | 20000
vacuum_multixact_freeze_table_age | 20000
vacuum_cost_page_dirty | 20
vacuum_freeze_min_age | 10000
vacuum_multixact_freeze_min_age | 10000
log_autovacuum_min_duration | 1000
autovacuum_naptime | 10
autovacuum_analyze_scale_factor | 0
autovacuum_vacuum_scale_factor | 0
vacuum_cleanup_index_scale_factor | 0
vacuum_cost_delay | 0
vacuum_defer_cleanup_age | 0
autovacuum_vacuum_cost_limit | -1
autovacuum_work_mem | -1
thanks in advance, Aliza.
This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms of Service, which you may review at https://www.amdocs.com/about/email-terms-of-service <https://www.amdocs.com/about/email-terms-of-service>
From | Date | Subject | |
---|---|---|---|
Next Message | Aliza Abulafia | 2019-03-04 13:24:01 | postgresql11.1 - stabilize partition pruning at execution time |
Previous Message | Ashok Kumar Tiwari | 2019-03-04 09:14:59 | Re: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend. |