> Do a simple test to see my point:
>
> 1. create table test (id int4, aaa int4, primary key (id));
> 2. insert into test values (0,1);
> 3. Execute "update test set aaa=1 where id=0;" in an endless loop
>
> I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.22 (MyISAM,
> sorry had no configured InnoDB). Ubuntu 6.0.6, AMD64, 2GB, default
> database settings.
>
> MySQL performs very well, approximately 15000-20000 updates per second
> with no degradation of performance.
>
> PostgreSQL does approximately 1600 records per second for the first
> 10000, then 200rps for the first 100k records, and then slower and
> slower downgrading to 10-20 rps(!!!) when reaching 300k.
Hi,
it would be cool if you could at least:
- bundle your updates into transactions of, say, 1000 updates at a time
i.e. wrap a BEGIN; END; around a 1000 of them
- run postgresql with fsync off, since you're using MyISAM
- run PostgreSQL at least 8, since you're running MySQL 5
I'd bet MySQL would still be faster on such an artificial, single user
test, but not *that much* faster.
If you don't want to install 8.0, could you maybe at least do the first
two items (shouldn't be a lot of work)...?
Which client are you using? Just mysql/psql or some API?
Bye, Chris.