Processes, Threads & Connections

Suele pasar mucho que nos confundimos con estos tres términos; procesos, hilos y conexiones (Processes, Threads & Connections), les quiero explicar un poco de que va cada uno y como están relacionados:

  • MySQL Server es un proceso de aplicación.
  • MySQL Server es multi hilo (multithreaded).
  • MySQL Server acepta multiples conexiones por TCP/IP.
  • MySQL Server dedica por cada conexión un hilo (thread).
  • Un hilo se puede llamar de vez en cuando proceso, y un proceso puede hacer referencia a una conexión.

La última parte suena confuso, y me explico; MySQL Server (mysqld) es solo un proceso que se está ejecutando en el Sistema Operativo, el cual éste proceso permite tener muchos hilos de ejecución, un hilo por cada conexión realizada con el servidor, cuando se cierra la conexión se destruye el hilo.

Crear y eliminar conexiones es una tarea costosa, porque se requiere administrar de forma dinámica una serie de recursos físicos, en especial la Memoria RAM, por eso existe la cache de hilos para optimizar las conexiones no persistentes, recuerda ver las variables: thread_cache_size y Threads_cached.

Si pensamos en todo esto, nos preguntamos porque el comando “SHOW PROCESSLIST” se llama así y no “SHOW THREADLIST“? Debe ser que es más fácil asociar proceso que hilo, una respuesta acertada no la sé.

Vamos a demostrar que todo esto tiene sentido en el servidor, lo primero es que debemos crear varias conexiones y las mandamos al background, cada una de las conexión ejecutas es una sentencia SQL SLEEP para esperar 120 segundos y evitar que se cierre, al terminar el tiempo de espera se cierran y así lo hacemos más divertido:

$ for i in {1..4}
do
  mysql -h localhost -u root -e 'SELECT SLEEP(120);' &
done

Ahora accedemos para ejecutar los comandos administrativos e ir comparando valores:

$ mysql -h localhost -u root
mysql> show processlist;
+----+------+-----------+------+---------+------+------------+-------------------+
| Id | User | Host      | db   | Command | Time | State      | Info              |
+----+------+-----------+------+---------+------+------------+-------------------+
|  5 | root | localhost | NULL | Query   |    9 | User sleep | SELECT SLEEP(120) |
|  6 | root | localhost | NULL | Query   |    9 | User sleep | SELECT SLEEP(120) |
|  7 | root | localhost | NULL | Query   |    9 | User sleep | SELECT SLEEP(120) |
|  8 | root | localhost | NULL | Query   |    9 | User sleep | SELECT SLEEP(120) |
|  9 | root | localhost | NULL | Query   |    0 | init       | show processlist  |
+----+------+-----------+------+---------+------+------------+-------------------+

Con este comando podemos ver cual es el ID de la conexión en la lista de procesos:

SELECT CONNECTION_ID();

Aquí podemos consultar el estado de las variables relacionadas a las conexiones, estás indican el número de conexiones cacheadas, conectadas, creadas y ejecutadas:

mysql> SHOW GLOBAL STATUS LIKE 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 5     |
| Threads_created   | 5     |
| Threads_running   | 5     |
+-------------------+-------+

Al finalizar las consultas enviadas anteriormente con el loop, podemos ver de nuevo el estado de las variables del servidor, ahora se tienen cacheadas las últimas 4 conexiones, solo hay una conexión activa que es la que está ejecutando este comando, en total se han ejecutado 5 conexiones, y solo hay una en ejecución, que es ésta.

mysql> SHOW GLOBAL STATUS LIKE 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 4     |
| Threads_connected | 1     |
| Threads_created   | 5     |
| Threads_running   | 1     |
+-------------------+-------+

Variables

Hay un par de variables que son para gestionar los recursos de conexión, es bueno revisarlas y asegurarse que todo está bien configurado en el servidor.

Origen de la palabra NoSQL

Es curioso como se desencadena toda una revolución por un simple hashtag. Aquí les dejo una breve historia de su verdadero origen:

Johan Oskarsson organizó un evento en Junio de 2009 en San Francisco, la intención era discutir las nuevas tecnologías en el mundo IT sobre el almacenamiento y procesamiento de datos. La principal razón del evento fueron los nuevos productos como BigTable y Dynamo. Para el evento era necesario encontrar una palabra clave para ser usada en un hashtag de Twitter, término siendo NoSQL y fue sugerido por Eric Evans de RackSpace. El término fue planeado para ser utilizado sólo para ésta reunión y no tenía un significado profundo. Pero resultó que se extendió por la red de forma viral y se convirtió en el nombre de facto de una tendencia de las bases de datos no relacionales y distribuidas.

