MySQL
Asterisk
December 2016 29

CDR. Сохранить и приумножить

image

Очень часто, созданию базы данных CDR отводится мало места в описаниях настройки. Как правило, все сводится к цитате SQL команд и обещанию, что если кинуть ее в консоль то «все будет ОК».

К примеру, первая же ссылка в гугле рекомендует создать табличку таким образом:

CREATE TABLE `cdr` (
`calldate` datetime NOT NULL default '0000-00-00 00:00:00',
`clid` varchar(80) NOT NULL default '',
`src` varchar(80) NOT NULL default '',
`dst` varchar(80) NOT NULL default '',
`dcontext` varchar(80) NOT NULL default '',
`channel` varchar(80) NOT NULL default '',
`dstchannel` varchar(80) NOT NULL default '',
`lastapp` varchar(80) NOT NULL default '',
`lastdata` varchar(80) NOT NULL default '',
`duration` int(11) NOT NULL default '0',
`billsec` int(11) NOT NULL default '0',
`disposition` varchar(45) NOT NULL default '',
`amaflags` int(11) NOT NULL default '0',
`accountcode` varchar(20) NOT NULL default '',
`userfield` varchar(255) NOT NULL default ''
);

ALTER TABLE `cdr` ADD INDEX ( `calldate` );
ALTER TABLE `cdr` ADD INDEX ( `dst` );
ALTER TABLE `cdr` ADD INDEX ( `accountcode` );

Сразу можно обратить внимание, что как минимум два индекса в базе бесполезны. Это calldate и accountcode. Первый в силу того, что при ежесекундном добавлении записей, размер индекса будет равен количеству записей в самой базе. Да, этот индекс отсортирован, и можно применить некоторые способы к ускорению поиска, но будет ли он эффективен? Второй индекс (accountcode) практически никогда и никем не используется. В качестве подопытной базы — база с 80 млн записей.

Выполним запрос:

SELECT * FROM CDR WHERE src=***** AND calldate>'2016-06-21' AND calldate<'2016-06-22';
/* Affected rows: 0  Найденные строки: 4  Предупреждения: 0  Длительность  1 query: 00:09:36 */

Почти 10 минут ожидания.

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

Внимание! Никогда не делай это в продакшене! Только на копии базы! База лочится на время от 1 часа до нескольких и возможны потери данных при аварийном завершении!

Итак, два шага к успеху эффективного хранения CDR:

  1. Разбить на партиции, чтобы ускорить выборку по периодам
  2. Эффективное индексирвоание

Шаг 0. Выбор движка хранения

Собственно есть два распространенных варианта — MyISAM и INNODB. Холиварить на эту тему можно бесконечно долго, но сравнение движков на реальной базе дало перевес в пользу MyISAM.

Причин тут несколько:

  • При чистой настройке сервера неопытным админом, именно MyISAM более корректно работает при индексации больших объемов. В то время, как INNODB требует тюнинга. В противном случае можно увидеть интересные ошибки о том, что индекс не может быть перестроен
  • MyISAM при включении опции FIXED ROW приобретает дополнительные свойства, а именно:

    1. Устойчивость к сбоям даже при падении сервера
    2. Возможность читать файл напрямую из внешнего приложения, минуя сервер MySQL, что бывает полезно
    3. Скорость обращения к рандомным строкам выше, за счет того, что все строки имеют одинаковую длину

Другими словами, для логирования лучше всего (ИМХО) подойдет MyISAM.

Остановимся на нем.

Шаг 1. Партиции.


В виду того, что мы либо дополняем базу, либо читаем из нее, эффективно раз и навсегда поделить базу на файлы, чтобы уменьшить возможное количество обращений, при чтении определенных временных промежутков. Естественно, разбивать базу нужно по какому-то ключу. Но по какому? Определенно, это должно быть время, но эффективно ли бить базу по calldate? Думаю нет, поэтому вводим дополнительное поле, которое нам также пригодиться и в следующем шаге. А именно — дату. Просто дату, без времени.

