Tunning y optimización del fichero
¿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:
SELECT table_schema, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';
Consulta para convertir una tabla a InnoDB
ALTER TABLE tblname
ENGINE=InnoDB;
Consulta para convertir todas tus tablas InnoDB a MyISAM
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
SELECT CONCAT('ALTER TABLE ',table_schema,'.',TABLE_NAME,' engine=InnoDB;')
FROM information_schema.TABLES
WHERE engine = 'MyISAM';
MyISAM key cacheVariable:
key_buffer_size =
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
find /var/lib/mysql -name '*.MYI'|xargs du -shc
InnoDB Buffer Polinnodb buffer pool
Variable
innodb_buffer_pool_size =
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:
SELECT SUM(data_length+index_length)/POWER(1024,1) IBPSize_KB
FROM information_schema.TABLES WHERE engine='InnoDB';
En MB:
SELECT SUM(data_length+index_length)/POWER(1024,2) IBPSize_MB
FROM information_schema.TABLES WHERE engine='InnoDB';
En GB:
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