ZFS optimization for MariaDB/MySQL server?

K

kalleboy

I'm planning to set my MariaDB SQL server with the following options on FreeBSD 13.1 + ZFS. I'm planning a modest, non-dangerous set of options:

skip-innodb_doublewrite
symbolic-links=0
max_allowed_packet=512M
max_connections=250
innodb_io_capacity=15000
innodb_doublewrite = 0
innodb_flush_method = O_DSYNC
innodb_log_write_ahead_size=16384
innodb_flush_neighbors=0
#innodb_use_atomic_writes=0 <- not really sure about this?
innodb_read_io_threads = 10
innodb_write_io_threads = 10
innodb_fast_shutdown = 0

Hardware: 64 GB DDR4 RAM + 2x1TB NVMe disk in ZFS - stripe mode (That's simply a RAID0 I guess)

What do you BSD people think on these?

Any advice, any suggestion would be much appreciated.
 
shatteredsilicon.net

MariaDB / MySQL on ZFS - Shattered Silicon

ZFS brings many performance and maintainability benefits to MySQL / MariaDB database deployments. Here we explore the benefits with InnoDB storage engine.
shatteredsilicon.net shatteredsilicon.net

And I would suggest running the database for a couple of days, then run databases/mysqltuner. That should give you some hints on what to tweak regarding memory usage. It's tempting to give MySQL/MariaDB a LOT of buffers but this is often quite detrimental to its performance, so don't fall into that trap. It's really easy to configure MariaDB/MySQL to consume all your memory and more, which is also not a good idea to do. Don't let it use more memory than the system actually has.
 
This is my configuration,
Code: 
[client-server]
port    = 3306
socket    = /var/run/mysql/mysql.sock

[mariadb]
bind_address=127.0.0.1
innodb_file_per_table=1
###Log Settings
general_log=0
slow_query_log=0
###log_output=TABLE
log_output=FILE
log_basename=mariadb
########################################## Tuning

###Zfs recordsize 16K
innodb_log_write_ahead_size=16384

###No partial pages on zfs due to previous settings,zfs prevents corruption
innodb_doublewrite=0

###Only Needed for mechanical disks
innodb_flush_neighbors=0

###Only Needed for Linux
innodb_use_native_aio=0

###SSD
innodb_io_capacity=10000
innodb_io_capacity_max=20000

###Numbers
innodb_flush_log_at_timeout=3
innodb_write_io_threads=32
innodb_read_io_threads=64
max_connections=100
#ISAM
key_buffer_size=64K
#Redo log
innodb_log_file_size=128M
tmp_table_size=256M
max_heap_table_size=256M
join_buffer_size=256M
key_buffer_size=256M
#ARIA
aria_pagecache_buffer_size=256M
innodb_buffer_pool_size=512M

###Other
innodb_flush_method=fsync
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
 
Alain De Vos it seems you defined "innodb_doublewrite=0" two times?

And I heard innodb_checksum_algorithm=strict_none could nead to data corruption. Ever had such issue?

And for "innodb_flush_method", I guess, if not being wrong, O_DSYNC is usually recommended on ZFS. Any reason using fsync for that?

And is this a dedicated server only for SQL service? Any other service running? How about CPU/RAM? Just curious..
 
You must log in or register to reply here.
Back
Top