From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: postgresql11 space reuse under high delete/update rate
Date: Mon, 4 Mar 2019 10:26:12 -0600
Message-ID: <49b92726-3397-d0d3-1e24-7535a976f720@gmail.com> (raw)
In-Reply-To: <AM6PR06MB4567867B0AF45B47BD2785E1E6710@AM6PR06MB4567.eurprd06.prod.outlook.com>
References: <AM6PR06MB4567867B0AF45B47BD2785E1E6710@AM6PR06MB4567.eurprd06.prod.outlook.com>
On 3/4/19 4:53 AM, Aliza Abulafia wrote:
>
> 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
>
How frequently did you manually vacuum?
For example, generate a list of tables with a "sufficient" number of dead
tuples, and then manually vacuum them in parallel:
TABLES=`mktemp`
psql $DB -c "SELECT '-t', schemaname||'.'||relname
FROM pg_stat_all_tables
WHERE n_dead_tuples > 500 -- or whatever number you think best
ORDER BY 2;" > $TABLES
vacuumdb --jobs=6 --dbname=$DB `cat $TABLES`
psql -c "CHECKPOINT;"
--
Angular momentum makes the world go 'round.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: postgresql11 space reuse under high delete/update rate
In-Reply-To: <49b92726-3397-d0d3-1e24-7535a976f720@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox