Cara Cek DB yang banyak mengkonsumsi disk

🔍 Query utama (per database)

SELECT 
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

Output:

• database_name → nama DB

• size_mb → total size (data + index)

---

🔎 Lebih detail (per table dalam DB tertentu)

Kalau mau drill-down:

SELECT 
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'nama_database'
ORDER BY size_mb DESC;

👉 Ini penting untuk identifikasi:

• tabel paling “rakus”

• kandidat cleanup / archiving

---

⚠️ Realita di Azure MySQL (important)

Kalau kamu pakai Microsoft Azure → disk usage yang dihitung di alert bukan cuma data MySQL, tapi termasuk:

• binary log (binlog)

• undo log

• temp tables

• system overhead

Jadi sering kejadian:

DB size cuma 60–70% tapi disk sudah 90% 😅

---

🔥 Quick check tambahan (binlog)

Cek apakah binlog jadi biang masalah:

SHOW BINARY LOGS;

Kalau size besar:

• cek retention:

SHOW VARIABLES LIKE 'expire_logs_days';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

---

🚀 Next action (practical, no BS)

Kalau disk tinggal 10%, jangan cuma analisa—langsung mitigate:

1. Fast relief

• purge binlog (kalau aman):

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

2. Medium fix

• cleanup data lama (log table, audit, session, dll)

• archive ke storage lain (Blob / S3 equivalent)

3. Long-term

• aktifkan auto-rotation binlog

• setup monitoring:

  • Azure Metrics (storage percent)

  • alert threshold 70% (bukan 90%)