Thread: Extreme high load averages

Extreme high load averages

From
Martin Foster
Date:
The only time that I have ever seen load averages of 30 or more under
OpenBSD is when one of my scripts goes wild.    However, I can say that
I am also seeing these load averages under PostgreSQL 7.3.2 after a
migration to it from MySQL.

MySQL Statistics:
Uptime: 1055352  Threads: 178  Questions: 75161710  Slow queries: 46
Opens: 1084  Flush tables: 1  Open tables: 206 Queries per second avg:
71.220

The above are statistics from older generation scripts that would make
use of MySQL as to give an idea of what's going on.   That generation of
scripts would handle the referential integrity, since foreign key
constraints are not enforced under that system.   However, the system
handled 250 concurrent users without a singular problem, while under
Postgres with new scripts using functions, referential integrity,
transactions and lighter code, the system starts to buckle at even less
then 70 users.

What I would like to know is.   Why?   The kernel has been compiled to
handle the number of concurrent connections, the server may not be the
best, but it should be able to handle the requests: PIII 1Ghz, 1GB
SDRAM, 2 IDE 20GB drives.

I have changed settings to take advantage of the memory.  So the
following settings are of interest:
    shared_buffers = 16384
    wal_buffers = 256
    sort_mem = 16384
    vacuum_mem = 32768

Statistics gathering has now been disabled, and logging is done through
syslog.    I do not expect those settings to cripple system performance
however.

The scripts are heavy SELECTS with a fair dose of UPDATES and INSERTS.
  To get a concept of what these scripts done, you can look at Ethereal
Realms (http://www.ethereal-realms.org) which are running the PostgreSQL
script variants or consider that this is a chat site.

Anyone have ideas?   Is the use of connection pooling consider bad?
Should flush be run more then once a day? I have no intention of going
back to MySQL, and would like to make this new solution work.

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



Re: Extreme high load averages

From
Richard Huxton
Date:
On Sunday 06 Jul 2003 5:54 am, Martin Foster wrote:
> The only time that I have ever seen load averages of 30 or more under
> OpenBSD is when one of my scripts goes wild.    However, I can say that
> I am also seeing these load averages under PostgreSQL 7.3.2 after a
> migration to it from MySQL.
[snip]
> However, the system
> handled 250 concurrent users without a singular problem, while under
> Postgres with new scripts using functions, referential integrity,
> transactions and lighter code, the system starts to buckle at even less
> then 70 users.
[snip]
> PIII 1Ghz, 1GB
> SDRAM, 2 IDE 20GB drives.
>
> I have changed settings to take advantage of the memory.  So the
> following settings are of interest:
>     shared_buffers = 16384
>     wal_buffers = 256
>     sort_mem = 16384
>     vacuum_mem = 32768

You do know that sort_mem is in kB per sort (not per connection, but per sort
being done by a connection). That's 16MB per sort you've allowed in main
memory, or for 70 concurrent sorts up to 1.1GB of memory allocated to
sorting. You're not going into swap by any chance?

Might want to try halving shared_buffers too and see what happens.

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)

From that lot, someone will be able to point towards the issue, I'm sure.
--
  Richard Huxton

Re: Extreme high load averages

From
"Shridhar Daithankar"
Date:
On 5 Jul 2003 at 22:54, Martin Foster wrote:
> What I would like to know is.   Why?   The kernel has been compiled to
> handle the number of concurrent connections, the server may not be the
> best, but it should be able to handle the requests: PIII 1Ghz, 1GB
> SDRAM, 2 IDE 20GB drives.
>
> I have changed settings to take advantage of the memory.  So the
> following settings are of interest:
>     shared_buffers = 16384
>     wal_buffers = 256
>     sort_mem = 16384
>     vacuum_mem = 32768

As somebody else has already pointed out, your sort_mem is bit too high
than required. Try lowering it.

Secondly did you tune effective_cache_size?

HTH
Bye
 Shridhar

--
Power, n.:    The only narcotic regulated by the SEC instead of the FDA.


Re: Extreme high load averages

From
Martin Foster
Date:
Shridhar Daithankar wrote:

