How to setup a guest machine for mysql database when hypervisor and guest both are running on zsf&

[A.M.]

Hi,

We use a fresh distribution of Proxmox VE, on RAID1 zfs pool on SSD.
What's best practice for configuring guest machine with zfs?
What should I look for when using a guest machine with zfs for use under a loaded database?

There is a good article from percona team explaining how to minimally configure zfs to work with mysql.

Are there any conflicts at the hypervisor file system level may occure when both hypervisor and guest mashine running on zfs?

 

[bobmc]

The proxmox host has access to the physical disks and you have chosen to use raid 1 to provide redundancy in case of physical disk failure. You then don't need to use any form of redundant file system such as zfs on your guest machines (and it would be counter productive to do so due to additional overhead) as this feature is provided at the host level.

You may want to look into changing the cache settings on the virtual disk(s) you set up for the guest VM and/or providing dedicated physical storage to the guest for the database

 

[wahmed]

Agree with @bobmc. Using any redundancy inside a VM is really counterproductive. Once you have some form of redundancy in the physical storage, using just standard LVM for a VM is the way to go for best performance. Same goes if you had shared storage such as Ceph, Gluster, DRBD etc.

 

[LnxBil]

What's best practice for configuring guest machine with zfs?
What should I look for when using a guest machine with zfs for use under a loaded database?

You have to optimize your host volblocksize and guest recordsize to match the blocksize of your database (normally 8K).

Why do you want to run your MySQL database on ZFS? What about running MySQL inside of LXC on the host ZFS for only have one layer of filesystem between your database and your disks?

Are there any conflicts at the hypervisor file system level may occure when both hypervisor and guest mashine running on zfs?

Besides doing everything twice (compression, ARC etc.), no.

 

[A.M.]

Thank you guys for your replies,

Why do you want to run your MySQL database on ZFS? What about running MySQL inside of LXC on the host ZFS for only have one layer of filesystem between your database and your disks?

Some researches of mysql database work on zfs were conducted.
here you can find more information about:

(i am new to this forum, so i can't post links, but you can find this articles by name on site of Percona team)

blog/2017/12/07/hands-look-zfs-with-mysql/
blog/2018/05/15/about-zfs-performance/

If there is redundancy at the server level, there is no need for a raid.
So, I want to try implement something like this:

[root@Centos7 ~]# zpool status
  pool: mysqldata
 state: ONLINE
  scan: none requested
config:

    NAME                     STATE     READ WRITE CKSUM
    mysqldata                ONLINE       0     0     0
      /mnt/zfs.img           ONLINE       0     0     0
    logs
      /dev/shm/zil_slog.img  ONLINE       0     0     0
    cache
      /dev/shm/l2arc.img     ONLINE       0     0     0

errors: No known data errors

 

[guletz]

You have to optimize your host volblocksize and guest recordsize to match the blocksize of your database (normally 8K).

For mysql/percona/maria, the blocksize is 16K

blog/2017/12/07/hands-look-zfs-with-mysql/

https://www.percona.com/blog/2017/12/07/hands-look-zfs-with-mysql/


You have misunderstanding the article .... see below from the same article:

"A file is not the fastest and most efficient storage, but it is fine for our hands-on. In production, please use real devices"
So if you do not make only a test, then your zfs setup is very wrong!

 

[A.M.]

For mysql/percona/maria, the blocksize is 16K


https://www.percona.com/blog/2017/12/07/hands-look-zfs-with-mysql/


You have misunderstanding the article .... see below from the same article:

"A file is not the fastest and most efficient storage, but it is fine for our hands-on. In production, please use real devices"
So if you do not make only a test, then your zfs setup is very wrong!

The main idea is not about kind of storage, but about setup of mysql on zfs.
So, i can implement this fs on real devise/

here you can more information (https://youtu.be/cACNxdzFXIo)

 

[guletz]

The main idea is not about kind of storage, but about setup of mysql on zfs.
So, i can implement this fs on real devise/

here you can more information (https://youtu.be/cACNxdzFXIo)

Sorry, but I do not have time to whatch such kind of video

The short answer is YES, you can use zfs for mysql/percona/what-ever-DB. I use perconaDB on zfs for many years, and it is working without problems(for me and for others) including with PMX.

 

[LnxBil]

For mysql/percona/maria, the blocksize is 16K

Thanks. I wasn't aware of that.
(just for the sake of completeness: 8 KB is at least the default for Oracle and PostgreSQL.)

 

[guletz]

Thanks. I wasn't aware of that.
(just for the sake of completeness: 8 KB is at least the default for Oracle and PostgreSQL.)

Hi,

Yes. This is true regarding this old DBs. This DB was starting when 4K was mostly use. The rest of DB who was appear on the market was start with 16/64 k.

And this is only one part of story as I learned on my own skin. The mysql/percona/maria binary log is 128k. myisam table format have another block size!