Jalankan query berikut:
SELECT
ROUND(SUM(data_length + index_length)/1024/1024/1024,2) AS innodb_size_gb
FROM information_schema.tables
WHERE engine='InnoDB';
jika hanya ke salah satu database saja
SELECT
ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS innodb_size_gb
FROM information_schema.tables
WHERE engine = 'InnoDB'
AND table_schema = 'mydb';
Cara mengecek apakah buffer pool sudah cukup
Lihat rasio pembacaan dari disk:
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
Kalau untuk MyISAM, query-nya sama, hanya engine-nya diganti menjadi MyISAM.
SELECT
ROUND(SUM(data_length + index_length)/1024/1024/1024,2) AS myisam_size_gb
FROM information_schema.tables
WHERE engine='MyISAM';
Jika diketahui ukuran index MyISAM
SELECT
ROUND(SUM(data_length)/1024/1024/1024,2) AS data_gb,
ROUND(SUM(index_length)/1024/1024/1024,2) AS index_gb
FROM information_schema.tables
WHERE engine='MyISAM';
Menghitung jumlah table dalam 1 database
SELECT COUNT(*) AS jumlah_table
FROM information_schema.tables
WHERE table_schema = 'mbs-online'
AND table_type = 'BASE TABLE';
Kalau ingin melihat jumlah berdasarkan engine
Untuk kasus server Anda yang memiliki banyak InnoDB + MyISAM, jalankan:
SELECT
ENGINE,
COUNT(*) AS jumlah_table
FROM information_schema.tables
WHERE table_schema = 'mbs-online'
AND table_type = 'BASE TABLE'
GROUP BY ENGINE
ORDER BY jumlah_table DESC;
Kalau ingin menghitung semua database sekaligus
SELECT
table_schema AS database_name,
COUNT(*) AS jumlah_table
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN (
'information_schema',
'mysql',
'performance_schema',
'sys'
)
GROUP BY table_schema
ORDER BY jumlah_table DESC;
