Обсуждение: Manual Vacuum Analyze Take More Time

Поиск
Список
Период
Сортировка

Manual Vacuum Analyze Take More Time

От
Moin Akther
Дата:
Dear All,

While running Manual “VACUUM ANALYZE” on table size of 1.2TB, its running more than 1 Hour and generated almost 15K WAL files and due to this we have faced huge replication lag on our standby db.

I have maitenance_work_mem of 6GB and RAM Size of 128GB.

There is no lock and blocking queries at that time and CPU and memory utilization also normal.

How we can fix this issue ?

DB Version: 9.4


Thanks and Best Regards,

Moin Akther

 

Re: Manual Vacuum Analyze Take More Time

От
Ron
Дата:
On 11/28/2018 08:22 AM, Moin Akther wrote:
Dear All,

While running Manual “VACUUM ANALYZE” on table size of 1.2TB, its running more than 1 Hour and generated almost 15K WAL files and due to this we have faced huge replication lag on our standby db.

I have maitenance_work_mem of 6GB and RAM Size of 128GB.

There is no lock and blocking queries at that time and CPU and memory utilization also normal.

How we can fix this issue ?

Not an answer to your question, but you should really think about partitioning that table.

--
Angular momentum makes the world go 'round.

Re: Manual Vacuum Analyze Take More Time

От
Shreeyansh Dba
Дата:
Hi Moin Akther,

It seems you have set good enough maitenance_work_mem and as your table size is large, so you should better to go for the partitioning..




On Wed, Nov 28, 2018 at 7:52 PM Moin Akther <moindba@hotmail.com> wrote:
Dear All,

While running Manual “VACUUM ANALYZE” on table size of 1.2TB, its running more than 1 Hour and generated almost 15K WAL files and due to this we have faced huge replication lag on our standby db.

I have maitenance_work_mem of 6GB and RAM Size of 128GB.

There is no lock and blocking queries at that time and CPU and memory utilization also normal.

How we can fix this issue ?

DB Version: 9.4


Thanks and Best Regards,

Moin Akther

 

Re: Manual Vacuum Analyze Take More Time

От
Keith Fiske
Дата:


On Wed, Nov 28, 2018 at 10:36 AM Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Moin Akther,

It seems you have set good enough maitenance_work_mem and as your table size is large, so you should better to go for the partitioning..




On Wed, Nov 28, 2018 at 7:52 PM Moin Akther <moindba@hotmail.com> wrote:
Dear All,

While running Manual “VACUUM ANALYZE” on table size of 1.2TB, its running more than 1 Hour and generated almost 15K WAL files and due to this we have faced huge replication lag on our standby db.

I have maitenance_work_mem of 6GB and RAM Size of 128GB.

There is no lock and blocking queries at that time and CPU and memory utilization also normal.

How we can fix this issue ?

DB Version: 9.4


Thanks and Best Regards,

Moin Akther

 



You're probably just going to have to let it finish at least once. I'd also highly recommend looking into upgrading to at least Postgres 9.6. That one added a feature where if an entire page contains only frozen tuples, then vacuum is able to completely skip over that page. For large tables, this can be a HUGE time saver. But you do have to get the table vacuumed with the FROZEN flag completed at least once to get those pages marked as such.

And as others have said, I would also consider jumping straight to PG 11 if you're upgrading so that you can take advantage of native partitioning.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

Re: Manual Vacuum Analyze Take More Time

От
Moin Akther
Дата:
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

 


From: Keith Fiske <keith.fiske@crunchydata.com>
Date: Wednesday, November 28, 2018 at 9:17 PM
To: "shreeyansh2014@gmail.com" <shreeyansh2014@gmail.com>
Cc: moin <moindba@hotmail.com>, "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: Re: Manual Vacuum Analyze Take More Time



On Wed, Nov 28, 2018 at 10:36 AM Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Moin Akther,

It seems you have set good enough maitenance_work_mem and as your table size is large, so you should better to go for the partitioning..




On Wed, Nov 28, 2018 at 7:52 PM Moin Akther <moindba@hotmail.com> wrote:
Dear All,

While running Manual “VACUUM ANALYZE” on table size of 1.2TB, its running more than 1 Hour and generated almost 15K WAL files and due to this we have faced huge replication lag on our standby db.

I have maitenance_work_mem of 6GB and RAM Size of 128GB.

There is no lock and blocking queries at that time and CPU and memory utilization also normal.

How we can fix this issue ?

DB Version: 9.4


Thanks and Best Regards,

Moin Akther

 



You're probably just going to have to let it finish at least once. I'd also highly recommend looking into upgrading to at least Postgres 9.6. That one added a feature where if an entire page contains only frozen tuples, then vacuum is able to completely skip over that page. For large tables, this can be a HUGE time saver. But you do have to get the table vacuumed with the FROZEN flag completed at least once to get those pages marked as such.

And as others have said, I would also consider jumping straight to PG 11 if you're upgrading so that you can take advantage of native partitioning.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

Re: Manual Vacuum Analyze Take More Time

От
Ravi Krishna
Дата:
> 
> What will be work around to do routine manual “VACUUM ANALYZE” ? If I do only analyze on a table does it will
generateWAL files ?
 
> 

If I am not mistaken analyze will still generate WAL for all recently committed rows whose hint bit is not yet set.
analyzewill set the hint bit
 
which will generate the WAL.

Re: Manual Vacuum Analyze Take More Time

От
Keith Fiske
Дата:


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