From: Andy Colson <andy@squeakycode.net>
To: Dave Crooke <dcrooke@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Can lots of small writes badly hamper reads from other tables?
Date: Tue, 24 Jan 2012 15:06:41 -0600
Message-ID: <4F1F1D61.7060201@squeakycode.net> (raw)
In-Reply-To: <CALi4UpiWCoaC4OOCKo_s34gket2Bpv_ZVET62N4TuEn33K+TTw@mail.gmail.com>
References: <CALi4UpiWCoaC4OOCKo_s34gket2Bpv_ZVET62N4TuEn33K+TTw@mail.gmail.com>
On 1/24/2012 2:16 PM, Dave Crooke wrote:
> Hi folks
>
> This could be a sheer volume issue, but I though I would ask the wisdom
> of this forum as to next investigative steps.
>
> ----
>
> We use PostgreSQL 8.4.4 which is bundled with our application as a
> VMware virtual appliance. The bulk of the app's database activity is
> recording performance data points which arrive in farily large sustained
> bursts of perhaps 10,000 rows a minute at a medium sized customer, each
> of which are logically separate items and being committed as individual
> transactions (JDBC auto-commit mode). Our offshore QA team was assigned
> to track an intermittent issue with speed of some large queries on other
> tables, and they believe based on correlation the two activities may be
> contending.
You have 10 connections, all doing:
begin
insert into PERF_RAW_2012_01_24.... -- one record
commit
If that's what you're doing, yes, I'd say that's the slowest way possible.
Doing this would be faster:
begin
insert into PERF_RAW_2012_01_24.... -- one record
insert into PERF_RAW_2012_01_24.... -- one record
...
insert into PERF_RAW_2012_01_24.... -- one record
commit
Doing this would be even faster:
begin
-- one insert, multiple rows
insert into PERF_RAW_2012_01_24 values (...) (...) (...) ... (...);
insert into PERF_RAW_2012_01_24 values (...) (...) (...) ... (...);
commit
And, fastest of all fastest, use COPY. But be careful, its so fast
it'll melt your face off :-)
I didnt even bother trying to pick out the uncommented settings from
your .conf file. Way to much work.
VM usually have pretty slow IO, so you might wanna watch vmstat and
iostat to see if you are IO bound or CPU bound.
Also watching iostat before and after the change might be interesting.
If you you keep having lots and lots of transaction, look into
commit_delay, it'll help batch commits out to disk (if I remember
correctly).
-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, dcrooke@gmail.com
Subject: Re: Can lots of small writes badly hamper reads from other tables?
In-Reply-To: <4F1F1D61.7060201@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