mysql problema de rendimiento en consulta. para expertos

Estimados.
he realizado un website muy grande, que tiene unas 50.000 visitas diarias.
el sitio corre en un servidor dedicado, en un NOC de europa. solo ese site en el server.

ultimamente hay problemas con el server se pone extremadamente lento y los admin me dicen que lo que esta generando la sobrecarga es una consulta en el back end (al back end solo ingresan 10 personas simultaneas)

la consulta que me dicen que esta tirando abajo el server es esta:
----------------------------------------------------------------------------------------------------
SELECT
content_news.content_news_id,
content_news.config_idioma_id,
content_news.content_news_cat_id,
content_news.fecha_creado,
content_news.fecha_edit,
content_news.prioridad,
content_news.titulo,
content_news.copete,
content_news.cuerpo,
content_news.fuente,
content_news.url_ext,
content_news.permalink,
content_news.tags,
content_news.estado,
content_news.fecha_prog,
content_news.comentarios,
content_news.gal_foto,
content_news.gal_vid,
content_news.gal_audio,
content_news.cant_comentarios,
content_news.cant_votos,
content_news.cant_puntos,
content_news.cant_visitas,
content_news.activo,
content_news_files.titulo AS alt,
content_news_files.archivo,
content_news_cat.content_news_cat_id_2,
content_news_cat.categoria

FROM content_news_cat

LEFT OUTER JOIN
content_news ON (content_news_cat.content_news_cat_id =
content_news.content_news_cat_id)

LEFT OUTER JOIN content_news_files
ON (content_news.content_news_id = content_news_files.content_news_id)

WHERE
(content_news.content_news_id IS NOT NULL)
AND
(content_news.activo = 1) GROUP BY content_news.content_news_id

ORDER BY
content_news.fecha_creado DESC,
content_news_files.tipo ASC
----------------------------------------------------------------------------------------------------

todos los campos dentro de clausulas tienen sus respectivos indicies asi que problema de indices en teoria no es.

Estoy cansado de pelear con los del host que me patean el problema a mi.

algun experto me puede dar la palabra final? o algun metodo de optimizar esto? ya probe todos los metodos que encontre.

Saludos y muchas gracias de antemano
Rodrigo

Mmm, Preguntas:

¿Un content_news siempre tiene un content_news_cat respectivo cierto?
Si la respuesta es positiva, usa INNER JOIN.

Lo mismo aplica para content_news_files.

Para cuando la respuesta es negativa, deja el LEFT.


Necesitas compatibilidad con ODBC?.
Deja el OUTER en caso de que la respuesta sea positiva.

`N`.content_news_id IS NOT NULL

Esa condición me parece inútil.


Por favor, usa alias. Mira que como se ve tu consulta con alias.

Código :

SELECT
   `N`.content_news_id,
   `N`.config_idioma_id,
   `N`.content_news_cat_id,
   `N`.fecha_creado,
   `N`.fecha_edit,
   `N`.prioridad,
   `N`.titulo,
   `N`.copete,
   `N`.cuerpo,
   `N`.fuente,
   `N`.url_ext,
   `N`.permalink,
   `N`.tags,
   `N`.estado,
   `N`.fecha_prog,
   `N`.comentarios,
   `N`.gal_foto,
   `N`.gal_vid,
   `N`.gal_audio,
   `N`.cant_comentarios,
   `N`.cant_votos,
   `N`.cant_puntos,
   `N`.cant_visitas,
   `N`.activo,
   `F`.titulo AS alt,
   `F`.archivo,
   `C`  .content_news_cat_id_2,
   `C`  .categoria
FROM content_news_cat `C`
LEFT JOIN content_news `N` ON `C`.content_news_cat_id = `N`.content_news_cat_id
LEFT JOIN content_news_files `F` ON (`N`.content_news_id = `F`.content_news_id)
WHERE
   `N`.content_news_id IS NOT NULL
      AND
         `N`.activo = 1 
GROUP BY `N`.content_news_id
ORDER BY
`N`.fecha_creado DESC,
`F`.tipo ASC

saludos

Antes que nada! muchas gracias!!! este tema me esta volviendo loco, los del server me tiran la pelota a mi pero estoy 100% seguro que esto no esta tirando abajo el server, ya que al back end entran 10 pers simultaneas maximo.

con respecto a los LEFT, si o si deben ir, ya que aunque no haya dependencias necesito mostrar las noticias huerfanas.

lo de los outer no lo sabia. eso puede relentizar todo?

