Re: Vacuum taking an age

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

 



On Jan 3, 2008 6:48 AM, Brian Modra <epailty@xxxxxxxxxxxxxx> wrote:
> Hi,
>
>
> I have a pretty "live" table: rows being inserted and updated more
> than once 1 per second, though far, far more inserts than updates.
>
> There are currently over 3 million rows.
>
> It has not been vacuumed for months.

How many rows per second?  1?  all of them?  Kinda makes a difference.

If it was 1 a second updated for 3 months that's about 7million dead
rows.  If it was all 3million, then that's 7million * 3million dead
rows, also know as a whole bunch of rows.

Either way, you probably have a table so terribly bloated that a
regular vacuum will not help you in terms of speeding it up.  Regular
vacuums are like brushing your teeth three times a day.  If you've
forgotten for three months, brushing them once isn't likely to fix all
the cavities you've got.  Same thing here.  You'll either need a
vacuum full or a cluster.  Cluster is often faster.  Or you can try
selecting everything into a temp table, truncating the real table, and
inserting the data back in.  Truncation will remove all rows, dead or
otherwise.  The advantage is that it's often faster to truncate /
reload than it is to vacuum full.  If you have indexes, you might want
to drop them while re-inserting and then recreated them.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[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