7.5.2 Параметры запуска InnoDB

Чтобы использовать таблицы InnoDB в MySQL-Max-3.23, НЕОБХОДИМО задать параметры конфигурации в разделе [mysqld] файла конфигурации `my.cnf' или в файле параметров Windows `my.ini'.

В версии 3.23 как минимум необходимо указать innodb_data_file_path. В MySQL-4.0 не требуется задавать даже innodb_data_file_path: по умолчанию для него создается автоматически увеличивающийся файл размером в 16 Мб с именем `ibdata1' в каталоге `datadir' MySQL. (в MySQL-4.0.0 и 4.0.1 размер файла данных составляет 64 Мб и он не является автоматически увеличивающимся).

Однако для того, чтобы получить высокую производительность, НЕОБХОДИМО явно задать параметры InnoDB, перечисленные в следующих примерах.

Начиная с версий 3.23.50 и 4.0.2 для InnoDB имеется возможность задавать последний файл данных в innodb_data_file_path как автоматически увеличивающийся. В этом случае для innodb_data_file_path используется следующий синтаксис:

pathtodatafile:sizespecification;pathtodatafile:sizespecification;...
... ;pathtodatafile:sizespecification[:autoextend[:max:sizespecification]]

Если последний файл данных указан с параметром автоматического увеличения, то в случае нехватки места для табличной области InnoDB будет увеличивать последний файл данных; приращение файла каждый раз составляет 8 Мб. Например, синтаксис:

innodb_data_file_path = /ibdata/ibdata1:100M:autoextend

указывает InnoDB создать один файл данных с начальным размером 100 Мб, который будет увеличиваться на 8 Мб каждый раз, когда не будет хватать места. Если текущий диск окажется заполненным, можно, к примеру, добавить еще один файл данных на другой диск. При задании размера автоматически увеличивающегося файла `ibdata1' следует округлить его текущий размер до ближайшего числа, кратного 1024 * 1024 байтам (= 1 Мб), и явно указать округленный размер `ibdata1' в innodb_data_file_path. После этой записи можно добавить еще один файл данных:

innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

Следует соблюдать осторожность при работе в файловых системах, в которых установлено ограничение на размер файла в 2 Гб! Максимальный для данной операционной системы размер файла InnoDB не известен. В таком случае желательно указать максимальный размер файла данных:

innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M

Предположим, что у вас есть компьютер Windows NT с 128 Мб ОЗУ и одним жестким диском на 10 Гб. Ниже приведен пример возможных параметров конфигурации в `my.cnf' или `my.ini' для InnoDB:

[mysqld]
# Сюда  можно  добавить  другие  опции MySQL
# ...
#
innodb_data_home_dir = c:\ibdata
# Файлы  данных  должны  иметь  достаточно
# места  для  сохранения  ваших  данных  и
# индексов
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M
# Размер  буферного  пула  следует  задавать
# как 50 - 80% памяти  компьютера
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
innodb_log_group_home_dir = c:\iblogs
# .._log_arch_dir должен  быть  таким
# же, как _log_group_home_dir
innodb_log_arch_dir = c:\iblogs
innodb_log_archive=0
set-variable = innodb_log_files_in_group=3
# Размер  файла  журналов  должен  составлять
# около 15% от  размера  буферного  пула
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_buffer_size=8M
# Если  допустима  потеря  нескольких
# последних  транзакций, установите
# flush_log_в_trx_commit в 0
innodb_flush_log_at_trx_commit=1
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50

Обратите внимание на то, что InnoDB не создает каталогов: это нужно делать самостоятельно. Чтобы создать каталоги для данных и журналов, воспользуйтесь командой Unix или MS-DOS `mkdir'. Кроме того, следует удостовериться, что у сервера MySQL есть права на создание файлов в указанных каталогах.

Не забывайте, что в некоторых файловых системах существует ограничение в 2 Гб на размер файла данных! Общий размер файлов данных должен быть больше или равен 10 Мб, а общий размер файлов журналов - меньше 4 Гб.

Если не указать innodb_data_home_dir, то InnoDB по умолчанию создает свои файлы данных в каталоге `datadir' MySQL. После задания innodb_data_home_dir нельзя использовать абсолютные пути к файлам в innodb_data_file_path.

При первом создании базы данных InnoDB лучше всего запустить сервер MySQL из командной строки. Тогда на экран будет выводиться информация о создании базы данных и вы сможете увидеть, что происходит. Например, в Windows можно запустить `mysqld-max.exe' с параметрами:

your-path-to-mysqld>mysqld-max --standalone --console

Чтобы узнать, какой вид должна иметь выводящаяся информация, обратитесь к разделу Creating InnoDB Tablespace.

Где поместить файл `my.cnf' или `my.ini' в Windows? Для Windows существуют следующие правила:

