Re: more anti-postgresql FUD

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

In order to keep performance of busy application steady, I had to
perform the vacuum every 10 seconds. As I said earlier ZABBIX Server
does hundredrs of updates per second and performance of the updates
degrades very fast.

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

Yes, I believe fsync=on during my tests, the option is commented in
PostgreSQL config file. It explains worses performance of PostgreSQL
for the first 20K updated, but still my observation are valid.

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

Face it, if one does hundreds updates per second for one table (that's
exactly what ZABBIX does, and not for one record(!) table as in my
simple test), performance degrades so fast that vacuum has to be
executed once per 5-15 seconds to keep good performance. The vacuum
will run at least several seconds with high disk io. Do you think it
won't make "PostgreSQL at least 10x slower than MySQL" as stated in the
manual? What we are discussing here? :)

And by the way, ZABBIX periodically doess execute vacuum for subset of
tables, the functionality is is built in ZABBIX.

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

I will try to experiment with newer PostgreSQL when I find some time.
I'm sure PostgreSQL is doing very good progress, and I'm really happy
to see that PostgreSQL became an excellent alternative to
Oracle/DB2/Informix.