MariaDB сумасшедшая на 500% процессорного времени

Question 1

Высокая загрузка ЦП обычно означает большое количество сканирований таблиц в памяти.

Рассмотрим один из ваших медленных запросов:

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

Запрос считывает всю таблицу из 4 млн строк, но возвращает не болееодиниз них (а в данном случае вообще ни одного).
Вам нужноиндексына этой таблице для поддержки этого запроса. Составной на user_idи file_idбыл бы хорошим началом.

Еще один убийца производительности:

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

Никогда не используйте "выбирать *" в производственном коде.
Всегдаукажите столбцы, которые вы хотите явно. Я бы предположил, что эта таблица "растет" (приобретает столбцы) в последнее время.
Хорошо, без предложения where это в любом случае будет сканировать таблицу, но это наводит на вопрос - что будет делать бедный клиент [приложение]делатьс 4 млн строк, которые этот запрос «бросает» в него?

Answer

Высокая загрузка ЦП обычно означает большое количество сканирований таблиц в памяти.

Рассмотрим один из ваших медленных запросов:

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

Запрос считывает всю таблицу из 4 млн строк, но возвращает не болееодиниз них (а в данном случае вообще ни одного).
Вам нужноиндексына этой таблице для поддержки этого запроса. Составной на user_idи file_idбыл бы хорошим началом.

Еще один убийца производительности:

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

Никогда не используйте "выбирать *" в производственном коде.
Всегдаукажите столбцы, которые вы хотите явно. Я бы предположил, что эта таблица "растет" (приобретает столбцы) в последнее время.
Хорошо, без предложения where это в любом случае будет сканировать таблицу, но это наводит на вопрос - что будет делать бедный клиент [приложение]делатьс 4 млн строк, которые этот запрос «бросает» в него?

Question 2

Похоже, что некоторые из этих запросов нуждаются в оптимизации.

Например:

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

Этот запрос сканирует 30000000 строк.

Возможно, вы могли бы изменить этот запрос, например, на два:

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

Так как сам запрос включает в себя бесполезное соединение и два полных сканирования таблицы, которые можно было бы сократить вдвое.

Многое из того, что вы показали, указывает на проблемы с оптимизацией запросов, и я бы посмотрел на журнал медленных запросов, чтобы определить наилучший способ извлечения этих данных с наименьшим количеством сканирований таблиц.

Вероятно, вам также захочется проиндексировать значительную часть этих данных с той же целью.

Answer

Похоже, что некоторые из этих запросов нуждаются в оптимизации.

Например:

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

Этот запрос сканирует 30000000 строк.

Возможно, вы могли бы изменить этот запрос, например, на два:

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

Так как сам запрос включает в себя бесполезное соединение и два полных сканирования таблицы, которые можно было бы сократить вдвое.

Многое из того, что вы показали, указывает на проблемы с оптимизацией запросов, и я бы посмотрел на журнал медленных запросов, чтобы определить наилучший способ извлечения этих данных с наименьшим количеством сканирований таблиц.

Вероятно, вам также захочется проиндексировать значительную часть этих данных с той же целью.

Question 3

** Это должно сделать первый вариант намного быстрее:

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

И имеют

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

Если вам необходимо более подробно обсудить этот вопрос, предоставьте, пожалуйста, SHOW CREATE TABLEобе таблицы, размеры таблиц и EXPLAIN SELECT ....

** Этот

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

Для более быстрой работы необходим составной индекс из 3 столбцов:

INDEX(user_id, file_id, udownload_id)

Вам все равно, какую совпадающую строку вы получите? Или вам следует добавить ORDER BY? Если вы добавите ORDER BY, мой совет по индексу, возможно, придется изменить.

** Это откуда mysqldump?

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

Если вас не устраивает, насколько инвазивны дампы, на dba.stackexchange.com есть несколько обсуждений на эту тему.

Answer

** Это должно сделать первый вариант намного быстрее:

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

И имеют

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

Если вам необходимо более подробно обсудить этот вопрос, предоставьте, пожалуйста, SHOW CREATE TABLEобе таблицы, размеры таблиц и EXPLAIN SELECT ....

** Этот

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

Для более быстрой работы необходим составной индекс из 3 столбцов:

INDEX(user_id, file_id, udownload_id)

Вам все равно, какую совпадающую строку вы получите? Или вам следует добавить ORDER BY? Если вы добавите ORDER BY, мой совет по индексу, возможно, придется изменить.

** Это откуда mysqldump?

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

Если вас не устраивает, насколько инвазивны дампы, на dba.stackexchange.com есть несколько обсуждений на эту тему.

MariaDB сумасшедшая на 500% процессорного времени

решение1

решение2

решение3

Связанный контент