Salin dan Bagikan
MySQL Monitoring Performance
Monitoring Performa Database MySQL dengan Slow Query Log dan Performance Schema - Panduan lengkap setup monitoring performa MySQL menggunakan slow query log, performance schema, dan …

Monitoring Performa Database MySQL dengan Slow Query Log dan Performance Schema

Monitoring Performa Database MySQL dengan Slow Query Log dan Performance Schema

Tanpa monitoring yang efektif, Anda seperti mengemudi dengan mata tertutup. Setelah mengalami beberapa performance crisis yang tidak terdeteksi hingga terlambat, saya memahami betapa pentingnya proactive monitoring. Artikel ini akan membahas setup monitoring lengkap untuk MySQL.

Arsitektur Monitoring MySQL

1. Three Layer Monitoring

  1. Infrastructure: CPU, RAM, Disk I/O, Network
  2. Database Metrics: Connections, Queries, Locks, Replication
  3. Application Metrics: Query latency, Error rates, Throughput

2. Key Metrics yang Wajib Dimonitor

-- Status variables critical
SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Threads_connected',
    'Threads_running', 
    'Queries',
    'Slow_queries',
    'Uptime',
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_reads',
    'Innodb_row_lock_waits',
    'Innodb_row_lock_time_avg'
);

Setup Slow Query Log

1. Enable Slow Query Log

# my.cnf configuration
[mysqld]
# Slow Query Log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
min_examined_row_limit = 100

2. Dynamic Enable/Disable

-- Enable tanpa restart
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Verifikasi
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query%';

3. Log Output Options

-- Log ke table (mudah query)
SET GLOBAL log_output = 'TABLE';

-- Atau ke file (better untuk high volume)
SET GLOBAL log_output = 'FILE';

-- Atau keduanya
SET GLOBAL log_output = 'FILE,TABLE';

4. Analisis Slow Query Log

-- Query slow log table
SELECT 
    start_time,
    user_host,
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    sql_text
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 20;

-- Aggregasi berdasarkan query pattern
SELECT 
    LEFT(sql_text, 100) as query_pattern,
    COUNT(*) as occurrences,
    AVG(query_time) as avg_time,
    MAX(query_time) as max_time,
    SUM(query_time) as total_time,
    AVG(rows_examined) as avg_rows_examined
FROM mysql.slow_log
WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY LEFT(sql_text, 100)
ORDER BY total_time DESC
LIMIT 20;

5. Percona Toolkit: pt-query-digest

# Install Percona Toolkit
sudo apt install percona-toolkit

# Analisis slow log
pt-query-digest /var/log/mysql/slow.log

# Simpan ke table untuk analisis lebih lanjut
pt-query-digest --review h=localhost,D=percona,t=query_review \
    --history h=localhost,D=percona,t=query_history \
    --no-report /var/log/mysql/slow.log

Performance Schema Setup

1. Enable Performance Schema

# my.cnf
[mysqld]
performance_schema = ON

-- Verifikasi
SHOW VARIABLES LIKE 'performance_schema';

2. Setup Consumers dan Instruments

-- Enable semua consumers
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES';

-- Enable specific consumers
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME IN (
    'events_waits_current',
    'events_waits_history',
    'events_waits_history_long',
    'events_statements_current',
    'events_statements_history',
    'events_statements_history_long',
    'thread_instrumentation'
);

-- Enable specific instruments
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES';

3. Query Performance Analysis

-- Top queries by total latency
SELECT 
    DIGEST_TEXT as query,
    SCHEMA_NAME as db,
    COUNT_STAR as exec_count,
    SUM_TIMER_WAIT/1000000000000 as total_latency_sec,
    AVG_TIMER_WAIT/1000000000 as avg_latency_ms,
    SUM_ROWS_SENT as total_rows_sent,
    SUM_ROWS_EXAMINED as total_rows_examined,
    SUM_CREATED_TMP_TABLES as tmp_tables,
    SUM_NO_INDEX_USED as no_index_used,
    FIRST_SEEN,
    LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

4. Table I/O Analysis

-- Tables dengan I/O tertinggi
SELECT 
    OBJECT_SCHEMA as db,
    OBJECT_NAME as table_name,
    COUNT_READ as read_ops,
    SUM_TIMER_WAIT/1000000000 as total_latency_ms,
    COUNT_WRITE as write_ops,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

5. Index Usage Analysis

-- Index efficiency
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE,
    SUM_TIMER_WAIT/1000000000 as total_latency_ms
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

6. Connection Analysis

-- Current connections by host
SELECT 
    PROCESSLIST_HOST,
    COUNT(*) as connection_count,
    SUM(CASE WHEN PROCESSLIST_COMMAND = 'Sleep' THEN 1 ELSE 0 END) as idle_count,
    SUM(CASE WHEN PROCESSLIST_COMMAND != 'Sleep' THEN 1 ELSE 0 END) as active_count
FROM performance_schema.threads
WHERE TYPE = 'FOREGROUND'
GROUP BY PROCESSLIST_HOST
ORDER BY connection_count DESC;

Custom Monitoring Scripts

1. Script untuk Metrics Collection

sudo nano /usr/local/bin/mysql_metrics.sh

#!/bin/bash

