MySQL, rotado de slow logs

Hace un tiempo publicamos una entrada acerca de cómo activar el log de Slow Query's para MySQL. Esta práctica suele ser bastante habitual y recomendada. Si una query tarda mucho en ejecutarse puede comprometer todo el rendimiento del sistema. Así que mejor saber cual es, para poder solucionarla (si es posible).

El detalle que nos trae a cuenta es que este log, al final es un fichero de texto con muchas entradas, pero que crece de forma indefinida y cada vez ocupa más o más espacio. Para evitar que crezca de forma descontrolada, lo que debemos hacer es rotarlo. Para hacer esta tarea, los sistemas GNU/Linux emplean logrotate.

Logrotate tiene dos técnicas de rotación,

• copytruncate, copia el fichero a uno nuevo y luego lo trunca.
• no copytruncate, emplea la función rename del sistema para mover el fichero a uno nuevo. Luego el daemon que escribe necesita recibir la señal de reopen.

El problema que tenemos es que truncar el fichero de logs de MySQL puede hacer que éste se bloquee y cambiar el nombre del fichero y reiniciar el servicio, no siempre es posible, ya que dejaríamos el sistema por un tiempo sin servicio. Puede no ser mucho, pero suficiente. Para solucionar este problema, desde el blog de Percona ofrecen una solución. Lo primero es crear el fichero de configuración en logrotate (mysql-slow).

/var/log/mysql/slow_query.log {
   nocompress
   create 660 mysql mysql
   size 1G
   dateext
   missingok
   notifempty
   sharedscripts
   postrotate
      mysql -u logrotate -ppasswd -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_time=@lqt_save;'
   endscript
   rotate 150

A diferencia del código original, nosotros vamos a configurar un usuario que tenga acceso restringido al servicio MySQL para poder hacer las operaciones que necesita. El usuario será logrotate,

mysql> CREATE USER 'logrotate'@'localhost' IDENTIFIED BY 'passwd';
mysql> GRANT select,file,super,reload ON *.* TO 'logrotate'@'localhost';

La idea de crear este usuario es para evitar problemas de seguridad de acceder con root o tener el sistema sin contraseña para localhost.

Con el usuario ya creado, probamos si el fichero de configuración de logrotate funciona. Para ello,

shell> logrotate -vf /etc/logrotate.d/mysql-slow

Si no hay fallos, el fichero de logs se rotará de forma automatizada.

Leer más

Expiración de contraseñas en MySQL

Desde la versión 5.6.6, MySQL implementó la posibilidad de poner caducidad o tiempo de expiración de contraseñas. Esto era algo que se llevaba mucho tiempo hablando y que era necesario, ya que la seguridad de los datos también reside en el lugar dónde se almacenan. Para reforzar esto, MySQL puede obligar a un usuario a cambiar la contraseña o el propio administrador puede forzar dicho cambio.

mysql> ALTER USER 'javier'@'localhost' PASSWORD EXPIRE;

Con ello, la próxima vez se conecte dicho usuario tendrá que cambiar la contraseña.

shell> mysql -u javier -p
mysql> SELECT 1;
  ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Con lo que nos está avisando de que necesitamos restablecer nuestra contraseña para poder hacer cualquier cosa. Para ello,

mysql> SET PASSWORD = PASSWORD('your_password');
  Query OK, 0 rows affected (0.01 sec)

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Si el cliente que se está usando para la conexión no tiene soporte para cambiar la contraseña, obtendremos el siguiente mensaje al conectar,

shell> mysql -u myuser -p
  ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

Con lo que tendremos que actualizar el cliente de conexión para poder seguir trabajando.

Este cambio es importante, pero también debemos tener cuidado con él, pues la fecha de expiración de una contraseña para usuarios de consulta web, por ejemplo, podría darnos dolor de cabeza.

Más información sobre esta nueva feature en la documentación oficial.

Leer más

Permisos de usuario avanzados en MySQL

Por temas del trabajo llevo unos días revisando documentación de MySQL y en la parte referente a los permisos de usuario descubrí que hay muchos más límites que se le pueden establecer a un usuario al conectarse al motor de base de datos. Hablando ya de cosas concretas, estamos hablando de límites para:

• Número de consultas a realizar por hora
• Número de actualizaciones a realizar por hora
• Número de conexiones por hora
• Número de conexiones simultáneas por usuario.

Cómo aplicar los nuevos límites

Estos límites son establecidos para cada usuario, y por lo tanto deben ser indicados para el usuario cuando se crea (comando GRANT), como se ve a continuación.

mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY 'passwd';
mysql> GRANT ALL ON *.* TO 'test'@'localhost'
    -> WITH MAX_QUERIES_PER_HOUR 20
    -> MAX_UPDATES_PER_HOUR 10
    -> MAX_CONNECTIONS_PER_HOUR 20
    -> MAX_USER_CONNECTIONS 1;

Hay que tener en cuenta que estos límites son para query's que no estén en caché, es decir, todas las que implican acceso a recursos. Por lo tanto, si se emplea la misma query se puede realizar más de las N veces establecidas como límite.

Una vez alcanzado el límite establecido, se nos advertirá de ello y no se podrá hacer nada hasta que pase una hora.

mysql> SELECT * FROM users;
ERROR 1226 (42000): User 'test' has exceeded the 'max_questions' resource

Cómo modificar los límites

Lógicamente, también se pueden modificar dichos límites, por ejemplo,

mysql> GRANT USAGE ON *.* TO 'test'@'localhost'
    -> WITH MAX_QUERIES_PER_HOUR 50;

Cómo borrar los límites

Y para borrarlos sólo tenemos que establecer a 0 su valor.

mysql> GRANT USAGE ON *.* TO 'test'@'localhost'
    -> WITH MAX_QUERIES_PER_HOUR 0;

Más información:

• MySQL doc

Leer más

Grabando sesiones MySQL completas

MySQL tiene una opción un tanto curiosa y a la vez interesante y es la de grabar una sesión completa de lo que se está haciendo. No deja de ser curioso que permita volcar en un fichero todo el trabajo, así como salidas que ofrece, pero a la vez puede ser práctico para enviarle a alguien y que vea en qué estás trabajando.

La opción que comentamos es --tee y únicamente hay que pasarle el fichero donde quieres dejar tu sesión grabada.

shell> mysql -p --tee=~/session.mysql.log

Leer más

Simplificar las conexiones a MySQL

Si estamos trabajando con varios sistemas, lo más probable es que terminemos por tener varios servidores de MySQL en la red. Ya bien sea en un esquema lógico (Producción - PreProducción - Desarrollo), ya bien sea por que cada proyecto requiere una instalación diferente. La vida del sysadmin es así ;-)

