MySQL configuration best practices

AdamF

So we all use mySQL/Maria DB for various open source projects around here. I would like to start a discussion about best practices regarding mySQL. There are a ton of configuration options that you can make in the my.cnf configuration file. Some are very important, while others might not make that much of a difference. One of the things taht make a huge difference in regards to disk space, is the use of binary loging. Most barebone installations of mysql/mariadb, enable binary logging, but do not make any additional entries for log rotation/expiration of maximum file size.

For example: adding these 2 lines to the my.cnf file is really important, if you are in fact using binary logging, and do not want mysql takign up all of your disk space with this.

expire_logs_days=X
max_binlog_size=X

What are some other parameters to optimize mariadb/mysql? It will vary from workload to workload, but it would be great to get a discussion going on this.

QuixoticJustin

Best practice is also to either manually configure security or more commonly to run the included secure script to lock things down.

dbeato

Have you ever used Percona?

Emad R

@fuznutz04

if you are replicating 2 MySQL instances you need
expire_logs_days=X
to be enabled, however having a high value will truly eat alot of space.

AdamF

@dbeato said in MySQL configuration best practices:

Have you ever used Percona?

No, never. What is it?

AdamF

@emad-r said in MySQL configuration best practices:

@fuznutz04

if you are replicating 2 MySQL instances you need
expire_logs_days=X
to be enabled, however having a high value will truly eat alot of space.

Very true.

dbeato

@fuznutz04 Percona is use for HA of MYSQL and replication.

jmoore

I use the lockdown script and mysql tuner. I am sure there are many other things I need to do but have not had time to investigate yet. I use mariadb.

Emad R

This post is deleted!

Emad R

@quixoticjustin

But that only covers the security aspect, what we are talking about is performance tweaks this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0	# Disabling symbolic-links is recommended to prevent assorted security risks
default-storage-engine=innodb
character-set-server=utf8
collation-server=utf8_general_ci

# CACHES AND LIMITS #
tmp-table-size=32M
max-heap-table-size=32M
query-cache-type=0
query-cache-size=0
max-connections=500
thread-cache-size=50
open-files-limit=65535
table-definition-cache=1024
table-open-cache=2048
thread_concurrency=4  	#recommend 2x CPU cores [0 create as many as needed]
thread_cache_size=100	#recommend 5% of max_connections

# INNODB #
innodb-buffer-pool-size=2G	#recommend 50% of RAM
innodb-flush-log-at-trx-commit=1
innodb-file-per-table=1
innodb_write_io_threads=2	#recommend 50% of CPU
innodb_read_io_threads=2	#recommend 50% of CPU
innodb_log_buffer_size=256M 	#global buffer
innodb_lock_wait_timeout=300	

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

That said if you dont know what you are doing, it is best to leave it default in a database world.

AdamF

@emad-r

What is your practice for log rotation? For example, the error log. If that grows (obviously indicating other issues) , and needs to be rotated, what procedure do you use to rotate logs?

Emad R

@fuznutz04 said in MySQL configuration best practices:

@emad-r

What is your practice for log rotation? For example, the error log. If that grows (obviously indicating other issues) , and needs to be rotated, what procedure do you use to rotate logs?

Currently the third party vendor uses this:

expire_logs_days = 10
max_binlog_size = 100M

In our MySQL instance.