performance - segundo - optimizar consultas sql server 2008 r2

Probando el rendimiento de las consultas en mysql

Question

Estoy intentando configurar un script que probaría el rendimiento de las consultas en un servidor mysql de desarrollo. Aquí hay más detalles:

• Tengo acceso root
• Soy el único usuario que accede al servidor.
• Principalmente interesado en el rendimiento de InnoDB.
• Las consultas que estoy optimizando son en su mayoría consultas de búsqueda ( SELECT ... LIKE ''%xy%'' )

Lo que quiero hacer es crear un entorno de prueba confiable para medir la velocidad de una sola consulta, libre de dependencias en otras variables.

Hasta ahora he estado usando SQL_NO_CACHE , pero a veces los resultados de tales pruebas también muestran un comportamiento de almacenamiento en caché: demoran mucho más en ejecutarse en la primera ejecución y demoran menos en las siguientes.

Si alguien puede explicar este comportamiento con todo detalle, podría seguir usando SQL_NO_CACHE ; Creo que podría deberse a la caché del sistema de archivos y / o al almacenamiento en caché de los índices utilizados para ejecutar la consulta, como explica this publicación. No me queda claro cuándo se invalidan el Buffer Pool y el Key Buffer o cómo podrían interferir con las pruebas.

Entonces, al no reiniciar el servidor mysql, ¿cómo recomendaría configurar un entorno que sea confiable para determinar si una consulta funciona mejor que la otra?

Answer 1

¿Has considerado utilizar Maatkit ? Una de sus capacidades con las que estoy un poco familiarizado es capturar datos de red de MySQL con tcpdump y procesar el volcado con mk-query-digest . Esta herramienta le permite mostrar algunos detalles detallados sobre cada consulta. Pero hay un montón de otras herramientas que deberían facilitar el análisis de consultas.

Answer 2

Citado en SQL_NO_CACHE : las opciones de SQL_NO_CACHE afectan el almacenamiento en caché de los resultados de la consulta en la caché de consulta . Si su tabla es bastante pequeña, es posible que la tabla ya esté en caché. Como simplemente evita el almacenamiento en caché de los resultados y no de las tablas, a veces obtiene el comportamiento descrito. Por lo tanto, como se indica en las otras publicaciones, debe vaciar las tablas entre las consultas.

Answer 3

Como lo sugiere el artículo vinculado, use FLUSH TABLES entre las ejecuciones de prueba para restablecer todo lo que pueda (especialmente el caché de consultas).

¿No deberían sus pruebas tener en cuenta que InnoDB tendrá diferentes estados durante el desempeño real, de modo que usted se interese en el desempeño agregado en múltiples ensayos? ¿Qué tan real será la prueba de rendimiento si desea restablecer InnoDB para cada prueba? La consulta que rechace porque tiene un rendimiento deficiente inmediatamente después del reinicio podría ser, con mucho, la mejor consulta después de que InnoDB se haya calentado un poco.

Si fuera usted, me concentraría en lo que el optimizador de consultas está haciendo por separado del rendimiento de InnoDB. Se ha escrito mucho sobre cómo ajustar InnoDB, pero es útil tener buenas consultas para comenzar.

También puede intentar medir el rendimiento con tablas MyISAM equivalentes, donde FLUSH TABLES realmente lo reiniciará a un punto de inicio casi idéntico.

¿Has intentado desactivar el caché de consultas por completo? Incluso con SQL_NO_CACHE, hay aproximadamente un 3% de penalización al tener la memoria caché de consulta activada.

Answer 4

Las consultas de texto completo en InnoDB son lentas (como LIKE "% query%"), no hay nada que pueda hacer para optimizarlas. Las soluciones varían desde pasar esa tabla en particular que está consultando a MyISAM para que pueda crear índices de texto completo (que innoDB no admite), a desnormalizar la fila en índices de búsqueda (no se recomienda), Doctrine ORM proporciona un ejemplo sencillo de cómo archivar esto: http://www.doctrine-project.org/documentation/manual/1_1/nl/behaviors:core-behaviors:searchable La solución "adecuada" a su problema sería indexar la información en la que está utilizando búsquedas de texto completo, con un Solución como Sphinx Search o Apache Solr.

Como se dijo anteriormente, debe considerar el estado de la memoria caché cuando se comparan los resultados, una memoria caché preparada proporciona consultas extremadamente eficaces. Debe considerar el porcentaje de aciertos de caché de una consulta en particular, incluso si es una consulta costosa, si tiene un índice de aciertos de caché del 99%, el rendimiento promedio será muy alto.

La optimización de las consultas no es una bala de plata, es posible que esté agregando complejidad a su aplicación en aras de las optimizaciones que, en general, en un entorno de producción, son insignificantes.

Considere su carga de trabajo, solucione problemas con las consultas frecuentes y sin rendimiento (use slow_query_log en mysql, no comience a optimizar las consultas a ciegas).

Answer 5

Puedes probar el banco de trabajo mysql, pensé que tenía un monitor de sentencias SQL para que puedas ver qué tan rápido es y por qué es rápido

Answer 6

Suponiendo que no puede optimizar la operación LIKE en sí, debe intentar optimizar la consulta base sin que minimicen el número de filas que deben verificarse.

Algunas cosas que podrían ser útiles para eso:

rows columna en EXPLAIN SELECT ... resultado. Entonces,

mysql> set profiling=1;
mysql> select sql_no_cache * from mytable;
...
mysql> show profile;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000063 |
| Opening tables | 0.000009 |
| System lock | 0.000002 |
| Table lock | 0.000005 |
| init | 0.000012 |
| optimizing | 0.000002 |
| statistics | 0.000007 |
| preparing | 0.000005 |
| executing | 0.000001 |
| Sending data | 0.001309 |
| end | 0.000003 |
| query end | 0.000001 |
| freeing items | 0.000016 |
| logging slow query | 0.000001 |
| cleaning up | 0.000001 |
+--------------------+----------+
15 rows in set (0.00 sec)

Entonces,

mysql> FLUSH STATUS;
mysql> select sql_no_cache * from mytable;
...
mysql> SHOW SESSION STATUS LIKE ''Select%'';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 1 |
+------------------------+-------+
5 rows in set (0.00 sec)

Y otro valor interesante es last_query_cost , que muestra lo caro que el optimizador estimó la consulta (el valor es el número de lecturas de páginas aleatorias):

mysql> SHOW STATUS LIKE ''last_query_cost'';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 2635.399000 |
+-----------------+-------------+
1 row in set (0.00 sec)

La documentación de MySQL es tu amiga.