Posts tagged with “mysql”


Sun 28 Aug

Indexación por rangos en Sphinx

Como vimos en el post anterior, es posible lanzar una query que lea todos los datos que deseamos indexar. Esto no es problema si los datos son pocos, por ejemplo en el caso de employeesdb estamos hablando de 300.024 registros, que son más bien pocos. Cuando ya hablamos de varios gigas de datos y millones de filas las cosas se complican. Los índices de Sphinx hay que recrearlos cada cierto tiempo para que las búsquedas de nuestra web nos den datos lo más actuales posibles. Por lo tanto, lanzar una query que en definitiva es un escaneo completo de tabla, cada 5 minutos, con millones de registros, será un problema de rendimiento importante. Existen varias soluciones para aliviar un poco esta carga. La primera y más obvia que a mi me viene a la cabeza es usar un mySQL slave que solo usaremos para este actualizar los índices de Sphinx. Podemos incluso, si hay RAM suficiente, crear tablas con engine Memory y aumentar aún más el rendimiento. Pero como esto no son tutoriales sobre MySQL, si no sobre Sphinx, hablaremos una segunda posibilidad. Hacer escaneos por rangos.

En resumen, lo que haremos es, en lugar de leer 20 millones de registros de golpe, lo haremos en tandas que nosotros mismos definiremos. Para ello, el primer valor a definir será sql_range_step. Esta directiva tendrá por defecto un valor de 1000, el cual, para nuestro hipotético caso es un valor que se nos queda corto. Otra directiva que nos ayudará a aliviar la carga de nuestra base de datos será sql_ranged_throttle que indicará cuantos milisegundos esperar entre cada escaneo por rango.

Con estos datos ya podemos definir como queremos que sea la obtención de los datos, por lo que nos ponemos manos a la obra.

1- En primer lugar Sphinx debe saber cual es el valor mínimo y máximo de filas que tendrá que obtener. Para esto, lo recomendable es hacer uso den la base de datos de una columna tipo auto-incremental. Hay que tener en cuenta que es posible que la tabla tenga pequeños vacíos o huecos dentro de dicho autoincremental, debido al borrado de filas. Por lo que, si nos devuelve 1 - 10.000 no tienen porque ser 10.000 registros.

sql_query_range = SELECT MIN(emp_no), MAX(emp_no) from employees

2- Sphinx leerá la query anterior y guardará en variables internas los dos valores devueltos por la query. Dentro de cada iteración de la búsqueda por rangos, Sphinx definirá las variables $start y $end. Dichas variables se actualizarán según vayamos avanzando por la tabla. De esta forma, la query que vimos en el post anterior debería quedar así:

sql_query               = \
        select employees.emp_no, employees.first_name, employees.last_name, 
UNIX_TIMESTAMP(employees.hire_date) as hire_ts, salaries.salary, departments.dept_name
 from employees LEFT JOIN salaries ON employees.emp_no = salaries.emp_no LEFT JOIN 
dept_emp ON salaries.emp_no = dept_emp.emp_no LEFT JOIN departments ON 
dept_emp.dept_no = departments.dept_no WHERE emp_no.employees BETWEEN $start AND $end
 GROUP BY employees.emp_no

Si nos fijamos en la clausula WHERE, Sphinx ejecutará la query las veces que sean necesarias hasta recorrer todas las filas de la tabla, cambiando en cada iteración los valores de inicio $start. Un procedimiento fácil y eficaz.

3- Como tenemos 300.000 registros configuraremos Sphinx para que lo haga en grupos de 10.000 registros y con una espera de 1 segundo entre cada iteración.

sql_range_step = 10000
sql_ranged_throttle = 1000

Por lo que, la sección "source" de nuestra configuración quedará así:

source perconaserver1 
{
    type                    = mysql
    sql_host                = 192.168.100.10 
    sql_user                = sphinx
    sql_pass                = sph1nX
    sql_db                  = employees
        sql_query_range                         = SELECT MIN(emp_no), MAX(emp_no) from employees
        sql_range_step                          = 10000
        sql_ranged_throttle                     = 1000
    sql_query               = \
        select employees.emp_no, employees.first_name, employees.last_name,
 UNIX_TIMESTAMP(employees.hire_date) as hire_ts, salaries.salary, departments.dept_name
 from employees LEFT JOIN salaries ON employees.emp_no = salaries.emp_no LEFT JOIN
 dept_emp ON salaries.emp_no = dept_emp.emp_no LEFT JOIN departments ON 
dept_emp.dept_no = departments.dept_no WHERE employees.emp_no BETWEEN $start AND $end 
GROUP BY employees.emp_no
    sql_attr_uint           = salary
    sql_attr_timestamp      = hire_ts
}
index employees 
{
    source          = perconaserver1
    path            = /var/lib/sphinxsearch/data/employees
    min_word_len        = 1
    charset_type        = sbcs
}

Lanzamos de nuevo el indexer para que relea los datos de las tablas y las agregamos a nuestro índice:

root@sphinx1:~# indexer --all --rotate
Sphinx 0.9.9-release (r2117)
Copyright (c) 2001-2009, Andrew Aksyonoff

using config file '/etc/sphinxsearch/sphinx.conf'...
indexing index 'employees'...
collected 300024 docs, 7.0 MB
sorted 0.9 Mhits, 100.0% done
total 300024 docs, 7036568 bytes
total 59.928 sec, 117416 bytes/sec, 5006.37 docs/sec
total 3 reads, 0.004 sec, 3202.0 kb/call avg, 1.3 msec/call avg
total 22 writes, 0.029 sec, 1000.2 kb/call avg, 1.3 msec/call avg
rotating indices: succesfully sent SIGHUP to searchd (pid=2728).

Como podemos comprobar, el proceso ha tardado un poco más que de la forma normal (o conocida como "a lo bruto"). Pero la carga de nuestra base de datos ha sido menor y por lo tanto el impacto contra la experiencia web de nuestros usuarios no se ha visto afectada.

