Modificando el Performance en MySQL


En la web la parte pesada casi nunca es el código. Rara vez tenes que manejar datos muy grandes. La performance depende, en su mayoría de la velocidad del tiempo de respuesta de las consultas a la base de datos. Hoy vamos a ver que podemos hacer desde el código (no a nivel servidor) para mejorar esas respuestas.

Optimizar cuesta trabajo. Prueba y error, como en la vida misma, asi que no podemos pretender optimizar todas las queries. Como siempre vamos a ir por la regla del 80-20 (¡gracias Pareto!). Es decir, busquemos las queries mas pesadas. Hay varias maneras de conseguir las queries pesadas, con los logs de la base o haciendo profiling.

Una vez que las tengas, si son hechas con algún ORM (Hibernate, Doctrine, Propel) pasalas a mano. Con los parametros dinámicos tenes dos opciones,
los hardcodeas o haces algo más prolijo y usas SET, es decir las pones en variables de MySQL. No te olvides de que los datos que vas a usar tienen que ser lo mas reales posible y ser cosas que sepas que involucran muchos datos, como un SELECT que devuelva 30.000 filas.

Vayamos a los bifes.

Lo básico

No nos tenemos que olvidar de lo básico, sobre todo si estamos trabajando con un ORM, que nos desliga de muchas cosas

  • LIMIT: Trata de limitar todo. En caso de hacer subqueries o UNION, no te limites (cuack!) solo a la query principal. Probá limitar también las subqueries.
  • WHERE: las condiciones se ejecutan por orden. No son al azar. Siempre va de izquierda a derecha. Escribí las condiciones que usen índices y las condiciones que sepas que te acoten los resultados primero. Por ejemplo en vez de
    name LIKE "%nombre%" AND is_active = 1 AND id = 3

    cambialo a

    id = 3 AND is_active = 1 AND name LIKE "%nombre%"

    (si este ejemplo no es muy real que digamos pero es solo ilustrativo).
    Para esto es imprescindible tener bien armados los índices, pero en caso de que se te pasó o los haya hecho otro (como siempre la culpa es de otro) este es un buen momento para pensar que índices podés usar. No te preocupes, en breve te muestro un mejor momento para pensar índices.

  • JOIN: Trata de poner las condiciones específicas en el JOIN y no el WHERE así límitas los datos que se van a cruzar. También cuando tenes mas de un JOIN fijate de poner primero el que te encuentre menos datos, así trabaja menos. Mas o menos lo mismo que en el WHERE.

Estas pelotudeces pavadas te hacen bajar desde un 30% a un 5% de tiempo. Algo considerable en algunos casos, muy poco en la mayoría, pero a la larga sirven, y la relación costo-beneficio entre el tiempo que tardas en modificar la query y la performance que mejoras en la consulta es buena.

Como dije antes, esto es prueba error. Andá cambiando las cosas de lugar para ver donde funcionan mejor. Para eso es fundamental que no te olvides de ¡borrar el cache! (por si no lo sabías, MySQL cacheá los resultados de las consultas). Para esto:

FLUSH TABLES
RESET QUERY CACHE

MySQL, no te entiendo, ¡explicate!

Sería ideal que el motor nos diga que hace por dentro cuando hace una consulta. Bueno señores (y señoras por supuesto) la utopía existe. Para esto usamos el EXPLAIN. Simplemente lo agregas antes de la consulta y te trae que es lo que está haciendo por dentro. O sea si tenes la consulta:

SELECT * FROM node n INNER JOIN node_type nt ON n.type = nt.type;

escribis

EXPLAIN SELECT * FROM node n INNER JOIN node_type nt ON n.type = nt.type;

y te muestra algo como

+----+-------------+-------+------+---------------+-----------+---------+--------------------+------+-------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref                | rows | Extra       |
+----+-------------+-------+------+---------------+-----------+---------+--------------------+------+-------------+
|  1 | SIMPLE      | nt    | ALL  | PRIMARY       | NULL      | NULL    | NULL               |    8 |             |
|  1 | SIMPLE      | n     | ref  | node_type     | node_type | 14      | matco_site.nt.type |    3 | Using where |
+----+-------------+-------+------+---------------+-----------+---------+--------------------+------+-------------+

