Оптимальные методы оптимизации базы данных MySQL
Каковы наилучшие методы оптимизации установки MySQL для достижения максимальной производительности при обработке нескольких больших таблиц ( > 50 тыс. записей с общим объемом около 100 МБ на таблицу)? В настоящее время мы изучаем переписывание DelphiFeeds.com(новостной сайт для сообщества разработчиков Delphi) и заметили, что простые операторы обновления могут занимать до 50 мс. Это похоже на много. Существуют ли какие-либо рекомендованные параметры конфигурации, которые мы должны включить/установить, которые обычно отключены при стандартной установке MySQL (например, чтобы использовать больше ОЗУ для кеширования запросов и данных и т.д.)?
Кроме того, какие последствия для производительности имеет выбор устройств хранения? Мы планируем работать с InnoDB, но если MyISAM рекомендуется по соображениям производительности, мы можем использовать MyISAM.
Ответы
Ответ 1
"Лучшая практика":
- Измерьте производительность, изолируя соответствующую подсистему, а также можете.
- Определите основную причину узкого места. Вы связаны с I/O? ЦП связан? Память связана? Ожидание блокировок?
- Внесите изменения, чтобы устранить причину, которую вы обнаружили.
- Мера снова, чтобы продемонстрировать, что вы исправили узкое место и на сколько.
- Перейдите к шагу 2 и повторите по мере необходимости, пока система не будет работать достаточно быстро.
Подпишитесь на RSS-канал в http://www.mysqlperformanceblog.com и прочитайте его исторические статьи. Это очень полезный ресурс для мудрости, связанной с производительностью. Например, вы спросили о InnoDB и MyISAM. Их вывод: InnoDB имеет на 30% более высокую производительность, чем MyISAM в среднем. Хотя есть также несколько сценариев использования, в которых MyISAM выдает InnoDB.
Авторы этого блога также являются соавторами "High Performance MySQL", книги, упомянутой @Andrew Barnett.
Re comment from @ʞɔıu: Как определить, связана ли привязка ввода/вывода от привязки процессора к памяти, зависит от платформы. Операционная система может предлагать такие инструменты, как ps, iostat, vmstat или top. Или вам, возможно, придется обратиться к стороннему инструменту, если ваша ОС не предоставляет его.
В принципе, какой бы ресурс не был привязан к 100% использованию/насыщенности, скорее всего, будет вашим узким местом. Если загрузка вашего процессора низкая, но ваша нагрузка ввода-вывода максимальна для вашего оборудования, тогда вы привязаны к вводу/выводу.
Это только одна точка данных. Средство может также зависеть от других факторов. Например, сложный SQL-запрос может выполнять файловое управление, и это заставляет занятые операции ввода-вывода. Должно ли вы бросать на него больше/быстрее оборудования или вы должны перепроектировать запрос, чтобы избежать файлового управления?
Слишком много факторов, которые можно суммировать в сообщении StackOverflow, и тот факт, что многие книги существуют по этому вопросу, поддерживает это. Хранение баз данных, эффективно работающих и наилучшим образом использующих ресурсы, - это работа на полный рабочий день, требующая специальных навыков и постоянного изучения.
Джефф Этвуд просто написал хорошую статью в блоге о поиске узких мест в системе:
Ответ 2
Идите покупать "Высокопроизводительный MySQL" от O'Reilly. Это почти 700 страниц на эту тему, поэтому я сомневаюсь, что вы найдете краткий ответ на SO.
Ответ 3
Трудно широко распространять вещи, но возможен вид на более высоком уровне.
- Вам нужно оценить коэффициенты чтения: записи. Для таблиц с коэффициентами ниже, чем около 5: 1, вы, вероятно, выиграете от InnoDB, потому что тогда вставки не будут блокировать выборки. Но если вы не используете транзакции, вы должны изменить
innodb_flush_log_at_trx_commit
на 1, чтобы вернуть производительность MyISAM.
- Посмотрите на параметры памяти. Значения MySQL по умолчанию очень консервативны, и некоторые ограничения на память могут быть увеличены на 10 или более раз даже на обычном аппаратном обеспечении. Это принесет пользу вашим SELECT, а не INSERT.
- MySQL может записывать такие вещи, как запросы, которые не используют индексы, а также запросы, которые занимают слишком много времени (определяемые пользователем).
- Кэш запросов может быть полезен, но вам нужно его измерить (то есть посмотреть, сколько он используется). Кактусы могут это сделать; как может Munin.
- Прикладной дизайн также важен:
- Слегка кэширование часто выбираемых, но небольших наборов данных будет иметь большое значение (т.е. срок службы кэша в несколько секунд).
- Не повторно извлекайте данные, которые у вас уже есть.
- Многоэтапное хранилище может помочь с большим объемом вставок в таблицах, которые также загружаются. Основная идея заключается в том, что вы можете иметь таблицу для ad-hoc inserts (
INSERT DELAYED
также может быть полезно), но пакетный процесс для перемещения обновлений в MySQL оттуда туда, где происходят все чтения. Есть вариации этого.
- Не забывайте, что важна и перспектива и контекст: то, что может показаться долгое время для
UPDATE
, может быть довольно тривиальным, если это "длинное" обновление происходит только один раз в день.
Ответ 4
Существует множество передовых методов, которые были обсуждены ранее, поэтому нет причин повторять их. Для фактического конкретного совета о том, что делать, я бы попытался запустить MySQL Tuner. Его perl script, который вы можете загрузить, а затем запустить на сервере базы данных, он даст вам множество статистических данных о том, как работает ваша база данных (например, кеш-хиты), а также некоторые конкретные рекомендации по тем, какие проблемы или параметры конфигурации необходимо чтобы улучшить производительность.
Хотя эти статистические данные доступны в самой MySQL, я нахожу, что этот инструмент обеспечивает их гораздо легче понять. Хотя важно отметить, что YMMV в отношении рекомендаций, я нашел их, как правило, довольно точными. Просто убедитесь, что вы хорошо проработали базу данных заранее с реалистичным трафиком.