Re: FreeBSD, PostgreSQL, semwait and sbwait!

From: Paul Pathiakis (paul_at_pathiakis.com)
Date: 03/23/04

  • Next message: wally morton: "FTP fileserver???" 
    To: freebsd-performance@freebsd.org
Date: Tue, 23 Mar 2004 16:48:46 -0500

    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@freebsd.org mailing list
    > http://lists.freebsd.org/mailman/listinfo/freebsd-performance
    > To unsubscribe, send any mail to
    > "freebsd-performance-unsubscribe@freebsd.org"
    _______________________________________________
    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: wally morton: "FTP fileserver???"

    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: cannot load color "black"
      ... Thx for your experience. ... I have installed openoffice recently, ... I am using a NCD X-Terminal on a local lan as my terminal server, ... To UNSUBSCRIBE, email to debian-user-REQUEST@xxxxxxxxxxxxxxxx ...
      (Debian-User)
    • 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)