FreeBSD, PostgreSQL, semwait and sbwait!

From: Jason Coene (jcoene_at_gotfrag.com)
Date: 03/23/04

  • Next message: Claus Guttesen: "Re: FreeBSD, PostgreSQL, semwait and sbwait!" 
    To: <freebsd-performance@freebsd.org>
Date: Tue, 23 Mar 2004 15:02:22 -0500

    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"

  • Next message: Claus Guttesen: "Re: FreeBSD, PostgreSQL, semwait and sbwait!"

    Relevant Pages

    • Re: ntp.conf access resctriction
      ... That is allow time service from anyone, but allow only queries ... LAN is not allowed control though. ... your time requests sent to external servers, ... these 'restrict' options have pretty complex/obscure semantics, ...
      (comp.unix.bsd.freebsd.misc)
    • RE: Limit W2K Queries.
      ... please let me know why there is a firewall appliance ... 60Subject: RE: Limit W2K Queries. ... Microsoft CDO for Windows 2000 ... 60>60One stand alone Windows 2000 server (an e-Gap Remote ...
      (microsoft.public.win2000.networking)
    • Re: A few thousands simple queries seem to clog SQL server (for a while)
      ... >queries using the OleDB DataReader, ... the SQL server seems to be clogged and starts failing all ... > dbParams, Object& executeResult) ... > connection to the database. ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: Alternatives to ADP?
      ... Another thing to look at, which Sylvain touched on, but didn't quite highlight is to write your queries on the SQL Server side, and ... then attach them in Access as either Tables or Pass-through Queries. ... have the disadvantage that you have to worry about linking them in...not so much a problem if your server is reliably in one place, ... So I am now testing the other upsizing option that creates an adp file. ...
      (microsoft.public.access.adp.sqlserver)
    • Re: Invalid Argument error...
      ... The same queries now always have a problem, or queries, at random, ... One place I would look is the "References" (open a module, ... The system is based around a SQL Server database, ... our IT support partners came to do a routine housekeeping ...
      (microsoft.public.access.queries)