myisam ve innodb veritabanları için key_buffer_size ve innodb_buffer_pool_size hesaplayan query

İşinize yarayabilir.

Myisam için;

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;
mysql> SELECT CONCAT(ROUND(KBS/POWER(1024,
    -> IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
    -> SUBSTR(' KMG',IF(PowerOf1024<0,0,
    -> IF(PowerOf1024>3,0,PowerOf1024))+1,1))inn
    -> recommended_key_buffer_size FROM
    -> (SELECT LEAST(POWER(2,32),KBS1) KBS
    -> FROM (SELECT SUM(index_length) KBS1
    -> FROM information_schema.tables
    -> WHERE engine='MyISAM' AND
    -> table_schema NOT IN ('information_schema','mysql')) AA ) A,
    -> (SELECT 2 PowerOf1024) B;
+-----------------------------+
| recommended_key_buffer_size |
+-----------------------------+
| 2095M                       |
+-----------------------------+
1 row in set (43.45 sec)

InnoDB için;

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;
mysql> SELECT CONCAT(ROUND(KBS/POWER(1024,
    -> IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
    -> SUBSTR(' KMG',IF(PowerOf1024<0,0,
    -> IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
    -> FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
    -> WHERE engine='InnoDB') A,
    -> (SELECT 2 PowerOf1024) B;
+-------------------------------------+
| recommended_innodb_buffer_pool_size |
+-------------------------------------+
| 93089M                              |
+-------------------------------------+
1 row in set (35.77 sec)

Kaynak : http://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam/2194#2194

Posted in Linux, MySQL on November 16th, 2012 by Kürşad DARA | | 0 Comments