> On 5 Jul 2003 at 22:54, Martin Foster wrote:
>
>>What I would like to know is.   Why?   The kernel has been compiled to
>>handle the number of concurrent connections, the server may not be the
>>best, but it should be able to handle the requests: PIII 1Ghz, 1GB
>>SDRAM, 2 IDE 20GB drives.
>>
>>I have changed settings to take advantage of the memory.  So the
>>following settings are of interest:
>>    shared_buffers = 16384
>>    wal_buffers = 256
>>    sort_mem = 16384
>>    vacuum_mem = 32768
>
>
> As somebody else has already pointed out, your sort_mem is bit too high
> than required. Try lowering it.
>
> Secondly did you tune effective_cache_size?
>
> HTH
> Bye
>  Shridhar
>
> --
> Power, n.:    The only narcotic regulated by the SEC instead of the FDA.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

I dropped the size of the sort_mem down to 8 megs.   Since I am not
swapping to cache at all this should not post much of a problem at that
value.

effective_cache_size seems interesting, though the description is
somewhat lacking.  Is this related to the swap partition and how much of
it will be used by PostgreSQL?   If I am correct, this should be fairly low?
    Martin Foster
    Creator/Designer Ethereal Realms
    martin@ethereal-realms.org



Re: Extreme high load averages

From
Martin Foster
Date:
Richard Huxton wrote:

> On Sunday 06 Jul 2003 5:54 am, Martin Foster wrote:
>
>>The only time that I have ever seen load averages of 30 or more under
>>OpenBSD is when one of my scripts goes wild.    However, I can say that
>>I am also seeing these load averages under PostgreSQL 7.3.2 after a
>>migration to it from MySQL.
>
> [snip]
>
>>However, the system
>>handled 250 concurrent users without a singular problem, while under
>>Postgres with new scripts using functions, referential integrity,
>>transactions and lighter code, the system starts to buckle at even less
>>then 70 users.
>
> [snip]
>
>>PIII 1Ghz, 1GB
>>SDRAM, 2 IDE 20GB drives.
>>
>>I have changed settings to take advantage of the memory.  So the
>>following settings are of interest:
>>    shared_buffers = 16384
>>    wal_buffers = 256
>>    sort_mem = 16384
>>    vacuum_mem = 32768
>
>
> You do know that sort_mem is in kB per sort (not per connection, but per sort
> being done by a connection). That's 16MB per sort you've allowed in main
> memory, or for 70 concurrent sorts up to 1.1GB of memory allocated to
> sorting. You're not going into swap by any chance?
>
> Might want to try halving shared_buffers too and see what happens.
>
> 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)
>
>From that lot, someone will be able to point towards the issue, I'm sure.

Actually, no I did not.   Which is probably why it was as high as it is.
   When looking at the PostgreSQL Hardware Performance Tuning page, it
seems to imply that you should calculate based on RAM to give it an
appropriate value.

  http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node8.html

I dropped that value, and will see if that helps.   The thing is, the
system always indicated plenty of memory available.   Even when at a 30
load level the free memory was still roughly 170MB.

Tomorrow will be a good gage to see if the changes will actually help
matters.    And if they do not, I will include vmstat, iostat, and top
as requested.

Thanks!

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



Re: Extreme high load averages

From
Shridhar Daithankar
Date:
On Sunday 06 July 2003 15:56, Martin Foster wrote:
> effective_cache_size seems interesting, though the description is
> somewhat lacking.  Is this related to the swap partition and how much of
> it will be used by PostgreSQL?   If I am correct, this should be fairly
> low? Martin Foster

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


Re: Extreme high load averages

From
Tom Lane
Date:
Martin Foster <martin@ethereal-realms.org> writes:
>> The only time that I have ever seen load averages of 30 or more under
>> OpenBSD is when one of my scripts goes wild.

Note also that "high load average" is not per se an indication that
anything is wrong.  In Postgres, if you have thirty queries waiting
for disk I/O, that's thirty processes --- so if that's the average
state then the kernel will report a load average of thirty.  While
I'm no MySQL expert, I believe that the equivalent condition in MySQL
would be thirty threads blocked for I/O within one process.  Depending
on how your kernel is written, that might show as a load average of
one ... but the difference is completely illusory, because what counts
is the number of disk I/Os in flight, and that's the same.

