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

Optimizar base de datos MySQL

MySQL es el motor de base de datos por defecto de numerosas aplicaciones, entre ellas Bacula. Y como otros muchos motores de bases de datos, tiene su parte buena y su parte mala. La parte buena es la flexibilidad y potencia que nos ofrece. La parte mala es que en aplicaciones muy dinámicas, hablando de datos, MySQL tiende a fragmentar las tablas y por lo tanto, espacio en disco ocupado y rapidez de acceso a datos aumentan. Aunque este post lo relacionamos directamente con Bacula, este pequeño truco sirve para cualquier otras aplicación que emplee MySQL. Lo que vamos a explicar aquí, es como optimizar un poco las tablas, es decir, que tras realizar muchas escrituras y borrados y también updates, hacer que la estructura de la tabla vuelva a ser la óptima. Existen dos formas, la primera es la clausulo OPTIMIZE de mysql, y la segunda, un pequeño script, que aparece en la documentación de bacula. Ambas estrategias van a realizar algo parecido, desfragmentar la tabla, repararla y ordenar los índices. Vamos a ver cómo funcionan.

• OPTIMIZE
  
  Es una clausula de MySQL y sobre tablas MyISAM da muy buenos resultados. Por lo tanto, para aplicarlo a bacula, simplemente tendremos que ejecutar dicha clausula sobre todas las tablas, una a una. 

  mysql> OPTIMIZE LOCAL TABLE bacula.BaseFiles;
  +------------------+----------+----------+----------+
  | Table            | Op       | Msg_type | Msg_text |
  +------------------+----------+----------+----------+
  | bacula.BaseFiles | optimize | status   | OK       |
  +------------------+----------+----------+----------+
  1 row in set (0.00 sec)
  
  mysql> OPTIMIZE LOCAL TABLE bacula.CDImages;
  +-----------------+----------+----------+----------+
  | Table           | Op       | Msg_type | Msg_text |
  +-----------------+----------+----------+----------+
  | bacula.CDImages | optimize | status   | OK       |
  +-----------------+----------+----------+----------+
  1 row in set (0.00 sec)
  

  Según el grado de fragmentación que presente, el proceso puede llevar más o menos tiempo.
• script
  
  Esta forma es algo más elaborada y requiere escribir un poco de código, pero el resultado es mucho más óptimo y queda más logrado. Realizamos el siguiente script,

  #!/bin/bash
  
  inMB=$(du -ms $DIR | awk '{print $1}')
  echo "DB Bacula size: $inMB MB"
  echo "Optimize MySQL Database (I): dump"
  mysqldump -f -uroot -p --opt bacula > /tmp/bacula.sql
  echo "Optimize MySQL Database (II): write"
  mysql -u root -p bacula < /tmp/bacula.sql
  echo "Delete security copy"
  rm -f /tmp/bacula.sql
  newInMB=$(du -ms $DIR | awk '{print $1}')
  echo "New DB Bacula size: $newInMB MB"
  RATIO=`echo "scale=2; ($inMB-$newInMB)/$inMB*100" | bc`
  echo "$RATIO % compress ratio"
  

  Antes de ejecutarlo, intentamos comprender un poco lo que hace, que no es más que realizar un dump de la base de datos bacula para luego volver a insertarla. Al insertarla, genera el schema y borra los datos actuales para volver a meterlos, sacando toda fragmentación y ordenando los índices.
  Un ejemplo de ejecución sería,
  

  DB Bacula size: 1577 MB
  Optimize MySQL Database (I): dump
  Optimize MySQL Database (II): write
  Delete security copy
  New DB Bacula size: 897 MB
  43.00% compress ratio
  

Cualquier de las dos opciones es buena, y es aconsejable según el uso que se haga de bacula ejecutarla cada 3 o 6 meses. Si el equipo realiza muchas copias de seguridad, ejecutarlo una vez al mes, tampoco sobra. Realmente, lo importante es el porcentaje de espacio que se le gana. En esta ejecución, fue de un 43%, efectivamente lo necesitaba. Si la mejora es de un 3%, obviamente, no ;-)

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

Zabbix + MySQL + CONSTRAINT FOREIGN KEY

La mayoría de la gente que usa habitualmente Zabbix sabe el problema que éste tiene con la base de datos. Ya bien sea MySQL o cualquier otro motor. Una de las formas de evitar un acceso tan abusivo a los datos almacenados es dehabilitar el Housekeeping, o dicho de otra forma, deshabilitar el purgado de datos. Si hacemos esto, los datos almacenados en la base de datos nunca se borrarán y para evitar que crezcan sin control, la mejor solución es sin duda emplear un particionado de tablas.

Particionando las tablas conseguimos borrar de forma muy sencilla una gran cantidad de datos (partición == fichero en disco). Puesto que prácticamente todas las tablas dignas de ser particionadas en Zabbix tienen un campo llamado clock, de tipo unixtime, lo que facilita el particionado y el borrado.

En Zabbix 1.8, particionar las tablas y que todo funcionase no conllevaba problema alguno. Sin embargo con el upgrade a la 2.0 la cosa se complica. Si miramos un poco el schema de la base de datos podremos ver cosas como esta,

ALTER TABLE `screens` ADD CONSTRAINT `c_screens_1` FOREIGN KEY (`templateid`)...
ALTER TABLE `media` ADD CONSTRAINT `c_media_1` FOREIGN KEY (`userid`)...
ALTER TABLE `rights` ADD CONSTRAINT `c_rights_2` FOREIGN KEY (`id`)...
...

