Saya seringkali pusing menentukan setting mysql setiap kali melakukan instalasi server hosting yang baru. Untuk itu catatan ini dibuat agar tidak hilang dan bisa tinggal disesuaikan saja dengan kebutuhan masing-masing server yang saya pakai kedepannya.
Settingan ini paling pas digunakan untuk server dengan Ram 8 GB dan 4 core, utamanya untuk panel WHM dan engintron, dikarenakan settingan ini pada dasarnya dibuat oleh pembuat engintron, dan saya hanya menyesuaikan sedikit sesuai dengan server yang biasa saya pakai.
Tidak hanya cPanel / WHM, setting mysql ini juga saya pakai untuk kontrol panel lain seperti DirectAdmin dan juga CWP, dan sejauh ini tidak ada masalah dengan performa yang diberikan. Untuk kapasitas jumlah website yang bisa ditampung kira-kira sekitar 80 hingga 120 website dengan engine WordPress. Untuk diketahui, server ini rata-rata penggunanya adalah untuk kebutuhan landing page, online shop, blog ringan, dan beberapa portal berita.
Catatan ini saya buat agar mudah ditemukan apabila saya butuhkan kedepannya. Apabila kamu merasa settingan ini cocok untukmu, silahkan dicopas, dan disesuaikan dengan kebutuhan server yang kamu gunakan. Dan jangan lupa lakukan backup sebelum mengganti my.cnf dengan settingan dibawah.
[mysql]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# Required Settings
basedir = /usr
bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections
datadir = /var/lib/mysql
max_allowed_packet = 256M
max_connect_errors = 1000000
pid_file = /var/lib/mysql/mysql.pid
port = 3306
skip_external_locking
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
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 = 512M
innodb_stats_on_metadata = 0
#innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file
#innodb_thread_concurrency = 3 # 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 produced by MySQL/MariaDB.
innodb_read_io_threads = 64
innodb_write_io_threads = 64
# MyISAM Settings
query_cache_limit = 4M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
query_cache_size = 64M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
query_cache_type = 1 # Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
key_buffer_size = 32M # UPD
low_priority_updates = 1
concurrent_insert = 2
# Connection Settings
max_connections = 70 # UPD - Important: high no. of connections = more RAM consumption
back_log = 512
thread_cache_size = 100
thread_stack = 192K
interactive_timeout = 180
wait_timeout = 180
# For MySQL 5.7+ only (disabled by default)
#max_execution_time = 30000 # Set a timeout limit for SELECT statements (value in milliseconds).
# This option may be useful to address aggressive crawling on large sites,
# but it can also cause issues (e.g. with backups). So use with extreme caution and test!
# More info at: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time
# For MariaDB 10.1.1+ only (disabled by default)
#max_statement_time = 30 # The equivalent of "max_execution_time" in MySQL 5.7+ (set above)
# The variable is of type double, thus you can use subsecond timeout.
# For example you can use value 0.01 for 10 milliseconds timeout.
# More info at: https://mariadb.com/kb/en/aborting-statements/
# Buffer Settings
join_buffer_size = 4M # UPD
read_buffer_size = 3M # UPD
read_rnd_buffer_size = 4M # UPD
sort_buffer_size = 4M # UPD
# Table Settings
# In systemd managed systems like CentOS 7, you need to perform an extra action for table_open_cache & open_files_limit
# to be overriden (also see comment next to open_files_limit).
# E.g. for MySQL 5.7 (when it's supported in cPanel), please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
# and for MariaDB check: https://mariadb.com/kb/en/library/systemd/
table_definition_cache = 40000 # UPD
table_open_cache = 40000 # UPD
open_files_limit = 60000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's
# open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf
# In systemd managed systems this limit must also be set in:
# /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+) and
# /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)
max_heap_table_size = 128M
tmp_table_size = 128M
# Search Settings
ft_min_word_len = 3 # Minimum length of words to be indexed for search results
# Logging
log_error = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes = 1
long_query_time = 5
slow_query_log = 0 # Disabled for production
slow_query_log_file = /var/lib/mysql/mysql_slow.log
[mysqldump]
# Variable reference
# For MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
# For MariaDB: https://mariadb.com/kb/en/library/mysqldump/
quick
quote_names
max_allowed_packet = 64M
Sekian dan terima gaji.
