Search Postgresql Archives

Insert speed new post

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux