Insert speed new post

Поиск
Список
Период
Сортировка
От Terry Fielder
Тема Insert speed new post
Дата
Msg-id 46895FB9.6050701@ashtonwoodshomes.com
обсуждение исходный текст
Ответы Re: Insert speed new post  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085


В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Restart after poweroutage
Следующее
От: Tom Lane
Дата:
Сообщение: Re: recovery_target_time ignored or recoveryalwaysrecovers to end of WAL