MySQL. Почему медленно выполняется update?

Question

[litvinov-an]

В таблице 150 записей. Запросы на обновление значения выполняются порядка секунды. При загрузке страницы необходимо выполнить 9 таких запросов.

MySQL крутится на отдельном сервере, 4 гига оперативы. В чем может быть проблема? Не представляю в какую сторону копать.
В таблице проставлены индексы.

Мои личные предположения - что-то не так в конфиге MySQL. Его прилагаю.
[server]
[mysqld]
max_connection = 350
max_allowed_packet = 16M
key_buffer = 16M
innodb_buffer_pool_size = 2048M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
query_cache_size = 16M
[galera]
[embedded]
[mariadb]
[mariadb-10.1]

PhpMyAdmina на сервере нет.

Answer 1

[hx510b]

1. Убедиться, что для id есть индекс.
2. Возможно для поля views очень много индексов и они долго обновляются.
3. Слишком широкая таблица tbl_banner
3. Проверить нагрузку на сервере с БД - LA, нагрузка CPU, iowait - чем меньше значение - тем лучше.
LA<5..10, cpu < 100, iowait < 10...20
4. Жесткий диск работает "медленно" или неисправен.
5. Возможно, физический размер базы слишком велик и не помещается в буфер памяти. Это можно увидеть с помощью mysqltuner.pl
6. Возможно, что конфиг mysql не применяется - надо в этом убедиться.
Это типичные причины. Дальше надо смотреть конкретно.

Answer 2

[Иван Шумов]

Скорее всего тормозит подзапрос. Используйте explain для анализа выборки

Comments

[litvinov-an]

В консоли запрос выполняется моментально. Обработал при этом всего одну строку.

На сайте при этом просто космические цифры временных затрат.

[Иван Шумов]

litvinov-an, есть несколько вариантов.
1. Время соединения с базой данных
2. Суммарное время исполнения скрипта.

Не всегда проблема в базе данных. Частенько именно код проблема времени обработки.

[Roman Terekhin]

litvinov-an, на поле views индексов нет случайно?

[litvinov-an]

Roman T,
Это может так сильно вредить?

[Roman Terekhin]

litvinov-an, Конечно. Сами подумайте, на каждый update столбца views mysql делает перерасчет всего индекса, отсюда и задержки.
Если поле часто обновляется не нужно на него индекс вешать.
У вас в приложении есть выборка по этому полю?

[Иван Шумов]

Roman T, 150 записей, 9 update на одну страницу - да это же не нагрузка. там int 11 поле наверняка. Должно мгновенно отрабатывать

[Иван Шумов]

Давайте теперь по делу и займемся немного реверс инжинирингом. Вы делаете калькуляцию счетчика баннера. Более того, вы в коде рассчитываете значение и фиксируете его, не рассчитывая на конкурирующие запросы, что означает что у вас статистика не верная.

Совет #1: упрощайте. вы можете свести все к одному обновлению таблицы:

update tbl_banner set views = views + 1 where id in (1,2,3,4,5);

и вам не придется предварительно выбирать эту информацию.

Совет #2: Выкиньте это все к чертям. Поясняю почему на пальцах.

У вас есть просмотры баннеров - это одна сущность (какой баннер, в какой момент времени, на какой странице, какой userAgent, язык, IP ....)

Суточная статистика это другая сущность, как и недельная, месячная и вообще.
Статистика просмотра баннера это вообще отдельный разговор - цифра бессмысленная по тому как не несет за собой ничего кроме суммарной цифры за все периоды (но может вам очень надо).
Подобные цифры калькулируются как минимум периодически по крону. В идеале подключить очереди, а вообще для скорости можно, допустим, использовать clickhouse.

Совет #3. Эт оне просмотры баннеров, а рендеринг страницы с этим баннером. Подключите яндекс.метрику и отправляйте с клиента события. и никаких проблем с этой статистикой у вас не будет