Вводим дополнительное поле date, и делаем очень простой триггер на табличку, before update cdr:


BEGIN
SET new.date=DATE(new.calldate);
END

Таким образом, в это поле у нас попадет только дата. И сразу разбиваем табличку на партиции по годам:

ALTER TABLE cdr PARTITION BY RANGE (YEAR(date))
(PARTITION old VALUES LESS THAN (2015) ENGINE = MyISAM,
 PARTITION p2015 VALUES LESS THAN (2016) ENGINE = MyISAM,
 PARTITION p2016 VALUES LESS THAN (2017) ENGINE = MyISAM,
 PARTITION p2018 VALUES LESS THAN (2018) ENGINE = MyISAM,
 PARTITION p2019 VALUES LESS THAN (2019) ENGINE = MyISAM,
 PARTITION p2020 VALUES LESS THAN (2020) ENGINE = MyISAM,
 PARTITION p2021 VALUES LESS THAN (2021) ENGINE = MyISAM,
 PARTITION p2022 VALUES LESS THAN (2022) ENGINE = MyISAM,
 PARTITION p2023 VALUES LESS THAN (2023) ENGINE = MyISAM,
 PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = MyISAM) 

Готово, теперь если мы будем выборку делать с указанием диапазона даты, то MySQL не придется лопатить всю базу за все года. Небольшой плюсик уже есть.

Шаг 2. Индексируем базу.


Собственно, это самый важный шаг. Эксперименты показывают, что в 90% случаев возникает необходимость в индексах на 3 столбцах (по мере необходимости):

  1. date
  2. src
  3. dst

date


MySQL может использовать только один индекс за раз, поэтому некоторые администраторы пытаются создавать составные индексы. Эффектность их не очень высока, потому что как правило приходиться выбирать диапазоны, а в этом случае составные индексы игнорируются MySQL, т.е. происходит FullScan. Исправить поведение скуля мы не можем, но можем сделать так, чтобы количество строк для сканирования было минимальным и дать движку выбор, какой индекс использовать. С одной стороны, нам необходима максимальная подробность индекса, с другой стороны нам нужно затратить как можно меньше операций, чтобы получить диапазон, который мы будем перебирать. Именно поэтому и рекомендую использовать индекс по полю date, а не calldate. Количество элементов в индексе будет равно количеству дней, с момента начала ведения базы, что позволит базе быстро перейти к нужным строчкам.

Есть еще один споcоб помочь базе — сделать так, чтобы она могла вычислить положение строки в файле еще ДО открытия файла. Именно для этого можно использовать FIXED ROW. Положение строки в файле будет вычисляться умножение номера строки на длину строки, а не перебором. Естественно, у того подхода есть жертвы — база будет занимать на диске значительно больше места. Вот к примеру:



Размер базы вырос с 18 Гб до 53,8 Гб. Делать или нет — выбор каждого админа, но если место на сервере позволяет, то это будет еще одним плюсиком.

src,dst


Тут несколько меньше простора для оптимизаций. Точнее, один момент:

Если у вас не используется текстовых номеров, например в софтфонах, то данные поля можно преобразовать в BigInt, что тоже очень хорошо скажется на индексировании и выборке. Но если, Вы как и мы, используете текстовые номера, то данная оптимизация не для вас и придется смириться с более низкой производительностью.

В качестве вишенки на торте — подчищаем те поля, которые нас не интересуют и выставляем размер полей в ожидаемый для нашего случая. У меня получилось вот так:



Ну и финальный запрос:

SELECT * FROM CDR WHERE src=***** AND date='2016-06-21';
/* Affected rows: 0  Найденные строки: 4  Предупреждения: 0  Длительность  1 query: 0,577 sec. */

Прирост на два порядка.

Для примера еще, по диапазону:

SELECT * FROM CDR WHERE src=***** AND date>'2016-09-01' AND date<'2016-09-05';
/* Affected rows: 0  Найденные строки: 1  Предупреждения: 0  Длительность  1 query: 3,900 sec. */
+7
10.5k 62
Comments 23
Top of the day