MariaDB (10.3)
Jump to navigation
Jump to search
It may have taken awhile but here we are. In my MySQL 5.5 Guide I go over whether the Query Cache is relevant for you, and how to make use of compression in the barracuda file format. These bits are still useful.
Buster at least includes mysqltuner, which is a useful guide program to watch for issues.
We need to disable transparent hugepages. Add transparent_hugepage=madvise to GRUB_CMDLINE_LINUX_DEFAULT in /etc/default/grub
Then run
echo madvise | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
After installing mariadb be sure to
systemctl stop mariadb chown mysql:mysql /innodb/ chmod 0750 /innodb usermod -a -G hugepager mysql
And either
mv /var/lib/mysql/ibdata1 /innodb/
or remove it entirely if you are on a virgin installation.
Replace /innodb/ or ignore as necessary.
If you are changing the size of the ib_logfiles, you will want to first remove them:
rm /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1
cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/99-server.cnf
To make your override file.
/etc/mysql/mariadb.conf.d/99-server.cnf
# # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # # See the examples of server my.cnf files in /usr/share/mysql # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] # # * Basic Settings # user = mysql pid-file = /run/mysqld/mysqld.pid socket = /run/mysqld/mysqld.sock #port = 3306 basedir = /usr datadir = /var/lib/mysql # Note if you mount /tmp as tmpfs, and are running a replication slave, # you will need to make a separate /mysqltmp mount or something similar # to make sure critical temporary files survive remoot. tmpdir = /tmp lc-messages-dir = /usr/share/mysql #skip-external-locking # Security # No community software I am aware of actually needs LOAD DATA LOCAL, # so I just disable it, and probably would not use software that did. See: # http://dev.mysql.com/doc/refman/5.1/en/load-data-local.html local_infile = 0 # This prevents granting access to stored procedures automagically just # for creating them. automatic_sp_privileges = 0 # secure-auth is probably not relevant if your server was built in the # past decade. I just like it on. secure-auth = 1 # Remove skip-show-database if you use phpMyAdmin or a similar tool to # manage your databases, it will just frustrate you or your users. skip_show_database # This limits where the LOAD_FILE, LOAD_DATA and SELECT INTO OUTFILE # statements can read from or write to. This is a good option to set. secure_file_priv = /tmp # Networking # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 # And yet for a single server machine I don't need to. skip-networking # There's no reason to waste time resolving domain names. If the ip # changes, we'll know. skip-name-resolve # # * Fine Tuning # You will need to enable Hugepages on your server to make use of # the large-pages option large-pages # The client default is 16M, while the server default used to be 1M. # Server default is now also 16M. # I've since hit 16M limits, so I use 32M now. max_allowed_packet = 32M # There's no serious reason to have a long interactive timeout. If you # are low on connections, you shouldn't set this higher than # wait_timeout interactive_timeout = 3600 # I am of the opinion that the default value is far too high. If you # use persistent connections, even a timeout of 300 may be too high. wait_timeout = 300 # The following are best set to the same size, because the size # of temporary tables is limited by the lower of them. # Setting these to the SkySQL defaults. tmp_table_size = 64M tmp_memory_table_size = 64M max_heap_table_size = 64M # table_definition_cache should be big enough for every table # in your database, plus temporary tables, and table_open_cache # should be a reflection of how many of these will be open in a live # connection at once - it will likely exceed your definition cache. # It doesn't hurt to set these to relatively large values. They don't # take a lot of RAM and it's better than hitting the limit. # I have actually topped 16K open tables. Yikes. table_definition_cache = 4096 table_open_cache = 65536 # The default optimizer_search_depth is 62. This causes MySQL to take # an obscene amount of time planning a query, though when it finally # executes, it is pretty close to optimal. I've set it to 8, which is # still a bit on the high end. optimizer_search_depth = 8 # Buffers # 384k for everyone! 384k blocks will generally mean that we are not # switching to the rather expensive mmap() operation most of the time. # read_rnd_buffer can be pretty greedy, at least for my setup. # If you are using forums or other software that generates large results, # you may find using 2mb is closer to optimal. join_buffer_size = 384K sort_buffer_size = 384K read_buffer_size = 384K read_rnd_buffer_size = 2M # As of MariaDB 10 most of these are now 384K as appropriate. Again # for forums, still needed to boost these a bit. # Threads # Defaults are moving up and so should we, it seems. thread_stack = 384K # max_connections is how many connections your server will tolerate at # once, while thread_cache_size is how many of these your server will # cache. There is no reason not to set these to be an equal number; I # have seen no evidence that the trivial amount of RAM a low # thread_cache_size is worth the performance hit of opening up a new # thread under load. # In realistic terms, you should 'tune to failure' - you don't want # to support more active connections than your system can feasibly handle. # 256 is a good number for most low-end servers produced these days. # Increase if you have multiple drive arrays or faster disks. thread_cache_size = 512 max_connections = 512 # Query cache # You may want to check the how useful the # Query cache may actually be for you. # The only additional point I would make here is that changing # query_cache_min_res_unit from the default is effectively useless, # especially if you flush regularly. query_cache_type = ON query_cache_limit = 384K query_cache_size = 128M query_cache_min_res_unit = 4K query_cache_strip_comments = ON # MyISAM # 16M is the most MySQL will store entirely in large-pages. Past that, # it will start shunting some of it off to normal memory. Since almost # everything I do is InnoDB, this isn't a problem. Otherwise, however, # it can make calculating how much space you need difficult. # It is possible that MariaDB has improved on this. However, I am well # past caring for the scant remaining tables that still demand this. key_buffer_size = 16M # Set this to the size of a filesystem block - e.g. 4k key_cache_block_size = 4K # These two should both be the default values. If you are bulk-loading # data from a script, you may want to increase bulk_insert_buffer_size # to speed up operation. bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 128M # Auto-creates a backup when running the recover operation. myisam_recover_options = BACKUP # By default, MyISAM tables will INSERT into deleted row space before # appending to the end of the disk. In exchange for saving this trivial # amount of space, once a row gets deleted, only one insert operation # may occur at a time until holes are filled. Setting concurrent_insert # to 2 stops this silly behavior, at the cost of wasting a bit of disk # space, for a significant performance improvement in MyISAM tables. concurrent_insert = 2 # InnoDB # In theory, you want this to encompass your entire database. In # practice, you only have so much RAM available. # Fortunately, as long as you can store a big enough chunk that it # gets a 99% hit rate, you will be fine. innodb_buffer_pool_size = 8192M # As with everything, only move these if you know you need to. # the log_group files belong with the rest of your database. They use # very little io # innodb_log_group_home_dir = /var/lib/mysql # ibdata1, on the other hand, will be roughly half of your writes. innodb_data_home_dir = /innodb # I set 128M as the size of ibdata1 because that's how big individual # extent entries are in the ext4 filesystem. innodb_autoextend_increment = 128 innodb_data_file_path = ibdata1:128M:autoextend # Supposedly, smaller is better because it makes recovery faster, even # if larger means slightly better performance. I have no idea what the # logic of this is - if I have a crash, downtime is expected. InnoDB # has yet to crash on me in six years. innodb_log_files_in_group = 2 innodb_log_file_size = 512M # Setting innodb_flush_log_at_trx_commit to 0 causes InnoDB to only # flush to disk once per second, improving performance considerably. # In a community environment, you are more likely to lose a topic # read entry or something equally trivial than a post, so the data # is very rarely going to be critical, and users often have backups # of their own posts. innodb_flush_log_at_trx_commit = 0 # file_per_table makes checking out which tables are doing the heavy # lifting a lot easier, for sure. It is also required for the # barracuda file format. innodb_file_per_table innodb_file_format = barracuda # Note that if you have an established database, and haven't used # file_per_table, you'll need to ALTER TABLE each table you want to # split off from your ibdata file. # Enabling strict mode helps prevent messing up creating or altering # a table to support the new row formats in barracuda. # Default is now ON in MariaDB innodb_strict_mode = ON # I haven't played too much with innodb_thread_concurrency. In my # experience, for web purposes at least, Intel architectures work best # up to four times the number of cores. Your mileage may vary. innodb_thread_concurrency = 16 # I set this to sixty because I have obsessive compulsive disorder. # Don't fret over it. innodb_lock_wait_timeout = 60 # O_DIRECT bypasses the operating system's handling of data. This # can drastically improve how well your system handles large amounts # of RAM by removing double buffers (once in InnoDB's cache, again # in the filesystem's cache), at a slight cost to reliability. # This appears to be more dramatic the more RAM you have. # Note that if you've mounted /tmp to tmpfs like a good admin, # InnoDB will whine at you regularly unless you've changed tmpdir to # something that isn't tmpfs. # Changing tmpdir in such a case only matters for your slave server, # but a good idea if you do. innodb_flush_method = O_DIRECT # We like to be able to do cold backups innodb_fast_shutdown = 0 # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # # Error log - should be very few entries. # log_error = /var/log/mysql/error.log # # Enable the slow query log to see queries with especially long duration slow_query_log_file = /var/log/mysql/mariadb-slow.log # You can set this to non-integers. 1 second has been fine for me for ages for this, however. long_query_time = 1 #log_slow_rate_limit = 1000 #log_slow_verbosity = query_plan #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log binlog_cache_size = 384K # Setting sync_binlog fairly high is generally beneficial if the average # value of data is fairly small. sync_binlog = 16 expire_logs_days = 14 max_binlog_size = 512M #binlog_do_db = include_database_name #binlog_ignore_db = exclude_database_name # You can also ignore tables. This can help ease the load on tour replicant servers, # especially for less important data. # # * Security Features # # Read the manual, too, if you want chroot! #chroot = /var/lib/mysql/ # # For generating SSL certificates you can use for example the GUI tool "tinyca". # #ssl-ca = /etc/mysql/cacert.pem #ssl-cert = /etc/mysql/server-cert.pem #ssl-key = /etc/mysql/server-key.pem # # Accept only connections using the latest and most secure TLS protocol version. # ..when MariaDB is compiled with OpenSSL: #ssl-cipher = TLSv1.2 # ..when MariaDB is compiled with YaSSL (default in Debian): #ssl = on # # * Character sets # # MySQL/MariaDB default is Latin1, but in Debian we rather default to the full # utf8 4-byte character set. See also client.cnf # character-set-server = utf8mb4 collation-server = utf8mb4_general_ci # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Unix socket authentication plugin is built-in since 10.0.22-6 # # Needed so the root database user can authenticate without a password but # only when running as the unix root user. # # Also available for other users if required. # See https://mariadb.com/kb/en/unix_socket-authentication-plugin/ # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.3 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.3]
Final steps
If you are using the Query Cache, then you will need to setup a cron to flush it regularly, else you are going to see the problems people complain about a lot sooner. First, make an appropriate user:
- In MySQL:
- GRANT RELOAD ON *.* TO qcmaint@localhost IDENTIFIED BY 'crazylongpass';
- In a script file, run
- /usr/bin/mysql -u qcmaint -p"crazylongpass" -e 'FLUSH QUERY CACHE;'
- via cron, two to three times an hour. My /etc/cron.d/mysql:
- 4,24,44 * * * * root /root/flushqc.sh
- My experience is that there is a spot between running it every half hour and every 15 minutes where flushing no longer helps - you should probably stop using the query cache entirely if this breaks down for you.
/root/.my.cnf
No point in hiding this, naturally. Since root can always set the MySQL password, it can be handy just to stick a giant uncrackable mess in /root/.my.cnf rather than have something crackable:
[client] user=root password=yourrandompasshere socket=/run/mysqld/mysqld.sock