Re: FreeBSD, PostgreSQL, semwait and sbwait!
From: Dror Matalon (dror_at_zapatec.com)
Date: 03/23/04
- Previous message: Claus Guttesen: "Re: FreeBSD, PostgreSQL, semwait and sbwait!"
- In reply to: Jason Coene: "FreeBSD, PostgreSQL, semwait and sbwait!"
- Next in thread: Darcy Buskermolen: "Re: FreeBSD, PostgreSQL, semwait and sbwait!"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Tue, 23 Mar 2004 13:12:00 -0800 To: freebsd-performance@freebsd.org
Databases tend to be io bound not cpu bound. Try running something like
iostat 5
and see how disk utilization varies between testing and production. If
it's taking 3 seconds to run a query that should take 50ms, I would
guess that it's fetching pages from disk.
Also, I'd suggest asking this question with lots more information about
the kind of queries you're running, on the postgresql-performance
mailing list. There's a lot more traffic there and knowledgeable people.
Dror
On Tue, Mar 23, 2004 at 03:02:22PM -0500, Jason Coene wrote:
> 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"
-- Dror Matalon Zapatec Inc 1700 MLK Way Berkeley, CA 94709 http://www.fastbuzz.com http://www.zapatec.com _______________________________________________ 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"
- Previous message: Claus Guttesen: "Re: FreeBSD, PostgreSQL, semwait and sbwait!"
- In reply to: Jason Coene: "FreeBSD, PostgreSQL, semwait and sbwait!"
- Next in thread: Darcy Buskermolen: "Re: FreeBSD, PostgreSQL, semwait and sbwait!"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
|
