Configuring my.cnf, Server Variables & Slow Query Log0%

Project 1: Fintech High-Performance Transaction Engine

Configuring my.cnf, Server Variables & Slow Query Log

Advanced14 min read•Updated: 2026-09-12

Configuring my.cnf, Server Variables & Slow Query Log

Default MySQL configurations are intentionally conservative to allow the server to boot on low-resource virtual machines. Deploying production databases with default settings leaves hardware capabilities heavily under-utilized.

Database administrators configure production parameters via the configuration file (my.cnf on Linux / my.ini on Windows) and dynamic server variables.

Configuration File Locations

Linux / Unix: /etc/mysql/my.cnf, /etc/my.cnf
Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Settings placed under the [mysqld] section apply directly to the MySQL server daemon.

Enterprise Production my.cnf Template

1[mysqld]
2# --- Network & Connections ---
3port                            = 3306
4bind-address                    = 0.0.0.0
5max_connections                 = 500
6max_connect_errors              = 10000
7wait_timeout                    = 600
8interactive_timeout             = 600
9 
10# --- Character Sets ---
11character-set-server            = utf8mb4
12collation-server                = utf8mb4_0900_ai_ci
13 
14# --- InnoDB Memory & Buffer Pool ---
15# (Allocate 70-80% of total RAM on a dedicated DB server)
16innodb_buffer_pool_size         = 12G
17innodb_buffer_pool_instances     = 8
18innodb_log_buffer_size          = 64M
19 
20# --- InnoDB Disk I/O & Redo Log ---
21innodb_log_file_size            = 2G
22innodb_flush_log_at_trx_commit  = 1
23innodb_flush_method             = O_DIRECT
24innodb_io_capacity              = 2000
25innodb_io_capacity_max          = 4000
26 
27# --- Slow Query Logging ---
28slow_query_log                  = 1
29slow_query_log_file             = /var/log/mysql/mysql-slow.log
30long_query_time                 = 1.0
31log_queries_not_using_indexes   = 1

Dynamic vs Static Variables: SET PERSIST (MySQL 8.0)

In older MySQL versions:

Running SET GLOBAL variable = value; modified memory, but changes were lost upon server restart unless manually copied into my.cnf.

MySQL 8.0 introduces SET PERSIST:

1-- Modifies running memory AND writes changes permanently to /var/lib/mysql/mysqld-auto.cnf!
2SET PERSIST max_connections = 600;
3 
4-- Persist variable without modifying currently running server:
5SET PERSIST_ONLY innodb_buffer_pool_size = 16G;

Critical Metric Inspection Queries

1-- Inspect buffer pool read efficiency (Should be > 99%!)
2SELECT 
3    ROUND((1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)) * 100, 2) AS buffer_pool_hit_rate
4FROM 
5    (SELECT VARIABLE_VALUE AS innodb_buffer_pool_reads FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') r,
6    (SELECT VARIABLE_VALUE AS innodb_buffer_pool_read_requests FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') rr;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In which section of my.cnf should server configuration parameters be placed?

Question 2

What is the major innovation of MySQL 8.0's SET PERSIST statement?

Question 3

What does innodb_flush_method = O_DIRECT accomplish on Linux hosts?

Question 4

What does a Buffer Pool Hit Rate of 99.5% indicate?

Question 5

What is the danger of setting max_connections to an excessively high number (e.g., 50,000) on a low-RAM server?

Next Lesson

Project 1: Fintech High-Performance Transaction Engine

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Database Maintenance: CHECK, ANALYZE & OPTIMIZE TABLE	Project 1: Fintech High-Performance Transaction Engine

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Project 1: Fintech High-Performance Transaction Engine

Configuring my.cnf, Server Variables & Slow Query Log

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Configuring my.cnf, Server Variables & Slow Query Log

Database administrators configure production parameters via the configuration file (my.cnf on Linux / my.ini on Windows) and dynamic server variables.

Configuration File Locations

Linux / Unix: /etc/mysql/my.cnf, /etc/my.cnf
Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Settings placed under the [mysqld] section apply directly to the MySQL server daemon.

Enterprise Production my.cnf Template

1[mysqld]
2# --- Network & Connections ---
3port                            = 3306
4bind-address                    = 0.0.0.0
5max_connections                 = 500
6max_connect_errors              = 10000
7wait_timeout                    = 600
8interactive_timeout             = 600
9 
10# --- Character Sets ---
11character-set-server            = utf8mb4
12collation-server                = utf8mb4_0900_ai_ci
13 
14# --- InnoDB Memory & Buffer Pool ---
15# (Allocate 70-80% of total RAM on a dedicated DB server)
16innodb_buffer_pool_size         = 12G
17innodb_buffer_pool_instances     = 8
18innodb_log_buffer_size          = 64M
19 
20# --- InnoDB Disk I/O & Redo Log ---
21innodb_log_file_size            = 2G
22innodb_flush_log_at_trx_commit  = 1
23innodb_flush_method             = O_DIRECT
24innodb_io_capacity              = 2000
25innodb_io_capacity_max          = 4000
26 
27# --- Slow Query Logging ---
28slow_query_log                  = 1
29slow_query_log_file             = /var/log/mysql/mysql-slow.log
30long_query_time                 = 1.0
31log_queries_not_using_indexes   = 1

Dynamic vs Static Variables: SET PERSIST (MySQL 8.0)

In older MySQL versions:

Running SET GLOBAL variable = value; modified memory, but changes were lost upon server restart unless manually copied into my.cnf.

MySQL 8.0 introduces SET PERSIST:

1-- Modifies running memory AND writes changes permanently to /var/lib/mysql/mysqld-auto.cnf!
2SET PERSIST max_connections = 600;
3 
4-- Persist variable without modifying currently running server:
5SET PERSIST_ONLY innodb_buffer_pool_size = 16G;

Critical Metric Inspection Queries

1-- Inspect buffer pool read efficiency (Should be > 99%!)
2SELECT 
3    ROUND((1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)) * 100, 2) AS buffer_pool_hit_rate
4FROM 
5    (SELECT VARIABLE_VALUE AS innodb_buffer_pool_reads FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') r,
6    (SELECT VARIABLE_VALUE AS innodb_buffer_pool_read_requests FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') rr;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In which section of my.cnf should server configuration parameters be placed?

Question 2

What is the major innovation of MySQL 8.0's SET PERSIST statement?

Question 3

What does innodb_flush_method = O_DIRECT accomplish on Linux hosts?

Question 4

What does a Buffer Pool Hit Rate of 99.5% indicate?

Question 5

What is the danger of setting max_connections to an excessively high number (e.g., 50,000) on a low-RAM server?

Next Lesson

Project 1: Fintech High-Performance Transaction Engine

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Database Maintenance: CHECK, ANALYZE & OPTIMIZE TABLE	Project 1: Fintech High-Performance Transaction Engine

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next