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
