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)