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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
