MariaDB / MySQL Config InnoDB – 2 CPU, 4 GB RAM

Challenge: Set up a good (initial) my.cnf file for database config, of a cPanel (CentOS) server with 2 CPUs, and 4 GB of memory.

Solution: Use the directives from the sample file below. The location of the file in cPanel is: /etc/my.cnf

After the database has operated for at least 24 hours, then effect small tuning tweaks (and test operations over each 24 hours, for each change).

[mysqld]
performance-schema = 0

# General #
local-infile = 0
bind-address = 127.0.0.1
default-storage-engine = InnoDB

# MyISAM #
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP

# Cache 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 = 4096
table-open-cache = 4096
max-allowed-packet = 16M
max-connect-errors = 1000000
# open_files_limit = 10000

# InnoDB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 128M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 2G

# Collation #
init_connect = 'SET collation_connection=utf8_unicode_ci'
init-connect = 'SET NAMES utf8'
character-set-server = utf8
collation-server = utf8_unicode_ci

# Slow Query Log #
# slow_query_log = On #
# long_query_time = 0.4 #
# slow_query_log_file = /var/log/slow_query #

[client]

[mysqld_safe]

1 thought on “MariaDB / MySQL Config InnoDB – 2 CPU, 4 GB RAM”

Leave a comment

BlogLogistics