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)
ibKpoxa
21.08.2015 15:22+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 мб? При работе с кешом есть оверхер на то, что любая вставка проверяем кеш на наличие там элементов под удаление.
varnav Автор
21.08.2015 16:01-4Все замечания верные. Но если рассматривать все нюансы — здесь будет простыня, а я сторонник минимализма. Лучше рассматривать статью как набор подсказок. В конце концов у аудитории должно быть понимание зачем нужен каждый шаг и что в нём можно изменить конкретно для них.
youROCK
22.08.2015 01:36+1Дело в том, что, не указывая деталей, почему вы решили, что это поможет, вы дискредитируете себя в первую очередь. Советы, в целом, не самые плохие, но явно не все нужно делать. Про query cache уже сказали, есть вопросы про buffer_pool_instances и про партишенинг. Вы меряли производительность при работе с партиционированными таблицами? Эта фича в мускуле появилась в мускуле относительно недавно и в интернете можно найти очень много ругани на счет деталей реализации. Я верю, что в вашем случае все нормально, но я бы точно не советовал бы другим людям использовать эту фичу бездумно.
varnav Автор
22.08.2015 12:19Недавно — это вы про начало 2009-го? Прошло шесть с половиной лет, за это время баги исправлены. В моём случае именно партиционирование стало наиболее эффективным из всего вышеописанного.
youROCK
22.08.2015 01:38Сжатые страницы, кстати, тоже далеко не идеально работает в innodb и имеют много багов, вплоть до порчи данных. Кейсы редкие, но обычно встречаются именно тогда, когда данных много и нагрузка высокая…
varnav Автор
22.08.2015 12:18-1Насколько актуальна эта информация? Одно дело когда эта фича только появилась, другое дело сейчас.
varnav Автор
21.08.2015 16:03-1Кстати о кэше — чем лучше всё измерить? Всё измерение моих оптимизаций сводилось к тому, что на каждой страничке системы, выводящей журнал, статистику и прочее внизу счётчик времени выполнения. Если он уменьшился для типовых операций — значит хорошо. А он уменьшился, и заметно.
ibKpoxa
21.08.2015 16:23Чтобы понять мешает кеш или помогает можно посмотреть в сторону профайлинга запросов, на «Waiting for query cache lock», это надо проверять у всех видов запросов. Скорее всего вы замеряли время генерации страницы, а не суммарное время всех запросов к mysql в промежуток времени.
catharsis
26.08.2015 02:18innodb_change_buffer_max_size=10; В CDR мы мало пишем и много читаем. Буфер на запись ставим небольшой.
так все же вставок или чтений?
Если основная нагрузка — вставки, предпочтительнее может оказаться myisam.varnav Автор
26.08.2015 10:56Да нет, разумеется основная нагрузка — чтение. Вставить несколько десятков строк в минуту — это ерунда.
Bozaro
24.08.2015 10:00wiki.asterisk.org/wiki/display/AST/MySQL+CDR+Backend
cdr_mysql module has been deprecated in 1.8
AloneCoder
Ваши манипуляции мне понятны, но статье явно не хватает выводов
Почему и зачем мне эти советы, какой прирост производительности?
varnav Автор
А какие тут могут быть выводы? :)
Прирост может быть 1% а может и 200% — зависит от железа и размера базы.
AloneCoder
Ну вы же зачем-то затеяли у себя эту оптимизацию, у вас есть конкретный кейс, с конкретным приростом