Server Configuration, Tuning, and Monitoring

MySQL behavior is controlled by system variables (configuration) and observed through status variables, the Performance Schema, the sys schema, information_schema, and log files.

Concept

MySQL behavior is controlled by system variables (configuration) and observed through status variables, the Performance Schema, the sys schema, information_schema, and log files. Tuning focuses on memory (buffer pool), I/O (redo log, flushing), connections, and temporary tables.

Why It Matters

Default settings are conservative (e.g., a 128 MB buffer pool). A few well-chosen settings and good monitoring prevent most production incidents.

Syntax

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern'];
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern'];
SET GLOBAL var = value;    SET PERSIST var = value;    SET PERSIST_ONLY var = value;   RESET PERSIST var;
SELECT * FROM performance_schema.variables_info WHERE variable_source <> 'COMPILED';   -- where each setting came from

Example

33.1 Key configuration variables Cache for data/index pages 50–75% of RAM on a dedicated server; or innodb_dedicated_server = ON to auto-size innodb_redo_log_capacity (8.0.30+) Total redo log size

Large enough for ~1 hour of writes at peak (e.g., 2–8 GB on busy servers) Durability of commits 1 for production

O_DIRECT on Linux to avoid double buffering innodb_io_capacity / _max Background flushing rate Match storage (SSD/NVMe: thousands)

Size for the app pools; use connection pooling Open table handles Increase for many tables

tmp_table_size / max_heap_table_size In-memory internal temp tables

Raise moderately if many temp tables spill to disk sort_buffer_size, join_buffer_size

Keep defaults; raise per query with SET_VAR hints if needed long_query_time, slow_query_log Slow query logging ≥ backup interval Idle connection timeout Align with pool settings

Memory estimate: buffer pool + (max_connections × per-connection buffers) + other caches must fit in RAM with headroom for the OS.

33.2 Status variables to watch Buffer pool hit ratio (should be > 99% for OLTP working sets):

33.3 Performance Schema and sys schema 33.4 Processes, killing queries, and connections

Startup/shutdown, crashes, warnings, deadlocks (with innodb_print_all_deadlocks) Slow query log Queries exceeding long_query_time General query log

Every statement (debug only; very heavy) All changes (replication/PITR) Replica copy of source binlog events 33.6 Monitoring stack

Built-in: Performance Schema, sys schema, MySQL Workbench performance reports, MySQL Enterprise Monitor (commercial).

Open source: Prometheus mysqld_exporter + Grafana dashboards, Percona Monitoring and Management (PMM), Zabbix templates.

Alert on: availability, replication lag/errors, connections near max_connections, Threads_running spikes, disk space, slow query rate, deadlock rate, long-running transactions, backup failures.

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected', 'Threads_running', 'Max_used_connections', 'Aborted_connects',
  'Questions', 'Slow_queries', 'Created_tmp_disk_tables', 'Innodb_row_lock_waits',
  'Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests', 'Uptime');

SELECT ROUND(100 * (1 - r.v / rr.v), 2) AS bp_hit_pct
FROM (SELECT VARIABLE_VALUE AS v FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') r,
     (SELECT VARIABLE_VALUE AS v FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') rr;

SELECT * FROM sys.host_summary\G
SELECT * FROM sys.user_summary\G
SELECT * FROM sys.schema_table_statistics WHERE table_schema = 'eh_company';
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;
SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10;
SELECT * FROM sys.session WHERE command <> 'Sleep'\G            -- what is running now
SELECT * FROM sys.innodb_lock_waits\G
SELECT * FROM sys.schema_unused_indexes;

SHOW FULL PROCESSLIST;
SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS query
FROM information_schema.processlist
WHERE command <> 'Sleep' ORDER BY time DESC;

KILL QUERY 1234;     -- stop the running statement, keep the connection
KILL 1234;           -- terminate the connection

Expected Result

(Values reflect the example configuration from Module 2.)

SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb, @@max_connections, @@long_query_time;

+----------------+-------------------+-------------------+
| buffer_pool_mb | @@max_connections | @@long_query_time |
+----------------+-------------------+-------------------+
|   2048.0000000 |               200 |          1.000000 |
+----------------+-------------------+-------------------+

Common Mistakes

Leaving the buffer pool at 128 MB on a large server.

Huge per-session buffers multiplied by hundreds of connections (out-of-memory).

Raising max_connections instead of fixing connection leaks or using pooling.

Enabling the general log in production.

Copying "tuning scripts" without understanding each setting.

Best Practices

Change one setting at a time and measure.

Use SET PERSIST or version-controlled option files.

Monitor continuously with dashboards and alerts.

Use connection pools in applications.

Interview Relevance

Medium-high: "Most important MySQL setting?" (buffer pool), "How do you find what is running now?", "What is the Performance Schema / sys schema?", "How do you kill a long query?".

Practice Questions

Compute the buffer pool hit ratio on your server.

List the 10 largest tables by data + index size.

Find the connection running the longest query and kill only the query.

Propose settings for a dedicated 16 GB server running a write-heavy OLTP workload.