MariaDB loca al 500% del tiempo de CPU

Question 1

Un uso elevado de la CPU generalmente significa una gran cantidad de escaneo de tablas en memoria.

Mirando una de sus consultas lentas:

Time=5.64s (5s)  Rows_sent=0.0 (0)  Rows_examined=4088258.0 (4088258)
SELECT  udownload_id AS retval 
FROM hb_udownloads 
WHERE  user_id = 415064  
AND file_id = 78499  
LIMIT 1

La consulta lee toda la tabla de 4 millones de filas pero regresa como máximounode ellos (y, en este caso, ninguno).
Necesitasíndicesen esta tabla para respaldar esta consulta. Uno compuesto user_idy file_idsería un buen comienzo.

Otro asesino del rendimiento aquí:

Time=20.58s (41s)  Rows_sent=4089261.5 (8178523)  Rows_examined=4089261.5 (8178523)
SELECT /*!40001 SQL_NO_CACHE */ * 
FROM `hb_udownloads`

Nunca usar "seleccionar *" en el código de producción.
Siempreespecifique las columnas que desee explícitamente. Supongo que esta tabla ha estado "creciendo" (ganando columnas) últimamente.
Bien, sin una cláusula dónde, esto se escaneará en la tabla de todos modos, pero eso plantea la pregunta: ¿qué va a hacer la [aplicación] cliente pobre?hacer¿Con las 4 millones de filas que le "arroja" esta consulta?

Answer

Un uso elevado de la CPU generalmente significa una gran cantidad de escaneo de tablas en memoria.

Mirando una de sus consultas lentas:

Time=5.64s (5s)  Rows_sent=0.0 (0)  Rows_examined=4088258.0 (4088258)
SELECT  udownload_id AS retval 
FROM hb_udownloads 
WHERE  user_id = 415064  
AND file_id = 78499  
LIMIT 1

La consulta lee toda la tabla de 4 millones de filas pero regresa como máximounode ellos (y, en este caso, ninguno).
Necesitasíndicesen esta tabla para respaldar esta consulta. Uno compuesto user_idy file_idsería un buen comienzo.

Otro asesino del rendimiento aquí:

Time=20.58s (41s)  Rows_sent=4089261.5 (8178523)  Rows_examined=4089261.5 (8178523)
SELECT /*!40001 SQL_NO_CACHE */ * 
FROM `hb_udownloads`

Nunca usar "seleccionar *" en el código de producción.
Siempreespecifique las columnas que desee explícitamente. Supongo que esta tabla ha estado "creciendo" (ganando columnas) últimamente.
Bien, sin una cláusula dónde, esto se escaneará en la tabla de todos modos, pero eso plantea la pregunta: ¿qué va a hacer la [aplicación] cliente pobre?hacer¿Con las 4 millones de filas que le "arroja" esta consulta?

Question 2

Parece que algunas de estas consultas podrían necesitar optimización.

Por ejemplo:

Rows_examined=10026558.0 (30079674), Rows_affected=0.0 (0), server***_fusion[server***_fusion]@localhost
  SELECT *,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=0) as selled,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=1) as used FROM tbl_keygroup
  order by `displayorder` asc

Esta consulta está escaneando 30000000 filas.

Es posible que puedas cambiar este, por ejemplo, a dos consultas:

SELECT * FROM tbl_keygroup ORDER by `displayorder` asc;
SELECT count(id) FROM tbl_swkey WHERE used IN (0,1) GROUP BY used;

Dado que la consulta en sí implica una combinación inútil y dos exploraciones completas de la tabla que podrían reducirse a la mitad.

Gran parte de lo que ha mostrado apunta a problemas con la optimización de consultas y yo estaría mirando ese registro de consultas lento para determinar cuál sería la mejor manera de recuperar esos datos con la menor cantidad de escaneos de tablas.

Probablemente también quieras indexar muchos de esos datos con el mismo propósito.

Answer

Parece que algunas de estas consultas podrían necesitar optimización.

