MySQL does not provide a default production setup when installed. It can serve connections and queries up to a limit and then it will start rejecting the connections. In this blog, we will explore the configuration variables that can be used to optimize the performance of the MySQL server and use the maximum resources from the server so that maximum efficiency can be brought out without increasing server size.
The file we will be making changes is: mysqld.cnf
This file can be found at /etc/mysql/mysql.conf.d/mysqld.cnf . You can start adding the variables at the end of the file. You can also add the variables in file /etc/mysql/my.cnf but you will need to add those variables below [mysqld] text.
We will explore the variables one by one and at the end of the blog, you will find the complete file containing all the variables which can be added directly. The values used as examples are the minimum resource configuration which are tested for runtime metrics.
Here’s an explanation of each variable in the provided my.cnf (MySQL configuration) file:
[mysqld]
This specifies that the following settings are for the MySQL server daemon.
Basic Configuration:
port = 3306: Defines the port on which MySQL will listen for connections (default is 3306).socket = /tmp/mysql.sock: Specifies the socket file location used for local connections.
Connection Management:
back_log = 50: Determines how many connections can be in the queue before MySQL starts rejecting new ones.max_connections = 100: Sets the maximum number of simultaneous client connections MySQL can handle.wait_timeout = 256: The amount of time (in seconds) a connection can remain idle before being closed.max_connect_errors = 10: If there are more than this number of failed connection attempts from a host, MySQL blocks it until you flush the host’s table or manually unblock it.
Cache and Buffer Sizes:
table_open_cache = 2048: Specifies how many table descriptors MySQL can keep open simultaneously. A higher number can help reduce disk I/O.max_allowed_packet = 16M: The maximum size of one packet or any generated/intermediate string.max_heap_table_size = 512M: The maximum size for in-memory tables (Heap tables).
Buffer Settings (Used for Performance Tuning):
read_buffer_size = 64M: The size of the buffer used for sequential reads (large scans).read_rnd_buffer_size = 64M: The size of the buffer used when reading rows in a sorted order.sort_buffer_size = 64M: The buffer size used for sorts performed during query execution.join_buffer_size = 64M: The buffer used for joins that do not use indexes (improves join performance).
Thread Management:
thread_cache_size = 8: The number of threads to store in the cache to handle new connections. Helps reduce the overhead of thread creation for each new connection.thread_stack = 240K: The size of the memory stack for each thread.
Full-Text Search and Storage Engine:
ft_min_word_len = 4: The minimum word length considered by full-text searches (useful for text-based indexing).default-storage-engine = InnoDB: Specifies the default storage engine (InnoDB) for creating new tables.transaction_isolation = REPEATABLE-READ: The default transaction isolation level, meaning a transaction will see the state of the database when it began, ignoring changes made by others.
Temporary Table Size:
tmp_table_size = 512M: The maximum size of temporary tables that MySQL will create in memory. If the table exceeds this, it will be written to disk.
Slow Query Log:
slow_query_log: Enables logging of slow queries.long_query_time = 2: Queries taking longer than this time (in seconds) are logged as slow queries.
Replication:
server-id = 1: A unique identifier for the MySQL server, often used for replication setups.
InnoDB Options (For Performance and Reliability):
innodb_buffer_pool_size = 4G: The size of the buffer pool, which is the memory area where InnoDB caches data and indexes.innodb_buffer_pool_instances = 8: The number of buffer pool instances. Splitting the buffer pool into multiple instances helps improve concurrency.innodb_data_file_path = ibdata1:10M:autoextend: Specifies the name, size, and properties of the InnoDB system tablespace.innodb_write_io_threads = 8: Number of I/O threads used for writing operations in InnoDB.innodb_read_io_threads = 8: Number of I/O threads used for reading operations in InnoDB.innodb_thread_concurrency = 16: Limits the number of threads allowed to enter the InnoDB kernel at the same time.innodb_flush_log_at_trx_commit = 1: Defines the behavior for flushing the transaction log to disk. Setting it to 1 means data is written and flushed to the log after every transaction (for durability).innodb_log_buffer_size = 1GB: The size of the buffer that InnoDB uses to write log data before flushing it to disk.innodb_change_buffering = all: Enables buffering of different types of changes (inserts, updates, deletes).innodb_change_buffer_max_size = 25: The maximum size of the insert buffer as a percentage of the buffer pool.innodb_log_file_size = 125M: The size of each log file in the InnoDB log group.innodb_log_files_in_group = 2: The number of log files in the group (used in a circular fashion).innodb_max_dirty_pages_pct = 90: The percentage of dirty pages (pages in memory that need to be written to disk) that triggers background flushing.innodb_lock_wait_timeout = 256: The time (in seconds) a transaction waits before InnoDB rolls back a transaction due to lock waits.innodb_file_per_table: This enables each InnoDB table to have its own tablespace file, rather than using the shared system tablespace.
Time Zone:
default_time_zone = +00:00: The default time zone for the MySQL server. This setting ensures the server operates in the UTC time zone.
This configuration is tuned for performance and reliability in high-traffic or resource-intensive environments, especially for systems using the InnoDB storage engine.
Below is the complete file which can be used directly in my.cnf .
[mysqld]
port = 3306
socket = /tmp/mysql.sock
back_log = 50
max_connections = 100
wait_timeout = 256
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
max_heap_table_size = 512M
read_buffer_size = 64M
read_rnd_buffer_size = 64M
sort_buffer_size = 64M
join_buffer_size = 64M
thread_cache_size = 8
thread_stack = 240K
ft_min_word_len = 4
default-storage-engine = InnoDB
transaction_isolation = REPEATABLE-READ
tmp_table_size = 512M
slow_query_log
long_query_time = 2
server-id = 1
# INNODB options
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 1GB
innodb_change_buffering = all
innodb_change_buffer_max_size = 25
innodb_log_file_size = 125M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 256
innodb_file_per_table
default_time_zone = +00:00