Репликация записей cdr и оптимизация таблицы

Материал из Mindsellers
Перейти к: навигация, поиск

Asterisk&Телефония

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

#!/bin/bash

host='192.168.0.2' #айпишник второго сервера
#должна быть настроена авторизация по ключу

if [ -f "/etc/asterisk/scripts/lock" ]
then
echo 'Already running'
echo $(date) 'Already running' $(ps aux | grep dump.sh | grep -v 'grep') >> /var/log/mysql-backup.log 
exit 1
fi

touch /etc/asterisk/scripts/lock

if [[ $host = "" ]] 
then

exit 1
fi

user='sync' #пользователь и пароль мускуля 
pass='sync' #используемый для синхронизации

echo $(date) "Starting" >> /var/log/mysql-backup.log


rm /var/lib/mysql-files/cdr.csv

calldate=$(/usr/bin/mysql -h$host -u$user -p$pass -N -e "select calldate from asteriskcdrdb.cdr order by calldate desc limit 1000;" | tail -1) 
#выбираем 1000-ную с конца запись на удаленном сервере
#именно с этой даты будем выгружать данные
#так как запись в cdr появляется только после завершения звонка
#но пишется дата его начала


if [[ $calldate = "" ]]
then

echo "NO CDR CALLDATE!!! ABORT" >> /var/log/mysql-backup.log
echo "NO CDR CALLDATE!!! ABORT" 

exit 1

fi


echo "cdr last date" $calldate >> /var/log/mysql-backup.log

/usr/bin/mysql -u$user -p$pass  -e "select * from asteriskcdrdb.cdr where calldate >= '$calldate' order by calldate into outfile '/var/lib/mysql-files/cdr.csv';"

/usr/bin/scp /var/lib/mysql-files/cdr.csv root@$host:/var/lib/mysql-files/

/usr/bin/mysql -h$host -u$user -p$pass -N -e "load data infile '/var/lib/mysql-files/cdr.csv' replace into table asteriskcdrdb.cdr;"

calldate=""

#синхронизируем записи и файлы asterisk

/usr/bin/rsync -a -W /var/spool/asterisk/monitor/ root@$host:/var/spool/asterisk/monitor/  >> /var/log/mysql-backup.log
/usr/bin/rsync -a -W /etc/asterisk/ root@$host:/etc/asterisk/  >> /var/log/mysql-backup.log
#заодно снимем дамп настроек FreePBX
/usr/bin/mysqldump --lock-tables=false --single-transaction -u$user -p$pass asterisk > /var/lib/mysql-files/asterisk.sql 
/usr/bin/mysql -h$host -u$user -p$pass asterisk < /var/lib/mysql-files/asterisk.sql >> /var/log/mysql-backup.log

echo $(date) "finished" >> /var/log/mysql-backup.log

rm /etc/asterisk/scripts/lock
exit 0


Скрипт засовываем в cron, обычно вполне достаточно выполнять его раз в 15 минут.

Как несложно заметить, мы используем REPLACE, а значит, требуется уникальное поле. Но ни одно из полей таблицы cdr не уникально. Применим следующий финт ушами:

mysql> use asteriskcdrdb;
mysql> alter table cdr add unique(`uniqueid`, `calldate`, `src`, `dst`);

То есть, уникальным мы делаем связку четырех полей. Это действие, помимо того, что позволит нормально использовать REPLACE и не плодить дубли(помните же, мы берем лишних 1000 записей, чтобы не пропустить те звонки, которые не успели попасть в предыдущую выборку), еще и увеличит скорость запросов.

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

mysql> create index calldate on cdr(calldate);

На текущий момент таблица у меня под руками есть только совсем небольшая, но даже на ней я заметил сокращение времени исполнения запроса


Вот, в общем, и все