Skip site navigation (1) Skip section navigation (2)

Re: set autovacuum=off

From: Andy Colson <andy(at)squeakycode(dot)net>
To: Alessandro Gagliardi <alessandro(at)path(dot)com>
Cc: Steve Crawford <scrawford(at)pinpointresearch(dot)com>, Thom Brown <thom(at)linux(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: set autovacuum=off
Date: 2012-02-23 20:57:29
Message-ID: 4F46A839.6040605@squeakycode.net (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Alessandro GagliardiDate: 2012-02-23 21:07:46
Subject: Re: set autovacuum=off
Previous:From: Alessandro GagliardiDate: 2012-02-23 20:40:45
Subject: Re: set autovacuum=off

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group