15.11. Consejos de afinamiento del rendimiento de InnoDB

MySQL 5.0

15.11. Consejos de afinamiento del rendimiento de `InnoDB`

Si la utilidad top de Unix o el Administrador de Tareas de Windows muestra que el porcentaje de uso de CPU durante la carga de trabajo es inferior al 70%, probablemente se está trabajando directamente sobre el disco. Podría suceder que se estén produciendo excesivas confirmaciones de transacciones, o que el pool de buffer sea muy pequeño. Puede ser de ayuda incrementar el tamaño del buffer, pero no debe alcanzar ni superar el 80% del total de la memoria física del ordenador.
Incluir varias modificaciones en una sola transacción. InnoDB debe descargar su registro (log) al disco cada vez que se confirma una transacción, si dicha transacción realiza modificaciones en la base de datos. Dado que la velocidad de rotación de un disco es generalmente de 167 revoluciones por segundo, esto restringe el número de confirmaciones a la misma fracción de segundo si el disco no “engaña” al sistema operativo.
Si es aceptable la pérdida de alguna de las últimas transacciones confirmadas, se puede establecer en my.cnf el parámetro innodb_flush_log_at_trx_commit a un valor de 0. InnoDB intenta descargar el registro (log) una vez por segundo en cualquier caso, aunque la descarga no está garantizada.
Incrementar el tamaño de los ficheros de registro (log), incluso hasta equiparar el tamaño del pool de buffer. Cuando InnoDB ha colmado la capacidad de los ficheros de log, debe escribir los contenidos modificados desde el pool de buffer al disco en un punto de verificación. Los ficheros de log pequeños pueden causar escrituras en disco innecesarias. La desventaja de los ficheros de log grandes es que la recuperación demanda más tiempo.
También el buffer del log debe ser suficientemente grande (en el orden de los 8MB).
Emplear el tipo de columna VARCHAR en lugar de CHAR si se almacenarán cadenas de longitud variable o si la columna contendrá muchos valores NULL. Una columna CHAR(N) siempre utiliza N bytes para almacenar los datos, inclusive si la cadena es más corta o es NULL. Las tablas más pequeñas aprovechan mejor el espacio del pool de buffer y reducen las operaciones de E/S en disco.

Cuando se utiliza row_format=compact (el formato de registro predeterminado para InnoDB en MySQL 5.0) y un conjunto de caracteres de longitud variable como utf8 o sjis, CHAR(N) ocupará una cantidad variable de espacio, con un mínimo de N bytes.

En algunas versiones de GNU/Linux y Unix, descargar ficheros a disco con la función de Unix fsync() (la cual es utilizada en forma predeterminada por InnoDB) y otros métodos similares, es sorprendentemente lento. Si no se está satisfecho con el rendimiento de las operaciones de escritura de la base de datos, se puede intentar establecer el valor de innodb_flush_method en my.cnf a O_DSYNC, si bien O_DSYNC parece ser más lento en otros sistemas.

Durante el empleo del motor de almacenamiento InnoDB en arquitecturas Solaris 10 para x86_64 (AMD Opteron), es importante usar la opción forcedirectio al montar cualquier sistema de ficheros usado para almacenar los ficheros relacionados con InnoDB (el comportamiento predeterminado en Solaris 10/x86_64 es no utilizar esta opción al montar el sistema de ficheros). Si no se utiliza forcedirectio se producirá una seria degradación en la velocidad y rendimiento de InnoDB en esta plataforma.

Al importar datos dentro de InnoDB, hay que asegurarse de que MySQL no tiene habilitado el modo de ejecución automática (autocommit) porque provocaría una descarga del log a disco en cada inserción. Para desactivar la ejecución automática durante la operación de importación, hay que encerrarla entre sentencias SET AUTOCOMMIT y COMMIT:

SET AUTOCOMMIT=0; /* Sentencias de importación SQL ... */ COMMIT;

Si se utiliza la opción --opt con mysqldump, se obtienen ficheros de voclado que son rápidos de importar en una tabla InnoDB, incluso sin encerrarlos en las sentencias SET AUTOCOMMIT y COMMIT.

Tener cuidado con las cancelaciones de inserciones masivas: InnoDB emplea el buffer de inserciones para reducir la cantidad de operaciones de E/S en disco durante las inserciones, pero ese mecanismo no tiene efecto en la cancelación. Una cancelación efectuada directamente sobre el disco puede tomar 30 veces el tiempo que insumen las correspondientes inserciones. Matar el proceso del servidor de bases de datos no es de ayuda, porque la cancelación recomienza al volver a iniciar el servidor. La única forma de librarse de una cancelación fuera de control es incrementar el tamaño del pool de buffer para que la cancelación se haga sobre la CPU y se ejecute más rápidamente, o utilizar un procedimiento especial. Consulte Sección 15.8.1, “Forzar una recuperación”.

También hay que tener cuidado con las operaciones de gran tamaño realizadas directamente sobre el disco. Hay que emplear DROP TABLE y CREATE TABLE para obtener una tabla vacía, no DELETE FROMtbl_name.