• Должен быть создан только один файл `my.cnf' или `my.ini'.
• Файл `my.cnf' должен находиться в корневом каталоге диска `C:'.
• Файл `my.ini' должен находиться в каталоге `WINDIR', например в `C:\WINDOWS' или `C:\WINNT'. Чтобы вывести значение `WINDIR', можно воспользоваться командой SET операционной системы MS-DOS.
• Если на вашем компьютере применяется загрузчик операционной системы, в котором диск `C:' не является загрузочным, то необходимо использовать файл `my.ini'.

Где указываются параметры в Unix?

В Unix mysqld считывает параметры из следующих файлов, если они существуют, в таком порядке:

• `/etc/my.cnf' - общие параметры.
• `COMPILATION_DATADIR/my.cnf' -- параметры для сервера.
• `defaults-extra-file' -- файл, указанный при помощи --defaults-extra-file=....
• `~/.my.cnf' - параметры для пользователя.

COMPILATION_DATADIR представляет собой каталог данных MySQL, который был указан как параметр ./configure при компиляции mysqld (обычно `/usr/local/mysql/data' для установки исполняемых файлов или `/usr/local/var' для установки исходного кода).

Если точно не известно, откуда mysqld считывает свои файлы `my.cnf' или `my.ini', можно задать путь как первый параметр командной строки для сервера: mysqld --defaults-file=your_path_to_my_cnf.

Предположим, что у вас есть компьютер Linux с 512 Мб ОЗУ и тремя жесткими дисками по 20 Гб (с путями каталогов ``/'', ``/dr2'' and ``/dr3''). Ниже приведен пример возможных параметров конфигурации в `my.cnf' для InnoDB:

[mysqld]
# Здесь  вы  можете  задать  другие  опции  сервера MySQL
# ...
#
innodb_data_home_dir = /
# Файлы  данных  должны  иметь  достаточно
# места  для  сохранения  ваших  данных  и
# индексов
innodb_data_file_path = ibdata/ibdata1:2000M;dr2/ibdata/ibdata2:2000M
# Размер  буферного  пула  следует  задавать
# как 50 - 80% памяти  компьютера, но
# для Linux x86 следует  убедиться, что
# общий  расход  памяти  не  превышает 2 Гб
set-variable = innodb_buffer_pool_size=350M
set-variable = innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
# .._log_arch_dir должен  быть  такой
# же, как _log_group_home_dir
innodb_log_arch_dir = /dr3/iblogs
innodb_log_archive=0
set-variable = innodb_log_files_in_group=3
# Размер  файла  журналов  должен  составлять
# около 15% от  размера  буферного  пула
set-variable = innodb_log_file_size=50M
set-variable = innodb_log_buffer_size=8M
# Если  допустима  потеря  нескольких
# последних  транзакций, установите
# flush_log_в_trx_commit в 0
innodb_flush_log_at_trx_commit=1
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
#innodb_flush_method=fdatasync
#innodb_fast_shutdown=1
#set-variable = innodb_thread_concurrency=5

Обратите внимание на то, что два файла данных размещены на разных дисках. Параметр innodb_data_file_path предназначен для указания путей к файлам данных, а содержимое параметра innodb_data_home_dir добавляется непосредственно перед путями к файлам данных через косую или обратную косую черту. InnoDB заполняет табличное пространство, образованное файлами данных, снизу вверх. В некоторых случаях это позволяет увеличить производительность баз данных, если не все данные размещены на одном физическом диске. Размещение файлов журнала на другом диске очень часто позволяет повысить производительность. Для файлов данных можно также использовать разделы реального диска (реальные устройства): в некоторых версиях Unix это ускоряет операции ввода/вывода. Информацию по указанию параметров в `my.cnf' вы найдете в разделе руководства по управлению пространством файлов InnoDB.

Предупреждение: в Linux x86 необходимо соблюдать осторожность, чтобы не установить слишком высокое значение использования памяти. glibc может дать процессу разрастись и превысить стеки потоков, что приведет к сбою сервера. Степень риска значительно повышается, если значение

innodb_buffer_pool_size + key_buffer +
max_connections * (sort_buffer + record_buffer) + max_connections * 2 MB

приближается к значению 2 Гб или превышает его. Каждый поток будет использовать стек (обычно 2 Мб, но в бинарной поставке MySQL AB только 256 Кб) и в худшем случае также дополнительную память sort_buffer + record_buffer.

Как настроить другие параметры сервера mysqld? Для большинства пользователей подходят следующие стандартные значения:

set-variable = max_connections=200
set-variable = record_buffer=1M
set-variable = sort_buffer=1M
# Размер key_buffer следует  задавать  как
# 5%-50% от  вашего  ОЗУ, в  зависимости
# от  того, как  часто  у  вас  используются
# таблицы MyISAM, но key_buffer +
# размер  буферного  пула InnoDB не  должен
# превышать 80% вашего  ОЗУ
set-variable = key_buffer=...

Обратите внимание на то, что некоторые параметры представлены при помощи формата числового параметра `my.cnf': set-variable = innodb... = 123, а остальные (строчные и логические параметры) - при помощи другого формата: innodb_... = ....

Параметры настройки имеют следующие значения:

Параметр	Описание
innodb_data_home_dir	Общая часть пути к каталогу всех файлов данных InnoDB. По умолчанию для этого параметра принято значение datadir MySQL.
innodb_data_file_path	Пути к отдельным файлам данных и их размеры. Полный путь к каталогу каждого файла данных получается путем объединения innodb_data_home_dir с путем, который задается данным параметром. Размеры файлов указаны в мегабайтах. InnoDB также ``понимает'' сокращение 'G', 1 G означает 1024 М. Начиная с версии 3.23.44 в операционных системах, где поддерживаются большие файлы, можно задавать размер файла, превышающий 4 Гб. В некоторых операционных системах файлы должны быть < 2 Гб. Общий объем файлов должен быть как минимум 10 Мб.
innodb_mirrored_log_groups	Количество идентичных копий групп журналов, которые хранятся для базы данных. На данный момент этому параметру должно быть присвоено значение 1.
innodb_log_group_home_dir	Путь к каталогу файлов журналов InnoDB.
innodb_log_files_in_group	Количество файлов журналов в группе журналов. InnoDB производит запись в файлы по круговому способу. Для этого параметра рекомендуется установить значение "3".
innodb_log_file_size	Размер каждого файла журнала в группе журналов (указывается в мегабайтах). Разумный диапазон значений составляет от 1М до 1/N от размера буферного пула, приведенного ниже, где N - количество файлов журналов в группе. Чем больше это значение, тем меньше требуется сбросов на диск информации из буферного пула, что сокращает количество дисковых операций ввода/вывода. Однако в случае сбоя восстановление при больших размерах файлов журналов займет больше времени. Общий размер файлов журналов на 32-разрядных компьютерах должен быть < 4 Гб.
innodb_log_buffer_size	Размер буфера, который в InnoDB используется для записи информации файлов журналов на диск. Разумный диапазон значений составляет от 1М до половины общего размера файлов журналов. Большой буфер журналов позволяет осуществлять объемные транзакции без записи журнала на диск до завершения транзакции. Поэтому если ваши транзакции отличаются значительными объемами, увеличение буфера журналов сократит количество операций ввода/вывода диска.
innodb_flush_log_at_trx_commit	Обычно этому параметру присваивается значение 1; при этом значении после завершения транзакции информация журнала записывается на диск и фиксируются изменения, внесенные транзакцией, благодаря чему данные сохраняются в случае сбоя базы данных. Если у вас выполняется большое количество маленьких транзакций и вы готовы пожертвовать такой возможностью, можно установить значение этого параметра в 0, чтобы снизить количество обращений к диску.
innodb_log_arch_dir	Каталог, в котором будут храниться заполненные файлы журналов, если включено архивирование журналов. Значение этого параметра на настоящий момент должно задаваться таким же, как и для innodb_log_group_home_dir.
innodb_log_archive	На данный момент значение этого параметра должно устанавливаться в 0. Поскольку восстановление из резервной копии MySQL осуществляет при помощи своих собственных файлов журналов, архивировать файлы журналов InnoDB нет необходимости.
innodb_buffer_pool_size	Размер буфера памяти, который InnoDB использует для кэширования данных и индексов своих таблиц. Чем больше это значение, тем меньше обращений к диску осуществляется при получении доступа к данным таблиц. На специально выделенном сервере баз данных этот параметр можно установить в значение до 80% физической памяти компьютера. Однако для этого параметра не следует задавать слишком большое значение, так как при недостатке физической памяти операционная система будет вынуждена сбрасывать часть информации на диск.
innodb_additional_mem_pool_size	Размер пула памяти, который InnoDB использует для хранения информации словаря данных и других внутренних структур данных. Разумным значением для этого параметра может быть 2М, но чем больше таблиц в вашем приложении, тем больше информации нужно будет разместить в этом пуле. Если памяти в этом пуле будет недостаточно для InnoDB, то будет выделятся память операционной системы, а в файл журнала MySQL будут записываться предупреждающие сообщения.
innodb_file_io_threads	Количество потоков ввода/вывода файлов в InnoDB. Обычно этому параметру присваивается значение 4, но в Windows при помощи увеличения данного значения можно сократить количество обращений к диску.
innodb_lock_wait_timeout	Время простоя (в секундах), на протяжении которого транзакция InnoDB может ожидать блокировки прежде, чем будет произведен откат. InnoDB автоматически обнаруживает зависшие транзакции в своей таблице блокировок и производит откат транзакций. Если в той же самой транзакции используется команда LOCK TABLES, или другие обработчики таблиц с безопасными транзакциями, отличными от InnoDB, то может возникнуть зависание, которое не будет обнаружено InnoDB. В таких ситуациях параметр времени простоя помогает устранить проблему.
innodb_flush_method (Доступен, начиная с версий 3.23.40 и выше). По умолчанию для этого параметра принято значение fdatasync. Другой возможный вариант - O_DSYNC.