После того, как наш сервис Mysql поработает какое-то время, он по какой-то причине станет медленным, как найти причину?
1. Ключевые показатели
При оценке производительности базы данных есть несколько показателей, которые очень важны.Это не ключевая роль, которую они могут играть для оценки возможностей базы данных, а способность более четко представлять базу данных в некоторых аспектах.
1.IOPS
IOPS: операция ввода-вывода в секунду, количество запросов ввода-вывода, обрабатываемых в секунду.
Мы знаем, что ввод-вывод — это возможность чтения и записи диска, например чтение 300 МБ и запись 200 МБ в секунду, что является пропускной способностью данных (еще один ключевой показатель возможностей ввода-вывода), ноIOPSЭто не относится к пропускной способности данных при чтении и записи, IOPS относится к每秒能够处理的 I/O 请求次数.
Если вы хотите, чтобы система ввода-вывода реагировала достаточно быстро, то чем выше IOPS, тем лучше, потому что IOPS связан с аппаратным обеспечением, поэтому для повышения IOPS в настоящее время можно в основном бороться только с аппаратным обеспечением. решение состоит в использовании нескольких дисков через RAID-страйпы.После этого, чтобы улучшить возможности чтения и записи ввода-вывода, мы также можем использовать твердотельные накопители (SSD) для повышения IOPS, но стоимость твердотельных накопителей может быть относительно высокий.
2.QPS
QPS: Query Per Second, количество запросов (запросов) в секунду. Этот параметр очень важен и может интуитивно отражать производительность системы, так же как IOPS измеряет, сколько запросов может получить диск в секунду.
Мы можем выполнить в режиме командной строки MySQLstatusкоманда, последняя строка возвращаемой информации содержит индикатор количества запросов в секунду.
3.TPS
TPS: Transaction Per Second, количество транзакций в секунду. Параметр TPS не предоставляется MySQL изначально, если нам нужно его рассчитать самостоятельно, мы можем использовать формулу расчета:
TPS = (Com_commit + Com_rollback) / SecondsЭта формула имеет две переменные состояния, представляющие количество коммитов и количество откатов, а Seconds — это определенный нами интервал времени.
2. Ключевые показатели теста TPCC
TPCC-MySQL — это набор тестовых программ MySQL, разработанный Percona на основе спецификации TPCC.Мы используем этот набор инструментов для тестирования предыдущих трех важных показателей.
1. Установка и использование инструмента TPCC
Для конкретной установки вы можете увидеть эти два сообщения в блогеИспользование инструмента стресс-тестирования MySQL tpcc-mysql,тест производительности mysql-tpcc, TPCC может лучше имитировать онлайн-бизнес.
3. Оптимизация конфигурации параметров базы данных
Если параметры базы данных настроены разумно, эффективность работы может быть значительно повышена, то есть можно максимально использовать системные ресурсы.
Конфигурация по умолчанию MySQL можно просматривать с этой командой:
show global variablesshow global variables like '%max_connections%'1. Параметры, связанные с подключением
1.1 max_connections
max_connections: указать сервер MySQL最大并发连接数, значение находится в диапазоне от 1 до 100 000, а значение по умолчанию — 151.
Этот параметр очень важен, поскольку он определяет, сколько сеансов может одновременно подключаться к службе MySQL. При установке этого параметра, в соответствии с конфигурацией и производительностью сервера базы данных, обычно не представляет большой проблемы установка значения параметра в диапазоне от 500 до 2000.
1.2 max_connect_errors
max_connect_errors:指定允许连接不成功的最大尝试次数, значение находится в диапазоне от 1 до 2^64, а значение по умолчанию — 100 в версии 5.6.6.
一定不要忽视这个参数，如果尝试连接的错误数量超过该参数指定值，则服务器就不再允许新的连接，没错，就是拒绝连接，尽管 MySQL 仍在提供服务，但无法创建新的连接了。 можно использоватьFLUSH HOSTSСостояние очищается или перезапускается служба базы данных, но эта цена слишком высока, как правило, этого не делают, и так,这个参数的默认值太小，这里建议将之设置为 10 万以上的量级.
1.3 интерактивный_тайм-аут и ожидание_тайм-аут
Эти два параметра связаны с автоматическим отключением сеанса соединения по тайм-ауту.Первый используется для указания времени ожидания перед закрытием интерактивного соединения, а второй используется для указания времени ожидания перед закрытием неинтерактивного соединения. в секундах, а значение по умолчанию — 28800, или 8 часов.
1.4 skip-name-resolve
skip-name-resolve: Это можно просто понять как отключение разрешения DNS.Обратите внимание, что это поведение сервера.При подключении имя хоста клиента не проверяется, а используется только IP. Если указан этот параметр, при создании пользователей и предоставлении разрешений в столбце HOST должен быть IP-адрес, а не имя хоста. Рекомендуется включить этот параметр, который помогает ускорить сетевое соединение и пропускает разрешение имени хоста.
1.5 back_log
back_log: указать MySQL连接请求队列中存放的最大连接请求数量,существует5.6.6Версия前, значение по умолчанию — 50, а максимальное значение не превышает 65 535. существует5.6.6версия后, значение по умолчанию равно -1, что означает, что оно автоматически настраивается MySQL.На самом деле существуют правила для так называемой самонастройки, то есть 50+ (max_connections/5).
Этот параметр в основном имеет дело с большим количеством запросов на подключение за короткий промежуток времени. Основной поток MySQL не может вовремя выделить (или создать) поток подключения для каждого запроса на подключение. Что мне делать? Он не может напрямую отклонить его.于是就将一部分请求放到等待队列中待处理, длина этой очереди ожидания является значением параметра back_log.Если очередь ожидания также заполнена, последующие запросы на подключение будут отклонены.
2. Параметры, связанные с файлом
2.1 sync_binlog
sync_binlog: указывает уровень файлов бинарных журналов синхронизации, по умолчанию используется по умолчанию.
2.2 expire_logs_day
expire_logs_day:指定设置二进制日志文件的生命周期，超出则将自动被删除, значение параметра указано в днях, а значение находится в диапазоне от 0 до 99. Значение по умолчанию равно 0. Рекомендуется устанавливать этот параметр в диапазоне от 7 до 14, и его достаточно сохранить в течение одной-двух недель.
2.2 max_binlog_size
max_binlog_size: указывает размер двоичного журнала, значение варьируется от 4 КБ до 1 ГБ, по умолчанию — 1 ГБ.
3. Параметры управления кешем
3.1 thread_cache_size
thread_cache_size: указывает количество потоков, которые MySQL кэширует для быстрого повторного использования. Диапазон значений от 0 до 16384, значение по умолчанию равно 0.
Как правило, когда клиент прерывается, когда соединение создается, его можно быстро создать, и MySQL помещает соединение с клиентом в кеш, а не сразу прерывает освобождение ресурса. Это позволяет вам добиваться успеха, когда у вас есть новый клиентский запрос на подключение. Поэтому этот параметр лучше всего поддерживать на определенном уровне, его рекомендуется устанавливать300~500Между возможными.Кроме того, хитрейт кэширования потоков также является более важным показателем мониторинга, правила расчета таковы (1-threads_created/connections) *
100%, мы можем оптимизировать и настроить параметры thread_cache_size через этот индикатор.
3.2 query_cache_type
sql_cache означает помещение результатов запроса в кэш запросов.
sql_no_cache означает, что результаты запроса не кэшируются при запросе.
sql_buffer_result означает, что в операторе запроса результат запроса кэшируется во временной таблице.
Все три используются вместе. sql_buffer_result снимет блокировку таблицы как можно скорее, чтобы другие sql могли выполняться как можно скорее.
Используя команду FLUSH QUERY CACHE, вы можете организовать кэш запросов, чтобы лучше использовать его память. Эта команда не удаляет запросы из кэша. FLUSH TABLES делает дамп для очистки кеша запросов. Задача RESET QUERY CACHE удаляет все результаты запроса из кэша запросов.
那么mysql到底是怎么决定到底要不要把查询结果放到查询缓存中呢？
основан наquery_cache_typeопределяется этой переменной.
Эта переменная имеет три значения:0,1,2, которые соответственно представляютвыкл, вкл, спрос.
mysql включен по умолчанию на
Это означает, что если он равен 0, тоquery cacheЗакрыто.
Если он равен 1, то запрос всегда сначала ищет в кеше запросов, даже если SQL_NO_Cache по-прежнему запрашивает кеш, потому что SQL_NO_CACHE — это просто результат запроса кеша, а не результаты запроса.
select count(*) from innodb;
1 row in set (1.91 sec)
select sql_no_cache count(*) from innodb;
1 row in set (0.25 sec)Если это 2, ТРЕБОВАНИЕ.
Добавьте строку в my.ini
query_cache_type=2
перезапустить службу mysql
select count(*) from innodb;
1 row in set (1.56 sec)
select count(*) from innodb;
1 row in set (0.28 sec)sql_cache не используется, кажется, что кэш запросов все еще используется
select sql_cache count(*) from innodb;
1 row in set (0.28 sec)Время запроса такое же при использовании sql_cache, поскольку sql_cache только помещает результаты запроса в кеш.Если sql_cache не используется, запрос сначала будет искать данные в кеше запросов.
Вывод: Пока query_cache_type не отключен, запросы SQL всегда будут использовать кеш запросов.Если кеш не сработает, план выполнения запроса начнет запрашивать данные в таблице.
Преимущества и недостатки кеша запросов
Преимущества очевидны: для некоторых частых запросов на выборку mysql напрямую возвращает соответствующий результирующий набор из кэша, а не извлекает его из таблицы, что снижает накладные расходы на ввод-вывод.
Несмотря на то, что преимущества кэширования запросов очевидны, нельзя игнорировать некоторые его недостатки:
- Потребление ресурсов, вызванное вычислением хэша и поиском хэша оператора запроса. MySQL будет выполнять расчет хэша для каждого полученного запроса типа select, а затем проверять, существует ли кеш запроса.Хотя эффективность вычисления хэша и поиска достаточно высока, потребление, вызванное запросом, можно игнорировать, но как только оно задействован Высокий параллелизм, когда есть тысячи запросов, стоимость вычисления хэша и поиска будет восприниматься серьезно;
- Проблема инвалидации кеша запросов. Если таблица часто изменяется, кэш запросов будет иметь очень высокую частоту сбоев. Изменение таблицы относится не только к изменению данных в таблице, но также включает любое изменение в структуре или индексе;
- Для запросов с разными SQL, но одним и тем же результирующим набором будет кэшироваться, что приведет к чрезмерному потреблению ресурсов памяти. Разный регистр символов sql, пробелы или комментарии, кеши считаются разными sql (поскольку их хэш-значения будут разными);
- Необоснованная установка связанных параметров приведет к значительной фрагментации памяти, соответствующие параметры будут введены позже.
Разумное использование кеша запросов
Кэш запросов имеет свои преимущества и недостатки, и разумное использование кеша запросов может заставить его использовать свои преимущества и эффективно избежать его недостатков.
- Не все таблицы подходят для кэширования запросов. Основная причина аннулирования кеша запросов заключается в том, что соответствующая таблица изменилась, поэтому вам следует избегать использования кеша запросов для часто меняющихся таблиц. В mysql есть две специальные подсказки sql для кеша запросов: SQL_NO_CACHE и SQL_CACHE, которые соответственно указывают, что принудительно не использовать и принудительно использовать кеш запросов.Принуждая не использовать кеш запросов, mysql не может использовать кеш запросов для часто меняющихся таблиц, поэтому Уменьшены накладные расходы памяти, а также накладные расходы на вычисление хэша и поиск;
Дополнительные сведения о кэше запросов см. в исходном тексте здесь:оптимизация кеша запросов mysql
3.3 query_cache_size
query_cache_size: указывает размер области памяти, используемой для кэширования набора результатов запроса. Значение этого параметра должно быть целым числом, кратным 1024.
Этот параметр не может быть слишком большим или слишком маленьким, кэшу запросов потребуется не менее 40 КБ пространства для выделения собственной структуры, если он слишком мал, то нет смысла кэшировать результирующий набор, а горячие данные не могут быть сохранены много , и сбрасывается он всегда быстро; но он не должен быть слишком большим, иначе может занимать слишком много ресурсов памяти и влиять на производительность всей машины.再说太大也没有意义，因为即便数据不被刷新，但只要源数据发生变更，缓存中的数据也就自动失效了，这种情况下分配多大都没有意义. Лично рекомендую настроить не превышать256MB.
3.4 query_cache_limit
query_cache_limit: используется для управления кешем запросов. Максимальный набор результатов, сгенерированный одним оператором SQL, который может быть кэширован, по умолчанию составляет 1 МБ. Если он превышает, не входите в кеш запросов. Этого размера достаточно для многих сцен, его можно уменьшить, но не обязательно увеличивать.
3.5 sort_buffer_size
sort_buffer_size: указывает размер области сортировки, которая может использоваться одним сеансом. Значение по умолчанию – 256 КБ. Рекомендуется установить его на1~4MBмежду.
3.6 read_buffer_size
read_buffer_size: указывает размер области буфера данных для произвольного чтения. По умолчанию 256 КБ, а максимальное значение может поддерживаться 4 ГБ. Правильное увеличение этого параметра поможет повысить эффективность полного сканирования таблицы.
4. Параметры, специфичные для InnoDB
4.1 innodb_buffer_pool_size
innodb_buffer_pool_size: указывает размер области буфера, выделенной для механизма InnoDB, используемой для кэширования данных и индексной информации табличных объектов, значение по умолчанию составляет 128 МБ, а максимальное значение может поддерживаться (2^64 -1) B.
Если у вас много транзакционных обновлений, вставок или удалений, вы можете сэкономить много места на диске, изменив параметр размера innodb_buffer_pool.I / O.
innodb_buffer_pool_size является глобальным параметром, и выделенная область буфера будет использоваться всеми доступными объектами таблицы InnoDb.若MySQL数据库中的表对象以 InnoDb 为主，那么本参数的值就越大越好，官方文档中建议，可以将该参数设置为服务器物理内存的70%~80%.
4.2 innodb_buffer_instances
innodb_buffer_instances: указывает, на сколько областей разделен буферный пул InnoDB.Значение находится в диапазоне от 1 до 64. Значение по умолчанию равно -1, что означает, что InnoDB изменит его самостоятельно.
Этот параметр действителен только тогда, когда значение параметра innodb_buffer_pool_size больше 1 ГБ.那么本参数怎么设置呢？个人感觉可以参照 InnoDB 缓存池的大小，以 GB 为单位，每GB指定一个instances. Например, если для innodb_buffer_pool_size установлено значение 16 ГБ, набор 16 можно обозначить как innodb_buffer_instances.
5. Случай оптимизации параметров
Тестовый сервер имеет 16 ГБ физической памяти.Предполагается, что максимальное количество подключений в пике равно 500. Объект таблицы использует механизм хранения InnoDB.Как настроить параметры нашей памяти?
Конкретная конфигурация выглядит следующим образом:
(1) Во-первых, зарезервируйте 20% памяти для операционной системы, около 3 ГБ.
(2) Несколько ключевых параметров, связанных с потоками, устанавливаются следующим образом:
sort_buffer_size=2m
read_buffer_size=2m
read_rnd_buffer_size=2m
join_buffer_size=2mКогда ожидается, что количество подключений достигнет пика, ожидается, что поток будет занят наибольшим числом подключений. 500 *（2+2+2+2）= 4GBПамять (теоретический максимум).
(3), оставшееся пространство 16-3-4 = 9 ГБ, вы можете выделить его для кэш-пула InnoDB, установив соответствующие параметры следующим образом:
innodb_buffer_pool_size=9g
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=16m
innodb_flush_log_at_trx_commit=24. Статус системы MySQL
Чтобы понять, что в данный момент делает служба MySQL, есть очень важная и очень распространенная команда:
SHOW [FULL] PROCESSLISTSHOW PROCESSLISTКоманда выводит каждый подключенный поток как отдельную запись.
Существуют также аналогичные операторы SHOW PROFILES и SHOW PROFILE, которые могут получать информацию об использовании ресурсов во время выполнения оператора в сеансе.