Различия

Показаны различия между двумя версиями страницы.

Ссылка на это сравнение

Следующая версия		 Предыдущая версия
mysql:optimize [2015/10/13 14:21] – внешнее изменение 127.0.0.1mysql:optimize [2018/06/21 12:53] (текущий) – [Утилита mysqldumpslow] mirocow
Строка 12: Строка 12:
 SHOW VARIABLES LIKE '%cache%'; SHOW VARIABLES LIKE '%cache%';
 </code> </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>
 +
 +Наблюдение в реальном времени
 +<code bash>
 +$ watch --interval=1 mysqldumpslow -s at -t 10
 +</code>
 +
 +  * [[mysql:sql-slow-query-monitor]]
 +
 +  * 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>
 +
 +===== Статьи =====
 +
 +  * http://devacademy.ru/posts/profilirovanie-zaprosov-v-mysql/
 +  * https://ruhighload.com/%D0%B8%D0%BD%D0%B4%D0%B5%D0%BA%D1%81%D1%8B
 +  * https://ruhighload.com/%D0%9A%D0%B0%D0%BA+%D0%B2%D0%BA%D0%BB%D1%8E%D1%87%D0%B8%D1%82%D1%8C+slow+log+%D0%B2+mysql%3F
 +  * https://ruhighload.com/doc/clickhouse/development/architecture/
 +  * http://ekimoff.ru/384/
 +  * http://yapro.ru/web-master/mysql/optimizaciya-mysql-s-pomoshiyu-nastroek-v-my-cnf.html
 +  * http://www.sql.ru/forum/1058569/join-buffer-size-kak-opredelit-optimalnoe-znachenie