У меня есть сервер MySQL, в котором есть таблицы InnoDB и MyISAM. Табличное пространство InnoDB довольно мало, менее 4 ГБ. MyISAM большой ~ 250 ГБ, из которых 50 ГБ для индексов.
Наш сервер имеет 32 ГБ оперативной памяти, но обычно использует только ~ 8 ГБ. Наш key_buffer_size составляет всего 2 ГБ. Но наш коэффициент попадания в ключевой кеш составляет ~ 95%. Мне трудно поверить ..
Вот наша основная статистика:
| Key_blocks_not_flasted | 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 в кеш ОС для чтения данных вместо диска?
Я легко могу поверить, что процент попаданий в ключевой кеш составляет 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#sysvar_key_buffer_size:
Вы можете увеличить значение, чтобы улучшить обработку индекса для всех операций чтения и множественной записи; в системе, основной функцией которой является запуск 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
Таким образом, коэффициент попадания в кэш-память (KHR) - это формула
KHR = 100 * (KRR_DELTA - KRD_DELTA) / KRR_DELTA
Вам нужен коэффициент попадания KeyCache 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;