> 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