El detalle es que siempre que tenemos muchos servidores que manejar, debemos de tener muy claro a qué servidor nos vamos a conectar, así como las credenciales del mismo. Para simplificarlo, MySQL, al más puro estilo ssh, permite la creación de un fichero en la home de cada usuario llamado ~/.my.cnf en el cual podemos definir nombres identificativos para cada una de las conexiones y simplemente con llamar al nombre, el cliente MySQL cogerá las credenciales y opciones de cada uno.

Puesto que este fichero es delicado, debemos de tener sumo cuidado en los permisos que le establecemos, así que únicamente lectura/escritura para el usuario.

Si deseamos que todos los usuarios de un sistema puedan hacer uso de un mismo nombre, lo podemos definir en el fichero general de configuración, /etc/mysql/my.cnf. En cualquiera de los casos, la configuración es la misma.

Para añadir varios, pues, debemos de crear una sección [client] para cada uno de ellos, con el nombre que deseemos a continuación. Este nombre es mejor que sea descriptivo. Después, dentro de cada sección podemos definir el usuario, la contraseña del mismo, el equipo o forma de conexión e incluso el prompt si deseamos que lo cambie.

[clientWeb]
   user=root
   password=your_passwd
   host=192.168.1.33
   prompt=\\u@\\h [\\d]>\\_

[clientZabbix]
   user=root
   password=your_passwd
   host=zabbix.local.net
   prompt=\\u@\\h [\\d]>\\_

Ahora que lo tenemos creado, simplemente debemos de indicarle al cliente MySQL que haga uso del nombre. Para ello empleamos la opción --defaults-group-suffix.

Si por ejemplo deseamos conectarnos al servidor web, entonces hacemos,

shell> mysql --defaults-group-suffix=Web

Como veis es mucho más cómodo y fácil de usar ahora el cliente de MySQL. Además, de que no tienes que tener presentes las credenciales para conectarte, lo que te permitirá definir unas contraseñas robustas para las cuentas.

Leer más

MySQL: Error writing file (No space left on device)

Si trabajando con MySQL obtenéis el siguiente error,

Error writing file '/tmp/pTdf4iTz' (Errcode: 28)

Lo primero que deberíais de hacer, no antes este error, sino ante cualquier, es mirar qué significa. Para ello,

# perror 28
OS error code 28: No space left on device

Por lo que parece, todo apunta a que no hay espacio libre en el directorio temporal sobre el que trabaja MySQL. Para comprobar cual es este,

mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.00 sec)

Y ahora comprobamos si efectivamente hay o no espacio libre y obtenemos lo siguiente,

shell> df -hP /tmp
S.ficheros            Size  Used  Avail  Use%  Montado en
/dev/mapper/vg0-tmp   504M   93M   387M   20%        /tmp

Tenemos un 80% de espacio libre y aun así nos está fallando la query por falta de espacio.

La explicación

MySQL cuando realiza una query emplea espacio temporal y al finalizarla y devolver los datos, borra ese fichero temporal. Lo que está sucediendo es que el espacio libre, sí esos casi 400 Mb no son suficientes para manejar el fichero temporal que genera la query.

La solución

Optimizar más la query o darle un directorio temporal a MySQL con más espacio libre para que la query se pueda ejecutar correctamente.

Leer más

Rotar log de slow querys en MySQL

El otro día hablamos de cómo rotar el log de errores de MySQL y hoy vamos a ver cómo hacerlo con el log de las slow-querys, en caso de que estén activadas.

Tener un servidor de MySQL con slow querys no es buena idea, ya que penalizará bastante el rendimiento de la aplicación que emplee la base de datos, pero peor idea es no tener registro de que esto esté sucediendo. Así que te aconsejo que si no lo tienes activado, mires cómo hacerlo.

Si ya tienes esta facility funcionando, entonces toca mantenerla, es decir, consultarla y cada cierto tiempo realizar un purgado del fichero, para evitar que crezca de forma descontrolada.

En GNU/Linux existe un sistema de rotado de logs, logrotate, que se puede emplear para hacer este proceso. Las dos técnicas que emplear son:

• copytruncate
  Copia el fichero con un nuevo nombre y trunca el original
• no copytruncate
  Renombra el fichero con un nuevo nombre y envía una señal al proceso que lo maneja para que vuelva a abrir el log

El problema es que MySQL tiene un mutex sobre dicho fichero, por lo que emplear un truncado del fichero puede provocar que el servicio se quede bloqueado. Así que la mejor forma es enviar una señal HUP al servicio para que reabra el fichero de log después del renombrado o la mejor forma, emplear "FLUSH LOGS" en combinación con una parada en el registro de las querys.

Os dejo aquí el pequeño script desarrollado para logrotate que hace todas las funciones de rotado del log de forma automática.

