Para poder hacer algunas pruebas representativas decidí realizar una carga de textos a partir de unos cuantos libros gratuitos del proyecto Gutenberg (http://www.gutenberg.org). La carga incluye 33278 documentos con un total de 3.67GB de texto danto una media de 115KB por documento. Para que nos hagamos todos una idea de la longitud en texto plano que son 115KB hice una prueba de copiar sobre un documento Word uno de los registros. Con interlineado simple y letra de 11 puntos correspondió a unas 50 páginas con lo que interpolando al total estamos indexando aproximadamente más de un millón seiscientas mil páginas. A dichas tablas añadí un par de columnas fecha y país para hacer pruebas sobre consultas cruzadas con alta-baja selectividad respecto al filtro relacional. La fecha la he distribuido uniformemente de forma que cada entrada corresponde a 1 hora con lo que la diferencia entre el primer documento y el último es de casi 4 años. El país lo he distribuido entre 15 valores de forma no uniforme. La distribución que he seguido ha sido en base 2. Esto es, al primero de los países le he asignado 2 documentos, al segundo 4, al tercero 8, 16, 32, 64,… J Ambas columnas tienen un índice no cluster sobre esa única columna. Esto lo podemos conseguir fácilmente con un update en función de un identity o utilizando row_number. En mi caso he hecho un sencillo update como este basándome en una columna IDENTITY(1,1) que ya tenía definida:
update documentos
set fecha = dateadd(hour, id, ‘20050101’)
, pais = floor(log10(id+1)/log10(2))
Obteniendo este resultado:
ID Fecha País
1 2005-01-01 01:00:00.000 1
2 2005-01-01 02:00:00.000 1
3 2005-01-01 03:00:00.000 2
4 2005-01-01 04:00:00.000 2
5 2005-01-01 05:00:00.000 2
6 2005-01-01 06:00:00.000 2
7 2005-01-01 07:00:00.000 3
8 2005-01-01 08:00:00.000 3
9 2005-01-01 09:00:00.000 3
10 2005-01-01 10:00:00.000 3
11 2005-01-01 11:00:00.000 3
12 2005-01-01 12:00:00.000 3
13 2005-01-01 13:00:00.000 3
14 2005-01-01 14:00:00.000 3
15 2005-01-01 15:00:00.000 4
16 2005-01-01 16:00:00.000 4
…. …. …
33278 2008-10-18 14:00:00.000 15
La primera diferencia obvia que encontramos al pasar a 2008 es el aumento de tamaño de nuestros ficheros de datos al incluirse los índices dentro de la propia base de datos. En ambos casos el tamaño ocupado por los datos es muy similar (~3.7 GB) pero en el caso de SQL Server 2008 el tamaño de los índices es de 7.3 GB. Si, no es una equivocación J Los índices full-text no son como los índices tradicionales con lo que es habitual que acaben teniendo un tamaño superior al de los datos indexados. Obviamente esto depende de muchos factores como la variabilidad de las palabras, el número de palabras de “ruido” que se descarten, etc. En SQL Server 2000 los índices se almacenan en ficheros independientes por defecto dentro de la carpeta FTDATA de la instancia en cuestión. En nuestro caso el tamaño de dichos índices es de 4.9 GB. Ciertamente la diferencia de casi un 50% en el tamaño de los índices es significativa y debe tenerse en cuenta de cara a afrontar los upgrades de versiones anteriores. Por otra parte el proceso de carga completa del índice (population) fue bastante lento en ambos casos debido principalmente a las limitaciones de la máquina en la que lo realicé (32 bits, 3Gb RAM).
En realidad la CPU no llegó a sobrepasar el 50% durante todo el proceso por lo que el subsistema de IO y la cantidad de memoria de la máquina se mostraron claramente insuficientes para esta labor. No puedo por tanto sino recomendar que implementaciones de fulltext search se realicen sobre máquinas de 64 bits con buena capacidad de entrada/salida así como memoria disponible. Casualmente, esto suele ser lo que todos deseamos para nuestros servidores de bases de datos por lo que no se requiere un tipo de servidor especializado diferente a los que utilicemos para bases de datos sin full-text search J Respecto al número de procesadores, diferentes pruebas muestran que el valor “dulce” está sobre 8 procesadores siendo complicado que a partir de esta cifra se obtenga un beneficio para la carga inicial. Obviamente, para resolver una gran cantidad de búsquedas por segundo tener más procesadores puede ayudarnos.
Por daros alguna referencia os indico que el tiempo de carga del índice en SQL Server 2000 fue de 1 hora y 40 minutos mientras que en SQL Server 2008 fue de 1 hora y 51 minutos. Aunque tenemos una diferencia de 11 minutos considero que no es significativa al tratarse de un portátil con características muy alejadas a las de un servidor. También destacar que la operación es muy intensiva en IO. Esto hizo que el portátil estuviera prácticamente “knoqueado” durante el proceso. Deberemos tener en cuenta esto para hacer estas operaciones masivas dentro de una ventana de mantenimiento o al menos en horas valle de poca actividad del sistema.
A continuación vamos a hacer algunas pruebas sobre algunas consultas puras full-text y algunas mixtas relacional + full-text para poder comparar el rendimiento del nuevo optimizador integrado. Para todas las pruebas haremos una limpieza de la caché previamente y lanzaremos 2 veces la consulta para comparar resultados en frio y en caliente.
Consultas Full-Text puras
Cuantos documentos con demonio tenemos:
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘daemon’)
Esta consulta nos devuelve 45 resultados en SQL Server 2008 y 44 en SQL Server 2000. El motivo de esto es que tanto los separadores de palabras así como el conjunto de sinónimos, etc. ha sido mejorado. Buscando el documento que existe de diferencia encontré que el problema es que en 2008 “daemon” se considera sinónimo de “demon” mientras que en SQL 2000 no. Muy probablemente ocurra algo parecido con “deamon”.
Cuantos documentos tenemos angelicales:
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘angel*’)
En este caso tenemos 820 resultados en ambos casos. Parece que nuestros textos son más angelicales que demoniacos J Veamos cuantos contienen ambos términos relativamente cerca.
Cuantos documentos tenemos de demonios y ángeles rondando cerca
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘angel NEAR daemon’)
En esta consulta obtenemos un resultado bastante sorprendente al encontrar SQL Server 2008 muchas más coincidencias de cercanía. No aparece nada en la documentación que indique que se ha aumentando la sensibilidad o el “alcance” del concepto de cercanía pero así parece ser. En SQL Server 2000 obtenemos únicamente 3 documentos mientras que en SQL Server 2008 obtenemos 26 documentos. Obviamente esto se verá reflejado en cierta forma en la entrada/salida necesaria para el conteo. Aunque podríamos extendernos utilizando muchos de las herramientas de las que tenemos disponibles (sinónimos, formas verbales, pesos, expresiones complejas, etc.) creo que este conjunto de consultas son suficientemente representativas. A continuación vamos a ver el rendimiento en consultas que combinan filtros sobre columnas de la tabla junto a filtros full-text. Para ello vamos a comprobarlo desde diferentes perspectivas de cardinalidad.
Consultas con filtros Full-Text combinados con relacional
Como filtro no selectivo para full-text utilizaremos “CONTAINS(texto,‘page’)” que devuelve 1242 filas mientras que para el relacional utilizaremos “fecha > ‘20060101’” que devuelve 24518 resultados. En el caso de filtro selectivo utilizaremos “CONTAINS(texto,‘torque’)” que devuelve 12 filas y para el relacional “pais=2” que devuelve 4 filas. Para cada escenario las consultas a utilizar serán las siguientes:
Full-text selectiva, relacional selectiva
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘torque’) AND pais=2
Full-text selectiva, relacional no selectiva
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘torque’) AND fecha > ‘20060101’
Full-text no selectiva, relacional selectiva
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘page’) AND pais=2
Full-text no selectiva, relacional no selectiva
SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘page’) AND fecha > ‘20060101’
Rendimiento 2000 vs 2008
En la siguiente tabla comparativa incluyo los valores medios de 4 ejecuciones en mi máquina de cada una de las consultas.
|
SQL SERVER 2000 |
SQL SERVER 2008 |
|||||||
| CONSULTA |
CACHE |
IO |
CPU |
Total |
IO |
CPU |
Total |
% Mejora |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘daemon’) | FRIA |
132 |
0 |
955 |
90 |
15 |
443 |
53,6% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘daemon’) | CALIENTE |
99 |
0 |
835 |
90 |
0 |
1 |
99,8% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘angel*’) | FRIA |
2235 |
0 |
1448 |
1640 |
32 |
622 |
57,1% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘angel*’) | CALIENTE |
1707 |
0 |
832 |
1640 |
10 |
14 |
98,3% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘angel NEAR daemon’) | FRIA |
6 |
0 |
1366 |
52 |
16 |
797 |
41,6% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘angel NEAR daemon’) | CALIENTE |
6 |
0 |
1015 |
52 |
4 |
5 |
99,5% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘torque’) AND pais=2 | FRIA |
30 |
10 |
879 |
86 |
31 |
563 |
35,9% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘torque’) AND pais=2 | CALIENTE |
30 |
0 |
50 |
86 |
0 |
54 |
-8,1% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘torque’) AND fecha > ‘20060101’ | FRIA |
36 |
16 |
121 |
86 |
10 |
495 |
-309,1% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘torque’) AND fecha > ‘20060101’ | CALIENTE |
30 |
0 |
118 |
86 |
0 |
57 |
51,6% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘page’) AND pais=2 | FRIA |
2 |
16 |
869 |
3192 |
31 |
829 |
4,6% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘page’) AND pais=2 | CALIENTE |
2 |
15 |
134 |
3192 |
16 |
64 |
52,2% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘page’) AND fecha > ‘20060101’ | FRIA |
2846 |
31 |
455 |
3192 |
16 |
972 |
-113,6% |
| SELECT COUNT(*) FROM documentos WHERE CONTAINS(texto,‘page’) AND fecha > ‘20060101’ | CALIENTE |
2580 |
0 |
54 |
3192 |
0 |
150 |
-177,7% |
Como podemos ver, en aquellas consultas puras full-text search el rendimiento es considerablemente mejor en todos los casos. Las mayores mejoras se encuentran cuando la consulta se ejecuta contra la caché en caliente en cuyo casi el coste de algunas de ellas es irrisorio mientras que en SQL Server 2000 necesita de un mínimo de 800ms en cada una de ellas. Cuando utilizamos escenarios mixtos donde se combinan tanto filtros relacionales como full-text la situación se vuelve mucho menos predecible. Cuando utilizamos un escenario donde ambos predicados son muy selectivos o lo es el predicado relacional el rendimiento de SQL Server 2008 está a la par o mejora bastante el de SQL Server 2000. El principal problema aparece cuando este predicado relacional no es selectivo (fecha > ‘20060101’). En ese caso, si el predicado fulltext es poco selectivo 2008 resulta ser el doble de lento que 2000. Si el predicado fulltext es selectivo, tenemos el escenario más radical de todos: con la caché fría 2008 es 3 veces más lento que 2000 y con la caché caliente es el doble de rápido.
En conclusión diremos que aunque estos resultados no son interpolables a un entorno de producción, hay ciertos indicios que la nueva funcionalidad de fulltext search muestra un mejor rendimiento en general cuando disponemos de los datos en caché o cuando la consulta es puramente sobre el índice full-text. Sin embargo, en escenarios mixtos y, especialmente en aquellas consultas únicas que puedan llegar con caché fría, el rendimiento puede ser significativamente más lento o algo mejor. Por tanto para conocer de forma fiable la ventaja de rendimiento que podemos obtener en nuestro entorno la única forma precisa es probando la carga actual de nuestro sistema SQL Server 2000 sobre una instalación de SQL Server 2008 y medir los resultados.
