FreeBSD, PostgreSQL, semwait and sbwait!

[Paul Pathiakis]

Hi,

	just a quick thought.... in /etc/sysctl.conf have you tried:

kern.ipc.shm_use_phys=1

to tell shared memory to be mapped into physical memory space?

	P.



On Tuesday 23 March 2004 03: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).
>
> 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.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
> To unsubscribe, send any mail to
> "freebsd-performance-unsubscribe at freebsd.org"