Skip to content

Instantly share code, notes, and snippets.

@Gomah
Created April 28, 2017 04:09
Show Gist options
  • Save Gomah/0859312d94619469a2a2b27e715c964c to your computer and use it in GitHub Desktop.
Save Gomah/0859312d94619469a2a2b27e715c964c to your computer and use it in GitHub Desktop.
MySQL config for 4 cores/8 threads, 32 GB RAM
# CLIENT
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# MYSQL SAFE
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
# GENERAL
user = mysql
default_storage_engine = InnoDB
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
# MyISAM
key_buffer_size = 32M
myisam_recover = FORCE,BACKUP
# SAFETY
innodb = FORCE
innodb_strict_mode = 1
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
# BINARY LOGGING
log_bin = /var/log/mysql/mysql-bin
expire_logs_days = 14
sync_binlog = 1
# CACHES AND LIMITS
max_connections = 500
max_heap_table_size = 32M
open_files_limit = 65535
query_cache_type = 0
query_cache_size = 0
table_definition_cache = 4096
table_open_cache = 10240
thread_cache_size = 50
tmp_table_size = 32M
# INNODB
innodb_buffer_pool_size = 26G
innodb_buffer_pool_instances = 4
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
# LOGGING
log_error = /var/log/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0
@M-Shahbaz
Copy link

M-Shahbaz commented Nov 16, 2021

On Ubuntu 20.04:
nano /etc/mysql/mysql.conf.d/mysqld.cnf

# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#
# * Basic Settings
#
user            = mysql
# pid-file      = /var/run/mysqld/mysqld.pid
# socket        = /var/run/mysqld/mysqld.sock
# port          = 3306
# datadir       = /var/lib/mysql


# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir                = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1
#
# * Fine Tuning
#
##key_buffer_size               = 16M
# max_allowed_packet    = 64M
# thread_stack          = 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP

# max_connections        = 151

# table_open_cache       = 4000

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log                = 1
# slow_query_log_file   = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id             = 1
# log_bin                       = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds    = 2592000
max_binlog_size   = 100M
# binlog_do_db          = include_database_name
# binlog_ignore_db      = include_database_name

# MyISAM
key_buffer_size                = 32M
#myisam_recover                 = FORCE,BACKUP

# SAFETY
innodb                         = FORCE
innodb_strict_mode             = 1
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve

# BINARY LOGGING
log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS
max_connections                = 500
max_heap_table_size            = 32M
open_files_limit               = 65535
#query_cache_type               = 0
#query_cache_size               = 0
table_definition_cache         = 4096
table_open_cache               = 10240
thread_cache_size              = 50
tmp_table_size                 = 32M

# INNODB
innodb_buffer_pool_size        = 26G
innodb_buffer_pool_instances   = 4
innodb_flush_method            = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_log_files_in_group      = 2
innodb_log_file_size           = 512M

# LOGGING
slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/mysql-slow.log

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links                 = 0

@Nirjonadda
Copy link

Any recommendation MySQL 8 optimization for AlmaLinux v8 with 10 CPUs and 32 GB ram?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment