Re: Autovacuum after bulk data insert(millions!)

Hi Scott,

Thank you for your response. I sent this post because I want to make sure
as to whether we need continuous autovacuum exactly for that database. I
read your answer and consider on it well. I'm gonna keep your suggest
"disabling
autovacuum on those tables and then vacuuming after the bulk upload" on my
mind this fastest way for specific tables.

The problem is not only about autovacuum is taking long time, I've read
some documents about autovacuum, the point is that disable autovacuum and
vacuuming monthly even after millions of bulk data load to a database is
reasonable or not? I'm not insist on vacuuming monthly just I'm confusing
about that.

Regards,
Gunce

On Tue, Oct 17, 2017 at 6:41 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
wrote:

> On Tue, Oct 17, 2017 at 12:59 AM, Günce Kaya <guncekaya14(at)gmail(dot)com>
> wrote:
> > 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?
>
> I'm not sure the exact problem you're trying to solve here. Do you
> want vacuum to finish more quickly? Is autovacuum impacting
> performance of the cluster? Or is autovacuum kicking in partway
> through the bulk load and causing that to be slow?
>
> If you have a fast IO subsystem and you just want autovacuum to finish
> more quickly, then look at making it more aggressive by lowering the
> autovacuum_vacuum_cost_delay from the default 20ms to something lower
> like 5 or 10ms.
>
> If autovacuum is kicking in part way and affecting performance then
> you can look at disabling autovacuum on those tables and then
> vacuuming after the bulk upload. Set things like vacuum_cost_delay to
> keep vacuum from taking up too much IO. Note that the default for
> vacuum_cost_delay is 0, while the default for
> autovacuum_vacuum_cost_delay is 20ms. These two values are worlds
> apart in terms of impact on the IO of your db server.
>
> Of the cuff I'd guess what you want is for autovacuum to keep up, and
> it's taking too long. In that instance, just try lowering
> autovacuum_vacuum_cost_delay from 20ms down to 10ms at first and check
> both to see if autovacuum is flooding your IO and if autovacuum now
> finishes "soon enough" for your needs.
>

--
Gunce Kaya

Linkedin <https://tr.linkedin.com/in/guncekaya> - Twitter
<https://twitter.com/gguncesi> - Blog
<http://www.guncekaya.blogspot.com.tr/>