-
-
Save coolpalani/712aac15cbb455477fb4790787127068 to your computer and use it in GitHub Desktop.
Optimized MySQL configuration for cPanel servers (updated July 2017)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Optimized MySQL configuration for cPanel servers by Fotis Evangelou - Updated July 2017 | |
# | |
# 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 should adjust accordingly to save CPU, | |
# RAM and disk I/O usage. | |
# The settings marked with a specific comment or the word "UPD" after the value | |
# should be adjusted for your system by using MySQL DB diagnostics tools like: | |
# http://mysqltuner.com/ | |
# or | |
# https://launchpad.net/mysql-tuning-primer (supports MySQL up to v5.6) | |
# | |
# | |
# Note that if there is NO comment after a setting value, then 99,9% of the times you won't need to adjust it. | |
# | |
# | |
# THINGS TO DO AFTER YOU UPDATE MY.CNF - TROUBLESHOOTING | |
# If MySQL cannot start or restart (most probably), then perform the following actions. | |
# If any terminal commands are mentioned, make sure you execute them as "root" user: | |
# | |
# 1. If the server had the stock MySQL configuration and you addded or updated any | |
# "innodb-log-*" settings (as suggested below), then execute these commands ONLY | |
# the first time you apply this configuration: | |
# | |
# $ rm -rvf /var/lib/mysql/ib_logfile* | |
# $ touch /var/lib/mysql/mysql.sock | |
# $ touch /var/lib/mysql/mysql.pid | |
# $ chown -R mysql:mysql /var/lib/mysql | |
# $ /scripts/restartsrv_mysql | |
# | |
# or use the shorthand command: | |
# $ rm -rvf /var/lib/mysql/ib_logfile*; touch /var/lib/mysql/mysql.sock; touch /var/lib/mysql/mysql.pid; chown -R mysql:mysql /var/lib/mysql; /scripts/restartsrv_mysql | |
# | |
# 2. If the setting "bind-address" is not commented out, then make sure the file /etc/hosts is | |
# properly configured. A good example of a "clean" /etc/hosts file is something like this: | |
# | |
# 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 | |
# ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 | |
# 1.2.3.4 hostname.domain.tld hostname | |
# | |
# Finally restart MySQL: | |
# | |
# $ /scripts/restartsrv_mysql | |
# | |
# 3. If MySQL cannot restart even after the first 2 steps, make sure the MySQL data folder: | |
# /var/lib/mysql | |
# is owned by the "mysql" user AND group. Additionally, the folder itself can have 0751 or 0755 | |
# file permissions. To fix it, simply do this: | |
# $ chown -R mysql:mysql /var/lib/mysql | |
# $ chmod 0755 /var/lib/mysql | |
# | |
# Finally restart MySQL: | |
# | |
# $ /scripts/restartsrv_mysql | |
[client] | |
port = 3306 | |
socket = /var/lib/mysql/mysql.sock | |
[mysqld-safe] | |
socket = /var/lib/mysql/mysql.sock | |
nice = 0 | |
[mysqld] | |
# Basic | |
basedir = /usr | |
bind-address = 127.0.0.1 # Comment out if you want remote servers to connect to this server's MySQL instance | |
datadir = /var/lib/mysql | |
lc-messages-dir = /usr/share/mysql | |
max-allowed-packet = 128M | |
max-connect-errors = 1000000 | |
pid-file = /var/lib/mysql/mysql.pid | |
port = 3306 | |
skip-external-locking | |
skip-name-resolve | |
socket = /var/lib/mysql/mysql.sock | |
tmpdir = /tmp | |
user = mysql | |
# InnoDB Settings | |
default-storage-engine = InnoDB | |
innodb-buffer-pool-instances = 2 # Use 1 instance per 1GB of InnoDB pool size | |
innodb-buffer-pool-size = 2G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0 | |
innodb-file-per-table = 1 | |
innodb-flush-log-at-trx-commit = 0 | |
innodb-flush-method = O_DIRECT | |
innodb-log-buffer-size = 16M | |
innodb-log-file-size = 128M | |
#innodb-thread-concurrency = 4 # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better contain CPU usage | |
# E.g. if your system has 8 CPUs, try 6 or 7 and check the overall load from MySQL. | |
# MyISAM Query Cache Settings | |
query-cache-limit = 4M # UPD | |
query-cache-size = 48M # UPD | |
query-cache-type = 1 | |
key-buffer-size = 48M # UPD | |
low-priority-updates = 1 | |
concurrent-insert = 2 | |
# Common | |
max-connections = 100 # UPD | |
back-log = 512 | |
wait-timeout = 90 | |
interactive-timeout = 90 | |
join-buffer-size = 2M # UPD | |
read-buffer-size = 2M # UPD | |
read-rnd-buffer-size = 4M # UPD | |
sort-buffer-size = 4M # UPD | |
thread-cache-size = 100 # UPD (most of the times you probably won't need to change this) | |
thread-stack = 192K | |
max-heap-table-size = 128M | |
tmp-table-size = 128M | |
table-definition-cache = 8000 # UPD | |
table-open-cache = 8000 # UPD | |
open-files-limit = 24000 # UPD | |
ft-min-word-len = 3 # Minimum length of words to be indexed for search results | |
expire-logs-days = 7 | |
log-error = /var/lib/mysql/mysql_error.log | |
log-queries-not-using-indexes = 1 | |
long-query-time = 8 | |
max-binlog-size = 100M | |
slow-query-log = 1 | |
slow-query-log-file = /var/lib/mysql/mysql_slow.log | |
[mysqldump] | |
quick | |
quote-names | |
max-allowed-packet = 16M | |
[mysql] | |
[isamchk] | |
key-buffer-size = 16M |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment