Re: Heaps of read() syscalls by the postmaster - Mailing list pgsql-hackers

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
read8.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
NOTNULL",                "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@noris.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
forhis allegiance,       taxes, and personal service.                              -- Ambrose Bierce, "The Devil's
Dictionary"