[[sysadmin:mysql_tunning]]

Tuning Mysql [чистовик]

Собственно описание настроек и рекомендации, т.к. нет толкового описания по оптимизации mysql и если есть какие-то предложения и замечания, можно обсудить здесь[link-forum], в wiki будут потом сделаны корректировки.

[client]
port = 3306
socket=/var/run/mysqld/mysqld.sock

Разрешить выполнять запросы из файлов: LOAD DATA INFILE «/tmp/test.sql» INTO TABLE test;

[mysql]
loose-local-infile=1

Порт, сокет, рабочая папка… Если не пишет в лог проверьте путь, mysql не умеет создавать папки.

[mysqld_safe]
err-log = /var/log/mysql/mysql.err

[mysqld]

#start options
user = mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysql.err
basedir = /usr
datadir = /var/lib/mysql
language = /usr/share/mysql/russian
skip-external-locking
symbolic-links=0

Если медленно отрабатывает DNS имеет смысл указать

skip-name-resolve

Имеет ли смысл понижать приоритет на обновление в преимущество к выборкам? У меня статистика допустим такая, аптайм правда сутки select 8,206 k 265.423 k 80.88% update 1,514 k 48.955 k 14.92%

low-priority-updates

query_cache

query_cache_size = 256M
query_cache_limit = 256M

убеждаемся query_cache_type=ON Кэширование запросов, т.е. хэш-массив, это что-то типа двумерного массива где ключ это запрос, а значение - данные. Как работает: при старте mysql выделяет блок памяти равный query_cache_size, затем поступает запрос и если он без директивы SQL_NO_CACHE ну и понятно если таблица не изменялась, то при поступлении первых строк начинает выделятся память размером query_cache_min_res_unit(По умолчанию 4КБ) и так далее до тех пор, пока размер выборки не превышает query_cache_limit.

Параметр query_cache_min_res_unit может забить память, т.е. я так понял фрагментирует её этими кусочками по 4Кб, Qcache_free_blocks будет сильно увеличиваться, query_cache_min_res_unit нужно увеличивать. Либо рассчитать по формуле:

query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache. 

У меня получилось 2Кб, можно значит его уменьшать. Можно также уменьшить query_cache_size, думаю 256Мб всё таки многовато будет, т.к. больше незадействованного кэша, плюс фрагментация, выборка из кэша производится сложнее и приводит к доп. нагрузке. Вообщем Qcache_free_blocks должен стремится к единице.

Также можно рассчитать эффективность работы кэша по формуле:

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)  

У меня получилось 0,4 - т.е. 40% попадает в кэш.

Если срабатывает счетчик Qcache_lowmem_prunes то query_cache_size нужно тоже увеличивать. Вообще, говорят в 6 или 7 версии кэширование хотят отменить.

Переменные можно просматривать или через phpMyAdmin во вкладке «Состояние» или через консоль:

SHOW GLOBAL STATUS LIKE 'Qcache%'
SHOW VARIABLES LIKE 'query_cache_%';

Буферы для объединенных выборок(SELECT JOIN….), вообще каждый буфер цепляется к созданному потоку, так сказать резервируется для него память, это тоже нужно учитывать.

join_buffer_size = 256M
max_join_size=256M

блокировать хост при 1000 неудачных попыток работы-соединения

max_connect_errors = 1K

Увеличивать если будет ошибка «Packet too large», т.е. при передаче данных на сервер, допустим заливаете дамп. По моему это актуально при приёме-передаче данных типа BLOB, т.к. если допустим видео перевести в двоичный формат размер пакета потребуется увеличить.

max_allowed_packet=16M

Держать открытыми таблицы для потоков, чтобы не тратить ресурс каждый раз на открытие. Если растёт значение Open_tables, то можно увеличивать, параметр равен кол-ву таблиц. В нашем случае с запасом на 500 таблиц. По сути это два одинаковых параметра - table_open_cache это после версии 5.1

table_open_cache = 1k
table_cache = 1k

Максимальный размер памяти для временных таблиц Mysql, у нас в конфиге 1Гб. Но всё равно Created_tmp_disk_tables немного растёт, чем меньше это значение тем лучше. Но может это вызвано тем, что согласно документации временные таблицы не могут быть созданы в памяти когда в запросе есть TEXT или BLOB