lo de N`.content_news_id IS NOT NULL eso lo usaba por q la db me la pasaron y en un principio tenia registros sin id. eso puede joder?

muchas gracias de nuevo por darme una mano con esto!!
Saludos!
Rodrigo

Se me olvidaba usa LIMIT, no creo que quieras/necesites mostrar todas las noticias en una sola vista.

saludos

si el limit lo tien, me olvide de adjuntarlo por q lo usa segun la paginacion
seria limit 0,50
que me quedaria por revisar?
muchas gracias por la onda!

me olvidaba, la tabla news tiene 50.000 registros

ademas de lo que dice Maikel debes colocar los indices a los campos que estas utilizando para unir las tablas, segun el ejemplo de Maikel serian indices (o PK) :

• `C`.content_news_cat_id
  
• `N`.content_news_cat_id
  
• `F`.content_news_cat_id
  

ademas seria conveniente hacerle una optimización a las tablas de la DB

Gracias Inyaka y Maikel!!!

los indices de todos los campos que stan dentro de una clausula where, order, join tienen sus respectivos indices,
que otra cosa puede ser?

por q me imagino que solo estan pateando la pelota de su server pedorro y trasladando la culpa a mi,
gracias!
Rod

Ejecuta el Query en phpmyadmin y dime cuanto se tarda?

Si es mas de medio segundo si es lento... veo por ahí un par de cosas que podrían mejorar el rendimiento, pero antes de ponerme a hacertelo necesito ver que tanto se tarda y ver si es tu query o el pichurriento server.

ejecutandola en el server (la ejecute en un soft de admin sql remoto EMS) me tira 0.9 seg que es alto para una tabla pedorra, pero esta tiene 50.000 records...
ese tiempo lo tira con limit sin limit se va a 1.6
Gracias!

¿optimizaste las tablas?
existe un comando para ello y en phpmyadmin existe un boton para ello

Ojo esa consulta no la deberias ejecutar sin LIMIT, a eso me refería yo, para la paginación está SQL_CALC_FOUND_ROWS

saludos

Prueba con este y dime cuanto tarda en ejecutarse:

Código :

SELECT
   `N`.content_news_id,
   `N`.config_idioma_id,
   `N`.content_news_cat_id,
   `N`.fecha_creado,
   `N`.fecha_edit,
   `N`.prioridad,
   `N`.titulo,
   `N`.copete,
   `N`.cuerpo,
   `N`.fuente,
   `N`.url_ext,
   `N`.permalink,
   `N`.tags,
   `N`.estado,
   `N`.fecha_prog,
   `N`.comentarios,
   `N`.gal_foto,
   `N`.gal_vid,
   `N`.gal_audio,
   `N`.cant_comentarios,
   `N`.cant_votos,
   `N`.cant_puntos,
   `N`.cant_visitas,
   `N`.activo,
   `F`.titulo AS alt,
   `F`.archivo,
   `C`  .content_news_cat_id_2,
   `C`  .categoria
FROM content_news `N`
INNER JOIN  content_news_cat `C`
ON `N`.content_news_cat_id = `C`.content_news_cat_id
LEFT JOIN content_news_files `F` ON (`N`.content_news_id = `F`.content_news_id)
WHERE
         `N`.activo = 1 
GROUP BY `N`.content_news_id
ORDER BY
`N`.fecha_creado DESC,
`F`.tipo ASC

Gracias por responder.
tiempos con el query que me pasaste:
60 rows fetched (211,84 sec)

con limit 50
50 rows fetched (4,39 sec)

mi consulta
60 rows fetched (148,44 sec)
con limit 50
50 rows fetched (4,80 sec)

como veras los numeros son cualquier cosa. y se supone que a esta hora no hay casi nadie visitandolo. asi que si no ven un gran error en esa consulta me parece que me estan haciendo pagar los platos que rompieron los otros.

otro dato importante, el site esta funcionando hace meses bien y esto pasa ultimamente, y los usuarios se mantienen en cantidad desde que subimos este sitio

el sitio es www.calciomercato.it
en fin, uds seguro saben mas que yo y todos los consejos que me dieron ya los habia aplicado antes, asi que creo que me confirman que esta ok no?

si se les ocurre una razon que sea mi culpa!! aja se los agradecera, esto me esta quitando el sueño!!!!
gracias a todos y buen fin de semana!!!
Rodrigo

Holy shit! al menos 2 minutos la consulta???? no, no, eso es increible, para una consulta tan sencilla como esa

claro. pienso lo mismo. muchas gracias de toda formas!!!! si se les ocurre algo agradecido!
Saludos desde Argentina
Rodrigo

Ese no es problema de capacidad server, a menos que fuera un celeron primera generación. Hay algo en el query y en la DB, seguro, ese tiempo es exagerado. Lo último que se me ocurre, mandame la BD, la importo y pruebo con queries y te lo hago ir mas rápido. Mandame en un mensaje privado un zip con la BD(cambia datos que consideres confidenciales) y pues probando seguro lo bajamos a menos de un segundo.

Así adivinando se me hace difícil de ver que es, pero estoy casi seguro que esos tiempos se pueden reducir. Tu también prueba en tu PC, si en tu PC también se tarda 4 segundos, el problema esta en tu DB y tu query.

Si la MISMA BD con el mismo número de registros en tu pc, se ejecuta en 0.5 segundos, pero en el server, sigue lento(4 segundos) entonces si es problema del server, pero como te digo, no creo que sea algo del server.

ejecuta este codigo y luego me dices cuanto se demora la consulta de Dano, a mi también me paso algo parecido hace un tiempo

Código :

OPTIMIZE TABLE `content_news` , `content_news_cat` , `content_news_files`

Hola Gente.
Las tablas ya las repare y optimize y sigue igual.
localmente me funciona bien. el problema es cuando hay varios users simultaneos...
una de las cosas que hice es pasar todo de innodb a MyISAM para optimizar un poco y evitar LOCKS

Dano:
gracias por la buena onda, en un rato te paso el sql con las tablas relacionadas. son 7mb
en el site hay consultas mil veces mas complejas que la que los del server me dicen que hay problemas, si queres te paso alguna otra consulta mas.

todas las consultas las hacemos con EMS SQL manager, desde hace años. que segun las malas lenguas es excelente para construir consultas.

en fin sigo perdido. gracias por la ayuda!

Saludos a Todos
Rodrigo

En local el query que te dí corre en 0.6495 sec y ya es muy difícil reducir ese tiempo. Si ese query que te dí en el server corre en mas de 4 segundos, debe ser por la cantidad de usuarios concurrentes y que por lo tanto implica la ejecución de varios queries al mismo tiempo.

Creo que de tu parte lo único que queda es checar que el server de mysql tenga activo el cache de queries, si es que no lo tienes activo eso te ayudará. También te queda dar una revisada haber si no hay peticiones a mysql que podrías evitar.

De la parte de hosting, pues si ni con el cache de query mejora(o tal vez ya estaba activo) pues lo único será cambiar a un servidor de mayor capacidad.

Estimados!
antes que nada sorry por no seguir el hilo antes, estuve enfermo con gripe de verano!!!
Muchas gracias por sus aportes amigos! voy a probar lo que dicen a ver que pasa.

por otro lado, ahora me dicen que la consulta que estaba mal no era la posteada, era esta:

---------------------------------------------------------------------------------------------------------
SELECT
content_news.content_news_id,
content_news.config_idioma_id,
content_news.content_news_cat_id,
content_news.fecha_creado,
content_news.fecha_edit,
content_news.prioridad,
content_news.titulo,
content_news.copete,
content_news.cuerpo,
content_news.fuente,
content_news.url_ext,
content_news.permalink,
content_news.tags,
content_news.estado,
content_news.fecha_prog,
content_news.comentarios,
content_news.gal_foto,
content_news.gal_vid,
content_news.gal_audio,
content_news.cant_comentarios,
content_news.cant_votos,
content_news.cant_puntos,
content_news.cant_visitas,
content_news.activo,
content_news_files.titulo AS alt,
content_news_files.archivo,
content_news_cat.content_news_cat_id_2,
content_news_cat.categoria
FROM
content_news_cat
LEFT OUTER JOIN content_news ON (content_news_cat.content_news_cat_id = content_news.content_news_cat_id)
LEFT OUTER JOIN content_news_files ON (content_news.content_news_id = content_news_files.content_news_id AND content_news_files.tipo = 1)
WHERE
(content_news.content_news_id IS NOT NULL) AND
(content_news_cat.content_news_cat_id_2 = 11) AND
(content_news.activo = 1)
GROUP BY
content_news.content_news_id
ORDER BY
content_news.fecha_creado DESC, content_news_files.tipo ASC
---------------------------------------------------------------------------------------------------------

asi que de nuevo en cero... me ayudan?
desde ya , como siempre! super agradecido por la ayuda!!
gracias!
Saludos
Rodrigo

Ese query sigue ejecutandose dentro de un rango normal. Creo que el problema definitivamente es el que server no puede ya con la carga de usuarios, hay que mejorar el servidor.

Muchas gracias DANO, realmente me ayudaste mucho. espero poder devolverte la ayuda algun dia.
Saludos y muchas gracias!