* alexei.vladishev@xxxxxxxxx (alexei.vladishev@xxxxxxxxx) 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
Attachment:
signature.asc
Description: Digital signature
