Мне часто приходится сталкиваться с задачами, требующими от базы данных очень большой производительности при обработке больших массивов данных. Сегодня я расскажу об очень простом, но действенном приеме, который может вас выручить, если база уже не поспевает за тем количеством данных, которые скапливаются и должны быть обработаны. Метод не зависит от базы данных, но по привычке публикую в блог PostgreSQL, и пример будет именно на ней. Давайте сразу перейдем к примеру.
Предположим, мы говорим о простейшем биллинге (понятно, что метод применим не только для биллинга, но с ним это будет выглядеть достаточно наглядно). Таблица, в которой скапливаются данные о звонках абонентов будет в нашем случае иметь такой формат:
Вот так вот все простенько. Данные о звонках ложатся в эту табличку с безумной скоростью, и должны быть за разумное время протарифицированы.
Для тарификации у нас есть функция БД с такой сигнатурой:
Тарификацию мы проводим запуская раз в 5 минут вот такой запрос:
И в один прекрасный момент мы понимаем, что этот запрос просто не успевает выполниться за 5 минут. За это время набегает еще больше данных, потом еще, и вот мы сидим и ждем ночи, когда поток чуть-чуть ослабнет, и очередь наконец разгребется. Такая вот перспектива. Надо сказать, что сидели и ждали мы не в одиночку. Вместе с нами просиживали 3 (к примеру) оставшихся ядра нашего сервера, пока одно корпело над запросом. PostgreSQL, к сожалению, не умеет распараллеливать запросы сам, но в нашем случае этого и не нужно. Намного лучшие результаты даст очень простая и очевидная уловка. Создаем индекс по функции «остаток от деления subscriber_id на 4»:
А теперь запускаем в четыре потока (например четыре разных джоба):
где mod равен 0,1,2 или 3 (для каждого потока свой).
Этот прием решает проблемы с блокировками, которые могут возникнуть, если двум разным потокам попадется звонок одного абонента. Также, в параллель эти джобы будут отрабатывать быстрее, чем если бы мы уповали на распараллеливание самой БД (если у нас не постгре, а оракл, например).
Метод применим для любой базы, поддерживающей индекс по функциям (Oracle, Postgresql). В случае MSSQL можно создать calculated column и индекс по ней. В MySQL поддержки функциональных индексов нет, но, в качестве обходного пути, можно создать новый столбец с индексом по нему, и обновлять его триггером.
Сферический конь
Предположим, мы говорим о простейшем биллинге (понятно, что метод применим не только для биллинга, но с ним это будет выглядеть достаточно наглядно). Таблица, в которой скапливаются данные о звонках абонентов будет в нашем случае иметь такой формат:
CREATE TABLE billing.calls
(
call_id BIGINT,
call_time TIMESTAMP,
subscriber_id INTEGER,
duration INTERVAL
);
* This source code was highlighted with Source Code Highlighter.Вот так вот все простенько. Данные о звонках ложатся в эту табличку с безумной скоростью, и должны быть за разумное время протарифицированы.
Для тарификации у нас есть функция БД с такой сигнатурой:
FUNCTION calculate(IN subscriber_id INTEGER, IN duration INTERVAL, OUT status_code text) RETURNS void
* This source code was highlighted with Source Code Highlighter.Тарификацию мы проводим запуская раз в 5 минут вот такой запрос:
SELECT calculate(subscriber_id, duration) FROM billing.calls;
* This source code was highlighted with Source Code Highlighter.И в один прекрасный момент мы понимаем, что этот запрос просто не успевает выполниться за 5 минут. За это время набегает еще больше данных, потом еще, и вот мы сидим и ждем ночи, когда поток чуть-чуть ослабнет, и очередь наконец разгребется. Такая вот перспектива. Надо сказать, что сидели и ждали мы не в одиночку. Вместе с нами просиживали 3 (к примеру) оставшихся ядра нашего сервера, пока одно корпело над запросом. PostgreSQL, к сожалению, не умеет распараллеливать запросы сам, но в нашем случае этого и не нужно. Намного лучшие результаты даст очень простая и очевидная уловка. Создаем индекс по функции «остаток от деления subscriber_id на 4»:
CREATE INDEX billing.calls_subscriber_id_mod_idx ON billing.calls USING btree ((subscriber_id % 4));
* This source code was highlighted with Source Code Highlighter.А теперь запускаем в четыре потока (например четыре разных джоба):
SELECT calculate(subscriber_id, duration) FROM billing.calls WHERE subscriber_id % 4 = @mod;
* This source code was highlighted with Source Code Highlighter.где mod равен 0,1,2 или 3 (для каждого потока свой).
В результате
Этот прием решает проблемы с блокировками, которые могут возникнуть, если двум разным потокам попадется звонок одного абонента. Также, в параллель эти джобы будут отрабатывать быстрее, чем если бы мы уповали на распараллеливание самой БД (если у нас не постгре, а оракл, например).
Метод применим для любой базы, поддерживающей индекс по функциям (Oracle, Postgresql). В случае MSSQL можно создать calculated column и индекс по ней. В MySQL поддержки функциональных индексов нет, но, в качестве обходного пути, можно создать новый столбец с индексом по нему, и обновлять его триггером.