Fwd: FreeBSD, PostgreSQL, semwait and sbwait!

----------  Forwarded Message  ----------

Subject: FreeBSD, PostgreSQL, semwait and sbwait!
Date: March 23, 2004 12:02 pm
From: "Jason Coene" <jcoene@gotfrag.com>
To: <freebsd-performance@freebsd.org>

Hello all,

We're having a substantial problem with our FreeBSD 5.2 database server
running PostgreSQL - it's getting a lot of traffic (figure about 3,000
queries per second), but queries are slow, and it's seemingly waiting on
other things than CPU time.

The database server is a dual P4-2.8 w/ HT enabled (kernel finds 4
processors), 2GB RAM, 4 disk Serial ATA on 3ware RAID, gigabit Ethernet
connection to web servers.  It's running FreeBSD 5.2 and PostgreSQL 7.4.1.

The server is taking a while to respond to both connections, and then
queries (between 1-3 seconds, on a query that should execute in 0.05 or
less).

The CPU usage for the server never goes above 30% (70% idle), and the CPU
time that's in use is nearly always split equal between user and system.
The system is using

Doing a "top", this is what we see:

last pid: 51833;  load averages: 13.72, 11.74, 10.01 up 0+01:55:45 15:00:03
116 processes: 1 running, 99 sleeping, 16 lock
CPU states: 14.6% user, 0.0% nice, 23.7% system, 0.2% interrupt, 61.5% idle
Mem: 91M Active, 1043M Inact, 160M Wired, 52K Cache, 112M Buf, 644M Free
Swap: 4096M Total, 4096M Free

20354 pgsql    131    0 80728K  5352K select 0   0:24  1.71%  1.71% postgres
36415 pgsql      4    0 81656K 67468K sbwait 2   0:00  3.23%  0.59% postgres
36442 pgsql    128    0 82360K 15868K select 2   0:00  1.75%  0.24% postgres
36447 pgsql     -4    0 82544K 10616K semwai 0   0:00  2.05%  0.20% postgres
36461 pgsql     -4    0 81612K  6844K semwai 2   0:00  2.05%  0.20% postgres
36368 pgsql      4    0 82416K 20780K sbwait 3   0:00  0.50%  0.15% postgres
36459 pgsql     -4    0 81840K  7816K semwai 0   0:00  1.54%  0.15% postgres
36469 pgsql     -4    0 81840K  7964K semwai 2   0:00  1.54%  0.15% postgres
36466 pgsql    129    0 81840K  7976K *Giant 2   0:00  1.54%  0.15% postgres
36479 pgsql     -4    0 81528K  6648K semwai 0   0:00  3.00%  0.15% postgres
36457 pgsql     -4    0 81840K  8040K semwai 1   0:00  1.03%  0.10% postgres
36450 pgsql    129    0 82352K  8188K *Giant 2   0:00  1.03%  0.10% postgres
36472 pgsql     -4    0 81824K  7416K semwai 2   0:00  1.03%  0.10% postgres
36478 pgsql    131    0 81840K  7936K select 0   0:00  2.00%  0.10% postgres
36454 pgsql      4    0 82416K 16300K sbwait 3   0:00  0.51%  0.05% postgres
36414 pgsql      4    0 82416K 15872K sbwait 2   0:00  0.27%  0.05% postgres

Our kernel is GENERIC plus:

maxusers        512
options         SYSVSHM
options         SHMMAXPGS=262144
options         SHMSEG=512
options         SHMMNI=512
options         SYSVSEM
options         SEMMNI=512
options         SEMMNS=1024
options         SEMMNU=512
options         SEMMAP=512
options         NMBCLUSTERS=32768

Interesting bits from postgresql.conf:

max_connections = 512
shared_buffers = 8192
sort_mem = 16384
vacuum_mem = 8192
fsync = false

It seems that queries are executing fine once they start, but it's taking a
while for them to get going, while the postgres process sits in semwait,
sbwait or select.  This problem doesn't happen when there's little load on
the server, it's only when we open it for public consumption that it
exhibits these problems.

Anyone have this type of problem before?  Am I missing something?

Thanks, Jason

_______________________________________________
freebsd-performance@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to
 "freebsd-performance-unsubscribe@freebsd.org"

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

--
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com