✎ Оптимизация работы MySql

Вы загрузили старую версию документа! Сохранив её, вы создадите новую текущую версию с этим содержимым.

====== Оптимизация работы MySql ======

===== Анализ окружения =====

<code sql>
SHOW VARIABLES LIKE '%trx%';

SHOW VARIABLES LIKE '%innodb%';

SHOW VARIABLES LIKE '%cache%';
</code>

===== Настройка =====

**key_buffer_size = 512M**
Таким образом выделяем 512 Mb под индексы таблиц MyISAM. Дело в том, что у меня половина баз в MyISAM (так исторически сложилось).
На 99,9% эти базы используются на чтение, так что переходить на InnoDB смысла нет.

**innodb_buffer_pool_size = 512M**
Такой же объем памяти выделяем на таблицы InnoDB.
Тут нужно знать меру. Если у вас 1 база размером 100 Mb, то нет смысла выделять 1 Гб памяти – она всё равно не будет использована.
Во-вторых, нужно смотреть не на размер таблицы, а на размер индексов. Пример из жизни: таблица 300 000 комментариев весит 300 Мб,
а ее индексы занимают в 15 раз меньше, что вполне логично, так как обычно индексы расставляются на числовые и временные столбцы, а не на текст.

**innodb_additional_mem_pool_size = 16M**
Размер памяти, выделяемый InnoDB для хранения различных внутренних структур.

**innodb_flush_method = O_DIRECT**
Тут мы вырубаем буферизацию таблиц для файловой системы и говорим MySQL обращаться к файлам напрямую.

**innodb_flush_log_at_trx_commit = 2**
При каждой транзакции MySQL пишет лог и сбрасывает на диск (значение 1). Значение 2 – сбрасываем в память. Мне не критично потерять транзакции за последние 2 секунды в случае падения сервера.

**join_buffer_size = 8M**
Память для запросов с джойнами, когда объединение происходит без использования индексов.

**sort_buffer_size = 8M**
read_rnd_buffer_size = 8M
Полезно для запросов с сортировкой ORDER BY и группировкой GROUP BY. При малом значении сортировка идет во временной таблице на диске.

**tmp_table_size = 64M**
**max_heap_table_size = 32M**
Настройки для хранения временных таблиц в памяти. Временные таблицы часто образуются при больших джойнах.

**table_cache = 256**
Максимальное число одновременно открытых таблиц.
Количество одновременных соединений * количество открытых таблиц в соединении
Т.е. для каждого соединения используется свои ячейки из кэша.

**log_slow_queries** = /var/log/mysql/mysql-slow.log
**long_query_time = 1**
Пишем в лог медленные запросы - http://ekimoff.ru/403/

**query_cache_type = 2**
**query_cache_limit = 1M**
**query_cache_size = 32M**
Кэширование запросов внутри MySQL - http://ekimoff.ru/384/

===== Пример конфигурации =====

<code>
[mysqld]

interactive_timeout=180
wait_timeout=180

#collation_server=utf8_general_ci
#character_set_server=utf8
#default-character-set = utf8
default-storage-engine = InnoDB
key_buffer_size = 64M

join_buffer_size = 8M
sort_buffer_size = 8M
read_rnd_buffer_size = 8M
tmp_table_size = 128M
max_heap_table_size = 64M
table_cache = 256
table_open_cache = 64

query_cache_type = 2
query_cache_limit = 64M
query_cache_size  = 64M

innodb_buffer_pool_instances = 2
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256MB
innodb_log_buffer_size = 4MB
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_additional_mem_pool_size = 16M
#transaction-isolation = READ-COMITTED
innodb_doublewrite=0
innodb_support_xa=0
#innodb_thread_concurrency=10
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table=1
skip-innodb_doublewrite
innodb_file_format=barracuda
#skip-name-resolve=1

long_query_time = 10
log-queries-not-using-indexes = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log = 1
</code>

===== Анализ запросов =====

==== Утилита mysqldumpslow ====

<code bash>
# mysqldumpslow -s at -t 10

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows_sent=1.0 (1), Rows_examined=2.0 (2), Rows_affected=0.0 (0), root[root]@localhost
  SELECT kc.CONSTRAINT_NAME,kc.TABLE_NAME,kc.COLUMN_NAME,kc.ORDINAL_POSITION
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc WHERE kc.TABLE_SCHEMA='S' AND kc.REFERENCED_TABLE_NAME IS NULL AND kc.TABLE_NAME='S'
  ORDER BY kc.CONSTRAINT_NAME,kc.ORDINAL_POSITION
</code>

* Count — количество вхождений запроса в лог;
  * Time — среднее и общее время запроса;
  * Lock — время блокировки таблицы;
  * Rows — Количество выбранных строк

=== Параметры ===

* -s at 
  * -t 10 
  * /var/log/mysql/slow.log

==== Mytop ====

[[mysql:mytop|]]

==== Explain ====

==== Сетевые подключения ====

<code bash>
$ netstat -ntp | grep :51244
tcp        0      0 127.0.0.1:3306          127.0.0.1:51244         ESTABLISHED 8552/mysqld
tcp        0      0 127.0.0.1:51244         127.0.0.1:3306          ESTABLISHED 24492/sshd: ro
</code>

где 45384 - можно получить из **show full processlist;**

<code>
6	root	localhost		Sleep	0			0
2003	root	localhost:51244	skringo	Query	0	init	show full processlist	0
</code>

Сводка изменений

Примечание: редактируя эту страницу, вы соглашаетесь на использование своего вклада на условиях следующей лицензии: CC0 1.0 Universal