1 Comment · Tags: ,

Fri 26 Aug

Búsquedas FULL TEXT con Sphinx y MySQL

Hasta que tengamos en las manos la versión 5.6 de MySQL, si queremos hacer uso de los índices FULL TEXT solo podemos guardas nuestros datos en tablas MyISAM. Por lo tanto, tenemos que dejar de lado consistencia de datos, rápido chequeo de tablas, transacciones, etc. Y aunque realmente no necesitemos todas esas funcionalidades y podamos vivir solo con MyISAM, llegará un momento en el que los índices sean tan grandes que hacer uso de búsquedas de texto contra MySQL se terminará volviendo un cuello de botella. No nos vamos a engañar, el rendimiento de los FULL TEXT en MySQL no es bueno :)

Para ayudarnos en las búsquedas de texto dentro de nuestra aplicación web vamos a aprender a usar, a nivel básico, el servicio de búsquedas FULL TEXT Sphinx. Este servicio nos proveerá de un servicio de búsquedas rápido y escalable, independientemente del origen de los datos que pueden ser:

  • MySQL
  • PostgreSQL
  • Oracle
  • Ficheros XML
  • etc.

Según los datos de la propia página de Sphinx, existen instalaciones que responden 200.000.000 de consultas al día con solo 15 servidores, lo que da un ejemplo de su potencia.

En una serie de posts iremos viendo como implementar y hacer uso de este sistema de búsquedas. Para ello, en un primer lugar tendremos dos máquinas con estos datos:

  • perconaserver1 (192.168.100.10) con la base de datos employees en InnoDB. PerconaServer 5.5
  • sphinx1 (192.168.100.1). Sphinx 0.99

Lo que vamos a terminar montando es un sistema de búsquedas mediante índices FULL TEXT que nos permita búscar en la base de datos de empleados, ya sea por nombre, apellido, departamento, fecha de contratación y todo lo que se nos ocurra. Todo esto sin necesidad de acceder a MySQL y con un incremento de velocidad realmente impresionante.

Doy por supuesto que todos sabemos instalar MySQL, crear usuarios e importar bases de datos. La base de datos que importaremos se puede descargar desde https://launchpad.net/test-db

Por lo tanto, comenzaremos con la configuración de Sphinx:

apt-get install sphinxsearch

El fichero de configuración se queda en /etc/sphinxsearch/sphinx.conf. El que viene por defecto tiene muchos comentarios, por lo que podemos usarlo como guia de referencia rápida. Para nuestro caso muchas de las opciones que vienen nos sobran, por lo que vamos a renombrarlo a sphinx.conf.old y creamos uno nuevo vacio.

El fichero de configuración se divide en secciones. Básicamente tenemos que indicarle de donde vamos a coger los datos a indexar, nombre y configuraciones de nuestro índice y servicio de búsqueda (searchd). Comenzamos.

source perconaserver1 
{
    type                    = mysql
    sql_host                = 192.168.100.10 
    sql_user                = sphinx
    sql_pass                = sph1nX
    sql_db                  = employees
    sql_query               = \
        select employees.emp_no, employees.first_name, employees.last_name, 
UNIX_TIMESTAMP(employees.hire_date) as hire_ts, salaries.salary, departments.dept_name
 from employees LEFT JOIN salaries ON employees.emp_no = salaries.emp_no LEFT JOIN 
dept_emp ON salaries.emp_no = dept_emp.emp_no LEFT JOIN departments ON 
dept_emp.dept_no = departments.dept_no group by employees.emp_no
    sql_attr_uint           = salary
    sql_attr_timestamp      = hire_ts
}

Aquí indicamos nuestro primer origen de datos, que se llamará percona1. Como podemos ver, será de tipo MySQL y le indicamos IP, usuario, contraseña y base de datos a la cual conectarse. Para recoger los datos le indicamos una query a ejecutar, que básicamente lo que hace es recoger todos los datos del empleado junto con su salario y nombre del departamento en el que trabaja. Aquí se puede escribir la query que cada uno desee. Los datos que devuelva la query será los que indexará Sphinx.

A la hora de crear la query solamente hay que tener en cuenta una cosa. El primer valor que devuelva nuestra query debe ser de tipo UNIQUE, por lo que generalmente se tratará de la Primary Key. En este caso, el campo UNIQUE será el número único identificador del trabajador, y por lo tanto, una Primary Key.

En las dos últimas líneas estamos creando dos atributos que podremos usar para afinar nuestras búsquedas. En este caso creamos un atributo de tipo "Unsigned Int" donde guardaremos el salarios y otro de tipo "Timestamp" donde guardaremos la fecha de contratación. Estos dos atributos se usarán para delimintar los resultados. Por ejemplo, podemos decir que queremos todos los empleados que se llamen "Juan" y que cobren más de 100.000. "Juan" será una búsqueda Full Text, mientras que el atributo "salary" se usará para mostrar únicamente aquellos "Juan" que cobren más de dicha cifra. Todas las demás columnas que no definamos como atributo se guardarán como índices FULL TEXT. En este caso, todo lo que no sea salario o fecha de contratación será un FULL TEXT.

index employees 
{
    source          = perconaserver1
    path            = /var/lib/sphinxsearch/data/employees
    min_word_len        = 1
    charset_type        = sbcs
}

Aquí indicamos la creación de un índice con nombre "employees" cuyo origen de datos está en perconaserver1. A parte de eso, indicamos la ruta donde se guardarán los ficheros, el tamaño mínimo de las palabras a indexar y el tipo de charset utilizado.

searchd
{
    log                 = /var/log/sphinxsearch/searchd.log
    query_log           = /var/log/sphinxsearch/query.log
    read_timeout        = 5
    client_timeout      = 300
    max_children        = 30
    pid_file            = /var/run/searchd.pid
    max_matches         = 1000
    seamless_rotate     = 1
    preopen_indexes     = 0
    unlink_old          = 1
    mva_updates_pool    = 1M
    max_packet_size     = 8M
    max_filters         = 256
    max_filter_values   = 4096
    listen = localhost:9312
    listen = localhost:9306:mysql41
}

