Я довольно большой новичок, когда дело касается настройки MySQL для повышения производительности. И, честно говоря, меня не беспокоит тонкая настройка, чтобы выжать из MySQL все до последнего бита, но я знаю, что самое важное, что нужно сделать, чтобы обеспечить одни из лучших результатов, - это правильно настроить кеши / буферы.
Я пытался упростить задачу, используя только InnoDB в качестве механизма хранения. И у меня есть выделенный сервер для MySQL. У него 8 ГБ ОЗУ, как мне выделить это, чтобы максимизировать производительность? Я хотел бы иметь возможность уместить всю мою базу данных в память для лучшей производительности. База данных составляет около 5 ГБ. Это возможно?
Какой объем памяти следует выделить для кеша запросов? Сколько в буферном пуле InnoDB? Сколько стоит остальная часть компьютера (т.е. процессы, не связанные с MySQL)? И т.п.
Поскольку я не использую MyISAM, мне действительно не нужно помещать много памяти в ключевой кеш, правильно?
Это сложно, не зная о самой базе данных. Есть несколько инструментов, о которых вам следует знать;
О хранении всей базы данных в памяти; Любые запросы, которые вносят изменения в базу данных, останутся открытыми до тех пор, пока не будет произведена запись на диск. Единственное, что может избежать того, чтобы диск стал узким местом, - это диск-контроллер с кешем записи.
Я бы начал со следующих изменений значений по умолчанию:
key_buffer_size = 128M
thread_stack = 128K
thread_cache_size = 8
table_cache = 8192
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M
innodb_buffer_pool_size = 4G
# This is crucial to avoid checkpointing all the time:
innodb_log_file_size = 512M
# If you have control on who consumes the DB, and you don't use hostnames when you've set up permissions - this can help as well.
skip_name_resolve
Затем я смотрел, как идут дела, и пробовал разные вещи, основываясь (среди прочего) на результатах упомянутых выше инструментов. Я также хотел бы графически отображать тенденции с помощью инструмента мониторинга, такого как Мунин или Кактусы, чтобы увидеть, с какой нагрузкой я на самом деле имею дело. Лично я имею большой опыт работы с MySQL-плагинами, поставляемыми с Munin.
ИМХО вы должны уметь идти с
innodb_buffer_pool_size=5G
Это будет 62,5% ОЗУ с достаточным объемом ОЗУ для ОС сервера плюс память для подключений к БД.
@kvisle рекомендуется использовать mysqltuner.pl. Этот сценарий отлично подходит для оценки объема оперативной памяти, выделяемой для join_buffer_size, sort_buffer_size, read_buffer_size и read_rnd_buffer_size. Эти 4 буфера, сложенные вместе, умножаются на max_connections. Этот ответ добавляется в статические буферы (innodb_buffer_pool_size + key_buffer_size). Сообщаются общие суммы. Если эта общая сумма превышает 80% ОЗУ, тогда вы должны уменьшить эти размеры буфера. mysqltuner.pl будет очень полезным в этом отношении.
Поскольку все ваши данные - это InnoDB, вы можете сделать key_buffer_size (буфер кэша ключей для индексов MyISAM) очень низким (я рекомендую 64M).
ОБНОВЛЕНИЕ 2011-10-15 19:55 EDT
Если вы знаете, что у вас будет 5 ГБ данных, тогда моя первая рекомендация была в порядке. Однако я забыл добавить одну вещь:
[mysqld]
innodb_buffer_pool_size=5G
innodb_log_file_size=1280M
Размер файла журнала должен составлять 25% от пула буферов InnoDB.
ОБНОВЛЕНИЕ 2011-10-16 13:36 EDT
Правило 25% основано исключительно на использовании двух файлов журнала. Хотя можно использовать несколько файлов журнала innodb, обычно лучше всего использовать два.
Другие выразились с использованием 25%
Естественно, правило 25% не может работать при большом количестве оперативной памяти. Фактически, самый большой innodb_log_file_size, разрешенный с использованием только 2 файлов журнала, составляет 2047 МБ, поскольку общий размер файла журнала должен быть меньше 4G (4096 МБ).
ПРИМЕР: У одного из клиентов моего работодателя есть сервер БД с ОЗУ 192 ГБ. Невозможно получить файлы журнала размером 48 ГБ. Я просто использую максимальный размер файла журнала innodb, 2047M. Комментарий @Kvisle к моему ответу просто дает ссылку, в которой говорится, что вам не нужно ограничивать себя двумя файлами журнала. Если у вас есть N файлов журнала, они не могут насчитывать 4G. Мое правило 25% просто в идеальном мире (сервер БД с 8 ГБ или меньше).