Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e4Quz-0000f9-Kq for pgsql-admin@arkaria.postgresql.org; Tue, 17 Oct 2017 12:24:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e4Quy-0005k6-Nu for pgsql-admin@arkaria.postgresql.org; Tue, 17 Oct 2017 12:24:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e4Quy-0005jp-1V for pgsql-admin@postgresql.org; Tue, 17 Oct 2017 12:24:20 +0000 Received: from mout.perfora.net ([74.208.4.194]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e4Quq-0003RU-8q for pgsql-admin@postgresql.org; Tue, 17 Oct 2017 12:24:19 +0000 Received: from [192.168.1.101] ([98.169.209.232]) by mrelay.perfora.net (mreueus003 [74.208.5.2]) with ESMTPSA (Nemesis) id 0MV3pT-1dj90D2TL3-00YUKs; Tue, 17 Oct 2017 14:24:04 +0200 Message-ID: <59E5F661.3060600@sqlexec.com> Date: Tue, 17 Oct 2017 08:24:01 -0400 From: MichaelDBA User-Agent: Postbox 5.0.20 (Windows/20171012) MIME-Version: 1.0 To: Thomas Markus CC: pgsql-admin@postgresql.org Subject: Re: Autovacuum after bulk data insert(millions!) References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------050904090104090608020005" X-Provags-ID: V03:K0:c94G9ht9wRTaCerR9TFQ0hphxeQ3YzYIrB20Z09PX5VM5i0d8UY qxqUq90tq/c+HUWFrVizhB6MxlT2BsLm4CqVPph/V1DB/oZLqOzsg/RTGwr08LByH4tla6+ LDAzlRpILHcZ7vyZdwND+A0NI4XE4FIciAuR3O/Fp87Ec8kOhGkZdY/bfG4tgYjY2gp5quX x91onesU8hwawLidCHp8Q== X-UI-Out-Filterresults: notjunk:1;V01:K0:r+u8EBdRRqE=:VEF2SIE7h9kKDv9f85YAnX 6+dkC+0eqhEeB1GpXIYXN8sll/1dvGqdWeDER1mbOrQtkCbUDSVV5fsEskijSuMQB9NT/a96F 2pXynU81mxUEQTau/ilvTDZ7f7Sc5DIyk8l6MUs35R71CbXApu3gsUmZzpy5Yq0ENAOs44Zwl Xq3zCMf1yKLBSPOB6rOijcCD/ZS/ZqUx3siKOs0oNE6fBfG+gdGcqqPfSMXOpdYfyfegqsKdX kzkztVstUD8uRn7vGFCzj4MPeIWOib+KeoS21epyn2cWM7Lb5DkWJOKGqJ/Oq9fUoiAdx4eCw pHFMaLZlEKfQr2nNB7N0Iq2H8iJvHE25yF3Terwbkxbg9DT4FKlzmHN7Sx9jWg/sOlMkyQB67 Moeakj4nwWyVFp0F1yJOh1gkCQy2VtlqUIY18rNNpxyt4h/n/cNCCXF2p9yck7BAK9BzsjRIv QWqLwAQIG1Hu4jPrVpcPiA94JKYKL/zpii6IWNASvPHN7wwSnIM0wKJMaHWR4+UQzMrmBgQKZ l48ptQozCGoPN9ZeQZa01eM4i9Z/BNq/SkHNf3ufKxTj9IRxaYu/URAebzGSy8/pAooqeql7B TUShc05aOZaD1HRiCRAw7NMBowO87VRkzcpBEraipfsNSAkH4o14vxrAsa0UMOiLLMZA/LjFD Hcha7BblEDRUM5yFv2MDxNr8FrChAAkVBVUyhk7RCWaz3C/rYd8HvcXGUTR9Qwrk6JCX3MTby 1rTZJqjYzT1twZlIK6p1T570RBUa7SJLIeeU31ICt6YxUBnxusFvIzAukPo= List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------050904090104090608020005 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Another thing to consider here is whether there are queries running against these tables shortly after the bulk inserts are done. Visibility checks can become expensive if these recent tuple inserts are not vacuumed right away. Please give more context to the SQL workload for a normal day. Are there multiple bulk inserts? How often? Any significant window where they occur? How many queries and how often related to these bulked up tables? Also, you may end up choosing a strategy that involves making autovacuum toggle between being more or less aggressive based upon knowledge of the SQL workload. Regards, Michael Vitale > Thomas Markus > Tuesday, October 17, 2017 7:48 AM > Hi, > > Am 17.10.17 um 13:37 schrieb Günce Kaya: > works same for a database, so it depends on your usecase > > * disable autovacuum for instance > * run 'vacuumdb -a -z' manually/in cron > > Regards > Thomas > > Günce Kaya > Tuesday, October 17, 2017 7:37 AM > Hello Fabrízio, > > Thank you for your advice but I'm not asking for only a specific table > and not looking for temporary solution. Previous post may not clear to > much and I'd explain again. > > So autovacuum process takes long time for that database. I don't know > how can I estimate timing for a vacuum after insert millions of bulk > data to a table. So there are many table inserting bulk data daily. > This solution not for only a table. I'm looking a solution for a > database. Data volume of this database is around 2GB so many > transactions in a day. > > This is only suggestion and I'm really curious because can not > estimate what do you think about that, what if disable autovacuum and > vacuum all tables in every month? > > Regards, > Gunce > > > > > -- > Gunce Kaya > > Linkedin - Twitter > - Blog > > Fabrízio de Royes Mello > Tuesday, October 17, 2017 7:16 AM > > > 2017-10-17 4:59 GMT-02:00 Günce Kaya >: > > > > Hi all, > > > > We have a database that is used for CRM. During day there are many > process like bulk loading large amounts of data(not thousand like > millions!). And all day we observe auto vacuum in server status. It > takes long time. I'm curious what happens if we disable autovacuum for > a database that process bulk insert? After insert data a table, what > autovacuum will do for that table? > > > > I've read some article like; "The autovacuum process takes care of > several maintenance chores inside your database that you really need. > Generally, if you think you need to turn regular vacuuming off because > it's taking too much time or resources, that means you're doing it > wrong. The answer to almost all vacuuming problems is to vacuum more > often, not less, so that each individual vacuum operation has less to > clean up. > > However, it's acceptable to disable autovacuum for short periods of > time, for instance when bulk loading large amounts of data." > > > > The reason why I'm asking, millions of data is inserting to CRM > database all day. What is your advice for that scenario? Should I > disable autovacuum during bulk process or disable autovacuum totally > and autovacuum tables in a some period? > > > > One option is: > 1) Disable autovacuum in target tables > 2) Run bulk load process > 3) Run VACUUM ANALYZE manually > 4) Enable autovacuum in target tables > > Regards, > > -- > Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/ > PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento > Günce Kaya > Tuesday, October 17, 2017 2:59 AM > Hi all, > > We have a database that is used for CRM. During day there are many > process like bulk loading large amounts of data(not thousand > like millions!). And all day we observe auto vacuum in server status. > It takes long time. I'm curious what happens if we disable autovacuum > for a database that process bulk insert? After insert data a table, > what autovacuum will do for that table? > > I've read some article like; /"The autovacuum process takes care of > several maintenance chores inside your database that you really need. > Generally, if you think you need to turn regular vacuuming off because > it's taking too much time or resources, that means you're doing it > wrong. The answer to almost all vacuuming problems is to vacuum more > often, not less, so that each individual vacuum operation has less to > clean up./ > /However, it's acceptable to disable autovacuum for short periods of > time, for instance when bulk loading large amounts of data."/ > > The reason why I'm asking, millions of data is inserting to CRM > database all day. What is your advice for that scenario? Should I > disable autovacuum during bulk process or disable autovacuum totally > and autovacuum tables in a some period? > > Regards, > Gunce Kaya > > Linkedin - Twitter > - Blog > --------------050904090104090608020005 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Another thing to consider here is whether there are queries running against these tables shortly after the bulk inserts are done. Visibility checks can become expensive if these recent tuple inserts are not vacuumed right away.

