Медленная работа агрегации

[rkomissarenko]

Заметил, что запускаемый каждые 15 минут скрипт агрегации данных (/pinboard/console aggregate) выполняется по времени как раз примерно эти же 15 минут.
Начал разбираться. Дошел до файла /root/pinboard/src/Pinboard/Command/AggregateCommand.php.
Увидел запрос на вставку данных в ipm_report_2_by_hostname_and_server. Он склеивается в один для всех серверов и выполняется. У меня он выполняется 11 минут.
Берём малый фрагмент запроса:

SELECT r2.server_name, r2.hostname,
(SELECT r.req_time FROM request r WHERE r.server_name = r2.server_name AND r.hostname = r2.hostname ORDER BY r.req_time DESC LIMIT 352, 1) as req_time_90
FROM request r2
WHERE r2.server_name = "{}" and r2.hostname = "{}";

Выполняется 6 секунд. Таблица request содержит 1 млн. записей (как раз где-то 20 минут сбора). Данные собираются с 15 серверов, по 6 хостов на каждом.
Проблема в отсутствии индексов в таблице request, о чем и уведомляет сам MySQL. Вручную индексы добавить не получается - #1121 - Table handler doesn't support NULL in given index. Please change column 'hostname' to be NOT NULL or use another handler.

[muxx]

А какие объемы данных?

[muxx]

Может быть, еще требуется посмотреть в сторону тюнинга innodb.

[rkomissarenko]

Обновил стартовое сообщение, ответив на вопрос.

[rkomissarenko]

Может быть, еще требуется посмотреть в сторону тюнинга innodb.

В данном узком месте работа идёт с таблицей типа PINBA. Влияют ли настройки innodb на неё? MySQL оттюнингован. Понятно, что до совершенства далеко, но есть сомнения, что тюнинг решит данную ситуацию.

Первое, что приходит в голову - запускать агрегацию почаще. И поменьше держать данных в request. Есть ли какие-то другие способы?

Что-то крутить и править в структуре БД (менять типы таблиц, значения по умолчанию и т. д.) я не решаюсь, т. к. не знаю логику работы с ней.

[muxx]

Да, innodb – это только по части таблиц Pinboard. Таблицы pinba лежат в памяти, для них не предусмотрено тюннинга.. Аггрегацию лично я делаю раз в 10 минут, но у меня на порядок меньше данных. А не понятно, что тормозит, выборка или вставка?

[rkomissarenko]

А не понятно, что тормозит, выборка или вставка?

Тормозит выборка из-за отсутствия индексов в таблице request. Практически уверен, были бы индексы, проблемы бы не было.

Я указал в первом сообщении фрагмент скрипта с подзапросом (квадратичная сложность). Он рассчитывается за 3-6 секунд. В одном запросе на вставку - 12 подзапросов. А всего около 90 insert-ов (на моей базе).

Решение - это не допускать разрастания таблицы request больше... сотни тысяч строк. Либо делать несколько pinba-серверов. Но мне кажется можно выжать гораздо большую производительность для большого объёма данных.

[muxx]

Таблица request в памяти и обновляется в реалтайме. Индексы будут дольше перестраиваться.

[muxx]

Я только вижу перенести это на сторону pinba. У нее есть возможность построения отчетов по персентилям, т.е. она в реалтайме будет и аггрегированные данные расчитывать, а pinboard будет только выбирать их и складывать в свои таблицы. На момент разработки Pinboard их еще не было, но надо смотреть, всего ли там хватает, чтобы мигрировать.

[rkomissarenko]

Согласен с вами. Будем ожидать новых версий pinboard :)

[rkomissarenko]

Поковырялся. Увидел ещё 2 варианта решения проблемы.

1. Дампить необходимые данные из таблицы request в свою таблицу, где уже выставлены все индексы. Запросы агрегации делать уже в новой таблице. После всех действий делаем TRUNCATE.
2. Новую таблицу не заводить, перенести логику из SQL на PHP (сортировки, группировки).