Re: Adding more space, and a vacuum question.

On 01/29/2011 05:12 AM, Herouth Maoz wrote:

> The machine has no additional room for internal disks. It is a recent
> purchase and not likely to be replaced any time soon.

Newly acquired or not, it sounds like it isn't sized correctly for the
load and needs an upgrade if it can't be shifted into a more suitable
role and replaced.

> Now, my position
> is that the best solution would be to add an external hard disk, via
> USB/firewire

eSATA? Via a PCI or PCIe add-in SATA controller if there's no existing
eSATA.

FireWire is usable for a database. USB is too ... kind of. Performance
will be poor because of the high latency, CPU-heavy non-DMA access done
by the USB stack.

For something read-only, that might be OK.

> and use it for the archive tables. My sysadmin, on the
> other hand, wants to mount a storage machine remotely and use it for the
> extra tablespace, as the storage machine is a more reliable hardware.

If you have iSCSI or ATA-over-Ethernet disk volumes you can mount, that
might be a good idea. I'd personally avoid NFS or SMB.

That said, again if it's read-only you might be fine.

> think that remote mounted volumes are not a proper device for a
> database, as the network is subject to load and I've ran into frozen
> mounts in both NFS and SMB in the past. Never mind being slower.

Remote *file* *level* shares aren't great for databases, IMO.

> a. Is it normal for vacuum processes to take two weeks?

Define "really huge" and describe the hardware; without numbers it's
hard to know. What version of Pg are you using?

Was it a standalone VACUUM or was it an autovacuum worker?

> b. What happens if the vacuum process is stopped? Are the tuples
> partially recovered, or are they only recovered if the process completes
> properly?

I *think* tuples become available progressively, but I'm not certain of
that.

> c. Is there anything I can do to make vacuums shorter?

Do it much more often.

Use Pg 8.4 or later, with visibility map.

--
Craig Ringer