Помогите с оптимизацией запросов MySQL

danneo

Честный
Регистрация
13 Ноя 2007
Сообщения
1.521
Реакции
121
Смотрю на логи, но понять ничего не могу. Проблема в том, что по статистике запросы грузять базу на хостинге, на 700% от допустимого. Жалобы от сапорта нет, но все же...
Запросы на апдейт идут каждве 15 секунд вроде, пока юзер смотрит страницу. Но это всего 1 запрос, что плохого то?
Нужно как-то оптимизировать.

Почему запрос обновления состоит из двух (update и select), разбитых строками:
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
И почему INSERT запрос работает от 0,1 до 10+сек? Обычный INSERT, просто вставка без других подзапросов и вычислений. Есть только обработка IP адреса через INET_ATON(). Но не в этом же пробелма?
 
Последнее редактирование:
Почему запрос обновления состоит из двух (update и select), разбитых строками
Это не запрос разбит. Сам запрос такой:
Код:
UPDATE dle_rev_views SET
                   `action` = CONCAT_WS('|', `action`, '49,1434,0|49,1441,0|49,1447,0|49,1454,0' ),
                   `time_view` = 1454
             
                  WHERE `userid` = 0
                  AND `postid` = 0
                  AND dle_rev_views.sid =  'ab66809906207c49714eb43d55c25a60'\G
Далее штука, которая сформировала этот лог (напишите что за штука, предполагаю что это из набора pt-query), дает вам подсказку, как посмотреть индексы и статистику выполнения этого запроса с помощью EXPLAIN.
Дело в том, что "EXPLAIN UPDATE ..." не работает на мускуле до версии 5.6.3, только "EXPLAIN SELECT", и поэтому этот запрос с UPDATE трансформировали, заменив UPDATE на SELECT.
Выполните
Код:
EXPLAIN
select
                   `action` = CONCAT_WS('|', `action`, '49,1434,0|49,1441,0|49,1447,0|49,1454,0' ),
                   `time_view` = 1454 from dle_rev_views where  `userid` = 0
                  AND `postid` = 0
                  AND dle_rev_views.sid =  'ab66809906207c49714eb43d55c25a60'\G
И получите интересную картинку, на основе которой сможете заоптимизировать запрос. Когда люди просят помощи с оптимизацией, я всегда пишу, чтобы предоставили "EXPLAIN SELECT ..." и схему таблиц.
И почему INSERT запрос работает от 0,1 до 10+сек?
Есть только обработка IP адреса через INET_ATON(). Но не в этом же пробелма?
Правильно думаете, INET_ATON выполняется мгновенно, запрос один, 10 секунд, как и 0,1 - это слишком много для него.
Полагаю, что таблица огромная, и на ней слишком много индексов.
Поэтому
Код:
SHOW CREATE TABLE dle_rev_views;
и
Код:
SELECT COUNT(*) FROM dle_rev_views;
в студию
 
Последнее редактирование:
Поэтому
Код:
SHOW CREATE TABLE dle_rev_views;
и
Код:
SELECT COUNT(*) FROM dle_rev_views;
в студию
1-й запрос:
2-й запрос посчитал кол-в строк в 60255 штук :) Так много, потому что это сбор свой метрики :)
 
1. Переделать Engine

Возможно ваш хостер настроил мускул на быстродействие с хранилищем Innodb, а для MyISAM отвел совсем скромные буферы. Попробуем перевести все таблицы. Это делается через

Код:
ALTER TABLE `таблица` ENGINE=INNODB;

Вообще я с MyISAM уже не работаю, поэтому советы ниже работают для INNODB

2. Чтобы быстрее шло обновление, нужно добавить составной индекс:
Код:
ALTER TABLE `dle_rev_views` ADD KEY `upd_idx` (sid, postid, userid);
Порядок (sid, postid, userid) я выбрал такой, потому что самым уникальным (больше вариантов данных) мне кажется sid, и затем уже postid, userid. Вообще проверьте это утверждение - посмотрите сколько уникальных значений:

Код:
select count(distinct(sid)) from dle_rev_views;

Аналогично для остальных полей: postid, userid. Там где вариантов больше - должны идти первыми в составном индексе.

Также, конкретно для ускорения этого запроса, необходимо:
  • `postid` перевести в UNSIGNED
  • `userid` перевести в NOT NULL - если логика приложения не поломается. Посмотрите, есть ли вообще NULL в данных.
Это улучшит работу с этим индексом `upd_idx`.

3. Запрос на вставку.

Он составлен с ошибками. На MySQL 5.7.22 он вообще не выполнился:
Код:
ERROR 1265 (01000): Data truncated for column 'cost_view' at row 1

На версии 5.5.41 выдает 4 предупреждения. Попробуйте выполнить вручную этот запрос в консоли MySQL и потом SHOW WARNINGS; Получите это:

Код:
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1364 | Field 'time_view' doesn't have a default value |
| Warning | 1364 | Field 'action' doesn't have a default value    |
| Warning | 1364 | Field 'params' doesn't have a default value    |
| Warning | 1265 | Data truncated for column 'cost_view' at row 1 |
+---------+------+------------------------------------------------+

