postgresql11 space reuse under high delete/update rate

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux