ГлавнаяОптимизацияMysql оптимизация производительности

Оптимизация и тюнинг производительности MariaDB MySQL сервера внутри Docker. Mysql оптимизация производительности


MySQL. Оптимизация производительности, 2-е издание – ScanLibs

Введение

При написании этой книги мы преследовали несколько целей. Многие из них обязаны нашей давней мечте об «идеальном» пособии по MySQL, которое никто из нас не читал, но которое мы всегда искали на книжных полках. Другие подсказал наш опыт помощи пользователям MySQL.

Мы стремились написать книгу, которая была бы не просто введением в язык SQL. Мы не желали, чтобы в ее названии фигурировал какой-то конкретный интервал времени, например «…за тридцать дней» или «Семь дней для…», и не собирались разговаривать с читателем свысока. Прежде всего, нам хотелось написать книгу, способную повысить квалификацию читателя и помочь ему создавать быстрые, надежные системы на основе MySQL. Такую книгу, которая содержала бы ответы на вопросы из разряда «Как настроить кластер серверов MySQL для обработки миллионов и миллионов запросов и быть уверенным, что он продолжит работать даже при выходе из строя пары серверов?».

…

В результате второе издание построено несколько иначе, чем первое. Мы придаем надежности и корректности работы такое же значение, как и производительности, отчасти потому, что сами использовали MySQL для решения задач, где от сервера баз данных зависят большие деньги. У нас также есть обширный опыт работы с веб-приложениями, где СУБД MySQL стала очень популярной. Второе издание предназначено для выросшего сообщества MySQL, которое не было таким во времена публикации первого издания.

Версии программного обеспечения и их доступность

MySQL постоянно меняется. С тех пор как Джереми написал план первого издания этой книги, появилось множество версий MySQL. Когда первое издание готовилось к печати, MySQL 4.1 и 5.0 существовали только в виде альфа-версий. С того момента прошло несколько лет, и они стали основой крупных веб-приложений, эксплуатируемых в промышленном масштабе. На момент окончания подготовки второго издания последними версиями являются MySQL 5.1 и 6.0 (MySQL 5.1 – релиз-кандидат, а 6.0 в стадии альфа-версии).

…

Мы старались отмечать возможности, которые отсутствуют в более старых версиях или появятся только в следующем семействе 5.1. Однако авторитетным источником информации о возможностях каждой конкретной версии является сама документация по MySQL. Мы надеемся, что в процессе чтения этой книги вы будете время от времени посещать сайт разработчиков СУБД, содержащий всю необходимую информацию (dev.mysql.com/doc/).

Другим замечательным свойством MySQL является то, что она работает практически на всех современных платформах: Mac OS X, Windows, GNU/Linux, Solaris, FreeBSD и других! Однако мы предпочитаем GNU/Linux и иные UNIX-подобные операционные системы. Пользователи Windows, вероятно, найдут некоторые различия. Например, пути к файлам записываются совершенно иначе. Мы также ссылаемся на стандартные утилиты командной строки UNIX и предполагаем, что вы знаете соответствующие команды в Windows.

Еще одна трудность при работе с MySQL на платформе Windows – отсутствие языка Perl в стандартной поставке операционной системы. В состав дистрибутива MySQL входят несколько полезных утилит, написанных на Pe

scanlibs.com

MySQL. Оптимизация производительности (2-е издание)

Шварц Б., Зайцев П., Ткаченко В. и др. - MySQL. Оптимизация производительности (2-е издание)

Год: 2010Автор: Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, etc.Издательство: М., СимволISBN 978-5-93286-153-0Язык: РусскийФормат: PDFКачество: Изначально компьютерное (eBook)

Описание: Авторы этой книги – известные специалисты с многолетней практикой – рассказывают о том, как создавать быстрые и надежные системы на основе MySQL. Ими подробно описываются различные нетривиальные подходы, которые позволят задействовать всю мощь этой СУБД. Рассматриваются методы проектирования схем, индексов и запросов для достижения максимальной производительности. Предлагаются детальные указания по настройке сервера MySQL, операционной системы и оборудования для полного раскрытия их потенциала. Описаны безопасные способы масштабирования приложений, основанные на репликации и балансировании нагрузки. Второе издание полностью переработано и существенно дополнено, особое внимание уделено отказоустойчивости, безопасности и обеспечению целостности данных. Книга рекомендуется как новичкам, так и опытным пользователям, которые хотели бы увеличить производительность своих приложений на базе MySQL.

Текущая раздача: ДатаКомментарии    РазмерПиры
2014-03-01 ► U MySQL. Оптимизация производительности (2-е издание) - Шварц Б., Зайцев П., Ткаченко В. [2010, PDF, RUS] 7.72 MB ↑ 1172 ↓ 6
Похожие раздачи по запросу - mysql:
2014-06-01 ► U Robin Nixon - Learning PHP, MySQL, JavaScript, CSS & HTML5, 3rd Edition [2014, PDF, ENG] 20.23 MB ↑ 603 ↓ 0
2014-02-22 ► U В подлиннике - Кузнецов М.В., Симдянов И.В. - MySQL 5 [2010, DJVU, RUS] + Code 301.12 MB ↑ 156 ↓ 8
2014-02-22 ► U Библиотека программиста - Гольцман В.И. - MySQL 5.0 [2010, PDF, RUS] 3.62 MB ↑ 156 ↓ 4
2014-02-22 ► U Артёменко Ю.Н. - MySQL. Руководство администратора [2005, PDF, RUS] 19.56 MB ↑ 150 ↓ 5
2014-02-04 ► U Никсон Р. - Создаем динамические веб-сайты с помощью PHP, MySQL и JavaScript [2011, DjVu, RUS] 13.54 MB ↑ 1069 ↓ 14
2013-11-11 ► U Борисов И.О. - Специалист. PHP. Уровень 2. Разработка web - сайтов и взаимодействие с MySQL (2013) PCRec 2.52 GB ↑ 1390 ↓ 65499
Обратная связь

torrent.by

MySQL. Оптимизация производительности. 2-е издание. - СУБД и базы данных - Учебники

MySQL. Оптимизация производительности. 2-е издание.

Авторы - Бэрон Шварц, Петр Зайцев, Вадим Ткаченко, Джереми Заводны, Арьен Ленц, Дерек Боллинг

Книга Бэрона Шварца, Петра Зайцева, Вадима Ткаченко, Джереми Заводны, Арьена Ленца и Дерека Боллинга "MySQL: Оптимизация производительности" ориентирована не только на потребности создателей приложений MySQL, но и на жесткие требования администраторов баз данных, которым нужно обеспечить бесперебойную работу системы вне зависимости от того, что разработчики или пользователи запускают на сервере. Переработанное и расширенное второе издание включает в себя более глубокое изложение всех тем, присутствовавших в первом издании, а также множество новых разделов. Частично это является ответом на изменения, произошедшие со времени публикации первого издания: СУБД MySQL стала значительно объемнее, сложнее и, что не менее важно, ее популярность существенно возросла.

Книга разбита на 14 больших глав. В главе 1 дается общий обзор архитектуре СУБД MySQL, приводятся сведения об основах реляционных баз данных, включая транзакции. В следующих четырех главах приведодится материал, который будет необходим снова и снова в процессе использования MySQL: эталонное тестирование и профилирование, оптимизация схемы и индексирование, оптимизация производительности запросов, расширенные возможности MySQL. В 6 и 7 главах обсуждается, как вносить изменения, повышающие производительность приложений на основе MySQL. В 8, 9 и 10 главах рассказывается о репликации баз данных, масштабировании и высокой доступности, оптимизации MySQL на уровне приложения. 11 и 12 главы посвящены резервному копированию и восстановлению баз данных, обеспечению безопасности сервера MySQL. И, наконец, последние две главы посвящены различным полезным темам, таким как состояние сервера MySQL и инструменты для оптимизации производительности.

Материал книги основывается на MySQL версии 5.1 и предполагает, что читатель уже немного знаком с с MySQL и имеет небольшой опыт системного администрирования, работы с сетями и операционными системами семейства UNIX.

Издательство – Символ-Плюс

Год издания – 2010

Формат книги - PDF

Размер - 6,8 Мб

СКАЧАТЬ с depositfiles.com

Любители печатных изданий могут заказать бумажный экземпляр этой же книги здесь.

it-ebooks.ru

Оптимизация производительности MySQL / Блог компании 1cloud.ru / Хабр

В нашем блоге мы много пишем о построении облачного сервиса 1cloud, но немало интересного можно почерпнуть и из опыта по работе с инфраструктурой других компаний.

