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: recovery_target_time ignored or recoveryalwaysrecovers to end of WAL