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
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
2017-10-17
4:59 GMT-02:00 Günce Kaya <
guncekaya14@xxxxxxxxx>:
>
>
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
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,