Мы уже рассказывали о дата-центре фотосервиса imgix, а сегодня затронем тему повышения производительности MySQL и взглянем на советы инженеров соцсети Pinterest.

/ фото Jason Cartwright CC

Работа новичков в компании Pinterest основана на их собственном выборе команды инженеров, в которой они хотели бы присоединиться. Новые сотрудники решают большое количество задач, одна из которых заключается с оптимизацией производительности MySQL, развернутой на Amazon Web Services (AWS).

Даже при довольно низкой рабочей нагрузке в 2000 QPS (запросов в секунду), инженерам компании не удавалось дойти до сколько-нибудь адекватных уровней производительности ввода/вывода – превышение порога в 800 IOPS (операций ввода/вывода в секунду) приводило к неприемлемому увеличению латентности.

Данная проблема могла быть решена двумя способами: переходом на более производительный инстанс, что удваивает затраты и уменьшает эффективность; либо повышением производительности существующей системы.

Суть решения, которое в итоге применила Pinterest заключается в отмеченном влиянии версии ядра Linux: ни стандартная 3.2, ни рекомендуемая 3.8 не давали достаточной эффективности; но сам факт влияния настроек на уровне операционной системы подтолкнул инженеров к поиску различных вариантов оптимизации. В рамках этой работы было проверено более 60 различных тестовых конфигураций SysBench с выводом в файл.

Чтобы оценить влияние изменений на скорость обработки транзакций в тесте SysBench вычисляли 99-й процентиль времени отклика при различных конфигурациях системы и разных количествах потоков: 16 и 32. В итоге производительность чтения/записи была увеличена примерно на 500% для обоих потоков.

Скорость обработки запросов чтения выросла с 4100-4600 QPS до более чем 22000-25000 QPS, в зависимости от степени параллелизма. Скорость обработки запросов записи выросла с 1000 QPS до 5100-6000 QPS.

Со стороны клиента 99-й процентиль латентности уменьшился с 15-35 мс (с отдельными выбросами до 100 мс) до стабильных 15 мс (с выбросами до 80 мс и меньше). Со стороны сервера время ожидания уменьшилось с 5-15 мс до стабильных 5 мс, с ежедневным 18-ти миллисекундным пиком на время обслуживания системы. Число отмеченных инцидентов, связанных с производительностью системы или перегрузкой сервера, упало с 300 до суммарных 10-и (в рамках пары месяцев).

P.S. Мы стараемся делиться не только собственным опытом работы над сервисом по предоставлению виртуальной инфраструктуры 1cloud, но и рассказывать о смежных областях знаний в нашем блоге на Хабре. Не забывайте подписываться на обновления, друзья!

habr.com

Оптимизация MySQL - sqlinfo.ru

В разделе собраны статьи по оптимизации сервера MySQL, окружения сервера и SQL-запросов.

Повышенная доступность MySQL Cluster и алгоритм арбитража

Дата: 02.05.2008

Статья описывает принципы построения отказоустойчивых конфигураций кластера MySQL и алгоритмы, срабатывающие при отказе нод кластера.

Хранилище данных Falcon

Дата: 04.02.2007

В статье обсуждается хранилище данных Falcon.

Использование кэшей индексов

Дата: 22.01.2007

Работа с кэшем индексов MyISAM, именованные кэши.

MySQL 8.0.1: использование SKIP LOCKED и NOWAIT

Дата: 03.05.2018

Перевод статьи Мартина Ханссона про введенные в MySQL 8.0 модификаторы запроса SKIP LOCKED и NOWAIT, позволяющие управлять поведением SELECT при наличии блокировок на запрашиваемые строки.

MySQL 8.0: улучшение производительности при использовании ОТВ

Дата: 20.04.2017

Это перевод статьи Øystein Grøvlen про улучшение производительности запросов при использовании обобщенных табличных выражений вместо производных таблиц и представлений.

MySQL 5.7: улучшение производительности запросов, использующих производные таблицы

Дата: 13.01.2017

Это перевод статьи Øystein Grøvlen про улучшение производительности запросов, содержащих from-подзапросы.

Релиз MySQL 8.0 Labs - убывающие индексы в MySQL

Дата: 13.01.2017

Перевод статьи Chaithra Gopalareddy про добавленные в MySQL 8.0 убывающие индексы и связанное с этим улучшение производительности.

Способ ускорения одно-табличных UPDATE/DELETE

Дата: 13.01.2017

Перевод статьи Øystein Grøvlen о том как повлиять на оптимизатор MySQL для ускорения одно-табличных UPDATE/DELETE.

Улучшаем производительность запросов, меняя IN-подзапросы на производные таблицы

Дата: 31.12.2016

Данный материал является переводом статьи Øystein Grøvlen про улучшение производительности запросов, путем замены некоторых видов IN-подзапросов на производные таблицы.

MySQL 8.0: улучшения в INFORMATION_SCHEMA

Дата: 30.12.2016

Данная статья является переводом статьи Гопала Шанкара о новой реализации INFORMATION_SCHEMA в MySQL 8.0

Импорт секций InnoDB в MySQL 5.6 и MariaDB 10.0/10.1

Дата: 22.12.2016

Инструкция как копировать секционированные таблицы с одного сервера на другой путем переноса табличных пространств в MySQL 5.6 и MariaDB 10.0/10.1

Блокировки в MySQL

Дата: 17.12.2008

В статье рассматриваются используемые в MySQL типы блокировок таблиц. Предназначена новичкам для понимания используемого в MySQL механизма блокировок

С чего начинать оптимизацию MySQL?

Дата: 02.05.2008

Статья описывает основные подходы, применяемые сегодня для решения данной задачи в масштабах одного сервера.

sqlinfo.ru

MySQL. Оптимизация производительности (2-е издание)

Все используемые материалы, размещенные на сайте http://www.slideshare.net/wavedocs/ , являются собственностью их автора (владельца прав).

Эти материалы предназначены только для ознакомления. Для прочих целей Вы должны купить лицензионное издание. Если Вы оставляете у себя в каком-либо виде материалы, но не приобретаете право на их использование, Вы нарушаете законы об Интеллектуальной собственности и Авторском праве, что может повлечь за собой преследование по соответствующим статьям существующего законодательства.

Если Вы являетесь автором и считаете, что размещение информации нарушает Ваши права, Вы можете связаться с мной и я удалю все материалы нарушающие Ваши права