El término “NoSQL” tiene un origen absolutamente natural y éste no está avalado por una institución científica. Se considera que el término esta lejos de su completa definición, hay autores como Pramod J. Sadalage y Martin Fowler que trataron de agrupar y organizar todo el conocimiento sobre el mundo NoSQL en el libro “NoSQL destilada“.

Fuentes:

Eventos y bloqueo exclusivo

Puede pasar que la ejecución de un procedimiento almacenado tarde más tiempo de lo previsto, y como hay un evento que lo ejecuta cada X tiempo, estos empiezan a verse amontonados en la lista de procesos, como podemos imaginar es muy malo. Afortunadamente MySQL tiene unas funciones que nos permite hacer un bloqueo exclusivo, así garantizamos que se ejecute solo uno a la vez.

En el siguiente ejemplo, tenemos un evento que se ejecuta cada 1 segundo y llama a un procedimiento almacenado que tarda 5 segundos en ejecutarse. Lo ideal de este caso, es que podremos observar en la lista de procesos 5 eventos ejecutando al mismo tiempo.

DELIMITER |
CREATE PROCEDURE sp_wait()
BEGIN
    SELECT SLEEP(5);
END|
DELIMITER ;

DROP EVENT IF EXISTS ev_wait;
DELIMITER |
CREATE EVENT ev_wait ON SCHEDULE EVERY 1 SECOND DO
BEGIN
  CALL sp_wait();
END|
DELIMITER ;

Una vez creado el procedimiento almacenado y el evento, ya podremos observar como se amontonan en la lista de procesos usando el comando “SHOW PROCESSLIST;”:

event_lock1

La solución es muy simple, vamos a implementar dos funciones; GET_LOCK y RELEASE_LOCK para hacer el bloqueo exclusivo, en el ejemplo siguiente podemos ver como lo implementamos:

DROP EVENT IF EXISTS ev_wait;
DELIMITER |
CREATE EVENT ev_wait ON SCHEDULE EVERY 1 SECOND DO
BEGIN
  SELECT GET_LOCK('temp.sp_wait', 0) INTO @got_lock;

  IF @got_lock = 1 THEN
    CALL sp_wait();

    SELECT RELEASE_LOCK('temp.sp_wait');
  END IF;
END|
DELIMITER ;

Ahora podremos observar en la lista de procesos que siempre hay un único proceso en ejecución y no 5 como antes:

event_lock2

¿Cómo hacer un UPDATE en una tabla grande sin bloquearla?

De vez en cuando nos toca hacer un gran UPDATE sobre una tabla grande que nos puede llevar 10 min. para completar su ejecución, el problema es que si lo hacemos directamente bloqueamos la tabla hasta que termine, eso puede ocasionar varios problemas muy graves a nivel de aplicación, hoy presento una alternativa usando Procedimientos Almacenados.

Básicamente consiste en tener una sentencia que hace UPDATE a cada 1000 rows, se ejecuta dicha query N veces hasta llegar a 0 rows actualizados, en cada interacción hacemos una pequeña pausa de 2 segundos para dejar que pasen las transacciones que están esperando usar dicho recurso.

DROP PROCEDURE IF EXISTS sp_users_update;

DELIMITER |
CREATE PROCEDURE sp_users_update()
  BEGIN
    SET @row_count = 0;

    REPEAT
      -- Query to update:
      UPDATE LOW_PRIORITY users SET username = CONCAT('DELETED_', username)
      WHERE deleted_at IS NOT NULL
      AND username NOT LIKE 'DELETED_%'
      LIMIT 1000;

      -- Save row counts for update:
      SET @row_count = (SELECT ROW_COUNT());

      -- Sleep for not blocking table:
      SELECT SLEEP(2);
    UNTIL @row_count = 0 END REPEAT;
  END;
|
DELIMITER ;

CALL sp_users_update();

DROP PROCEDURE IF EXISTS sp_users_update;

La verdad que es una alternativa rápida a comparación de un script, es cierto que el proceso de buscar con LIKE es algo costoso para la CPU si son muchos rows, de echo pude observar un pico durante la actualización, nada grave por ser una situación controlada y probada en otros entornos.