Postgresql performance on Solaris - Mailing list pgsql-performance

Hi,

I am getting poor performance from my postgresql (version 7.3.2 compiled with gcc 2.95.2) database when running load tests on my web application.  The database works great until I get above 200 concurrent users.  The following query runtime will vary from:

explain analyze select TIMESTAMP, SPM_CONVSRCADDR,

SPM_CONVDSTADDR, SPM_CONVSRCPORT, SPM_CONVDSTPORT,

SPM_CONVPROTO, ACTION from FirewallLogs where TimeStamp>=1044939600000

and TimeStamp<=1047391020000 and spm_subid='462' and fqdn ='bs2@pcp' order by

timestamp desc;

                                                                    QUERY PLAN                              

---------------------------------------------------------------------------------------------------------------------------------------------------

 Sort  (cost=3525.27..3527.49 rows=888 width=47) (actual time=55.65..56.16 rows=913 loops=1)

   Sort Key: "timestamp"

   ->  Index Scan using firewalllogsindex on firewalllogs  (cost=0.00..3481.77 rows=888 width=47) (actual time=0.40..50.33 rows=913 loops=1)

         Index Cond: ((fqdn = 'bs2@pcp'::character varying) AND ("timestamp" >= 1044939600000::bigint) AND ("timestamp" <= 1047391020000::bigint))

         Filter: (spm_subid = 462)

 Total runtime: 57.36 msec

(6 rows)

to

                                                                    QUERY PLAN                              

---------------------------------------------------------------------------------------------------------------------------------------------------

 Sort  (cost=3525.27..3527.49 rows=888 width=47) (actual time=2323.79..2324.31 rows=913 loops=1)

   Sort Key: "timestamp"

   ->  Index Scan using firewalllogsindex on firewalllogs  (cost=0.00..3481.77 rows=888 width=47) (actual time=0.26..2318.11 rows=913 loops=1)

         Index Cond: ((fqdn = 'bs2@pcp'::character varying) AND ("timestamp" >= 1044939600000::bigint) AND ("timestamp" <= 1047391020000::bigint))

         Filter: (spm_subid = 462)

 Total runtime: 2325.62 msec

(6 rows)

NOTE:  I am only performing select queries - no inserts, deletes or updates. 

I am running postgresql on a SunFire v880 with 4 750MHz sparcv9 processors with 8 Gig of RAM running solaris 8.  I have 2 tables with 500,000 records in each and both tables are indexed.  I am connecting to the database through JDBC using a pool of connections (tried pools of 50, 100, and 200 with similar results).  When running the load tests, the cpu of the box is always above 60% idle.  I have run iostat and I am not seeing any problems with io.

I have tried different size shared_buffers from 4100 to 64000, and I have added the following to the /etc/system file:

set shmsys:shminfo_shmmax=0xffffffff

set shmsys:shminfo_shmmin=1

set shmsys:shminfo_shmmni=256

set shmsys:shminfo_shmseg=256

set semsys:seminfo_semmap=256

set semsys:seminfo_semmni=512

set semsys:seminfo_semmns=512

set semsys:seminfo_semmsl=32

I understand that this could be a problem with the kernel and not postgresql but I am at a loss at what to change to get better performance out of the database or the kernel.

Any help would be appreciated.