You didn't say whether you were seeing any real performance problems,
like slow queries or performance dropping when query load rises, but
that is the aspect to concentrate on.

I concur with the nearby recommendations to drop your resource settings.
The thing you have to keep in mind about Postgres is that it likes to
have a lot of physical RAM available for kernel disk buffers (disk
cache).  In a correctly tuned system that's been up for any length of
time, "free memory" should be nearly nada, and the amount of RAM used
for disk buffers should be sizable (50% or more of RAM would be good
IMHO).

            regards, tom lane

Re: Extreme high load averages

From
Martin Foster
Date:
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



Re: Extreme high load averages

From
Martin Foster
Date:
Tom Lane wrote:

> Martin Foster <martin@ethereal-realms.org> writes:
>
>>>The only time that I have ever seen load averages of 30 or more under
>>>OpenBSD is when one of my scripts goes wild.
>
>
> Note also that "high load average" is not per se an indication that
> anything is wrong.  In Postgres, if you have thirty queries waiting
> for disk I/O, that's thirty processes --- so if that's the average
> state then the kernel will report a load average of thirty.  While
> I'm no MySQL expert, I believe that the equivalent condition in MySQL
> would be thirty threads blocked for I/O within one process.  Depending
> on how your kernel is written, that might show as a load average of
> one ... but the difference is completely illusory, because what counts
> is the number of disk I/Os in flight, and that's the same.
>
> You didn't say whether you were seeing any real performance problems,
> like slow queries or performance dropping when query load rises, but
> that is the aspect to concentrate on.
>
> I concur with the nearby recommendations to drop your resource settings.
> The thing you have to keep in mind about Postgres is that it likes to
> have a lot of physical RAM available for kernel disk buffers (disk
> cache).  In a correctly tuned system that's been up for any length of
> time, "free memory" should be nearly nada, and the amount of RAM used
> for disk buffers should be sizable (50% or more of RAM would be good
> IMHO).
>
>             regards, tom lane

Under a circumstance where we have 250 concurrent users, MySQL would
report an uptime of 0.5 sometimes 0.8 depending on the tasks being
performed.

This would translate to wait times averaging less then a second, and
under a heavy resource script 4 seconds.    That system had less RAM
however.

This new system when showing a load average of 30, produced wait times
of 12 seconds averages and about 30 seconds for the heavy resource
script.     The web server itself showed a load average of 0.5 showing
that it was not heavy client interaction slowing things down.

So there is a very noticeable loss of performance when the system
skyrockets like that.   All of the load as indicated by top is at user
level, and not swap is even touched.

This may help show why I was slightly concerned.

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



Re: Extreme high load averages

From
Martin Foster
Date:
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

Re: Extreme high load averages

From
Dennis Björklund
Date:
On Sun, 6 Jul 2003, Martin Foster wrote:

> 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?

It could also be all the usual things that affect performance. Are your
queries using indexes where it should? Do you vacuum analyze after you
have updated/inserted a lot of data?

It could be that some of your queries is not as efficient as it should,
like doing a sequenctial scan over a table instead of an index scan. That
translates into more IO needed and slower response times. Especially when
you have more connections figthing for the available IO.

--
/Dennis


Re: Extreme high load averages

From
"scott.marlowe"
Date:
On Sun, 6 Jul 2003, Martin Foster wrote:

> 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.

I would try a few things.  First off, effective_cache_size is the size
measured in 8k blocks, so 512 would be a setting of 4 Megs.  Probably a
little low.  If you average 512Meg free, that would be a setting of 65536.

Note that the higer the effective_cache_size, the more the planner will
favor index scans, and the lower, the more it will favor sequential scans.

Generally speaking, index scans cost in CPU terms, while seq scans cost in
I/O time.

Since you're reporting low CPU usage, I'm guessing you're getting a lot of
seq scans.

Do you have any type mismatches anywhere that could be the culprit?
running vacuum and analyze regurlarly?  Any tables that are good
candidates for clustering?

