Re: [PERFORM] Extreme high load averages - Mailing list pgsql-novice

Richard Huxton wrote:
>
> I don't know the *BSDs myself, but do you have the equivalent of iostat/vmstat
> output you could get for us? Also a snapshot of "top" output? People are
> going to want to see:
>  - overall memory usage (free/buffers/cache/swap)
>  - memory usage per process
>  - disk activity (blocks in/out)
>

I changed a bit of the scripting code to cut down on the weight of a
query being run.   This is the only thing in the entire system that
would cause scripts to run at high processor times for extended lengths.
  With the corrections, postgres processes average more closely to < 1%
then before.

This is not stopping the system from getting high load averages.
Attached, is an example of the site running at 160 users with very slow
response rates (30 seconds for some scripts).   According to top, and ps
nothing is taking up all that processing time.

The processor seems to be purposely sitting there twiddling it's thumbs.
  Which leads me to believe that perhaps the nice levels have to be
changed on the server itself?    And perhaps increase the file system
buffer to cache files in memory instead of always fetching/writing them?

Anyone more ideas?

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

--- top ---
load averages:  5.00,  4.72,  3.75                                                                             21:45:56
134 processes: 6 running, 128 idle
CPU states: 91.7% user,  0.0% nice,  6.6% system,  1.6% interrupt,  0.2% idle
Memory: Real: 279M/390M act/tot  Free: 617M  Swap: 0K/2048M used/tot

  PID USERNAME PRI NICE  SIZE   RES STATE WAIT     TIME    CPU COMMAND
23235 postgres  64    0   12M   95M run   -        0:02  9.42% postgres
 5299 postgres  64    0 3872K   57M run   -        0:01  5.86% postgres
 8933 postgres  64    0 3408K   55M run   -        0:01  5.47% postgres
16398 postgres   2    0 3776K   17M sleep netio    0:02  0.05% postgres
14007 named      2    0 2528K 2572K sleep select   0:06  0.00% named
 3684 postgres   2    0 2120K 4812K sleep select   0:07  0.00% postgres
23518 postgres   2    0 3664K   36M sleep netio    0:03  0.00% postgres
  571 postgres   2    0 3776K   51M sleep netio    0:03  0.00% postgres
11159 postgres   2    0 3664K   35M sleep netio    0:03  0.00% postgres
19184 postgres   2    0 3776K   16M sleep netio    0:03  0.00% postgres
28931 postgres   2    0 3712K   16M sleep netio    0:02  0.00% postgres
17523 postgres   2    0 3712K   14M sleep netio    0:02  0.00% postgres
 8272 postgres   2    0 3712K   14M sleep netio    0:02  0.00% postgres
12034 postgres   2    0 3712K   14M sleep netio    0:02  0.00% postgres
30825 postgres   2    0 3776K   17M sleep netio    0:02  0.00% postgres
29173 postgres   2    0 3712K   15M sleep netio    0:02  0.00% postgres
 9472 postgres   2    0 3664K   34M sleep netio    0:02  0.00% postgres
11542 postgres   2    0 3776K   16M sleep netio    0:02  0.00% postgres


--- vmstat ---
 procs   memory        page                    disks     faults     cpu
 r b w    avm    fre   flt  re  pi  po  fr  sr wd0 wd1   in    sy   cs us sy id
 1 0 0 275352 642800  8173   0   0   0   0   0  18  21  534   853  243 50  5 45

--- 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   74 13.00  18 0.23  10.08  21 0.21  50  0  4  1 45

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

