Дальше много буков (в основном нытье
)Что есть - время агрегации трафика 5 мин.
Списание периодической услуги - раз в месяц на пользователя.
Думаю, у многих коллег написаны свои интерфейсы к UTM со своими отчетами и тд, а у кого их нет, тот рано или поздно приходит к этому. Например, нужно, чтобы при нажатии на кнопку заполнялся тикет для тех. отдела, нужны минимальные скрипты диагностики из интерфейса и т.д., и, конечно же, отчеты, чтобы можно было доходчиво объяснить абоненту, что 5го числа у него закончился предоплаченный трафик и насчиталось 10грн трафа по превышению, ну или так: 6-го числа абонент заплатил 1 гривну и на счету у него 1 гривна, а 7-го числа у него снялась абонплата 2 гривны, поэтому он заблокирован.
Посмотрим, как мы можем это организовать. Для данных отчетов есть 3 таблицы: payment_transactions, discount_transactions_iptraffic_all, discount_transactions.
Первая содержит данные по платежам (комментарий к платежу, метод и тд).
Вторая хранит данные о трафике (какого класса был трафик, сколько, его стоимость).
В третью пишется все выше перечисленное и данные о периодических слугах.
Попробуем организовать отчет, содержащий следующие данные: дата, входящий остаток, название услуги, сумма.:
01.01.2011 | 100 | Абонплата | 5
01.01.2011 | 95 | IP трафик | 10
01.01.2011 | 85 | Доп. Услуга | 50
01.01.2011 | 35 | IP трафик | 50
Т.е. мы не будем показывать данные, какой именно класс трафика. Нам просто важно знать, что в период между снятием абон. платы и доп. услуги был трафик и за него были сняты деньги. На первый взгляд discount_transactions нам подходит. Нам необходимо просто выбрать записи по 2м параметрам – ид аккаунта и дата. Смотрим дальше - данные о трафике поступают каждые 5 мин (время агрегации в настройках UTM). Значит необходимо группировать выборку и суммировать по полю суммы: GROUP by slink_id , YEAR(from_unixtime(discount_date)), MONTH(from_unixtime(discount_date)), DAY(from_unixtime(discount_date)). Тут получаются некоторые грабли: если мы выбираем еще поле incoming_rest то при группировке мы можем выбрать только первое значение в сгруппированных рядах. В нашем случае, услуга «IP трафик» будет упоминаться один раз в отчете. И входящий остаток будет – 95. Получим такой отчет:
01.01.2011 | 100| Абонплата | 5
01.01.2011 | 95 | IP трафик | 60
01.01.2011 | 85 | Доп. Услуга | 50
Разобраться в данном отчете НАМНОГО сложнее – 95 отнимаем 60 – получаем 35 а не 85 –как в отчете. Грубо говоря, подобного отчета не получатся. Нам необходим, какой-то параметр, по которому мы будем группировать выборку – его нет.
Отсюда такое предложение – писать в дополнительную колонку ид последней транзакции другого типа. Если записываем что был трафик - то в доп. колонке будет ид транзакции со списанием периодической услуге. Не знаю насколько правильно со стороны теории БД
. Вспомним что таблица эта – огромна. Из нее возможно нормально выбрать только отчет по периодическим услугам. Зато есть ненужное поле - outgoing_rest. Дополнительно вспомним что в mysql нет индексов по функциям – те мы не можем уплотнить индекс по дате округлив его до часа или например (совсем круто) до даты последнего списания.
Вопрос: зачем писать в эту таблицу ip трафик?
В таблицу пишутся дополнительно платежи. Зачем? Да это несравненно меньшее количество записей. Почему не сделать 1 большую (по строкам а не по длине ряда) таблицу – ид(автоинкремент), дата, ид аккаунта, вх. остаток, сумма, ид услуги, slink_id…….. и тд; со всеми необходимыми индексами. И три таблицы поменьше – с трафиком, периодическими услугами и платежами связанные с первой по первичному ключу (ид). Если поля не будут повторяться – то суммарно таблицы будут меньше по объему. Не факт что это правильно. Или просто не писать туда ип трафик - но тогда следующие грабли: в таблице с трафиком нет поля вх. баланс, которое (думаю многим) крайне необходим. Главный тезис - ликвидировать дублирование на таких огромных таблицах. Почему упразднили такую замечательную утилиту как оптимизатор? Какая разница мне, что 1го июня именно в 17:04 у меня был трафик? Мне важно знать что он действительно был в этот день и если посчитать все списания то баланс сходится и провайдер меня не обманывает.
Сейчас у нас дела обстоят так: 70 Гб – данных в архиве. Это без индексов. Индексы занимают 14 Гб. Индексы свои и используется MyIsam:
Для таблицы с трафиком индекс по полям ид аккаунта и discount_date_hour
Для второй таблицы после архивирования делается
"ALTER TABLE ${db}.discount_${year}_${month} ADD discount_date_hour INT NOT NULL"
И после – такой же индекс. Т.е. для архива необходимо 14 Гб памяти. И каждый следующий месяц это значение будет увеличиваться на 1Гб и больше. Сейчас конец месяца и вот что получается по еще не архивированным данным:
-rw-rw---- 1 mysql mysql 8.9K 2011-01-28 10:38 discount_transactions_all.frm
-rw-rw---- 1 mysql mysql 4.4G 2011-01-31 21:53 discount_transactions_all.ibd
-rw-rw---- 1 mysql mysql 9.0K 2011-01-28 10:55 discount_transactions_iptraffic_all.frm
-rw-rw---- 1 mysql mysql 4.8G 2011-01-31 21:53 discount_transactions_iptraffic_all.ibd
Получаем еще 9Гб памяти.
Теперь про архивирование. Было предложение делать просто переименование таблиц и пересоздавать их. Мне этот подход не нравится – хочется архивировать по месяцу, чтобы в таблицу попал строго один месяц. И все тут хорошо – но получаем доооолгий делит. И не один. Почему из биллинга не складывать статистику по месяцам? Неужто так сложно дописать данный функционал? Плохо что приходится городить костыли чтобы трафик безлимитчиков складывался например раз в час.
Кто все это осилил - прокомментируйте: какие индексы используете, стоит ли отказываться от архивирования строго по месяцам ? Ибо при текущем положении надо или немеринно памяти или забить на быстрые выборки статистики.
