Puñetero SQL
Llevaba meses persiguiendo al cabrón. Esporádicamente se ponía el mysql al 100% y no encontraba cuál era el sql que la generaba. Meses mirando con lupa todos los sql hasta que se me ocurrió habilitar el log-slow-queriesen el servidor.
Así encuentro que era select count(*) from links, votes where link_date > date_sub(now(), interval 6 hour) and link_author = XXXX and vote_type='links' and vote_link_id = link_id and vote_user_id > 0 and vote_value > 0;. La consulta en teoría está bien, hay un índice vote_type, vote_link_id,...pero aún así no hay manera que el mysql lo use en el “join” anterior, y se recorre los más de 5 millones de votos en la tabla.
mysql> select count(*) from links, votes use INDEX (vote_type) where link_date ...; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (5 min 19.30 sec)
Alucinante, como un sql en principio tan inocente puede molestar tanto y esconderse durante tanto tiempo. Y qué tonto que no he mirado lo del log-slow-queries antes.
A los que usen el menéame, que apliquen en siguiente parche si es que tienen ese código, antes que la base de datos sea más grande y haga plonk el servidor.
Ejem… idea para Free Software (o para organizar una petición a IBM para que lo libere
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.relg/bjnw0016.gif
Funcionalmente es una gozada. Tiene la pega de ser cerrado
pero lo de ver gráficos detallados y perfectamente anotados de todos los pasos de cada query es insuperable. Cuando te acostumbras a la notación, claro. Tambien genera reports en HTML, brutales.
Comment by guillem — Wednesday 7/3/2007 @ 0:39
log-slow-queries es un “lifesaver” para los que nunca hemos recibido un curso en bases de datos
Comment by RBA — Wednesday 7/3/2007 @ 1:11
Sipes, pero es que además me estudié con mucho detenimiento (y mucho explain, me ha costado horas y horas) los sql e índices de la base de datos. Este SQL está perfectamente adaptado a los índices creados para “votes” y no lo usa ni forzándolo:
UNIQUE KEY `vote_type` (`vote_type`,`vote_link_id`,`vote_user_id`,`vote_ip_int`)
Yo creo que es un error del engine, y quizás se solucione con un inner join, pero no me da la cabeza ahora.
Comment by gallir — Wednesday 7/3/2007 @ 1:21
En el curro tengo un servidor de aplicaciones que corre una Mysql y en algún que otro momento algunas consultas se comen la CPU. A través de Eduard Llull conocí tuning-primer.sh (no se exactamente la url ahora) y ayuda bastante a afinar el servidor Mysql, o al menos te da una serie de sugerencias al respecto.
Para que funcione correctamente, debe llevar la Mysql en marcha unos días. No creo que te indique nada que no sepas, pero quizá a algún lector le viene bien conocerlo.
Comment by David Martín :: Suki_ :: — Wednesday 7/3/2007 @ 9:13
Algunas cosas que yo probaría:
1.- Calcular a mano en PHP date_sub(now(), interval 6 hour) y ponerlo directamente en la query.
2.- Definir un índice por vote_type únicamente y otro por vote_user_id.
3.- Poner las condiciones más restrictivas (vote_type, link_author) al final de la query y las menos restrictivas (joins) al principio del where.
Explicación:
1.- El motor debería ser listo y calcularlo una sola vez, no una vez por fila a comparar. Pero por probar…
2.- El índice por tipo de voto te dividirá el número potencial de resultados… bueno, ya sabes lo que hace un índice. Si el número de valores distintos que puede alcanzar vote_type es inferior a 6 ó 7 no lo definiría. Había una fórmula para determinar el número óptimo de valores distintos para definir el índice, pero ahora no la tengo a mano y un vistazo por Google no me la da. Te la puedo buscar si te interesa.
3.- Esto es típico de Oracle. Antes, cuando no había optimizador de Queries integrado en el motor, las queries se evaluaban de abajo arriba y era mejor poner las condiciones más restrictivas siempre al final porque las menos restrictivas tenían que trabajar sobre menos filas.
El optimizador es bastante caprichoso y puede determinar no intervenir si no hay más de X tablas en el from por considerar que, a priori, la optimización sería más lenta que el “full scan”.
No sé cómo lo implementará MySQL, pero, nuevamente, por probar…
Por último, esa UNIQUE KEY que tienes es muy amplia. Probablemente el índice sea tan grande que no le baste la memoria (sobre todo si hay varias de esas ejecutándose a la vez) y necesite hacer las operaciones en disco. También probaría de definir ese índice por separado (3 índices).
Suerte!
Comment by Paco Ros — Wednesday 7/3/2007 @ 9:30
Como el número de notícias enviadas no creo que sea muy grande, a lo mejor prodrías probar con esta sentencia:
select count(1) from votes where
vote_type=’links’ and
vote_link_id in ( select link_id from links where link_date > date_sub(now(), interval 6 hour) and link_author = XXXX) and
vote_user_id > 0 and vote_value > 0;
Comment by Biel Frontera — Wednesday 7/3/2007 @ 11:12
A mí una vez me dijo un DBA que evitase a toda costa los joins. Vale que a veces es necesario, y que en aquel caso se trataba de otra base de datos, no de mysql, que hace estas cosas con bastante rapidez, pero bueno, ahí queda la cosa por si sirve.
A mi las queries con más de 3 condicionales ya me da cosa usarlas, y esta que pones tiene 6.
Otra cosa.. ¿Eso de vote_type=’links’ ??? ¿No seria mucho mas eficaz usar un tinyint o algo así para especificar tipos de algo, en lugar de un literal?
Comment by RBA — Wednesday 7/3/2007 @ 21:53
> Otra cosa.. ¿Eso de vote_type=’links’ ??? ¿No seria mucho mas eficaz usar un tinyint o algo así para especificar tipos de algo, en lugar de un literal?
No es un literal, es un “enum”.
Comment by gallir — Wednesday 7/3/2007 @ 21:56
Me imaginaba algo así pero estaba vago para ponerme a indagar
Comment by RBA — Thursday 8/3/2007 @ 1:17
Lo mejor el parche
“Tu malo encerrado entre /* */ y calladito que ya m’has jodio basante” 
Comment by Agustí — Thursday 8/3/2007 @ 8:27