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

From Kenji Morishige
Subject Best OS & Configuration for Dual Xeon w/4GB & Adaptec RAID 2200S
Date
Msg-id 20060317221116.GA14661@juniper.net
Whole thread Raw
Responses Re: Best OS & Configuration for Dual Xeon w/4GB &  (Scott Marlowe <smarlowe@g2switchworks.com>)
Re: Best OS & Configuration for Dual Xeon w/4GB & Adaptec RAID 2200S  ("Claus Guttesen" <kometen@gmail.com>)
Re: Best OS & Configuration for Dual Xeon w/4GB & Adaptec RAID 2200S  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Best OS & Configuration for Dual Xeon w/4GB & Adaptec RAID 2200S  ("Claus Guttesen" <kometen@gmail.com>)
Re: Best OS & Configuration for Dual Xeon w/4GB & Adaptec RAID 2200S  (Vivek Khera <vivek@khera.org>)
List pgsql-performance
About a year ago we decided to migrate our central database that powers various
intranet tools from MySQL to PostgreSQL. We have about 130 tables and about
10GB of data that stores various status information for a variety of services
for our intranet.  We generally have somewhere between 150-200 connections to
the database at any given time and probably anywhere between 5-10 new
connections being made every second and about 100 queries per second. Most
of the queries and transactions are very small due to the fact that the tools
were designed to work around the small functionality of MySQL 3.23 DB.
Our company primarily uses FreeBSD and we are stuck on FreeBSD 4.X series due
to IT support issues, but I believe I may be able to get more performance out
of our server by reconfiguring and setting up the postgresql.conf file up
better.  The performance is not as good as I was hoping at the moment and
it seems as if the database is not making use of the available ram.

snapshot of active server:
last pid:  5788;  load averages:  0.32,  0.31,  0.28                                                     up
127+15:16:0813:59:24 
169 processes: 1 running, 168 sleeping
CPU states:  5.4% user,  0.0% nice,  9.9% system,  0.0% interrupt, 84.7% idle
Mem: 181M Active, 2632M Inact, 329M Wired, 179M Cache, 199M Buf, 81M Free
Swap: 4096M Total, 216K Used, 4096M Free

  PID USERNAME      PRI NICE  SIZE    RES STATE  C   TIME   WCPU    CPU COMMAND
14501 pgsql           2   0   254M   242M select 2  76:26  1.95%  1.95% postgre
 5720 root           28   0  2164K  1360K CPU0   0   0:00  1.84%  0.88% top
 5785 pgsql           2   0   255M 29296K sbwait 0   0:00  3.00%  0.15% postgre
 5782 pgsql           2   0   255M 11900K sbwait 0   0:00  3.00%  0.15% postgre
 5772 pgsql           2   0   255M 11708K sbwait 2   0:00  1.54%  0.15% postgre


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.

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

In summary, my questions:

1. Would running PG on FreeBSD 5.x or 6.x or Linux improve performance?

2. Should I change SCSI controller config to use RAID 10 instead of 5?

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

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

Attachment

pgsql-performance by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: 1 TB of memory
Next
From: Scott Marlowe
Date:
Subject: Re: Best OS & Configuration for Dual Xeon w/4GB &