Tuning Mysql [чистовик] [Документация ACP MikBiLL]

Эта страница только для чтения. Вы можете посмотреть её исходный текст, но не можете его изменить. Сообщите администратору, если считаете, что это неправильно.
====== Tuning Mysql [чистовик]======

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

<sxh language width classes>
[client]
port = 3306
socket=/var/run/mysqld/mysqld.sock
</sxh>


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

<sxh language width classes>
[mysql]
loose-local-infile=1
</sxh>


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

<sxh language width classes>
[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
</sxh>



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

<sxh language width classes>
skip-name-resolve
</sxh>



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

<sxh language width classes>
low-priority-updates
</sxh>



**query_cache**

<sxh language width classes>
query_cache_size = 256M
query_cache_limit = 256M
</sxh>


убеждаемся 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** нужно увеличивать. 
Либо рассчитать по формуле: 

<sxh language width classes>
query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache. 
</sxh>

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

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

<sxh language width classes>
qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)  
</sxh>

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

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

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

<sxh language width classes>
SHOW GLOBAL STATUS LIKE 'Qcache%'
SHOW VARIABLES LIKE 'query_cache_%';
</sxh>



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

<sxh language width classes>
join_buffer_size = 256M
max_join_size=256M
</sxh>


блокировать хост при 1000 неудачных попыток работы-соединения
<sxh language width classes>
max_connect_errors = 1K
</sxh>



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

<sxh language width classes>
max_allowed_packet=16M
</sxh>


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

<sxh language width classes>
table_open_cache = 1k
table_cache = 1k
</sxh>



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

<sxh language width classes>
max_heap_table_size = 1024M
tmp_table_size = 1024M
</sxh>


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

<sxh language width classes>
thread_cache_size = 32
</sxh>


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

<sxh language width classes>
max_connections = 64
</sxh>


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

<sxh language width classes>
key_buffer_size = 8M
myisam_sort_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
</sxh>


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

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


**InnoDB**


Каждую таблицу  хранить в отдельном файле 
<sxh language width classes>
innodb_file_per_table
</sxh>


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

<sxh language width classes>
innodb_flush_method=O_DIRECT
</sxh>


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


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

<sxh language width classes>
innodb_buffer_pool_size = 3G
</sxh>


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

<sxh language width classes>
innodb_additional_mem_pool_size = 4M
</sxh>


Путь к базе

<sxh language width classes>
innodb_data_home_dir = /var/lib/mysql/
innodb_log_group_home_dir = /var/lib/mysql/
</sxh>


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

<sxh language width classes>
innodb_data_file_path = ibdata1:10M:autoextend
</sxh>


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

<sxh language width classes>
innodb_log_file_size = 256M
</sxh>


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

<sxh language width classes>
innodb_log_files_in_group = 2
</sxh>


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

<sxh language width classes>
innodb_log_buffer_size = 8M
</sxh>


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

<sxh language width classes>
innodb_flush_log_at_trx_commit = 0
</sxh>


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

<sxh language width classes>
innodb_lock_wait_timeout = 70
</sxh>


<sxh language width classes>
[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
</sxh>