MySQL configuration best practices

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.