This configuration file outlines an optimized setup for achieving high performance with a MySQL database server.
It is designed to enhance efficiency, maximize resource utilization, and provide robust support for concurrent connections and heavy workloads.
[client]
port = 3306 # Port for client connection
socket = /tmp/mysql.sock # Socket file for client connection
[mysqld]
port = 3306 # Port for the MySQL server
socket = /tmp/mysql.sock # Socket file for server connection
back_log = 50 # Maximum number of pending connections
max_connections = 100 # Maximum simultaneous client connections
wait_timeout = 256 # Time to wait before closing idle connections
max_connect_errors = 10 # Number of failed attempts before blocking a host
table_open_cache = 2048 # Cache for open tables
max_allowed_packet = 16M # Maximum size of packets sent/received
max_heap_table_size = 512M # Maximum size of in-memory tables
read_buffer_size = 64M # Memory for sequential table scans
read_rnd_buffer_size = 64M # Memory for random table scans
sort_buffer_size = 64M # Memory for sorting operations
join_buffer_size = 64M # Memory for join operations
thread_cache_size = 8 # Cache size for threads
thread_stack = 240K # Stack size for each thread
ft_min_word_len = 4 # Minimum length of words for full-text indexing
default-storage-engine = InnoDB # Default storage engine for tables
transaction_isolation = REPEATABLE-READ # Default transaction isolation level
tmp_table_size = 512M # Maximum size of temporary tables
slow_query_log # Enable slow query logging
long_query_time = 2 # Threshold for slow query logging (in seconds)
server-id = 1 # Unique server ID for replication
# INNODB options
innodb_buffer_pool_size = 4G # Memory allocated to the InnoDB buffer pool
innodb_buffer_pool_instances = 8 # Number of buffer pool instances
innodb_data_file_path = ibdata1:10M:autoextend # Path and size of InnoDB data files
innodb_write_io_threads = 8 # Number of I/O threads for writing
innodb_read_io_threads = 8 # Number of I/O threads for reading
innodb_thread_concurrency = 16 # Maximum number of concurrent threads
innodb_flush_log_at_trx_commit = 1 # Log flushing behavior at transaction commit
innodb_log_buffer_size = 1GB # Memory for log buffering
innodb_change_buffering = all # Enable change buffering for all operations
innodb_change_buffer_max_size = 25 # Max percentage of change buffer size
innodb_log_file_size = 125M # Size of each InnoDB log file
innodb_log_files_in_group = 2 # Number of InnoDB log files in group
innodb_max_dirty_pages_pct = 90 # Max percentage of dirty pages in buffer pool
innodb_lock_wait_timeout = 256 # Lock wait timeout in seconds
innodb_file_per_table # Store table data in individual files
default_time_zone = +00:00 # Default server time zone
# Changes for MySQL vs MariaDB
performance_schema = off # Disable performance schema
skip-log-bin # Disable binary logging
[mysqldump]
quick # Enable quick dumping
max_allowed_packet = 50M # Max packet size for mysqldump
[mysql]
no-auto-rehash # Disable automatic rehashing for speed
[mysqlhotcopy]
interactive-timeout # Enable interactive timeout
[mysqld_safe]
open-files-limit = 8192 # Max number of open files
The configuration begins with client and server socket and port definitions, ensuring smooth communication between the MySQL client and server. The back_log and max_connections parameters manage connection handling, enabling the server to accept up to 100 simultaneous connections, while the wait_timeout and max_connect_errors settings improve security and resource management.
Performance-related parameters such as table_open_cache, max_allowed_packet, and max_heap_table_size are set to optimize memory utilization for queries and data storage. Buffer settings like read_buffer_size, read_rnd_buffer_size, sort_buffer_size, and join_buffer_size are adjusted to ensure faster read operations, sorting, and joining of data, making the server suitable for intensive query processing.
Thread management settings (thread_cache_size and thread_stack) are fine-tuned to minimize overhead associated with thread creation, while InnoDB-specific configurations are emphasized for transactional workloads. The innodb_buffer_pool_size is set to 4GB to handle a significant amount of cached data, while dividing this pool into 8 instances ensures better concurrency.
The configuration supports efficient I/O operations with innodb_write_io_threads and innodb_read_io_threads, complemented by innodb_thread_concurrency to manage multiple threads efficiently. To safeguard data integrity, innodb_flush_log_at_trx_commit is set to 1, ensuring all transactions are logged immediately.
Logging and monitoring are also addressed. The slow_query_log is enabled to track queries taking longer than 2 seconds, aiding in the identification of performance bottlenecks. Binary logging is explicitly disabled using skip-log-bin, which reduces overhead in environments that do not require replication.
To support large databases and modern workloads, innodb_file_per_table is enabled, allowing each table to have its own tablespace file. Additionally, the configuration sets a universal timezone to UTC, ensuring consistency across distributed systems.
Finally, dump-specific settings (mysqldump) and safety features (mysqld_safe) are included for reliable backups and resource limits, rounding out the configuration for a well-rounded, high-performance MySQL environment. This setup is tailored for developers and database administrators seeking an efficient, scalable, and secure MySQL deployment.
Leave a Reply