沙鷗工作室

關於innodb buffer pool的大小設定：

大小應為資料量的1.6倍 或是 實體記憶體的70%

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

 

SELECT table_schema "Database Name"      , SUM(data_length + index_length) / (1024 * 1024) "Database Size in MB" FROM information_schema.TABLES GROUP BY table_schema

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = ‘db_name’
    AND table_name = ‘table_name’;

Size of all tables, descending order:

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

 

取得該資料庫所有使用者

select * from mysql.user;