From: Vladimir Rusinov
Date: Mon, 16 Mar 2009 17:02:14 +0000 (UTC)
Subject: Обзор и техника оптимизации для MySQL хранилища InnoDB
Оригинал: http://greenmice.info/ru/node/92
Данная статья является вольным переводом презентации с MySQL User
Conference, проходившей в апреле 2008.
Обзор InnoDB
InnoDB - один из почти десятка доступных движков для MySQL, и вот его
основные достоинства:
* Скорость:
+ построчные блокировки (а не целых таблиц как в MyISAM)
+ эффективное использование CPU, памяти и i/o
+ эффективные индексы
* Стабильность и целостность:
+ автоматическое восстановление после сбоев
+ транзакции и ссылочная целостность
+ возможен онлайн бекап с помощью InnoDB Hot Backup
+ хороший, протестированный код
* Проверенность:
+ распространяется в составе MySQL с 2001 года
+ широко используется в различных крупных проектах
InnoDB в MySQL 5.1
В свежем релизе MySQL InnoDB:
* полностью поддерживает все новые функции (partitioning, построковая
репликация)
* исправлено множество багов из 5.0
* существенное улучшение производительности при использовании
AUTO_INCREMENT
InnoDB AUTO_INCREMENT в 5.1
До 5.1.22 InnoDB делал блокировку на AUTOINC счетчике до конца
выполнения SQL запроса. Это нормально для коротких запросов, но
разумеется плохо для долго выполняющихся. Такое поведение было связано
с требованиями репликации. Начиная с 5.1.22 в MySQL используется более
легкие блокировки. Введен новый параметр: innodb_autoinc_lock_mode,
который может принимать следующие значения:
0 - "Traditional/Традиционный" - для обратной совместимости
1 - "Consecutive/Последовательный" - по умолчанию в 5.1
2 - "Interleaved/Перемежающийся" - быстрее чем последовательный, но
работает только с построчной репликацией Благодаря этим изменениям
теперь производительность AUTO_INCREMENT изменяется практически линейно
при увеличении количества одновременных вставок.
Обзор InnoDB Plugin
Одно из существенных изменений в MySQL 5.1 - введение плагинов. Теперь
движок может поставляться в виде отдельной, независимой библиотеки,
установка которой не требует перекомпиляции всего сервера. В данный
момент на сайте http://www.innodb.com доступна тестовая версия InnoDB
Plugin для MySQL 5.1. Он обладает бОльшим количеством функций по
сравнению с InnoDB встроенным в MySQL:
* более быстрое создание и удаление индексов
* сжатие данных
* новый формат строк: хранение длинных BLOB, TEXT и VARCHAR вне страницы
* таблицы со служебной информацией и статистикой в INFORMATION_SCHEMA
* изменения для удобства:
+ возможность динамического изменения innodb_file_per_table
+ TRUNCATE TABLE теперь пересоздает .ibd файлы, очищая место
+ "режим соответствия"
InnoDB Plugin распространяется как в виде бинарников для разных
платформ, так и в виде исходников под лицензией GPLv2 (также как и
MySQL). InnoDB Plugin поддерживает существующие базы и может быть
использован временно (можно откатиться до встроенного InnoDB, но если
не использовался новый формат базы).
Установка InnoDB Plugin
1. Скачайте InnoDB в бинарном виде, либо в виде исходников и соберите его.
2. Скопируйте ha_innodb.so в директорию с библиотеками MySQL (обычно
префикс установки/lib)
3. Остановите MySQL
4. Добавите в my.cnf следующее:
skip_innodb // отключаем встроеный InnoDB
innodb_file_per_table // храним каждую таблицу в отдельном файле (не обязательно)
innodb_file_format=Barracuda // включаем новый формат файлов данных (не обязательно)
5. Запустите MySQL.
6. Залогинтель в MySQL в под аккаунтом суперпользователя:
mysql -u root -p
7. установите плагин:
INSTALL PLUGIN INNODB SONAME 'ha_innodb.so'
8. Если вам нужна статистика установите еще плагинов:
INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb.so' и др.
9. Проверьте что все загрузилось правильно:
SHOW PLUGINS
Если что-то не работает - проверьте логи.
Быстрое создание индексов
Во встроенном InnoDB при создании/удалении нового вторичного (не
primary) индекса таблица пересоздавалась заново, а InnoDB Plugin
создает только индекс, и не трогая саму таблицу. Разумеется это гораздо
быстрее. Добавление primary key индекса все еще требует пересоздания
всей таблицы.
Сжатие таблиц
InnoDB Plugin может создавать и использовать таблицы со сжатыми
блоками. При этом сжимаются и страницы с данными и с индексами. Для
работы сжатых таблиц требуется innodb_file_per_table=1 и
innodb_file_format=Barracuda. Как известно, данные в innodb таблицах
обычно содержатся в страницах размером 16 Кб. При использовании сжатия
InnoDB пытается сжать (с помощью zlib) 16-килобайтные страницы в
страницы меньшего размера (обычно 8 или 4 килобайта). Для
среднестатистического набора данных страницы сжимаются более чем на
50%, т.е. обычная 16 кб страница влезает в 8 кб сжатую. InnoDB пытается
минимизировать количество компрессий/декомрессий при изменениях
страниц. для этого InnoDB хранит лог изменений каждой страницы и
пересжимает страницу (расжимает сжатую страницу, применяет изменения из
лога и сжимает ее заново) только когда заканчивается место для лога.
Если страница не сжимается, InnoDB разбивает ее и пытается снова. На
это тратится существенно больше времени, чем если она сжимается с
первого раза. Сжатые страницы кешируются MySQL в buffer pool (точно
также как и обычные). Если страница используется часто, то в buffer
pool хранится еще и ее расжатая копия. В зависимости от вида нагрузки,
соотношение сжатых и несжатых страниц в buffer pool может быть разным:
при активном i/o до 90% в buffer pool занимают сжатые страницы, при
активном использовании CPU - наоборот. Также в зависимости от нагрузки
InnoDB пытается подобрать оптимальную степень сжатия. В будущем
планируется позволить пользователю указывать степень сжатия
самостоятельно. Компрессию следует использовать при активном i/o:
сжатие означает большее количество страниц в buffer pool, следовательно
процент чтений из buffer pool, а не с диска будет выше, следовательно
сканирование таблиц будет быстрее и дисковых i/o операций будет меньше.
Компрессию не следует использовать:
* при высокой нагрузке на CPU: сжатие и декомпрессия будет требовать
еще больше CPU
* на небольших и часто используемых таблицах, которые и так целиком
помещаются в buffer pool
* если ваши данные плохо сжимаются (можно проверить сжатием .ibd
файла: если он сжимается с помощью gzip менее чем на 50%, сжатие
лучше не использовать).
Мониторинг сжатия: Используйте таблицу INFORMATION_SCHEMA.INNODB_CMP.
Best practice: менее 1% неудачных сжатий.
Форматы файлов
В связи с введением новых функций, InnoDB Plugin поддерживает несколько
форматов файлов. Формат меняется с помощью параметра innodb_file_format
Старый формат: Antelope, используется в обычном InnoDB Новый формат:
Barracuda, поддерживается InnoDB Plugin - возможна компрессия,
ROW_FORMAT=DYNAMIC. Встроенный InnoDB может работать только с Antelope
файлами.
INFORMATION_SCHEMA
Виртуальная база данных INFORMATION_SCHEMA содержит много служебной
информации и статистики. Пример: USE INFORMATION_SCHEMA SELECT * FROM
INNODB_TRX; Подробнее о таблицах читайте в документации.
Другие улучшения
* TRUNCATE TABLE пересоздает .ibd файл, тем самым сразу освобождая
место (ранее для этого требовалось сделать DROP и CREATE TABLE).
* новый параметр innodb_strict_mode=on включает режим соотвествия
стандартам (strict mode). В этом режиме все предупреждения станут
ошибками. Strict mode скоро станет включеной по умолчанию, поэтому
рекомендуется проверить ваш код заранее!
Как определить оптимальный размер innodb_log_file_size
Как известно, при коммите InnoDB записывает данные не сразу в файлы данных, а сначала
записывает изменения в innodb_log_file. Дело в том что записать данные непосредственно в
таблицу - существенно более дорогая операция, чем записать изменения в бинарный лог.
Ведение innodb_log_file позволяет проводить оптимизацию i/o: записывать данные большими
последовательными кусками, а также более быстрее обслуживать клиентов (клиент быстро сделал
коммит, а данные в табличное пространство записываются в фоне). Поэтому чем больше файл,
тем больше возможности для InnoDB оптимизировать ввод/вывод. В настоящее время суммарный
размер innodb_log_file ограничен 4 Гб, что более чем достаточно для большинства случаев.
При старте после неожиданного отключения MySQL просматривает innodb_log_file, откатывая
транзакции, которые не успели завершиться перед крахом и отмечая коммиты, которые успели (и
были полностью записаны в innodb_log_file). И естественно, чем больше файл, тем больше
времени требуется серверу чтобы просмотреть его.
Как же определить наиболее оптимальный размер?
Выполните эти команды во время наиболее интенсивной нагрузки вашего сервера:
mysql> pager grep sequence PAGER SET TO 'grep sequence'
mysql> SHOW engine innodb
STATUSG SELECT sleep(60); SHOW engine innodb STATUSG Log sequence number 84 3836410803 1
row IN SET (0.06 sec) 1 row IN SET (1 min 0.00 sec) Log sequence number 84 3838334638 1
row IN SET (0.05 sec)
Обратите внимание на номер log sequence. Это общее количество байт записанных в лог. Так
что мы можем узнать сколько Мб было записано за минуту. (Эти команды будут работать в любой
версии MySQL, начиная с 5.0 можно воспользоваться метрикой Innodb_os_log_written из SHOW
GLOBAL STATUS.)
Хорошим правилом будет установка такого размера, чтобы там могли уместиться около часа
логов. Тогда InnoDB сможет весьма эффективно спланировать записи в файлы данных, и вместе с
тем это хороший компросс для скорости запуска. Округляем до 128 Мб и поскольку по умолчанию
файлов два устанавливаем innodb_log_file_size=64M.
Не слишком ли это мало? Возможно. Часто я вижу размеры логов транзакций в несколько
гигабайт, но обычно это ошибка. Сервер который я использовал для измерений - большой и
делающий много работы, это не игрушка для тестов. Размер лог файлов не стоит оставлять в 5
Мб по умолчанию, но часто не нужно устанавливать их настолько большими, как вы можете
думать.
Если воспользовавшись этим парвилом вы получули цифру в несколько гигабайн, значит видимо
вы очень активно пишете в базу. В этом случае вы можете попробовать установить размер
поменьше, чтобы минимизировать время восстановления. Но учтите: время восстановления
зависит не только от размера лога транзцакций, но и от количества записей в нем. Если у вас
много больших транзакций, можно установить размер побольше. И наоборот: если у вас много
маленьких транзакций, стоит установить размер поменьше.
В любом случаев, цифра полученая этим правилом - хорошая отправная точка.
Оригинал: Baron Schwartz, How to calculate a good InnoDB log file size
Нужно ли переходить с MyISAM на Innodb?
Автор: Peter Zaitsev "Should you move from MyISAM to Innodb ?"
Performance Blog
Перевод: Vladimir Rusinov
Существует значительная часть проектов, которые используют MyISAM и
задаются вопросом, стоит ли им перейти на InnoDB, или же лучше
продолжить использовать MyISAM?
Я предпочитаю Innodb в качестве основного движка, потому что для
большинства пользователей это делает жизнь намного проще - не
приходится беспокоиться о восстановлении таблиц после сбоя, таблицы не
блокируются целиком, "горячие" бекапы делать гораздо проще, но есть
несколько вещей о которых нужно подумать перед принятием решения о
переходе.
MyISAM используется по умолчанию, или это был осмысленный выбор? Это
самый главный вопрос. Иногда MyISAM используется только потому что он
выбран по умолчанию. В других случаях это намеренный выбор для системы,
которая учитывает ограничения MyISAM. В таком случае должен быть
хороший аргумент для перехода на Innodb.
Готовность приложения. Приложение должно быть готово для работы с
Innodb. К примеру, оно должно быть готово к возникновению дедлоков,
которые в Innodb могут случаться даже если вы не используете
транзакции, но никогда не случаются в MyISAM. Разумеется перед
переходом нужно тщательно протестировать приложение.
Производительность. Innodb предлагает очень много для
производительности: как улучшения, так и регрессии производительности.
В качестве улучшений мы обычно видим кеширование данных, более высокий
параллелизм, фоновая запись на диск, ухудшения - увеличение размера
таблиц, обычно более медленная запись, более медленная обработка
BLOB'ов, проблемы с работой с очень большим количеством таблиц,
медленная загрузка данных и ALTER TABLE, и другие проблемы. Наверное
самая известная - низкая производительность COUNT(*) без WHERE,
исправление этой проблемы требует модификации приложения.
Операции. То, что хорошо для MyISAM - плохо для Innodb. Очень важно
чтобы все в команде понимали Innodb и знали как работать с ним, или
хотя бы имели возможность узнать это. Кроме того, нужно проверить
насколько хорошо рутинные процессы работают с Innodb. Например,
бинарное копирование таблицы с одного из слейвов на машину
разработчиков работает нормально в MyISAM и не работает в Innodb.
Некоторые утилиты для бекапа (например mysqlhotcopy) не работают,
другие вещи могут оказаться намного медленнее с Innodb, например бекап
взятый с помощью mysqldump может достаточно быстро восстанавливаться на
MyISAM, но очень медленно на Innodb.
Функциональность. Некоторые функции, доступные в MyISAM не доступны в
Innodb. Например, полнотекстовый поиск и RTREE индексы. Есть обходные
пути для исправления этого, например можно сделать MyISAM-слейв
необходимых таблиц, но это конечно же нужно иметь в виду.
Что насчет использования и MyISAM и Innodb? Конечно, это возможно, но
нужно использоваться аккуратно, т.к. это усложняет такие задачи как
бекапы, балансировка и анализ производительности. Кроме того, это может
осложнить жизнь оптимизатору запросов - ему сложнее учитывать стоимость
операций в разных движках.
Я предпочитаю выбрать один движок (обычно Innodb), а другой движок
использоваться только там где это дает очень хорошие результаты. Я не
буду конвертировать таблицу в MyISAM если это не увеличивает
производительность более чем на 5%, но определенно буду использовать
MyISAM для хранения логов и подобного.
Innodb требует настройки. На самом деле. MyISAM во многих случаях
работает достаточно хорошо и с настройками по умолчанию. Я встречал
множество гигабайтных баз данных, которые вполне нормально работали со
стандартными настройками.
Является ли DNS Ахиллесовой пятой вашего сервера MySQL?
Автор: Baron Schwartz "Is DNS the Achilles heel in your MySQL installation?"
Перевод: Vladimir Rusinov
Установлена ли у вас опция skip_name_resolve в my.cnf? Если нет,
подумайте об этом. DNS работает хорошо то тех пор пока что-то не
ломается. Не позвольте сломаному DNS сломать вам сервер MySQL.
Действительно ли вам нужно ограничивать доступ к MySQL по именам
хостов? Если нет, вам определенно стоит отключить эту функцию системы
аутентификации MySQL. Вы не можете знать когда DNS-сервер вашено
провайдера (или даже ваш собственный) захочет отдохнуть. Но когда это
случается, MySQL загадочным образом не отказывает в коннекте
пользователям, и очень сложно понять в чем же причина.
Возможен и другой вариант: DNS не ломается, но становится медленнее. Не
настолько медленнее чтобы вы заметили это, но достаточно для того,
чтобы аутентификация в MySQL вызывала проблемы.
Я встречался с обоими сценариями при работе с клиентами.
Чтобы не делать два DNS запроса при каждой попытке аутентификации, вам
необоходимо просто добавить "skip_name_resolve" в my.cnf и перезапутить
MySQL. Но перед этим выполните следующую команду:
mysql> SELECT user, host FROM mysql.user
-> WHERE host <> 'localhost' AND host RLIKE '[a-z]';
+------+--------+
| user | host |
+------+--------+
| foo | my.com |
+------+--------+
Любые хосты которые вы тут увидете необходимо преобразовать в
ip-адреса, диапазоны ip-адресов или 'localhost', иначе они не смогут
пройти аутентификацию после выключения dns-запросов.
За более подробной информацией обращайтесь к документации MySQL
715 Прочтений • [Обзор и техника оптимизации для MySQL хранилища InnoDB (innodb mysql database)] [08.05.2012] [Комментариев: 0]
Vova
