Re: Performance (was: The New Slashdot Setup (includes MySql server))

The Hermit Hacker wrote:
>
> thanks for the pointer ... I just posted my response ... specifically
> pointing out how "accurate" the MySQL docs tend to be *rofl*

And now there is a response to your response stating the following

>
> The MySQL people have said exactly the same sort of things about
> the PostgreSQL people. So please stop the name-calling and
> the quotes around "test", it's not going to get you anywhere.
>
> That being said, the standard MySQL benchmark _still_ is 30 times
> faster for MySQL 3.23 than on PostgreSQL 7.0 (with fsync turned off,
> _and_ nonstandard speed-up PostgreSQL features like VACUUM enabled,

btw, how does one "enable" vacuum ?

> I might add). The main reason seems to be some sort of failure to
> use the index in the SELECT and UPDATE test loops on the part of
> PostgreSQL.
>
> The benchmark, for the curious, works like this:
>
> First it creates a table with an index:
>
> 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);
>
> Then it fills the table with 300.000 entries with unique id values.
>
> Then, it issues a query like this:
>
> update bench1 set dummy1='updated' where id=1747
>
> which causes the backend to do one thousand read() calls. For each query.

could it be that just updating 1 unique index causes 1k read()'s ?

> No wonder it's slow. An EXPLAIN query states that it's using the
> index, though. I have no clue what happens here. I've sent this
> to the pgsql-general mailing list and have just reposted it to -hackers.

I somehow missed it (on -hackers at least) so I repost it here

> Oh yes, the benchmark also revealed that CREATE TABLE in PostgreSQL 7.0
> leaks about 2k of memory.

-------------------
Hannu