2. По договору между издательством «Символ-Плюс» и Интернет-мага­зином «Books.Ru – Книги России» единственный легальный способполучения данного файла с книгой ISBN 978-5-93286-153-0, назва­ иен«MySQL. Оптимизация производительности, 2-е издание» – покупкав Интернет-магазине «Books.Ru – Книги России». Если Вы получилиданный файл каким-либо другим образом, Вы нарушили междуна­родное законодательство и законодательство Российской Федерацииоб охране авторского права. Вам необходимо удалить данный файл,атакже сообщить издательству «Символ-Плюс» ([email protected]),где именно Вы получили данный файл. 3. High PerformanceMySQLSecond EditionBaron Schwartz, Peter Zaitsev, Vadim Tkachenko,Jeremy D. Zawodny, Arjen Lentz,Derek J. Balling 4. MySQL Оптимизацияпроизводительности Второе изданиеБэрон Шварц, Петр Зайцев, Вадим Ткаченко, Джереми Д. Заводны, Арьен Ленц,Дерек Дж. БэллингСанкт-Петербург – Москва2010 5. Бэрон Шварц, Петр Зайцев, Вадим Ткаченко,Джереми Д. Заводны, Арьен Ленц, Дерек Дж. Бэллинг MySQL. Оптимизация производительности, 2-е изданиеПеревод А. Слинкина Главный редактор А. Галунов Зав. редакцией Н. Макарова Выпускающий редактор П. Щеголев Научный редактор А. Рындин Редактор П. Шалин Корректор С. Минин Верстка К. ЧубаровШварц Б., Зайцев П., Ткаченко В., Заводны Дж., Ленц А., Бэллинг Д.MySQL. Оптимизация производительности, 2-е издание. – Пер. с англ. – СПб.:Символ-Плюс, 2010. – 832 с., ил.ISBN 978-5-93286-153-0Авторы этой книги – известные специалисты с многолетней практикой – рас-сказывают о том, как создавать быстрые и надежные системы на основе MySQL.Ими подробно описываются различные нетривиальные подходы, которые по-зволят задействовать всю мощь этой СУБД.Рассматриваются методы проектирования схем, индексов и запросов для до-стижения максимальной производительности. Предлагаются детальные ука-зания по настройке сервера MySQL, операционной системы и оборудования дляполного раскрытия их потенциала. Описаны безопасные способы масштабиро-вания приложений, основанные на репликации и балансировании нагрузки.Второе издание полностью переработано и существенно дополнено, особое вни-мание уделено отказоустойчивости, безопасности и обеспечению целостностиданных.Книга рекомендуется как новичкам, так и опытным пользователям, которыехотели бы увеличить производительность своих приложений на базе MySQL.ISBN 978-5-93286-153-0ISBN 978-0-596-10171-8 (англ)© Издательство Символ-Плюс, 2010Authorized translation of the English edition © 2008 O’Reilly Media Inc. This trans­lation is pub­ ished and sold by permission of O’Reilly Media Inc., the owner of all rights lto publish and sell the same.Все права на данное издание защищены Законодательством РФ, включая право на полное или час­тичное воспроизведение в любой форме. Все товарные знаки или зарегистрированные товарные зна­ки, упоминаемые в настоящем издании, являются собственностью соответствующих фирм.Издательство «Символ-Плюс». 199034, Санкт-Петербург, 16 линия, 7, тел. (812) 324-5353, www.symbol.ru. Лицензия ЛП N 000054 от 25.12.98.Налоговая льгота – общероссийский классификатор продукции ОК 005-93, том 2; 953000 – книги и брошюры.Подписано в печать 15.04.2010. Формат 70×100 1/16. Печать офсетная. Объем 52 печ. л. Тираж 1500 экз. Заказ № Отпечатано с готовых диапозитивов в ГУП «Типография «Наука»199034, Санкт-Петербург, 9 линия, 12. 6. ОглавлениеПредисловие................................................................................. 9Введение..................................................................................... 101. Архитектура MySQL .................................................................. 23Логическая архитектура MySQL .................................................. 24Управление конкурентным доступом............................................ 26Транзакции............................................................................... 29Multiversion Concurrency Control (MVCC) . .................................... 37.Подсис­ емы хранения в MySQL....................................................39 т2. Поиск узких мест: эталонное тестирование и профилирование...... 60Почему нужно тестировать производительность?. .......................... 61 .Стратегии эталонного тестирования. ............................................ 62 .Тактики эталонного тестирования................................................66Инструменты эталонного тестирования......................................... 72Примеры эталонного тестирования............................................... 76Профилирование. ...................................................................... 86.Профилирование операционной сис­ емы..................................... 112 т3. Оптимизация схемы и индексирование................................... 116 Выбор оптимальных типов данных............................................. 117 Основы индексирования........................................................... 135 Стратегии индексирования для достижения высокой производительности..................................................... 147 Практические примеры индексирования..................................... 176 Обслуживание индексов и таб­ иц............................................... 182 л Нормализация и денормализация.............................................. 186 Ускорение работы команды ALTER TABLE. ................................ 193. Замечания о подсис­ емах хранения............................................ 197 т4. Оптимизация запросов........................................................... 200 Основная причина замедления: оптимизируйте доступ к данным....... 200 Способы реструктуризации запросов. ......................................... 206. Основные принципы выполнения запросов.................................. 209 Ограничения оптимизатора MySQL. ........................................... 232. 7. 6 Оглавление Оптимизация запросов конкретных типов................................... 242 Подсказки оптимизатору запросов............................................. 250 Переменные, определяемые пользователем. ................................ 253. 5. Дополнительные средства MySQL............................................ 261Кэш запросов MySQL................................................................ 261Хранение кода внутри MySQL.................................................... 275Курсоры................................................................................. 284Подготовленные команды ......................................................... 285Определяемые пользователем функции....................................... 290Представления........................................................................ 292Кодировки и схемы упорядочения.............................................. 299Полнотекстовый поиск............................................................. 307Ограничения внешнего ключа. .................................................. 317 .Объединенные таб­ ицы и секционирование................................. 318лРаспределенные (XA) транзакции.............................................. 329 6. Оптимизация параметров сервера.......................................... 332Основы конфигурирования........................................................ 333Общие принципы настройки...................................................... 339Настройка ввода/вывода в MySQL.............................................. 351Настройка конкурентного доступа в MySQL................................. 368Настройка с учетом рабочей нагрузки......................................... 372Настройка параметров уровня соединения................................... 379 7. Оптимизация операционной сис­ емы и оборудования............ 381 т Что ограничивает производительность MySQL?............................ 382 Как выбирать процессор для MySQL........................................... 382 Поиск баланса между памятью и дисками................................... 386 Выбор оборудования для подчиненного сервера............................ 396 Оптимизация производительности с помощью RAID..................... 396 Сети хранения данных и сетевые сис­ емы хранения данных.......... 406т Использование нескольких дисковых томов................................ 408 Конфигурация сети.................................................................. 410 Выбор операционной сис­ емы.................................................... 413 т Выбор файловой сис­ емы.......................................................... 414 т Многопоточность..................................................................... 417 Свопинг.................................................................................. 418 Состояние операционной сис­ емы.............................................. 420 т 8. Репликация............................................................................ 427 Обзор репликации.................................................................... 427 Настройка репликации............................................................. 432 Взгляд на репликацию изнутри.................................................. 441 8. Оглавление 7Топологии репликации............................................................. 449Репликация и планирование пропускной способности................... 466Администрирование и обслуживание репликации........................ 469Проблемы репликации и их решение.......................................... 480Насколько быст­ о работает репликация?.................................... 501рПерспективы репликации в MySQL............................................ 5049. Масштабирование и высокая доступность............................... 506 Терминология......................................................................... 507 Масштабирование MySQL. ........................................................ 509 . Балансирование нагрузки......................................................... 539 Высокая доступность................................................................ 55210. Оптимизация на уровне приложения..................................... 564Общие сведения о производительности приложений..................... 564Проблемы веб-сервера. ............................................................. 568.Кэширование. ......................................................................... 572.Расширение MySQL.................................................................. 579Альтернативы MySQL............................................................... 58111. Резервное копирование и восстановление.............................. 582Обзор. .................................................................................... 583 .Различные факты и компромиссы.............................................. 589Резервное копирование двоичных журналов................................ 600Резервное копирование данных.................................................. 603Восстановление из резервной копии............................................ 616Скорость резервного копирования и восстановления..................... 628Инструменты резервного копирования........................................ 629Сценарии резервного копирования............................................. 63812. Безопасность......................................................................... 642Терминология......................................................................... 642Основы учетных записей........................................................... 643Безопасность на уровне операционной сис­ емы............................ 665тБезопасность на уровне сети...................................................... 666Шифрование данных................................................................ 675MySQL в окружении с измененным корневым каталогом............... 68013. Состояние сервера MySQL...................................................... 682Системные переменные............................................................. 682Команда SHOW STATUS........................................................... 683Команда SHOW INNODB STATUS.............................................. 691Команда SHOW PROCESSLIST................................................... 707Команда SHOW MUTEX STATUS............................................... 708 9. 8 ОглавлениеСостояние репликации. ............................................................ 709.База данных INFORMATION_SCHEMA....................................... 710 14. Инструменты для оптимизации производительности............. 712Средства организации интерфейса.............................................. 712Инструменты мониторинга........................................................ 715Инструменты анализа............................................................... 727Утилиты MySQL...................................................................... 730Источники дополнительной информации.................................... 733 A. Передача больших файлов..................................................... 734 B. Команда EXPLAIN.................................................................... 739 С. Использование Sphinx совместно с MySQL................................ 756 D. Отладка блокировок............................................................... 788 Алфавитный указатель. ............................................................. 799 . 10. ПредисловиеЯ давно знаком с Петром, Вадимом и Арьеном. Могу засвидетельство-вать, что они используют MySQL как в  своих собственных проектах,так и  при работе со множеством солидных клиентов. В  свою очередь,Бэрон пишет клиентское программное обеспечение, упрощающее ис-пользование MySQL.При подготовке второго издания этой книги был учтен практическийопыт авторов по оптимизации, репликации, резервному копированиюи  другим вопросам. Это не просто книга, которая рассказывает, какоптимизировать работу, чтобы использовать MySQL более эффективно,чем раньше. Помимо всего прочего авторы проделали значительную до-полнительную работу, выполнив тесты и опубликовав полученные ре-зультаты, подтверждающие их точку зрения. Это позволит читателю,заглянув во внутренние механизмы MySQL, в будущем избежать мно-жества ошибок, приводящих к недостаточной производительности.Я рекомендую эту книгу как новичкам в  MySQL, которые успели не-много повозиться с сервером и теперь готовы к написанию своего перво-го серьезного приложения, так и опытным пользователям, которые ужеимеют на своем счету хорошо настроенные приложения на базе MySQL,но хотели бы выжать из них еще капельку производительности.Майкл Видениус Март 2008 года 11. ВведениеПри написании этой книги мы преследовали несколько целей. Мно-гие из них обязаны нашей давней мечте об «идеальном» пособии поMySQL, которое никто из нас не читал, но которое мы всегда искали накнижных полках. Другие подсказал наш опыт помощи пользователямMySQL.Мы стремились написать книгу, которая была бы не просто введениемв  язык SQL. Мы не желали, чтобы в  ее названии фигурировал какой-то конкретный интервал времени, например «...за тридцать дней» или«Семь дней для...», и не собирались разговаривать с читателем свысока.Прежде всего, нам хотелось написать книгу, способную повысить ква-лификацию читателя и  помочь ему создавать быст­ ые, надежные сис­ ртемы на основе MySQL. Такую книгу, которая содержала бы ответы навопросы из разряда «Как настроить кластер серверов MySQL для обра-ботки миллионов и миллионов запросов и быть уверенным, что он про-должит работать даже при выходе из строя пары серверов?».Мы решили написать книгу, ориентированную не только на потреб-ности создателей приложений MySQL, но и на жесткие требования ад-министраторов баз данных, которым нужно обеспечить бесперебойнуюработу сис­ емы вне зависимости от того, что разработчики или пользо- тватели запускают на сервере. Мы рассчитываем, что у вас уже есть не-который опыт работы с MySQL, а в идеале, что вы прочли какое-нибудьвведение в MySQL. Мы также предполагаем, что у вас есть небольшойопыт сис­ емного администрирования, работы с сетями и операционны- тми сис­ емами семейства UNIX.тПереработанное и  расширенное второе издание включает в  себя болееглубокое изложение всех тем, присутствовавших в  первом издании,а также множество новых разделов. Частично это является ответом наизменения, произошедшие со времени публикации первого издания:СУБД MySQL стала значительно объемнее, сложнее и, что не менее важ-но, ее популярность существенно возросла. Сообщество MySQL теперьнамного обширнее, а крупные корпорации используют MySQL для сво-их жизненно важных приложений. Со времени первого издания СУБДMySQL стала рассматриваться как ПО масштаба предприятия1. Кроме1 Мы рассматриваем эту фразу скорее как маркетинговую уловку, но, похо- же, многие люди воспринимают ее серьезно. 12. Введение 11того, она все чаще используется в приложениях для Интернета, где про-стои и другие проблемы нельзя ни допустить, ни скрыть.В результате второе издание построено несколько иначе, чем первое.Мы придаем надежности и  корректности работы такое же значение,как и  производительности, отчасти потому, что сами использовалиMySQL для решения задач, где от сервера баз данных зависят большиеденьги. У нас также есть обширный опыт работы с веб-приложениями,где СУБД MySQL стала очень популярной. Второе издание предназначе-но для выросшего сообщества MySQL, которое не было таким во време-на публикации первого издания.Структура книгиВ этой книге освещено множество сложных тем. Они упорядочены та-ким образом, чтобы упростить их изучение.Общий обзорГлава 1 «Архитектура MySQL» посвящена основам, которые необходи-мо знать, прежде чем приступать к более сложным темам. Для того что-бы эффективно использовать СУБД MySQL, вы должны понимать, какона устроена. В этой главе рассматривается архитектура MySQL и клю-чевые особенности ее подсис­ ем хранения. Приводятся сведения обтосновах реляционных баз данных, включая транзакции. Эта глава так-же может выступать в роли введения в MySQL, если вы уже знакомыс какой-нибудь другой СУБД, например с Oracle.Закладка фундаментаВ следующих четырех главах приведен материал, к которому вы будетеобращаться снова и снова в процессе использования MySQL.В главе 2 «Поиск узких мест: эталонное тестирование и профилирова-ние» рассказывается об основах эталонного тестирования производи-тельности и профилирования. Здесь приводится методика определениятого, какого рода нагрузки способен выдерживать сервер, насколькобыст­ о он может выполнять конкретные задачи и  т. п. Тестирование рприложения следует выполнять до и после серьезных изменений, что-бы понять, насколько они оказались эффективными. Изменения, ка-жущиеся полезными, при больших нагрузках могут оказать противо-положный эффект, и вы никогда не узнаете причину падения произво-дительности, пока не измерите ее точно.В главе 3 «Оптимизация схемы и индексирование» мы описываем раз-личные нюансы типов данных, проектирования таб­ иц и  индексов.лПравильно спроектированная схема помогает MySQL работать значи-тельно быст­ ее, а  многие вещи, которые мы будем обсуждать в  после-рдующих главах, зависят от того, насколько хорошо ваше приложение 13. 12 Введениеиспользует индексы. Четкое понимание индексов и  принципов их ис-пользования очень важно для эффективного использования MySQL, по-этому вы, скорее всего, будете неоднократно возвращаться к этой главе.В главе 4 «Оптимизация производительности запросов» речь пойдето  том, как MySQL выполняет запросы и  как можно воспользовать-ся сильными сторонами оптимизатора запросов. Четкое пониманиетого, как работает оптимизатор, поможет творить с  запросами чудесаи  лучше разобраться с  индексами. (Индексирование и  оптимизациязапросов  – это что-то вроде проблемы яйца и  курицы; возможно, длявас будет полезным перечитать заново третью главу после прочтениячетвертой.) В этой главе также приведено много конкретных примеровпочти всех типичных запросов, иллюстрирующих оптимальную рабо-ту MySQL и показывающих, как преобразовать запросы в такую форму,чтобы получить от СУБД максимум возможностей.Все упомянутые нами до этого момента темы – таб­ ицы, индексы, дан-лные и  запросы  – касались любых сис­ ем управления базами данных. тВ главе 5 «Расширенные возможности MySQL» мы выйдем за пределыэтих основ и покажем, как работают дополнительные расширенные воз-можности MySQL. Мы рассмотрим кэш запросов, хранимые процедуры,триггеры, кодировки и прочее. Эти средства реализованы в MySQL ина-че, чем в других базах данных, и хорошее их понимание откроет передвами новые возможности для повышения производительности, о кото-рых вы, быть может, даже не задумывались.Настройка приложенияВ следующих двух главах обсуждается, как вносить изменения, повы-шающие производительность приложений на основе MySQL.В главе 6 «Оптимизация параметров сервера» мы обсудим, как настро-ить MySQL, чтобы извлечь максимум возможного из имеющейся аппа-ратной конфигурации сервера в  применении к  конкретному приложе-нию. В главе 7 «Оптимизация операционной сис­ емы и оборудования»тобъясняется, как выжать все, что только можно, из операционной сис­темы и используемого вами оборудования. Мы также предложим аппа-ратные конфигурации, которые могут обеспечить наилучшую произво-дительность для крупномасштабных приложений.Вертикальное масштабированиепосле внесения измененийОдного сервера бывает достаточно далеко не всегда. В  главе 8 «Репли-кация» мы обсудим репликацию, то есть автоматическое копированиеданных на несколько серверов. В сочетании с уроками, посвященнымимасштабированию, распределению нагрузки и  обеспечению высокойдоступности в главе 9, озаглавленной «Масштабирование и высокая до- 14. Введение 13ступность», вы получите базовые знания для масштабирования прило-жений до необходимого уровня.Оптимизация зачастую возможна и  на уровне самих приложений, ра-ботающих на крупномасштабном сервере MySQL. Можно спроектиро-вать крупное приложение хорошо или плохо. Хотя проектированиеи не является основной темой этой книги, мы не хотим, чтобы вы тра-тили все свое время только на MySQL. Глава 10 «Оптимизация на уров-не приложения» поможет выявить наиболее очевидные проблемы об-щей архитектуры, особенно если это касается веб-приложения.Обеспечение надежности приложенияХорошо спроектированная, масштабируемая база данных также долж-на быть защищена от сбоев электроснабжения, атак злоумышленников,ошибок в приложениях и прочих напастей.В главе 11 «Резервное копирование и восстановление» мы обсудим раз-личные стратегии резервного копирования и  восстановления баз дан-ных MySQL. Эти стратегии помогут минимизировать время простояв случае выхода из строя оборудования и гарантировать, что данные пе-реживут такую «катастрофу».Глава 12 «Безопасность» дает ясное представление о некоторых вопро-сах безопасности сервера MySQL. Но гораздо важнее то, что мы пред-лагаем целый ряд рекомендаций, позволяющих предотвратить внеш-ние вторжения на сервер. Мы расскажем о  некоторых редко освещае-мых аспектах безопасности баз данных и  покажем, как разные реше-ния влияют на их производительность. Обычно с точки зрения произ-водительности имеет смысл использовать как можно более простые по-литики безопасности.Различные полезные темыВ последних нескольких главах и приложениях мы углубимся в вопро-сы, которые либо не вписываются ни в одну из предыдущих глав, либотак часто упоминаются в других главах, что заслуживают отдельногорассмотрения.В главе 13 «Состояние сервера MySQL» показано, как исследовать теку-щий режим работы сервера MySQL. Очень важно знать, как получитьинформацию о  состоянии сервера. Но еще важнее понимать, что этаинформация означает. Мы подробно рассмотрим команду SHOW INNODBSTATUS, поскольку она позволяет детально разобраться в операциях, осу-ществляемых транзакционной подсис­ емой хранения InnoDB. тВ главе 14 «Инструменты для оптимизации производительности» опи-саны инструменты, которые можно использовать для более эффектив-ного управления MySQL. В  их число входят инструменты мониторин-га и анализа, инструменты, помогающие писать запросы, и т. д. В этой 15. 14 Введениеглаве описывается созданный Бэроном комплект инструментов Maatkit,который расширяет функциональность MySQL и  упрощает жизнь ад-министратору базы данных. В ней также рассказано о написанной Бэ-роном программе innotop, которая обладает удобным графическим ин-терфейсом и  позволяет узнавать о  том, что делает сервер MySQL. Онаработает подобно команде UNIX top и  может оказать бесценную по-мощь на всех этапах процесса настройки, позволяя выяснить, что про-исходит внутри самого сервера MySQL и подсис­ ем хранения. тВ приложении A «Передача больших файлов» говорится о том, как эф-фективно копировать очень большие файлы, что критически важно приработе со значительными объемами данных. В приложении B «Коман-да EXPLAIN» показано, как на практике использовать очень полезнуюкоманду EXPLAIN. Приложение C «Использование Sphinx совместнос MySQL» представляет собой введение в высокопроизводительную сис­тему полнотекстового поиска Sphinx, которая дополняет собственныевозможности СУБД MySQL. И наконец, приложение D «Отладка блоки-ровок» поможет вам выяснить, что происходит, когда запросы вызыва-ют конфликтующие друг с другом блокировки.Версии программного обеспеченияи их доступностьMySQL постоянно меняется. С тех пор как Джереми написал план пер-вого издания этой книги, появилось множество версий MySQL. Ког-да первое издание готовилось к  печати, MySQL 4.1 и  5.0 существова-ли только в виде альфа-версий. С того момента прошло несколько лет,и  они стали основой крупных веб-приложений, эксплуатируемыхв промышленном масштабе. На момент окончания подготовки второгоиздания последними версиями являются MySQL 5.1 и 6.0 (MySQL 5.1 –релиз-кандидат, а 6.0 в стадии альфа-версии).В этой книге мы не ограничиваемся какой-то конкретной версией,а опираемся на свой обширный опыт работы с MySQL в реальных при-ложениях. В основном речь идет о версии MySQL 5.0, поскольку имен-но ее мы считаем «текущей». В большинстве примеров предполагается,что вы используете какую-то относительно зрелую версию MySQL 5.0,например MySQL 5.0.40 или более новую. Мы старались отмечать воз-можности, которые отсутствуют в более старых версиях или появятсятолько в  следующем семействе 5.1. Однако авторитетным источникоминформации о возможностях каждой конкретной версии является самадокументация по MySQL. Мы надеемся, что в процессе чтения этой кни-ги вы будете время от времени посещать сайт разработчиков СУБД, со-держащий всю необходимую информацию (http://dev.mysql.com/doc/).Другим замечательным свойством MySQL является то, что она работа-ет практически на всех современных платформах: Mac OS X, Windows, 16. Введение 15GNU/Linux, Solaris, FreeBSD и других! Однако мы предпочитаем GNU/Linux1 и  иные UNIX-подобные операционные сис­ емы. Пользователи тWindows, вероятно, найдут некоторые различия. Например, пути к фай-лам записываются совершенно иначе. Мы также ссылаемся на стандарт-ные утилиты командной строки UNIX и предполагаем, что вы знаете со-ответствующие команды в Windows2.Еще одна трудность при работе с  MySQL на платформе Windows – от-сутствие языка Perl в  стандартной поставке операционной сис­ емы. тВ состав дистрибутива MySQL входят несколько полезных утилит, на-писанных на Perl, а в некоторых главах этой книги представлены при-меры Perl-сценариев, которые служат основой для более сложных ин-струментов, создаваемых уже вами. Комплект Maatkit также написанна Perl. Чтобы использовать эти сценарии, вам потребуется загрузитьверсию Perl для Windows с сайта компании ActiveState и установить до-полнительные модули (DBI и DBD::mysql) для доступа к MySQL.Типографские соглашенияВ книге применяются следующие типографские соглашения:КурсивТаким начертанием выделяются новые термины, URL, адреса элек-тронной почты, имена пользователей и хостов, имена и расширенияимен файлов, пути к  файлам, имена каталогов, а  также команди утилит UNIX.Моноширинный шрифт Применяется для фрагментов кода, конфигурационных параметров, имен баз данных и таб­ иц, имен и значений переменных, имен функ-л ций, модулей, содержимого файлов и результатов работы команд.Моноширинный полужирный шрифт Команды или другой текст, который пользователь должен вводить буквально. Также используется для выделения в  результатах рабо- ты команды.Моноширинный курсив Текст, вместо которого надо подставить значения, вводимые пользо- вателем.1 Во избежание путаницы мы ссылаемся на Linux, когда пишем о ядре, и наGNU/Linux, когда пишем обо всей инфраструктуре операционной сис­ емы, ткоторая поддерживает приложения.2 Вы можете найти версии UNIX-утилит для Windows на сайтах http://unxutils.sourceforge.net или http://gnuwin32.sourceforge.net. 17. 16 ВведениеТаким способом выделяются советы, предложения и  примеча-ния общего характера.Таким способом выделяются предупреждения и предостереже­ния.О примерах кодаЭта книга призвана помочь вам в работе. Поэтому вы можете использо-вать приведенный в ней код в собственных программах или в создава-емой вами документации. Спрашивать у  нас разрешения необязатель-но, если только вы не собираетесь воспроизводить значительную частькода. Например, не требуется разрешение, чтобы включить в свою про-грамму несколько фрагментов кода из книги. Однако для продажи илираспространения примеров на компакт-диске нужно получить разре-шение. Можно без ограничений цитировать книгу и примеры в ответахна вопросы. Но чтобы включить значительные объемы кода в докумен-тацию по собственному продукту, нужно получить разрешение.Примеры можно найти на сайте http://www.highperfmysql.com, где онипериодически обновляются. Однако мы не в состоянии обновлять и тес­тировать код для каждой версии MySQL.Мы высоко ценим, хотя и  не требуем указывать, ссылки на наши из-дания. В  ссылке обычно приводятся название книги, имя автора, из-дательство и ISBN, например: «High Performance MySQL: Optimization,Backups, Replication, and More, Second Edition, by Baron Schwartz et al.»Copyright 2008 O’Reilly Media, Inc., 9780596101718.Если вы полагаете, что планируемое использование кода выходит зарамки изложенной выше лицензии, пожалуйста, обратитесь к нам поадресу [email protected].Доступность на Safari Если на обложке вашей любимой книги присутствует зна-чок Safari® Enabled, это означает, что книга доступнав сетевой библиотеке Safari издательства O’Reilly.У Safari есть преимущество перед обычными электронными книгами.Это виртуальная библиотека, которая позволяет легко находить тыся-чи технических изданий, копировать примеры программ, загружатьотдельные главы и быст­ о получать точную и актуальную информацию.рБиблиотека бесплатна и расположена по адресу http://safari.oreilly.com. 18. Введение 17Как с нами связатьсяВопросы и  замечания по поводу этой книги отправляйте в  издатель-ство: O’Reilly Media, Inc. 1005 Gravenstein Highway North Sebastopol, CA 95472 800-998-9938 (в США или Канаде) 707-829-0515 (международный или местный) 707-829-0104 (факс)Для этой книги есть веб-страница, на которой выкладываются спискизамеченных ошибок, примеры и  разного рода дополнительная инфор-мация. Адрес страницы: http://www.oreilly.com/catalog/9780596101718/Замечания и  вопросы технического характера следует отправлять поадресу: [email protected]Дополнительную информацию о  наших книгах, конференциях, ре-сурсных центрах и о сети O’Reilly Network можно найти на сайте: http://www.oreilly.comВы также можете связаться с  авторами напрямую. Блог Бэрона

docslide.net

Оптимизация и тюнинг производительности MariaDB MySQL сервера внутри Docker

Пожалуй, это единственное актуальное и максимально полное руководство на русском языке по оптимизации MySQL сервера в docker-контейнерах. По сути большая часть советов отлично подойдёт и тем, кто не работает с Docker. Оптимизацию базы данных можно разделить на 3 слоя:

  1. Оптимизация запросов, таблиц и индексов
  2. Тюнинг параметров сервера баз данных
  3. Оптимальная настройка сервера, операционной и файловой систем

В этой замете рассмотрим второй пункт: тюнинг параметров сервера баз данных. И конечно же первым советом будет не использовать Docker для контейнеризации MySQL и других хранилищ! Я серьёзно, если вы используете базу данных находящуюся в контейнере и беспокоитесь о тюнинге производительности, то первым же делом вынесите его на отдельный полноценный сервер. Однако, есть и преимущество при запуске MySQL в Docker: можно для каждого приложения оптимально сконфигурировать настройки.

Как это не удивительно, но официальный image от MariaDB для Docker уже сконфигурирован с некоторыми оптимизациями, в том числе и для работы в контейнерах. В файле /etc/mysql/my.cnf уже включено innodb_file_per_table = 1, а в конфиге /etc/mysql/conf.d/docker.cnf присутствуют сроки:

[mysqld] skip-host-cache skip-name-resolve

Установка и знакомство с MySQLTuner

MySQLTuner достаточно интересный и полезный инструмент для тюнинга и оптимизации таких серверов баз данных: MySQL 5.7, MySQL 5.6, MySQL 5.5, MariaDB 10.1, MariaDB 10.0, Percona Server 5.6, Percona XtraDB cluster. Также он частично поддерживает MySQL 3.23, 4.0, 4.1, 5.0, 5.1, но они помечены как deprecated. Приступим к подготовке к установке:

apt update apt install wget nano -y

Установка MySQLTuner достаточно тривиальна:

cd / wget https://github.com/major/MySQLTuner-perl/tarball/master tar xf master rm master cd /major-MySQLTuner-perl-9cf48b5/

Чтобы каждый раз не вводить логин и пароль, можно создать специальный файл ~/.my.cnf содержащий данные администратора БД:

[client] user=someusername pass=thatuserspassword ./mysqltuner.pl --defaults-file=~/my.cnf

С настройками по-умолчанию отчёт будет выглядеть примерно так:

>> MySQLTuner 1.7.8 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 10.2.10-MariaDB-10.2.10+maria~jessie [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: /var/lib/mysql/ec3476d51dbf.err(0B) [!!] Log file /var/lib/mysql/ec3476d51dbf.err doesn't exist [!!] Log file /var/lib/mysql/ec3476d51dbf.err isn't readable. -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE +SPHINX [--] Data in Aria tables: 3M (Tables: 1) [--] Data in InnoDB tables: 45G (Tables: 108) [!!] Total fragmented tables: 1 -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] User 'app@%' hasn't specific host restriction. [--] There are 612 basic passwords in the list. -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 2d 17h 8m 9s (252K q [1.075 qps], 7K conn, TX: 88M, RX: 31M) [--] Reads / Writes: 57% / 43% [--] Binary logging is disabled [--] Physical Memory : 31.0G [--] Max MySQL memory : 1.3G [--] Other process memory: 17.7M [--] Total buffers: 616.0M global + 7.5M per thread (100 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 646.1M (2.04% of installed RAM) [OK] Maximum possible memory usage: 1.3G (4.32% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (0/252K) [OK] Highest usage of available connections: 4% (4/100) [OK] Aborted connections: 0.26% (19/7425) [!!] Query cache may be disabled by default due to mutex contention. [OK] Query cache efficiency: 48.1% (192K cached / 399K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 23 sorts) [OK] No joins without indexes [!!] Temporary tables created on disk: 62% (167 on disk / 267 total) [OK] Thread cache hit rate: 99% (11 created / 7K connections) [OK] Table cache hit rate: 95% (123 open / 129 opened) [OK] Open file limit used: 0% (29/65K) [OK] Table locks acquired immediately: 100% (24 immediate / 24 locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 8 thread(s). [--] Using default value is good enough for your version (10.2.10-MariaDB-10.2.10+maria~jessie) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.2% (24M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/124.0K [!!] Read Key buffer hit rate: 87.5% (16 cached / 2 reads) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 256.0M/45.3G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (37.5 %): 48.0M * 2/256.0M should be equal 25% [OK] InnoDB buffer pool instances: 1 [--] Number of InnoDB Buffer Pool Chunk : 2 for 1 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 92.19% (1313501 hits/ 1424772 total) [!!] InnoDB Write Log efficiency: 64.27% (35911 hits/ 55871 total) [OK] InnoDB log waits: 0.00% (0 waits / 19960 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/1.4M [!!] Aria pagecache hit rate: 91.7% (2K cached / 167 reads) -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- RocksDB Metrics --------------------------------------------------------------------------- [--] RocksDB is disabled. -------- Spider Metrics ---------------------------------------------------------------------------- [--] Spider is disabled. -------- Connect Metrics --------------------------------------------------------------------------- [--] Connect is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: MIXED [--] XA support enabled: ON [--] Semi synchronous replication Master: Not Activated [--] Semi synchronous replication Slave: Not Activated [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance OPTIMIZE TABLE `app`.`log`; -- can free 9493 MB Total freed space after theses OPTIMIZE TABLE : 9493 Mb Restrict Host for user@% to user@SpecificDNSorIp When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Performance should be activated for better diagnostics Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS Variables to adjust: query_cache_size (=0) query_cache_type (=0) tmp_table_size (> 32M) max_heap_table_size (> 32M) performance_schema = ON enable PFS innodb_buffer_pool_size (>= 45G) if possible.

Установка MySQL Tuning Primer Script

Ещё один скрипт для автоматической проверки конфигурации MySQL-сервера, который так же даёт некоторые советы по оптимизации.

apt install bc net-tools -y wget https://launchpadlibrarian.net/78745738/tuning-primer.sh chmod +x tuning-primer.sh ./tuning-primer.sh

Результат работы скрипта примерно такой:

-- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 10.2.10-MariaDB-10.2.10+maria~jessie x86_64 Uptime = 1 days 6 hrs 28 min 15 sec Avg. qps = 13 Total Questions = 1464526 Threads Connected = 79 Warning: Server has not been running for at least 48hrs. It may not be safe to use these recommendations To find out more information on how each of these runtime variables effects performance visit: http://dev.mysql.com/doc/refman/10.2/en/server-system-variables.html Visit http://www.mysql.com/products/enterprise/advisors.html for info about MySQL's Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is NOT enabled. Current long_query_time = 10.000000 sec. You have 0 out of 1464560 that take longer than 10.000000 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See http://dev.mysql.com/doc/refman/10.2/en/point-in-time-recovery.html WORKER THREADS Current thread_cache_size = 100 Current threads_cached = 23 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 100 Current threads_connected = 79 Historic max_used_connections = 101 The number of used connections is 101% of the configured maximum. You should raise max_connections No InnoDB Support Enabled! MEMORY USAGE Max Memory Ever Allocated : 8.93 G Configured Max Per-thread Buffers : 753 M Configured Max Global Buffers : 8.19 G Configured Max Memory Limit : 8.93 G Physical Memory : 30.96 G Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 124 K Current key_buffer_size = 128 M Key cache miss rate is 1 : 42 Key buffer free ratio = 81 % Your key_buffer_size seems to be fine QUERY CACHE Query cache is enabled Current query_cache_size = 64 M Current query_cache_used = 23 M Current query_cache_limit = 128 K Current Query cache Memory fill ratio = 36.27 % Current query_cache_min_res_unit = 4 K MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 4 M Current read_rnd_buffer_size = 1 M Sort buffer seems to be fine JOINS ./tuning-primer.sh: 401: local: 2097152: bad variable name root@2cb99988447a:/# nano +943 tuning-primer.sh root@2cb99988447a:/# root@2cb99988447a:/# root@2cb99988447a:/# ./tuning-primer.sh -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 10.2.10-MariaDB-10.2.10+maria~jessie x86_64 Uptime = 1 days 6 hrs 29 min 49 sec Avg. qps = 13 Total Questions = 1478085 Threads Connected = 6 Warning: Server has not been running for at least 48hrs. It may not be safe to use these recommendations To find out more information on how each of these runtime variables effects performance visit: http://dev.mysql.com/doc/refman/10.2/en/server-system-variables.html Visit http://www.mysql.com/products/enterprise/advisors.html for info about MySQL's Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is NOT enabled. Current long_query_time = 10.000000 sec. You have 0 out of 1478106 that take longer than 10.000000 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See http://dev.mysql.com/doc/refman/10.2/en/point-in-time-recovery.html WORKER THREADS Current thread_cache_size = 100 Current threads_cached = 96 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 100 Current threads_connected = 6 Historic max_used_connections = 101 The number of used connections is 101% of the configured maximum. You should raise max_connections No InnoDB Support Enabled! MEMORY USAGE Max Memory Ever Allocated : 8.93 G Configured Max Per-thread Buffers : 753 M Configured Max Global Buffers : 8.19 G Configured Max Memory Limit : 8.93 G Physical Memory : 30.96 G Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 124 K Current key_buffer_size = 128 M Key cache miss rate is 1 : 43 Key buffer free ratio = 81 % Your key_buffer_size seems to be fine QUERY CACHE Query cache is enabled Current query_cache_size = 64 M Current query_cache_used = 23 M Current query_cache_limit = 128 K Current Query cache Memory fill ratio = 36.27 % Current query_cache_min_res_unit = 4 K MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 4 M Current read_rnd_buffer_size = 1 M Sort buffer seems to be fine JOINS Current join_buffer_size = 260.00 K You have had 2 queries where a join could not use an index properly You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. If you are unable to optimize your queries you may want to increase your join_buffer_size to accommodate larger joins in one pass. Note! This script will still suggest raising the join_buffer_size when ANY joins not using indexes are found. OPEN FILES LIMIT Current open_files_limit = 65536 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_open_cache = 400 tables Current table_definition_cache = 400 tables You have a total of 192 tables You have 400 open tables. Current table_cache hit rate is 35% , while 100% of your table cache is in use You should probably increase your table_cache TEMP TABLES Current max_heap_table_size = 32 M Current tmp_table_size = 32 M Of 13212 temp tables, 2% were created on disk Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 2 M Current table scan ratio = 31 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 0 : 1478289 Your table locking seems to be fine

Если в конце вывода вы видите что-то на подобии:

./tuning-primer.sh: 401: local: 2097152: bad variable name

то нужно вручную поправить скрипт, т.к. в нём до сих пор присутствует баг, который зарепортили ещё в 2013 году! Возникает он при использовании MariaDB, а не чистого MySQL сервера. Открываем файл uning-primer.sh на 943 строке:

nano +943 tuning-primer.sh

и заменяем

mysql_variable \'join_buffer%\' join_buffer_size

на:

mysql_variable \'join_buffer_size%\' join_buffer_size

После чего можно запускать скрипт повторно.

Первичная оптимизация MySQL сервера

Под первичной оптимизацией я подразумеваю тот тюнинг, который можно произвести зная только информацию о железе, по большей части объёме доступной оперативной памяти, а также о объёме и структуре данных. Фактически вся оптимизация сводится к двум действиям:

Некоторые из «оптимизаций» могут быть не рекомендуемыми либо требующими особых условий эксплуатации, например, настроенной репликации.

Смотрим и изучаем отчёт. Какой можно сделать из этого вывод? Сразу же можно смотреть последние секции General recommendations и Variables to adjust, а так же все пункты с пометкой [!!]. Давайте начнём с конца.

Тюнинг innodb_buffer_pool_size в MySQL

Тюнер предлагает увеличить размер параметра innodb_buffer_pool_size до 45G и более. Давайте посмотрим текущее значением запросом:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Скорее всего его значение будет 268435456, т.е. 256 Мб, что очень мало для современных приложений, вот тюнер и предлагает увеличить его до 45 Гб. Но откуда он взял такую цифру? Всё просто, именно такой объём в данный момент занимают данные в InnoDB хранилище, о чём было указано выше:

[--] Data in InnoDB tables: 45G (Tables: 108)

Параметр innodb_buffer_pool_size отвечает за максимальный объём оперативной памяти, которая будет выделена для хранения данных и индексов InnoDB-таблиц. Фактически тюнер рекомендует выделить столько RAM, сколько занимают все данные. По хорошему к этому значению нужно добавить ещё 15-25%, т.к. размер базы данных со временем увеличивается. Однако, MySQLTuner не учитывает, что не все данные одинаково полезны, а некоторые и вовсе не нужны.

Узнать размер каждой конкретной таблицы можно с помощью запроса:

SELECT table_schema, table_name, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.tables ORDER BY data_length + index_length DESC;

В моём случае объём полезных данных приложения занимает порядка 3 Гб, всё остальное — логи, несущие только историческую ценность. Если брать с запасом, то 8 Гб должно хватить с хорошим запасом.

Как отредактировать конфиг MariaDB в Docker?

Конечно, ваше право, можно не заморачиваться и редактировать конфигурацию напрямую в файле /etc/mysql/my.cnf, однако, лучше потратить немного времени и вынести конфигурацию приложения в отдельный файл.

Проблема в том, что нельзя просто так взять и отредактировать конфиг внутри docker-контейнера. т.к. при его пересоздании все эти данные потеряются, то нужно прокидывать конфиг внутрь контейнера из постоянного хранилища. Создадим файл storage/mariadb/etc/mysql/conf.d/app.cnf с содержимым:

[mysqld] innodb_buffer_pool_size = 8GB

Затем добавим этот файл как волюм в docker-compose.yml:

mariadb: volumes: - ./storage/mariadb/etc/mysql/conf.d/app.cnf:/etc/mysql/conf.d/app.cnf

После чего пересоберём и перезапустим контейнер с MySQL:

/usr/local/bin/docker-compose up -d --no-deps --build mariadb

И проверим:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Значение изменилось и в моём случае стало 8589934592, т.е. 8 Гб.

Настройка innodb_log_file_size в MySQL

Следующая по важности опция для оптимизации. MySQLTuner советует установить размер этого параметра равным 25% от buffer pool size, в моём случае 25% от 8Гб это  2 Гб. По-умолчанию он имеет размер 50 Мб:

SHOW VARIABLES LIKE 'innodb_log_file_size';

Этот параметр устанавливает размер лога операций  и влияет на скорость записи данных на диск. Чем больше размер лога, тем быстрее будет происходить запись данных. MySQL имеет сразу 2 файла с логом, а опция влияет на размер каждого файла, т.е. установив значение 1 Гб выделится 2 Гб по одному на каждый лог. Есть и обратная сторона, чем больше файл с логом, тем больше времени система будет восстанавливаться во время сбоев т.к. будет много данных которые нужно применить из лога операций.

Собственно в файл storage/mariadb/etc/mysql/conf.d/app.cnf добавляем строку:

innodb_log_file_size = 1GB

И перезапускаем MySQL сервер, пересоздавать контейнер на этот раз не нужно:

/usr/local/bin/docker-compose restart mariadb

Настройка innodb_log_buffer_size

Параметр отвечает за размер буфера ещё незакомиченных транзакций. Значение стоит увеличивать если вы используете большие поля вроде BLOB или TEXT. По-умолчанию составляет 8 Мб, чего хватает для большинства приложений.

Тюнинг innodb_flush_log_at_trx_commit

Параметр innodb_flush_log_at_trx_commit определяет, как именно MySQL сервер будет писать в лог на диске данные о транзакциях и имеет три допустимых значения: 0, 1, 2. Тюнинг этого параметра повысит скорость записи в базу данных в десятки и сотни раз. По-умолчанию это значение установлено в значение 1, что даёт самые надежные гарантии сохранности данных, но и является при этом самым медленным режимом.

Если потерять даже 0.000000000001% записей для вашей БД критично — то оставляйте значение 1. Такая настройка будет идеальна для приложений работающих с деньгами или имуществом.

Если же небольшая потеря данных в экстремальных условиях не критична, то смело выставляйте innodb_flush_log_at_trx_commit в значение 2. В этом режиме транзакции будут сохраняться в кэш операционной системы, а запись лога на диск остаётся на совести ОС. Данные могут быть утеряны лишь в случае краха ОС и лишь за несколько секунд, что зависит от настроек операционной системы. Такой случай подойдёт для социальных сетей и прочих приложений, в которых пользователи совершают действия. Потеря нескольких лайков не окажет никакого влияния и скорее всего этого никто не заметит.

При значении равном 0 лог сбрасывается на диск один раз в секунду, вне зависимости от происходящих транзакций. Скорость записи возрастает до космических масштабов, но так же растёт и риск эти данные потерять. Данные могут быть утеряны как при крахе ОС, так и при крахеMySQL сервера и обычно не более, чем за 1-2 последних секунды. Этот режим идеально подойдёт для тех ситуаций, когда вы легко сможете восстановить данные, например из реплики. Либо вы работаете с API-сервисами и при потере данных сможете их перезапросить.

Изменяем конфиг storage/mariadb/etc/mysql/conf.d/app.cnf:

innodb_flush_log_at_trx_commit = 2

Рестартим:

/usr/local/bin/docker-compose restart mariadb

Проверяем:

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

Оптимизация innodb_doublewrite в MariaDB

Ещё одна интересная опция включенная по-умолчанию:

SHOW VARIABLES LIKE 'innodb_doublewrite';

Doublewrite представляет собой буфер двойной записи и используется в InnoDB чтобы изменённые страницы были записаны в файл данных. Позволяет избежать потери данных при внезапном сбое сервера. В этом режиме InnoDB перед записью страниц в основной файл данных предварительно записывает их в непрерывную область — doublewrite. Только после записи в этот буфер производится запись страниц на соответствующие позиции в файле данных. Если произошёл сбой операционной системы в процессе записи страницы, то при восстановлении InnoDB движок возьмёт копию страницы из буфера doublewrite.

Если на сервере используется файловая система ZFS, то буфер двойной записи можно смело отключать, т.к. у этой ФС есть свой механизм обеспечения целостности данных. В целом, хоть параметр и содержит в своём названии слово double, его отключение не ускоряет процесс записи в 2 раза. В среднем пользователи отмечают только 5-10% прирост производительности. Рисковать ли данными ради этого — решайте сами.

skip-innodb_doublewrite

или:

innodb_doublewrite = 0

В целом не рекомендуется отключать на продакшене при работе с ценными данными, т.к. в результате сбоя сервера повреждается файл с данными без возможности сделать repair. В случае повреждения файлы спасёт только бэкап или реплика.

Тюнинг с помощью уровней изоляции транзакций

По-умолчанию уровень изоляции транзакций выставлен в REPEATABLE-READ. Сильнее только SERIALIZABLE. А что, если понизить его до READ COMMITTED? Для некоторых приложений это позволит ещё немного уменьшить время на выполнение запросов. Однако, нужно быть уверенным, что смена уровня изоляции не нарушит консистентность данных в приложении. В некоторых ситуациях можно вообще перейти на самый низкий уровень изоляции — READ UNCOMMITED. Например, во время обслуживания базы данных: загрузки дампов, и т.п.

SHOW VARIABLES LIKE 'tx_isolation'; transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

Можно менять уровень изоляции для отдельно взятой сессии или нового соединения таким образом:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

При использовании ключевого слова SESSION устанавливается уровень изоляции по-умолчанию для всех будущих транзакций, выполняемых в текущем соединении.

По-умолчанию уровень изоляции устанавливается для следующей (ещё не начальной) транзакции. При использовании ключевого слова GLOBAL устанавливается уровень изоляции по-умолчанию глобально для всех новых соединений. Чтобы изменить изоляцию глобально необходима привилегия SUPER.

Настройка query_cache_size в MySQL

Параметр определяет объём оперативной памяти выделяемый сервером под кэш запросов. На практике этот механизм работает не очень эффективно, т.к. кэш запросов для таблицы очищается каждый раз, когда в таблице проиcходят вставка или изменение строк. Такой подход может оказаться неэффективным для приложений с большим количеством запросов на изменение таблиц. Это приводит к тому, что таблицы блокируются в режиме Waiting for query cache lock.

Изменяем конфиг storage/mariadb/etc/mysql/conf.d/app.cnf:

query_cache_size = 0

Рестартим:

/usr/local/bin/docker-compose restart mariadb

Проверяем:

SHOW VARIABLES LIKE 'query_cache_size';

Если кэш запросов всё же включен, то можно посмотреть его статистику с помощью запроса:

SHOW STATUS LIKE 'Qcache%';

В более удобном виде эту информацию выдаёт MySQL Tuning Primer Script:

QUERY CACHE Query cache is enabled Current query_cache_size = 64 M Current query_cache_used = 11 M Current query_cache_limit = 128 K Current Query cache Memory fill ratio = 17.70 % Current query_cache_min_res_unit = 4 K Your query_cache_size seems to be too high. Perhaps you can use these resources elsewhere MySQL won't cache query results that are larger than query_cache_limit in size

В моём случае под кэш запросов по-умолчанию выделилось 64 Мб, однако используется всего 11 Мб.

Тюнинг max_heap_table_size и tmp_table_size

Чаще всего эти два параметра настраиваются вместе и устанавливаются в одно и то же значение. Параметр max_heap_table_size отвечает за максимально допустимый размер таблицы типа MEMORY хранящейся в оперативной памяти. Значение по умолчанию 32 Мб, если ваше приложение не использует MEMORY таблицы, то установите это значение равным tmp_table_size.

Параметр tmp_table_size отвечает за максимальный размер оперативной памяти выделяемой для временных служебных таблиц. Это значение также зависит от значения max_heap_table_size, и в итоге будет выбрано минимальное значение между max_heap_table_size и tmp_table_size, а остальные временные таблицы будут создаваться на диске. Значение по-умолчанию так же равняется 32 Мб.

Необходимые значения сильно зависят от ваших данных, от методов их обработки, от количества клиентов и частоты выполнения сложных запросов. Попробуйте экспериментальным способом найти значения, при которых запросы не будут создавать временных файлов на диске. Именно создание временных таблиц на диске сильнее всего тормозит сложные SELECT запросы на сортировках и группировках.

tmp_table_size = 64M max_heap_table_size = 64M

А если позволяют ресурсы, то можно и:

tmp_table_size = 2048M max_heap_table_size = 2048M

Параметры wait_timeout и interactive_timeout

В параметре interactive_timeout указано время в секундах, отвечающих за ожидание активности со стороны интерактивного соединения (использующего флаг CLIENT_INTERACTIVE), прежде чем закрыть его.

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

SHOW VARIABLES LIKE 'wait_timeout'; SHOW VARIABLES LIKE 'interactive_timeout';

По-умолчанию оба параметра установлены в 28 800 секунд, что составляет 8 часов. Далеко не каждое приложение может похвастаться таким временем жизни запущенного скрипта. Для большинства приложений с запасом хватит и 30 секунд. Для веб-сайтов вряд ли имеет смысл выставлять это значение больше 3-5 секунд.

wait_timeout = 5 interactive_timeout = 5

Вторичная оптимизация конфига MySQL

Под вторичной оптимизацией я подразумеваю тот тюнинг, который можно произвести только зная профиль нагрузки на БД: соотношение операций чтения и записи, долгие запросы и т.п.

Тюнинг performance_schema в MySQL

Опция performance_schema производит мониторинг всей БД, на что расходуется некоторая часть ресурсов, держать эту опцию постоянно включенной в продакшене крайне не рекомендуется, т.к. может замедлять время выполнения запросов до 25%. Объем потребляемых ресурсов зависит от конфигурации схемы, которую можно посмотреть выполнив запрос:

SHOW VARIABLES LIKE 'performance%';

А если выполнить от имени администратора БД этот запрос:

SHOW ENGINE performance_schema STATUS;

С помощью этого запроса можно понять все ли данные мониторятся, или что-то пропадает:

SHOW STATUS LIKE 'performance%';

Если какой-то счетчик оказался выше ноля, то нужно увеличить соответствующий параметр.

Именно на данных из performance_schema и основана вся фишка MySQLTuner! Чем дольше собираются данные, тем точнее будут рекомендации по оптимизации MySQL и MariaDB. Стоит учесть, что данные performance_schema обнуляются после каждой перезагрузки сервера, поэтому сначала лучше выполнить первичную конфигурацию, после чего оставить сервер под боевой нагрузкой на сутки для последующего анализа.

Работа с данными performance_schema

Переходим в базу данных performance_schema :

USE performance_schema;

И смотрим какие таблицы здесь есть:

SHOW TABLES;

Обратим внимание на наблицы с префиксом setup_, например:

SELECT * FROM setup_consumers; SELECT * FROM setup_instruments;

В них содержатся настройки того, что будет мониториться. С помощью UPDATE можно менять значение колонки ENABLED с NO на YES и наоборот.

Самые горячие таблицы

С помощью этого запроса можно узнать к каким таблицам происходит наибольшее число чтений и записей:

select substring_index(file_name, '/', -1) file_name, event_name, count_read, count_write from file_summary_by_instance where COUNT_READ+COUNT_WRITE > 0 order by COUNT_READ+COUNT_WRITE desc limit 30;

А этим запросом можно узнать статистику по блокировкам:

select event_name, source, sum(timer_wait) timer_wait from events_waits_history_long where event_name not like 'wait/io/file%' group by event_name, source order by 3 desc limit 30;

Тюнинг MySQL для самых маленьких

Если вы впервые сталкиваетесь с оптимизацией сервера MySQL, то эти пункты помогут встать на истиный путь:

Читайте также

evilinside.ru

Prostoy-Site | Все права защищены © 2018 | Карта сайта