Cara Monitoring MariaDB dengan Performance Schema: Query Analysis dan Diagnostics

· Februari 3, 2026 · 10 menit baca

Performance Schema adalah tool diagnostic built-in yang powerful di MariaDB. Setelah menggunakannya untuk troubleshoot performance issues di production, saya menyaksikan betapa valuable-nya data yang disediakan. Artikel ini akan membantu Anda setup dan menggunakan Performance Schema secara efektif.

Apa itu Performance Schema?

Performance Schema adalah:
– Storage engine yang menyimpan metadata tentang server execution
– Low overhead monitoring (production-safe)
– Detailed instrumentation untuk events, waits, stages, statements
– No persistent storage (in-memory only)

Setup Performance Schema

1. Enable Performance Schema

# Edit my.cnf
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
performance_schema = ON

-- Verifikasi
SHOW VARIABLES LIKE 'performance_schema';
-- Value seharusnya: ON

2. Configure Consumers

Consumers adalah tujuan untuk data instrumentasi:

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

-- Atau enable spesifik saja UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME IN ( 'events_waits_current', 'events_waits_history', 'events_waits_history_long', 'events_stages_current', 'events_stages_history', 'events_statements_current', 'events_statements_history', 'events_statements_history_long', 'events_transactions_current', 'events_transactions_history', 'statements_digest' );

3. Configure Instruments

Instruments adalah sumber data:

-- Enable semua instruments UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'; -- Enable spesifik (misalnya InnoDB) UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%innodb%';

-- Enable mutex instruments UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%mutex%';

4. Configure Actors (User/Host Monitoring)

-- Monitor semua users
INSERT INTO performance_schema.setup_actors 
(HOST, USER, ROLE, ENABLED, HISTORY) 
VALUES ('%', '%', '%', 'YES', 'YES');
-- Atau spesifik user saja
INSERT INTO performance_schema.setup_actors 
VALUES ('localhost', 'app_user', '%', 'YES', 'YES');

Top Queries Analysis

1. 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,
    MAX_TIMER_WAIT/1000000000 as max_latency_ms,
    SUM_LOCK_TIME/1000000000 as total_lock_time_ms,
    SUM_ROWS_SENT as total_rows_sent,
    SUM_ROWS_EXAMINED as total_rows_examined,
    SUM_ROWS_AFFECTED as total_rows_affected,
    SUM_CREATED_TMP_TABLES as tmp_tables,
    SUM_CREATED_TMP_DISK_TABLES as tmp_disk_tables,
    SUM_NO_INDEX_USED as no_index_used_count,
    SUM_NO_GOOD_INDEX_USED as no_good_index_used_count,
    FIRST_SEEN,
    LAST_SEEN,
    DIGEST as query_digest
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

2. Queries with No Index Usage

SELECT 
    DIGEST_TEXT,
    SCHEMA_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000 as total_latency_ms,
    SUM_ROWS_EXAMINED,
    SUM_NO_INDEX_USED,
    SUM_NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
WHERE (SUM_NO_INDEX_USED > 0 OR SUM_NO_GOOD_INDEX_USED > 0)
  AND DIGEST_TEXT NOT LIKE '%SHOW%'
  AND DIGEST_TEXT NOT LIKE '%INFORMATION_SCHEMA%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

3. Frequent Queries (High Exec Count)

SELECT 
    DIGEST_TEXT,
    SCHEMA_NAME,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 as avg_latency_ms,
    SUM_TIMER_WAIT/1000000000 as total_latency_ms,
    (SUM_TIMER_WAIT/COUNT_STAR)/1000000000 as avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE COUNT_STAR > 1000
ORDER BY COUNT_STAR DESC
LIMIT 15;

Table I/O Analysis

1. Tables with Highest I/O

SELECT 
    OBJECT_SCHEMA as db,
    OBJECT_NAME as table_name,
    COUNT_READ as read_ops,
    SUM_TIMER_WAIT/1000000000 as total_wait_ms,
    COUNT_WRITE as write_ops,
    SUM_TIMER_FETCH/1000000000 as fetch_wait_ms,
    SUM_TIMER_INSERT/1000000000 as insert_wait_ms,
    SUM_TIMER_UPDATE/1000000000 as update_wait_ms,
    SUM_TIMER_DELETE/1000000000 as delete_wait_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

2. Index Usage Statistics

SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH as index_fetches,
    COUNT_INSERT as index_inserts,
    COUNT_UPDATE as index_updates,
    COUNT_DELETE as index_deletes,
    SUM_TIMER_WAIT/1000000000 as total_latency_ms
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

3. Unused Indexes Detection

SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND COUNT_FETCH = 0
  AND OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY OBJECT_SCHEMA, OBJECT_NAME;

Connection and Thread Analysis

1. Current Thread Activity

SELECT 
    THREAD_ID,
    PROCESSLIST_ID,
    PROCESSLIST_USER,
    PROCESSLIST_HOST,
    PROCESSLIST_DB,
    PROCESSLIST_COMMAND,
    PROCESSLIST_TIME,
    PROCESSLIST_STATE,
    PROCESSLIST_INFO,
    THREAD_OS_ID,
    NAME as thread_name,
    TYPE as thread_type
FROM performance_schema.threads
WHERE TYPE = 'FOREGROUND'
ORDER BY PROCESSLIST_TIME DESC
LIMIT 20;

2. Connection Statistics 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,
    SUM(CASE WHEN PROCESSLIST_COMMAND = 'Query' THEN 1 ELSE 0 END) as query_count
FROM performance_schema.threads
WHERE TYPE = 'FOREGROUND'
GROUP BY PROCESSLIST_HOST
ORDER BY connection_count DESC;

3. Memory Usage by Thread

SELECT 
    THREAD_ID,
    PROCESSLIST_USER,
    PROCESSLIST_HOST,
    SUM(CURRENT_COUNT_USED) as memory_ops,
    SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 as memory_mb
FROM performance_schema.memory_summary_by_thread_by_event_name
GROUP BY THREAD_ID
ORDER BY memory_mb DESC
LIMIT 10;

Wait Events Analysis

1. Top Wait Events

SELECT 
    EVENT_NAME,
    COUNT_STAR as event_count,
    SUM_TIMER_WAIT/1000000000000 as total_latency_sec,
    AVG_TIMER_WAIT/1000000000 as avg_latency_ms,
    MAX_TIMER_WAIT/1000000000 as max_latency_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
  AND EVENT_NAME NOT LIKE '%idle%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

2. File I/O Waits

SELECT 
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000 as total_latency_ms,
    SUM_NUMBER_OF_BYTES_READ/1024/1024 as mb_read,
    SUM_NUMBER_OF_BYTES_WRITE/1024/1024 as mb_written
FROM performance_schema.file_summary_by_event_name
WHERE EVENT_NAME LIKE '%innodb%'
   OR EVENT_NAME LIKE '%myisam%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

3. Table Lock Waits

SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ_NORMAL,
    COUNT_READ_WITH_SHARED_LOCKS,
    COUNT_READ_HIGH_PRIORITY,
    COUNT_READ_NO_INSERT,
    COUNT_WRITE_NORMAL,
    COUNT_WRITE_ALLOW_WRITE,
    COUNT_WRITE_LOW_PRIORITY,
    SUM_TIMER_WAIT/1000000000 as total_wait_ms
FROM performance_schema.table_lock_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 15;

File I/O Analysis

1. I/O by File Type

SELECT 
    FILE_NAME,
    EVENT_NAME,
    COUNT_STAR as io_count,
    SUM_TIMER_WAIT/1000000000 as total_latency_ms,
    SUM_NUMBER_OF_BYTES_READ/1024/1024 as mb_read,
    SUM_NUMBER_OF_BYTES_WRITE/1024/1024 as mb_written,
    (SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE)/1024/1024 as total_mb
FROM performance_schema.file_summary_by_instance
WHERE FILE_NAME LIKE '%.ibd%'
   OR FILE_NAME LIKE '%.MYD%'
   OR FILE_NAME LIKE '%.MYI%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

2. Temporary Tables on Disk

SELECT 
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000 as total_latency_ms,
    SUM_NUMBER_OF_BYTES_WRITE/1024/1024 as mb_written
FROM performance_schema.file_summary_by_event_name
WHERE EVENT_NAME LIKE '%temp%'
ORDER BY SUM_TIMER_WAIT DESC;

Replication Monitoring

1. Master Replication Status

-- Status variables untuk replication
SHOW STATUS LIKE '%slave%';
SHOW STATUS LIKE '%binlog%';
-- Replication connection status
SELECT * FROM performance_schema.replication_connection_status\G

