This document provides an in-depth analysis and guide for tuning MySQL performance based on the server configuration found in /etc/my.cnf. Each setting is dissected to understand its impact on performance and how to adjust it according to the needs of your environment.
The configuration file located at /etc/my.cnf contains several sections that are crucial for MySQL performance tuning. These sections include basic settings, performance tuning parameters, and settings for logging and replication. Below is a description of each configuration and its recommended adjustments.
symbolic-links=0
Disables the use of symbolic links for the database files, enhancing security by preventing unauthorized data manipulation.
skip-name-resolve
MySQL will not perform DNS lookups for client connections. This reduces connection times when clients connect to the server, especially in environments with slow or unreliable DNS servers.
slow-query-log=1
Enables the logging of slow queries, which are queries that take longer than long_query_time seconds to execute. This is crucial for identifying inefficient queries.
slow-query-log-file=/var/lib/mysql/mysql-slow-query.log
Specifies the file where slow queries are logged.
long_query_time=1
Defines the threshold in seconds for considering a query as slow. Here, any query taking more than 1 second will be logged.
max_connections=1000
Sets the maximum number of concurrent connections to the server. Reducing this value can lower memory usage but be sure to adjust based on actual need.
innodb_buffer_pool_size=8G
Sets the size of the buffer pool, where InnoDB caches table and index data. Ideally, this should be up to 80% of RAM in dedicated servers, but 50% is used here to leave room for other processes. Calculation: Total RAM * 0.5.
innodb_log_file_size=1G
Defines the size of the InnoDB redo log files. A good starting point is 1GB, but this should be adjusted based on the workload and the amount of writes.
innodb_file_per_table=ON
Enables storing InnoDB tables in separate files, which can improve performance and make backups easier.
thread_cache_size=50
Sets the size of the cache for threads. Adjust this based on monitoring your server's thread usage to improve efficiency.
query_cache_size=0
Disables the query cache. This is recommended for modern MariaDB/MySQL versions, as the query cache can often reduce performance.
wait_timeout=60
The number of seconds the server waits for activity on a non-interactive connection before closing it.
max_allowed_packet=16M
Sets the maximum packet size for network communication. Adjust as needed based on your data sizes.
tmp_table_size=128M and max_heap_table_size=128M
These settings control the maximum size of internal in-memory temporary tables. Reducing their values can help lower memory usage. Ensure they are matched to prevent issues.
log_bin=mysql-bin
Enables binary logging, which is essential for replication and can be useful for data recovery.
expire_logs_days=3
Automatically deletes binary logs older than 3 days, helping manage disk space.
max_binlog_size=100M
Limits the size of each binary log file to prevent them from becoming too large.
server-id=1
Specifies the server ID, which is essential for replication setups.
binlog_format=mixed
Sets the binary logging format to mixed, which is a balance between performance and data integrity.
The !includedir /etc/my.cnf.d directive includes additional configuration files from the /etc/my.cnf.d directory. This allows for modular configuration and easier management.
[client-server]
[mysqld]
# Basic Settings
symbolic-links=0
skip-name-resolve
slow-query-log=1
slow-query-log-file=/var/lib/mysql/mysql-slow-query.log
long_query_time=1
# Performance Tuning
max_connections=1000 # Reduced to lower memory usage, adjust based on actual need
innodb_buffer_pool_size=8G # Adjusted to 50% of RAM to leave room for other processes
innodb_log_file_size=1G # Good starting point, adjust based on workload
innodb_file_per_table=ON
thread_cache_size=50 # Adjusted based on monitoring thread usage
query_cache_size=0 # Disabled for modern MariaDB, consider enabling if needed
wait_timeout=60
max_allowed_packet=16M
tmp_table_size=128M # Reduced to lower memory usage
max_heap_table_size=128M # Match tmp_table_size to ensure consistency
# Ensure these settings do not negatively impact your application's performance
# Logging and Replication (if applicable)
log_bin=mysql-bin
expire_logs_days = 3 # Automatically delete binary logs older than 3 days
max_binlog_size = 100M # Limit each binary log file to a maximum of 100MB
server-id=1
binlog_format=mixed
!includedir /etc/my.cnf.d
Tuning MySQL's performance requires a deep understanding of each configuration parameter and its impact on the system. The settings provided in /etc/my.cnf are a solid starting point but should be adjusted based on specific workload requirements and server resources. Regular monitoring and adjustments are essential for maintaining optimal performance..