FreeBSD, PostgreSQL, semwait and sbwait!

[Darcy Buskermolen]

On March 23, 2004 12:02 pm, 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).

What does the following query show (on the DB while it's under load)?
SELECT r.relname,l.mode, count(*) AS numlocks FROM pg_locks l, pg_class r 
WHERE r.oid = l.relation GROUP BY relname,mode;

if you have any sort of insert/updates happening, you may be waiting for a 
lock on that table to be available.  Things like foreign keys make big use of 
locking while they CASCADE.


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

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