MySQL configuration best practices

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.