A common problem is a table like this:

create table test (info text, id int8 primary key);
insert into test values ('ted',1);
.. a few thousand more inserts;
vacuum full;
analyze;
select * from test where id=1;

will result in a seq scan, always, because the 1 by itself is
autoconverted to int4, which doesn't match int8 automatically.  This
query:

select * from test where id=1::int8

will cast the 1 to an int8 so the index can be used.


Re: Extreme high load averages

From
Martin Foster
Date:
scott.marlowe wrote:
>
>
> I would try a few things.  First off, effective_cache_size is the size
> measured in 8k blocks, so 512 would be a setting of 4 Megs.  Probably a
> little low.  If you average 512Meg free, that would be a setting of 65536.
>
> Note that the higer the effective_cache_size, the more the planner will
> favor index scans, and the lower, the more it will favor sequential scans.
>
> Generally speaking, index scans cost in CPU terms, while seq scans cost in
> I/O time.
>
> Since you're reporting low CPU usage, I'm guessing you're getting a lot of
> seq scans.
>
> Do you have any type mismatches anywhere that could be the culprit?
> running vacuum and analyze regurlarly?  Any tables that are good
> candidates for clustering?
>
> A common problem is a table like this:
>
> create table test (info text, id int8 primary key);
> insert into test values ('ted',1);
> .. a few thousand more inserts;
> vacuum full;
> analyze;
> select * from test where id=1;
>
> will result in a seq scan, always, because the 1 by itself is
> autoconverted to int4, which doesn't match int8 automatically.  This
> query:
>
> select * from test where id=1::int8
>
> will cast the 1 to an int8 so the index can be used.
>

That last trick actually listed seemed to have solved on the larger
slowdowns I had.   It would seem that a view was making use of INTERVAL
and CURRENT_TIMESTAMP.   However, the datatype did not make use of
timezones and that caused significant slowdowns.

By using ::TIMESTAMP, it essentially dropped the access time from 4.98+
to 0.98 seconds.   This alone makes my day, as it shows that Postgres is
performing well, but is just a bit more picky about the queries.

I changed the settings as you recommended, locked the memory to 768 megs
so that PostgreSQL cannot go beyond that and made the database priority
higher.   All of those changes seems to have increase overall performance.

I do have a site question:

   ENABLE_HASHJOIN (boolean)
   ENABLE_INDEXSCAN (boolean)
   ENABLE_MERGEJOIN (boolean)
   ENABLE_TIDSCAN (boolean)

All of the above, state that they are for debugging the query planner.
  Does this mean that disabling these reduces debugging overhead and
streamlines things?   The documentation is rather lacking for
information on these.

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



Re: Extreme high load averages

From
"Matthew Nuzum"
Date:
> A common problem is a table like this:
>
> create table test (info text, id int8 primary key);
> insert into test values ('ted',1);
> .. a few thousand more inserts;
> vacuum full;
> analyze;
> select * from test where id=1;
>
> will result in a seq scan, always, because the 1 by itself is
> autoconverted to int4, which doesn't match int8 automatically.  This
> query:
>
> select * from test where id=1::int8
>
> will cast the 1 to an int8 so the index can be used.
>
>

Hey Scott, this is a little scary because I probably have a lot of this
going on...

Is there a way to log something so that after a day or so I can go back and
look for things like this that would be good candidates for optimization?

I've got fast enough servers that currently the impact of this problem might
not be too obvious, but I suspect that after the server gets loaded up the
impact will become more of a problem.

By the way, I must say that this thread has been very useful.

Matthew Nuzum        | Makers of "Elite Content Management System"
www.followers.net        | View samples of Elite CMS in action
matt@followers.net    | http://www.followers.net/portfolio/


Re: Extreme high load averages

From
"scott.marlowe"
Date:
On Mon, 7 Jul 2003, Matthew Nuzum wrote:

> > A common problem is a table like this:
> >
> > create table test (info text, id int8 primary key);
> > insert into test values ('ted',1);
> > .. a few thousand more inserts;
> > vacuum full;
> > analyze;
> > select * from test where id=1;
> >
> > will result in a seq scan, always, because the 1 by itself is
> > autoconverted to int4, which doesn't match int8 automatically.  This
> > query:
> >
> > select * from test where id=1::int8
> >
> > will cast the 1 to an int8 so the index can be used.
> >
> >
>
> Hey Scott, this is a little scary because I probably have a lot of this
> going on...
>
> Is there a way to log something so that after a day or so I can go back and
> look for things like this that would be good candidates for optimization?
>
> I've got fast enough servers that currently the impact of this problem might
> not be too obvious, but I suspect that after the server gets loaded up the
> impact will become more of a problem.
>
> By the way, I must say that this thread has been very useful.

Well, you can turn on some of the newer logging features that tell you how
long the query took to run.

Look here:

http://developer.postgresql.org/docs/postgres/runtime-config.html#RUNTIME-CONFIG-LOGGING

and here:

http://developer.postgresql.org/docs/postgres/runtime-config.html#RUNTIME-CONFIG-STATISTICS

Note that those are the developer docs.  I'm pretty sure the first one has
a corrolary to the 7.3.x docs, but the second set (log_statement_stats,
parser_stats, etc...) looks new for 7.4


Re: [NOVICE] Extreme high load averages

From
Martin Foster
Date:
Dennis Björklund wrote:

> On Sun, 6 Jul 2003, Martin Foster wrote:
>
>
>>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?
>
>
> It could also be all the usual things that affect performance. Are your
> queries using indexes where it should? Do you vacuum analyze after you
> have updated/inserted a lot of data?
>
> It could be that some of your queries is not as efficient as it should,
> like doing a sequenctial scan over a table instead of an index scan. That
> translates into more IO needed and slower response times. Especially when
> you have more connections figthing for the available IO.
>

I actually got a bit more respect for PostgreSQL tonight.  It seems that
one of my scripts was not committing changes after maintenance was
conducted.  Meaning that rows that would normally be removed after
offline archiving was completed were in fact still around.

Normally at any given point in time this table would grow 50K rows
during a day, be archived that night and then loose rows that were no
longer needed.    This process, is what allowed MySQL to maintain any
stability as the size of this table can balloon significantly.

PostgreSQL with tweaking was handling a table with nearly 300K rows.
That size alone would of dragged the MySQL system down to a near grind,
and since most of those rows are not needed.   One can imagine that
queries are needlessly processing rows that should be outright ignored.

This probably explains why row numbering based searches greatly
accelerated the overall process.

By fixing the script and doing the appropriate full vacuum and re-index,
the system is behaving much more like it should.  Even if the process
may seem a bit odd to some.

The reason for removing rows on a daily basis is due to the perishable
nature of the information.  Since this is a chat site, posts over a day
old are rarely needed for any reason.   Which is why they are archived
into dumps in case we really need to retrieve the information itself and
this gives us the added bonus of smaller backup sizes and smaller
database sizes.

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



Re: [NOVICE] Extreme high load averages

From
"Shridhar Daithankar"
Date:
On 9 Jul 2003 at 23:37, Martin Foster wrote:

> By fixing the script and doing the appropriate full vacuum and re-index,
> the system is behaving much more like it should.  Even if the process
> may seem a bit odd to some.
>
> The reason for removing rows on a daily basis is due to the perishable
> nature of the information.  Since this is a chat site, posts over a day
> old are rarely needed for any reason.   Which is why they are archived
> into dumps in case we really need to retrieve the information itself and
> this gives us the added bonus of smaller backup sizes and smaller
> database sizes.

I have an idea.

How about creating a table for each day. Use it for a while and rename it.
Since you can rename a table in transaction, it should not be a problem.

You can use inheritance if you want to query all of them. Using indexes and
foregin keys on inherited tables is a problem though.

That way deletion would be avoided and so would vacuum. It should be mich
lighter on the system overall as well.

Tell us if it works.

Bye
 Shridhar

--
Kaufman's Law:    A policy is a restrictive document to prevent a recurrence    of a
single incident, in which that incident is never mentioned.


Re: [NOVICE] Extreme high load averages