Estas son las opciones del servicio de búsquedas, al cual le haremos las peticiones para que este busque en el índice y nos devuelva los resultados buscados. En este caso, las opciones a recalcar son:

  • max_children: son el número máximo de procesos que se podrán lanzar para atender peticiones en paralelo
  • client_timeout: tiempo máximo que esperará entre peticiones antes de cerrar la conexión
  • max_matches: número máximo de resultados que guardará en RAM y que devolverá al cliente
  • listen: aquí le indicamos en que IP y puerto escuchar las peticiones de búsqueda. En la primera línea escuchará la API de searchd, que será contra la cual nos tendremos que conectar mediante PHP, Ruby, Python o el lenguaje que más rabia nos de. Mientras que en la segunda línea le indicamos que escuche en el puerto 9306 y use el protocolo de red de MySQL. Gracias a este último nos podremos conectar a searchd con un cliente de mysql normal y corriente y lanzar peticiones de búsquedas con los SELECT de toda la vida.

Una vez terminada la configuración debemos ejecutar el indexador para que lea todos los datos de la base de datos y cree el índice. Desde la consola ejecutamos:

root@sphinx1:~# indexer --all
Sphinx 0.9.9-release (r2117)
Copyright (c) 2001-2009, Andrew Aksyonoff
using config file '/etc/sphinxsearch/sphinx.conf'...
indexing index 'employees'...
collected 300024 docs, 7.0 MB
sorted 0.9 Mhits, 100.0% done
total 300024 docs, 7036568 bytes
total 11.790 sec, 596822 bytes/sec, 25447.19 docs/sec
total 4 reads, 0.004 sec, 2694.5 kb/call avg, 1.2 msec/call avg
total 24 writes, 0.028 sec, 1014.5 kb/call avg, 1.1 msec/call avg

Listo, índice creado. Ya podemos lanzar el servicio de Sphinx y lanzarle peticiones:

root@sphinx1:~# /etc/init.d/sphinxsearch start

Para lanzar una búsqueda nos podemos conectar usando un cliente de MySQL:

root@sphinx1:~# mysql -h 127.0.0.1 -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-release (r2117)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

Aunque lo pueda parecer, no estamos conectados a la base de datos MySQL. Todas las consultas SELECT que lancemos las resolverá Sphinx sin llegar a conectarse a la base de datos final. Vamos a lanzar una query de ejemplo:

Queremos saber todos los trabajadores de nuestra empresa que se llamen Jongsuk o Saniya o Dekang, que NO trabajen en el departamento de producción que que su salario sea superior a los 85000 euros.

mysql> select * from employees WHERE MATCH('(Jongsuk|Saniya|Dekang)-Production') 
AND salary > 85000 ORDER BY salary ASC;
+--------+--------+-----------+--------+
| id     | weight | hire_ts   | salary |
+--------+--------+-----------+--------+
| 461700 |   1532 | 508287600 |  85090 |
| 236341 |   1531 | 522885600 |  85728 |
| 434733 |   1532 | 561855600 |  86368 |
| 204028 |   1532 | 559000800 |  86506 |
|  88618 |   1532 | 524959200 |  86608 |
|  87515 |   1531 | 541033200 |  87103 |
| 491721 |   1531 | 611704800 |  87360 |
|  54136 |   1531 | 669250800 |  87459 |
|  13564 |   1531 | 857343600 |  87914 |
|  82319 |   1531 | 624409200 |  88094 |
| 209948 |   1532 | 646005600 |  89143 |
| 417533 |   1531 | 709164000 |  90811 |
| 499794 |   1531 | 699404400 |  91982 |
| 425202 |   1531 | 627951600 |  95582 |
| 446559 |   1532 | 520380000 |  98285 |
| 279372 |   1531 | 659746800 |  99881 |
|  79903 |   1532 | 693702000 | 100803 |
+--------+--------+-----------+--------+
17 rows in set (0.00 sec)

¿Que vemos aquí?

  • ID: es el identificador de nuestro trabajador, que identifica única y exclusivamente a un único trabajador. Es el ID del que hablamos anteriormente y que obligatoriamente tiene que ser UNIQUE y ser el primer resultado de nuestra query. En este caso, concuerda con la Primary Key de la tabla employees.
  • hire_ts: es el timestamp de la fecha de contratación.
  • salary: su sueldo.
  • weight: el peso o importancia del resultado

Como vemos, no podemos saber nombres, apellidos o departamento. Esto es así porque los índices FULL TEXT guardan solamente eso, un índice, pero no su contenido. Pensad en el como en el índice de un libro. Por otro lado, como hire_ts y salary eran atributos y no FULL TEXT, si podemos ver sus valores. Ahora que tenemos el ID del trabajador podemos hacer una consulta a MySQL y sacar todos los datos de dicho trabajador. Como esto será una consulta contra la Primary Key, la respuesta será instantanea. Todo el trabajo de búsqueda ya lo ha hecho Sphinx, así que ahora el trabajo fácil queda para MySQL :)

Aquí la consulta al servidor de MySQL:

root@perconaserver1:~# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.5.13-55 Percona Server (GPL), Release 20.4
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use employees;
Database changed
mysql> select * from employees where emp_no=461700;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 461700 | 1960-06-17 | Dekang     | Anger     | M      | 1986-02-09 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

3 Comments · Tags: ,

Fri 29 Jul

ALTER TABLE en caliente y sin parada de servicio

El funcionamiento de un ALTER TABLE es sencillo:

1- MySQL bloquea la tabla que se desea modificar.
2- Crea una nueva tabla vacia con la nueva estructura.
3- Copia los datos de la antigua a la nueva.
4- Borra la antigua.
5- Renombra la nueva tabla con el nombre original.

