Привет, Хаброжители!

Хотите выжать из MySQL максимум возможностей? Вам поможет уникальная книга, написанная экспертами для экспертов.

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

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

Настройка ввода/вывода в MySQL

Несколько конфигурационных параметров управляют тем, как MySQL синхронизирует данные в памяти и на диске и выполняет восстановление. Эти параметры могут существенно повлиять на производительность, поскольку относятся к весьма затратным операциям ввода/вывода. Кроме того, они определяют компромисс между производительностью и безопасностью данных. Как правило, стремление обеспечить немедленную и непротиворечивую запись ваших данных на диск очень затратно. Если вы готовы пойти на риск, обусловленный тем, что запись на диск еще не обеспечивает постоянного хранения, то можете увеличить степень конкурентности и/или уменьшить время ожидания ввода/вывода. Но вам придется самостоятельно определять допустимый уровень риска.

InnoDB позволяет вам управлять не только способом восстановления, но и тем, как открываются файлы и сбрасываются на диск данные кэшей, что сильно влияет на восстановление и общую производительность. Процесс восстановления в InnoDB полностью автоматический и всегда выполняется в момент запуска InnoDB, хотя вы можете влиять на то, какие действия при этом предпринимаются. Даже если оставить в стороне вопрос о восстановлении и предполагать, что ничего никогда не выйдет из строя или вообще все нормально, у вас в любом случае имеется множество возможностей для конфигурирования InnoDB. Эта подсистема имеет сложную цепочку буферов и файлов, спроектированных так, чтобы добиться высокой производительности и гарантировать свойства ACID, и при этом каждый элемент цепочки можно настраивать. На рис. 5.1 показаны эти файлы и буферы.


Наиболее важные вещи, которые имеет смысл изменить для нормального использования, — это размер файла журнала InnoDB, способ сброса журнального буфера на диск и то, как InnoDB выполняет ввод/вывод.

Журнал транзакций InnoDB

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

InnoDB использует журнал для преобразования произвольного дискового ввода/вывода в последовательный. Как только произведена запись в журнал на диске, транзакции становятся долговечными, хотя изменения еще не были записаны в файлы данных. Если произойдет какая-то авария (например, сбой питания), InnoDB сможет воспроизвести журнал и восстановить закоммиченные транзакции.

Конечно, InnoDB в конечном итоге должна записывать изменения в файлы данных, потому что журнал имеет фиксированный размер. Запись в журнал выполняется циклически: при достижении конца журнала происходит переход к его началу. InnoDB не может затереть запись журнала, если содержащиеся в ней изменения не были занесены в файлы данных, потому что при этом была бы уничтожена единственная долговечная запись о закоммиченной транзакции.

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

Общий размер файла журнала, задаваемый параметрами innodb_log_file_size и innodb_log_files_in_group, очень важен с точки зрения производительности записи. Если вы воспользовались нашей предыдущей рекомендацией и задействовали параметр innodb_dedicated_server, эти параметры будут регулироваться за вас в зависимости от того, сколько памяти имеет ваша система.

Буфер журнала

При модификации любых данных InnoDB помещает запись об изменении в свой буфер журнала, который хранится в памяти. InnoDB сбрасывает буфер в файлы журнала на диске в следующих случаях: когда буфер заполняется, когда производится коммит транзакции или раз в секунду в зависимости от того, что произойдет раньше. Увеличение размера буфера, который по умолчанию составляет 1 Мбайт, может помочь сократить количество операций ввода/вывода в случае больших транзакций. Управляющая размером буфера переменная называется innodb_log_buffer_size.

Обычно нет необходимости делать буфер очень большим. Рекомендуемый размер варьируется в диапазоне 1–8 Мбайт, и обычно этого достаточно, если только вы не вставляете много записей с огромными BLOB. Записи журнала очень компактны по сравнению с обычными данными InnoDB. Они не используют страницы как единицу хранения, поэтому не расходуют место на запись целых страниц каждый раз. InnoDB также стремится максимально сократить записи журнала. Иногда даже в них хранятся лишь несколько целых чисел — номер зарегистрированной операции и параметры, необходимые для ее выполнения!

Как InnoDB сбрасывает буфер журнала

Когда InnoDB сбрасывает буфер журнала в файлы журнала на диске, он блокирует доступ к буферу с помощью мьютекса, переписывает данные на диск вплоть до нужной точки, а затем перемещает все оставшиеся записи в начало буфера. Может случиться так, что к моменту освобождения мьютекса скопится несколько транзакций, готовых к записи в журнал. InnoDB использует механизм группового коммита, позволяющий записать их все в журнал за одну операцию ввода/вывода.