/var/log/mysql/slow_querys.log {
  nocompress
  create 660 mysql mysql
  size 1G
  dateext
  missingok
  notifempty
  sharedscripts
  postrotate
    /usr/bin/mysql -e 'select @@global.long_query_time into @lqt_save; \
                       set global long_query_time=2000; select sleep(2); \
                       FLUSH LOGS; select sleep(2); \
                       set global long_query_time=@lqt_save;'
  endscript
  rotate 150

Por defecto, este es bastante permisivo y necesitamos tener nuestro servidor de MySQL muy poco protegido, cosa que no interesa. Así que vamos a cambiar la línea de postrotate por otra igual, pero pasándole un usuario que tendrá los permisos necesarios para hacer estas tareas administrativas.

/usr/bin/mysql -u logrotate -ppasswd \
               -e 'select @@global.long_query_time into @lqt_save; \
                   set global long_query_time=2000; select sleep(2); \
                   FLUSH LOGS; select sleep(2); \
                   set global long_query_time=@lqt_save;'

El comando a ejecutar es el mismo, lo único que cambiamos es que ahora le pasamos un usuario con contraseña, que previamente tenemos que crear en el servidor. Como nos interesa darle los mínimos permisos posibles, estos serán:

• SELECT y FILE, para el "select ... into"
• SUPER, para los "set global"
• RELOAD, para el "flush slow logs"

Por lo tanto, la creación del usuario quedaría tal que así,

mysql> GRANT select, file, super, reload \
       ON *.* \
       TO 'logrotate'@'localhost' \
       IDENTIFIED BY 'passwd';
mysql> FLUSH PRIVILEGES;

Ya sólo nos quedará probarlo,

shell> logrotate -vf /etc/logrotate.d/mysql-slow

Si el resultado fue el correcto y esperado, el fichero de slow-querys quedará con un tamaño 0 y comenzarán a registrarle en él las nuevas querys que aparezcan. Habrá un fichero al lado de éste con las viejas querys, el cual podremos borrar o mover, pues ya no se usará. Es el histórico.

Más información: mysqlperformanceblog.com

Leer más

Rotar o purgar MySQL error.log

Cuando estás empleando MySQL de forma regular suele suceder que se producen errores, bien en el replicado (si empleas replicación M-M o M-S) o bien cualquier otro error que haga que el servidor de MySQL escriba directamente en el log de error. Cada vez que algo nuevo se escribe, este fichero ocupa un poco más y pasado un tiempo, es lógico que pueda llegar a alcanzar más de 1 Gb. Es por lo tanto buena idea hacer un rotado o purgado del mysql-error.log.

Puesto que lo que menos interesa es parar el servidor, desde la página oficial de MySQL nos dicen cómo hacerlo "en caliente" y de forma segura. Depende mucho de la versión que tengamos instalada, pero para versiones de MySQL superiores a la 5.5.7 la forma es la siguiente,

shell> mv mysql-error.log mysql-error.log.old
shell> mysqladmin -u root -p flush-logs
shell> mv mysql-error.log.old backup-directory

La última sentencia ejecutada, lo que hace es mover el fichero de error a un directorio donde tengamos espacio de almacenamiento. Lógicamente, si lo que deseas es un purgado de dicho fichero, puedes optar por borrarlo libremente.

Leer más

MySQL FULL PROCESSLIST

Fijo que muchos de vosotros os habéis preguntado alguna vez cómo obtener el proceso completo que se está ejecutando un MySQL. Por defecto, esto se consigue con 'show processlist', pero este comando, si el proceso ocupa más de un límite corta la línea, evitando así que veamos query's completas si éstas son muy grandes.

Por poner un ejemplo,

mysql> SHOW PROCESSLIST;
+----+------+-----------+-----+---------+------+-------+-----------------
| Id | User | Host      | db  | Command | Time | State | Info            
+----+------+-----------+-----+---------+------+-------+-----------------
| 10 | web  | localhost | web | Sleep   |  118 |       | NULL            
| 14 | web  | localhost | web | Query   |    0 | NULL  | SHOW PROCESSLIST
+----+------+-----------+-----+---------+------+-------+-----------------
2 rows in set (0.01 sec)

Como podemos observar, ahora que no hay ninguna query compleja, la salida es correcta y completa. Sin embargo, si ejecutamos alguna query grande y volvemos a ver los procesos, el resultado cambia sustancialmente.

mysql> SHOW PROCESSLIST;
+----+------+-----------+-----+---------+------+---------+---------------
| Id | User | Host      | db  | Command | Time | State   | Info          
+----+------+-----------+-----+---------+------+---------+---------------
| 10 | web  | localhost | web | Sleep   |  118 |         | NULL            
| 14 | web  | localhost | web | Query   |    0 | NULL    | SHOW PROCESSLIST
| 17 | webb | localhost | web | Query   |    0 | Sending | SELECT t.*,i.hostid FROM triggers t,items i,ho |
+----+------+-----------+-----+---------+------+--------------+----------
3 rows in set (0.01 sec)

En este caso, el proceso con Id: 17, un "SELECT" no está completo y por lo tanto no sabemos qué query es la que se está lanzando. Puede que esta información no sea necesaria, o puede que sí. En caso de que sí la necesites ver, el comando 'show' tiene la posibilidad de decirle que se ejecute con la salida completa y por lo tanto nos mostrará las query's. Para ello,

mysql> SHOW FULL PROCESSLIST;
+----+------+-----------+-----+---------+------+--------------+----------
| Id | User | Host      | db  | Command | Time | State        | Info     
+----+------+-----------+-----+---------+------+--------------+----------
| 10 | web  | localhost | web | Sleep   |  118 |              | NULL     
| 14 | web  | localhost | web | Query   |    0 | NULL         | SHOW PROCESSLIST
| 17 | webb | localhost | web | Query   |    0 | Sending data | SELECT t.*,i.hostid FROM triggers t,items i,hosts h WHERE h.hostid=t.hostid AND h.hostid=i.hostid AND h.hostid="15" h.status="0"
+----+------+-----------+-----+---------+------+--------------+----------
3 rows in set (0.01 sec)

Como vemos ahora el comando ya está al completo, que era justamente lo que buscábamos.

La entrada MySQL FULL PROCESSLIST la puedes leer en Puppet Linux.

Leer más

MySQL, cambio de nombres

Hace ya tiempo que no hablaba de cosas relacionadas sobre MySQL, así que hoy voy a escribir acerca de cambios de nombres sobre tablas, columnas y bases de datos. No suele ser lo habitual, pero pueda darse el caso de que tengamos que modificar el nombre de la base de datos o de una columna por una actualización, por ejemplo, y por lo tanto sí necesitemos.

Cambio de nombre a una tabla

mysql> RENAME TABLE users TO users_tmp;

Cambio de nombre a una columna

Partimos del siguiente esquema de la base de datos,

mysql> SHOW CREATE TABLE users;
+--------+------------------------------------------+
| Table  | Create Table                             |
+--------+------------------------------------------+
| users  | CREATE TABLE `users` (
  `userid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `alias` varchar(100) NOT NULL DEFAULT '',
  `name` varchar(100) NOT NULL DEFAULT '',
  `surname` varchar(100),
  `passwd` char(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`userid`),
  KEY `users_1` (`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1              |
+--------+------------------------------------------+
1 row in set (0.00 sec)

Y queremos cambiar el nombre de la columna surname por apellido. Aquí lo importante es la parte que viene a continuación, ya que tenemos que crear la nueva columna con el mismo tipo de datos que la vieja, para evitar pérdidas. Para ello,

mysql> ALTER TABLE users CHANGE surname apellido varchar(100);

La entrada MySQL, cambio de nombres la puedes leer en Puppet Linux.

Leer más

Linux benchmark II

Dentro de la serie de artículos relacionados con el benchmarcking de sistemas GNU/Linux, hoy vamos a ver cómo comprobar el rendimiento de una base de datos MySQL. Para hacerlo vamos a emplear el comando que introdujimos en el primer post, sysbench. Gracias a él, vamos a simular un número muy elevado de conexiones a MySQL y ejecutar una serie de sentencias, sacando un informe final de rendimiento.

Puesto que cuando evaluamos una base de datos, no siempre es necesario escribir en ella, sysbench lo tiene en cuenta y permite optar por hacer los test en RO o en RW. También, y para no alterar el correcto funcionamiento de las bases de datos que ya tenemos funcionando, sysbench creará la suya propia, con los datos adecuados para realizar los tests e introducirá una sería de tuplas aleatorias en ella.

Para comenzar, por lo tanto, vamos a preparar nuestro entorno de pruebas. Lo único que necesitaremos será una base de datos MySQL a la que poder conectarnos.

Preparando datos

Vamos a crear una tabla temporal dentro de la base de datos test, que tenga 1 millón de filas con las que posteriormente operar.

shell> sysbench --test=oltp --oltp-table-size=1000000 \
       --mysql-db=test --mysql-user=root --mysql-password=your_passwd \
       prepare

Una vez el comando haya finalizado, vamos a ver qué ha creado. Esto no es para nada necesario, pero así también nos hacemos una idea de qué tipo de datos emplear para los test.

mysql> show create table sbtest;
+--------+----------------------------------------------------+
| Table  | Create Table                                       |
+--------+----------------------------------------------------+
| sbtest | CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from sbtest limit 3;
+----+---+---+----------------------------------------------------+
| id | k | c | pad                                                |
+----+---+---+----------------------------------------------------+
|  1 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  2 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  3 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+----+---+---+----------------------------------------------------+
3 rows in set (0.00 sec)

Lo que más destaca aquí es el tipo de columnas que emplear. Un par de ellas de tipo int y una autoincremental y otras dos de tipo char.

Realizando comprobaciones

Antes de entrar en este punto sí es bastante importante echar un vistazo al manual de sysbench, pues las diferentes opciones con las que puede trabajar son importantes. Por defecto el tipo de tabla es InnoDB, que en mi caso es correcto por lo que no lo alteraré.

shell> sysbench --test=oltp --oltp-table-size=1000000 \
       --mysql-db=test --mysql-user=root --mysql-password=your_passwd \
       --max-time=60 --oltp-read-only=on --num-threads=8 run
...
OLTP test statistics:
    queries performed:
        read:                            261282
        write:                           0
        other:                           37326
        total:                           298608
    transactions:                        18663  (311.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 261282 (4353.94 per sec.)
    other operations:                    37326  (621.99 per sec.)
...

Otro ejemplo podría ser,

shell> sysbench --num-threads=16 --max-requests=100000 --test=oltp \
       --mysql-db=test --mysql-user=root --mysql-password=your_passwd \
       --oltp-table-size=1000000 --oltp-read-only=on run
...
transactions:                        100000 (304.29 per sec.)
...

Y otro más,

shell> sysbench --test=oltp \
       --mysql-db=test --mysql-user=root --mysql-password=your_passwd \
       --max-requests=10000 --num-threads=10 run
...
transactions:                        10000  (224.58 per sec.)
...

Como podemos observar, según las opciones especificadas en el comando, el rendimiento obtenido es diferente, de ahí la importancia de combinar este comando con una buena configuración. Según el objetivo que tengamos (conseguir más lecturas, más escrituras, un término intermedio, etc. tendremos que ir alterando la configuración de MySQL (/etc/my.cfg) y volver a lanzar el test de estrés.

En mi sistemas, como en los vuestros, vemos que el rendimiento de desploma bastante de ejecutar un único thread a varios thread's simultáneamente. Y es justo en estos casos donde debemos de ganar rendimiento!

Borrando tablas temporales

Una vez hayamos finalizamos el test debemos de borrar la tabla temporal que se creó para tal efecto, para que no quede ocupando espacio tontamente en el servidor.

También tras cada ejecución si los datos son alterados, deberíamos de borrar los datos de pruebas y volver a generarlos, para trabajar siempre con la misma batería de pruebas iniciales y que ésto no influya en los resultados.

shell> sysbench --test=oltp \
       --mysql-db=test --mysql-user=root --mysql-password=your_passwd \
       cleanup

La entrada Linux benchmark II la puedes leer en Puppet Linux.

Leer más

MySQL, escalando en BlackHole

En anteriores entradas ya vimos cómo montar un clúster MySQL Master-Slave y también de cómo hacerlo para un Master-Master. En ambos casos la solución era siempre la misma y se comprobó que la segunda opción (máster-máster), no era más que la primera en ambos sentidos.

Hoy vamos a ver una solución más óptima al escalado de un sistema MySQL. Esta opción que veremos suele ser más desconocida, aunque viene perfectamente implementada en el motor de base de datos y permite realizar un escalado master-multi-slave de forma sencilla.

En la mayoría de los casos los sistemas necesitan escalar para dar soporte a múltiples lecturas y no tanto en los que a escrituras se refiere. Esto significa que por cada escritura, a lo mejor tenemos, 500 lecturas. Es decir, la gente consume más de lo que aporta. Obviamente estamos hablando en términos relativos y por supuesto, cada aplicación es un mundo. Sin embargo, si vuestro caso se ajusta al aquí descrito, lo más probable es que vayáis a necesitar varios nodos de lectura. Para ello, MySQL ya aporta el escalado Máster - Slave. y podemos poner, por ejemplo, los Slave en modo sólo lectura. Ahora bien, si pensamos lo que estamos diciendo, vamos a tener un equipo que hará de Máster y del que se replicarán los datos a N Slave's. Según la forma tradicional de trabajo de MySQL, el Máster escribirá en sus tablas los datos, luego en el binary log y luego estos datos serán replicados a los equipos Slave's. Pero si queremos tener un amplio escalado, ¿por qué necesitamos escribir los datos en el Máster? Este tiempo de acceso a disco es innecesario. Si no lo vamos a emplear, lo mejor sería suprimirlo.

Para intentar dar solución a este problema, vamos a hacer uso de la engine de MySQL, BlackHole. Sí, efectivamente, si lo traducimos, agujero negro. Antes de seguir  vamos a ver cómo trabaja en un pequeño ejemplo práctico.

mysql> CREATE TABLE `test` (
    -> `id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `nombre` text NOT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=BLACKHOLE;

Ya tenemos la tabla creada. Ahora metamos algunas tuplas...

mysql> INSERT INTO test (nombre) VALUES ('javier');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (nombre) VALUES ('puppet');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (nombre) VALUES ('linux');
Query OK, 1 row affected (0.00 sec)

Las inserciones se realizaron correctamente. Vamos a ver ahora al leer qué obtenemos...

mysql> SELECT * FROM test;
Empty set (0.00 sec)

Lo ocurrido es realmente lo esperado, la engine BlackHole no guarda datos.
Pero entonces, ¿en qué nos puede ayudar BlackHole a la hora de escalar una base de datos?
Pues muy simple, esta nueva engine no escribe los datos en las tablas de MySQL, pero sí lo hace en el binary log, que es justo el log que MySQL emplea para realizar la sincronización de los datos con los esclavos. Por lo tanto, empleando BlackHole podríamos terminar con una arquitectura como la que sigue.

MySQL BackHole system escalate

En el equipo máster  que sólo se encargará de realizar las escrituras, todas las tablas serán de tipo BlackHole y tendrá como esclavos al resto de nodos, sobre los que sí existirán realmente los datos y sobre los que se realizarán todas las lecturas.

En el ejemplo anterior, las tres tuplas no se habían escrito en el máster y todo apuntaba a que se habían perdido. Ahora que la nuevo engine se ha explicado, vamos a ver como realmente las tuplas sí están escritas en los esclavos.

slave> select * from test;
+----+--------+
| id | nombre |
+----+--------+
|  1 | javier |
|  2 | puppet |
|  3 | linux  |
+----+--------+
3 rows in set (0.00 sec)

Así que ya sabéis, si realmente os interesa escalar una base de datos de un máster a varios esclavos y las escrituras las tenéis controladas, la opción de poner las tablas del maestro de tipo agujero negro es muy interesante ya que nos permitirá tener un máster más liviano y dejar el trabajo y el proceso de almacenar datos a los esclavos, que son los que realmente lo tienen que hacer.

La entrada, MySQL, BlackHole para escalar la puede leer en Puppet Linux.

Leer más

Skipping the data of table mysql.event

Recientemente realicé la actualización de los servidores de MySQL/Percona a la última versión estable en Debian. Concretamente ahora tengo la versión 5.5.30 de MySQL. Como es lógico, antes tenía un pequeño script que se encargaba de realizar periódicamente un dump de las bases de datos para poder almacenarlas en backup. Funcionaba perfectamente. Tras la actualización, descubro lo que parece un bug, y todo apunta a ello. Al tratar de hacer un dump de la base de datos mysql, obtengo un warning, no importante, pero sí molesto.

shell> mysqldump -u root -p'passwd' -B mysql > /tmp/mysql.sql
-- Warning: Skipping the data of table mysql.event.
Specify the --events option explicitly.

Y la versión actual de MySQL,

shell> mysql -V
mysql  Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1

La solución adoptada fue indicarle al mysqldump que ignore la tabla event de la base de datos mysql y con eso ya sacamos el warning.

shell> mysqldump -u root -p'passwd' --ignore-table=mysql.event -B mysql > /tmp/mysql.sql

Otra posible solución si deseamos tener copia también de esa tabla es indicar en el proceso del dump que deseamos obtener la tabla events, tal como hacía antes, por defecto. Para ello,

shell> mysqldump -u root -p'passwd' --events -B mysql > /tmp/mysql.sql

Este fallo no sólo es cosa de Debian, sino que sucede en todas las distribuciones que actualizaron la versión de MySQL y está considerado un bug. Se puede leer más acerca de él en bugzilla.redhat.com y en bugs.mysql.com.

La entrada Skipping the data of table mysql.event la puedes leer en Puppet Linux.

Leer más

MySQL, unir filas duplicadas

Hoy os voy a enseñar un pequeño truco en MySQL que permite borrar datos duplicados de una tabla forma sencilla. Para hacerlo, simplemente vamos a recurrir a una "índice temporal" sobre una tabla, que hará justamente el trabajo que deseamos. Para que se entienda mucho mejor y más rápido, lo veremos con una prueba de concepto. Partimos de la siguiente tabla test.

mysql> CREATE TABLE `test` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `a` int(11),
       `b` int(11),
       `c` int(11),
       `time` timestamp,
       PRIMARY KEY (`id`) );

El esquema es muy sencillo, pero para la demostración nos llegará. Ahora vamos a insertar unos cuentos valores, entre ellos, alguno repetido.

mysql> INSERT INTO test (a,b,c) VALUES (1,2,3),
       (1,2,3),
       (2,3,4),
       (3,4,5);
mysql> INSERT INTO test (a,b,c) VALUES (1,2,3);
mysql> SELECT * FROM test;
+----+------+------+------+---------------------+
| id | a    | b    | c    | time                |
+----+------+------+------+---------------------+
|  1 |    1 |    2 |    3 | 2013-03-04 19:41:20 |
|  2 |    1 |    2 |    3 | 2013-03-04 19:41:20 |
|  3 |    2 |    3 |    4 | 2013-03-04 19:41:20 |
|  4 |    3 |    4 |    5 | 2013-03-04 19:41:20 |
|  5 |    1 |    2 |    3 | 2013-03-04 19:41:42 |
+----+------+------+------+---------------------+
5 rows in set (0.00 sec)

Como podemos observar tenemos 5 filas con datos diferentes (el valor id, autoincremental, difiere). También para comprobar que funciona, hemos creado una nueva fila un poco más tarde que las demás, para así ver que el tiempo no influye en el resultado.

Nuestra intención es por lo tanto que aquellas filas que tengan los valores de a y b comunes se junten en una única fila. Aunque en este caso los valores son numéricos, sería aplicable a cualquier otro tipo soportado por MySQL. Para lograrlo, simplemente,

mysql> ALTER IGNORE TABLE test ADD UNIQUE INDEX(a,b);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 2  Warnings: 0

Y vemos el resultado,

mysql> SELECT * FROM test;
+----+------+------+------+---------------------+
| id | a    | b    | c    | time                |
+----+------+------+------+---------------------+
|  1 |    1 |    2 |    3 | 2013-03-04 19:41:20 |
|  3 |    2 |    3 |    4 | 2013-03-04 19:41:20 |
|  4 |    3 |    4 |    5 | 2013-03-04 19:41:20 |
+----+------+------+------+---------------------+
3 rows in set (0.00 sec)

Observamos por lo tanto que algunos valores que había en la tabla han desaparecido, concretamente los que se vieron afectados por la creación de UNIQUE INDEX de las columnas a y b. En caso de que no queramos que este índice quede, tendremos que borrarlo. Muy sencillo y puede que en algunos casos necesario.

Nota: En caso de que al ejecutar la sentencia de creación de nuevo índice os de el siguiente fallo,

mysql> ALTER IGNORE TABLE  test ADD UNIQUE INDEX(a,b);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'a'

es por que la tabla está en InnoDB y no en MyISAM. Así que antes de ejecutar dicha sentencia la cambiamos de engine, la modificamos y la volvemos a poner como nos interesa.

mysql> show create table test;
+-------+------------------------------+
| Table | Create Table                 |
+-------+------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `time` timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test ENGINE MyISAM;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> ALTER IGNORE TABLE test ADD UNIQUE INDEX(a,b);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 2  Warnings: 0

mysql> ALTER TABLE test ENGINE InnoDB;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

La entrada MySQL, unir filas duplicadas la puedes leer en Puppet Linux.

Leer más

Lock wait timeout exceeded in zabbix

Zabbix es un muy buen sistema de monitorización, sin embargo tiene varios problemas ampliamente comentados. Uno de los principales es el empleo intensivo que hace de la base de datos. Por defecto, el servidor Zabbix, así como el interfaz gráfico, realizan un gran número de operaciones contra la base de datos, lo que hace que ésta necesite una gran cantidad de RAM, así como un disco con un muy buen rendimiento. Esto es necesario y se nota especialmente cuando el número de equipos a controlar es alto (más de 5k hosts).

Otro de los problemas que presenta zabbix, bajo mi punto de vista es que no emplea valores autoincrementales. Si miramos las tablas de la base de datos, veremos que todas tienen un id numérico (eventid, historyid, itemid, etc.). Estos valores son numéricos y contiguos, es decir, incrementales, pero sin embargo es el zabbix-server el que se encarga de calcularlos y no los deja en manos del motor de base de datos. Esto es así para ofrecer compatibilidad con SQLite, pero el problema que presenta es todo lo contrario en bases de datos tipo MySQL o Postgress con una carga importante.

Para saber cual es el próximo id a insertar, existe la tabla ids que contiene sobre 40 filas con los ids de cada una de las tablas y es el propio servidor el que calcula el siguiente id y actualiza dicha tabla. Hablando con datos exactos, lanza consultas de este tipo: "update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid'". Ahora, si la carga del sistema es elevada, cientos o incluso miles de vps (valores por segundo), entonces puede que tengamos en los log's los siguientes mensajes, y además muy abundantes.

...
23966:20120412:234746.094 [Z3005] query failed: [1205] Lock wait timeout exceeded; try restarting transaction [update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid']
23942:20120412:234750.128 slow query: 50.982490 sec, "update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid'"
zabbix_server [23942]: ERROR [file:db.c,line:1582] Something impossible has just happened.
...
23969:20120412:234752.153 [Z3005] query failed: [1205] Lock wait timeout exceeded; try restarting transaction [update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid']
23969:20120412:234752.153 slow query: 50.206625 sec, "update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid'"
zabbix_server [23969]: ERROR [file:db.c,line:1582] Something impossible has just happened.

El problema, "Lock wait timeout exceeded; try restarting transaction" en realidad no tiene nada que ver con Zabbix, sino que es un fallo de MySQL cuando dos procesos intentan acceder a la misma fila de una tabla y no pueden. Si hay un gran número de eventos a escribir, estos mensajes aparecen con más frecuencia.

Navegando por la red encontré una posible solución, que la verdad es muy sencilla. La solución es pasar la tabla ids de InnoDB a MyISAM ya que resulta que para este tipo de casos la segunda es más rápida que la primera.

mysql> ALTER TABLE ids ENGINE = MyISAM;

Tras realizar este cambio, el número de mensajes como el descrito, sino desaparecen cuando menos sí deberían de descender en frecuencia.

Leer más

MySQL: Tipos de datos

Nunca os ha pasado que tenéis que crear algún campo en una base de datos MySQL y no sabéis qué tipo de datos ponerle. Aquí os dejo una pequeña chuleta con todos los tipos de datos que MySQL soporta y que será muy útil a la hora de definir mejor los campos.

Ahorrarás espacio y ayudarás al motor de base de datos a ir más fluido.

Más información: MySQL.com.

Tipo	Descripción
CHAR	Almacenan un carácter o una cadena de caracteres de 0 hasta 255
VARCHAR	Almacenan una cadena de caracteres variables de 0 a 255
TINYINT	Almacena un valor entero pequeño de 0 a 255 o de -128 a 127
BIT BOOL BOOLEAN	Son sinónimos de TINYINT(3)
SMALLINT	Almacena un entero corto entre -32768 y 32767 En caso de no tener un signo de 0 a 65535
MEDIUMINT	Almacena un entero de tamaño medio entre -83888608 y 8388607 o entre 0 y 16777215 sin signo.
INT INTEGER	Contiene un entero de tamaño normal entre -2147483648 y 2147483647 o entre 0 y 4294967295 sin signo
BIGINT	Contiene un entero grande entre -9223372036854775808 y 922337203685477807 con signo o de 0 a 18446744073709551615 sin signo
FLOAT	Guarda un número de coma flotante Opcionalmente puede acompañarse (al tipo flota sin los paréntesis) de la palabra PRECISION que le indica la precisión decimal que se desea, que puede llegar hasta 24 para precisión sencilla y de 25 a 53 para una precisión doble En caso de especificarse el modificador UNSIGNED los valores negativos no son permitidos.
DOUBLE	Contiene un número de coma flotante de precisión normal Si se define UNSIGNED, los valores negativos no son permitidos.
DOUBLE PRECISION REAL	Sinónimos de DOUBLE (10)
DECIMAL	Guarda un número de coma flotante sin empaquetar, lo que implica que da número es un caracter (CHAR)
DEC NUMERIC FIXED	Son todos sinónimos de DECIMAL
DATE	Almacena un valor de fecha Los rangos permitidos son desde '1000-01-01' y '9999-12-31' El formato de fecha presentado es: 'AAAA-MM-DD'
DATETIME	Almacena un valor de fecha +  hora Los rangos permitidos, desde '1000-01-01 00:00:00' hasta '9999-12-31 23:59:59' El formato es del tipo 'AAAA-MM-DD HH:MM:SS'
TIMESTAMP	Es un valor temporal que va desde '1970-01-01 00:00:00' hasta el año 2037
TIME	Almacena valores de tipo hora, pero entre dos eventos, de ahí que pueda superar el rango de las 24 horas y ser incluso negativo Los rangos permitidos, '-838:59:59' a '838:59:59'
TINYBLOB TINYTEXT	Almacena valores de tipo BLOB o TEXT con una longitud de hasta 255 caracteres
BLOX TEXT	Almacena valores de tipo BLOB o TEXT con una longitud de hasta 65535 caracteres
MEDIUMBLOB MEDIUMTEXT	Almacena valores de tipo BLOB o TEXT con una longitud de hasta 16777215 caracteres
LONGBLOB LONGTEXT	Almacena valores de tipo BLOB o TEXT con una longitud de hasta 4294967298 caracteres
ENUM	Almacena un enumerado con un máximo de 65535 valores diferentes
SET	Almacena un conjunto con un máximo de miembros de 64

Leer más

MySQL version info

MySQL o Percona, del que ya hablamos largo y tendido en este blog permite obtener mucha información de la versión y las opciones de compilación con las que fue creado el binario.

Es muy raro que se compile el paquete propiamente dicho (configure & make & make install), sino que se suelen obtener paquetes de los repositorios oficiales de la distribución que se use. Dichos paquetes fueron compilados por alguien y las opciones con las que fueron creados suelen ser,

• version: Es la versión exacta del servidor MySQL. Concatenada a la versión puede estar alguna característica con la que fue compilado. Éstas pueden ser:
• log: Tiene soporte para logs.
• debug: Está compilado con soporte para depuración.
• version_comment: Contiene el comentario que se haya especificado durante la compilación. Si es un paquete propio, aquí se puede poner la información del creador. Se añade esta información con la opciones --with-comment.
• version_compile_machine: Arquitectura para la cual el servidor fue compilado. Generalmente i686 o x86_64.
• version_compile_os: Sistema operativo para el que fue compilado.
  Para sistemas GNU/Linux las opciones que típicamente hay son: "Linux", "unknown-linux-gnu", "pc-linux-gnu".

mysql> show variables like 'version%';
+-------------------------+------------------------------------+
| Variable_name           | Value                              |
+-------------------------+------------------------------------+
| version                 | 5.5.27-28.1-log                    |
| version_comment         | Percona Server (GPL), Release 28.1 |
| version_compile_machine | i686                               |
| version_compile_os      | Linux                              |
+-------------------------+------------------------------------+
4 rows in set (0.00 sec)

Leer más

MySQL, sustituir parte de una cadena

Hoy tuve la imperiosa necesidad de hacer un UPDATE sobre una tabla un poco grande. El UPDATE era algo complicado y afectaba a muchas entradas a la vez, por lo que si salía mal, tenía la posibilidad de armar una buena en todos los datos. La sentencia en cuestión consistía en cambiar una parte del nombre de un campo por otro nombre, pero todo ello sin alterar el correcto funcionamiento de la tabla. Es decir, partimos de algo similar a lo siguiente:

mysql> SELECT * FROM groups;
+---------+-----------------------------+----------+
| groupid | name                        | internal |
+---------+-----------------------------+----------+
|      26 | Pais::Alemania              |        0 |
|      27 | Pais::Andorra               |        0 |
|      28 | Pais::Arabia Saudita        |        0 |
|      29 | Pais::Argentina             |        0 |
|      30 | Pais::Australia             |        0 |
|      31 | Pais::Austria               |        0 |
|      32 | Pais::Azerbaiyan            |        0 |
|      33 | Pais::Bahrein               |        0 |
|      34 | Pais::Belgica               |        0 |
|      35 | Pais::ZZ-Bosnia-herzegovina |        0 |
|      36 | Pais::Brasil                |        0 |
|      37 | Pais::Bulgaria              |        0 |
|      38 | Pais::Canada                |        0 |
|      39 | Pais::Chile                 |        0 |
|      40 | Pais::China                 |        0 |
|      41 | Pais::Chipre                |        0 |
|      42 | Pais::Colombia              |        0 |
|      43 | Pais::Congo                 |        0 |
|      44 | Pais::Corea del Sur         |        0 |
|      45 | Pais::Costa rica            |        0 |
|      46 | Pais::Croacia               |        0 |
|      47 | Pais::Dinamarca             |        0 |
|      48 | Pais::EE.UU.                |        0 |
...

Y la idea es terminar con algo tal que así, cambiando la palabra País por Z.

Leer más

Paginar salida de MySQL

Este es un pequeño truco que se puede emplear para obtener una salida mucho más lógica de una consulta en MySQL. Por defecto, la salida de una consulta se envía a stdout, por lo que si esta tiene un número muy elevado de tuplas, es complicado verla y seguirla. Para evitarlo qué mejor que paginar. MySQL permite paginar las salidas al más puro estilo GNU/Linux (diferencia entre cat FILE y more FILE).

Para habilitar el paginado en las salidas de MySQL se emplea el comando pager y éste admite como parámetros el programa GNU/Linux que deseemos emplear para paginar (more o less, por ejemplo). Para emplear simplemente,

mysql> pager more

Con lo que obtendremos una paginación al más puro estilo more o sino también,

mysql> pager less

Muy útil si la salida de la consulta es más grande que la pantalla, que permite navegar vertical y horizontalmente por los resultados, facilitando la consulta y explotación de los datos.

A mayores, también permite realizar salidas más útiles, como por ejemplo,

mysql> pager md5sum

mysql> select * from users;
e9b09f919e6abf68b46f84f0bedc2e72  -
71 rows in set (0.00 sec)

Con lo que obtenemos un md5 de la salida. La próxima vez que se ejecute si este número cambia, significará que hubo cambios en los datos de la consulta, sino, es que todo está igual.

Y pager también permite enviar los resultados por correo, empleando el comando del sistema mail.

mysql> pager mail -s "query mysql" user@domain.com

Cuando deseemos volver al estilo por defecto de MySQL simplemente,

mysql> nopager

Nota: pager permite realizar salidas también mucho más espectaculares  al enviar todos los datos de las consultas como input del programa especificado,

mysql> pager cat > /tmp/output.txt
mysql> pager less -n -i -S -F -X
mysql> pager cat \ 
 -> | tee /tmp/res.txt \
 -> | tee /tmp/res2.txt | less -n -i -S

Más info en: MySQL 5.5

Leer más

MySQL, concat values

Hay veces que por algún motivo necesitas a un valor que ya tienes en la base de datos, añadirle concatenarle otro valor. Si es este el caso, MySQL permite hacerlo de forma muy simple e inmediata, sin necesidad de pasar por código externo. Partimos de los siguientes datos,

mysql> select * from scripts;
+----------+---------+----------------------------+
| scriptid | name    | command                    |
+----------+---------+----------------------------+
|        1 | ES-Ping | /bin/ping -c 3 {HOST.CONN} |
+----------+---------+----------------------------+
1 row in set (0.00 sec)

Y nos interesa, poner en la columna command, antes del valor actual un 'timeout 30', para hacerlo, qué mejor que concatenar el valor que deseamos al actual que hay. tal como sigue,

mysql> update scripts set command=concat('timeout 30 ',command);

Tras ello, vemos que el cambio ha tenido el efecto deseado.

mysql> select * from scripts;
+----------+---------+---------------------------------------+
| scriptid | name    | command                               |
+----------+---------+---------------------------------------+
|        1 | ES-Ping | timeout 30 /bin/ping -c 3 {HOST.CONN} |
+----------+---------+---------------------------------------+
1 row in set (0.00 sec)

Si nos interesa, concat también lo puede hacer al revés, primero el valor que hay y luego el nuevo,

mysql> update scripts set command=concat(command, 'timeout 30 ');

Más info en mysql-string-functions.

Leer más