Общие параметры конфигурационного файла my.cnf
max_connect_errors - максимальное кол-во ошибок, при достижении которого хост с которого они шли будет заблокирован и не сможет подключиться к серверу. Сбросить накопленное кол-во ошибок для всех хостов можно с помощью команды flush hosts.
binlog_cache_size - размер кэша, используемого для хранения SQL-операторов бинарного журнала регистраций во время транзакций. Этот кэш выделяется для каждого клиента, если сервер использует любой из транзакционных движков, например InnoBD и на сервере включена поддержка бинарного журнала регистраций.
Если отсортированные данные не помещаются в текущий буфер сортировки, то данные начинают использовать жесткий диск. Если вы видите большое значение sort_merge_passes в выводите show global status, то надо задуматься об увеличении sort_buffer_size.
Максимальный объем буфера сортировки 4Гб.
join_buffer_size - размер буфера использующегося при операциях объединения таблиц (при этом объединение происходит без использования индексов). Данные объединения не очень хорошо сказываются на производительности сервера и увеличив join_buffer_size мы можем снизить влияние таких объединений на производительность.
Максимальное значение 4Гб.
thread_cache_size - регулирует кол-во потоков, которые сервер должен поместить в кэш для повторного использования. После отключения клиента, потоки не удаляются а помещаются в кэш, как только MySQL серверу понадобятся новые потоки, он берет их из кэша. Это значение может быть увеличено для повышения производительности, если у вас много новых подключений.
thread_concurrency - кол-во одновременно запускаемых потоков, для Linux систем это кол-во CPU * кол-во ядер в каждом CPU.
ft_min_word_len - минимальная длина слова для включения в индекс fulltext. При изменении данного значения, необходимо заново перестроить ваши fulltext индексы, это можно сделать с помощью команды repair table tbl_name quick или с помощью утилиты mysqlrepair, так mysqlrepair -u root -p --all-databases.
tmp_table_size - максимальный размер временных таблиц, расположенных в оперативной памяти. Если таблица не помещается в рамки указанного значения tmp_table_size, то для работы с ней начинает использоваться диск. Данное ограничение применяется для одной таблицы.
Параметры относящиеся к MyISAM
read_buffer_size - размер буфера, выделяемого потоком для каждой сканируемой таблицы. Используется во время полного сканирования таблиц. Максимальное значение 2Гб.
read_rnd_buffer_size - размер буфера, выделяемого для чтения строк после сортировки, что-бы избежать повторного поиска на диске. Увеличив размер данного буфера, вы значительно улучшите производительность order by. Данный буфер выделяется для каждого клиента.
bulk_insert_buffer_size - MyISAM использует специальный древовидный кэш, чтобы быстрее выполнять массивные insert'ы (такие как INSERT ... SELECT, INSERT ... VALUES (...), (...), ... и LOAD DATA INFILE). Эта переменная ограничивает размер древовидного кэша на каждый поток. При выставлении 0 значения, полностью его отключает. Для оптимальное производительности не устанавливайте его значение больше, чем значение key_buffer_size.
Этот буфер выделяется в момент обнаружения массивной вставки.
myisam_sort_buffer_size - выделяется на поток, используется данный буфер при сортировке MyISAM индексов во время восстановлении индексов, ремонте, оптимизации таблиц или изменении их структуры с помощью оператора alter table, а так же при выполнении load data infile в пустую таблицу.
myisam_max_sort_file_size - размер временного файла, использующегося при восстановлении индексов MyISAM, при использовании операторов repair table, alter table или load data infile. Если размер необходимого файла больше значения данной переменной, то для создания индекса будет использоваться кэш ключей, что значительно медленней.
myisam_repair_threads - указывает кол-во потоков при ремонте таблицы. Увеличение этого значения имеет смысл, если у вас несколько процессоров и большое кол-во оперативной памяти.
Параметры относящиеся к InnoDB
innodb_additional_mem_pool_size - дополнительный пул памяти, который используется InnoDB для хранения метаданных, чем больше у вас таблиц, тем большее значение innodb_additional_mem_pool_size надо выставлять. Если InnoDB потребуется больше памяти, чем указано в данном параметре, то она начнет выделять память из ОС, это происходит достаточно быстро, поэтому менять данный параметр в большинстве случаев не надо.
innodb_buffer_pool_size - InnoDB в отличие от MyISAM использует буферный пул для кэширования индексов и строковых данных. При достаточном объеме памяти увеличение буфера InnoDB может снизить загруженность диска при доступе к табличным и индексным данным. На отдельно стоящем выделенном Mysql сервере можно установить этот параметр в 80% от общего объема физической памяти сервера.
innodb_file_io_threads - кол-во файловых потоков ввода/вывода для использования асинхронных операций. Для *nix систем жестко задано значение в 4, увеличение не даст эффекта.
innodb_log_group_home_dir - движок InnoDB ведет свои собственные журналы, которые создаются во время запуска сервера, если их еще не существует. По умолчанию эти журналы создаются в каталоге данных, их имена начинаются с ib_, пример файлов
-rw-rw---- 1 mysql mysql 5.0M Dec 23 02:43 /var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Oct 31 13:23 /var/lib/mysql/ib_logfile1
innodb_data_file_path - InnoDB по умолчанию хранит данные в одном или нескольких файлах с данными формирующими табличное пространство. Если у вас есть один логический диск для хранения данных, то размещение одного автоматически расширяющегося файла на нем будет достаточно.
При задании объема файла можно указывать суффикс в конце, например K (килобайты), M (мегабайты), G (гигабайты). Если вообще не указывать параметр innodb_data_file_path в конфиг.файле my.cnf, то по умолчанию создастся автоматически расширяющийся 10Мб файл данных с именем ibdata1
-rw-rw---- 1 mysql mysql 10M Dec 23 06:20 /var/lib/mysql/ibdata1
это тоже самое что и innodb_data_file_path = ibdata1:10M:autoextend
Синтаксис описания файла:
path:size
path:size:autoextended
path:size:autoextended:max:maxsize
autoextended - придает файлу свойство автоматического расширения, т.е. когда файл заполняется, InnoDB движок автоматически увеличивает его размер. По умолчанию при авторасширении файлы увеличиваются на 8Мб. Для того чтобы задать другое приращение, используется параметр innodb_autoextend_increment
maxsize - максимальный размер до которого может расширяться файл
Вышеуказанные атрибуты autoextended и maxsize могут быть использованы только для последнего файла данных в параметре innodb_data_file_path
Примеры:
innodb_data_file_path = ibdata1:50M;ibdata2:50M:autoextend - будет создан файл ibdata1 фиксированного размера в 50Мб и автоматически расширяющийся файл ibdata2 размером 50Мб
innodb_data_file_path=ibdata1:10M:autoextend:max:500M - будет создан единственный автоматически расширяющийся файл данных ibdata1 размером 10Мб, который сможет разрастись максимум до 500Мб
Ниже указан пример более интересного варианта, в каталогах /dr2 и /dr3 примонтированы два дополнительных жестких диска на считая основного. Таким образом мы разнесли наши файлы данных и файл-журналов на разные физические диски, что может дать ощутимый прирост производительности Mysql сервера. Кроме того это придает некую гибкость, например можно вынести файл-журналов на быстрый SSD-диск или поэкспериментировать со сменой файловой системы на дополнительных дисках.
Значение innodb_data_home_dir мы задали пустым для того чтобы иметь возможность использовать абсолютные пути в параметрах innodb_data_file_path и innodb_log_group_home_dir.
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
innodb_log_group_home_dir = /dr3/iblogs
и помним что InnoDB заполняет файлы данных поочередно, начиная с первого файла данных
innodb_data_home_dir - общая часть пути к каталогу всех файлов данных InnoDB. Если он не указан, то будет использоваться путь по умолчанию, а если указать пустую строку в качестве его значения, то в innodb_data_file_path можно будет использовать абсолютные пути к файлам.
max_connect_errors - максимальное кол-во ошибок, при достижении которого хост с которого они шли будет заблокирован и не сможет подключиться к серверу. Сбросить накопленное кол-во ошибок для всех хостов можно с помощью команды flush hosts.
binlog_cache_size - размер кэша, используемого для хранения SQL-операторов бинарного журнала регистраций во время транзакций. Этот кэш выделяется для каждого клиента, если сервер использует любой из транзакционных движков, например InnoBD и на сервере включена поддержка бинарного журнала регистраций.
При частом использовании объемных транзакций со множеством операторов, значение данной переменной рекомендуется увеличить. Связано это с тем что большая транзакция может выйти за пределы буфера и серверу придется сохранять часть данных на жесткий диск в временном файле, что снижает производительность. По умолчанию данный кэш равен 32768.
sort_buffer_size - размер буфера, выделяемого на поток при выполнении операций сортировки. Для ускорения операций ORDER BY, GROUP BY рекомендуется увеличить данное значение.Если отсортированные данные не помещаются в текущий буфер сортировки, то данные начинают использовать жесткий диск. Если вы видите большое значение sort_merge_passes в выводите show global status, то надо задуматься об увеличении sort_buffer_size.
Максимальный объем буфера сортировки 4Гб.
join_buffer_size - размер буфера использующегося при операциях объединения таблиц (при этом объединение происходит без использования индексов). Данные объединения не очень хорошо сказываются на производительности сервера и увеличив join_buffer_size мы можем снизить влияние таких объединений на производительность.
Максимальное значение 4Гб.
thread_cache_size - регулирует кол-во потоков, которые сервер должен поместить в кэш для повторного использования. После отключения клиента, потоки не удаляются а помещаются в кэш, как только MySQL серверу понадобятся новые потоки, он берет их из кэша. Это значение может быть увеличено для повышения производительности, если у вас много новых подключений.
thread_concurrency - кол-во одновременно запускаемых потоков, для Linux систем это кол-во CPU * кол-во ядер в каждом CPU.
ft_min_word_len - минимальная длина слова для включения в индекс fulltext. При изменении данного значения, необходимо заново перестроить ваши fulltext индексы, это можно сделать с помощью команды repair table tbl_name quick или с помощью утилиты mysqlrepair, так mysqlrepair -u root -p --all-databases.
tmp_table_size - максимальный размер временных таблиц, расположенных в оперативной памяти. Если таблица не помещается в рамки указанного значения tmp_table_size, то для работы с ней начинает использоваться диск. Данное ограничение применяется для одной таблицы.
Параметры относящиеся к MyISAM
read_buffer_size - размер буфера, выделяемого потоком для каждой сканируемой таблицы. Используется во время полного сканирования таблиц. Максимальное значение 2Гб.
read_rnd_buffer_size - размер буфера, выделяемого для чтения строк после сортировки, что-бы избежать повторного поиска на диске. Увеличив размер данного буфера, вы значительно улучшите производительность order by. Данный буфер выделяется для каждого клиента.
bulk_insert_buffer_size - MyISAM использует специальный древовидный кэш, чтобы быстрее выполнять массивные insert'ы (такие как INSERT ... SELECT, INSERT ... VALUES (...), (...), ... и LOAD DATA INFILE). Эта переменная ограничивает размер древовидного кэша на каждый поток. При выставлении 0 значения, полностью его отключает. Для оптимальное производительности не устанавливайте его значение больше, чем значение key_buffer_size.
Этот буфер выделяется в момент обнаружения массивной вставки.
myisam_sort_buffer_size - выделяется на поток, используется данный буфер при сортировке MyISAM индексов во время восстановлении индексов, ремонте, оптимизации таблиц или изменении их структуры с помощью оператора alter table, а так же при выполнении load data infile в пустую таблицу.
myisam_max_sort_file_size - размер временного файла, использующегося при восстановлении индексов MyISAM, при использовании операторов repair table, alter table или load data infile. Если размер необходимого файла больше значения данной переменной, то для создания индекса будет использоваться кэш ключей, что значительно медленней.
myisam_repair_threads - указывает кол-во потоков при ремонте таблицы. Увеличение этого значения имеет смысл, если у вас несколько процессоров и большое кол-во оперативной памяти.
Параметры относящиеся к InnoDB
innodb_additional_mem_pool_size - дополнительный пул памяти, который используется InnoDB для хранения метаданных, чем больше у вас таблиц, тем большее значение innodb_additional_mem_pool_size надо выставлять. Если InnoDB потребуется больше памяти, чем указано в данном параметре, то она начнет выделять память из ОС, это происходит достаточно быстро, поэтому менять данный параметр в большинстве случаев не надо.
innodb_buffer_pool_size - InnoDB в отличие от MyISAM использует буферный пул для кэширования индексов и строковых данных. При достаточном объеме памяти увеличение буфера InnoDB может снизить загруженность диска при доступе к табличным и индексным данным. На отдельно стоящем выделенном Mysql сервере можно установить этот параметр в 80% от общего объема физической памяти сервера.
innodb_file_io_threads - кол-во файловых потоков ввода/вывода для использования асинхронных операций. Для *nix систем жестко задано значение в 4, увеличение не даст эффекта.
innodb_thread_concurrency - максимальное кол-во одновременно действующих потоков для InnoDB. Как только кол-во потоков достигает значения указанного в данном параметре, все дополнительные потоки помещаются в FIFO очередь.
Правильное значение для этого параметра сильно зависит от аппаратной конфигурации сервера и рабочей нагрузки. Рекомендуемое значение это значение в два раза большее кол-ва CPU ядер, плюс кол-во жестких дисков. Начиная с версии 5.1.12 можно вообще отключить данный параметр выставив его значение в 0, это разрешит InnoDB использовать то кол-во потоков, которое ей потребуется.
Правильное значение для этого параметра сильно зависит от аппаратной конфигурации сервера и рабочей нагрузки. Рекомендуемое значение это значение в два раза большее кол-ва CPU ядер, плюс кол-во жестких дисков. Начиная с версии 5.1.12 можно вообще отключить данный параметр выставив его значение в 0, это разрешит InnoDB использовать то кол-во потоков, которое ей потребуется.
innodb_flush_log_at_trx_commit - крайне желательно выставлять значение в 1. Возможные значения в 0 и 2 могут снизить нагрузку на дисковую подсистему сервера, но этого делать не рекомендуется, так как перестанет соблюдаться ACID.
innodb_log_buffer_size - размер буфера для хранения информации о каждой транзакции. После выполнения транзакции данные о ней сбрасываются на диск. Для больших транзакций, выходящих за пределы этого буфера есть смысл увеличить значение данного параметра, чтобы во время выполнения транзакции данные не сбрасывались на диск по несколько раз подряд, из-за того что они не помещаются в буфер.
Возможное значение от 1 до 8Мб.
Возможное значение от 1 до 8Мб.
innodb_log_group_home_dir - движок InnoDB ведет свои собственные журналы, которые создаются во время запуска сервера, если их еще не существует. По умолчанию эти журналы создаются в каталоге данных, их имена начинаются с ib_, пример файлов
-rw-rw---- 1 mysql mysql 5.0M Dec 23 02:43 /var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Oct 31 13:23 /var/lib/mysql/ib_logfile1
и данный параметр используется для установки пути к каталогу в котором InnoDB будет записывать свои регистрационные журналы.
innodb_log_file_size - размер каждого файла-журнала в группе журналов. Когда регистрационный журнал переполняется, InnoDB сохраняет буфер на диске. Увеличение размера файла регистрационного журнала уменьшает периодичность переполнения журнала и соответственно периодичность с которой происходит обновление, тем самым снижая нагрузку на дисковую подсистему сервера. При этом, чем больше размер регистрационного журнала, тем больше и время восстановления после сбоя.
Общий размер должен быть не больше 4Гб, по умолчанию 5Мб. Общий размер считается как произведение значений innodb_log_file_size и innodb_log_files_in_group.
Общий размер должен быть не больше 4Гб, по умолчанию 5Мб. Общий размер считается как произведение значений innodb_log_file_size и innodb_log_files_in_group.
innodb_log_files_in_group - общее кол-во файлов журналов в группе журналов. Значение в 2-3 будет хорошим.
innodb_max_dirty_pages_pct - максимальный процент так называемых грязных страниц в буферном пуле InnoDB. Если оно будет достигнуто, то сервер попытается их сбросить, чтобы не закончились все чистые страницы. Диапазон значения от 0 до 100, по умолчанию 90.
innodb_lock_wait_timeout - время в секундах на протяжении которого транзакция InnoDB может ожидать блокировку до того, как будет произведен ее откат. InnoDB автоматически обнаруживает взаимные блокировки транзакций (deadlocks) в своей таблице блокировок и выполняет их откат. Если вы используете команду lock tables или работаете с другими движками с транзакциями, отличными от InnoDB, то в случае взаимной блокировки InnoDB не сможет ее обнаружить. По умолчанию данный параметр равен 50.
innodb_data_file_path - InnoDB по умолчанию хранит данные в одном или нескольких файлах с данными формирующими табличное пространство. Если у вас есть один логический диск для хранения данных, то размещение одного автоматически расширяющегося файла на нем будет достаточно.
При задании объема файла можно указывать суффикс в конце, например K (килобайты), M (мегабайты), G (гигабайты). Если вообще не указывать параметр innodb_data_file_path в конфиг.файле my.cnf, то по умолчанию создастся автоматически расширяющийся 10Мб файл данных с именем ibdata1
-rw-rw---- 1 mysql mysql 10M Dec 23 06:20 /var/lib/mysql/ibdata1
это тоже самое что и innodb_data_file_path = ibdata1:10M:autoextend
Синтаксис описания файла:
path:size
path:size:autoextended
path:size:autoextended:max:maxsize
autoextended - придает файлу свойство автоматического расширения, т.е. когда файл заполняется, InnoDB движок автоматически увеличивает его размер. По умолчанию при авторасширении файлы увеличиваются на 8Мб. Для того чтобы задать другое приращение, используется параметр innodb_autoextend_increment
maxsize - максимальный размер до которого может расширяться файл
Вышеуказанные атрибуты autoextended и maxsize могут быть использованы только для последнего файла данных в параметре innodb_data_file_path
Примеры:
innodb_data_file_path = ibdata1:50M;ibdata2:50M:autoextend - будет создан файл ibdata1 фиксированного размера в 50Мб и автоматически расширяющийся файл ibdata2 размером 50Мб
innodb_data_file_path=ibdata1:10M:autoextend:max:500M - будет создан единственный автоматически расширяющийся файл данных ibdata1 размером 10Мб, который сможет разрастись максимум до 500Мб
Ниже указан пример более интересного варианта, в каталогах /dr2 и /dr3 примонтированы два дополнительных жестких диска на считая основного. Таким образом мы разнесли наши файлы данных и файл-журналов на разные физические диски, что может дать ощутимый прирост производительности Mysql сервера. Кроме того это придает некую гибкость, например можно вынести файл-журналов на быстрый SSD-диск или поэкспериментировать со сменой файловой системы на дополнительных дисках.
Значение innodb_data_home_dir мы задали пустым для того чтобы иметь возможность использовать абсолютные пути в параметрах innodb_data_file_path и innodb_log_group_home_dir.
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
innodb_log_group_home_dir = /dr3/iblogs
и помним что InnoDB заполняет файлы данных поочередно, начиная с первого файла данных
innodb_data_home_dir - общая часть пути к каталогу всех файлов данных InnoDB. Если он не указан, то будет использоваться путь по умолчанию, а если указать пустую строку в качестве его значения, то в innodb_data_file_path можно будет использовать абсолютные пути к файлам.