From
Martin Foster
Date:
Shridhar Daithankar wrote:
>
>
> I have an idea.
>
> How about creating a table for each day. Use it for a while and rename it.
> Since you can rename a table in transaction, it should not be a problem.
>
> You can use inheritance if you want to query all of them. Using indexes and
> foregin keys on inherited tables is a problem though.
>
> That way deletion would be avoided and so would vacuum. It should be mich
> lighter on the system overall as well.
>
> Tell us if it works.
>
> Bye
>  Shridhar
>


Generally I won't be pulling 250K rows from that table.   It's
maintained nightly during the general cleanup process where stale users,
rooms and posts are removed from the system.   Then the system runs a
normal VACUUM ANALYSE to get things going again smoothly.

Once a week a more detailed archiving takes place which runs an all out
vaccume and re-index.    That's the so called plan at least.

As for creating a new table, that in itself is a nice idea.   But it
would cause issues for people currently in the realm.   Their posts
would essentially dissapear from site and cause more confusion then its
worth.

Inheritance would work, but the database would essentially just grow and
grow and grow right?

BTW, I can't thank you all enough for this general advice.   It's
helping me get this thing running very smoothly.

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



Re: [NOVICE] Extreme high load averages

From
"Shridhar Daithankar"
Date:
On 10 Jul 2003 at 0:43, Martin Foster wrote:
> As for creating a new table, that in itself is a nice idea.   But it
> would cause issues for people currently in the realm.   Their posts
> would essentially dissapear from site and cause more confusion then its
> worth.

No they won't. Say you have a base table and your current post table is child
of that. You can query on base table and get rows from child table. That way
all the data would always be there.

While inserting posts, you would insert in child table. While qeurying you
would query on base table. That way things will be optimal.

> Inheritance would work, but the database would essentially just grow and
> grow and grow right?

Right. But there are two advantages.

1. It will always contain valid posts. No dead tuples.
2. You can work in chuncks of data. Each child table can be dealt with
separately without affecting other child tables, whereas in case of a single
large table, entire site is affected..

Deleting 100K posts from 101K rows table is vastly different than deleting 10K
posts from 2M rows table. Later one would unnecessary starve the table with
dead tuples and IO whereas in former case you can do create table as select
from and drop the original..

HTH

Bye
 Shridhar

--
"[In 'Doctor' mode], I spent a good ten minutes telling Emacs what Ithought of
it.  (The response was, 'Perhaps you could try to be lessabusive.')"(By Matt
Welsh)


Re: [NOVICE] Extreme high load averages

From
Sean Chittenden
Date:
> I actually got a bit more respect for PostgreSQL tonight.  It seems
> that one of my scripts was not committing changes after maintenance
> was conducted.  Meaning that rows that would normally be removed
> after offline archiving was completed were in fact still around.
>
> Normally at any given point in time this table would grow 50K rows
> during a day, be archived that night and then loose rows that were
> no longer needed.  This process, is what allowed MySQL to maintain
> any stability as the size of this table can balloon significantly.
>
> PostgreSQL with tweaking was handling a table with nearly 300K rows.
> That size alone would of dragged the MySQL system down to a near
> grind, and since most of those rows are not needed.  One can imagine
> that queries are needlessly processing rows that should be outright
> ignored.

Having used MySQL once upon a time and run into it's problems when you
have more than 1M rows in a table, it took me a while when 1st using
PostgreSQL to trust that PostgreSQL can reliably handle millions or
billions of rows without crapping out randomly and corrupting itself.
If you would have let this grow, you'd run out of disk space long
before you hit anything close to a stability, reliability, or
performance problem with PostgreSQL.  I have one table in particular
that has about 1.9B rows in it right now and it conservatively takes
about 0.04ms for non-complex queries to run against the table.  In
MySQL land, I wouldn't dare let something grow that big... which
would've been a huge problem because the table mentioned above isn't
logging data or something I can routinely purge.  It's a strange
feeling at first to not have to design your application around size or
tuple limitations of the database any more.  :) I'm glad you're
enjoying PostgreSQL.  -sc

