Thread: Insert speed new post
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
Terry Fielder <terry@ashtonwoodshomes.com> writes:
> 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.
> 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.
Hmm. With no indexes, inserts ought to be basically a constant-time
operation. I suspect what you are looking at is stalls caused by
checkpoints or other competing disk activity. I'd suggest watching the
output of "vmstat 1" or local equivalent, and seeing if you can
correlate the slow inserts with bursts of disk activity.
Have you tuned the 8.1 installation? I'm wondering about things like
checkpoint_segments and wal_buffers maybe being set lower than you had
them in 7.4.
regards, tom lane
Responses below.
Tom Lane wrote:
max_connections increased to 250
shared_buffers increased to 11000
The below I increased based on HINT's in the log file.
max_fsm_pages increased to 800000 (I have
max_fsm_relations to 1000
checkpoint_segments increased to 300
And much playing around with logging settings, done on my own.
#fsync = on # turns forced synchronization on or off
#wal_sync_method = fsync # the default is the first option
#full_page_writes = on # recover from partial page writes
#wal_buffers = 8 # min 4, 8KB each
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#increased by terry 20070402
checkpoint_segments = 300 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # in seconds, 0 is off
Any ideas based on this?
Thanks for your help.
Terry
Terry Fielder terry@greatgulfhomes.com Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085
Tom Lane wrote:
My sentiments exactly.Terry Fielder <terry@ashtonwoodshomes.com> writes: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.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.Hmm. With no indexes, inserts ought to be basically a constant-time operation.
There were warnings in the logs when I first deployed the 8.1 version. Sorry, I should have mentioned. Here are the postgresql.conf changes I made based on what I know I need from my 7.4 install:I suspect what you are looking at is stalls caused by checkpoints or other competing disk activity.
max_connections increased to 250
shared_buffers increased to 11000
The below I increased based on HINT's in the log file.
max_fsm_pages increased to 800000 (I have
max_fsm_relations to 1000
checkpoint_segments increased to 300
And much playing around with logging settings, done on my own.
I can do that, next peak period (tomorrow).I'd suggest watching the output of "vmstat 1" or local equivalent, and seeing if you can correlate the slow inserts with bursts of disk activity.
I have tweaked the settings based on HINT's as described above. Is there a document or something else you are referring to?Have you tuned the 8.1 installation?
I left the default in 8.1, which is:I'm wondering about things like checkpoint_segments and wal_buffers maybe being set lower than you had them in 7.4.
#fsync = on # turns forced synchronization on or off
#wal_sync_method = fsync # the default is the first option
#full_page_writes = on # recover from partial page writes
#wal_buffers = 8 # min 4, 8KB each
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#increased by terry 20070402
checkpoint_segments = 300 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # in seconds, 0 is off
Any ideas based on this?
Thanks for your help.
Terry
regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: 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