--- dmesg ---
 OpenBSD 3.3-stable (compile) #2: Sat Jul  5 15:17:30 MDT 2003
    martin@carme:/usr/src/sys/arch/i386/compile
 cpu0: Intel Pentium III (Coppermine) ("GenuineIntel" 686-class) 1 GHz
 cpu0: FPU,V86,DE,PSE,TSC,MSR,PAE,MCE,CX8,SYS,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SIMD
 real mem  = 1073250304 (1048096K)
 avail mem = 992940032 (969668K)
 using 4278 buffers containing 53764096 bytes (52504K) of memory
 mainbus0 (root)
 bios0 at mainbus0: AT/286+(a1) BIOS, date 07/20/01, BIOS32 rev. 0 @ 0xf0b20
 apm0 at bios0: Power Management spec V1.2
 apm0: AC on, battery charge unknown
 pcibios0 at bios0: rev. 2.1 @ 0xf0000/0x1382
 pcibios0: PCI IRQ Routing Table rev. 1.0 @ 0xf12d0/176 (9 entries)
 pcibios0: PCI Interrupt Router at 000:04:0 ("VIA VT82C586 PCI-ISA" rev 0x00)
 pcibios0: PCI bus #1 is the last bus
 bios0: ROM list: 0xc0000/0x8000 0xc8000/0x1800
 pci0 at mainbus0 bus 0: configuration mode 1 (no bios)
 pchb0 at pci0 dev 0 function 0 "VIA VT82C691 Host-PCI" rev 0xc4
 ppb0 at pci0 dev 1 function 0 "VIA VT82C598 PCI-AGP" rev 0x00
 pci1 at ppb0 bus 1
 vga1 at pci1 dev 0 function 0 "NVidia/SGS-Thomson Velocity128" rev 0x22
 wsdisplay0 at vga1: console (80x25, vt100 emulation)
 wsdisplay0: screen 1-5 added (80x25, vt100 emulation)
 pcib0 at pci0 dev 4 function 0 "VIA VT82C686 PCI-ISA" rev 0x40
 pciide0 at pci0 dev 4 function 1 "VIA VT82C571 IDE" rev 0x06: ATA100, channel 0 configured to compatibility, channel 1
configuredto compatibility 
 wd0 at pciide0 channel 0 drive 0: <FUJITSU MPG3204AT E>
 wd0: 16-sector PIO, LBA, 19546MB, 16383 cyl, 16 head, 63 sec, 40031712 sectors
 wd0(pciide0:0:0): using PIO mode 4, Ultra-DMA mode 5
 wd1 at pciide0 channel 1 drive 0: <FUJITSU MPF3204AT>
 wd1: 16-sector PIO, LBA, 19546MB, 16383 cyl, 16 head, 63 sec, 40031712 sectors
 atapiscsi0 at pciide0 channel 1 drive 1
 scsibus0 at atapiscsi0: 2 targets
 scsibus0 targ 0 lun 0: <LG, CD-ROM CRD-8522B, 2.03> SCSI0 5/cdrom removable not configured
 wd1(pciide0:1:0): using PIO mode 4, Ultra-DMA mode 4
 atapiscsi0(pciide0:1:1): using PIO mode 4, DMA mode 2
 "VIA VT82C686 SMBus" rev 0x40 at pci0 dev 4 function 4 not configured
 fxp0 at pci0 dev 10 function 0 "Intel 82557" rev 0x0c: irq 11, address 00:02:b3:8c:4f:db
 inphy0 at fxp0 phy 1: i82555 10/100 media interface, rev. 4
 isa0 at pcib0
 isadma0 at isa0
 pckbc0 at isa0 port 0x60/5
 pckbd0 at pckbc0 (kbd slot)
 pckbc0: using irq 1 for kbd slot
 wskbd0 at pckbd0: console keyboard, using wsdisplay0
 pcppi0 at isa0 port 0x61
 sysbeep0 at pcppi0
 npx0 at isa0 port 0xf0/16: using exception 16
 biomask c000 netmask c800 ttymask c802
 pctr: 686-class user-level performance counters enabled
 mtrr: Pentium Pro MTRR support
 dkcsum: wd0 matched BIOS disk 80
 dkcsum: wd1 matched BIOS disk 81
 root on wd0a
 rootdev=0x0 rrootdev=0x300 rawdev=0x302