Tengo un problema que ocasionalmente (una o dos veces por semana, no necesariamente durante las horas pico) las consultas en una determinada base de datos (la base de datos del mayor de mis sitios) de repente dejan de responder durante aproximadamente una hora. Las consultas comienzan a acumularse, principalmente en los estados de "envío de datos" y "estadísticas", eventualmente la carga sube > 40 y todo el servidor se detiene.
Mientras ocurre este problema, si desactivo este sitio, todos los demás sitios y bases de datos en el mismo servidor se ejecutan normalmente.
Tengo el resultado de "MOSTRAR ESTADO INNODB DEL MOTOR" cuando no respondía, me pregunto si alguien podría decirme si muestra alguna anomalía. Eliminé la sección "TRANSACCIONES" que enumera todas las consultas pendientes, puedo agregar si fuera de ayuda.
Servidor comunitario MySQL 5.1.70-cll, sin complemento Innodb, CentOS 5.9
Gracias de antemano.
=====================================
130901 17:10:26 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 50 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 25977, signal count 14540
--Thread 140248378771776 has waited at btr/btr0cur.c line 467 for 1.00 seconds the semaphore:
S-lock on RW-latch at 0x7f8e273f8ae8 created in file buf/buf0buf.c line 550
a writer (thread id 140248378771776) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0sea.c line 794
Last time write locked in file buf/buf0buf.c line 1820
--Thread 140248434157888 has waited at btr/btr0cur.c line 467 for 3.00 seconds the semaphore:
S-lock on RW-latch at 0x7f8e272877c8 created in file buf/buf0buf.c line 550
a writer (thread id 140248434157888) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0sea.c line 794
Last time write locked in file buf/buf0buf.c line 1820
Mutex spin waits 0, rounds 550914, OS waits 8104
RW-shared spins 34629, OS waits 16634; RW-excl spins 2759, OS waits 586
--------
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: doing file i/o (read thread) ev set
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 172, 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
105909 OS file reads, 8365 OS file writes, 4079 OS fsyncs
1 pending preads, 1 pending pwrites
106.42 reads/s, 19475 avg bytes/read, 9.50 writes/s, 4.68 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 20, free list len 70, seg size 91,
1099 inserts, 8617 merged recs, 2154 merges
Hash table size 1888559, node heap has 2598 buffer(s)
8189.64 hash searches/s, 941.80 non-hash searches/s
---
LOG
---
Log sequence number 196 2706819157
Log flushed up to 196 2706818752
Last checkpoint at 196 2705105219
0 pending log writes, 0 pending chkp writes
3880 log i/o's done, 4.42 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1061750976; in additional pool allocated 1048576
Dictionary memory allocated 1530760
Buffer pool size 58240
Free buffers 0
Database pages 55642
Modified db pages 1543
Pending reads 172
Pending writes: LRU 120, flush list 0, single page 0
Pages read 127164, created 59, written 5866
126.52 reads/s, 0.08 creates/s, 5.82 writes/s
Buffer pool hit rate 996 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 27 queries in queue
17 read views open inside InnoDB
Main thread process no. 2504, id 140248394586432, state: sleeping
Number of rows inserted 963, updated 4397, deleted 5, read 43384960
1.56 inserts/s, 4.92 updates/s, 0.00 deletes/s, 17291.99 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Respuesta1
Resuelto aumentando el grupo de buffers de innodb al 60% de la RAM y también reconstruyendo la base de datos (exportar/eliminar/importar)