On 2/23/2012 2:40 PM, Alessandro Gagliardi wrote:
checkpoint_segments can help insert speed, what do you have that set to?
40. Checking
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server it looks
like setting that as high as 256 would not necessarily be unreasonable.
What do you think?
I'd say go slow. Try a little bit and see if it helps. I don't
actually have high insert rate problems, so I don't actually know from
experience.
Also how you insert can make things faster too. (insert vs prepared
vs COPY)
I'm doing this all with INSERT. Is COPY that much faster? I don't know
anything about prepared.
If you can batch multiple records then COPY is the fastest method. (Of
course your triggers might be the cause for the slowness and not insert
speed).
Depending on the language you are using to insert records, you can
prepare a query and only send the arguments vs sending the entire sql
statement every time.
In pseudo-perl code I'd:
my $q = $db->prepare('insert into table(col1, vol2) values ($1, $2)');
$q->execute('one', 'two');
$q->execute('three', 'four');
$q->execute('five', 'six');
This is faster because the "insert..." is only sent over the wire and
parsed once. Then only the arguments are sent for each execute.
Speed wise, I think it'll go:
1) slowest: individual insert statements
2) prepared statements
3) fastest: COPY
Again.. assuming the triggers are not the bottleneck.
Have you run an insert by hand with 'EXPLAIN ANALYZE'?
-Andy
Have you read up on synchronous_commit?
Only a tiny bit. A couple people suggested disabling it since my
database is being hosted on AWS so I did that. It seems a bit risky but
perhaps worth it.
I would think they are running on battery backed IO, with boxes on UPS,
so I'd guess its pretty safe. It would also depend on your commit size.
If you are batching a million records into one commit, you might loose
all of them.
-Andy
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