Please give more context to the SQL workload for a normal day.  Are there multiple bulk inserts?  How often? Any significant window where they occur?  How many queries and how often related to these bulked up tables?

Also, you may end up choosing a strategy that involves making autovacuum toggle between being more or less aggressive based upon knowledge of the SQL workload.

Regards,
Michael Vitale

Tuesday, October 17, 2017 7:48 AM
Hi,

Am 17.10.17 um 13:37 schrieb Günce Kaya:
works same for a database, so it depends on your usecase

* disable autovacuum for instance
* run 'vacuumdb -a -z' manually/in cron

Regards
Thomas

Tuesday, October 17, 2017 7:37 AM
Hello Fabrízio,

Thank you for your advice but I'm not asking for only a specific table and not looking for temporary solution. Previous post may not clear to much and I'd explain again. 

So autovacuum process takes long time for that database. I don't know how can I estimate timing for a vacuum after insert millions of bulk data to a table. So there are many table inserting bulk data daily. This solution not for only a table. I'm looking a solution for a database. Data volume of this database is around 2GB so many transactions in a day. 

This is only suggestion and I'm really curious because can not estimate what do you think about that, what if disable autovacuum and vacuum all tables in every month?

Regards,
Gunce




--
Gunce Kaya

Tuesday, October 17, 2017 7:16 AM


