Re: more anti-postgresql FUD

On 11 Oct 2006 07:54:52 -0700, alexei.vladishev@gmail.com
<alexei.vladishev@gmail.com> wrote:
> Hello,
>
> I'm author and maintainer of ZABBIX and the manual. I would like to add
> some comments to the thread.

just so you know, I brought this up after taking a look at the zabbix
software, which is in my opinion very excellent.  I came across a
little strong in my comments and peter e was correct in pointing out
that the performance related comments were not 'fud'.  I felt a little
bad after opening this thread but you have to take this in context of
the bigger picture.  The postgresql poeple have been dealing with
(sometimes) unfounded prejudices for years.

> Unfortunately PostgreSQL performs much slower than MySQL doing large
> number of updates for one single table. By its nature ZABBIX requires
> to execute hundreds of updates per second for large installations.
> PostgreSQL cannot handle this nicely.
>
> 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

this is a very contrived test:
1. nothing really going on
2. no data
3. single user test
4. zabbix doesn't do this, nor does anything else
5. proves nothing.

zabbix is a bit more complex than that with multiple users, tables and
the ocassional join.  With a high number of servers in play things
might go differently than you expect.

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

8.0, 8.1, and 8.2 are all a bit faster at these types of queries, just
so you know.  8.1 and up i believe have autovcacuum defaulted on.  In
fairness, vacuuming in the 7.1x days was a different beast.

> 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.
>
> The manual states that PostgreSQL works ten times slower for ZABBIX, in
> reality it is much worser.
>
> Yes, I'm aware of autovacuuming, etc. But it eats resources and I
> cannot handle to run it periodically because I want steady performance
> from my application. I do not want to see ZABBIX performing slower just
> because of database housekeeper.

vacuum gives you stable performance, not vice verca.  I would imagine
zabbixs server scalability is driven by a number of factors.

> Several years ago I contacted PostgreSQL developers but unfortunately
> the only answer was "Run vacuum. We won't change PostgreSQL to reuse
> unused tuples for updates".

> Perhaps something has changed in recent releases of PostgreSQL, I don't
> think so. Please correct me if I'm wrong.

well, I am playing with zabbix with the possible eventuality of
rolling it out in our servers I might be able to get you some hard
data on performance.  By the way, I'm currently managing a
spectactularly large mysql database which is getting moved to
postgresql with the next release of the software -- in part because I
was able to show that postgresql gave much more reliable performance
in high load envirnonments.

In light of this discussion, I might be interested in running a little
test to see how zabbix would hold up on postgresql under a
artificially high load.  If I was to show that things were quite so
one-sided as you assumed, would you be willing to say as much in your
documentation? :-)

merlin