Cara mengetahui ukuran data InnoDB,MyISAM dan Jml table di MySQL

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;