Re: Can lots of small writes badly hamper reads from other tables?

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