| From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
|---|---|
| To: | Jonathan Hoover <jhoover(at)yahoo-inc(dot)com> |
| Cc: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>, Kenneth Marshall <ktm(at)rice(dot)edu> |
| Subject: | Re: Disk Performance Problem on Large DB |
| Date: | 2010-11-05 03:33:56 |
| Message-ID: | AANLkTin=UOPrm6jEY3_mJfNH0z74n=yTnKk4eLFgrtm=@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
On Thu, Nov 4, 2010 at 9:03 PM, Jonathan Hoover <jhoover(at)yahoo-inc(dot)com> wrote:
> 1. I have now set maintenance_work_mem to 256 MB (which was previously commented by the default config)
> 2. The version is PostgreSQL 8.1.18 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46)
If performance matters, look at upgrading to at LEAST 8.3.latest or
8.4.latest. Preferably 9.0.latest.
> 3. What would be the best and cheapest thing I could for IO performance?
Get an SSD drive. Or a RAID controller with battery backed cache. If
you're data isn't valuable (i.e. you can reproduce it at will) then
turning off things like fsync and full page writes (I don't think 8.1
has the ability to turn off full page writes). can help a lot.
> 4. I need to read up on TRUNCATE, which I have not used before. Care to give a quick overview before I RTFM?
Truncate is basically a DDL (data definition language) command as
opposed to a DML (data manipulation language) command. It baseically
drops the table underneath the table def and recreates it as empty.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Samuel Stearns | 2010-11-05 03:40:33 | Re: Disk Performance Problem on Large DB |
| Previous Message | Samuel Stearns | 2010-11-05 03:33:31 | Re: Disk Performance Problem on Large DB |