Буфер журнала должен быть сброшен на устройство постоянного хранения, чтобы обеспечить долговечность зафиксированных транзакций. Если производительность вам важнее, чем долговечность, можете изменить innodb_flush_log_at_trx_commit, чтобы контролировать, куда и как часто сбрасывается буфер журнала.

Возможны следующие значения:

• 0 — писать буфер в файл журнала и сбрасывать журнал на устройство постоянного хранения каждую секунду, но ничего не делать в момент коммита транзакции;
• 1 — писать буфер в файл журнала и сбрасывать его в долговременное хранилище каждый раз, когда выполняется коммит транзакции. Это настройка по умолчанию (и самая безопасная), она гарантирует, что вы не потеряете ни одной зафиксированной транзакции, если диск или операционная система не делают операцию сброса фиктивной;
• 2 — писать буфер в файл журнала при каждом коммите, но не сбрасывать его. InnoDB планирует выполнение сброса один раз в секунду. Самое важное отличие от параметра 0 заключается в том, что при значении 2 транзакции не будут потеряны в случае аварийного завершения процесса MySQL. Однако, если весь сервер выйдет из строя или будет нарушено его питание, вы все равно можете потерять транзакции.

Важно понимать разницу между записью буфера в файл журнала и сбросом журнала на устройство долговременного хранения. В большинстве операционных систем запись буфера в журнал сводится к простому копированию данных из буфера памяти InnoDB в кэш операционной системы, который также находится в памяти. Никакой записи на реальное устройство при этом не происходит. Таким образом, настройки 0 и 2 обычно приводят к потере данных не более чем на 1 с в случае сбоя или отключения питания, поскольку в течение этого времени информация, возможно, существует только в кэше операционной системы. Мы говорим «обычно», потому что InnoDB старается сбросить файл журнала на диск примерно раз в секунду при любых обстоятельствах, но в некоторых ситуациях возможна потеря транзакций более чем на секунду, например, когда поток сброса останавливается.

Иногда контроллер жесткого диска или операционная система имитирует сброс, помещая данные в еще один кэш, например в собственный кэш жесткого диска. Этот прием более быстрый, но очень опасный, потому что данные могут быть потеряны, если у диска внезапно исчезнет питание. Такая ситуация даже хуже, чем установка параметра innodb_flush_log_at_trx_commit в любое значение, отличное от 1, потому что может привести к повреждению данных, а не просто к потере транзакций.

Установка для параметра innodb_flush_log_at_trx_commit значения, отличного от 1, может привести к потере транзакций. Однако если долговечность (буква D (durability) в аббревиатуре ACID) для вас не очень важна, то, возможно, вы сочтете полезными другие настройки. Может быть, вам просто нужны иные возможности InnoDB, такие как кластерные индексы, устойчивость к повреждению данных и блокировка на уровне строк.

Наилучшая конфигурация для высокопроизводительных транзакционных приложений получается, если оставить значение innodb_flush_log_at_trx_commit равным 1 и поместить файлы журналов в RAID-том с кэш-памятью записи с батарейным питанием и твердотельными накопителями. Это и безопасно, и очень быстро. На самом деле мы осмеливаемся сказать, что любой производственный сервер базы данных, который должен обрабатывать серьезную рабочую нагрузку, должен иметь такое оборудование.

Как InnoDB открывает и сбрасывает файлы журналов и данных

Параметр innodb_flush_method позволяет вам указать, как InnoDB фактически взаимодействует с файловой системой. Несмотря на свое название, он влияет также на то, как InnoDB читает данные, а не только на то, как он их записывает.

Изменение режима выполнения операций ввода/вывода в InnoDB может существенно повлиять на производительность в целом, поэтому убедитесь, что вы понимаете, что делаете, прежде чем что-либо менять!

Этот параметр может слегка сбить с толку, поскольку он влияет как на файлы журналов, так и на файлы данных и иногда выполняет разные операции для разных типов файлов. Было бы неплохо иметь один параметр конфигурации для журналов, а другой для файлов данных, но они объединены.

Если вы используете Unix-подобную операционную систему и ваш RAID-контроллер имеет кэш записи с резервным питанием от батареи, рекомендуем вам применять O_DIRECT. Если нет, то лучшим выбором, вероятно, будет либо значение по умолчанию, либо O_DIRECT в зависимости от вашего приложения. Как было сказано ранее, этот параметр устанавливается автоматически, если вы решили использовать параметр innodb_dedicated_server.

---

Более подробно с книгой можно ознакомиться на сайте издательства:
» Оглавление
» Отрывок

По факту оплаты бумажной версии книги на e-mail высылается электронная книга.
Для Хаброжителей скидка 25% по купону — MySQL