Re: set autovacuum=off

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