MySQL performance : Proxmox over zfs + Raid 0

[chencho]

Hi all.

Actually we have 3 proxmox servers (not in a cluster) with 2 x 480 SSD over ZFS and RAID 0 ( we make remote backups )

One of them have 15 CT's with a WebApp (same app, different customers) and have a high level of MySQL processes and several times have several MySQL deadlocks in some CT's

Is any way to make better the MySQL performance over ZFS?

We consider to put out the MySQL databases to an external server and leave only the nginx+php services in each CT

 

[LnxBil]

What have you done with respect to optimising MySQL performance for ZFS? If you have done nothing yet, please google, there are a ton of information about this.

 

[chencho]

I only found this https://planet.mysql.com/entry/?id=19489 and i think this can't help me at this moment (the server is up from 3 years)

 

[LnxBil]

I'm unfamiliar with optimizing MySQL, I normally run only Oracle and Postgres on ZFS, but maybe this also applies:

- get the volblocksize of your data disk for your database to the same size as your blocksize in your database. This will optimize write speeds
- align everything to the volblocksize, e.g. start partition if any at multiple integer factos ofr your blocksize etc.
- ZFS has already data integrity, so you can turn that off in your database

In general, all the links on the first page are worth a read.

 

[chencho]

I set
zfs set recordsize=16K rpool/data/subvol-701-disk-1
To be equal than MySQL

But i have not volblocksize:
rpool/data/subvol-701-disk-1 volblocksize - -

And i'm on MySQL 8 and when i add innodb_doublewrite = 0 to my.cnf MySQL don't start

 

[LnxBil]

recordsize is for zfs dataset (as in filesystem) for a container (LXC), volblocksize is for zfs zvol (as in block storage) for QEMU/KVM. So you cannot set both.

 

[sigo]

I set
zfs set recordsize=16K rpool/data/subvol-701-disk-1
To be equal than MySQL

But i have not volblocksize:
rpool/data/subvol-701-disk-1 volblocksize - -

And i'm on MySQL 8 and when i add innodb_doublewrite = 0 to my.cnf MySQL don't start

what error you get when innodb_doublewrite set to off?
and show "zfs get all rpool/....." for you mysql filesystem

 

[chencho]

@LnxBil , thanks for the explanation . I'm only use LXC

@sigo , MySQL cannot start after i edit /etc/mysql/my.cnf :

sep 11 12:17:06 server6 mysql-systemd-start[17956]: my_print_defaults: [ERROR] Found option without preceding group in config file /etc/mysql/my

I need to set before [mysql], my fault

