pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Buchan <sbuchan@technicacorp.com>
To: pgsql-performance@postgresql.org
Subject: Postgresql performance on Solaris
Date: Tue, 1 Apr 2003 14:31:13 -0500
Message-ID: <71F044551C53974EB0735B3737EFE2FC088645@wwwserver> (raw)

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.

 

 

view thread (3+ messages)  latest in thread

Message-ID: <71F044551C53974EB0735B3737EFE2FC088645@wwwserver>
Permalink:  ../71F044551C53974EB0735B3737EFE2FC088645@wwwserver/
Also on:    postgresql.org/message-id/71F044551C53974EB0735B3737EFE2FC088645@wwwserver

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: sbuchan@technicacorp.com
  Subject: Re: Postgresql performance on Solaris
  In-Reply-To: <71F044551C53974EB0735B3737EFE2FC088645@wwwserver>
  References: <71F044551C53974EB0735B3737EFE2FC088645@wwwserver>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox