Salin dan Bagikan
MariaDB Monitoring Performance
Cara Monitoring MariaDB dengan Performance Schema: Query Analysis dan Diagnostics - Panduan lengkap menggunakan Performance Schema di MariaDB untuk query analysis, diagnostics, dan …

Cara Monitoring MariaDB dengan Performance Schema: Query Analysis dan Diagnostics

Cara Monitoring MariaDB dengan Performance Schema: Query Analysis dan Diagnostics

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="admin@example.com"
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 "
    SELECT DIGEST_TEXT, AVG_TIMER_WAIT/1000000000 as avg_ms 
    FROM performance_schema.events_statements_summary_by_digest 
    WHERE AVG_TIMER_WAIT > ${THRESHOLD_SLOW_MS}000000000
    ORDER BY AVG_TIMER_WAIT DESC
    LIMIT 5;
    " >> $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

  1. Always enable Performance Schema di production: Overhead minimal (< 5%)
  2. Configure specific consumers: Jangan enable semua jika tidak perlu
  3. Regular cleanup: Truncate summary tables monthly
  4. Create monitoring views: Simplify regular analysis
  5. Set alerting thresholds: Automated detection of issues
  6. Document query patterns: Use digests untuk track changes
  7. 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.

Artikel Terkait

Link Postingan : https://www.tirinfo.com/monitoring-performa-mariadb-performance-schema/

Hendra WIjaya
Tirinfo
6 minutes.
3 February 2026