Re: more anti-postgresql FUD

* alexei.vladishev@gmail.com (alexei.vladishev@gmail.com) wrote:
> 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.

If you refuse to vacuum (or have the table autovacuumed) then sure.  Of
course, I don't know of anyone who actually uses PostgreSQL who would
run a system like that.

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

Don't say 'sorry' to us for using MyISAM (though it pretty much
invalidates the test), say 'sorry' to your users...  You can try running
Postgres with fsync=off but I would strongly recommend against it in a
production environment (just like I'd strongly recommend against
MyISAM).

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

If you periodically vacuum the table (where periodically most likely
would mean after some number of write transactions) I expect you'd find
Postgres performance to at *least* stabalize.  If you vacuum with a
periodicity reasonably ratioed to your update statement frequency you'd
find that it will *improve* performance and Postgres will provide a
*consistant* performance.

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

This, above all things imv, would be FUD here.  Vacuum/autovacuum aren't
something to be feared as damaging, detrimental, or resource hogging.
Vacuum doesn't take an exclusive lock and moves along quite decently if
done with an appropriate frequency.  If you wait far, far, too long to
do a vacuum (to the point where you've got 10x as many dead tuples as
live ones) then sure it'll take a while, but that doesn't make it
resource hogging when you consider what you're having it do.

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

That's exactly what vacuum *does*, it marks dead tuples as being
available for reuse.  Please understand that vacuum != vacuum full.

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

I'm afraid there's a bit of a misunderstanding about what vacuum is for
and how it can affect the behaviour of Postgres.  Please, please forget
whatever notion you currently have of vacuum and actually run some tests
with it, and post back here (or -performance) if you run into problems,
have questions or concerns.  I expect you could also tune autovacuum to
be frequent enough on the appropriate tables that you wouldn't have to
intersperse your own vacuum commands in.  Also, as pointed out, current
releases (8.1) also have quite a few enhanments and performance
improvements.

    Thanks,

        Stephen