Re: Extreme high load averages - Mailing list pgsql-performance

Shridhar Daithankar wrote:
>
> It gives hint to psotgresql how much file system cache is available in the
> system.
>
> You have 1GB memory and your application requirement does not exceed 400MB. So
> OS can use roughly 600MB for file system cache. In that case you can set this
> parameter to 400MB cache to leave room for other application in FS cache.
>
> IIRC, BSD needs sysctl tuning to make more memory available for FS cache other
> wise they max out at 300MB.
>
> Roughly this setting should be (total memory -application
> requirement)*(0.7/0.8)
>
> I guess that high kernel load you are seeing due to increased interaction
> between postgresql and OS when data is swapped to/fro in shared memory. If OS
> cache does well, postgresql should reduce this interaction as well.
>
>
> BTW, since you have IDE disks, heavy disk activity can eat CPU as well. Is
> your disk bandwidth totally maxed out? Check with vmstat or whatever
> equivalent you have on BSD.
>
>  Shridhar
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

I changed the value of effective_cache_size seems interesting to 512.
The database restarted without any problems and load averages seem to be
a bit lower as a result.

Since people have been asking for it, I added in most of the stat
command outputs that I could think of.   All located below my signature
block, this will show you what roughly 127 client connections with
Postgre will generate.   The numbers are a lot nicer to see then a 30
load level.

Note, that the high number of connections is a side effect of connection
pooling under Apache using Apache::DBI.   This means that for every
client on the http server there is a connection to Postgres even if the
connection is idle.

The above may be a factor of performance as well.   As I had noticed
that with an idle child setting being too high, that server would show
very high load averages as well.   Probably an indication that the
system is continually forking new children trying to just keep the idle
child count at the right level.

    Martin Foster
    Creator/Designer Ethereal Realms
    martin@ethereal-realms.org

vmstat:
  2:09PM  up 16:45, 1 user, load averages: 0.36, 0.30, 0.35

vmstat:
  procs   memory
  r b w    avm    fre
  1 0 0 234036 687548

  page
  flt  re  pi  po  fr  sr
  621   0   0   0   0   0

  faults     cpu
   in    sy   cs us sy id
  364   396   88 19  1 79

iostat:
       tty            wd0             wd1             cpu
  tin tout  KB/t t/s MB/s   KB/t t/s MB/s  us ni sy in id
    0 1023  4.53   1 0.01   9.72  11 0.10  19  0  1  0 79

pstat -s:
  Device      512-blocks     Used    Avail Capacity  Priority
  swap_device    4194288        0  4194288     0%    0

top header:
   load averages:  0.31,  0.35,  0.42

   147 processes: 2 running, 145 idle
   CPU states: 32.9% user, 0.0% nice, 0.9% system, 0.0% interrupt, 66.2%
idle
   Memory: Real: 263M/377M act/tot  Free: 630M  Swap: 0K/2048M used/tot

ps -uax:
USER       PID %CPU %MEM   VSZ   RSS TT   STAT STARTED       TIME COMMAND
postgres  1561  0.0  0.5  2120  4812 p0  I      1:48PM    0:00.10
/usr/local/bin/postmaster (postgres)
postgres  9935  0.0  2.8  3832 29744 p0  I      1:48PM    0:00.74
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres  7436  0.0  0.6  3640  6636 p0  S      1:48PM    0:00.92
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 18814  0.0  7.0  3876 72904 p0  I      1:48PM    0:04.53
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 15346  0.0  4.1  3820 42468 p0  I      1:48PM    0:00.93
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 13621  0.0  6.9  3832 71824 p0  I      1:48PM    0:02.66
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 20916  0.0  4.7  3812 49164 p0  I      1:48PM    0:00.59
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 21684  0.0  2.2  3688 23356 p0  S      1:48PM    0:01.27
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 19472  0.0  6.9  3824 72452 p0  I      1:48PM    0:02.61
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 27361  0.0  0.7  3664  6976 p0  S      1:48PM    0:00.91
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 28925  0.0  2.8  3840 29528 p0  I      1:48PM    0:00.46
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 12790  0.0  2.7  3800 28080 p0  I      1:48PM    0:01.11
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 13957  0.0  6.8  3820 71476 p0  I      1:48PM    0:02.26
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 29129  0.0  2.8  3828 29096 p0  I      1:48PM    0:01.50
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 24279  0.0  2.7  3824 27992 p0  S      1:48PM    0:01.08
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 20382  0.0  0.6  3640  6748 p0  S      1:48PM    0:00.91
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 28258  0.0  6.9  3872 71912 p0  S      1:48PM    0:03.01
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres 16961  0.0  0.6  3664  6612 p0  S      1:48PM    0:00.96
postmaster: ethereal ethereal 192.168.1.6 idle in tra
postgres  8599  0.0  0.6  3664  6656 p0  S      1:48PM    0:00.90
postmaster: ethereal ethereal 192.168.1.6 idle in tra