Re: Manual Vacuum Analyze Take More Time

Поиск
Список
Период
Сортировка
От Keith Fiske
Тема Re: Manual Vacuum Analyze Take More Time
Дата
Msg-id CAODZiv7v3GSWym+ttsoo5756Bmq1nqD9dOY=nLXFNH=ysDTzbQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Manual Vacuum Analyze Take More Time  (Moin Akther <moindba@hotmail.com>)
Список pgsql-admin


On Wed, Nov 28, 2018 at 11:42 AM Moin Akther <moindba@hotmail.com> wrote:
Dear All,

Thanks for your suggestions !

What will be work around to do routine manual “VACUUM ANALYZE” ? If I do only analyze on a table does it will generate WAL files ?

If we don’t do frequent “VACUUM ANALYZE” what are the possible impact on DB ?

Upgrading to higher version and planning for partitioning require series of approval’s and it takes more time to implement.


Thanks and Best Regards,

Moin Akther

 


Not vacuuming should not be considered an option. Only analyzing just updates the statistics and does not do necessary maintenance functions on the table. The longer you let this table go unvacuumed, the worse it's going to be when it does actually need to be vacuumed.  At some point it will be required for it to be vacuumed. It will cause autovacuum to kick in a more aggressive vacuum first (look into autovacuum_freeze_max_age) or your database will shut down (look into transaction ID wraparound). The other more immediate issue is that queries will start to become slower and slower as old rows are not being marked as reusable and bloat continues to grow. 


If this table hasn't been vacuumed for a very long time, a good chunk of the size could indeed be due to bloat. I highly recommend using pgstattuple or the tool discussed in this blog post to check


One thing you could do would be to do the ANALYZE separate from the VACUUM. That will at least shorten the transaction time since it doesn't have to wait until the ANALYZE finishes to commit. But then you should do the ANALYZE soon after the VACUUM finishes.
 
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

В списке pgsql-admin по дате отправления:

Предыдущее
От: Ravi Krishna
Дата:
Сообщение: Re: Manual Vacuum Analyze Take More Time
Следующее
От: "Shukla, Sumeet"
Дата:
Сообщение: Keeping /data directory on dNFS share makes it extremely slow ad notusable