MySQL Configuration Guide

Overview

This document provides a comprehensive explanation of the MySQL configuration settings used in the provided configuration file. The settings are optimized for a high-performance MySQL server with a focus on balancing memory usage, CPU utilization, and disk I/O efficiency. The comments in the file also include guidelines for adjusting parameters based on server hardware and workload characteristics.

General Settings

innodb_buffer_pool_size

• Description: The size of the InnoDB buffer pool, which caches data and indexes from InnoDB tables.
• Calculation: Set to 60-70% of the available RAM. In this case, it's set to 26G based on 43GB of total RAM.

innodb_buffer_pool_instances

• Description: The number of memory instances for the InnoDB buffer pool.
• Calculation: Set to CPU cores divided by 2. Here, it's set to 24 for a 48-core CPU.

innodb_log_file_size

• Description: The size of each InnoDB log file. Larger values reduce disk I/O but increase recovery time.
• Calculation: Using (RAM * 0.5) / (Number of Log Files), the value is set to 8G. Ensure that the value is a power of 2 for optimal performance.

innodb_log_buffer_size

• Description: The size of the buffer used to store logs before writing them to disk.
• Calculation: Set to the minimum of 64MB or 1% of total RAM. In this case, it is set to 64MB.

innodb_flush_log_at_trx_commit

• Description: Controls the balance between performance and data durability.
• Value: Set to 1 for maximum data durability.

Performance Tuning

max_connections

• Description: The maximum number of connections MySQL allows at the same time.
• Calculation: For web applications, set to ((CPU_CORES * 2) + (total RAM_GB * 2)) * 2. In this case, it's set to 576.

thread_cache_size

• Description: Caches client threads to reduce the overhead of thread creation.
• Calculation: Set to 10% of max_connections, resulting in a value of 58.

table_open_cache

• Description: The number of tables MySQL keeps open.
• Calculation: Set to max_connections * 5, which results in 2880.

tmp_table_size and max_heap_table_size

• Description: Control the maximum size of temporary tables in memory.
• Calculation: tmp_table_size is set to the nearest power of 2 greater than 102MB, which is 128MB. max_heap_table_size is set to 8MB based on 10% of available RAM divided by the number of connections.

Cache Settings

query_cache_type and query_cache_size

• Description: Query caching is disabled to avoid performance bottlenecks in modern multi-core systems.
• Value: Both set to 0.

InnoDB Settings

innodb_flush_method

• Description: Controls how InnoDB writes data to disk.
• Value: Set to O_DIRECT for HDD or RAID setups to bypass the OS cache.

innodb_file_per_table

• Description: Stores each table in its own file, improving I/O performance.
• Value: Set to ON.

innodb_read_io_threads and innodb_write_io_threads

• Description: Controls the number of I/O threads for read and write operations.
• Calculation: Based on CPU cores, read threads are set to 26, and write threads are set to 22.

innodb_io_capacity and innodb_io_capacity_max

• Description: Limits the number of I/O operations per second.
• Calculation: Based on the disk's IOPS, set to 4500 for standard operations and 9000 for peak I/O.

Logging & Monitoring

slow_query_log

• Description: Enables logging of slow queries for performance analysis.
• Value: Enabled with a threshold of 1 second. Queries not using indexes are also logged.

log_error

• Description: Specifies the location of the error log.
• Value: Set to /var/log/mysql/error.log.

log_warnings

• Description: Increases the verbosity of warning logs.
• Value: Set to 2 for more detailed logs.

Timeout Settings

net_read_timeout and net_write_timeout

• Description: Control the timeouts for reading from and writing to the network.
• Calculation: Set to 25 seconds, considering execution time, network latency, and a safety margin.

Character Set

character-set-server and collation-server

• Description: Set the default character set and collation for the server.
• Value: Set to utf8mb4 and utf8mb4_unicode_ci for full Unicode support.

Connection Settings

max_connect_errors

• Description: Limits the number of failed connection attempts before blocking a client.
• Calculation: Set to 20% of max_connections, resulting in 115.

Buffer Settings

key_buffer_size

• Description: Buffer for MyISAM index blocks.
• Value: Set to 1MB since MyISAM is not used.

sort_buffer_size

• Description: Buffer used for sorting operations like ORDER BY and GROUP BY.
• Calculation: Set to 8MB.

read_buffer_size and read_rnd_buffer_size

• Description: Buffers used for full table scans and random reads.
• Value: Set to 16MB for both.

join_buffer_size

• Description: Buffer used for joins without indexes.
• Calculation: Set to 128MB based on the number of join tables and buffer sizes.

Other Settings

table_definition_cache

• Description: Caches table definitions to improve performance when opening tables.
• Value: Set to 2000, based on the number of tables and connections.

---

Final Notes

This configuration is optimized for a server with the following specifications:

• RAM: 43GB
• CPU: 48 cores
• Disk: SSD with high IOPS

The settings are designed for a balanced workload with a focus on high concurrency, efficient memory usage, and optimal disk I/O. Adjustments may be necessary based on your specific environment and workload. Always monitor performance metrics and adjust settings incrementally to avoid over-allocating resources.