2017-10-17 4:59 GMT-02:00 Günce Kaya <guncekaya14@gmail.com>:
>
> Hi all,
>
> We have a database that is used for CRM. During day there are many process like bulk loading large amounts of data(not thousand like millions!). And all day we observe auto vacuum in server status. It takes long time. I'm curious what happens if we disable autovacuum for a database that process bulk insert? After insert data a table, what autovacuum will do for that table?
>
> I've read some article like; "The autovacuum process takes care of several maintenance chores inside your database that you really need. Generally, if you think you need to turn regular vacuuming off because it's taking too much time or resources, that means you're doing it wrong. The answer to almost all vacuuming problems is to vacuum more often, not less, so that each individual vacuum operation has less to clean up.
> However, it's acceptable to disable autovacuum for short periods of time, for instance when bulk loading large amounts of data."
>
> The reason why I'm asking, millions of data is inserting to CRM database all day. What is your advice for that scenario? Should I disable autovacuum during bulk process or disable autovacuum totally and autovacuum tables in a some period?
>

One option is:
1) Disable autovacuum in target tables
2) Run bulk load process
3) Run VACUUM ANALYZE manually
4) Enable autovacuum in target tables

Regards,

--
   Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Tuesday, October 17, 2017 2:59 AM
Hi all,

We have a database that is used for CRM. During day there are many process like bulk loading large amounts of data(not thousand like millions!). And all day we observe auto vacuum in server status. It takes long time. I'm curious what happens if we disable autovacuum for a database that process bulk insert? After insert data a table, what autovacuum will do for that table?

I've read some article like; "The autovacuum process takes care of several maintenance chores inside your database that you really need. Generally, if you think you need to turn regular vacuuming off because it's taking too much time or resources, that means you're doing it wrong. The answer to almost all vacuuming problems is to vacuum more often, not less, so that each individual vacuum operation has less to clean up.
However, it's acceptable to disable autovacuum for short periods of time, for instance when bulk loading large amounts of data." 

The reason why I'm asking, millions of data is inserting to CRM database all day. What is your advice for that scenario? Should I disable autovacuum during bulk process or disable autovacuum totally and autovacuum tables in a some period?

Regards,
Gunce Kaya


--------------050904090104090608020005--