Как использовать большую часть памяти, доступной на MySQL
У меня есть сервер MySQL с таблицами InnoDB и MyISAM. Табличное пространство InnoDB довольно мало - до 4 ГБ. MyISAM большой ~250 ГБ, из которых 50 ГБ для индексов.
Наш сервер имеет 32 ГБ оперативной памяти, но обычно он использует только ~8 ГБ. Наш key_buffer_size составляет всего 2 ГБ. Но наш коэффициент попадания в кеш ключа составляет ~95%. Мне трудно поверить..
Вот наша ключевая статистика:
| Key_blocks_not_flushed | 1868 | | Key_blocks_unused | 109806 | | Key_blocks_used | 1714736 | | Key_read_requests | 19224818713 | | Key_reads | 60742294 | | Key_write_requests | 1607946768 | | Key_writes | 64788819 |
key_cache_block_size по умолчанию на 1024.
У нас есть 52 ГБ индексных данных и 2 ГБ кеша ключей достаточно, чтобы получить коэффициент совпадения 95%. Это возможно? С другой стороны, объем данных составляет 200 ГБ, и поскольку MyISAM использует кэширование ОС (Centos), я ожидаю, что он будет использовать гораздо больше памяти для кэширования данных myisam, к которым осуществляется доступ. Но на этом этапе я вижу, что key_buffer полностью используется, размер нашего буферного пула для innodb составляет 4 ГБ, а также полностью используется, что добавляет до 6 ГБ. Что означает, что данные кэшируются с использованием всего 1 ГБ?
Мой вопрос: как я могу проверить, где можно использовать всю свободную память? Как я могу проверить, попадает ли MyISAM в кэш ОС для чтения данных вместо диска?
1 ответ
Я легко могу поверить в 95% -ную частоту попаданий в кеш. Послушайте вашу статистику:
Вы заявили, что у вас есть 50 ГБ индексов для таблиц MyISAM
Кэш ключей предназначен для кэширования страниц индекса для таблиц MyISAM. Эта опция устанавливается key_buffer_size вариант
Вы сказали, что он установлен на 2G. Неудивительно, что процент попаданий составляет 95%. Индексные блоки выгружаются в кеш ключа и из него постоянно. Десятки запросов, нуждающихся в определенной строке, загружаются в кэш ключа после того, как MySQL обнаружит, что информация индекса (.MYI) по первому необходимому запросу изначально не была там. Все последующие запросы, требующие одинаковой информации.MYI для поиска данных (.MYD), уже будут кэшироваться после того, как первый запрос загрузит их. MySQL НЕ кэширует данные MyISAM в своих собственных кэшах.
Вы должны быть в состоянии установить key_buffer_size до 8G.
Вот выдержка из http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html:
Вы можете увеличить значение, чтобы получить лучшую обработку индекса для всех операций чтения и множественной записи; в системе, основной функцией которой является запуск MySQL с использованием механизма хранения MyISAM, 25% общего объема памяти машины является приемлемым значением для этой переменной. Однако вы должны знать, что, если вы сделаете значение слишком большим (например, более 50% общей памяти машины), ваша система может начать пейджинг и работать очень медленно. Это связано с тем, что MySQL использует операционную систему для выполнения кэширования файловой системы при чтении данных, поэтому вы должны оставить некоторое место для кэша файловой системы. Вам также следует учитывать требования к памяти любых других механизмов хранения, которые вы можете использовать в дополнение к MyISAM.
Этот же URL-адрес объясняет, что максимальный размер key_buffer_size для 32-битной ОС равен 4G. Вы не должны идти дальше 8G для key_buffer_size с вашим заданным конфигом сервера.
Что касается чтения ваших данных, единственная переменная состояния, которая вам нужна, это Key_reads. Это указывает на то, как часто индексная страница должна выбираться из файла.MYI.
То, что вам нужно контролировать для вас коэффициент попадания в кеш-ключ является следующим
KRR_NOW = Key_read_requests Now
KRR_SEC = Key_read_requests one ago
KRD_NOW = Key_reads Now
KRD_SEC = Key_reads one second ago
KRR_DELTA = KRR_NOW - KRR_SEC
KRD_DELTA = KRD_NOW - KRD_SEC
KeyCache Hit Ratio (KHR), поэтому эта формула
KHR = 100 * (KRR_DELTA - KRD_DELTA) / KRR_DELTA
Вы хотите, чтобы коэффициент попадания ключей составлял 99+%
Теперь на оборотной стороне, давайте обсудим InnoDB.
Ваш innodb_buffer_pool_size должен быть установлен в 4G. Это связано с тем, что пул буферов InnoDB кэширует как страницы данных, так и страницы индексов. Вы можете дать innodb_buffer_pool более конкретную цифру, округленную в следующем виде:
SELECT CONCAT(CEILING(ibbytes / POWER(1024,3)),'G') FROM (SELECT SUM(data_length+index_length) ibbytes FROM information_schema.tables where engine='InnoDB') A;