Esto en tablas pequeñas puede ser un proceso de segundos o minutos. En tablas muy grandes, podemos estar hablando de horas. Horas en las cuales las consultas a dicha tabla estarán bloqueadas y las conexiones y peticiones se encolarán. Por lo tanto, hacer un ALTER TABLE en producción puede tener consecuencias bastante desastrosas.

Si tenemos replicación master-slave, podemos hacer el ALTER TABLE por fases. Primero lo hacemos en el esclavo. Cuando termine, promocionamos el esclavo a maestro y el maestro a esclavo. Y vuelta a lanzar el ALTER TABLE en el antiguo maestro (ahora esclavo).

¿Y si no tenemos replicación? Eso es lo que voy a explicar ahora :)

Estamos ante esta situación... nuestros amigos desarrolladores han tenido la magnifica idea de hacer un ALTER TABLE para modificar el tipo de dato de una columna, a las 15:00 de la tarde y sin posibilidad de parada. La primera opción es cagarnos en sus madres. Una vez terminado ese primer e indispensable paso, haremos uso de las herramientas openark para disminuir las posibilidades de parada al mínimo.

La herramienta que nos ayudará será oak-online-alter-table. Su funcionamiento a alto nivel es el siguiente:

1- Crea una tabla fantasma (o también llamada delta) sobre la que poco a poco va copiando filas de la original.
2- Cuando la copia se completa, se crean triggers AFTER INSERT, AFTER UPDATE, AFTER DELETE en la tabla original.
3- Cuando la sincronización se completa, se cambia una tabla por otra.

Para que esto funcione correctamente se deben cumplir una serie de requisitos:

  • La tabla debe tener al menos una columna con UNIQUE KEY.
  • La nueva tabla comparte una columna UNIQUE KEY con la tabla original.
  • En la tabla original no deben existir triggers AFTER.
  • La tabla no puede tener claves foráneas.
  • El nombre de la tabla no puede superar los 57 carácteres.

En la documentación se ven varios ejemplos de su funcionamiento:

Añadir una key

oak-online-alter-table --table=world.City --alter="ADD KEY(Population)"

Añadir una key v2.0

oak-online-alter-table --table=world.City --alter="ADD KEY(Population)" --chunk-size=5000 --sleep=20

En este caso, hay dos opciones importantes. --chunk-size indica que la primera sincronización se hará en grupos de 5000 filas. Contra más grande sea el número, menos tardará el alter table, pero se producirán bloqueos de tabla más largo. Y al contrario, contra menos filas copiemos, menos bloqueos pero más largo será el proceso. Si es MyISAM se bloqueará la tabla (READ LOCK) durante la copia de dichas filas, si es INNODB se bloquearán únicamente las filas que se están copiando.

--sleep por su parte indica cuantos milisegundos esperar entre cada copia de grupos de filas.

Vamos a lanzar, por ejemplo, el alter table en una tabla llamada "salaries" con casi 3 millones de registros:

CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`)
  KEY `emp_no_idx` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Antes de poder hacer uso de ella he elimindo las claves foráneas. Procedemos a lanzar el ALTER TABLE como hemos hecho siempre:

mysql> ALTER TABLE salaries MODIFY emp_no VARCHAR(40);
Query OK, 2844047 rows affected (57.75 sec)
Records: 2844047  Duplicates: 0  Warnings: 0

Un minuto ha tardado el proceso. Esto, en una página web con cientos de visitantes concurrentes, puede ser un desastre. Ahora, hagamoslo online:

[root@miguelangelnieto scripts]# time ./oak-online-alter-table --table employees.salaries 
--alter="MODIFY emp_no VARCHAR(40)" -H 127.0.0.1 -u root --ask-pass -q -c 50000
Password: 
real    4m25.628s
user    0m0.138s
sys 0m0.040s

Como vemos, ha tardado 4 minutos siendo un proceso más lento, pero en este caso no hemos tenido parada ni bloqueos largos de la tabla. Nadie se ha enterado y nosotros hemos hecho un ALTER TABLE en producción.

Durante el proceso nos crea la tabla fantasma __oak_salaries:

mysql> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| __oak_salaries      |
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
7 rows in set (0.00 sec)

Para los administradores que prefieran PHP, Mark Callaghan de Facebook ha publicado su propio script para realizar ALTER TABLE's online, siguiendo un método muy similar al explicado aquí:

Script PHP Documentación en Facebook

5 Comments · Tags: ,

Thu 28 Jul

Backups y restauraciones de tablas en InnoDB

Mucha gente cree erroneamente que gracias a la opción innodb-file-per-table te permite, como MyISAM, portar una tabla en binario de un servidor a otro de forma transparente o recuperar el backup de una tabla. El problema viene cuando realmente necesitan hacer uso de ese backup y no funciona como ellos esperaban.

Al contrario que con MyISAM, donde los ficheros de tablas MYD e MYI son independientes del resto y portables, todas las tablas de InnoDB dependen de un tablespace común donde se almacenan las definiciones de las tablas y además depende de los IDs de transacciones entre otras cosas. Por lo que, si restauras un .idb, no recuperarás los datos.

Todo esto se aplica a la versión original de MySQL, la desarrollada por Oracle. Pero Xtrabackup y Percona Server nos permite esquivar esta limitación y trabajar con los ficheros binarios como si se tratasen de tablas MyISAM, moviéndolas y restaurándolas de un servidor a otro.

Lo primero que sorprende es que el servidor de origen no tiene porque ser Percona Server, puede ser el MySQL del repositorio de tu distribución. Los únicos requisitos son:

  • El servidor origen y destino deben tener --innodb-file-per-table
  • Hacer el backup con xtrabackup
  • Recuperar el tablespace en un Percona Server

El proceso de realización del backup no cambia, pero si la posterior reparación (--prepare). El comando de reparación será el mismo, solo que añadimos la opción --export.

xtrabackup --prepare --export --innodb-file-per-table --target-dir=/mnt/backups/mysql-data

Esta opción generará un fichero extra .exp

/mnt/backups/mysql-data/db/table.exp
/mnt/backups/mysql-data/db/table.ibd

Ahora, ¿cómo importamos esa tabla en otro MySQL?

Tal y como hemos comentado antes, el destino debe ser un Percona Server. Únicamente tendremos que ejecutar los siguientes comandos:

ALTER TABLE db.table DISCARD TABLESPACE;

Copiamos las tablas exportadas al subdirectorio db/ del servidor destino.

ALTER TABLE db.table IMPORT TABLESPACE;

Y listo.

Tenéis información más actualizada en el propio manual de xtrabackup.

http://www.percona.com/docs/wiki/percona-xtrabackup:xtrabackup:export_and_import


Tue 26 Jul

Pre-cachear los datos de InnoDB en el buffer pool

Cuando tienes un entorno activo-pasivo o montas un esclavo para las lecturas, el mayor problema que te puedes encontrar al poner los nuevos servidores en producción es que las cachés se encuentren frias (cold cache). Como dichos servidores no han recibido consultas, todas sus cachés, como query cache o innodb_buffer_pool se encuentran vacias y todas las consultas tendrán que ir a disco duro durante los primeros minutos u horas. En esos primeros instantes, el rendimiento de tu backend será pésimo.

Hasta ahora, para evitar en la medida de lo posible ese problema, se lanzaban SELECT contra las tablas que obligasen a leerse todas las filas. Eran consultas muy pesadas que tardaban mucho tiempo en ejecutarse y ralentizaban aún más el rendimiento, pero... no había otra solución. Un ejemplo de está solución se puede leer en el blog de Santi Saez Woop!. Pero esto ya ha cambiado.

En la versión de desarrollo de MySQL 5.6 ya es posible guardar el estado actual del buffer pool en un fichero y recuperarlo en memoria después del reinicio del servicio. No más esperas ni trucos de scripting, ahora hablamos de un procedimiento nativo dentro del propio servicio.

Lanzar un dump del buffer pool:

mysql> SET innodb_buffer_pool_dump_now=ON;

Configurar un dump cuando se realice el apagado del servicio:

mysql> SET innodb_buffer_pool_dump_at_shutdown=ON;

Recuperar un dump en memoria:

mysql> SET innodb_buffer_pool_load_now=ON;

Y en my.cnf podemos poner esta línea, para que se cargue automáticamente durante el arranque del servicio:

innodb_buffer_pool_load_at_startup=ON

Podemos ver el progreso del dump:

mysql> SHOW STATUS LIKE 'innodb_buffer_pool_dump_status';

Y el progreso de la restauración:

mysql> SHOW STATUS LIKE 'innodb_buffer_pool_load_status';

Así como cancelarlo :)

mysql> SET innodb_buffer_pool_load_abort=ON;

Es importante recalcar que si estás utilizando Percona Server, su engine xtradb dispone también de esta funcionalidad, por lo que no tienes que esperar. Simplemente, los comandos cambian:

Lanzar un dump del buffer pool:

mysql> select * from information_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_DUMP*/;

Recuperar un dump en memoria:

mysql> select * from information_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_RESTORE*/;

Programar un dump cada X segundos:

innodb_auto_lru_dump = X

Cargar automáticamente el dump en el arranque:

innodb_buffer_pool_restore_at_startup=1

Replicación con Multi-Threaded Slaves, sacando uso a nuestros cores

Ahora mismo, todos los servidores que compramos son multi-core o multi-cpu. MySQL ha ido solucionando sus problemas de escalabilidad, sobre todo a nivel del engine InnoDB, y la diferencia es clara entre MySQL 5.0 y MySQL 5.5, donde el rendimiento en entornos multicore es cada vez mayor. Pero aún queda un punto por mejorar, la replicación de MySQL.

En una replicación MySQL Master/Slave el problema se puede ver claramente. Mientras que en el maestro puedes tener cientos de threads modificando datos en paralelo, estos se escriben de forma ordenada en el binlog mientras que el slave, que solo tiene un thread para aplicar los cambios (SQL Thread), tiene que escribir los cambios uno a uno. De esta forma, el rendimiento que ganamos con la paralelización de las consultas, se pierden al llegar al Slave. Razón por la cual en entornos de alta carga siempre vemos que el esclavo va muy por detrás del Master aplicando los cambios (Seconds Behind Master).

Este es un problema que se está intentando solucionar en las versiones de desarrollo de MySQL. En la versión 5.6 se ha introducido un nuevo concepto llamado Workers, que son diferentes SQL Threads para la aplicación en paralelo de los cambios.

Fuente

Podemos descargar una versión de desarrollo con esta funcionalidad desde http://labs.mysql.com/.

Con una serie nueva de parámetros que veremos a continuación podemos decirle a nuestro Slave cuantos Workers debe lanzar. Cada Worker podrá escribir los cambios del relaylog en paralelo, siempre y cuando pertenezcan a diferentes bases de datos. Por lo que de momento, para poder sacar beneficio a esta nueva funcionalidad, deberás tener diferentes bases de datos, cosa que es bastante habitual en entornos complejos donde el Sharding es la norma a seguir.

En primer lugar, debemos decirle a nuestro MySQL que ahora algunos de los estados relacionados con la replicación la queremos guardar en base de datos, concretamente en tablas dentro de la BD mysql:

relay-log-info-repository="TABLE" 
master-info-repository="TABLE" 

relay-log-info-repository: esta variable indica donde se guardará la posición en la que nos encontramos dentro del relay log master-info-repository: esta variable indica donde se guardará la información referente a su master

Todas las opciones se encuentran ya documentadas en http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html. Es recomendable tener esta URL a mano, ya que al ser algo que se encuentra en desarrollo, las especificaciones y las opciones cambian constantemente.

Una vez hecho, nos conectamos al Slave y le decimos que queremos cuatro Workers.

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL slave_parallel_workers=4;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@slave_parallel_workers;
+------------------------------+
| @@slave_parallel_workers |
+------------------------------+
|                            4 |
+------------------------------+
1 row in set (0.00 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.05 sec)

A continuación comprobamos si realmente se han lanzado los 4 procesos:

mysql> show processlist;
+----+-------------+------+---------+------+---------------------------------------------+
| Id | User        | db   | Command | Time | State                                       |
+----+-------------+------+---------+------+---------------------------------------------+
|  1 | root        | NULL | Query   |    0 | init                                        |
|  8 | system user | NULL | Connect |    2 | Waiting for master to send event            |
|  9 | system user | NULL | Connect |    2 | Slave has read all relay log; waiting [...] |
| 10 | system user | NULL | Connect |    2 | Waiting for an event from sql thread        |
| 11 | system user | NULL | Connect |    2 | Waiting for an event from sql thread        |
| 12 | system user | NULL | Connect |    2 | Waiting for an event from sql thread        |
| 13 | system user | NULL | Connect |    2 | Waiting for an event from sql thread        |
+----+-------------+------+---------+------+---------------------------------------------+
7 rows in set (0.00 sec)

Ahora mismo ya tenemos cuatro threads SQL para la aplicación de datos en paralelo. Como hemos comentado anteriormente, es posible aplicar cambios en paralelo siempre que estos sean de bases de datos diferentes. Por lo no tiene sentido que el número de threads que lancemos sea superior al del número de bases de datos que queremos replicar.

Esta es solo una más de las múltiples mejoras que se están implantando en la rama de desarrollo, intentaré manteneros informados de todas las novedades interesantes que sigan surgiendo :)


Sat 16 Jul

Sniffing del protocolo de red de MySQL

En ocasiones es necesario hacer un análisis de que está pasando en un servidor con MySQL, comprobar que queries se están ejecutando, cuanto tardan, donde están los cuellos de botella, etc. Hay diferentes formas de hacerlos:

  • A lo pobre: ejecutar SHOW FULL PROCESSLIST cada pocos segundos, identificar a ojo las querys que pueden ser interesantes y luego analizarlas.
  • A lo basto: habilitar el log general de MySQL y almacenar todas las querys que se ejecutan. Te llevarás una gran cantidad de IOPS y puede que el fichero termine siendo tan grande que analizarlo sea un infierno.

Como casi siempre, las maatkit vienen a ayudarnos en esta tarea. En esta ocasión, mk-query-digest nos va a permitir analizar la ejecución de querys y generarnos un reporte. Esta utilidad es capaz de coger datos de los logs, pero aquí vamos a utilizar un parámetro que nos ayudará a capturar el tráfico directamente de la interfaz de red y hacer un reporte de lo que está pasando ahora mismo.

Para ello solo necesitamos tcpdump y mk-query-digest :)

Iniciamos la captura de tráfico y se la pasamos a mk-query-digest por STDIN:

root: ~ # tcpdump -s 65535 -x -n -q -tttt -i lo0 -c 10000
 port 3306 | ./mk-query-digest --type tcpdump

Ahora mismo, tcpdump estará capturando el tráfico del puerto TCP 3306 y pasándolo por pipe a mk-query-digest. Pasados unos tiempo (10000 paquetes) veremos el reporte:

listening on lo0, link-type NULL (BSD loopback), capture size 65535 bytes
10000 packets captured
17211 packets received by filter
7070 packets dropped by kernel
# 2.2s user time, 30ms system time, 16.28M rss, 2.33G vsz
# Current date: Sat Jul 16 20:50:09 2011
# Hostname: iMac-Punisher.local
# Files: STDIN
# Overall: 2.20k total, 9 unique, 668.40 QPS, 0.38x concurrency __________
# Time range: 2011-07-16 20:50:05.650775 to 20:50:08.940697
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time             1s       0    95ms   569us     1ms     2ms   287us
# Rows affecte       1.91k       0       1    0.89    0.99    0.31    0.99
# Query size       835.46k      25     433  389.05  420.77  118.11  420.77
# Warning coun           0       0       0       0       0       0       0
# Boolean:
# No index use   0% yes,  99% no
# Profile
# Rank Query ID           Response time Calls R/Call Apdx V/M   Item
# ==== ================== ============= ===== ====== ==== ===== ==========
#    1 0xCAEC22E79B0EFD3B  0.9023 72.1%  1955 0.0005 1.00  0.01 INSERT t?
#    2 0xA21B9C02CA2BF472  0.2649 21.2%   139 0.0019 1.00  0.00 SELECT t?
#    3 0xBCFD2D3AD85C5621  0.0475  3.8%     1 0.0475 1.00  0.00 CREATE TABLE t? `t1`
# MISC 0xMISC              0.0368  2.9%   104 0.0004   NS   0.0 <6 ITEMS>

Podemos mejorarlo aún más, haciendo que las queries pasen por el explain automáticamente. Esto último hay que hacerlo con cuidado, porque al contrario de lo que cree mucha gente, un explain no siempre evita que parte de la query se tenga que ejecutar consumiendo recursos.

tcpdump -s 65535 -x -n -q -tttt -i lo0 -c 10000 port 3306 | ./mk-query-digest 
--type tcpdump --explain h=localhost,u=root

# Query 2: 60.04 QPS, 0.11x concurrency, ID 0xA21B9C02CA2BF472 at byte 269106
# This item is included in the report because it matches --limit.
# Scores: Apdex = 1.00 [1.0], V/M = 0.00
# EXPLAIN sparkline: a
# Query_time sparkline: |  .^_   |
# Time range: 2011-07-16 20:59:46.310625 to 20:59:48.425763
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5     127
# Exec time     21   242ms   143us    15ms     2ms     4ms     2ms     2ms
# Rows affecte   0       1       0       1    0.01       0    0.09       0
# Query size     0   7.19k      58      58      58      58       0      58
# Warning coun   0       0       0       0       0       0       0       0
# String:
# Databases    mysqlslap
# Errors       none
# Hosts        127.0.0.1
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us  ###########################
#   1ms  ################################################################
#  10ms  ##
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM 'mysqlslap' LIKE 't1'\G
#    SHOW CREATE TABLE 'mysqlslap'.'t1'\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT intcol1,intcol2,charcol1,charcol2,charcol3 FROM t1\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: t1
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 34
#         Extra:

No solo puedes analizar las queries de tu MySQL en tiempo real, si no incluso hacer un explain y comprobar sus planes de ejecución :)


Fri 8 Jul

Mantener la consistencia de los datos en la replicación

Las replicaciones necesitan de un chequeo constante en la integridad de los datos. Fallos de disco, corrupción de de logs, mezcla de tablas transaccionales y no transaccionales y otros problemas pueden tumbar la consistencia de nuestros datos. Por lo tanto, podemos tener una replicación funcionando, pero los datos, si no hay una comprobación activa, pueden ser diferentes en las dos máquinas. MySQL no tiene comprobaciones activas de consistencia, por lo que es trabajo nuestro. Para ello, instalamos las herramientas maatkit de Percona:

apt-get install maatkit

Las herramientras que usaremos serán mk-table-checksum y mk-table-sync. El funcionamiento de la herramienta se basa en la replicación en base a sentencias de mysql. mk-table-checksum realiza una comprobación mediante un algoritmo de hashing en las tablas, escribiendo los resultados en la base de datos. Estas sentencias se replicarán en el esclavo y se volverán a ejecutar, realizando por lo tanto el mismo hashing en las tablas del esclavo. De esta forma, unicamente debemos comparar los resultados en el maestro y en el esclavo para comprobar si los datos son exactamente iguales o no.

Tenemos dos MySQL en Master-Slave.

NODO 1:

Puerto TCP 19369

master [localhost] {msandbox} (vida) > select * from producto;
+---+--------+
| i | nombre |
+---+--------+
| 1 | VPS    |
| 2 | Cloud  |
+---+--------+
2 rows in set (0.00 sec)

NODO 2:

Puerto TCP 19370

slave1 [localhost] {msandbox} (vida) > select * from producto;
+---+---------+
| i | nombre  |
+---+---------+
| 1 | VPS     |
| 2 | Cloud   |
| 3 | Storage |
+---+---------+
3 rows in set (0.00 sec)

¿Como comprobamos si los datos son o no consistentes?

Primero, le decimos a mk-table-checksum que haga un checksum de todas las tablas de la base de datos vida y guarde el resultado en test.checksum.

~$ mk-table-checksum u=root,p=msandbox
--socket=/tmp/mysql_sandbox19369.sock --databases=vida
--replicate test.checksum --create-replicate-table
Cannot connect to P=19369,S=/tmp/mysql_sandbox19369.sock,h=SBslave1,p=...,u=root
Cannot connect to P=19369,S=/tmp/mysql_sandbox19369.sock,h=SBslave1,p=...,u=root
DATABASE TABLE    CHUNK HOST        ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
vida     producto     0 soporteit69 InnoDB          2         935b4964    0 NULL NULL NULL

Comprobamos los resultados en Master:

master [localhost] {msandbox} (test) > select * from checksum;
+------+----------+-------+------------+----------+----------+------------+------------+
| db   | tbl      | chunk | boundaries | this_crc | this_cnt | master_crc | master_cnt |
+------+----------+-------+------------+----------+----------+------------+------------+
| vida | producto |     0 | 1=1        | 935b4964 |        2 | 935b4964   |          2 |
+------+----------+-------+------------+----------+----------+------------+------------+
1 row in set (0.00 sec)

Comprobamos los resultados en Slave:

slave1 [localhost] {msandbox} (test) > select * from checksum;
+------+----------+-------+------------+----------+----------+------------+------------+
| db   | tbl      | chunk | boundaries | this_crc | this_cnt | master_crc | master_cnt |
+------+----------+-------+------------+----------+----------+------------+------------+
| vida | producto |     0 | 1=1        | 6120c018 |        3 | 935b4964   |          2 |
+------+----------+-------+------------+----------+----------+------------+------------+
1 row in set (0.00 sec)

Como podemos ver, el crc del slave (this_crc) es diferente al del master (master_crc) por lo tanto, tenemos incosistencias en los datos de la tabla producto de la base de datos vida.

¿Cómo lo arreglamos?

Podriamos hacerlo volviendo a montar la replicación de cero usando un dump de mysqldump. Esto vale si tu BBDD ocupa solo unos cientos de megas, pero si hablamos de gigas, puedes tardar horas y provocar caidas del sistema. Así que haremos uso de mk-table-sync.

~$ mk-table-sync --execute --replicate test.checksum 
--sync-to-master u=root,p=msandbox --socket=/tmp/mysql_sandbox19370.sock

Comprobamos si ahora los datos son consistentes:

master [localhost] {msandbox} (vida) > select * from producto;
+---+--------+
| i | nombre |
+---+--------+
| 1 | VPS    |
| 2 | Cloud  |
+---+--------+
2 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (vida) > select * from producto;
+---+--------+
| i | nombre |
+---+--------+
| 1 | VPS    |
| 2 | Cloud  |
+---+--------+
2 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (test) > select * from checksum;
+------+----------+-------+------------+----------+----------+------------+------------+
| db   | tbl      | chunk | boundaries | this_crc | this_cnt | master_crc | master_cnt |
+------+----------+-------+------------+----------+----------+------------+------------+
| vida | producto |     0 | 1=1        | 935b4964 |        2 | 935b4964   |          2 |
+------+----------+-------+------------+----------+----------+------------+------------+
1 row in set (0.00 sec)

Listo! Replicación consistente y en marcha :)


Mon 20 Jun

Gestión centralizada de MySQL en un DataCenter

Cuando el número de servidores que administras es pequeño, realizar tareas sobre estos no supone un gran problema. Pero cuando se empieza a superar el número de pestañas que entran en el terminal, empiezas a tener un problema. El uso de una administración centralizada te permite tener el control total de las configuraciones, paquetes y servicios que hay en cada servidor desde un punto central, aliviando así la carga de trabajo y manteniendo un entorno lo más homogéneo posible.

Para llevar a cabo esta tarea existen múltiples soluciones en el mundo del Software Libre. Estas semanas he dedicado parte de mi tiempo libre a rellenar esta laguna de conocimientos que tenía como sysadmin y el elegido ha sido Puppet.

Puppet es una herramienta de gestión centralizada de servidores, basado en Ruby y con un lenguaje declarativo muy parecido a este. Existen múltiples módulos para gestionar servicios como Apache, nginx, Postfix, Cron, etc. Y lo más importante, gran parte de estos, por ejemplo el de la gestión de paquetes, es independiente de la distribución final. Usando el lenguaje de Puppet, a un nivel superior, indicando que quieres tener instalado el paquete X, el agente de Puppet en el lado del cliente se encargará de usar yum, apt-get, emerge o lo que sea necesario para cumplir la tarea indicada.

Para poner en práctica mis conocimientos, he desarrollado un módulo de MySQL que ahora comparto con todos vosotros. La razón para elegir son varías:

  • Es un servicio que conozco
  • Los módulos realizaban pequeñas tareas, no había uno que hiciese de todo un poco
  • Porque si

Así que me puse manos a la obra, y aquí está el resultado:

https://github.com/miguelangelnieto/puppet-mysql

Con este módulo de Puppet, podréis gestionar todos los MySQL de vuestro datacenter desde un Puppet Master con una sintaxis sencilla. Las tareas que se podrán realizar son:

  • Instalar el paquete de MySQL Server
  • Gestionar la activación en arranque y la puesta en marcha del servicio
  • Configurar my.cnf
  • Importación de bases de datos
  • Creación de usuarios y dar privilegios

¿Cómo lo hacemos? El ejemplo que vemos a continuación demuestra todo lo que se puede hacer. Configuración de un nodo de bases de datos MySQL:

node 'debian.client.puppet' {
    include mysql
mysql::config_file {
        "/etc/mysql/my.cnf":
        max_connections => "100",
        bind_address => "10.10.0.2",
        key_buffer => "40M",
        query_cache_size => "0",
    innodb_buffer_pool_size => "120M";
        }
mysql::db {
    "test":
    source_file => "test.sql"
    }
mysql::grants {
    "admin_test":
    user => "admin",
    password => "4dm1n",
    db => "test",
    privileges => "all privileges";
    }
}

Como se puede ver la sintaxis es fácil de leer. La configuración, dividida en tres partes, genera un fichero my.cnf a medida, crea la base de datos test usando como origen el dump test.sql y finalmente, crea el usuario admin y le da all privileges a dicha base de datos.

Este ejemplo es para una sola máquina, debian.cliente.puppet, pero si eliminamos el nombre y en su lugar ponemos una expresión regular o simplemente "default" tendremos nuestros cientos de servidores gestionados desde un único punto central. Podrás crear cientos de bases de datos, asignarlo a otros cientos de usuarios y generar una configuración de my.cnf acorde a dichas bases de datos.

Respecto a las posibilidades de parametrización de my.cnf, las opciones que se pueden configurar están reflejados en https://github.com/miguelangelnieto/puppet-mysql/blob/master/modules/mysql/manifests/config_file.pp Si se desean añadir más, lo único que habría que hacer es editar este fichero y reflejar los cambios en el template https://github.com/miguelangelnieto/puppet-mysql/blob/master/modules/mysql/templates/my.erb

Gracias a este módulo podremos gestionar todos los MySQL de nuestro DataCenter de forma fácil y sencilla, meter nuevos servidores en producción en tiempo record y lo más importante, trabajar con un entorno homogéneo y desde un punto único y central.


Fri 27 May

BOTs para Twitter de MySQL

Llevo dos semanas de baja, sin poder tampoco salir de casa. Razón por la cual pensé en aprovechar el tiempo e intentar mejorar mis conocimientos en algo que nunca se me ha dado bien. La programación y más exactamente, la orientada a objetos. Así que me pillé un libro Ruby y Kindle en mano he ido aprendiendo y poniendo en funcionamiento conceptos que siempre me han sonado a chino. Si bien es cierto que sigo teniendo muchísimas deficiencias de conocimientos a nivel de programación (soy sysadmin, fuera de bash scripting casi nunca tengo que hacer nada), creo que he cogido una base que me permitirá ir mejorando poco a poco dentro de mis limitaciones ;)

Para poner en práctica todo lo aprendido, quise hacer algo relacionado con las redes sociales y MySQL. Y entonces recordé que un buen amigo y ex-compañero de trabajo llamado Saúl, también conocido como el chico SIP, programó en su día un BOT que hacía retweet de todos los hashtag #asterisk. Así que me puse manos a la obra, e intentando hacer un código lo menos sucio y guarro posible, me programé mi propio bot. El código está disponible en github, al alcance de todo el mundo que quiera mejorarlo, forkearlo... o imprimirlo y limpiarse el culo con el https://github.com/miguelangelnieto/Twitter-BOT

Ahora mismo ya hay dos bots corriendo, que son @MySQLBot_en y @MySQLBot_es. Como os podéis imaginar, el primero retweetea los mensajes de MySQL en inglés y el segundo en español. Tengo pensado montarlo también en francés y aleman. El problema es que como no entenderé los tweets, por lo que será complicado filtrar y banear usuarios indeseados. Si alguien quiere colaborar, que me avise :)

← Previous Next → Page 2 of 7