Re: Performance Tuning, hardware-wise - Mailing list pgsql-general

>     As for the drive in that machine, doing inserts on it was SLOW.
Slower
> even than on our beater development machine.  I suppose I could have
fiddled
> with hdparm to increase the disk I/O, but that would have been a
temporary
> fix at best. Our CGI applications were eating lots of CPU time, and we
just
> needed more processors.

If you needed more CPU power, then that's fair. However, bear in mind that
hdparm is NOT necessarily just a TEMPORARY fix. Using DMA modes can HALVE
the CPU utilization required for heavy disk I/O. It is also not uncommon to
increase the disk bandwidth by as much as four times by tuning the disks
with hdparm. I have seen tens, if not hundreds of Linux machines. I have
yet to see one that didn't benefit greatly from using hdparm to tune up
disk transfers. On my database development server, the CPU consumption on
heavy SELECT/INSERT/UPDATE setup went from around 60% to around 40%, and
the hdparm -t -T reported the increase from 4 MB/s on both buffer-cache and
buffer-disk transfers to about 50 MB/s and 20 MB/s respectively. It takes a
bit to get it right, but if you know your disk and motherboard spec, and
don't feel like adventuring into overclocking and overtuning, you are
pretty much guaranteed success on the first try. Then you just have to add
the tune-up to your startup scripts, preferably before the swap is enabled,
as in my experience, in a very limited number of cases, retuning the disk
after the swap has been started can cause some minor, random stability
problems.

>      So, we moved to a large database machine to service a group of web
> servers.  The database machine has four Xeon 700's in it, with 512 megs
of
> RAM.  For the disk, we did decide to go with a RAID array, for disk
> redundancy as much as efficiency.  The fast disk array makes inserts go
> tremedously fast, but for selects, well, it's still the same - you're
> limitted by CPU and memory.   Why not disk I/O?  Well, the entire dataset
is
> in the disk cache anyway, so there's very little disk I/O involved with
> selects.

Glad to hear that this worked for you, but some of us have a setup where
you have to randomly query a 8+ GB database. Having it all in cache just
isn't going to happen in that case and lots of disk I/O bandwidth is the
only feasible answer (I don't consider an 8 GB RAM disk to be a feasible
option)

>      Our data directory is 340 megs, and the machine has 512 megs of RAM,
> and Postgres is just too efficient with RAM.   ; )  I start up the
> postmaster with 3072 buffer blocks, and yesterday increased the amount of
> memory each postmaster can use for sorting from the default of 4 megs to
32
> megs, which did give a small speed increase, but the machine *still* has
at
> least 120 megs completely unused at all times.  Maybe I'll give each
backend
> more RAM for sorting. : )

You're lucky that you have a comparatively small data set. My data set is
about 20 times as big, and we were stargint to experience crashes (well,
things would abort, rather then crash as they are well behaved, but still)
because the machine would blow the 256MB of RAM and 256 MB of swap. And it
would grind to a halt long before that... 512 MB made a world of
difference... But as with all different applications, YMWV.

>   Under Linux, RAM disks aren't much faster/better than regulsr disks,
from
> the benchmarks I've seen.

I can second this. The caching seems to be just too efficient for a vast
improvement in a real-world application... I don't know about benchmarking,
though, as IMHO and experience, benchmark numbers are often meaningless
when used to assess a real-world situation.

> Besides, then a power-outtage can really hose
> you. : )

Well, that is entirely true, but technically, you ought to have a UPS if
you have a mission critical system. I have recently had a complete disk's
worth of data hosed due to power failure, as something went wrong and the
root inode got corrupted. Usefulness of backups is difficult to
overestimate...

HTH.

Gordan