Fijate como cambia si le agrego un ORDER BY

EXPLAIN SELECT * FROM node n INNER JOIN node_type nt ON n.type = nt.type ORDER BY n.type ASC;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra          |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------+
|  1 | SIMPLE      | n     | ALL    | node_type     | NULL    | NULL    | NULL              |   19 | Using filesort |
|  1 | SIMPLE      | nt    | eq_ref | PRIMARY       | PRIMARY | 98      | matco_site.n.type |    1 |                |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------+

Bueno esto puede llevar mas investigación y trabajo, pero la clave es hacer que use índices (cuando pone index). Y que evite cosas pesadas como filesort. Les dejó un cheatsheet de EXPLAIN de la gente de Pythian.

Este si es un estupendo momento para repensar los índices (y a veces las tablas).

La locura de los locks

Los locks son grosso modo los bloqueos que el motor hace para evitar que se escriban o lean resultados mientras se estan escribiendo otros.

Si estas usando MyISAM (para hacer FULLTEXT, por ejemplo) tené en cuenta que el INSERT hace un Lock-Table o sea que si vas a hacer un
SELECT tiene que esperar a que se haga bien el INSERT lo que te puede bajar la performance en algunas consultas, asi que puede ser mejor poner un INSERT LOW_PRIORITY (lo mismo con UPDATE). Esto es para los motores que lockean por tabla (table-level: MyISAM, MEMORY, y MERGE) y no por fila (row-level: InnoDB).

Bueno fijate eso de los lockings por que a veces a pesar de ser row-level te molestan, por ejemplo cuando haces un GROUP BY. Imaginate que tenes una tabla Users y Users_Stuffs y en Users tenes un campo fecha Last que es el último ingreso, para saber si esta online, lo que estas haciendo es lockear siempre algunas filas, cosa que si haces muy seguido un SELECT en Users_Stuffs con JOIN a Users puede repercutir gravemente en la performance.

Otras consideraciones

Algo que me resultó práctico fue partir algunas tablas en Tabla y TablaHistorica. Por ejemplo si tenes algo que se ordene por fecha, guardas los ultimos en Tabla y a medida q pasen determinada fecha los pones en TablaHistorica. Asi evitas tener una tabla muy grande. Esto ya depende mucho de casos específicos de la aplicación, pero tené la idea de partir tablas en mente quizás te sirva. Otro ejemplo puede ser la tabla de usuarios separar donde tiene los datos más importantos de los datos que aparecen solamente en el perfil.

Si tenés algo que haces muchos INSERT (por ejemplo un LOG) y no necesitas tenerlos en la tabla 100% actualizada podes guardarlos juntos en otro lado y después hacer un BULK o un MULTIPLE INSERT que los hace mas rápido. Si la info no es totalmente necesaria o sea la podes” perder”, guardalos en Cache sino en un archivo (lo ideal sería guardarlos en Cache que es más rápido). De esta manera también te evitas los lockeos. Y el BULK lo podés hacer con un proceso en el cron en el momento que menos carga tenga el servidor.

Bueno puede ser que estos consejos no te cambien la vida y tu query siga tardando 13.73 segundos. No te olvides, lo importante es ir modificando cosas de la query para conocer bien que es lo que está perjudicando tu salud. Y si después de horas y horas no logras dar con la solución, planteale a tu cliente/líder la necesidad de más servidores de base de datos ;)

About these ads

Acerca de Francisco Castán

Creador, Diseñador, Investigador y Programador de Software Lenguajes Preferidos: C/C++, C#, Java, PHP, Python, PERL, Shell, JavaScript

Publicado el 12/01/2010 en MySQL y etiquetado en . Guarda el enlace permanente. Deja un comentario.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

Únete a otros 46 seguidores

A %d blogueros les gusta esto: