Re: Adding more space, and a vacuum question.

בתאריך 29/01/11 13:57, ציטוט Craig Ringer:
> 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.
Sigh. Budget considerations, you know.
>
>> 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.
Oh, yes, I forgot about eSATA. I meant basically a real local connection
rather than network one.

>
> 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.

OK.
>
> That said, again if it's read-only you might be fine.
Question is - if the read-only tablespace gets stuck/frozen, what
happens to the read-write part of the database, which is absolutely
essential to have in good responsive working order?
>> 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?
Pg 8.3.11. The tables have more than 200,000,000 records. About the
hardware, I'm not entirely in the loop, but it has two dual-core Intel
Xeon 5130 CPUs, 4G of memory, and its system disk (111G) is separate
from the database disk (825G). The disks are hardware RAID, but I'm not
sure which level, and I think they are 10,000 RPM but I could be wrong.
>
> Was it a standalone VACUUM or was it an autovacuum worker?
Autovacuum worker.

TIA,
Herouth