Оптимизация базы данных CDR в MySQL

Рано или поздно, но база CDR разрастается до значительных размеров. Существует несколько довольно простых способов её оптимизировать.

1. Обновите MySQL. Каждая новая версия быстрее предыдущей. Вопрос обновления выходит за рамки темы, но напоминаю — при прыжках «через версию» оптимально выгрузить всю базу и загрузить заново на чистую установку, иначе возможны сюрпризы.

2. Оптимизируйте InnoDB. В конфиге my.cnf рекомендую следующие настройки:

innodb_buffer_pool_size = 256M ; Рекомендую установить здесь значение, равное половине RAM вашего сервера.
innodb_file_format = Barracuda ; Более новый формат БД. Пригодится далее.
innodb_buffer_pool_instances = 1 ; Если у вас не больше гигабайта выделено под буферный пул, ставьте единицу.
innodb_change_buffer_max_size=10 ; В CDR мы мало пишем и много читаем. Буфер на запись ставим небольшой.
innodb_flush_log_at_trx_commit=2 ; Дополнительная буферизация при записи ценой риска потери пары последних записей в случае краха.


3. Смените движок на InnoDB

ALTER TABLE cdr ENGINE=InnoDB;


4. Отключите performance schema если её не используете (my.cnf)

performance_schema=OFF


5. Включите кэширование запросов (my.cnf)

query_cache_type=1
query_cache_size=32M


6. Партиционируйте таблицу, создав 12 разделов, по одному на месяц года.

ALTER TABLE cdr
PARTITION BY HASH (month(calldate))
PARTITIONS 12;


7. Перейдите на сжатый формат, если у вас механический, а не SSD диск. Для этого и нужен формат Barracuda.

ALTER TABLE cdr
ROW_FORMAT=COMPRESSED;


Рекомендации актуальны для MySQL 5.6

Комментарии 18

    +3
    Ваши манипуляции мне понятны, но статье явно не хватает выводов
    Почему и зачем мне эти советы, какой прирост производительности?
      0
      А какие тут могут быть выводы? :)

      Прирост может быть 1% а может и 200% — зависит от железа и размера базы.
        +3
        Ну вы же зачем-то затеяли у себя эту оптимизацию, у вас есть конкретный кейс, с конкретным приростом
      +3
      Контекст применения совета конечно бы не помешал, например что такое CDR в вашем случае можно только догадаться по названию хаба, иначе это совсем не ясно.

      Не все советы одинаково полезны:

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

      Далее innodb_buffer_pool_size = 256M; Рекомендую установить здесь значение, равное половине RAM вашего сервера. совет никакой, т.к. не подкреплен реальными данными о базе, если база 10гб а на сервере 48гб, тоже делать пул в половину оперы? А зачем?

      Смена движка на innodb начинание хорошее, но далеко не всегда, например если верить первому абзацу текста, то проблема в занимаемом месте, а innodb таблицы раза в 2 больше, чем MyISAM с теми же данными, возможно альтер просто не пройдет из-за нехватки места, а если и пройдет и места хватит, то его будет меньше, чем раньше.

      innodb_buffer_pool_instances = 1; Если у вас не больше гигабайта выделено под буферный пул, ставьте единицу.
      А что делать если под пул выделено 10гб? А если 20?

      Такой большой кеш запросов, в 128м, чем-то поможет? В CDR вставок больше, чем чтений, кеш, возможно, будет не сильно полезен, т.к. есть оверхед на его обслуживание. Вы проверяли работу с кешом и без кеша и если да, то какой конфиг сервера, размер таблицы, частота запросов вставок и селектов, какие результаты того и другого при выключенном кеше, при включенном кеше размером в 10, 50, 100 мб? При работе с кешом есть оверхер на то, что любая вставка проверяем кеш на наличие там элементов под удаление.

        –4
        Все замечания верные. Но если рассматривать все нюансы — здесь будет простыня, а я сторонник минимализма. Лучше рассматривать статью как набор подсказок. В конце концов у аудитории должно быть понимание зачем нужен каждый шаг и что в нём можно изменить конкретно для них.
          +1
          Дело в том, что, не указывая деталей, почему вы решили, что это поможет, вы дискредитируете себя в первую очередь. Советы, в целом, не самые плохие, но явно не все нужно делать. Про query cache уже сказали, есть вопросы про buffer_pool_instances и про партишенинг. Вы меряли производительность при работе с партиционированными таблицами? Эта фича в мускуле появилась в мускуле относительно недавно и в интернете можно найти очень много ругани на счет деталей реализации. Я верю, что в вашем случае все нормально, но я бы точно не советовал бы другим людям использовать эту фичу бездумно.
            0
            Недавно — это вы про начало 2009-го? Прошло шесть с половиной лет, за это время баги исправлены. В моём случае именно партиционирование стало наиболее эффективным из всего вышеописанного.
            0
            Сжатые страницы, кстати, тоже далеко не идеально работает в innodb и имеют много багов, вплоть до порчи данных. Кейсы редкие, но обычно встречаются именно тогда, когда данных много и нагрузка высокая…
              –1
              Насколько актуальна эта информация? Одно дело когда эта фича только появилась, другое дело сейчас.
            –1
            Кстати о кэше — чем лучше всё измерить? Всё измерение моих оптимизаций сводилось к тому, что на каждой страничке системы, выводящей журнал, статистику и прочее внизу счётчик времени выполнения. Если он уменьшился для типовых операций — значит хорошо. А он уменьшился, и заметно.
              0
              Чтобы понять мешает кеш или помогает можно посмотреть в сторону профайлинга запросов, на «Waiting for query cache lock», это надо проверять у всех видов запросов. Скорее всего вы замеряли время генерации страницы, а не суммарное время всех запросов к mysql в промежуток времени.
              0
              innodb_change_buffer_max_size=10; В CDR мы мало пишем и много читаем. Буфер на запись ставим небольшой.

              так все же вставок или чтений?
              Если основная нагрузка — вставки, предпочтительнее может оказаться myisam.
                0
                Да нет, разумеется основная нагрузка — чтение. Вставить несколько десятков строк в минуту — это ерунда.
              0
              wiki.asterisk.org/wiki/display/AST/MySQL+CDR+Backend

              cdr_mysql module has been deprecated in 1.8
                +1
                И что? Об этом модуле в моём посте ни слова.
                  0
                  На сегодняшний день всё тот-же CDR MYSQL работает через ODBC.

                  Хранение CDR остаётся актуальным.
                    0
                    Причём модуль ODBC толком не задокументирован, сложен в настройке, а у меня вообще крашится.
                      0
                      Согласен, но надо.

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                Самое читаемое