Message-ID: <4F46A839.6040605@squeakycode.net>
Date: Thu, 23 Feb 2012 14:57:29 -0600
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
To: Alessandro Gagliardi <alessandro@path.com>
CC: Steve Crawford <scrawford@pinpointresearch.com>,
	Thom Brown <thom@linux.com>, pgsql-performance@postgresql.org
Subject: Re: set autovacuum=off
References: 
 <CAAB3BBLsZ94tTzaiTZrdpK+2m1waj8=VL31aPvOGFJ7oAGCHiQ@mail.gmail.com>
	<CAA-aLv4KL4atuaqJFi3BBWiRsWiJOVQZMk65JYw=H8ezZV6O8g@mail.gmail.com>
	<4F4658CB.3030505@squeakycode.net>
	<CAAB3BBK-nzheaG4Ju0snB7b3hqc2pZ+wV6vv9BPt81_k_cLcWw@mail.gmail.com>
	<4F467EDF.5090708@pinpointresearch.com>
	<CAAB3BBKU4f3QqNMmjYeuZFkOhp6iP+2aMhLWODQBEMQt-+yCnA@mail.gmail.com>
	<4F468E5B.2070009@squeakycode.net>
	<CAAB3BBLUwzmqj516DDL54souKobh9o139AJzK8WCYPdgGZUELQ@mail.gmail.com>
In-Reply-To: 
 <CAAB3BBLUwzmqj516DDL54souKobh9o139AJzK8WCYPdgGZUELQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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