Я полагал, что вставка была долгой по причине кучи индексов, которые при каждой вставке перестаиваются. Теперь мое предположение - тормозит лог предупреждений.

Выполните эти два запроса и вывод - сюда:

Код:
SHOW VARIABLES LIKE '%warn%';
SHOW VARIABLES LIKE '%log_error%';
 
Последнее редактирование:
Попробуем перевести все таблицы. Это делается через
Это безопасно? Хуже не будет? :)
select count(distinct(sid)) from dle_rev_views;
Получается: 61549

Он составлен с ошибками. На MySQL 5.7.22 он вообще не выполнился:
Почему так? По умолчанию же указаны данные: "0.00"
Попробовал через phpmyadmin, вставка прошла. Может быть из-за версии mysql...

Выполните эти два запроса и вывод - сюда:

SHOW VARIABLES LIKE '%warn%';
SHOW VARIABLES LIKE '%log_error%';

Получается:
Variable_name | Value
log_warnings | 2
sql_warnings | OFF
warning_count | 0
и еще:
Variable_name | Value
binlog_error_action | ABORT_SERVER
log_error | /var/log/mysql/error.log
log_error_verbosity | 3
 
Еще раз перечитал сообщение. Вставка в таблицу на 60 тыс записей, без доп индексов за 10 секунд - это нонсенс. У вас какой хостинг - шаред? Они могут искусственно занижать скорость, либо сервер БД перегружен.
В любом случае, меняйте хостинг, или просите дать другой сервак баз данных.

Раз начал отвечать на вопросы, оставлю ответы:

Это безопасно? Хуже не будет?
Безопасно. Сделайте
Код:
SHOW ENGINES;
В колонке Support будет показано, поддерживается или нет. И ENGINE всегда можно вернуть назад на MyISAM.
Получается: 61549
По другим колонкам будущего индекса (postid, userid) тоже.
Почему так? По умолчанию же указаны данные: "0.00"
Потому что
Код:
ERROR 1265 (01000:( Data truncated for column 'cost_view' at row 1
Более внимательно посмотрите запрос INSERT и поймете.
Может быть из-за версии mysql...
Да, в старых версиях вставляется с 4 предупреждениями, которые пишутся в лог. В новой версии запрос вообще не работает.
Это мое предположение - запрос выполняется дольше из-за предупреждений, которые сбрасываются в лог (а он ведется). Попробуем исправить, чтобы вставка происходила без предупреждений.
Вообще я сомневаюсь что логирование ошибок настолько может увеличить время вставки. Милисекунды - да, секунды - дико сомневаюсь.
 
У вас какой хостинг - шаред?
да

select count(distinct(sid)) from dle_rev_views;
Получается:
sid - 63482
postid - 887
userid - 58

ERROR 1265 (01000:( Data truncated for column 'cost_view' at row 1
Более внимательно посмотрите запрос INSERT и поймете.
Еще бы быть профи... посмотрел, ничего не увидел :) Нужно указать значения по умолчанию вместо пустых значений?
Что в инсерте не так, можете подсказать?
 
Получается:
sid - 63482
postid - 887
userid - 58
Вот видите, самое уникальное значение - sid, затем postid, затем userid. Поэтому именно в таком порядке будет самый эффективный индекс:
Код:
ALTER TABLE `dle_rev_views` ADD KEY `upd_idx` (sid, postid, userid);
Если в условии `WHERE` есть `sid = ...` (а он есть в вашем запросе с UPDATE), то сразу все количество строк "будет поделено" на 63482 частей, возьмется подходящая часть, и уже среди нее будет вестись поиск по `postid`, который еще раз поделит этот кусочек на 887 частей, и в конце уже поиск по `userid`. Так работают составные индексы.

Код:
`userid` int(11) DEFAULT NULL,

поиск по полю, которое может быть NULL, ведется медленнее, так как идет дополнительная проверка на NULL. Так индекс будет работать лучше, но нужно быть осторожным и не сломать логику CMS:
Код:
ALTER TABLE `dle_rev_views` MODIFY `userid` int(11) UNSIGNED NOT NULL DEFAULT '0';

Я еще добавил "UNSIGNED", с беззнаковыми числами быстрее. Ну и для `postid`можно убрать знак:

Код:
ALTER TABLE `dle_rev_views` MODIFY `postid` int(11) UNSIGNED NOT NULL;

Еще бы быть профи... посмотрел, ничего не увидел

поле `cost_view` должно быть числом:
Код:
`cost_view` float(2,2) NOT NULL DEFAULT '0.00' COMMENT '',

вместо этого вставляется строка:

Код:
INSERT INTO dle_rev_views
(... `cost_view` ...) VALUES
(... '' ...)

MySQL преобразует строку в "0", и выдает предупреждение. С полем `num_letters` - аналогично.
Правильно:

Код:
INSERT INTO dle_rev_views
(... `cost_view` ...) VALUES
(... 0 ...)

На шаред хостинге у вас очень мало возможностей. Я бы посмотрел через htop и mytop загрузку сервера, чтобы прийти к окончательному выводу. Но на шареде этого не сделать. Меняйте сервер БД, такая долгая вставка - не порядок.
 
Автор, у меня подобная проблема на хостинге. У вас получилось? На сколько повысилась производительность?
 
Назад
Сверху