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.
