billing:mysql_tunning [MikBiLL]

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

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


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

<code>
[mysql]
loose-local-infile=1
</code>


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

<code>
[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
</code>



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

<code>
skip-name-resolve
</code>



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

<code>
low-priority-updates
</code>



**query_cache**

<code>
query_cache_size = 256M
query_cache_limit = 256M
</code>


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

<code>
query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache. 
</code>

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

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

<code>
qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)  
</code>

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

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

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

<code>
SHOW GLOBAL STATUS LIKE 'Qcache%'
SHOW VARIABLES LIKE 'query_cache_%';
</code>



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

<code>
join_buffer_size = 256M
max_join_size=256M
</code>


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



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

<code>
max_allowed_packet=16M
</code>


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

<code>
table_open_cache = 1k
table_cache = 1k
</code>



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

<code>
max_heap_table_size = 1024M
tmp_table_size = 1024M
</code>


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

<code>
thread_cache_size = 32
</code>


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

<code>
max_connections = 64
</code>


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

<code>
key_buffer_size = 8M
myisam_sort_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
</code>


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

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


**InnoDB**


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


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

<code>
innodb_flush_method=O_DIRECT
</code>


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


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

<code>
innodb_buffer_pool_size = 3G
</code>


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

<code>
innodb_additional_mem_pool_size = 4M
</code>


Путь к базе

<code>
innodb_data_home_dir = /var/lib/mysql/
innodb_log_group_home_dir = /var/lib/mysql/
</code>


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

<code>
innodb_data_file_path = ibdata1:10M:autoextend
</code>


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

<code>
innodb_log_file_size = 256M
</code>


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

<code>
innodb_log_files_in_group = 2
</code>


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

<code>
innodb_log_buffer_size = 8M
</code>


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

<code>
innodb_flush_log_at_trx_commit = 0
</code>


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

<code>
innodb_lock_wait_timeout = 70
</code>


<code>
[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
</code>