# Konfigurasi
DB_USER="monitoring"
DB_PASS="Monitor#Pass123"
LOG_FILE="/var/log/mysql_metrics.log"

# Collect metrics
METRICS=$(mysql -u $DB_USER -p$DB_PASS -e "
SELECT 
    'threads_connected', VARIABLE_VALUE 
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME = 'Threads_connected'
UNION ALL
SELECT 
    'threads_running', VARIABLE_VALUE 
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME = 'Threads_running'
UNION ALL
SELECT 
    'slow_queries', VARIABLE_VALUE 
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME = 'Slow_queries'
UNION ALL
SELECT 
    'uptime', VARIABLE_VALUE 
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME = 'Uptime'
UNION ALL
SELECT 
    'innodb_buffer_pool_reads', VARIABLE_VALUE 
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads';
")

# Log dengan timestamp
echo "[$(date '+%Y-%m-%d %H:%M:%S')]" >> $LOG_FILE
echo "$METRICS" >> $LOG_FILE
echo "---" >> $LOG_FILE

2. Alerting Script

sudo nano /usr/local/bin/mysql_alert.sh

#!/bin/bash

# Thresholds
MAX_CONNECTIONS=150
MAX_SLOW_QUERIES=10
ALERT_EMAIL="admin@example.com"

# Check current connections
CURRENT_CONN=$(mysql -u monitoring -p'Monitor#Pass123' \
    -e "SHOW STATUS LIKE 'Threads_connected';" | tail -1)

# Check slow queries in last hour
SLOW_COUNT=$(mysql -u monitoring -p'Monitor#Pass123' \
    -e "SELECT COUNT(*) FROM mysql.slow_log WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 HOUR);" | tail -1)

# Alert jika threshold terlampaui
if [ $CURRENT_CONN -gt $MAX_CONNECTIONS ]; then
    echo "ALERT: High connections: $CURRENT_CONN" | \
        mail -s "[WARNING] MySQL High Connections" $ALERT_EMAIL
fi

if [ $SLOW_COUNT -gt $MAX_SLOW_QUERIES ]; then
    echo "ALERT: $SLOW_COUNT slow queries in last hour" | \
        mail -s "[WARNING] MySQL Slow Queries Spike" $ALERT_EMAIL
fi

3. Setup Cron

sudo crontab -e

# Collect metrics setiap 5 menit
*/5 * * * * /usr/local/bin/mysql_metrics.sh

# Check alerts setiap 10 menit
*/10 * * * * /usr/local/bin/mysql_alert.sh

MySQL Enterprise Monitor (Opsional)

1. Install MySQL Enterprise Monitor

# Download dari Oracle
# Install agent di MySQL server
sudo ./mysqlmonitoragent.bin

# Konfigurasi dengan Service Manager

2. Query Analyzer

-- MySQL Enterprise Query Analyzer memberikan:
-- - Query execution plans
-- - Historical performance data
-- - Automated alerts
-- - Expert recommendations

Visualization dengan Grafana

1. Setup Data Source

-- Create user untuk monitoring
CREATE USER 'grafana'@'%' IDENTIFIED BY 'Grafana#Pass123';
GRANT SELECT ON performance_schema.* TO 'grafana'@'%';
GRANT SELECT ON sys.* TO 'grafana'@'%';

2. Key Dashboard Panels

-- Query untuk panel "Current Connections"
SELECT COUNT(*) FROM performance_schema.threads WHERE TYPE = 'FOREGROUND';

-- Query untuk panel "Slow Queries per Hour"
SELECT COUNT(*) FROM mysql.slow_log 
WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- Query untuk panel "Top Tables by I/O"
SELECT OBJECT_NAME, SUM_TIMER_WAIT 
FROM performance_schema.table_io_waits_summary_by_table 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

Troubleshooting dengan Monitoring Data

1. High CPU Usage

-- Identifikasi queries yang menyebabkan high CPU
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 as avg_latency_ms,
    SUM_CREATED_TMP_DISK_TABLES as disk_tmp_tables
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

2. Memory Pressure

-- Check buffer pool efficiency
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Buffer pool hit ratio harus > 95%
-- Jika rendah, naikkan innodb_buffer_pool_size

3. Lock Contention

-- Check lock waits
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ_NORMAL,
    COUNT_WRITE_NORMAL,
    SUM_TIMER_WAIT/1000000000 as total_wait_ms
FROM performance_schema.table_lock_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC;

Best Practices Monitoring

  1. Start dengan slow query log: Identifikasi queries lambat
  2. Setup Performance Schema: Untuk detailed analysis
  3. Baseline metrics: Catat metrics normal untuk perbandingan
  4. Automated alerts: Setup threshold-based alerting
  5. Regular review: Analisis trends mingguan/bulanan
  6. Actionable insights: Setiap alert harus punya action plan

Kesimpulan

Monitoring yang efektif adalah kombinasi dari:

  • Slow query log untuk query-level analysis
  • Performance schema untuk detailed metrics
  • Custom scripts untuk business-specific metrics
  • Visualization untuk trend analysis
  • Alerting untuk proactive response

Dengan setup ini, Anda akan mengetahui masalah sebelum user mengeluh.

Artikel Terkait

Link Postingan : https://www.tirinfo.com/cara-monitoring-performa-mysql/

Hendra WIjaya
Tirinfo
5 minutes.
3 February 2026