From: Andy Colson <andy@squeakycode.net>
To: Alessandro Gagliardi <alessandro@path.com>
Cc: Steve Crawford <scrawford@pinpointresearch.com>
Cc: Thom Brown <thom@linux.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: set autovacuum=off
Date: Thu, 23 Feb 2012 14:57:29 -0600
Message-ID: <4F46A839.6040605@squeakycode.net> (raw)
In-Reply-To: <CAAB3BBLUwzmqj516DDL54souKobh9o139AJzK8WCYPdgGZUELQ@mail.gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, alessandro@path.com, scrawford@pinpointresearch.com, thom@linux.com
Subject: Re: set autovacuum=off
In-Reply-To: <4F46A839.6040605@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox