15.11. Consejos de afinamiento del rendimiento de InnoDB

MySQL 5.0

previous page next page

15.11. Consejos de afinamiento del rendimiento de InnoDB

  • Si la utilidad 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. 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 el parámetro a un valor de 0. 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 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 en lugar de si se almacenarán cadenas de longitud variable o si la columna contendrá muchos valores . Una columna ) siempre utiliza bytes para almacenar los datos, inclusive si la cadena es más corta o es . 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 (el formato de registro predeterminado para InnoDB en MySQL 5.0) y un conjunto de caracteres de longitud variable como o , ) ocupará una cantidad variable de espacio, con un mínimo de bytes.

  • En algunas versiones de GNU/Linux y Unix, descargar ficheros a disco con la función de Unix (la cual es utilizada en forma predeterminada por ) 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 en a , si bien 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 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 se producirá una seria degradación en la velocidad y rendimiento de InnoDB en esta plataforma.

  • Al importar datos dentro de , 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 y : 

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

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

  • Tener cuidado con las cancelaciones de inserciones masivas: 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 y para obtener una tabla vacía, no .

  • Emplear la sintaxis de múltiples filas de 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 .

  • Si se tienen restricciones 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 puede emplear su buffer de inserción para escribir de una vez los registros de indice secundarios.

  • Si se tienen restricciones 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

incluye los Monitores que muestran información relativa al estado interno de . Se puede emplear la sentencia SQL para obtener la salida del Monitor 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 .) Para más información sobre los modos de bloqueo de consulte Sección 15.10.1, “Modos de bloqueo ”. 

mysql> SHOW INNODB STATUS\G

Otra forma de emplear los Monitores 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 imprimen datos aproximadamente cada 15 segundos. La salida del servidor normalmente se dirige a un fichero 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 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 .

Para que el Monitor estándar 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 es solamente una forma de pasar un comando al motor a través del intérprete SQL de MySQL: Lo único importante aquí es que la tabla se llame y que sea una tabla . La estructura de la tabla no es relevante para el Monitor. 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 para iniciar el monitor. Esta sintaxis puede cambiar en una entrega futura de MySQL.

De la misma forma se puede emplear . Esto es lo mismo que , con la excepción de que también proporciona abundante información sobre bloqueos. Un 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 con el que se pueden imprimir los contenidos del diccionario de datos interno de .

Un ejemplo de la salida del Monitor : 

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 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 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 . 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 en un valor más bajo.

  • La sección 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 muestra lo que está haciendo el subproceso principal.

envía su salida de diagnóstico a o a ficheros en lugar de a o a buffers de memoria de tamaño fijo, para evitar potenciales desbordamientos de buffer. Como efecto secundario, la salida de se escribe cada quince segundos en un fichero de estado. El nombre de este fichero es , donde es el ID del proceso del servidor. Este fichero se crea en el directorio de datos de MySQL. 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, solamente se crea si se establece la opción de configuración .

previous page start next page