4 ms·

I appreciate the straight-forwardness of this comment :) One of the things that I think would be interesting to show, is how the consistency of performance has

by morgo 12y ago

I appreciate the straight-forwardness of this comment :) One of the things that I think would be interesting to show, is how the consistency of performance has improved over time too. There has been a lot of focus on removing any stalls in recent versions of InnoDB, and this something that is incredibly valuable doesn't always show up in benchmarks :( I assume that PostgreSQL has gone through the same evolution in managing garbage collection, etc. Peter Boros @ Percona has done some nice benchmark visualization, for example: http://www.percona.com/blog/2014/08/12/benchmarking-exflash-with-sysbench-fileio/ http://www.percona.com/blog/2014/08/12/benchmarking-exflash-... Slides on how to produce these graphs: https://fosdem.org/2015/schedule/event/benchmark_r_gpplot2/ https://fosdem.org/2015/schedule/event/benchmark_r_gpplot2/ (Disclaimer: I work on the MySQL team.)

pgaddict 12y ago
You mean how the transaction rate changes when things like checkpoint or vacuum are triggered in the background? Yeah, I do have those numbers, and the consistency improved a lot (especially when autovacuum was introduced in 8.1, and when spread checkpoints were introduced in 8.3). The other part of the story are filesystems - back in 8.x days the filesystem was usually ext3, and that handled sync pretty badly (and that made the impact of checkpoints / autovacuum much worse). Today we have xfs/ext4 which do handle that much better.
- morgo 12y ago
  Checkpoint and vacuum are good examples. There are other stalls we have had to address: extending the size of tablespace files, dropping tables and scanning the LRU to free pages.
  - pgaddict 12y ago
    PostgreSQL stores the data in a different way (each object gets a different file, or several files), so extending the size of tablespace files is not a big issue and dropping objects is as simple as deleting the file. The LRU is basically the shared_buffers, and that's where most of the 9.2 improvements were (more or less).

by morgo 12y ago

pgaddict 12y ago

You mean how the transaction rate changes when things like checkpoint or vacuum are triggered in the background? Yeah, I do have those numbers, and the consistency improved a lot (especially when autovacuum was introduced in 8.1, and when spread checkpoints were introduced in 8.3). The other part of the story are filesystems - back in 8.x days the filesystem was usually ext3, and that handled sync pretty badly (and that made the impact of checkpoints / autovacuum much worse). Today we have xfs/ext4 which do handle that much better.

morgo 12y ago
Checkpoint and vacuum are good examples. There are other stalls we have had to address: extending the size of tablespace files, dropping tables and scanning the LRU to free pages.
- pgaddict 12y ago
  PostgreSQL stores the data in a different way (each object gets a different file, or several files), so extending the size of tablespace files is not a big issue and dropping objects is as simple as deleting the file. The LRU is basically the shared_buffers, and that's where most of the 9.2 improvements were (more or less).