Как ускорить выполнение запроса?
Все привет, тематика автозапчасти. Данных ежедневно льется 20 млн в clickhouse.
На основе данных есть скрипт который считает данные и строит отчет.
Столкнулся с тем что если выбрать большой период и попробовать посчитать данные то процесс занимает очень большое время. Что я сделал уже:
1. Получаю группы уникальных артикулов-брендов из выбранного диапазона времени, отправляю в rabbitMq в очередь.
2. Сообщений в очереди примерно 50к, каждое сообщение состоит из 100 артикулов-брендов, калькуляция занимает 40 сек.
3. Работает 10 консьюмеров, но с учетом этого разбор 50к сообщение длится 3 дня это если период отчета 10 дней. При периоде отчета 90 дней будет работать расчет 27 дней.
Пока закончились мысли как ускорится.
Сервер: 12th Gen Intel(R) Core(TM) i9-12900K 1Тб SSD, память 116g
Дополнительно:
"Дорогие ученые! У меня который год в подполе происходит подземный стук. Объясните, пожалуйста, как он происходит."
Что до "10 консюмеров" - теоритически это 10 поднятых процессов, каждый из которых пока неизвестно что делает и где его можно затюнинговать.
Непонятно, что еще есть на машине. Даже на слабой машине даже обычный fpm, не то что прям рэббит движ - настраивается под 50 возможных процессов (это дефолтный конфиг если что). Точно 10 это достаточно?
На тему достаточности - сам скрипт тоже важно как написан. Если он подымает целый фреймворк а не состоит из 20-30 строк кода - может от увеличения числа скриптов все очень напряжется. Но чесслово - я запускал на винде не на серверной машине сотку консолей, каждая из которых что-то читала-писала, и ничего, сидел ютуб еще одновременно с этим смотрел. 10 это прям как-то мало. С другой стороны - если задача прям хардовая - то по скрипту на ядро - может и нормально.
Но хардовость задачи не раскрыта никак кроме "каждый день 20 лямов пишется, и я пытаюсь считать эти 20 лямов, чтобы агрегатить их в 100 строчек". Вместо того чтобы и агрегатить на уровне SQL запроса, и вообще делать это в момент записи сайд эффектом не влияющим на саму запись.
Еще можешь упереться в сам рэббит, там есть особенности. Попробуй сэмулировать свою же задачу в сильно меньшем количестве данных на обычном кроне. Как бы ты это делал если бы скрипт работал раз в минуту. Подключение крутых и быстро реагирующих штук для построения отчетов вычиткой из СУБД миллионов записей само по себе странная идея. То есть быстро реагирующий инструмент для редко но постоянно считаемых тяжелых задач.
На очень упрощенном уровне правильная очередь работает в режиме "накопи буффер из N или раз в M секунд выполни сколько скопилось даже если оно меньше чем N". То есть это крон + счетчик + менеджер процессов. Рэббит в свою очередь представляет собой буфер на 1 ячейку, которая всегда и немедленно передается на выполнение. Это прикольно, чтобы пользователи не ждали минуту, пока крон запустится, но для операций расчетов это вообще не важно. Другой вопрос, что рэббит это еще и приложение на порту/сокете, т.е. с доступом с нескольких машин, а крон это все таки штука на конкретной машине. Но обдумать можно.
Консьюмер получает сообщение в котором находится 100 пар артикул+бренд, уникальных. Эти артикулы летят в КХ и извлекают ценовые предложения по данным парам артикул, бренд, кол-во, цена и данных много так как одна деталь каждый день может быть с разной ценой или кол-вом.
Консьюмер логирует в файл то что делает вот лог
[11/03/2024 15:23:48] [DEBUG] generate part report start, hash 000125dadae7fa7b2d1273bd3a1751bf [Context [] Extra []]
[11/03/2024 15:24:25] [DEBUG] execute success, 1 time: 37.32 [Context [] Extra []]
[11/03/2024 15:24:26] [DEBUG] calculatePart finish, hash 000125dadae7fa7b2d1273bd3a1751bf mem: 19.00 MB [Context [] Extra []]
[11/03/2024 15:24:26] [DEBUG] save results finish, hash 000125dadae7fa7b2d1273bd3a1751bf mem: 19.35 MB [Context [] Extra []]
[11/03/2024 15:24:26] [DEBUG] generate part report finish, time: 0.01 hash 000125dadae7fa7b2d1273bd3a1751bf mem: 19.33 MB [Context [] Extra []]
Запросы такого плана
SELECT * FROM partscanner_sheet_raw_log WHERE event_date >= '2023-12-01' AND event_date <= '2024-02-24' AND sheet_id IN (15,17,18,19,20,21,22,23,24,25,26,.........,255,256,257,258,259,260,261,262,263) AND type = 'info' AND upperUTF8(key) IN ('MAZ_54321310400601','..........'MAZ_54322704125','MAZ_54322801080') ORDER BY event_time ASC LIMIT 0,10000000
Кстати сейчас заглянул в лог, вижу ошибки такого плана
In StreamIO.php line 268:
fwrite(): Send of 21 bytes failed with errno=104 Connection reset by peer
Может действительно раббит барахлит, StreamIO.php - класс раббита php
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Для ускорения выполнения запроса в базе данных можно применить несколько методов оптимизации. Вот несколько советов, которые могут помочь ускорить выполнение запроса:
1. Индексирование: Создание индексов на столбцах, по которым часто выполняются поисковые запросы, может значительно ускорить выполнение запросов. Индексы позволяют базе данных быстро находить нужные записи без полного сканирования таблицы.
CREATE INDEX index_name ON table_name (column_name);
2. Оптимизация запроса: При написании запросов старайтесь использовать только необходимые столбцы, избегайте использования символьных функций в условиях WHERE, используйте объединения таблиц (JOIN) вместо подзапросов и т.д.
3. Кэширование: Используйте кэширование запросов, чтобы уменьшить нагрузку на базу данных. Можно кэшировать результаты выполнения запросов на определенное время, чтобы повторные запросы не выполнялись заново.
4. Оптимизация таблиц: Проверьте структуру таблицы и убедитесь, что она оптимально настроена. Используйте правильные типы данных для столбцов, убедитесь, что у вас нет лишних индексов, избегайте NULL-значений, оптимизируйте размеры таблиц и т.д.
5. Использование кэширующих решений: В случае большой нагрузки на базу данных можно рассмотреть использование кэширующих решений, таких как Redis или Memcached, чтобы ускорить доступ к часто используемым данным.
Это лишь несколько методов оптимизации, которые могут помочь ускорить выполнение запроса в базе данных. Важно проводить тестирование и мониторинг производительности, чтобы определить наиболее эффективные методы оптимизации для конкретной ситуации.