Estas reglas realizan una restricción de clave foránea, o dicho de forma más comprensible, un campo de una tabla influye directamente en la otra tabla. Por lo tanto, si borramos un valor de una de las tablas, este borrado se tiene que propagar al resto de tablas afectadas.

En la versión 2.0 de Zabbix este tipo de claves han aparecido y lo que en realidad es una facility muy interesante, en entornos de trabajo grandes puede ser un problema. Imaginemos el siguiente escenario en el que tenemos la tabla events está particionado por fecha (semana | mes) y que queremos comenzar a emplear las alertas para determinados eventos. Puesto que la tabla tiene creada la siguiente clave,

ALTER TABLE `alerts` ADD CONSTRAINT `c_alerts_2` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE;

Cuando un nuevo evento intenta insertarse en la tabla alerts, tenemos el siguiente fallo,

[Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`tiendas_produccion`.`alerts`, CONSTRAINT `c_alerts_2` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE) [insert into alerts (alertid,actionid,eventid,userid,clock,mediatypeid,sendto,subject,message,status,alerttype,esc_step) values (9754,2,327497453,8,1362020524,1,'javier@domain.com','PROBLEM: Zabbix Agent fail','Trigger: Zabbix Agent fail
Trigger status: PROBLEM
Trigger severity: Information

Este fallo realmente es provocado por MySQL, que no soporta las claves "CONSTRAINT FOREIGN KEY" contra tablas particionadas. Si se piensa fríamente, tiene lógica ya que si se borra una partición, el motor de base de datos no puede controlar la clave. Esto pasará con cualquier tabla, pero en lo que nos afecta a nosotros es para Zabbix, en su versión 2.0.

Entonces, ¿cómo lo solucionamos?

Pues bien, como sabemos cual es el problema (la clave foránea) y como tenemos controlado el crecimiento de nuestras tablas con particiones y no nos interesa la funcionalidad que ofrecen estas claves (no las podemos usar), la mejor solución es sin duda, borrar las claves que no se usan. Así que vamos allá!

mysql> ALTER TABLE events DROP FOREIGN KEY c_alerts_2;
ERROR 1506 (HY000): Foreign key clause is not yet supported in conjunction with partitioning

Justamente queremos borrar la clave y nos da un error de que no se puede hacer, justamente por tener la tabla particionada. No pasa nada, no hay nada que root no sea capaz de hacer. Para ello vamos a optar por deshabilitar las claves foráneas,

mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Y lo volvemos a intentar,

mysql> ALTER TABLE alerts DROP FOREIGN KEY c_alerts_2;

Esta vez ya sin problemas lo pudo ejecutar y las alertas ya comenzarán a funcionar sin problemas en Zabbix.
La entrada Zabbix + MySQL + CONSTRAINT FOREIGN KEY 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 Quick Status

Si deseas obtener información sencilla pero útil de tu base de datos, únicamente ejecuta \s, con la que obtendrás una pequeña salida del estado del servidor.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.25a, for Linux (i686) using readline 5.1

Connection id:      464882
Current database:    test
Current user:      root@localhost
SSL:       Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:     ;
Server version:      5.5.25a-27.1-log Percona Server (GPL), Release 27.1
Protocol version:    10
Connection:      Localhost via UNIX socket
Insert id:      6
Server characterset: latin1
Db     characterset: latin1
Client characterset: latin1
Conn.  characterset: latin1
UNIX socket:      /var/run/mysqld/mysqld.sock
Uptime:       71 days 19 hours 46 min 2 sec

Threads: 10
Questions: 67830202
Slow queries: 30
Opens: 10555
Flush tables: 1
Open tables: 400
Queries per second avg: 10.930
--------------

Leer más

MySQL, ID de posición en SELECT

Muchas veces cuando hacemos una consulta SQL nos interesaría poder tener un ID al lado para poder posicionarnos o referirnos a una línea del resultado. Dicho de otra forma, tener un campo autoincremental que dependiese de la consulta y no de la propia tabla. Gracias a ese campo nos podríamos referir a los datos de la fila 4 y sería más rápido para todos poder referenciarlos.

mysql> SELECT alias,name FROM users;
+----------+---------+
| alias    | name    |
+----------+---------+
| Admin    | Zabbix  |
| guest    | Default |
| javier   | Javier  |
| adm      | Adm     |
| alberto  | Alberto |
+----------+---------+

Existe un pequeño truco en MySQL que nos permite hacer una consulta de ese tipo y obtener la fila extra que realmente interesa. A continuación lo vemos.

mysql> SET @num=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @num:=@num+1 AS pos,alias,name from users;
+-----+----------+---------+
| pos | alias    | name    |
+-----+----------+---------+
|   1 | Admin    | Zabbix  |
|   2 | guest    | Default |
|   3 | javier   | Javier  |
|   4 | adm      | Adm     |
|   5 | alberto  | Alberto |
+-----+----------+---------+

Como bien podéis observar lo que hacemos es crear en tiempo de ejecución una variable, num, inicializada a  cero (o al valor que deseemos) y que luego la colocamos en el SELECT, incrementando el valor de ésta en cada iteración resultante. Así de forma muy sencilla tenemos el ID que estábamos buscando y que nos indica la posición de cada una de las filas.

Leer más

MySQL random value

Si alguna vez os habéis preguntado cómo se obtiene un valor aleatorio de una tabla, a continuación podéis ver cómo hacerlo.

Partimos del siguiente esquema de tabla con valores,

CREATE TABLE temp (
   id int(11) NOT NULL auto_increment,
   value int,
   PRIMARY KEY (id)
);

Para obtener un registro aleatorio de los que hay, simplemente debemos realizar un ORDER BY RAND,

mysql> SELECT * FROM temp ORDER BY RAND() LIMIT 1;

Leer más