Una consulta sobre performance

[Ayesha]

¡Hola!

Tengo un VPS con 512MB de RAM garantizada y desde que actualicé la versión del programa que uso, he tenido problemas de performance con el mySQL

Revisando la cola de procesos con el WHM, encuentro que siempre que aparece una consulta que tiene la siguiente forma:

SELECT count( stories.sid ) as stories,
author.penname as penname,
author.uid as uid
FROM fanfiction_authors as author
LEFT JOIN fanfiction_authorprefs AS ap
ON author.uid = ap.uid
LEFT JOIN fanfiction_stories AS stories
ON stories.validated > 0
AND (FIND_IN_SET(author.uid, stories.coauthors) > 0 OR stories.uid = author.uid)
WHERE author.penname LIKE '%Ejemplo%' GROUP BY author.uid

Esa consulta genera que se cree una tabla me pone en status: "Copying to tmp table" y mientras lo hace, bloquea todas las demás consultas y el mySQL se cuelga. Suele tardar más de 1000 segundos.

Si hablamos de data, en stories tengo algo de 8000 registros e igual en authors.

Yo he modificado el my.cnf y he colocado :

tmp_table_size=324M
key_buffer=32M
join_buffer_size=4M

Pero el problema sigue y no consigo determinar qué parámetro más puedo cambiar.

Cualquier ayuda se agradecerá muchísimo.

[mhatter]

En principio, te sugiero que publiques tu pregunta en el foro de MySQL, en donde posiblemente te puedan dar una ayuda más precisa.

Ahora, respecto a lo que planteas, hay algunas cosas que te sugeriría revisaras:

1. Si deseas usar la memoria RAM en tu servidor para gestionar tablas temporales en MySQL, verifica si existe en tu máquina algún punto de montaje que opere sobre memoria (en sistemas unix usualmente se reportan como particiones tipo 'tmpfs'). Luego modifica tu configuración de MySQL para usar ese sistema de archivos.

Por ejemplo, si sabes que /var/tmp en tu máquina es de tipo tmpfs, edita my.cnf y usa esa ruta en el parámetro tmpdir:

Código:

tmpdir = /var/tmp/

2. Revisa tu consulta MySQL, y si te es posible, replantea la operación que estás haciendo. Esto puede tomar un poco de trabajo, pero si realmente es necesario en vista de las limitaciones que estás alcanzando, no hay otra manera.

Por ejemplo:

Código:

LEFT JOIN fanfiction_authorprefs AS ap
ON author.uid = ap.uid

¿Es este JOIN realmente necesario? Ya que la consulta no utiliza 'ap' para nada más, quizás puedas prescindir de esta parte de la consulta, depende de cómo trabaje tu sistema.

También:

Código:

FIND_IN_SET(author.uid, stories.coauthors)

Me parece un poco extraño usar un campo tipo SET para almacenar IDs de autores, que supongo pueden ser valores en un rango bien amplio. Probablemente haya una forma más eficiente (en espacio y tiempo de consulta) para hacer lo mismo que esa expresión FIND_IN_SET está haciendo actualmente.

3. Ejecuta EXPLAIN SELECT argumentos de tu consulta. Esto suele darte una idea más clara de qué tipo de operaciones hace MySQL para devolverte tus resultados. Si no encuentras mucho sentido en la información que recibes, pregunta a personas más familiarizadas con esto (el foro de MySQL puede ser un buen lugar :).

4. Al optimizar consultas SELECT a veces resulta útil convertir JOINs explícitos en declaraciones WHERE tradicionales. Por ejemplo, prueba cambiando tu consulta a algo como:

Código:

SELECT count( stories.sid ) as stories,
author.penname as penname,
author.uid as uid
FROM fanfiction_authors as author,
fanfiction_authorprefs AS ap,
fanfiction_stories AS stories
WHERE author.penname LIKE '%Ejemplo%'
AND author.uid = ap.uid
AND stories.validated > 0
AND (FIND_IN_SET(author.uid, stories.coauthors) > 0
     OR stories.uid = author.uid)
GROUP BY author.uid

Mira si el tiempo de ejecución cambia significativamente. También mira qué cambios observas usando EXPLAIN en esta nueva consulta, en comparación con la consulta original.

[Ayesha]

¡hola!

Muchas gracias... lo que hice fue desaparecer ese subquery del FIND IN SET, y ahora ya no se producen bloqueos.