Юникс и другие

Мой предыдущий пост Основы оптимизации производительности InnoDB (Перевод) получил большое количество комментариев по поводу оптимального значения innodb_buffer_pool_size. Я действительно не слишком подробно описал проблему, посему исправляюсь.

Пул буферов InnoDB - это очень важный параметр и для него должно быть выбрано правильное значение. Мне приходилось видеть множество людей, которые оставляли значение по умолчанию (8М) и получали из-за этого проблемы. Если у вас есть выделенный сервер для MySQL и вы используете только innodb-таблицы, возможно вы захотите отдать всю неиспользуемую память для пула буферов InnoDB.

Конечно же предполагается, что размер ваших InnoDB-таблиц достаточно велик для большого размера пула. Если нет, то установите размер пула немного большим, чем размер ваших таблиц. Несомненно, стоит учесть дальнейшее увеличение объема ваших баз данных. Размер пула должен быть немного (скажем, на 10%) большим, чем размер ваших таблиц, потому что в нем содержатся не только сами таблицы, но также и индексы, вставленные буферы, блокировки, которым тоже необходимо некоторое количество памяти. Но это не очень критично. На большинстве задач размер пула, меньший чем 10% особо не отразится на производительности.

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

Необходимо помнить о том, что InnoDB выделяет немного больше памяти для хранения пула, чем задано в конфигурации - я только что проверил на наших серверах с размером буфера 12GB и очень маленьким дополнительным буфером и лог-буфером. InnoDB занимал там немного больше 13GB. Некоторое время назад Вадим писал об этом.

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

Следующий шаг, который необходимо сделать - это рассчитать объем памяти, необходимый остальным приложениям. Это могут быть нужды операционной системы - системные процессы, буферы сокетов и так далее :) Всем нужна память. Я использую примерно такие интервалы: от 256MB (на маленьких системах) до 5% от всего объема (соответсвенно, на больших :)). Хотя реальные потребности могут быть и меньше. Помимо ОС самому серверу MySQL также необходима дополнительная память - это буферы MySQL, кэш запросов, key_buffer, нити, временные таблицы, буферы сортировки в нитях. На это тоже необъодимо выделить память. Также подумайте о доаолнительных буферах InnoDB (которые могут занимать бОльший объем памяти, чем вы установили для них. Особенно если у вас много таблиц).

Я мог бы начать приводить вам какие-то цифры. Например, сложите все ваши глобальные буферы и добавьте по 1 мегабайту на каждое планируемое соединение. Но в реальности эти числа могут сильно отличаться - все зависит от конкретных задач. К примеру неактивные соединения будут потреблять меньше памяти, чем работающие с временными таблицами. Или чем другие более сложные запросы к БД. Как правило, это гораздо проще и быстрее проверить на практике. Запустите mysql-server с буфером размером 10GB для начала и посмотрите, как увеличиваются RSS и VSZ (их значения можно увидеть в выводе команды `ps` на Unix-системах). Если буфер увеличился до 12GB, а вам необходимо 2GB под иные задачи, вы сможете немнго увеличить его размер, в соответствии с вашими потребностями.

Третий важный момент, о котором необходимо помнить - это кэш операционной системы. Даже если вы и исключили ваши InnoDB-таблицы из кэша ОС, есть и другие процессы, которым этот кэш необходим: таблицы MyISAM (собственные баз данных mysql, временные таблицы и так далее), frm-файлы, логи.. Логи транзакций InnoDB тоже неплохо кэшировать там. Ну и конечно же у вас есть системные процессы, которым тоже необходимо некоторое количество кэш-памяти. Значение этого параметра сильно варьируется в зависимости от системы и задач. Обычно значения этого параметра лежат в диапазоне от 200MB до 1GB.

Отключите двойную буферизацию - это опять-таки очень важно для выбора размера пула. Вы же не хотите, чтобы операционная система кэшировала данные, уже находящиеся в кэше InnoDB. Собственный кэш более эффективен, чем кэш ОС, так как он не копирует данные, адаптирует хэш-индексы, позволяет писать в буферы.. Есть множество факторов, чтобы отказаться от использования двойной буферизации. Тем более, как уже говорилось выше, кэш ОС нужен не только для InnoDB, и если вы не отключите двойную буферизацию, InnoDB попросту затрет весь ваш кэш. На win-системах вам не нужно ничего менять. На Linux, FreeBSD, Solaris вам нужно задать параметр innodb_flush_method=O_DIRECT. На других системах вы сможете блокировать двойную буфферизацию на уровне ОС, но будьте уверены в своих действиях.
Но бывают ситуации, когда отключение двойной буфферизации не очень полезно. Например, если у вас нет RAID с BBU, а для вашей системы характерна интенсивная запись. Но даже в этом случае бывают исключения.

Облегчите работу вашей ОС - хорошей идеей будет запретить MySQL и другим важным процессам использовать файловую систему для кэша. Но ОС может решить, что это “неправильно” - MySQL занимает 95% памяти и лишь несколько процентов кэша на диске. Некоторые администраторы пытаются решить эту пролему, вообще отключая своппинг. Но тут есть подводный камень: ОС может убить процесс MySQL, который вышел за допустимые пределы (или ОС “думает”, что вышел). Такое сожет случиться, например, из-за неожиданно высокой нагрузки с большим числом соединений. Вдобавок не все ядра хорошо работают с отключенным своппингом. Да есть еще масса причин, чтобы так не делать. Люди, которые все-таки отключают использование файла подкачки, обычно подстраховывают себя, обеспечивая достаточное количество свободной памяти для кэша и буферов. Кевин Бартон (Kevin Burton) написал хорошую заметку о его экспериментах

В зависимости от ОС вы можете использовать различные методы стабилизации виртуальной памяти. Например, можно “заставить” MySQL использовать LargePages (большие страницы) памяти для размещение пула буферов InnoDB и использовать другие буферы, что хорошо скажется на производительности системы. Можно подкорректировать работу вирутальной памяти, выполнив echo 0 > /proc/sys/vm/swappiness, но не факт, что это спасет вас от своппинга. Некоторые ядра имеют другие собственные опции для оптимизации работы VM. Наконец, вы можете попробовать “закрепить” процессы MySQL в памяти, используя опцию -memlock, но будьте осторожны - ОС может уничтожить процессы, потребляющие слишком много памяти.

Два момента в использовании вирутальной памяти. Первый: мониторинг swap по показателям “swap used” - не самая удачная идея, так как вы не можете точно сказать, кто пишет в своп - MySQL или другие процессы. Чтобы убедиться в отстутствии активного своппинга, следите за значениями “si/so” в выводе vmstat на linux-системах. Пока там 0 - все в порядке :) Нет ничего страшного в нескольктх обращениях к swap-файлу в минуту. Но если за 60 секунд пишется 100+ страниц, то это уже проблема.

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

Эти рекомендации подходят для 70-80% систем, и, конечно же, не стоит слишком сильно привязываться к цифрам. Если у вас 50GB RAM, то для вас вполне будут приемлемы настройки, описаные для 64GB систем.

P.S. В заметке описаны рекомендации только для InnoDB. Если вы используете иной тип хранения данных (например MyISAM, Falcon, PBXT или Archive), то вы должны оптимизировать работу сервера в соответствии с рекомендациями именно к используемому вами Storage Engine.

Оригинал: http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/