Emplear la sintaxis de múltiples filas de INSERT para reducir la carga extra de la comunicación entre el cliente y el servidor si se necesita insertar muchos registros:

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

Esta sugerencia es válida para las inserciones en cualquier tipo de tabla, no solamente en InnoDB.

Si se tienen restricciones UNIQUE en claves secundarias, se puede acelerar la importación desactivando temporalmente, durante la importación, la verificación de tales restricciones:

SET UNIQUE_CHECKS=0;

En tablas grandes, esto ahorra una gran cantidad de operaciones de E/S en disco debido a que InnoDB puede emplear su buffer de inserción para escribir de una vez los registros de indice secundarios.

Si se tienen restricciones FOREIGN KEY en las tablas, se puede acelerar la importación desactivando la verificación de claves foráneas durante la misma:

SET FOREIGN_KEY_CHECKS=0;

Para tablas grandes, esto puede ahorrar gran cantidad de operaciones sobre el disco.

Si a menudo se realizan consultas sobre tablas que no se actualizan con frecuencia, utilizar el cache de consultas:

[mysqld] query_cache_type = ON query_cache_size = 10M

15.11.1. SHOW INNODB STATUS y los monitores InnoDB

InnoDB incluye los Monitores InnoDB que muestran información relativa al estado interno de InnoDB. Se puede emplear la sentencia SQL SHOW INNODB STATUS para obtener la salida del Monitor InnoDB estándar en el cliente SQL utilizado. Esta información es útil para ajustes de rendimiento. (Si se usa el cliente SQL interactivo mysql, la salida es más legible si se reemplaza el punto y coma que usualmente termina cada sentencia por \G.) Para más información sobre los modos de bloqueo de InnoDB consulte Sección 15.10.1, “Modos de bloqueo InnoDB”.

mysql> SHOW INNODB STATUS\G

Otra forma de emplear los Monitores InnoDB es permitirles escribir datos contínuamente en la salida estándar del servidor mysqld. En este caso, no se envía la salida a los clientes. Cuando se activa, los Monitores InnoDB imprimen datos aproximadamente cada 15 segundos. La salida del servidor normalmente se dirige a un fichero .err en el directorio de datos de MySQL. Estos datos son útiles para ajustes de rendimiento. En Windows, se debe iniciar el servidor desde la linea de comandos de una ventana de consola con la opción --console si se desea dirigir la salida a la ventana en lugar de usar para ello el registro de errores.

La salida del Monitor incluye información de los siguientes tipos:

Tablas y bloqueos de registros en uso por cada transacción activa.

Esperas de transacciones debidas a bloqueos.

Esperas de subprocesos debidas a semáforos.

Solicitudes de E/S de ficheros pendientes.

Estadísticas del pool de buffer.

Actividad de descarga y mezcla del buffer de inserciones del subproceso principal de InnoDB.

Para que el Monitor estándar InnoDB escriba en la salida estándar de mysqld, se debe emplear la siguiente sentencia SQL:

CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

El monitor puede detenerse emitiendo la siguiente sentencia:

DROP TABLE innodb_monitor;

La sintaxis de CREATE TABLE es solamente una forma de pasar un comando al motor InnoDB a través del intérprete SQL de MySQL: Lo único importante aquí es que la tabla se llame innodb_monitor y que sea una tabla InnoDB. La estructura de la tabla no es relevante para el MonitorInnoDB. si se apaga el servidor mientras el monitor se está ejecutando, y se desea iniciar el monitor nuevamente, se debe eliminar la tabla antes de emitir una nueva sentencia CREATE TABLE para iniciar el monitor. Esta sintaxis puede cambiar en una entrega futura de MySQL.

De la misma forma se puede emplear innodb_lock_monitor. Esto es lo mismo que innodb_monitor, con la excepción de que también proporciona abundante información sobre bloqueos. Un innodb_tablespace_monitor separado imprime una lista de los segmentos de ficheros creados existentes en el espacio de tablas y valida las estructuras de datos de asignación del espacio de tablas. Adicionalmente, hay un innodb_table_monitor con el que se pueden imprimir los contenidos del diccionario de datos interno de InnoDB.

Un ejemplo de la salida del Monitor InnoDB:

