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(at)gotfrag(dot)com>
To: <freebsd-performance(at)freebsd(dot)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(at)freebsd(dot)org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to
"freebsd-performance-unsubscribe(at)freebsd(dot)org"

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

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