tablas - optimizar mysql innodb
MySQL-La forma más rápida de verificar si los datos en la tabla InnoDB han cambiado (2)
Mi aplicación requiere mucha base de datos. Actualmente, ejecuto MySQL 5.5.19 y el uso de MyISAM, pero estoy en el proceso de migrar a InnoDB. El único problema que queda es el rendimiento de la suma de comprobación.
Mi aplicación hace aproximadamente 500-1000 sentencias "CHECKSUM TABLE" por segundo en las horas pico, porque la GUI de los clientes está sondeando constantemente la base de datos en busca de cambios (es un sistema de monitoreo, por lo que debe ser muy receptivo y rápido).
Con MyISAM, hay sumas de verificación en vivo que se calculan previamente en la modificación de la tabla y son MUY rápidas. Sin embargo, no existe tal cosa en InnoDB. Entonces, CHECKSUM TABLE es muy lento ...
Esperaba poder verificar la última actualización de la tabla. Desafortunadamente, esto tampoco está disponible en InnoDB. Estoy estancado ahora, porque las pruebas han demostrado que el rendimiento de la aplicación cae drásticamente ...
Simplemente hay demasiadas líneas de código que actualizan las tablas, por lo que no es posible implementar la lógica en la aplicación para registrar los cambios en la tabla ...
El ecosistema de la base de datos consta de un maestro na 3 esclavos, por lo que las verificaciones de archivos locales no son una opción. Pensé en un método para imitar una caché de suma de comprobación: una tabla de búsqueda con dos columnas, table_name, checksum y actualizar esa tabla con triggers cuando ocurren cambios en una tabla, pero tengo alrededor de 100 tablas para monitorear y esto significa 3 desencadenadores por tabla = 300 desencadenantes. Es difícil de mantener, y no estoy seguro de que esto no sea un cerdo de rendimiento nuevamente.
Entonces, ¿hay algún método FAST para detectar cambios en las tablas InnoDB?
¡Gracias!
La forma más simple es agregar una columna que admite nulos con el tipo TIMESTAMP, con el disparador: ON UPDATE CURRENT_TIMESTAMP .
Por lo tanto, las inserciones no cambiarán porque la columna acepta nulos, y puede seleccionar solo las columnas nuevas y modificadas diciendo:
SELECT * FROM `table` WHERE `mdate` > ''2011-12-21 12:31:22''
Cada vez que actualice una fila, esta columna cambiará automáticamente.
Aquí hay más información: http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
Para ver las filas borradas, simplemente crea un desencadenador que va a registrar cada eliminación en otra tabla:
DELIMITER $$
CREATE TRIGGER MyTable_Trigger
AFTER DELETE ON MyTable
FOR EACH ROW
BEGIN
INSERT INTO MyTable_Deleted VALUES(OLD.id, NOW());
END$$
Creo que encontré la solución. Durante algún tiempo estuve mirando Percona Server para reemplazar mis servidores MySQL, y ahora creo que hay una buena razón para esto.
El servidor Percona presenta muchas nuevas tablas INFORMATION_SCHEMA como INNODB_TABLE_STATS, que no está disponible en el servidor MySQL estándar. Cuando tu lo hagas:
SELECT rows, modified FROM information_schema.innodb_table_stats WHERE table_schema=''db'' AND table_name=''table''
Obtienes recuento de filas reales y un contador. La documentación oficial dice lo siguiente acerca de este campo:
Si el valor de la columna modificada excede "rows / 16" o 2000000000, el recálculo de estadísticas se realiza cuando innodb_stats_auto_update == 1. Podemos estimar la vejez de las estadísticas por este valor.
De modo que este contador se ajusta de vez en cuando, pero puede hacer una suma de comprobación del número de filas y el contador, y luego, con cada modificación de la tabla, obtiene una suma de comprobación única. P.ej:
SELECT MD5(CONCAT(rows,''_'',modified)) AS checksum FROM information_schema.innodb_table_stats WHERE table_schema=''db'' AND table_name=''table'';
Iba a actualizar mis servidores al servidor de Percona de todos modos, así que este límite no es un problema para mí. Administrar cientos de desencadenantes y agregar campos a las tablas es un gran problema para esta aplicación, ya que es muy tarde en el desarrollo.
Esta es la función de PHP que he creado para asegurarme de que las tablas puedan ser sumadas a cualquier motor y servidor que se utilice:
function checksum_table($input_tables){
if(!$input_tables) return false; // Sanity check
$tables = (is_array($input_tables)) ? $input_tables : array($input_tables); // Make $tables always an array
$where = "";
$checksum = "";
$found_tables = array();
$tables_indexed = array();
foreach($tables as $table_name){
$tables_indexed[$table_name] = true; // Indexed array for faster searching
if(strstr($table_name,".")){ // If we are passing db.table_name
$table_name_split = explode(".",$table_name);
$where .= "(table_schema=''".$table_name_split[0]."'' AND table_name=''".$table_name_split[1]."'') OR ";
}else{
$where .= "(table_schema=DATABASE() AND table_name=''".$table_name."'') OR ";
}
}
if($where != ""){ // Sanity check
$where = substr($where,0,-4); // Remove the last "OR"
$get_chksum = mysql_query("SELECT table_schema, table_name, rows, modified FROM information_schema.innodb_table_stats WHERE ".$where);
while($row = mysql_fetch_assoc($get_chksum)){
if($tables_indexed[$row[table_name]]){ // Not entirely foolproof, but saves some queries like "SELECT DATABASE()" to find out the current database
$found_tables[$row[table_name]] = true;
}elseif($tables_indexed[$row[table_schema].".".$row[table_name]]){
$found_tables[$row[table_schema].".".$row[table_name]] = true;
}
$checksum .= "_".$row[rows]."_".$row[modified]."_";
}
}
foreach($tables as $table_name){
if(!$found_tables[$table_name]){ // Table is not found in information_schema.innodb_table_stats (Probably not InnoDB table or not using Percona Server)
$get_chksum = mysql_query("CHECKSUM TABLE ".$table_name); // Checksuming the old-fashioned way
$chksum = mysql_fetch_assoc($get_chksum);
$checksum .= "_".$chksum[Checksum]."_";
}
}
$checksum = sprintf("%s",crc32($checksum)); // Using crc32 because it''s faster than md5(). Must be returned as string to prevent PHPs signed integer problems.
return $checksum;
}
Puedes usarlo así:
// checksum a signle table in the current db
$checksum = checksum_table("test_table");
// checksum a signle table in db other than the current
$checksum = checksum_table("other_db.test_table");
// checksum multiple tables at once. It''s faster when using Percona server, because all tables are checksummed via one select.
$checksum = checksum_table(array("test_table, "other_db.test_table"));
Espero que esto ahorre algunos problemas a otras personas que tienen el mismo problema.