Too many connections, Slow Queries y Locks

Este es un caso que me he encontrado ya varias veces y, al menos la primera vez, fue complicado de diagnosticar.

Todo empieza porque detectamos (vía monitorización o porque llama el cliente, uff), que el contenido que se muestra en una wed desde base de datos... no aparece.

Al ojear los logs de Apache en error_log vemos trazas tal que:

[Thu Sep 04 17:15:53 2008] [error] [client 74.6.*.*] PHP Warning:  mysql_select_db() [<a href='function.mysql-select-db'>function.mysql-select-db</a>]: Too many connections in /var/www/html/whatever.php on line X

Lo primero a comprobar es, vía access_log, si tenemos muchas peticiones simultáneas, pero... sorpresa: ¡no hay!

Por defecto MySQL tiene configuradas 100 conexiones simultáneas, que en general suele ser un buen valor para la mayoría de los casos.

Entonces, si tenemos pocas peticiones y, por lo tanto, pocas consultas a la base de datos, ¿qué es lo que está acabando con nuestro MySQL?

Bueno, el caso más habitual que me encuentro es un foro PhpBB2 (uff, lo sé :D) con tablas que tienden a degradarse por cómo está diseñado el sistema para las búsquedas en el foro. Se abusa de la indexación de palabras buscadas, y nos encontramos con tablas de más de 20 millones de filas.

La clave para el diagnóstico es ejecutar un SHOW PROCESSLIST;, y ver el siguiente resultado:

+--------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id     | User   | Host      | db     | Command | Time | State                | Info                                                                                                 |
+--------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 383506 | bd_clientu | localhost | bd_client | Query   |  729 | Copying to tmp table | SELECT m.word_id 
				FROM phpbb_search_wordmatch m, phpbb_search_wordlist w 
				WHERE w.word_text  | 
| 383526 | bd_clientu | localhost | bd_client | Query   |  237 | Copying to tmp table | SELECT m.word_id 
				FROM phpbb_search_wordmatch m, phpbb_search_wordlist w 
				WHERE w.word_text  | 
| 383535 | bd_clientu | localhost | bd_client | Query   |  228 | Copying to tmp table | SELECT m.word_id 
				FROM phpbb_search_wordmatch m, phpbb_search_wordlist w 
				WHERE w.word_text  | 
| 383541 | bd_clientu | localhost | bd_client | Query   |  149 | Copying to tmp table | SELECT m.word_id 
				FROM phpbb_search_wordmatch m, phpbb_search_wordlist w 
				WHERE w.word_text  | 
| 383542 | bd_clientu | localhost | bd_client | Query   |  148 | Copying to tmp table | SELECT m.word_id 
				FROM phpbb_search_wordmatch m, phpbb_search_wordlist w 
				WHERE w.word_text  | 
| 383572 | bd_clientu | localhost | bd_client | Query   | 2511 | Sending data         | INSERT INTO phpbb_search_wordmatch (post_id, word_id, title_match) 
				SELECT 152262, word_id, 1  
 | 
| 383574 | bd_clientu | localhost | bd_client | Query   | 2511 | Locked               | INSERT INTO phpbb_search_wordmatch (post_id, word_id, title_match)
                                SELECT 152263, word_id, 1
 |
[... etc ...]
| 384946 | root   | localhost | NULL   | Query   |    0 | NULL                 | SHOW PROCESSLIST                                                                                     | 
+--------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
100 rows in set (0.00 sec)

Con lo que ahí tenemos nuestras 100 conexiones ocupadas ;). He recortado la salida, porque hay muchas entradas como la que he destacado, y la clave está en rojo.

En este foro una búsqueda lleva implícita un SELECT y un INSERT, siendo ambas operaciones lentas en una tabla muy grande.

En este caso el problema es que bots de spammers entran en el foro y hacen gran cantidad de búsquedas de forma que nuestra tabla degradada (recordemos que hablamos de millones de registros) satura el servidor de bases de datos, lo que acaba en una auténtica denegación de servicio :(.

Las solución inmediata pasa por aumentar el número máximo de conexiones para MySQL (en my.cnf, max-connections en el apartado [mysqld]), confiando que el servidor aguante así el tirón, pero lo más probable es que el problema se vuelva a producir con el tiempo.

Para arreglarlo de verdad, se podría evitar el acceso de los bots, pedir que el usuario del foro esté registrado para hacer búsquedas, o simplemente cambiar de aplicación para el foro (aunque esto último no es justo, porque me he encontrado con el mismo problema con software que disfruta de mucha mejor consideración en la comunidad y que se supone de gran calidad...).