> > 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