2. Replication Lag Analysis

-- Applier status
SELECT 
    WORKER_ID,
    LAST_SEEN_TRANSACTION,
    LAST_ERROR_NUMBER,
    LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status_by_worker;

Custom Monitoring Views

1. Create Helpful Views

-- View untuk top queries
CREATE VIEW ps_top_queries AS
SELECT 
    DIGEST_TEXT,
    SCHEMA_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000 as total_latency_ms,
    AVG_TIMER_WAIT/1000000000 as avg_latency_ms,
    SUM_ROWS_SENT,
    SUM_ROWS_EXAMINED,
    (SUM_ROWS_EXAMINED/SUM_ROWS_SENT) as rows_examined_per_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 50;
-- View untuk table I/O
CREATE VIEW ps_table_io AS
SELECT 
OBJECT_SCHEMA,
OBJECT_NAME,
COUNT_READ,
COUNT_WRITE,
SUM_TIMER_WAIT/1000000000 as total_latency_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY SUM_TIMER_WAIT DESC;

2. Query untuk Regular Monitoring

-- Check slow queries in last hour
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 as avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE LAST_SEEN > DATE_SUB(NOW(), INTERVAL 1 HOUR)
  AND AVG_TIMER_WAIT > 100000000000  -- > 100ms
ORDER BY AVG_TIMER_WAIT DESC;

Automated Monitoring Script

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

#!/bin/bash
Performance Schema monitoring script
THRESHOLD_SLOW_MS=100
ALERT_EMAIL="[email protected]"
LOG_FILE="/var/log/ps_monitor.log"
Function to log
log_msg() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> $LOG_FILE
}
Check slow queries
SLOW_QUERIES=$(mysql -e "
SELECT COUNT(*) 
FROM performance_schema.events_statements_summary_by_digest 
WHERE AVG_TIMER_WAIT > ${THRESHOLD_SLOW_MS}000000000;
" | tail -1)
if [ $SLOW_QUERIES -gt 0 ]; then
log_msg "ALERT: $SLOW_QUERIES slow queries detected (> ${THRESHOLD_SLOW_MS}ms)"
# Get details
mysql -e &quot;
SELECT DIGEST_TEXT, AVG_TIMER_WAIT/1000000000 as avg_ms 
FROM performance_schema.events_statements_summary_by_digest 
WHERE AVG_TIMER_WAIT &gt; ${THRESHOLD_SLOW_MS}000000000
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 5;
&quot; &gt;&gt; $LOG_FILE
 fi
 Check high I/O tables
 HIGH_IO=$(mysql -e "
 SELECT COUNT(*) FROM (
 SELECT 1
 FROM performance_schema.table_io_waits_summary_by_table
 WHERE SUM_TIMER_WAIT > 10000000000000000
 LIMIT 1
 ) t;
 " | tail -1)
 if [ $HIGH_IO -gt 0 ]; then
 log_msg "WARNING: High I/O tables detected"
 fi
 echo "Performance Schema monitoring completed"
 
 Reset dan Cleanup
 -- Reset semua statistics
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name;
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;
TRUNCATE TABLE performance_schema.file_summary_by_event_name;
-- Reset specific table
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
 
 Best Practices
  Always enable Performance Schema di production: Overhead minimal (< 5%)
 Configure specific consumers: Jangan enable semua jika tidak perlu
 Regular cleanup: Truncate summary tables monthly
 Create monitoring views: Simplify regular analysis
 Set alerting thresholds: Automated detection of issues
 Document query patterns: Use digests untuk track changes
 Monitor trends: Don’t just look at current snapshot
 
 Kesimpulan
 Performance Schema menyediakan visibilitas yang belum pernah ada sebelumnya ke dalam operasi database:
 – Query-level performance metrics
 – Table dan index usage patterns
 – Wait events dan bottlenecks
 – File I/O analysis
 – Memory dan thread monitoring
 Dengan data ini, Anda dapat optimize dengan presisi yang sebelumnya tidak mungkin.

Ditulis oleh

Hendra Wijaya

Hanya hamba Allah Ta'ala yang berusaha berbuat baik..

Artikel Sebelumnya Keutamaan Puasa Senin Kamis dan Cara Mengerjakannya yang Benar Artikel Selanjutnya Cara Qadha Puasa Ramadhan yang Benar: Niat, Tata Cara, dan Waktunya