Consultas SQL para saber key Buffer en MyISAM y Buffer Pol en InnoDB

[el-brujo]

Tunning y optimización del fichero

Código [Seleccionar] Expandir

/etc/my.cnf

¿Cómo puedo saber el  valor exacto y real que que ocupan mis tablas ya sean en MyISAM o InnoDB?

Para saber si usas tablas con el motor InnoDB y cuáles son:

Código (sql) [Seleccionar] Expandir

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';

Consulta para convertir una tabla a InnoDB

Código (sql) [Seleccionar] Expandir

ALTER TABLE tblname
ENGINE=InnoDB;

Consulta para convertir todas tus tablas InnoDB a MyISAM

Código (sql) [Seleccionar] Expandir

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=MyISAM;')
FROM information_schema.tables
WHERE engine = 'InnoDB';

Consulta para convertir todas tus tablas MyISAM a InnoDB

Código (sql) [Seleccionar] Expandir

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM';


MyISAM key cache


Variable:
key_buffer_size =

Código (sql) [Seleccionar] Expandir

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;


Ejemplo Resultado:
recommended_key_buffer_size
463M

    (SELECT 0 PowerOf1024) genera resultado en Bytes
    (SELECT 1 PowerOf1024) genera resultado en KB
    (SELECT 2 PowerOf1024) genera resultado en MB
    (SELECT 3 PowerOf1024) genera resultado en GB

Comando shell script:

asumiendo que
datadir=/var/lib/mysql

Código (bash) [Seleccionar] Expandir

find /var/lib/mysql -name '*.MYI'|xargs du -shc

InnoDB Buffer Pol


innodb buffer pool

Variable
innodb_buffer_pool_size =

Código (sql) [Seleccionar] Expandir


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;




En KB:

Código (sql) [Seleccionar] Expandir

SELECT SUM(data_length+index_length)/power(1024,1) IBPSize_KB
FROM information_schema.tables WHERE engine='InnoDB';

En MB:

Código (sql) [Seleccionar] Expandir

SELECT SUM(data_length+index_length)/power(1024,2) IBPSize_MB
FROM information_schema.tables WHERE engine='InnoDB';

En GB:

Código (sql) [Seleccionar] Expandir

SELECT SUM(data_length+index_length)/power(1024,3) IBPSize_GB
FROM information_schema.tables WHERE engine='InnoDB';


Más información y ejemplos:
http://wiki.elhacker.net/bases-de-datos/mysql/optimizacion