Posts tagged with “mysql”
Posts publicados en MySQL High Performance
Como ya comenté anteriormente ahora mis esfuerzos de blogging se centran más en generar contenido para MySQL High Performance :) Para los que quieran un rápido listado de mis últimos posts, aquí lo tenéis:
- Avoiding auto-increment holes on InnoDB with INSERT IGNORE
- Statement based replication with Stored Functions, Triggers and Events
- Actively monitoring replication connectivity with MySQL’s heartbeat
- How to recover a single InnoDB table from a Full Backup
- How to recover deleted rows from an InnoDB Tablespace
- InnoDB’s gap locks
Y si, prometo volver a escribir en mi blog en un futuro cercano. Solo necesito reordenar mi vida :)
Último certificado del año, MySQL 5.0 Developer
Justo cuando llegaba el final del año conseguí mi objetivo, el certificado MySQL 5.0 Developer. Ya tengo el pack completo :) La verdad es que este me ha parecido ligeramente más complicado que los anteriores, más que nada por mi poca experiencia con el lenguaje SQL. Pero para eso es están las certificaciones, para aprender.
Ahora toca centrarse en aplicar lo aprendido y seguir mejorando poco a poco. Tengo muchos libros para leer en mi Kindle pero poco tiempo. He empezado por "MySQL High Availability" que a pesar de ser un poco pesado de leer en algunas partes y que son muy pesados con su librería de Python, es un buen libro para ampliar los conocimientos en replicación y alta disponibilidad.
Fuera de la lectura técnica, ¿tenéis alguna recomendación para leer en este 2012? A mi me ha picado la curiosidad Metro 2033.
Nuevos cambios, nuevas oportunidades
Hace bastante tiempo que no escribo aquí y creo que es necesario dar una explicación a las personas que me siguen, aunque no sean muchas ;)
Mi vida laboral ha vuelto a dar un cambio importante, el segundo en lo que voy de año. Desde Octubre he entrado a trabajar en Percona como Support Engineer lo cual, bajo mi punto de vista, es un salto profesional grandísimo y le estoy muy agradecido a Ewen Fortune por esta oportunidad. Ahora mismo tengo de todo en mi vida, pero no tiempo libre ;) Los que me conocen bien saben que en cuanto tengo un reto por delante no puedo dejar de trabajar y esforzarme dando lo mejor de mi, por lo que estos meses están siendo muy intensos. Mucha lectura, estudio y práctica para poder alcanzar el nivel de profesionalidad y conocimiento que tanto hacen destacar a Percona.
Seguiré publicando posts sobre MySQL en mi blog, pero en un principio con menos frecuencia que antes, al menos hasta que me asiente completamente en mi nuevo trabajo. De vez en cuando podéis encontrar algún post mio en MySQL Performance Blog y si saco tiempo iré publicando aquí las versiones en Español.
Para empezar, aquí tenéis mi primera contribución:
Avoiding auto-increment holes on InnoDB with INSERT IGNORE
Muchas gracias a todos y un saludo especial a mis antiguos compañeros de Arsys.
Percona Live London
La próxima semana comienza la Percona Live y esta vez podremos disfrutarla sin salir de Europa. Durará dos días, 24 y 25 de Octubre.
Durante el primer dia se impartirán tutoriales de diversos tema, como por ejemplo NDB o Sphinx.
http://www.percona.com/live/london-2011/schedule-tutorial/
Y el segundo día se reserva para las conferencias:
http://www.percona.com/live/london-2011/schedule-conference/
Las conferencias serán impartidas no solo por compañeros de Percona, si no también por trabajadores de empresas como Paypal, Facebook, Nokia, Couchbase o Monty Program.
Como se puede comprobar, posibilidades de aprender hay miles. Aún estás a tiempo de apuntarte :) Yo estaré por allí toda la semana, así que si algún lector de este blog va a asistir a la Percona Live... ¡allí nos veremos!
Recopilar información del SO en el momento preciso
Muchas veces los problemas que tenemos con nuestra base de datos no son continuos, si no puntuales, y ocurren generalmente cuando no estamos mirando la pantalla. Las herramientas que hoy voy a mostraron os permitirán cazar el momento exacto de un problema y recabar todos los datos posibles en ese preciso instante de tiempo.
Las dos herramientas pertenecen al Percona Toolkit. Estas toolkit incluyen lo que antes era Aspersa y Maatkit. Por lo tanto, comenzamos descargando:
Dentro de el las utilidades que hoy usaremos serán:
pt-collector: es la herramienta que se lanzará cuando una condición específica se de. Entre otras cosas se encargará de recargar información sobre IO, procesos, memoria, processlist, estado de las variables en MySQL y salidas de comandos como iostat, mpstat, df, lsof. También permite capturar tráfico tcpdump, el proceso con strace y lanzar un profile con Oprofile.
pt-stalk: será el encargado de llamar a pt-collector cuando una determinado estado, definido por nosotros, se de. Por ejemplo, podemos configurarlo de forma que lance pt-collector cuando el número de queries simultáneas alcancen los 100, cuando existan más de 25 tabla temporales en creación, cuando un proceso/usuario en particular se conecte, etc.
Los dos son script en bash (no es Perl! Yuhuu!). No hay fichero de configuración, se deben editar las variables dentro de los propios scripts.
Empezamos con pt-stack:
# ######################################################################## # Configuration settings. # ######################################################################## # This is the max number ofwe want to tolerate. THRESHOLD=${THRESHOLD:-50} # This is the thing to check for. VARIABLE=${VARIABLE:-Threads_connected} # How many times must the condition be met before the script will fire? CYCLES=${CYCLES:-1} # Collect GDB stacktraces? GDB=${GDB:-no} # Collect oprofile data? OPROFILE=${OPROFILE:-yes} # Collect strace data? STRACE=${STRACE:-no} # Collect tcpdump data? TCPDUMP=${TCPDUMP:-yes} # Send mail to this list of addresses when the script triggers. # EMAIL= # Any options to pass to mysql/mysqladmin, such as -u, -p, etc # MYSQLOPTIONS="" # This is the interval between checks. INTERVAL=${INTERVAL:-30} [...] # This is the location of the 'collect' script. if [ -z "${COLLECT}" ]; then COLLECT="/usr/bin/pt-collect"; fi # This is where to store the collected data. if [ -z "${DEST}" ]; then DEST="/tmp/collected/" fi
Como vemos, la configuración es sencilla. Estamos diciendo que cuando la variable "Threads_connected" tenga un valor superior a 50 lanzará el pt-collect junto con información de oprofile y tcpdump. Hay más opciones, pero esto es un post de introducción, no un manual :)
Entonces, el primer paso es lanzar el pt-stack:
root@debian-slave:~# pt-stalk 2011_10_07_14_34_36 check results: Threads_connected = 2, matched = no, cycles_true = 0 [...] 2011_10_07_14_41_28 check results: Threads_connected = 71, matched = yes, cycles_true = 1 2011_10_07_14_41_28 sleeping 300 seconds to avoid DOS attack
Si ahora miramos la carpeta donde se guardan los resultados de collect, ahí tendemos toda la información necesaria recogida.
root@debian-slave:~# ls -l /tmp/collected/ total 1248 -rw-r--r-- 1 root root 9120 oct 7 14:42 2011_10_07_14_41_29-df -rw-r--r-- 1 root root 19800 oct 7 14:42 2011_10_07_14_41_29-diskstats -rw-r--r-- 1 root root 13 oct 7 14:42 2011_10_07_14_41_29-hostname -rw-r--r-- 1 root root 2645 oct 7 14:41 2011_10_07_14_41_29-innodbstatus1 -rw-r--r-- 1 root root 2645 oct 7 14:42 2011_10_07_14_41_29-innodbstatus2 -rw-r--r-- 1 root root 40800 oct 7 14:42 2011_10_07_14_41_29-interrupts -rw-r--r-- 1 root root 66 oct 7 14:41 2011_10_07_14_41_29-iostat -rw-r--r-- 1 root root 66 oct 7 14:41 2011_10_07_14_41_29-iostat-overall -rw-r--r-- 1 root root 91 oct 7 14:41 2011_10_07_14_41_29-log_error -rw-r--r-- 1 root root 16327 oct 7 14:41 2011_10_07_14_41_29-lsof -rw-r--r-- 1 root root 35580 oct 7 14:42 2011_10_07_14_41_29-meminfo -rw-r--r-- 1 root root 66 oct 7 14:41 2011_10_07_14_41_29-mpstat -rw-r--r-- 1 root root 66 oct 7 14:41 2011_10_07_14_41_29-mpstat-overall -rw-r--r-- 1 root root 66573 oct 7 14:41 2011_10_07_14_41_29-mutex-status1 -rw-r--r-- 1 root root 66573 oct 7 14:42 2011_10_07_14_41_29-mutex-status2 -rw-r--r-- 1 root root 442530 oct 7 14:42 2011_10_07_14_41_29-mysqladmin -rw-r--r-- 1 root root 32040 oct 7 14:42 2011_10_07_14_41_29-netstat -rw-r--r-- 1 root root 41520 oct 7 14:42 2011_10_07_14_41_29-netstat_s -rw-r--r-- 1 root root 53 oct 7 14:41 2011_10_07_14_41_29-opentables1 -rw-r--r-- 1 root root 52 oct 7 14:42 2011_10_07_14_41_29-opentables2 -rw-r--r-- 1 root root 423 oct 7 14:42 2011_10_07_14_41_29-output -rw-r--r-- 1 root root 26742 oct 7 14:41 2011_10_07_14_41_29-pmap -rw-r--r-- 1 root root 15714 oct 7 14:41 2011_10_07_14_41_29-processlist1 -rw-r--r-- 1 root root 194 oct 7 14:42 2011_10_07_14_41_29-processlist2 -rw-r--r-- 1 root root 35224 oct 7 14:42 2011_10_07_14_41_29-procstat -rw-r--r-- 1 root root 44906 oct 7 14:42 2011_10_07_14_41_29-procvmstat -rw-r--r-- 1 root root 6451 oct 7 14:41 2011_10_07_14_41_29-ps -rw-r--r-- 1 root root 229140 oct 7 14:42 2011_10_07_14_41_29-slabinfo -rw-r--r-- 1 root root 25 oct 7 14:41 2011_10_07_14_41_29-stacktrace -rw-r--r-- 1 root root 22924 oct 7 14:41 2011_10_07_14_41_29-sysctl -rw-r--r-- 1 root root 7006 oct 7 14:41 2011_10_07_14_41_29-top -rw-r--r-- 1 root root 90 oct 7 14:41 2011_10_07_14_41_29-trigger -rw-r--r-- 1 root root 6596 oct 7 14:41 2011_10_07_14_41_29-variables -rw-r--r-- 1 root root 2670 oct 7 14:42 2011_10_07_14_41_29-vmstat -rw-r--r-- 1 root root 313 oct 7 14:42 2011_10_07_14_41_29-vmstat-overall
Por ejemplo:
root@debian-slave:~# cat /tmp/collected/2011_10_07_14_41_29-processlist1 |more *************************** 1. row *************************** Id: 304 User: root Host: localhost db: sbtest Command: Query Time: 0 State: NULL Info: UNLOCK TABLES *************************** 2. row *************************** Id: 305 User: root Host: localhost db: sbtest Command: Execute Time: 0 State: Writing to net Info: SELECT c from sbtest where id between 504280 and 504379 order by c *************************** 3. row *************************** Id: 306 User: root Host: localhost db: sbtest Command: Query Time: 0 State: NULL Info: UNLOCK TABLES [...]
Ya no es necesario que te quedes a las 3 de la mañana esperando a que todo deje de funcionar para empezar a recopilar datos :)
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.
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)
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í:
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
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