X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EC04747621C for ; Tue, 1 Apr 2003 14:27:35 -0500 (EST) Received: from mailrelay (technicacorp.com [216.181.29.134]) by postgresql.org (Postfix) with ESMTP id D4A364760CC for ; Tue, 1 Apr 2003 14:27:34 -0500 (EST) thread-index: AcL4hLkaPHjk8tWvTL+s4AJ78XT9fw== Content-Transfer-Encoding: 7bit Received: from WWWSERVER.technicacorp.com ([216.181.29.149]) by mailrelay with Microsoft SMTPSVC(5.0.2195.5329); Tue, 1 Apr 2003 14:27:21 -0500 Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Received: by wwwserver with Internet Mail Service (5.5.2656.59) id ; Tue, 1 Apr 2003 14:31:13 -0500 Message-ID: <71F044551C53974EB0735B3737EFE2FC088645@wwwserver> From: "Scott Buchan" To: Subject: Postgresql performance on Solaris Date: Tue, 1 Apr 2003 14:31:13 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2656.59) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2F885.4198FFC0" X-OriginalArrivalTime: 01 Apr 2003 19:27:21.0186 (UTC) FILETIME=[B7259420:01C2F884] X-Spam-Status: No, hits=-5.7 required=5.0 tests=BAYES_10,HTML_50_60,MISSING_OUTLOOK_NAME version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/8 X-Sequence-Number: 1514 This is a multi-part message in MIME format. ------_=_NextPart_001_01C2F885.4198FFC0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="iso-8859-1" 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. ------_=_NextPart_001_01C2F885.4198FFC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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&= gt;=3D1044939600000

and TimeStamp<=3D1047391020000 and spm_subid=3D'462' = and fqdn =3D'bs2@pcp' order by

timestamp desc;

         &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;        QUERY PLAN            = ;            &n= bsp;     

--------------------------------------------------------= ---------------------------------------------------------------------------= ----------------

 Sort  (cost=3D3525.27..3527.49 rows=3D888 wid= th=3D47) (actual time=3D55.65..56.16 rows=3D913 loops=3D1)

   Sort Key: "timestamp"

   ->  Index Scan using firewalllogsin= dex on firewalllogs  (cost=3D0.00..3481.77 rows=3D888 width=3D47) (actual time=3D0.40..50.33 rows=3D913 loops=3D1)

         Index C= ond: ((fqdn =3D 'bs2@pcp'::character varying) AND ("timestamp" >=3D 1044939600000::bigint) AND ("timestamp" <=3D 1047391020000::bi= gint))

         Filter:= (spm_subid =3D 462)

 Total runtime: 57.36 msec

(6 rows)

 

to

 

         &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;        QUERY PLAN            = ;            &n= bsp;     

--------------------------------------------------------= ---------------------------------------------------------------------------= ----------------

 Sort  (cost=3D3525.27..3527.49 rows=3D888 wid= th=3D47) (actual time=3D2323.79..2324.31 rows=3D913 loops=3D1)

   Sort Key: "timestamp"

   ->  Index Scan using firewalllogsin= dex on firewalllogs  (cost=3D0.00..3481.77 rows=3D888 width=3D47) (actual time=3D0.26..2318.11 rows=3D913 loops=3D1)

         Index C= ond: ((fqdn =3D 'bs2@pcp'::character varying) AND ("timestamp" >=3D 1044939600000::bigint) AND ("timestamp" <=3D 1047391020000::bi= gint))

         Filter:= (spm_subid =3D 462)

 Total runtime: 2325.62 msec

(6 rows)

 

NOTE:  I am only performing select queries - no ins= erts, 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 ha= ve 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=3D0xffffffff

set shmsys:shminfo_shmmin=3D1

set shmsys:shminfo_shmmni=3D256

set shmsys:shminfo_shmseg=3D256

set semsys:seminfo_semmap=3D256

set semsys:seminfo_semmni=3D512

set semsys:seminfo_semmns=3D512

set semsys:seminfo_semmsl=3D32

 

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.

 

 

------_=_NextPart_001_01C2F885.4198FFC0--