Настройка MySQL + оптимизация InnoDB. Настройки mysql
Настройка MySQL
Не знаю почему, но по умолчанию настройки MySQL рассчитаны на десктопы 90-х годов. Например, 8Mb памяти под индексы InnoDB. Помните, как Билл Гейтс заявил, что «640 Кб памяти должно хватать каждому». Дефолтные настройки MySQL из этой серии.
Для начала моя выжимка из конфига (4G RAM, AMD Athlon 64 X2 Dual 5600+)
# ТОЛЬКО UTF! ТОЛЬКО ХАРДКОР! collation_server=utf8_general_ci character_set_server=utf8 default-character-set = utf8 # по умолчанию пускай будет InnoDB default-storage-engine = InnoDB key_buffer_size = 512M innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 16M innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 2 innodb_thread_concurrency = 8 join_buffer_size = 8M sort_buffer_size = 8M read_rnd_buffer_size = 8M tmp_table_size = 64M max_heap_table_size = 32M table_cache = 256 log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1 query_cache_type = 2 query_cache_limit = 1M query_cache_size = 32M
Сами настройки можно посмотреть в том же phpMyAdmin во вкладке «Системные переменные».
У MySQL есть несколько настроек, с помощью которых можно разогнать базу до первой космической. Во-первых, настройки по хранению индексов в памяти. Мало того, что индексы значительно ускоряют выборки, но если их хранить в памяти, а не на диске (где они обычно лежат), то профит будет значительным.
key_buffer_size = 512M Таким образом выделяем 512 Mb под индексы таблиц MyISAM. Дело в том, что у меня половина баз в MyISAM (так исторически сложилось). На 99,9% эти базы используются на чтение, так что переходить на InnoDB смысла нет.
innodb_buffer_pool_size = 512MТакой же объем памяти выделяем на таблицы InnoDB.Тут нужно знать меру. Если у вас 1 база размером 100 Mb, то нет смысла выделять 1 Гб памяти – она всё равно не будет использована.Во-вторых, нужно смотреть не на размер таблицы, а на размер индексов. Пример из жизни: таблица 300 000 комментариев весит 300 Мб, а ее индексы занимают в 15 раз меньше, что вполне логично, так как обычно индексы расставляются на числовые и временные столбцы, а не на текст. Посмотреть это опять же можно в phpMyAdmin
innodb_additional_mem_pool_size = 16MРазмер памяти, выделяемый InnoDB для хранения различных внутренних структур.
innodb_flush_method = O_DIRECTТут мы вырубаем буферизацию таблиц для файловой системы и говорим MySQL обращаться к файлам напрямую.
innodb_flush_log_at_trx_commit = 2При каждой транзакции MySQL пишет лог и сбрасывает на диск (значение 1). Значение 2 – сбрасываем в память. Мне не критично потерять транзакции за последние 2 секунды в случае падения сервера.
join_buffer_size = 8M Память для запросов с джойнами, когда объединение происходит без использования индексов.
sort_buffer_size = 8Mread_rnd_buffer_size = 8MПолезно для запросов с сортировкой ORDER BY и группировкой GROUP BY. При малом значении сортировка идет во временной таблице на диске.
tmp_table_size = 64Mmax_heap_table_size = 32MНастройки для хранения временных таблиц в памяти. Временные таблицы часто образуются при больших джойнах.
table_cache = 256Максимальное число одновременно открытых таблиц.
log_slow_queries = /var/log/mysql/mysql-slow.loglong_query_time = 1Пишем в лог медленные запросы
query_cache_type = 2query_cache_limit = 1Mquery_cache_size = 32MКэширование запросов внутри MySQL
Вроде всё.
ekimoff.ru
Оптимальная настройка MySQL сервера » Блог. ArtKiev Design Studio
Конфигурационные параметры по умолчанию в Mysql расчитаны на небольшие базы данных, работающие под малыми наргузками на весьма скромном железе. Если Ваши планы насчет Mysql выходят за границы таблиц на несколько сотен записей, Вам обязательно придется менять настройки по умолчанию. Процесс оптимальной настройки Mysql состоит из двух частей - первоначальная настройка и корректировака параметров во время работы. Корректировка параметров в рабочем режиме во многом зависит от специфики Вашей системы и ее мониторинга - тут особых правил не существует. Для стартовой настройки есть ряд рекомендаций:
MySQL — свободная система управления базами данных. Разработку и поддержку MySQL осуществляет корпорация Oracle, получившая права на торговую марку вместе с поглощённой Sun Microsystems, которая ранее приобрела шведскую компанию MySQL AB. Продукт распространяется как под GNU General Public License, так и под собственной коммерческой лицензией. Помимо этого разработчики создают функциональность по заказу лицензионных пользователей, именно благодаря такому заказу почти в самых ранних версиях появился механизм репликации.
Откройте файл настроек mysql, например:
/etc/mysql/my.cnf
Сымае распространенные параметры, на которые стоит обратить внимание и изменить под Ваши требования:
key_buffer_size
Если Вы используете только MyIsam таблицы, устанавливайте это значение в 30%…40% всей доступной оперативной памяти на сервере. MyIsam использует кеш операционной системы для данных, поэтому учтите, что оставшаяся свободная память понадобится именно для этого. Если же MyIsam таблиц у Вас немного и их совокупный размер маленький - оставьте это значение в пределах 32M.
innodb_buffer_pool_size
Если Вы использутете только InnoDB таблицы, устанавливайте это значение максимально возможным для Вашей системы. Буффер InnoDB кеширует и данные и индексы (а кеш операционной системы не используется), поэтому значение этого ключа стоит устанавливать в 70%…80% доступной памяти.
Если Ваш сервер работает на линкусе или юниксе, не забудьте установить параметр innodb_flush_method в значение “O_DIRECT”, что-бы избежать кеширования на уровне ОС того, что уже кеширует Mysql.
innodb_log_file_size
Обратите внимание на этот параметр, если у Вас предусматривается большой показатель записей. Чем больше размер этого ключа, тем более эффективно будет происходить запись данных. Но учтите, что при этом увеличится время восстановления системы! Этот параметр обычно устанавливают в 64M-512M.
innodb_flush_log_at_trx_commit
Этот параметр в значительной степени влияет на скорость работы (записи) innoDB таблиц.Значение “1″ означает, что любая завершенная транзакция будет синхронно сбрасывать лог на диск.Значение “2″ делает то же самое, только сбрасывает лог не на диск, а в кеш операционной системы. Это значение подойдет в большинстве случаев, т.к. не выполняет дорогой операции записи после каждой транзакции. При этом лог пишется на диск с задержкой в несколько секунд, что весьма безопасно с точки зрения сохранности данных.Значение “0″ даст наибольшую производительность. В этом случае буфер будет сбрасывать в лог файл независимо от транзакций. Устанавливайте этот параметр в “0″ на свой риск, т.к. в этом случае риск потери данных возрастает.
table_cache
Этот ключ определяет память, выделяюмую для хранения открытых таблиц. Если у Вас несколько сотен таблиц, устанавливайте это значение в 1024. Если же у Вас огромное количество соединений, увеличивайте постепенно это значение, т.к. для каждого соединения храниться отдельная запись.
thread_cache
Этот параметр помагает избежать операций создания/уничтожения потоков при соединении к серверу. Установите этот параметр в 16 и наращивайте по мере потребности. Проверяйте показатель “Threads_created”, идеально он должан быть равным нулю:
mysql> show status like ‘threads_created’; +—————–+——–+ | Variable_name | Value | +—————–+——–+ | Threads_created | 423312 | +—————–+——–+query_cache_size
Значение этого параметра определяет сколько памяти стоит использовать под кеш запросов. Не увлекайтесь установкой огромных значений. Кеш запросов не должен быть большим, т.к. mysql будет съедать ресурсы на управление данными в кеше. Начните с 32М…128М, и увеличивайте по мере необходимости.
artkiev.com
Оптимальные настройки MySQL, какие они? Как правильно сконфигурировать MySQL?
MySQL-сервер, как и любой другой сложный продукт, имеет множество настроек. К сожалению, не существует какого-то универсального конфига, который бы подходил на любые случаи жизни, ведь решаемые задачи и используемые приложения у каждого разные. Кроме того, значения многих настроек в первую очередь определяются конфигурацией оборудования: объёмом доступной оперативной памяти, количеству ядер процессора и т.д. Поэтому ниже дан лишь краткий перечень параметров конфигурации с пояснениями к ним, из которых с минимальными усилиями вы сможете построить себе практически оптимальную конфигурацию MySQL, т.е. правильно настроить MySQL-сервер.
Важное замечание. Всё ниженаписанное является моим ЛИЧНЫМ мнением и не претендует на абсолютную правильность!
low-priority-updates
Уменьшает приоритет операций обновления данных (UPDATE/INSERT), отдавая преимущества операциям чтения. Улучшает производительность на системах, где в основном используется чтение данных (SELECT).
thread_cache_size
Кэш потоков. MySQL-сервер написан как многопоточное (thread - поток, нить, тред) приложение. Следует поставить не менее 16.
query_cache_type
Включает кэш запросов. Установить в значение On в большинстве случаев. Off имеет смысл только в случаях, если вы уверены, что повторяющихся запросов будет крайне мало или не будет совсем.
query_cache_limit
Максимальный размер кэшируемого запроса. Если запрос будет больше данного значения, в кэш он не попадёт. Опять же, вам виднее какие у вас запросы. Если не знаете, оставьте по умолчанию.
query_cache_size
Количество оперативной памяти, которое вы отдадите под кэш. Не стоит думать, что чем больше, тем лучше. Черезмерно большой кэш - это тоже плохо. Начните со 128Mb если у вас меньше 4G оперативной памяти и с 256M, если больше,
key_buffer_size
Количество оперативной памяти, которое вы отдаёте под хранение индексов. Если у вас множество таблиц с индексами, отдайте под этот параметр 10% памяти, которую вы планируете вообще выделить MySQL-серверу. Если в ваших таблицах подавляющую часть занимают данные, а индесов мало, то 3-5% хватит за глаза.
tmp_table_size
Количество оперативной памяти, выделяемое под временные таблицы. Временные таблицы часто неявно создаются при JOIN запросах и запросах с сортировкой. То, что не влезет в tmp_table_size будет создано на диске во временном каталоге.
myisam_sort_buffer_size
Размер буфера, который будет использоваться при различного рода сортировках, если ваши таблицы использует менеджер хранения MYISAM. Начните с 10% от всей памяти, которую вы планировали выделить MySQL-серверу.
read_buffer_size
По умолчанию 128K. Редко бывает нужно менять, но можно попробовать увеличить не более чем до 512K.
read_rnd_buffer_size
Влияет на запросы с ORDER BY. Можно попробовать увеличить до 1-4Mb. Эта величина будет действовать на каждый поток MySQL-сервера, т.е. следует использовать осторожно, если у вас к серверу открывается множество соединений.
sort_buffer_size
Выделяется для операций ORDER BY или GROUP BY на каждый поток. Начните с 1-2Mb. Не следует делать значение слишком большим, потому что возможно ухудшение производительности, а не улучшение.
table_cache
Количество кэшируемых MySQL-сервером таблиц. Поскольку открытие таблицы является затратной (с точки зрения производительности) операцией, лучше чтобы побольше таблиц кэшировалось в память. В идеале это значение должно быть равно общему количеству ваших таблиц.
innodb_buffer_pool_size
Количество оператиной памяти, которое будет использоваться менеджером хранения InnoDB для индексов и данных. Если вы планируете в основном использовать именно InnoDB, то это значение может быть до 80% от памяти, которую вы планировали выделить под MySQL вообще.
innodb_flush_log_at_trx_commit
Режим сохранения буферов InnoDB на диск. Необходимо изменить на 0 или 2. Значение 1, которое почему-то установлено по умолчанию, заставляет сохранять буферы при каждой транзакции. Получается сихронная работа с диском, что крайне медленно и убивает сам диск. Режим 0 говорит сохранять буферы каждую секунду, режим 2 вообще оставляет сохранение буферов на диск операционной системе. Режим 1 супернадёжен. Режимы 0 или 2 в случае краха системы могут привести к потере нескольких последних транзакций.
innodb_log_buffer_size
Значение по умолчанию необходимо увеличить только если у вас будет большое количество транзакций. Обычно 4Mb хватает.
innodb_log_file_size
Максимальный размер log-файла. В log-файлы происходит запись журнала работы InnoDB, куда сохраняются отчёты о выполненных транзакциях и т.д. Больший размер лога улучшает производительность, но замедляет восстановление данных. Начните с 64Mb
innodb_thread_concurrency
Количество конкуретных потоков с которым работает InnoDB. Можно смело начинать с количества ядер процессора и доводить до удвоения.
innodb_flush_method
Рекомендуется выставить в значение O_DIRECT, для избежания двойной буферизации.
innodb_file_per_table
Обычно, при использовании менеджера хранения InnoDB, все таблицы хранятся в одном большом файле. Это далеко не всегда хорошо, особенно когда таблиц много. Данный параметр заставляет InnoDB для каждой таблицы создавать свой файл.
transaction-isolation
Если ваши приложения не требуют иного, рекомендуется для улучшения производительности установить значение READ-COMMITED.
Среднее:
Ваш рейтинг: Нет
linuxdata.ru
Оптимальная настройка MySQL | IT Knowledge Base
Стандартные параметры в Mysql рассчитаны на микроскопические базы данных, работающие под малыми нагрузками на скромном железе.
Процесс оптимальной настройки Mysql состоит из двух частей — первоначальная настройка и корректировка параметров во время работы. Корректировка параметров в рабочем режиме во многом зависит от специфики Вашей системы и ее мониторинга. Разберемся с параметрами и рекомендациями по установке их значений.
Настройки нужно вносить в my.cnf.
innodb_buffer_pool_size
Если Вы используете только InnoDB таблицы, устанавливайте это значение максимально возможным для Вашей системы. Буфер InnoDB кеширует и данные и индексы. Поэтому значение этого ключа стоит устанавливать в 70%…80% всей доступной памяти.
innodb_buffer_pool_size = 24Ginnodb_log_file_size
Эта опция влияет на скорость записи. Она устанавливает размер лога операций (так операции сначала записываются в лог, а потом применяются к данным на диске). Чем больше этот лог, тем быстрее будут работать записи (т.к. их поместится больше в файл лога). Файлов всегда два, а их размер одинаковый. Значением параметра задается размер одного файла:
innodb_log_file_size = 512MСтоит понимать, что увеличение этого параметра увеличит и время восстановления системы при сбоях. Это происходит потому, что при запуске системы все данные из логов будет накатываться на данные. Однако с каждой новой версией, производительность этого процесса растет. Подумайте над использованием реплик для обеспечения доступности, чтобы не зависеть от времени восстановления базы данных.
innodb_log_buffer_size
Это размер буфера транзакций, которые не были еще закомментированы. Значение этого параметра стоит менять в случаях, если вы используете большие поля вроде BLOB или TEXT.
innodb_log_file_size = 2Minnodb_file_per_table
Если включить эту опцию, Innodb будет сохранять данные всех таблиц в отдельных файлах (вместо одного файла по умолчанию). Прироста в производительности не будет, однако есть ряд преимуществ:
- При удалении таблиц, диск будет освобождаться. По умолчанию общий файл данных может только расширяться, но не уменьшаться.
- Использование компрессионного формата таблиц потребует включить этот параметр.
innodb_flush_method
Этот параметр определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узов, вы будете выбирать между O_DSYNC и O_DIRECT:
innodb_flush_method = O_DSYNCinnodb_flush_log_at_trx_commit
Изменение этого параметра может повысить пропускную способность записи данных в базу в сотни раз. Он определяет, будет ли Mysql сбрасывать каждую операцию на диск (в файл лога).
Тут следует руководствоваться такой логикой:
- innodb_flush_log_at_trx_commit = 1 для случаев, когда сохранность данных — это приоритет номер один.
- innodb_flush_log_at_trx_commit = 2 для случаев, когда небольшая потеря данных не критична (например, вы используете дублирование и сможете восстановить небольшую потерю). В этом случае транзакции будут сбрасываться в лог на диск только раз в секунду.
Устанавливайте значение на свое усмотрение, однако в большинстве случаев подойдет второй вариант:
innodb_flush_log_at_trx_commit = 2query_cache_size
Значение этого параметра определяет сколько памяти стоит использовать под кеш запросов. Самый правильный подход — не полагаться на этот механизм. На практике он работает очень неэффективно. Так, весь кеш запросов для определенной таблицы сбрасывается всякий раз, когда в таблицу вносится хотя бы одно изменение. Это может привести к тому, что включение кеширования даже замедлит базу данных:
query_cache_size = 0max_connections
Не следует изменять значение этого параметра на старте. Однако, если вы получаете ошибки “Too many connections”, эту опцию стоит поднимать. Она определяет максимальное количество одновременных соединений с базой данных:
max_connections = 256disnetern.ru
MySQL - установка и базовая настройка
Пора заняться установкой MySQL-сервера, поскольку много чего будем хранить именно в этой базе данных.
Список необходимых опций сборки добавим в /etc/make.conf:
| # Путь к коллекции портовPORTSDIR?= /usr/ports# Версия MySQL сервераDEFAULT_MYSQL_VER=55 # Oпции для сборки клиента.if ${.CURDIR} == ${PORTSDIR}/databases/mysql55-client # Кодировка клиента по умолчанию.WITH_CHARSET=cp1251 # Коллэйшн или сравнение.WITH_COLLATION=cp1251_bin # В общем, если эта опция действительно хоть что-то# оптимизирует, то странно что она по дефолту не включена,# а предлагается опционально.BUILD_OPTIMIZED=yes .endif # Oпции для сборки сервера.if ${.CURDIR} == ${PORTSDIR}/databases/mysql55-server # Кодировка сервера по умолчанию.WITH_CHARSET=cp1251 # Какие кодировки компилить еще.WITH_XCHARSET=all # Кодировка коллэйшн.WITH_COLLATION=cp1251_bin # Вкомпилить ли SSL. Есть смысл, если к MySQL-серверу# разрешены коннекты откуда либо, кроме как с локалхоста.WITHOUT_OPENSSL=yes # Если следующую опцию поставить в yes, то MySQL будет работать# в несколько потоков (только для i386)WITH_LINUXTHREADS=yes # Тоже че-то связано с многопоточностью сервера.# Чего не знаем - нетрогаем.#WITH_PROC_SCOPE_PTH=yes # Как и с клиентом, типа "оптимизируемся".BUILD_OPTIMIZED=yes# Сборка статического варианта mysql демона. Я так понимаю, что# статический демон не станет подгружать дополнительные# библиотеки, потому что уже будет собран с ними же. Но где# тогда здесь выигрыш в производительности? Хоть в случае с# динамической версией - будут тратиться определенные ресурсы# на подгрузку библиотек; хоть в случае со статиком - он будет# эти библиотеки постоянно удерживать в памяти...# Эту опцию нельзя применять если у Вас WITH_OPENSSL=yesBUILD_STATIC=yes# Поддержка INNODB таблиц. Кому не надо, можете отключить.WITH_INNODB=yes # Следущая опция - это для тех, кто использует кластера MySQL.WITHOUT_NDB=yes .endif |
Приступаем непосредственно к инсталляции серверной части (клиентскую часть подтянет автоматически).
| # cd /usr/ports/databases/mysql55-server# make install clean# rehash |
Добавляем в /etc/rc.conf строку о необходимости запуска MySQL-сервера:
| # echo '# MySQL' >> /etc/rc.conf# echo 'mysql_enable="YES"' >> /etc/rc.conf |
Запускаем сервер
| # sh /usr/local/etc/rc.d/mysql-server start |
Меняем пароль для пользователя root в MySQL (хотя, обычно, завожу пользователя с полными привилегиями, а запись пользователя root удаляю полностью):
| # mysqladmin -u root password new_passwd_here |
Теперь следует отредактировать конфигурационный файл mysql, который называется my.cnf. Положить его можно в любую из этих папок: /var/db/mysql/, /etc/, /usr/local/etc/. MySQL при запуске проверит его наличие во всех этих каталогах. Если конфигурациооный файл отсутствует – можно скопировать доступный пример и при необходимости отредактировать его (доступны примеры для нагруженного сервера, для сервера со средней нагрузкой и для ненагруженного сервера)
| # cp /usr/local/share/mysql/my-medium.cnf /var/db/mysql/my.cnf |
Для решения проблем с кодировкой кирилицы, добавим в секцию [client]:
| default-character-set=cp1251 |
И, соответственно, в секцию [mysqld]:
| character-set-server = cp1251collation-server = cp1251_general_ciinit-connect="SET NAMES cp1251"skip-character-set-client-handshakeskip-name-resolve |
Также, для удобства, можете изменить параметры логгирования. Для этого в секцию [mysqld] файла /var/db/mysql/my.cnf добавляем строку log=/var/log/mysql.log
Также необходимо создать сам файл логов:
| # touch /var/log/mysql.log# chown mysql:mysql /var/log/mysql.log |
Перегружаем MySQL для того, чтобы новые настройки вступили в силу:
| # sh /usr/local/etc/rc.d/mysql-server restart |
Кстати... Если уж возьметесь писать логи MySQL - ОБЯЗАТЕЛЬНО настройте ротацию логов, а не то лог-файл очень скоро разрастется до неимоверных размеров (вплоть до того, что не останется свободного места на разделе. Например, будем архивировать лог раз в неделю. Для этого в /etc/newsyslog.conf необходимо добавить следующую строку:
| /var/log/mysql.log mysql:mysql 600 2 * $W6D0 JB /var/db/mysql/hostname.pid |
Обратите внимание: pid-файл будет уникальный (зависит от от имени сервера).
Дальше создадим пользователя, с правами суперпользователя в БД MySQL:
| GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'user_pass' WITH GRANT OPTION; |
Теперь еще осталось удалить остальных пользователей, которых mysql создает по-умолчанию.
| # mysql -u username -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.0.84-log FreeBSD port: mysql-server-5.0.84 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> USE mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> DELETE FROM user WHERE NOT user='username';Query OK, 4 rows affected (0.00 sec) mysql> quit |
Базовая настройка MySQL-сервера завершена.
www.muff.kiev.ua
Настройка MySQL + оптимизация InnoDB
Настройка MySQL + оптимизация InnoDB
Первоначальная настройка mysql-server
Мой любимый вопрос, задаваемый DBA, которые хотят увеличить производительность MySQL: “какие параметры надо настраивать в первую очередь, сразу после установки сервера?”Я удивлен количеством людей, которые не могут дать ответа на этот вопрос. И еще более удивлен количеством серверов, которые работают с настройками по умолчанию
Для настройки доступно довольно большое количество параметров, но лишь некоторые из них действительно влияют на производительность сервера. После установки этих параметров в правильные для вашего проекта значения, остальные настройки будут лишь незначительно влиять на поведение сервера.
key_buffer_size - очень важный параметр, если вы используете MyISAM-таблицы. Установите его равным 30-40% от имеющейся оперативной памяти, если вы используете только MyISAM. Актуальное для вашей системы значение зависит от индексов, размера данных и рабочего процесса. Помните, MyISAM использует кэш операционной системы для хранения данных и вам необходимо позаботиться о достаточных размерах выделяемой памяти. Во многих случаях, объем данных может быть значительно больше. Проверьте, не используется ли завышенное значение key_buffer. Нередко параметр установлен в значение 4GB при суммарном объеме .MYI-файлов в один гигабайт. Это напрасная трата ресурсов. Если вы используете несколько таблиц MyISAM - понизьте значение этого параметра. Но не опускайте его ниже 16-23 MB - этого будет достаточно для размещения индексов временных таблиц, которые создаются на диске.
innodb_buffer_pool_size - это очень важный параметр для настройки InnoDB. Таблицы этого типа гораздо более чувствительны к размеру буфера, нежели MyISAM. MyISAM может нормально работать даже при дефолтном значении buffer_size, в отличии от InnoDB, производительность которых будет заметно ниже при значении innodb_buffer_pool_size по умолчанию и больших объемах данных. Также пул буферов InnoDB самостоятельно кэширует индексы и данные, так что не нужно оставлять место для кэша ОС. Обычно предполагается выделение 70 - 80% памяти для серверов, на которых ничего не запущено, кроме InnoDB. Некоторые правила key_buffer применимы и в этом параметре: если у вас небольшие объемы данных и они не собираются стремительно увеличиваться, не завышайте значение innodb_buffer_pool_size, вы сможете найти свободной оперативной памяти лучшее применение. innodb_additional_mem_pool_size - этот параметр не имеет сильного влияния на производительность. По крайней мере в операционных системах с грамотным распределением памяти. Но вы можете установить значение этого параметра раным 20MB (иногда больше) и вы можете видетm сколько памяти выделяет InnoDB для различных нужд.innodb_log_file_size - очень важный параметр для систем с интенсивной записью, особенно больших объемов данных. Увелечение значения этого параметра обычно дает прирост производительности, но будьте осторожны. Обычно я использую значения 64M - 512 MB в зависимости от сервера.
innodb_log_buffer_size - значение по умолчанию вполне подойдет для большинства проектов со средней интенсивностью записи и короткими транзакциями. Однако если у вас бывают пики активности или работа с большим объемом данных, вы, вероятно, захотите увеличить значение этого параметра. Не делайте его слишком большим, это повлечет лишний расход памяти. Буфер сбрасывается каждую секунду и вам не нужен бОльший объем памяти. Обычно вполне хватает 8 - 16МB. Чем меньше система - тем меньше должно быть значение.
innodb_flush_logs_at_trx_commit - Вам кажется, что InnoDB в сто раз медленнее MyISAM? Вероятно, вы забыли изменить значение этого параметра. Значение по умолчанию 1 означает, что после каждой завершенной транзакции (или после изменения состояния транзакции) лог должен быть сброшен на диск. Это достаточно дорогая операция, особенно если у вас нет Battery backed up cache. Многие приложения, особенно те, в которых раньше использовался MyISAM будут хорошо работать при значении 2, который означает, что не надо сбрасывать буфер на диск, а следует отправить его в кэш операционной системы. Лог по-прежнему будет сбрасываться на диск каждую секунду и максимум, что вы можете потерять - это 1-2 секунды записей. Значение 0 обеспечивает более высокую скорость, но и более низкую надежность. Есть вероятность потерять транзакции даже при падении mysql-сервера. При значении равном 2 единственная возможность потерять данные - это фатальный сбой операционной системы.
table_cache - открытые таблицы могут разрастаться. Например, таблицы MyISAM помечают MYI-заголовок, как используемый. Вы, конечно же, не хотите, чтобы это происходило слишком часто и это, как правило, хорошее решение. Лучше увеличить размер кэша, чтобы он мог вместить большинство открытых таблиц. Кэш использует некоторое количество памяти и ресурсов ОС, но для современной техники это, как правило, не проблема. Значение 1024 будет оптимальным решением для нескольких сотен открытых таблиц (помните, каждое соединение нуждается в собственной копии). Если у вас много соединений или большое количество открытых таблиц - увеличьте это значение. Я видел системы со значением этого параметра > 100.000
thread_cache - Создание/уничтожение нитей (threads) может ухудшать производительность, особенно если они создаются/уничтожаются при каждом соединении/разъединении. Обычно я устанавилваю значение этого параметра равным 16. Если приложение делает большие прыжки в параллельных соединениях, то можно увидеть, как быстро растет переменная Threads_Created. Параметр предназначен для того, чтобы не создавать новых нитей в нормальных операциях.
query_cache_size - если ваше приложение читает много данных и у вас нет кэша на уровне приложения, этот параметр может неплохо помочь. Но не устанавливайте слишком большого значения - это может замедлить работу и содержание такого кэша может обойтись довольно дорого. Оптимальные значения - от 32MB до 512MB. Тем не менее, проверьте эффективность работы кэша через некоторое время. Вполне возможно, что текущее значение слишком велико.
Примечание: как вы заметили, все эти переменные являются глобальными и зависят от аппаратного обеспечения и устройств хранения данных. Сессионные переменные зависят от специфики конкретного проекта. Если у вас простые запросы, вам совершенно не нужно увеличивать параметр sort_buffer_size, даже если в вашем распоряжении 64GB оперативной памяти. Кроме того, это может снизить производительность. Обычно я оставляю настройку сессионных переменных на второй шаг, уже после оценки фронта работ.
P.S.: в дистрибутиве MySQL есть отличные примеры файла my.cnf для систем различных размеров. Они могут использоваться в качестве базы для ваших собственных файлов конфигурации, главное правильно выбрать шаблон.
Основы оптимизации производительности InnoDB
Проводя опрос среди посетителей раздела Job Opening я задавал им один простой вопрос: если бы у вас был сервер с 16GB RAM, который был бы предназначен для MySQL-сервера с очень большим объемом innodb-таблиц, работающий с стандартным веб-проектом, какие бы настройки вы скорректировали? И самое интересное, что большинство не смогло четко ответить. Поэтому и было принято решение опубликовать эту заметку, которая, возможно, расширит ваши знания об оптимизации программной и аппаратной частей сервера.
Я назвал эту статью Основы оптимизации производительности InnoDB, желая подчеркнуть, что это именно основы. Это универсальные советы, работающие на большинстве систем. Но более точную настройку необходимо производить, исходя из конкретных поставленных задач.
Hardware
Если у вас есть база данных innodb большого объема, и это действительно важные данные, то 16 - 32 GB оперативной памяти будут оптимальным решением. Процессоры - 2*DualCore подойдут для большой нагрузки, а два Quad Core помогут решить проблемы с дальнейшим масштабированием системы. Хотя имеется множество нюансов. Третий момент - это подсистема ввода/вывода. Напрямую подключенный DataStorage с большим количеством дисков и RAID с возможностью сохранения кэша будут отличным выбором. Обычно необходимо 6 - 8 жестких дисков в стандартный блок, но порой может понадобиться и больше. Также обратите внимание на новые 2.5" SAS диски. Они меньше, но часто работают гораздо быстрее, чем обычные HDD. RAID10 хорошо подходит как для хранения, так и для чтения данных, но в случае, если вы можете позволить некоторую избытычность. В противном случае можно сделать RAID5, но опасайтесь случайных записей.
Операционная система
Дя начала: установите 64-битную операционную систему. Часто можно увидеть 32-битный linux, или запущенный в режиме совместимости с 64-bit. Не делайте так. Если вы используете LVM для хранения базы данных, вы сможете более эффективно работать с резервными копиями. Файловая система Ext3 будет оптимальным выбором в большинстве случаев, но если вы запускаете particular roadblocks, то попробуйте XFS. Вы можете использовать опции noatime и nodiratime, если вы используете innodb_file_per_table и большое количество таблиц, но это, в принципе, не столь важно. Также убедитесь, что OS резервирует достаточно большое количество памяти для MySQL.
Опции MySQL InnoDB
Важнейшими опциями являются:innodb_buffer_pool_size - 70 - 80% оперативной памяти. Я ставлю это значение в 12G на системе с 16G RAMinnodb_log_file_size - зависит от необходимого вам объема данных для восстановления, но 256МБ будут разумным компромиссом между производительностью и рамером лог-файлаinnodb_log_buffer_size=4M - 4 мегабайта - нормальное значение, если вы не используете подачу больших блоков данных в InnoDB через каналы (pipes). Если используете, это значение лучше увеличить.innodb_flush_logs_at_trx_commit=2 - если вас не особо заботит ACID, и вы можете себе позволить потерять транзакции за последние секунду или две, в случае полного краха ОС, то установите это значение. Но это может повлечь печальные эффекты при коротких записях транзакций.innodb_thread_concurrency=8 - даже при имеющихся InnoDB Scalability Fixes будет совсем не лишним иметь ограниченное количество потоков. Значение может быть больше или меньше в зависомости от ваших потребностей, но 8 будет оптимальным значением для начала.innodb_flush_method=O_DIRECT - избегайте двойной буферизации и уменьшите активность swap, в большинстве случаев это увеличивает производительность. Но будьте осторожны, если у вас нет RAID с возможностью сохранения данных, операции ввода-вывода могут проходить некорректно и данные могут быть повреждены.innodb_file_per_table - если у вас немного таблиц, используйте эту опцию и рост занимаемого таблицами места не будет бесконтрольным. Эта опция добавлена в MySQL 4.1 и сейчас достаточно стабильна для использования.
Проверьте также, могут ли ваши приложения запускаться в режиме изоляции READ-COMMITED. Если это так, то установите опцию transaction-isolation=READ-COMITTED. Этот вариант увеличит производительность.
Есть еще немало опций, значения которых можно поменять для достижения лучшей производительности. Об этом можно прочитать в заметке Настройка опций mysql-server (перевод) или в одной из наших презентаций.
Настройка приложений для работы с InnoDB
При переходе с типа MyISAM, вам конечно будет интересно, что изменилось и какие новые возможности вам теперь доступны. Во-первых: убедитесь, что вы используете транзакции при обновлениях. Это необходимо для повышения производительности. Во-вторых: готово ли ваше приложение обрабатывать проблемы, которые могут произойти? И в-третьих: возможно вы захотите пересмотреть структуру своих таблиц и посмотреть как вы можете использовать свойства InooDB: распределение по первичному ключу, использование первичного ключа на всех индексах (это позволяет сократить первичеый ключ), быстрый просмотр по первичным ключам (попробуйте использовать это при запросах с JOIN) или большие несжаты индексы (облегчают индексирование).
При помощи этого краткого описания вы сможете провести первичную настройку InnoDB, которая повысит производительность на системах без battery backup, без изменения настроек ОС и не внося изменения в настройки приложений, до сих пор использующих MyISAM
l.wzm.me