Por ejemplo:

Rows_examined=10026558.0 (30079674), Rows_affected=0.0 (0), server***_fusion[server***_fusion]@localhost
  SELECT *,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=0) as selled,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=1) as used FROM tbl_keygroup
  order by `displayorder` asc

Esta consulta está escaneando 30000000 filas.

Es posible que puedas cambiar este, por ejemplo, a dos consultas:

SELECT * FROM tbl_keygroup ORDER by `displayorder` asc;
SELECT count(id) FROM tbl_swkey WHERE used IN (0,1) GROUP BY used;

Dado que la consulta en sí implica una combinación inútil y dos exploraciones completas de la tabla que podrían reducirse a la mitad.

Gran parte de lo que ha mostrado apunta a problemas con la optimización de consultas y yo estaría mirando ese registro de consultas lento para determinar cuál sería la mejor manera de recuperar esos datos con la menor cantidad de escaneos de tablas.

Probablemente también quieras indexar muchos de esos datos con el mismo propósito.

Question 3

** Esto debería hacer que el primero sea mucho más rápido:

SELECT  k.*, s.sold, s.used
    FROM ( SELECT group_id,
                  SUM(used = 0) AS sold,
                  SUM(used = 1) AS used
               FROM tbl_swkey
         ) AS s
    JOIN tbl_keygroup AS k  ON s.group_id = k.id
    ORDER BY displayorder

Y tiene

tbl_swkey:    INDEX(group_id, used)
tbl_keygroup: INDEX(displayorder)

Si necesita analizar más a fondo esta consulta, proporcione SHOW CREATE TABLEambas tablas, los tamaños de las tablas y EXPLAIN SELECT ....

** Este

SELECT  udownload_id AS retval
    FROM  hb_udownloads
    WHERE  user_id = 415064
      AND  file_id = 78499
    LIMIT  1

necesita un índice compuesto de 3 columnas para ser mucho más rápido:

INDEX(user_id, file_id, udownload_id)

¿No te importa qué fila coincidente obtienes? ¿O deberías agregar un ORDER BY? Si agrega un ORDER BY, es posible que deba cambiar mi consejo de índice.

** ¿Esto vino de mysqldump?

SELECT /*!40001 SQL_NO_CACHE */ * FROM `hb_udownloads`

Si no está satisfecho con lo invasivos que son los volcados, hay varias discusiones en dba.stackexchange.com sobre ese tema.

Answer

** Esto debería hacer que el primero sea mucho más rápido:

SELECT  k.*, s.sold, s.used
    FROM ( SELECT group_id,
                  SUM(used = 0) AS sold,
                  SUM(used = 1) AS used
               FROM tbl_swkey
         ) AS s
    JOIN tbl_keygroup AS k  ON s.group_id = k.id
    ORDER BY displayorder

Y tiene

tbl_swkey:    INDEX(group_id, used)
tbl_keygroup: INDEX(displayorder)

Si necesita analizar más a fondo esta consulta, proporcione SHOW CREATE TABLEambas tablas, los tamaños de las tablas y EXPLAIN SELECT ....

** Este

SELECT  udownload_id AS retval
    FROM  hb_udownloads
    WHERE  user_id = 415064
      AND  file_id = 78499
    LIMIT  1

necesita un índice compuesto de 3 columnas para ser mucho más rápido:

INDEX(user_id, file_id, udownload_id)

¿No te importa qué fila coincidente obtienes? ¿O deberías agregar un ORDER BY? Si agrega un ORDER BY, es posible que deba cambiar mi consejo de índice.

** ¿Esto vino de mysqldump?

SELECT /*!40001 SQL_NO_CACHE */ * FROM `hb_udownloads`

Si no está satisfecho con lo invasivos que son los volcados, hay varias discusiones en dba.stackexchange.com sobre ese tema.

MariaDB loca al 500% del tiempo de CPU

Respuesta1

Respuesta2

Respuesta3

información relacionada