Date: Tue, 23 Mar 2004 15:02:22 -0500 From: "Jason Coene" <jcoene@gotfrag.com> To: <freebsd-performance@freebsd.org> Subject: FreeBSD, PostgreSQL, semwait and sbwait! Message-ID: <200403232002.i2NK2Gam077177@mail.gotfrag.com>
next in thread | raw e-mail | index | archive | help
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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?200403232002.i2NK2Gam077177>