Optimized MySQL configuration

my.cnf

# Optimized MySQL configuration by Fotis Evangelou - Updated Jan 2016
#
# The settings provided below are a starting point for a 4GB-8GB RAM server with 4 CPU cores.
# If you have less or more resources available you MUST adjust accordingly to save CPU, RAM and disk I/O usage.
# To fine tune these settings for your system, use MySQL DB diagnostics tools like:
# https://launchpad.net/mysql-tuning-primer
# or
# http://blog.mysqltuner.com/download/
# Note that if there is no comment beside a setting, then you don't need to adjust it.

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
basedir = /usr
bind-address = 127.0.0.1 # Comment this line if you want remote clients to connect to your MySQL instance
datadir = /var/lib/mysql
lc-messages-dir = /usr/share/mysql
max_allowed_packet = 16M
myisam-recover = BACKUP
pid-file = /var/run/mysqld/mysqld.pid
port = 3306
skip-external-locking
socket = /var/run/mysqld/mysqld.sock
thread_stack = 192K
tmpdir = /tmp
user = mysql

# ============================================================ #
# =============== Custom server tweaks [start] =============== #
# ============================================================ #

default_storage_engine = InnoDB
innodb_buffer_pool_size = 2G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0. Use Tuning Primer or MySQL Tuner to adjust accordingly.
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M # Delete the ib_logfile0 & ib_logfile1 files in /var/lib/mysql whenever you change this, even the first time you set it up, as the default value is 8M. Don't go above 256M.

query_cache_limit = 3M # The max size of queries to cache. Use Tuning Primer or MySQL Tuner to adjust accordingly.
query_cache_size = 48M # The total query cache size for MyISAM tables. Use Tuning Primer or MySQL Tuner to adjust accordingly.
query_cache_type = 1

max_connections = 80 # Total connection limit for all MySQL users. Adjusting this too high will result in excess resource usage even if it's not required. Use Tuning Primer or MySQL Tuner to adjust accordingly.
max_user_connections = 60 # Per user connection limit

key_buffer_size = 48M # Use Tuning Primer or MySQL Tuner to adjust accordingly.

wait_timeout = 300 # Using 300 secs for maintaining connections keeps the entire connections count low, thus decreasing resource consumption.

join_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
sort_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
read_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
read_rnd_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.

max_heap_table_size = 128M # According to Percona, these values do not affect performance. Percona sets those to 32M. 128M is a reasonable value though.
tmp_table_size = 128M # See above.

table_definition_cache = 3000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
table_open_cache = 3000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
open_files_limit = 10000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.

low_priority_updates = 1
concurrent_insert = 2
thread_cache_size = 40 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
thread_concurrency = 8 # Set to twice the number of cores (2 x 4 cores in this example)

ft_min_word_len = 3 # Minimum length of words to be indexed for search results

log-error = /var/lib/mysql/mysql_error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql_slow.log
long_query_time = 5
expire_logs_days = 7
max_binlog_size = 100M

# ============================================================ #
# ============== Custom server tweaks [finish] =============== #
# ============================================================ #

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]

[isamchk]
key_buffer = 16M