Score:1

Server

Will disk compression impact the performance of a MySQL database?

Baa

2/14/24, 1:45 PM

I've recently setup a TrueNAS storage server and hosted on it is a MySQL database. It's about 3GB; I'm not too concerned with how much space it's utilising.

By default, TrueNAS enables LZ4 compression on the ZFS filesystem. There's nothing else on this disk.

I'd like to know if this disk compression is going to have a significant effect on the performance of the database, if I should use something else (MySQL's own compression system potentially) or nothing at all.

228

2 + 4

mysql

performance

zfs

compression

truenas

Romeo Ninov

2/14/24, 1:57 PM

Please define your understanding of **significant effect**

Baa

2/14/24, 2:44 PM

@RomeoNinov I want to know best practice for performance. If disk compression is known to cause problems with MySQL databases for little gain then that would be my answer, or alternatively if the impact is little-to-none for decent storage gains etc.

Rick James

2/14/24, 4:17 PM

"Cause problems" -- The FS would not exist if that happens. "Performance" -- Maybe.

Wilson Hauck

2/27/24, 3:19 PM

Yes it will affect performance.

Score:3

Server

Brennen Smith

2/14/24, 2:59 PM

It will likely make little to zero difference in terms of performance. Unless your workload is heavily based on performing full table scans, MySQL performance is governed by IOPS/disk latency. If you are performing these r/w's across the network (TrueNAS), then that will be the performance bottleneck.

The other detail to keep in mind is that ZFS compression is per block, and performs a heuristic (byte peeking) to determine if compression will have a material effect upon each block. So depending on the data you store in MySQL, it may not even be compressed.

With that said, MySQL on ZFS in general is known to need tuning to perform well - see: https://www.percona.com/blog/mysql-zfs-performance-update/

+ 0

Score:2

Server

Romeo Ninov

2/14/24, 2:59 PM

Based on my personal experience and this page compression of ZFS have insignificant effect on overall performance. Of course it depend on the hardware you have - slow CPU may lead to slow operations, but on contemporary hardware IMHO you will have no problem. And at the end you can always disable it for database pool.

About MySQL compression - it is not good practice to try to compress already compressed data. This will lead (generally speaking) to increase of the used disk space.

When sizing MySQL get in mind the size of records (blocks) which is 128kb by default (in Solaris)

+ 2

Baa

2/14/24, 3:45 PM

Thanks for your advice, I'll have a read through that blog too

Rick James

2/14/24, 4:20 PM

Solaris is an oddball implementation of MySQL. Still, I agree that compression is not what it is cracked up to be.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: Will disk compression impact the performance of a MySQL database?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.