Why MySQL 8’s Default Configuration Isn’t Enough
Installing MySQL 8 and leaving it to run with default settings is a “trap” that many junior sysadmins fall into. You should know that default parameters are designed to run even on the weakest hardware, like a Raspberry Pi. When deployed on a production server with 16GB or 64GB of RAM, MySQL will only use a tiny fraction of the available resources. Consequently, your website slows down or even freezes when traffic spikes.
To be honest, I once managed an e-commerce system running on a 32GB RAM server, yet the database was sluggish. Upon checking, I found the innodb_buffer_pool_size was still at the default 128MB. At that time, Disk I/O was constantly hitting 100% because MySQL had to read and write to the disk instead of RAM. After increasing this value to 20GB, the site’s response time immediately improved fivefold.
Quick Start: Sample Configuration for an 8GB RAM Server
If you need a standard template to get started, open the configuration file at /etc/mysql/my.cnf. Remember to back up the original file before overwriting it with the code below.
[mysqld]
# 1. Buffer Cache (Most Important)
innodb_buffer_pool_size = 5G
# 2. Connection Management
max_connections = 500
max_user_connections = 450
# 3. Disk Write Optimization
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
# 4. Temporary Tables and Sorting
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 2M
# 5. MySQL 8 Specifics
innodb_dedicated_server = OFFApply the changes by restarting the service:
sudo systemctl restart mysqlAnalyzing “Vital” Parameters
1. innodb_buffer_pool_size: The Soul of Performance
This is where MySQL stores data and indexes in RAM. Accessing RAM is thousands of times faster than accessing an SSD.
- Rule of thumb: The larger, the better, but don’t let the server run out of RAM (swap).
- Allocation: If the server is shared with Nginx/PHP, allocate 40-50% of RAM to MySQL. For a dedicated database server, this can go up to 75-80%.
MySQL 8 features innodb_dedicated_server = ON, which automatically calculates this parameter. However, I recommend enabling it only if the server isn’t running any other heavy applications.
2. Don’t Overuse max_connections
Seeing a “Too many connections” error doesn’t mean you should just bump it up to 2000 or 5000. This is a fatal mistake. Every connection consumes a certain amount of RAM for buffers.
Assuming each connection takes 2MB, 5000 connections would eat up 10GB of RAM just to… stay idle. Instead of increasing the count, optimize your code to close connections immediately after use. If the system is truly massive, use ProxySQL for more efficient connection pooling.
3. Balancing Safety and Speed with Logging
The innodb_flush_log_at_trx_commit parameter determines how data is written to disk:
- Value = 1: Absolute safety but the slowest, as it waits for disk confirmation for every transaction.
- Value = 2: The optimal choice for most systems. Data is written to the OS cache every second. If MySQL crashes, you lose nothing. You only lose about 1 second of data if the server loses power suddenly.
- Value = 0: Blazing speed but extremely risky. Use only for scratch data or non-critical logs.
A Note on Sort and Join Buffers
Many people have a habit of setting sort_buffer_size to several hundred MBs to speed up queries. Don’t do that! This buffer is allocated per connection. If you have 500 users and set it to 128MB, the server will crash due to OOM (Out Of Memory) during peak minutes. Keep it between 1MB – 4MB and focus on creating proper indexes for your SQL statements.
An Expert’s Tuning Process
Never change 10 parameters at once. Follow a sequence to manage risks:
- Modify a maximum of 2 parameters at a time.
- Monitor CPU, RAM, and the Slow Query Log for at least 24 hours.
- Use the mysqltuner.pl tool to get real-world suggestions based on running data.
You can run this script very easily:
wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.plIt will point out exactly which tables are missing indexes or which buffers are too low. Optimizing MySQL is a journey of continuous fine-tuning, not a fixed formula. Start with RAM and I/O, and you’ll see a significantly smoother system.
