3 ms·

I wonder what the performance impact of running MySQL on ZFS would be?

by zaphar 9y ago

empthought 9y ago
Correctly tuned, it could be faster on some workloads and hardware (block-level compression really saves on disk I/O).
- willvarfar 9y ago
  My understanding is that ZFS compresses to multiples of the sector size, so it's the same ratio as innodb page compression gives but ought be much faster and transparent. My own experiments put tokudb far far ahead: https://williamedwardscoder.tumblr.com/post/160628887798/compressing-mysql-databases https://williamedwardscoder.tumblr.com/post/160628887798/com... Unfortunately tokudb has taken a massive performance hit recently - it was so fast when tokutek integrated it into 5.6 but the percona and Mariadb packaging in 5.7 is much slower than innodb - not sure what they haven't done but wish they'd profile and fix it - you'd think that percona, having brought tokutek - would have noticed and benchmarked and care :(
  - kbenson 9y ago
    > not sure what they haven't done but wish they'd profile and fix it Sometimes a slowdown comes because somebody noticed a problem and fixed it, such as the default compile options including a nasty bug that makes it unsafe. I don't have any specific knowledge of this case, but it the kind of thing I've seem happen multiple times in the past, so I wouldn't be surprised.
jlgaddis 9y ago
> "Future posts will be devoted to more specific topics like performance, ..." I'm looking forward to them myself.
X86BSD 9y ago
No idea about MySQL. I use postgresql exclusively. And it screams pretty good on ZFS. You can set the record size. The sector size. If you’re insane you can mess with fsync. You can tune ZFS so easily to handle the workload you have.
espadrine 9y ago
Are there Facebook engineers here? I believe Facebook heavily uses btrfs, which has a similar design and feature set. But they may only use it for object storage. Did they try using it for MySQL databases?
phil21 9y ago
I haven't done it on heavily enough MySQL servers to really tell you - but on "typical" installs there is no noticeable difference. I'd say add a bit of extra RAM for ARC overhead you wouldn't usually, and of course invest in SLOG devices. ZFS is never going to win any speed races, but what we gain in snapshotting/rollback/trivial remote backups makes it a great fit for many of our pgsql/mysql installs.
gigatexal 9y ago
Future articles should flesh that out.
late2part 9y ago
Conventional wisdom is that with basic (lz4) file system compression, the speed up you get in I/O r/w is substantial enough to be many tens-of-percentages performance improvement.

I wonder what the performance impact of running MySQL on ZFS would be?

by zaphar 9y ago

I wonder what the performance impact of running MySQL on ZFS would be?

empthought 9y ago

Correctly tuned, it could be faster on some workloads and hardware (block-level compression really saves on disk I/O).

willvarfar 9y ago
My understanding is that ZFS compresses to multiples of the sector size, so it's the same ratio as innodb page compression gives but ought be much faster and transparent. My own experiments put tokudb far far ahead: https://williamedwardscoder.tumblr.com/post/160628887798/compressing-mysql-databases https://williamedwardscoder.tumblr.com/post/160628887798/com... Unfortunately tokudb has taken a massive performance hit recently - it was so fast when tokutek integrated it into 5.6 but the percona and Mariadb packaging in 5.7 is much slower than innodb - not sure what they haven't done but wish they'd profile and fix it - you'd think that percona, having brought tokutek - would have noticed and benchmarked and care :(
- kbenson 9y ago
  > not sure what they haven't done but wish they'd profile and fix it Sometimes a slowdown comes because somebody noticed a problem and fixed it, such as the default compile options including a nasty bug that makes it unsafe. I don't have any specific knowledge of this case, but it the kind of thing I've seem happen multiple times in the past, so I wouldn't be surprised.

jlgaddis 9y ago

> "Future posts will be devoted to more specific topics like performance, ..." I'm looking forward to them myself.

X86BSD 9y ago

No idea about MySQL. I use postgresql exclusively. And it screams pretty good on ZFS. You can set the record size. The sector size. If you’re insane you can mess with fsync. You can tune ZFS so easily to handle the workload you have.

espadrine 9y ago

Are there Facebook engineers here? I believe Facebook heavily uses btrfs, which has a similar design and feature set. But they may only use it for object storage. Did they try using it for MySQL databases?

phil21 9y ago

I haven't done it on heavily enough MySQL servers to really tell you - but on "typical" installs there is no noticeable difference. I'd say add a bit of extra RAM for ARC overhead you wouldn't usually, and of course invest in SLOG devices. ZFS is never going to win any speed races, but what we gain in snapshotting/rollback/trivial remote backups makes it a great fit for many of our pgsql/mysql installs.

gigatexal 9y ago

Future articles should flesh that out.

late2part 9y ago

Conventional wisdom is that with basic (lz4) file system compression, the speed up you get in I/O r/w is substantial enough to be many tens-of-percentages performance improvement.