Re: Heaps of read() syscalls by the postmaster

Hi *,

I am trying to find out why the MySQL benchmarks result in such _lousy_
performance for postgreSQL.

Tracing a simple update loop, I see the postmaster hogging 95% CPU,
doing this:

$ strace -c -p 10174
[ wait a few seconds ] ^C
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
88.84 1.813381 60 30247 read
8.82 0.180123 6 30552 lseek
1.76 0.035868 1087 33 recv
0.45 0.009101 54 170 write
0.07 0.001528 45 34 send
0.03 0.000619 19 33 time
0.03 0.000618 19 33 33 ioctl
------ ----------- ----------- --------- --------- ----------------
100.00 2.041238 61102 33 total

... which translates to "the query

update bench1 set dummy1='updated' where id=1747

is processed by calling the read() system call _one_thousand_times_".

That's a bit excessive. What the hell could possibly cause this?
Failure to use the index?? EXPLAIN says it's using the index.
This query, unsurprisingly, takes 0.08 seconds to execute, which is way
too much.

The table has been created (via Perl) thusly:

$server->create("bench1",
["id int NOT NULL",
"id2 int NOT NULL",
"id3 int NOT NULL",
"dummy1 char(30)"],
["primary key (id,id2)",
"index index_id3 (id3)"]));

which translates to

create table bench1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30));
create unique index bench1_index_ on bench1 using btree (id,id2);
create index bench1_index_1 on bench1 using btree (id3);

I don't have a clue what the reason for this might be.

This is postgreSQL 7.0, compiled with i686-pc-linux-gnu/2.95, using
no special options to compile or setup, except that fsync was turned off,
as verified by the above sytem call summary. Auto-commit was on during
this test (what's the SQL command to turn it off, anyway? I couldn't
find it).

NB: The same benchmark revealed that CREATE TABLE (or maybe it's CREATE
INDEX) leaks about 2k of memory.

--
Matthias Urlichs | noris network GmbH | smurf(at)noris(dot)de | ICQ: 20193661
The quote was selected randomly. Really. | http://smurf.noris.de/
--
Justice: A commodity which (in a more or less adulterated condition)
the State sells to the citizen as a reward for his allegiance,
taxes, and personal service.
-- Ambrose Bierce, "The Devil's Dictionary"