The OpenNET Project / Index page

[ новости /+++ | форум | wiki | теги | ]

Обзор и техника оптимизации для MySQL хранилища InnoDB (innodb mysql database)


<< Предыдущая ИНДЕКС Исправить src / Печать Следующая >>
Ключевые слова: innodb, mysql, database,  (найти похожие документы)
From: Vladimir Rusinov <vladimir@greenmice.info.> 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 скоро станет включеной по умолчанию, поэтому рекомендуется проверить ваш код заранее! Ссылки * http://www.innodb.com * http://forums.innodb.com
Как определить оптимальный размер 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 STATUS\G SELECT sleep(60); SHOW engine innodb STATUS\G 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.) mysql> SELECT (3838334638 - 3836410803) / 1024 / 1024 AS MB_per_min; +------------+ | MB_per_min | +------------+ | 1.83471203 | +------------+ Хорошим правилом будет установка такого размера, чтобы там могли уместиться около часа логов. Тогда 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

<< Предыдущая ИНДЕКС Исправить src / Печать Следующая >>

 Добавить комментарий
Имя:
E-Mail:
Заголовок:
Текст:




Спонсоры:
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2021 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру