insert performance riddle

From: "Ed L(dot)" <pgsql(at)bluepolka(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: insert performance riddle
Date: 2005-08-10 23:02:46
Message-ID: 200508101702.46732.pgsql@bluepolka.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I have two identical servers giving abysmal INSERT performance in
pgsql 7.3.4, 7.4.8, and 8.1devel under no load or I/O contention
at all (no dumps, no vacuums, no apps, etc). Any suggested
investigations appreciated...

Metric: I'm measuring average insert speed on the following
table with the following psuedo-loop via DBI 1.48 and DBD::Pg
1.43 (older versions of DBD::Pg/DBI are slow, too):

create table foo (id serial, msg varchar);
for i in 1..1000 do
insert into foo (msg, "this is a test message");

Results: The slow servers are doing 6...count'em, SIX...that's
right, I said S-I-X inserts per second (QPS) on average.
Measuring on 4 other systems, some of them heavily loaded, the
measured range is 60-1200 QPS.

Hardware: The slow servers are identical HP DL740s running RHEL
AS 3.0 with kernel 2.4.21-4.0.2.ELsmp (RHLE AS 3.0), 12gb of
RAM, RAID5 via Compaq Smart Array 5i/532 RAID controller.

The systems I'm comparing to include:

4-cpu Itanium HP rx4650, HPUX 11.23, SAN (1000 QPS)...
4-cpu PA-RISC HP rp5470, HPUX 11.00, SAN (190 QPS)...
2-cpu Intel Dell 2650, Linux 2.4.20-30.7, RAID5 (350 QPS)...
1-cpu IBM T41 1.4ghz laptop (7200RPM IDE drives...I think they're
lying because they report 1100 QPS)...

What I've Tried: I've measured the raw I/O via bonnie and dd,
and the speeds are comparable across these boxes, definitely not
10-100x differences. Some of the faster QPS boxes are slower on
dd/bonnie times. I tested multiple versions of pgsql with same
essential results. I tested multiple versions of DBD and
DBD::Pg, same results. I've tested on old clusters and tested
with newly initdb'd clusters, same results.

Our next/last guess is that there is some sort of major slowdown
in one of the system libraries, so we're thinking of wiping it
clean and trying a different OS/version. Ideas?

Ed

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Fuhr 2005-08-11 00:03:34 Re: insert performance riddle
Previous Message Joshua D. Drake 2005-08-10 22:42:59 Re: What's up with EnterpriseDB?