mysql> SHOW INNODB STATUS\G *************************** 1. row *************************** Status: ===================================== 030709 13:00:59 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 18 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 413452, signal count 378357 --Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the semaphore: X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135 a writer (thread id 32782) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 731 Last time write locked in file btr0sea.c line 1347 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits 375485 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 030709 13:00:59 Transaction: TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 inser ting 15 lock struct(s), heap size 2496, undo log entries 9 MySQL thread id 25, query id 4668733 localhost heikki update insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk') Foreign key constraint fails for table test/ibtest11a: , CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`, `D`) ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index PRIMARY tuple: 0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2: len 4; hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4: len 7; hex 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;; But in parent table test/ibtest11b, in index PRIMARY, the closest match we can find is record: RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex 80000005; a sc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex 0000111ef3eb; asc ...... ;; 4: len 7; hex 800001001e0084; asc .......;; 5: len 3; hex 6b6864; asc khd;; ------------------------ LATEST DETECTED DEADLOCK ------------------------ 030709 12:59:58 *** (1) TRANSACTION: TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733 inser ting LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146 MySQL thread id 21, query id 4553379 localhost heikki update INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t','e187358f' ,'g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),7 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole trx id 0 290252780 lock mode S waiting Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a a35818;; 1: *** (2) TRANSACTION: TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782 inser ting 130 lock struct(s), heap size 11584, undo log entries 437 MySQL thread id 23, query id 4554396 localhost heikki update REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','', NULL,' h396', NULL, NULL, 7.31,7.31,7.31,200) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole trx id 0 290251546 lock_mode X locks rec but not gap Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a a35818;; 1: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole trx id 0 290251546 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230; asc aa 35720;; 1: *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 0 290328385 Purge done for trx's n:o < 0 290315608 undo n:o < 0 17 Total number of lock structs in row lock hash table 70 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002 MySQL thread id 32, query id 4668737 localhost heikki show innodb status ---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id 38929 in serting 1 lock struct(s), heap size 320 MySQL thread id 29, query id 4668736 localhost heikki update insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlhh gghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh ---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id 28684 co mmitting 1 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 19, query id 4668734 localhost heikki update insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlh hgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf ---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id 36880 st arting index read LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 27, query id 4668644 localhost heikki Searching rows for update update ibtest11a set B = 'kHdkkkk' where A = 89572 ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index PRIM ARY trx id 0 290328327 lock_mode X waiting Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; ------------------ ---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 ro llback of SQL statement ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9 MySQL thread id 25, query id 4668733 localhost heikki update insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk') ---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id 32782 58 lock struct(s), heap size 5504, undo log entries 159 MySQL thread id 23, query id 4668732 localhost heikki update REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t','e200498f ','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'), ---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id 30733 in serting 4 lock struct(s), heap size 1024, undo log entries 165 MySQL thread id 21, query id 4668735 localhost heikki update INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','', NULL, 'h321', NULL, NULL, 7.31,7.31,7.31,200) -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs 25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 19, seg size 21, 85004 inserts, 85004 merged recs, 26669 merges Hash table size 207619, used cells 14461, node heap has 16 buffer(s) 1877.67 hash searches/s, 5121.10 non-hash searches/s --- LOG --- Log sequence number 18 1212842764 Log flushed up to 18 1212665295 Last checkpoint at 18 1135877290 0 pending log writes, 0 pending chkp writes 4341 log i/o's done, 1.22 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 84966343; in additional pool allocated 1402624 Buffer pool size 3200 Free buffers 110 Database pages 3074 Modified db pages 2674 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 171380, created 51968, written 194688 28.72 reads/s, 20.72 creates/s, 47.55 writes/s Buffer pool hit rate 999 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 3004, id 7176, state: purging Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779 1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.05 sec)

Algunas notas acerca de la salida:

Si la sección TRANSACTIONS informa sobre esperas por bloqueo, la aplicación puede tener conflictos causados por bloqueos. La salida también puede ayudar a determinar las razones de interbloqueos en transacciones.

La seccion SEMAPHORES informa sobre esperas de subprocesos debidas a semáforos y brinda estadísticas de cuántas veces los subprocesos han debido esperar por un mutex o un semáforo rw-lock. Una gran cantidad de subprocesos esperando por semáforos puede ser el resultado de operaciones de E/S en disco, o conflictos dentro de InnoDB. Los conflictos pueden deberse a un intenso paralelismo en las consultas, o problemas en la planificación de subprocesos del sistema operativo. En tales situaciones puede ser de ayuda establecer innodb_thread_concurrency en un valor más bajo.

La sección BUFFER POOL AND MEMORY proporciona estadísticas sobre las páginas leídas y escritas. A partir de estas cifras se puede calcular cuántas operaciones de E/S sobre ficheros de datos están realizando actualmente las consultas emitidas.

La sección ROW OPERATIONS muestra lo que está haciendo el subproceso principal.

InnoDB envía su salida de diagnóstico a stderr o a ficheros en lugar de a stdout o a buffers de memoria de tamaño fijo, para evitar potenciales desbordamientos de buffer. Como efecto secundario, la salida de SHOW INNODB STATUS se escribe cada quince segundos en un fichero de estado. El nombre de este fichero es innodb_status.pid, donde pid es el ID del proceso del servidor. Este fichero se crea en el directorio de datos de MySQL. InnoDB borra el fichero durante un apagado normal del servidor. Si se producen caídas o terminaciones anormales del servidor, pueden quedar copias de estos ficheros que deberán ser eliminadas manualmente. Antes de eliminarlas, se las podría examinar para ver si contienen información útil relativa a la causa de las caídas. En MySQL 5.0, innodb_status.pid solamente se crea si se establece la opción de configuración innodb_status_file=1.

previous page start next page

15.11. Consejos de afinamiento del rendimiento de InnoDB

MySQL 5.0