Master MySQL Performance: Essential my.cnf Tweaks with Docker Compose

Many newcomers to MySQL are overwhelmed by the dense list of options in my.cnf. This article shows how to use an AlmaLinux server with Docker Compose to create a clean testing environment and demystify the key parameters that most affect performance.

Why tune MySQL?

Out‑of‑the‑box settings are conservative; they keep the engine safe but often under‑utilize available hardware, leading to slow queries and apparent bottlenecks. Adjusting the configuration unlocks the full potential of your server.

Environment preparation: Docker Compose

Install Docker and Docker Compose if they are not already present.

Create a project directory and navigate into it: mkdir mysql-tuning && cd mysql-tuning Create a docker-compose.yml file (use any editor, e.g., vim or nano) with the following content:

# docker-compose.yml – MySQL tuning environment
version: '3.8'
services:
  mysql:
    image: mysql:8.0   # official MySQL 8.0 image
    container_name: mysql_tuning
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: itxianyu123   # use a strong password in production
      MYSQL_DATABASE: test_db
    ports:
      - "3306:3306"   # map container port 3306 to host
    volumes:
      - ./my.cnf:/etc/mysql/conf.d/my.cnf   # mount custom config
      - mysql_data:/var/lib/mysql   # persistent data
    networks:
      - mysql-net
volumes:
  mysql_data:
networks:
  mysql-net:

Create the initial my.cnf

# my.cnf – basic MySQL tuning configuration
[mysqld]
# Core performance parameters will be added here

Start the MySQL container

Run docker compose up -d in the mysql-tuning directory. When the container is up, you can begin tuning.

Core parameter breakdown

innodb_buffer_pool_size – Memory cache for InnoDB data and indexes. Set to 50‑75% of physical RAM (e.g., 4G‑6G on an 8G server). Example: innodb_buffer_pool_size = 4G max_connections – Maximum simultaneous client connections. Small apps can use 150‑300; larger workloads may need 500‑1000. Example: max_connections = 300 innodb_flush_log_at_trx_commit – Controls how transaction logs are flushed. 1 is safest but slow; 2 offers a good balance for most workloads. Example: innodb_flush_log_at_trx_commit = 2 sync_binlog – Determines how often the binary log is flushed to disk. 1 is safest; 0 is fastest but risks data loss. A common compromise is 100. Example:

sync_binlog = 100

Apply the configuration

[mysqld]
innodb_buffer_pool_size = 4G   # assume 8G RAM
max_connections = 300
innodb_flush_log_at_trx_commit = 2   # balanced safety & performance
sync_binlog = 100   # reasonable compromise

Restart the container to load the new settings:

docker compose restart mysql

Verify the changes

Connect with a client such as DataGrip (host: your AlmaLinux IP, port 3306, user root, password you set) and run:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';

Important tips & pitfalls

Change 1‑2 parameters at a time and monitor the impact before proceeding.

Use SHOW STATUS and SHOW ENGINE INNODB STATUS or a monitoring stack (Prometheus + Grafana) to observe load, slow queries, and errors.

Enable the slow‑query log ( slow_query_log = ON) and set an appropriate long_query_time (e.g., 0.5 s).

Regularly check the error log ( /var/log/mysql/error.log) for configuration issues.

Remember that innodb_buffer_pool_size is the biggest memory consumer, but other buffers (thread, sort, etc.) also count toward total RAM usage.

Parameters edited in my.cnf are global; session‑level changes can be made with SET statements for temporary testing.

Conclusion

Performance tuning is not mystical—focus on the four key settings ( innodb_buffer_pool_size, max_connections, innodb_flush_log_at_trx_commit, and sync_binlog), adjust them according to your hardware and workload, and continuously monitor. With Docker Compose you can iterate quickly and become the DBA who truly controls MySQL performance.