Хочу рассказать о настройках, которые применял, но не все из них помогли, просто опишу их.
Введение
Как я успел разобраться, при каждом соединении с MySQL создается mysqld (демон), который и обрабатывает все запросы соединения. Вот в блоке [mysqld] описывается именно настройки таких демонов.
Итак, давайте рассмотрим настройки демона [mysqld].
Выставить кодировку по умолчанию можно так:
character-set-server = utf8
collation-server = utf8_unicode_ci
Защитить сервер от кривых рук программиста, способного join`ом на 10 миллионов записей похоронить даже 4-х процессорный сервер, можно так:
max_join_size = 1000000
Буфер можно выставить 25% от общего объема оперативной памяти:
key_buffer_size = 2048M
как я понял, это буфер обмена для всех демонов, т.е. реально будет: key_buffer_size / кол-во демонов = ???M
Размер стека для каждого потока (демона):
thread_stack = 512K
стек - это место для хранения списка задач (открыть таблицу, выполнить запрос, закрыть и т.п.)
Кол-во потоков, которые сервер должен поместить в кэш для повторного использования:
thread_cache_size = 32
т.е. если к примеру есть часто повтояющийся SELECT * FROM myTable, то он попадет в кэш, чтобы не выполняться каждый раз.
Полезная настройка: если размер временной таблицы превышает размер, установленный этой переменной, она сбрасывается на диск. При наличии достаточного количества памяти на сервере, рекомендуется повысить значение данной переменной, по ускорения запросов с конструкцией GROUP BY
tmp_table_size = 512M
Установить максимальный размер таблиц типа MEMORY ( HEAP ) можно так:
max_heap_table_size = 256M
Размер буфера, выделяемого демону при выполнении операций сортировки. Для ускорения операций ORDER BY, GROUP BY рекомендуется увеличить данное значение
sort_buffer_size = 4M
Размер буфера выделяемого для сортировки MyISAM индексов с помощью оператора REPAIR TABLE или при создании индексов операторами CREATE TABLE, ALTER TABLE:
myisam_sort_buffer_size = 256M
Размер буфера, выделяемого демону для каждой сканируемой таблицы. При частом последовательном сканировании, рекомендуется увеличить значение данной переменной.
read_buffer_size = 4M
Размер буфера, выделяемого для чтения строк после сортировки, что-бы избежать повторного поиска на диске. Увеличение значения данной переменной может существенно увеличить эффективность конструкции ORDER BY. Имейте в виду, так как данный буфер выделяется для каждого демона, не следует устанавливать чересчур большое значение.
read_rnd_buffer_size = 8M
Размер буфера использующегося при операциях объединения таблиц ( если не используются индексы ). Буфер устанавливается один раз во время каждой операции объединения
join_buffer_size = 8M
Величина буфера, который используется для индексов, всех демонов. Если используется много DELETE или INSERT запросов к таблицам с большим кол - индексов, то увеличение значения повысит скорость выполнения таких запросов. Для достижения еще большей скорости нужно использовать LOCK TABLES. Советуют устанавливать не больше чем 1/3 озу и не больше объема всех б.д.
key_buffer = 2048M
Максимально количество соединений клиентов с сервером
max_connections = 35
Задает максимально количество неудачных попыток подключения с хоста. Значение по-умолчанию 10. При достижении данного значения, хост блокируется. Разблокировать хост можно с помощью: mysql> FLUSH HOSTS
max_connect_errors = 50
Максимальное число одновременных подключений для одной учетной записи MySQL. Значение по-умолчанию 0, отсутствие каких-либо ограничений
max_user_connections = 25
table_cache старое название для переменной table_open_cache, в нем указывается количество открытых таблиц для всех демонов. Увеличение значения приведет к увеличению количества используемых дескрипторов файла. Советуют рассчитывать по формуле: количество одновременных соединений * количество открытых таблиц в соединении. Т.е. для каждого соединения используется свои ячейки из кэша. Для проверки можно запустить mysqltuner.pl
table_cache = 128
Количество одновременно запускаемых демонов, советуют формулу: количество ядер процессора умножаем на 2
thread_concurrency = 16
Размер буфера для соединений, устанавливаемый сервером в промежутках между запросами
net_buffer_length = 1024
Максимальный объем одного SQL-запроса к серверу. Изначально буфер сообщений имеет размер net_buffer_length и при необходимости, автоматически увеличивается до значения данной переменной.
max_allowed_packet = 512M
Переменная задает количество байт при операциях сортировки значений BLOB или TEXT. Использованы только первые max_sort_length, остальные игнорируются
max_sort_length = 512
Полезная настройка: Изначальная величина буфера запросов. Если большинство запросов большого размера (например, при вставке записей с BLOB), его необходимо увеличить!
query_buffer_size
Полезная настройка: запросы, результат которых превышает значение данной переменной, не будут размещаться в кэше запросов.
query_cache_limit = 2M
Полезная настройка: объем памяти, выделенной для кэширования результатов запросов. По-умолчанию данный кэш отключен, значение - 0
query_cache_size = 16M
Полезная настройка: вид кэширования:
Значение | Объяснение | Примечание |
0 | OFF | Не кэширует и не извлекает результаты (по-умолчанию). |
1 | ON | Кэширует все результаты, кроме запросов SELECT SQL_NO_CACHE .... |
2 | DEMAND | Кэширует только запросы SELECT SQL_CACHE .... |
query_cache_type = 2
не забывайте про query_cache_startup_type.
Настройки innodb (извините, что без пояснений, просто оставлю их тут, чтобы не забыть):
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 64M
innodb_additional_mem_pool_size = 32M
innodb_file_io_threads = 8
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
Битрикс рекомендует:
transaction-isolation = READ-COMMITTED
default-character-set = utf8
Настройки блока для создания дампов
[mysqldump]
default-character-set = utf8
[mysql]
Старое название следующей настройки: character-set-server = utf8 выдает ошибку: /usr/bin/mysql_upgrade: unknown variable 'character-set-server=utf8
default-character-set = utf8
Как находить проблемные места
И не забываем про разгон: 1 - 2 - 3 - 4
Надеюсь, кому-нибудь помог разобраться, удачки в освоении MySQL.
Комментарии