Re: more anti-postgresql FUD

> > 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.
>
> I'm quite sure the results will be very close to what I get before even
> if I do all of the above. My post was not about MySQL vs PostgreSQL. It
> was about very fast performance degradation of PostgreSQL in case of
> large number of updates provided vacuum is not used.
>
> > 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?
>
> C API

Ok,
I did some tests at last on this using the above 3 suggestions.
I ran on 8.1.4 on Linux with fsync=off and did 300k updates bundled
into 100 updates / 1 transaction (100 turned out to be a sweeter
spot than 1000).

The box was comparable to yours, I think: 1xOpteron 2.2GHz, 2xSATA
RAID0 (yes, I know...), 1GB RAM

Details and results are here:
http://www.1006.org/misc/20061014_pgupdates_bench/

The interesting part is the graph that shows updates / sec real time
vs. running total of updates:
http://www.1006.org/misc/20061014_pgupdates_bench/results.png

Let's start with the red crosses: that's without vacuum, and yes,
you're right: PG's performance degrades.

But, it doesn't degrade quite as bad as you mentioned
(you mentioned 1600u/s for the first 10k, then 200u/s for
the first 100k). At 100k I'm still at 2700u/s down
from ~8000u/s. Only after ~140k updates my line drops
quicker. I obviously bump into some limit given by my
setup there. The thing is totally CPU-bound by the way.

Ok.
So, this a very bizarre load for PostgreSQL, especially
without any vacuum.

Let's add some vacuum: every 50k (green x) or even every 10k
(blue *) updates - which is a very reasonable thing do to for
this type of load.

With vacuum, I get a stable performance all the way up to
300k updates. Rates are 4700 u/s or even 8500 u/s.

Note the curves show no drops when vacuum is active.

Out of curiosity I did a run having autovacuum visit the db
every 30 seconds (purple squares): even without any special
effort to find good vacuum spots, I can get a good 3300
updates/sec all the way up to 300k updates!

I'd dare to say that if you just ran ZABBIX on 8.1 with
autovacuum on with a shortish interval (30 sec?) you'd
get rid of your performance problems. Time to update
the documentation after all? ;)

Bye,
Chris.

--

Chris Mair
http://www.1006.org