Thread: Extreme high load averages
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
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
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.
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
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
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
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
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
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
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
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
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.
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
> 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/
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
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
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.
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
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)
> 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
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
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