Re: Manual Vacuum Analyze Take More Time

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Wed, Nov 28, 2018 at 10:36 AM Shreeyansh Dba <shreeyansh2014@xxxxxxxxx> 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@xxxxxxxxxxx> 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

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux