Re: Best OS & Configuration for Dual Xeon w/4GB & Adaptec RAID 2200S - Mailing list pgsql-performance

> Here is my current configuration:
>
> Dual Xeon 3.06Ghz 4GB RAM
> Adaptec 2200S 48MB cache & 4 disks configured in RAID5
> FreeBSD 4.11 w/kernel options:
> options         SHMMAXPGS=65536
> options         SEMMNI=256
> options         SEMMNS=512
> options         SEMUME=256
> options         SEMMNU=256
> options         SMP                     # Symmetric MultiProcessor Kernel
> options         APIC_IO                 # Symmetric (APIC) I/O
>
> The OS is installed on the local single disk and postgres data directory
> is on the RAID5 partition.  Maybe Adaptec 2200S RAID5 performance is not as
> good as the vendor claimed.  It was my impression that the raid controller
> these days are optimized for RAID5 and going RAID10 would not benefit me much.

I don't know whether 'systat -vmstat' is available on 4.x, if so try
to issue the command with 'systat -vmstat 1' for 1 sec. updates. This
will (amongst much other info) show how much disk-transfer you have.

> Also, I may be overlooking a postgresql.conf setting.  I have attached the
> config file.

You could try to lower shared_buffers from 30000 to 16384. Setting
this value too high can in some cases be counterproductive according
to doc's I read.

Also try to lower work_mem from 16384 to 8192 or 4096. This setting is
for each sort, so it does become expensive in terms of memory when
many sorts are being carried out. It does depend on the complexity of
your sorts of course.

Try to do a vacuum analyse in your crontab. If your aliases-file is
set up correctly mails generated by crontab will be forwarded to a
human being. I have the following in my (root) crontab (and mail to
root forwarded to me):

time /usr/local/bin/psql -d dbname -h dbhost -U username -c "vacuum
analyse verbose;"

> In summary, my questions:
>
> 1. Would running PG on FreeBSD 5.x or 6.x or Linux improve performance?

Going to 6.x would probably increase overall performance, but you have
to try it out first. Many people report increased performance just by
upgrading, some report that it grinds to a halt. But SMP-wise 6.x is a
more mature release than 4.x is. Changes to the kernel from being
giant-locked in 4.x to be "fine-grained locked" started in 5.x and
have improved in 6.x. The disk- and network-layer should behave
better.

Linux, don't know. If your expertise is in FreeBSD try this first and
then move to Linux (or Solaris 10) if 6.x does not meet your
expectations.

> 3. Why isn't postgres using all 4GB of ram for at least caching table for reads?

I guess it's related to the usage of the i386-architecture in general.
If the zzeons are the newer noconas you can try the amd64-port
instead. This can utilize more memory (without going through PAE).

> 4. Are there any other settings in the conf file I could try to tweak?

max_fsm_pages and max_fsm_relations. You can look at the bottom of
vacuum analyze and increase the values:

INFO:  free space map: 153 relations, 43445 pages stored; 45328 total
pages needed

Raise max_fsm_pages so it meet or exceed 'total pages needed' and
max_fsm_relations to relations.

This is finetuning though. It's more important to set work- and
maintenance-mem correct.

hth
Claus