3.0.1.9. Maria db settings

[henk]

After the upgrade your mysql settings versus MariaDB could need some tweeking:

Remember some setting depends on other settings ( see the *** lines)

You can use Mysqltuner to check before and after the restart

My current 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]

symbolic-links=0
skip-external-locking
skip-host-cache
skip-name-resolve

thread_cache_size = 4

query_cache_type = 0
query_cache_size = 0M

read_buffer_size = 2M
read_rnd_buffer_size = 1M

key_buffer_size = 64M
join_buffer_size = 2M

max_connections = 30 # ***

myisam_sort_buffer_size = 128M
max_heap_table_size = 32M
tmp_table_size = 32M
sort_buffer_size = 2M

tmpdir = /tmp

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
bind-address = 127.0.0.1
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# 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.1 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.1]
innodb-defragment = 1
innodb_file_per_table = 1
innodb_buffer_pool_size= 512M # ***
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit =2
innodb_buffer_pool_instances = 1 # *** innodb_buffer_pool_size < 1GB

[shawniverson]

Please share with us the importance of the changes

We can roll them into the next update depending on level of importance

[henk]

Ok. It's quite a story, but did my best to add some useful comments

# Disabling symbolic-links is recommended to prevent assorted security risks;
symbolic-links=0

#If the server is run with external locking enabled, you can use myisamchk at any time for read operations
#such a checking tables. In this case, if the server tries to update a table that myisamchk is using,
#the server will wait for myisamchk to finish before it continues.
#skip-external-locking

# Only accept connections to localhost aka bump security
bind-address = 127.0.0.1

# It does not use the cache for TCP connections established using a loopback interface address (127.0.0.1)
skip-host-cache

# To disable DNS host name lookups, speed up connections
skip-name-resolve

# If set to 1, (0 is the default), the server does not listen for TCP/IP
# connections. All interaction with the server by be through socket files
# (Unix) or named pipes or shared memory (Windows). It's recommended to use
# this option if only local clients are permitted to connect to the server.
# Enabling this option also prevents a server from functioning as a replication client.
# Scope: Global <br>
# Default Value: 0 #
skip-networking

# Size of the buffer for the index blocks used by MyISAM tables and shared
# for all threads. See Optimizing key_buffer_size for more on selecting the
# best value. #
# Default Value: 134217728
# Scope: Global
# https://mariadb.com/kb/en/mariadb/myisa ... variables/#
#
key_buffer_size = 64M

# Scope: Global, Session Default Value: 2M (2097152) (some distributions
# increase the default)
# https://mariadb.com/kb/en/mariadb/serve ... variables/#
#
sort_buffer_size = 2M

# Size in bytes of the buffer allocated when creating or sorting indexes on
# a MyISAM table. #
# Scope: Global, Session
# Default Value: 134217720
# https://mariadb.com/kb/en/mariadb/myisa ... variables/#
#
myisam_sort_buffer_size = 128M

# Each thread performing a sequential scan (for MyISAM, Aria and MERGE
# tables) allocates a buffer of this size in bytes for each table scanned.
# Increase if you perform many sequential scans. If not in a multiple of
# 4KB, will be rounded down to the nearest multiple. Also used in ORDER
# BY's for caching indexes in a temporary file (not temporary table), for
# caching results of nested queries, for bulk inserts into partitions, and
# to determine the memory block size of MEMORY tables. #
# Scope: Global, Session # Default Value: 131072
# https://mariadb.com/kb/en/mariadb/serve ... variables/#
#
read_buffer_size = 2M

# Size in bytes of the buffer used when reading rows from a MyISAM table in
# sorted order after a key sort. Larger values improve ORDER BY
# performance, although rather increase the size by SESSION where the need
# arises to avoid excessive memory use. #
# Scope: Global, Session
# Default Value: 262144
# https://mariadb.com/kb/en/mariadb/serve ... variables/#
#
read_rnd_buffer_size = 1M

# Minimum size of the buffer used for queries that cannot use an index, and
# instead perform a full table scan. Increase to get faster full joins when
# adding indexes is not possible, although be aware of memory issues, since
# joins will always allocate the minimum size. Best left low globally and
# set high in sessions that require large full joins. In 64-bit platforms,
# Windows truncates values above 4GB to 4GB with a warning. #
# Scope: Global, Session
# Default Value: 256K (>=MariaDB 10.1.7), 131072 (128KB) (<=MariaDB 10.1.6)
# https://mariadb.com/kb/en/mariadb/serve ... variables/#
#
join_buffer_size = 2M

# Number of threads server caches for re-use. If this limit hasn't been
# reached, when a client disconnects, its threads are put into the cache,
# and re-used where possible. Normally this setting has little effect, as
# the other aspects of the thread implementation are more important, but
# increasing it can help servers with high volumes of connections per
# second so that most can use a cached, rather than a new, thread. The
# cache miss rate can be calculated as the server status variables
# threads_created/connections. If the thread pool is active,
# thread_cache_size is ignored. #
# Scope: Global # Default Value: 0
# https://mariadb.com/kb/en/mariadb/serve ... -variables
#
thread_cache_size = 4

# max_connections = max connections possible) # Check first with mysqltuner
max_connections = 30

# If set to 1, new XtraDB/InnoDB tables are created with data and indexes
# stored in their own .ibd file. If set to 0, the default, new tables are
# created in the system tablespace. Compression is only available with per
# table storage. Note that this value is also used when a table is
# re-created with an ALTER TABLE which requires a table copy. #
# Scope: Global
# Default Value: ON (>= MariaDB 5.5), OFF (<= MariaDB 5.3)
# https://mariadb.com/kb/en/mariadb/xtrad ... variables/#
#
innodb_file_per_table = 1

# When sorting (ORDER BY or GROUP BY), MySQL normally uses one or two temporary files.
# temporary files are removed if mysqld is terminated
# make sure it's /tmp and not /usr/tmp.
tmpdir = /tmp

#use O_DIRECT to open the data files and fsync() system call to flush both the data and log files.
innodb_flush_method=O_DIRECT

# this value is related to the size of your db ( see mysqltuner)
# default 128 MB
innodb_buffer_pool_size= 512M

# if the innodb_buffer_pool_size < 1GB
innodb_buffer_pool_instances = 1

# amazing option.
# This new defragmentation feature works inplace, thus no new tables are created and there is no need to copy data from old table to new table.
#Instead this feature loads n pages and tries to move records so that pages would be full of records and frees pages that are fully empty after the operation.
# see https://mariadb.org/defragmenting-unuse ... ablespace/
innodb-defragment = 1

# you need a ups to safely use this
#Your safest choice is
#[mysqld]
#innodb_flush_log_at_trx_commit=1
#sync_binlog=1
#If you do not mind possible data loss (up to 1 second's worth) then you can use either 0 or 2 at your own risk if the rewards (faster write speed) are worth it.

innodb_flush_log_at_trx_commit =2

[pdwalker]

If I only had to make one change, It'd be this:
innodb_file_per_table = 1

I just checked my database files, and they currently are already in innodb_file_per_table format, and "show variables" already shows the innodb_file_per_table variable is already on.

Not sure where that was set, maybe it's a new default for mariadb?

Anyway, the defaults out of the box are reasonable. They only need tweaking if you have a busy mail server (which I don't).