max_heap_table_size = 1024M
tmp_table_size = 1024M

thread_cache_size cледует увеличивать если растет thread_created и он значительно больше чем thread_cache, т.е. клиент отключился, но кеш остался, затем клиент подключился, поток заново не создаётся а берётся из кэша, ресурс на подключение не тратится, значения 32 для нашего случая вполне хватит, если ставить 64 или предельное 100, то это нужно для нагруженных веб-серверов.

thread_cache_size = 32

Увеличивать если будет ошибка «Too many connections», думаю это только для веб-серверов актуально

max_connections = 64

Myisam За ненадобностью не нужен, но если всё таки осталось часть таблиц, дайте ему немного В нашем случае много давать не нужно, ведь каждый трейд зарезервирует для них место.

key_buffer_size = 8M
myisam_sort_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M

Ниже включается бинарный лог, раскомментируйте следующие 2 строки чтобы включить Нам не нужно, т.к. мы делаем резервную копию. #server-id=1 #log-bin=mysqld-bin

Здесь можно перенести размещение временных таблиц в tmpfs, но мы используем InnoDB, нам не нужно. tmpdir = /tmp/

InnoDB

Каждую таблицу хранить в отдельном файле

innodb_file_per_table

Не использовать системный буфер, только свой.

innodb_flush_method=O_DIRECT

В идеале рекомендуют каждую инстанцию ставить по 1Гб, т.е. в данном примере буфер состоит из 3 инстанций, т.к. buffer_pool_size = 3G

Размер памяти выделенный под данные и индексы, рекомендуется выставлять до 70-80% доступной ОЗУ.

innodb_buffer_pool_size = 3G

Если у InnoDB закончится память, то она запросит 4М у операционной системе, о чём предварительно запишет в лог.

innodb_additional_mem_pool_size = 4M

Путь к базе

innodb_data_home_dir = /var/lib/mysql/
innodb_log_group_home_dir = /var/lib/mysql/

Параметры файла данных, авто-расширение с шагом 10Мб. Но мы храним каждую таблицу в отдельном файле. Хотя есть некоторое мнение, что хранить можно в одном файле, особенно мелкие таблицы, не тратятся ресурсы на открытие таблиц.

innodb_data_file_path = ibdata1:10M:autoextend

Размер одного лог файла, при превышении размера будет создан ещё один файл, максимальное значение рекомендуют до 512МБ. Чем больше лог, тем дольше восстановление. Если лог будет маленький часто будет переполняться и обновляться, т.е. по его окончании будет создаваться контрольная метка. Много тоже нельзя, долго будет стартовать после аварийного завершения.

innodb_log_file_size = 256M

Кол-во файлов журналов

innodb_log_files_in_group = 2

Размер буфера лога, рекомендуют выставить максимум 8М, если имеется большое колво транзакций и Innodb_log_waits увеличивается, тогда ещё увеличить.

innodb_log_buffer_size = 8M

Сбрасывать лог транзакции раз в секунду при значении - 0. Считается самый оптимальный вариант между производительностью и надежностью. Самый медленный 1 - записывать лог после каждой транзакции. 2 - самый быстрый вариант, лог сбрасывается только в кэш ОС, а она потом фиксирует в лог, но так как у нас есть и финансовые данные ставим раз в секунду сбрасывать лог транзакций. Хотя можно будет попробовать поставить и «2», будет создавать контрольные точки раз в 2-5 секунд. «1» - это актуально для финансовых операций в базе, фиксируется каждая транзакция, нам не нужно.

innodb_flush_log_at_trx_commit = 0

Тайминг ожидания блокировки записи какой нибудь транзакции, я так понял что если транзакция сама себя заблокировала, ну т.е. что-то типа бесконечного цикла, до для этого есть этот тайминг, если запись не разблокировалась через 70 мс, то произойдет отмена блокировки и откат изменений.

innodb_lock_wait_timeout = 70
[mysqldump]
quick

Тут думаю можно и 64 не пожалеть, хотя больших строк у нас нет, но для дапма не жалко))

max_allowed_packet = 64M

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
  • sysadmin/mysql_tunning.txt
  • Последние изменения: 2019/08/29 10:56
  • — Дмитрий Грошилин