Skip to content

Latest commit

 

History

History
71 lines (53 loc) · 2.88 KB

MariaDbAndMySql.md

File metadata and controls

71 lines (53 loc) · 2.88 KB
Raw

MariaDB / MySQL

THIS IS STILL UNDER CONSTRUCTION!!! I've probably only got this 30% completed; there's no harm in using what's in here, just note, it's not "done" yet.

Assumptions

  • All zpools on host included redundancy (i.e no unmirrored stripes, so raidz/mirror)
  • Based on the LSIO MariaDB container (applies to any maria/mysql, just the location of config files may differ otherwise)
  • (and of course, that you've enough system memory to allow for such additional

Host changes

  • Edit your go file (nano /boot/config/go) adding:

    echo 1 /sys/module/zfs/parameters/zfs_txg_timeout

(note assumptions section: all zpools should be redundant setting the above)

Creating ZFS dataset tuned for MySQL/Maria

zfs create wd/dock/mariadb -o recordsize=16K -o logbias=throughput -o primarycache=metadata -o atime=off -o xattr=sa -o compression=zstd-3

Explanation

  • Recordsize - InnoDB writes in 16KB chunks
  • Logbias - recommendation from oracle re: databases on ZFS
  • Primarycache - the DB does it's own caching ('buffer pool'), so we should only cache filesystem metadata, not 'everything'
  • aTime - updating metadata every time a file is accessed is, in nearly all cases, absurd. Should be disabled as a general rule in ZFS unless you've a very specific need for it
  • xAttr - If you're on Linux, you should frankly have this configured at the pool level as it should be set for all (otherwise you'll likely encounter severe issues if you ever do anything like NFS or SMB access, at a minimum)
  • Compression - zstd-3 is 'cheap' enough (resource-wise) that I feel it should be the standard when running any modern-ish CPU (at least for server use... there are caveats for workstations etc)

Creating your container

Example env variables:

mariadb:
    container_name: mariadb
    image: mariadb
    ports:
      - 3306:3306
      - 3307:3307
    volumes:
      - /opt/mariadb:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: yourrootpasswordhere
      MYSQL_USER: homeassistant
      MYSQL_PASSWORD: yourstrongpasswordhere
      MYSQL_DATABASE: homeassistant
    restart: on-failure

MariaDB Tuning that's specific to use on ZFS

Explain each: why we're disabling various maria data 'safety' features, etc

innodb_doublewrite=0
innodb_checksum_algorithm=crc32
innodb_flush_neighbors=0
innodb_use_native_aio=0
innodb_use_atomic_writes=0
innodb-compression-algorithm=none

Tuning MariaDB Generally

Run diff on currently running custom.cnf against base my.cnf, explain where necessary, and note that user shouldn't blindly copy/pasta from "some guy on the internet" without confirming with their own research