postgresql on FreeBSD 5.3 and high load

From: Claus Guttesen (cguttesen_at_yahoo.dk)
Date: 12/01/04

  • Next message: Vlad GALU: "Re: postgresql on FreeBSD 5.3 and high load" 
    Date: Wed, 1 Dec 2004 11:51:13 +0100 (CET)
To: freebsd-performance@freebsd.org

    Hi.

    Our postgresql-server hits the wall when we get many
    hits on our web. The load-average reaches 70. It
    serves 11 webservers.

    The server is a Dell PE 2850 with 4 GB RAM, dual
    Nocona at 3.2 GHz, HTT is not enabled. It's running
    FreeBSD 5.3 stable (amd64-port). Postgresql ver. 7.4.5
    from ports. The SCSI-controller is LSILogic MegaRAID
    with six disks and three stripes which is mirrored
    (RAID 0+1). Only hardware RAID.

    Due to problems with 4 GB RAM I added hw.physmem=4G to
    /boot/loader.conf, which for some reason reduced
    available memory to 3211 MB. Since then it has run
    stable.

    I have this custom-kernel:

    options SHMMAXPGS=262144
    options SEMMNI=160
    options SEMMNS=960
    options SEMUME=160
    options SEMMNU=480
    options PMAP_SHPGPERPROC=1280

    postgresql.conf:

    max_connections = 768
    shared_buffers = 65536
    sort_mem = 4096
    vacuum_mem = 65536
    checkpoint_segments = 4
    commit_delay = 100000
    commit_siblings = 250
    effective_cache_size = 32768

    Fsync is *not* enabled. I have increased
    shared_buffers and commit_siblings gradually, but it
    does not seem to lower the load on the server.

    /etc/sysctl.conf:
    kern.ipc.somaxconn=2048
    kern.maxfiles=65536
    net.isr.enable=1

    ipcs -M:
    shminfo:
     shmmax: 1073741824 (max shared memory segment size)
     shmmin: 1 (min shared memory segment size)
     shmmni: 192 (max number of shared memory
    identifiers)
     shmseg: 128 (max shared memory segments per
    process)
     shmall: 262144 (max amount of shared memory in
    pages)

    Eventhough load average is high, it's not always 0 %
    idle, but between 0 and 2 percent.

    netstat -m
    18446744073709545011 mbufs in use
    4519/25600 mbuf clusters in use (current/max)
    0/0/0 sfbufs in use (current/peak/max)
    7386 KBytes allocated to network
    0 requests for sfbufs denied
    0 requests for sfbufs delayed
    0 requests for I/O initiated by sendfile
    20286 calls to protocol drain routines

    I can't find the bottleneck.

    regards
    Claus

    _______________________________________________
    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: Vlad GALU: "Re: postgresql on FreeBSD 5.3 and high load"

    Relevant Pages

    • [Info-ingres] Re: Is shared memory a total mess or am I just being thick????
      ... I was going through the ingres code, ... And it's not process oriented either, it's a multi threaded server. ... but each of these iidbms serversneed shared memory to ... the DBMS server was internally-threaded (userland ...
      (comp.databases.ingres)
    • Application Object Replacement
      ... instead of a database hit, my pages hit server memory for small things ... RAM after reading it from a database every 15 minutes; ... I calculated the shared memory stuff ...
      (comp.lang.php)
    • Re: Spurious segvs and malloc catastrophe
      ... instances of apps using it doesn't clean shared memory. ... But that is the whole point of this problem: the server (an Ingres ... The problem persisted in all instances of the user app ...
      (comp.unix.aix)
    • Re: Solaris Error Log
      ... monitoring software as network interfaces. ... Is this server part of a cluster? ... Remote Shared Memory API for Solaris Clusters ...
      (comp.unix.solaris)
    • Re: sqlclient: existing connection was forcibly closed
      ... I saw that post regarding flipping off Shared Memory. ... The box that runs my SQL Server instance also runs a series of processes ... Windows Server 2003 sp2 ...
      (microsoft.public.sqlserver.clients)