My first posting stalled because I posted from the wrong email account,
here is the new posting, plus some more info:
I have a user application use log.
Under pg 7.x the system performed fine.
In 8.1.9, the insert statements seem to take a long time sometimes, upto
several seconds or more.
Here is the table:
CREATE TABLE user_app_use_log
(
user_id int4 NOT NULL,
access_stamp timestamptz NOT NULL DEFAULT now(),
app_name char(50) NOT NULL,
url char(150) NOT NULL,
form_params text,
division_id char(3),
url_params text,
ip_address varchar(31)
)
WITHOUT OIDS;
There is no primary key, but the table is never updated, only inserted.
I removed the only index, with no improvement in performance (in case
the 8.2 "resolves index locking issues" was the concern for an 8.1 install.
Should I add a primary key column of serial? Will that help?
If anyone has any ideas it would be appreciated.
And in addition, I do a regular vacuum analyze, and to be clear this
table has 948851 and rising records. I USED to purge the table
regularly, but not since SOX came around. (But that happened prior to
my upgrade from 7.4 to 8.1)
The server is a very powerful 8 CPU on SCSI Raid.
iostat tells me its not backlogged on disk IO:
avg-cpu: %user %nice %system %iowait %steal %idle
6.54 0.00 0.66 1.31 0.00 91.49
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
hda 0.51 2.02 22.36 1292832 14285368
sda 0.00 0.01 0.00 4585 2552
sdb 0.65 4.66 7.39 2975813 4720552
sdc 40.37 384.92 1072.08 245922466 684946704
sdd 0.34 0.00 7.39 248 4720552
sde 40.27 389.03 1066.04 248548400 681086784
sdf 40.21 385.00 1072.58 245976056 685265296
dm-0 1.26 4.66 7.39 2975581 4720552
dm-1 0.00 0.00 0.00 1662 128
dm-2 1.26 4.65 7.39 2973050 4720424
hdd 0.00 0.00 0.00 140 0
md0 230.85 373.72 1783.57 238766922 1139514032
And top tells me minimal cpu load:
top - 16:28:55 up 7 days, 9:30, 2 users, load average: 2.61, 2.82, 2.86
Tasks: 220 total, 1 running, 219 sleeping, 0 stopped, 0 zombie
Cpu0 : 2.3%us, 2.0%sy, 0.0%ni, 95.7%id, 0.0%wa, 0.0%hi, 0.0%si,
0.0%st
Cpu1 : 2.0%us, 3.0%sy, 0.0%ni, 91.0%id, 2.3%wa, 0.7%hi, 1.0%si,
0.0%st
Cpu2 : 0.0%us, 0.3%sy, 0.0%ni, 89.4%id, 10.3%wa, 0.0%hi, 0.0%si,
0.0%st
Cpu3 : 0.0%us, 0.3%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si,
0.0%st
Cpu4 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si,
0.0%st
Cpu5 : 4.3%us, 0.3%sy, 0.0%ni, 95.0%id, 0.3%wa, 0.0%hi, 0.0%si,
0.0%st
Cpu6 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si,
0.0%st
Cpu7 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si,
0.0%st
Mem: 15894024k total, 15527992k used, 366032k free, 323760k buffers
Swap: 17880304k total, 1084k used, 17879220k free, 13912888k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
20914 postgres 15 0 200m 93m 90m S 4 0.6 1:14.89 postmaster
20014 postgres 15 0 200m 93m 90m S 4 0.6 2:55.08 postmaster
2389 root 10 -5 0 0 0 S 3 0.0 33:46.72 md0_raid5
15111 postgres 15 0 209m 102m 90m S 2 0.7 25:32.37 postmaster
2577 root 10 -5 0 0 0 D 1 0.0 22:59.43 kjournald
4949 root 15 0 12996 1336 792 S 1 0.0 38:54.10 top
--
Terry Fielder
terry@xxxxxxxxxxxxxxxxxx
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085
