On 10/14/06, Chris Mair <chrisnospam@1006.org> 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