On 10/14/06, Chris Mair <chrisnospam@xxxxxxxx> wrote:
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).
My postgresql 'magic number' is 150. 150 is the point at which I stop getting meangingful improvements on two important cases: simple update/insert transactions like yours and also where the performance improvement on fetching multiple rows level's off. In other words, selecting 100k rows in 150 record chunks is marginally slower then selecting the whole thing at once (and single record selects is of course much slower). In code, the number 150 is called 'merlin's constant' :) however, its a pretty safe bet zabbix is not doing updates grouped in transactions like that. on the other hand, the updates are not so localized either.
Details and results are here: http://www.1006.org/misc/20061014_pgupdates_bench/
wow, great chart! 8500 updates/sec is really spectacular. It proves that mvcc bloat on small tables is controllable. On large tables, the bloat is usually not as much of a concern and can actually be a good thing. You also proved, in my opinion conclusively, that running vacuum in high update environments is a good thing.
With vacuum, I get a stable performance all the way up to 300k updates. Rates are 4700 u/s or even 8500 u/s.
It looks like with careful tuning 10k could be cracked. Also, while mvcc provides certain scnenarios that have to be worked around, you also get its advantages. Updates lock only the record being written to and only to other writers. mysql ISAM does full table locking...which is going to perform better in a 2p server with 100 users? 4p and 1000 users? merlin