--
Sean Chittenden

Re: [NOVICE] Extreme high load averages

From
Martin Foster
Date:
Shridhar Daithankar wrote:
> On 10 Jul 2003 at 0:43, Martin Foster wrote:
>
>>As for creating a new table, that in itself is a nice idea.   But it
>>would cause issues for people currently in the realm.   Their posts
>>would essentially dissapear from site and cause more confusion then its
>>worth.
>
>
> No they won't. Say you have a base table and your current post table is child
> of that. You can query on base table and get rows from child table. That way
> all the data would always be there.
>
> While inserting posts, you would insert in child table. While qeurying you
> would query on base table. That way things will be optimal.
>
>
>>Inheritance would work, but the database would essentially just grow and
>>grow and grow right?
>
>
> Right. But there are two advantages.
>
> 1. It will always contain valid posts. No dead tuples.
> 2. You can work in chuncks of data. Each child table can be dealt with
> separately without affecting other child tables, whereas in case of a single
> large table, entire site is affected..
>
> Deleting 100K posts from 101K rows table is vastly different than deleting 10K
> posts from 2M rows table. Later one would unnecessary starve the table with
> dead tuples and IO whereas in former case you can do create table as select
> from and drop the original..
>
> HTH
>
> Bye
>  Shridhar

While your idea is sound, I can easily report that this is as bad or
even worse then removing thousands of rows at any given point in time.
  Trying to remove a child table, will pretty much guarantee a complete
and total deadlock in the database.

While it's waiting for a lock, it's locking out authenticating users but
allows existing connections to go through.  And considering this goes on
for tens of minutes and people keep piling on requests to the server,
this quickly disintegrates into one hell of a mess.  I.E. requires a
cold boot to get this thing up again.

Perhaps it is more efficient, but until I can remove archived tables
entirely, I do not exactly see a compelling reason to use inheritance.

Also, some questions are not answered from documentation.   Such as are
indexes carried forth, if you call the parent table, or do you have to
re-create them all manually.   And what happens to the primary key
constraints that no longer show up.

Thanks for the tip though.  Just wish it worked better then it does.

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



Re: [NOVICE] Extreme high load averages

From
Martin Foster
Date:
Shridhar Daithankar wrote:

> On 10 Jul 2003 at 0:43, Martin Foster wrote:
>
>>As for creating a new table, that in itself is a nice idea.   But it
>>would cause issues for people currently in the realm.   Their posts
>>would essentially dissapear from site and cause more confusion then its
>>worth.
>
>
> No they won't. Say you have a base table and your current post table is child
> of that. You can query on base table and get rows from child table. That way
> all the data would always be there.
>
> While inserting posts, you would insert in child table. While qeurying you
> would query on base table. That way things will be optimal.
>
>
>>Inheritance would work, but the database would essentially just grow and
>>grow and grow right?
>
>
> Right. But there are two advantages.
>
> 1. It will always contain valid posts. No dead tuples.
> 2. You can work in chuncks of data. Each child table can be dealt with
> separately without affecting other child tables, whereas in case of a single
> large table, entire site is affected..
>
> Deleting 100K posts from 101K rows table is vastly different than deleting 10K
> posts from 2M rows table. Later one would unnecessary starve the table with
> dead tuples and IO whereas in former case you can do create table as select
> from and drop the original..
>
> HTH
>
> Bye
>  Shridhar
>
> --
> "[In 'Doctor' mode], I spent a good ten minutes telling Emacs what Ithought of
> it.  (The response was, 'Perhaps you could try to be lessabusive.')"(By Matt
> Welsh)
>

When I ran EXPLAIN on the views and queries making use of the inherited
tables, I noticed that everything worked based on sequence scans and it
avoided all indexes.    While making use of ONLY kicked in full indexes.

This is even after having created a child table with the same indexes as
the parent.   Is this a known issue, or just some sort of oddity on my
setup?

Tables still cannot be removed easily, but I found a way to work around
it for a day-to-day basis.  Essentailly I just clean out the tables
containing old rows and delete them later.  However based on the above,
I doubt performance would get any better.

Thanks for the advice however!

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