root@server4:~# zfs get all rpool/data/subvol-701-disk-1
NAME                          PROPERTY                        VALUE                           SOURCE
rpool/data/subvol-701-disk-1  type                            filesystem                      -
rpool/data/subvol-701-disk-1  creation                        mié oct 18  0:08 2017          -
rpool/data/subvol-701-disk-1  used                            120G                            -
rpool/data/subvol-701-disk-1  available                       14,6G                           -
rpool/data/subvol-701-disk-1  referenced                      120G                            -
rpool/data/subvol-701-disk-1  compressratio                   1.34x                           -
rpool/data/subvol-701-disk-1  mounted                         yes                             -
rpool/data/subvol-701-disk-1  quota                           none                            default
rpool/data/subvol-701-disk-1  reservation                     none                            default
rpool/data/subvol-701-disk-1  recordsize                      16K                             local
rpool/data/subvol-701-disk-1  mountpoint                      /rpool/data/subvol-701-disk-1   default
rpool/data/subvol-701-disk-1  sharenfs                        off                             default
rpool/data/subvol-701-disk-1  checksum                        on                              default
rpool/data/subvol-701-disk-1  compression                     on                              inherited from rpool
rpool/data/subvol-701-disk-1  atime                           off                             inherited from rpool
rpool/data/subvol-701-disk-1  devices                         on                              default
rpool/data/subvol-701-disk-1  exec                            on                              default
rpool/data/subvol-701-disk-1  setuid                          on                              default
rpool/data/subvol-701-disk-1  readonly                        off                             default
rpool/data/subvol-701-disk-1  zoned                           off                             default
rpool/data/subvol-701-disk-1  snapdir                         hidden                          default
rpool/data/subvol-701-disk-1  aclinherit                      restricted                      default
rpool/data/subvol-701-disk-1  createtxg                       510                             -
rpool/data/subvol-701-disk-1  canmount                        on                              default
rpool/data/subvol-701-disk-1  xattr                           sa                              local
rpool/data/subvol-701-disk-1  copies                          1                               default
rpool/data/subvol-701-disk-1  version                         5                               -
rpool/data/subvol-701-disk-1  utf8only                        off                             -
rpool/data/subvol-701-disk-1  normalization                   none                            -
rpool/data/subvol-701-disk-1  casesensitivity                 sensitive                       -
rpool/data/subvol-701-disk-1  vscan                           off                             default
rpool/data/subvol-701-disk-1  nbmand                          off                             default
rpool/data/subvol-701-disk-1  sharesmb                        off                             default
rpool/data/subvol-701-disk-1  refquota                        135G                            local
rpool/data/subvol-701-disk-1  refreservation                  none                            default
rpool/data/subvol-701-disk-1  guid                            4691974859655609545             -
rpool/data/subvol-701-disk-1  primarycache                    all                             default
rpool/data/subvol-701-disk-1  secondarycache                  all                             default
rpool/data/subvol-701-disk-1  usedbysnapshots                 0B                              -
rpool/data/subvol-701-disk-1  usedbydataset                   120G                            -
rpool/data/subvol-701-disk-1  usedbychildren                  0B                              -
rpool/data/subvol-701-disk-1  usedbyrefreservation            0B                              -
rpool/data/subvol-701-disk-1  logbias                         latency                         default
rpool/data/subvol-701-disk-1  dedup                           off                             default
rpool/data/subvol-701-disk-1  mlslabel                        none                            default
rpool/data/subvol-701-disk-1  sync                            standard                        inherited from rpool
rpool/data/subvol-701-disk-1  dnodesize                       legacy                          default
rpool/data/subvol-701-disk-1  refcompressratio                1.34x                           -
rpool/data/subvol-701-disk-1  written                         120G                            -
rpool/data/subvol-701-disk-1  logicalused                     160G                            -
rpool/data/subvol-701-disk-1  logicalreferenced               160G                            -
rpool/data/subvol-701-disk-1  volmode                         default                         default
rpool/data/subvol-701-disk-1  filesystem_limit                none                            default
rpool/data/subvol-701-disk-1  snapshot_limit                  none                            default
rpool/data/subvol-701-disk-1  filesystem_count                none                            default
rpool/data/subvol-701-disk-1  snapshot_count                  none                            default
rpool/data/subvol-701-disk-1  snapdev                         hidden                          default
rpool/data/subvol-701-disk-1  acltype                         posixacl                        local
rpool/data/subvol-701-disk-1  context                         none                            default
rpool/data/subvol-701-disk-1  fscontext                       none                            default
rpool/data/subvol-701-disk-1  defcontext                      none                            default
rpool/data/subvol-701-disk-1  rootcontext                     none                            default
rpool/data/subvol-701-disk-1  relatime                        off                             default
rpool/data/subvol-701-disk-1  redundant_metadata              all                             default
rpool/data/subvol-701-disk-1  overlay                         off                             default
rpool/data/subvol-701-disk-1  com.sun:auto-snapshot:frequent  false                           inherited from rpool
rpool/data/subvol-701-disk-1  com.sun:auto-snapshot:hourly    false                           inherited from rpool

 

[chencho]

What you think is better?

A) set a CT with each WebApp (nginx+php+filestorage) with recordsize > 16 and separate MYSQL CT with all db's with recordsize = 16

B) set a CT with each WebApp (nginx+php+filestorage+MySQL) with recordsize = 16

Put all MySQL databases together is not the best, if the CT have some problem, i think.

 

[guletz]

Hi,

Some ideas

- for mysql server I would use at least 3 separate CTs using a mysql cluster(haproxy, gallera and perconadb cluster)
- use others CT for the rest (web-app) and also use some php accelerators

For mysql only CTs:
- use as much ram as possible
- use a dedicated mount point with 16k record size if you use only innodb files
- for any non-innodb files use CT default 128 k
- move your mysql logs on to 128k recordsize
- disable mysql checksums (zfs can do this)
-disable double write?(I do not remember exact the name, as I do not have my PC around me, could be innodb buffer write? I do not remember now ) at mysqldb level and at zfs level

skip-innodb_doublewrite
-since ZFS does not allow partial writes, you can safely turn off the double write buffer

innodb_doublewrite=0
- ZFS is CoW, so you cannot get a partial log write, no need do double-write the log to catch partial writes due to a crash

You must set for zfs dataset where is mysql

primarycache= metadata




Good luck!

 

[Kevin Myers]

or install proxmox 6 on a node using ext4 and lvm, and migrate the vms over .....

 

[LnxBil]

Some ideas

In addition to that, I recently found an article about MySQL and PostgreSQL optimization for ZFS in the usenix magazine. It's a good read with a lot of background information:

https://www.usenix.org/system/files/login/articles/login_winter16_09_jude.pdf