Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h0qQ0-000329-PJ for pgsql-admin@arkaria.postgresql.org; Mon, 04 Mar 2019 16:26:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h0qPz-0002Wz-7s for pgsql-admin@arkaria.postgresql.org; Mon, 04 Mar 2019 16:26:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h0qPy-0002Lr-OK for pgsql-admin@lists.postgresql.org; Mon, 04 Mar 2019 16:26:19 +0000 Received: from mail-ot1-x344.google.com ([2607:f8b0:4864:20::344]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h0qPv-0008S2-RI for pgsql-admin@lists.postgresql.org; Mon, 04 Mar 2019 16:26:17 +0000 Received: by mail-ot1-x344.google.com with SMTP id g1so4695307otj.11 for ; Mon, 04 Mar 2019 08:26:15 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=bbpW2bmn/1kDA2Oc6BE4o+bx56Dwp3fY6DIjFATiyWY=; b=pCMYOW+9pWAOKaJBLEpUicccBSQEBdkRYcvDIC6pbgCVN+T5WkShDKiPAOJX8jtez+ za5O+gCCdj2GL0GXlYlLgzpsSV1YzpntyUy7rfjNbwZR7ELKnsgYety38yG/ZkX89AAm G2r34nYZBTeVPtktBaZVsYTFtNGWRVLuI4QxB3f8Fhjrrb6VZA9WDrHEYTkk4KhDRAJ1 5oGxpc4LvLS4FRF3/stmw6nkN658hA+6VMMEJT2MsqIvxczSdwTcmS2Wc7RJEsRlqopR ZhcJzhPGxUTQYF9Bk4HTQgykVzapjoaJrlbzQPD2LadwmOBtA2s1XNkskKnJRYbPt1R0 CG4g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=bbpW2bmn/1kDA2Oc6BE4o+bx56Dwp3fY6DIjFATiyWY=; b=khbfq3KX3ptUd/N6Mu58UZKXbgThgBQ5aj2LRv3nZvkIbB7q6wJSt5HyMOK7KnOPCc mlhuQn6mlkzWoMzkn5miDLEuBIcKnmLmIqoX6MaMWpRCurMXdOGYjoGHy9fBjTbWS0Az JiJPjET9NvOlcJSfdmq5gz7/Lpmul5qXwWFmGOYzhxf336sq1Rtd7Jz3xLTgLnP8bo4q wShLeoQZp2COp8QhAerZaUs2wz+h1G3dCOfx2kMSu9C5bIklAlvcbAoXzV/xVW9fMnPK nytLBsRzMgppe62pblv1thjQy22n1ei15i9hH31pLhTe2LVm0xe3/c8DrmoiysPqP8pM CuSA== X-Gm-Message-State: APjAAAUBuG0VAU0C1ZBDw9Kz/PTpJtVXOiOzBevu+EJ1SHkdo38qym+L r+SEQDTLIo8gt+Z6k4dO8Dy7orSx X-Google-Smtp-Source: APXvYqxths0mX96RZa9SC5llOxWO8tw9RynJOwx5Df4Xm46PAks5RdT0TrVMk7FVOoTnBRrKSyAVnw== X-Received: by 2002:a9d:6394:: with SMTP id w20mr13527369otk.72.1551716774417; Mon, 04 Mar 2019 08:26:14 -0800 (PST) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id w18sm2558121otp.70.2019.03.04.08.26.13 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 04 Mar 2019 08:26:13 -0800 (PST) Subject: Re: postgresql11 space reuse under high delete/update rate To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: <49b92726-3397-d0d3-1e24-7535a976f720@gmail.com> Date: Mon, 4 Mar 2019 10:26:12 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------25B8D063AA52B3938FA3C546" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------25B8D063AA52B3938FA3C546 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------25B8D063AA52B3938FA3C546 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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.
--------------25B8D063AA52B3938FA3C546--