Профилирование запросов MySQL на примерах

Одним из инструментов повышения производительности MySQL, как и большинства других СУБД, является кэширование запросов. Однако преимущества использования кэша MySQL могут сойти на нет или даже может быть ухудшение производительности, если при настройке кэша не было полного представления о принципах и отличиях его работы по сравнению с другими СУБД . В этой статье я постараюсь как можно более подробно описать все преимущества, недостатки и нюансы использования кэширования sql-запросов.

Новые вопросы

1 Невозможно выбрать более 1 записи 2 Сообщения внутренней таблицы соединения запроса MariaDB SQL с пользователями таблицы 1 Если MySQL выключен, то включен с другим столбцом 3 Как получить первый элемент и последний элемент в одном выражении sql 1 Я не могу получить список игроков на Android Studio через MySQL, PHP, Volley в порядке убывания очков 1 Таблица mysql 2 с различными 1 Можно ли сохранить объект, который ссылается на несохраненный прокси? 1 Эквивалент следующего кода в nodejs. Я хочу знать, что делает метод FirstorDefault? 1 Django REST RetrieveAPIView, объединяющий наборы запросов 1 Нахождение второго по величине значения подряд в MySQL

Все вопросы по теме mysql >

Как сохранить вывод команды в файл с помощью PowerShell

Чтобы сохранить вывод команды в текстовый файл с помощью PowerShell, выполните следующие действия:

  1. Откройте PowerShell, например, через системный поиск Windows.
  2. Введите следующую команду, чтобы сохранить вывод в текстовый файл и нажмите Enter:

    ваша-команда | Out-File -FilePath c:\путь\папка\

    В этой команде обязательно замените «ваша-команда» на нужную вам командную строку, а «c:\путь\папка\» на путь и имя файла для хранения выходных данных.

  3. (Необязательно) Введите следующую команду, чтобы просмотреть сохраненный вывод на экране, и нажмите Enter:

    Get-Content -Path c:\путь\папка\

    В этой команде обязательно замените «c:\путь\папка\» на путь и имя файла с выходным содержимым.

После выполнения шагов команда PowerShell сохранит результат в текстовый файл в указанном вами месте.

Читайте также:  BIND 9: опыт настройки и эксплуатации DNS-сервера

Мы ориентировались в этом руководстве на Windows 10, но вы также можете использовать эти инструкции для Windows 8.1 и Windows 7.

Кэшируйте правильно

Как было сказано выше, далеко не во всех ситуациях использование кэширования запросов будет давать выигрыш в производительности. Чтобы однозначно ответить на вопрос в каких случаях использовать кэш, надо понимать специфику работы именного вашего сервера баз данных. Запросы из таблиц с частым обновлением данных необходимо будет обновлять в кэше при каждом изменении хотя бы одной записи в таблице, что не имеет смысла в принципе и в конечном итоге обернется для вас снижением производительности. Однако практически в каждой базе данных, при условии что спроектирована она должным образом, найдутся таблицы, данные в которых не изменяются или изменяются достаточно редко. Таким образом, может возникнуть вполне логичная мысль кэшировать только запросы из определенных таблиц, число операций DML 5 с которыми на порядки ниже, чем DQL.

Благо в MySQL есть возможность избирательного кэширования и определяется она значением переменной query_cache_type 6, которая может принимать три значения:

0 или OFF Не кэшировать или не извлекать результаты из кэша запросов. Стоит отметить, что эта опция не отключает буфер кэша в оперативной памяти. Чтобы его отключить, вы должны установить переменную размера кэша в значение 0.
1 или ON Кэшировать все результаты запросов, которые являются кэшируемыми, кроме запросов, начинающихся на SELECT SQL_NO_CACHE.
2 или DEMAND Кэшировать только результаты запросов, которые, во-первых, поддаются кэшированию, а во-вторых, начинаются на SELECT SQL_CACHE.

Если есть необходимость кэшировать только определенные запросы, рекомендуется в них напрямую указать необходимость кэширования с помощью инструкции SELECT SQL_CACHE, а также выставить переменную типа кэша в значение 2 или DEMAND.

Важным моментом в использовании кэша является максимальное ограничение размера результата запроса в 1МВ (значение по умолчанию). Запросы, результат которых больше этого значения, не попадут в кэш. Поэтому если у вас такие запросы могут быть, рекомендуется увеличить значение переменной query_cache_limit 7 до необходимого.

Читайте также:  Как настроить родительский контроль в Windows 10

Использование профиля для оптимизации

Итак, у вас есть профиль сервера или запроса — что с ним делать? Хороший профиль обычно делает проблему очевидной, но решения может и не быть (хотя чаще всего есть). На этом этапе, особенно при оптимизации запросов, вам нужно полагаться на знания о сервере и о том, как он выполняет запросы. Профиль или те данные, которые вы можете собрать, указывают направление движения и дают основания для применения ваших знаний и нахождения результатов с помощью дополнительных инструментов, таких как EXPLAIN.

В общем, хотя нахождение источника проблемы с помощью профиля со всеми метри­ками не должно представлять труда, наделе невозможно выполнить измерения абсо­лютно точно, поскольку оцениваемые системы не поддерживают этой возможности. Ранее, рассматривая пример, мы подозревали, что на временные таблицы и неиндек­сированные чтения затрачивается большая часть времени отклика, однако не можем этого доказать. Иногда проблемы трудно решить, потому что, возможно, не измерено все, что нужно, либо измерения сделаны в неверном направлении. Например, вы мо­жете определять активность всего сервера вместо изучения того фрагмента, который пытаетесь оптимизировать, или анализировать измерения, проведенные с момента времени до начала выполнения запроса, а не тогда, когда он был запущен.

Существует еще одна возможность. Предположим, вы анализируете журнал мед­ленных запросов и находите простой запрос, на несколько запусков которого за­трачено неоправданно много времени, хотя он быстро запускался в тысячах других случаев. Вы снова запускаете запрос, и он выполняется молниеносно, как и должно быть. Применяете EXPLAIN и обнаруживаете, что он правильно использует индекс. Вы пытаетесь использовать похожие запросы с разными значениями в разделе WHERE, чтобы убедиться, что запрос не обращается к кэшу, и они тоже выполняются быстро. Кажется, что с этим запросом все нормально. Что дальше?

Если у вас есть только стандартный журнал медленных запросов MySQL без плана выполнения или подробной информации о времени, вы знаете только, что запрос плохо работал, когда был журналирован, и не можете понять, почему это произошло. Возмож­но, что-то еще потребляло ресурсы в системе, например резервное копирование или какая-то блокировка или параллелизм тормозили ход запроса. Периодически возника­ющие проблемы — это особый случай, который мы рассмотрим в следующей статье.

Читайте также:  Как установить Windows 7, 8, 10 на флешку?

Вас заинтересует / Intresting for you: Модель развития базы данных My… 696 просмотров Ирина Светлова Thu, 10 Jan 2019, 12:29:03 Транзакции в базе данных MySQL 7251 просмотров Ирина Светлова Mon, 07 Jan 2019, 05:18:23 Выбор оптимальных типов данных… 3160 просмотров Валерий Павлюков Sun, 27 Oct 2019, 15:24:19 Обзор версий MySQL — какой рел… 4262 просмотров Ирина Светлова Fri, 05 Feb 2021, 17:19:41 Author: AidaДругие статьи автора:

  • Инновации SQL Server 2019. Использование технологий больших данных и машинного обучения
  • Фармгигант AstraZeneca готов поставлять вакцину от коронавируса по всему Миру
  • Внутренняя SEO оптимизация сайта

Первоначальная настройка

У MySQL достаточно сложная конфигурация, но оптимизация запросов не требует делать все вручную. Для устранения проблем со скоростью выполнения существует специальный скрипт — MySQLTuner. Он анализирует работу базы данных и выводит рекомендации, какие параметры с какими значениями требуется изменить.

Чтобы скрипт работал и показывал текущие проблемы, необходимо загрузить три файла через wget:

# wget -O # wget -perl/master/basic_ -O basic_ # wget -perl/master/ -O

Первоначальная настройка

Первый файл — это скрипт написан на Perl. Два остальных — сведения о простых паролях и уязвимостях, которые позволяют найти проблемы с безопасностью.

Далее необходимо провести тест базы данных. Оптимизация производительности будет основана на выявленных проблемах. Тест запускается скриптом # perl ./. Он выдает полную статистику работы базы. Все проблемные места обозначаются красным восклицательным знаком [!].

Следует помнить, что параметры изменяются согласно рекомендациям, которые выдает утилита. Нельзя бездумно копировать параметры из статьи и применять их к собственной базе данных. В ином случаем можно столкнуться с рядом практических проблем. Например, недостаточным размером буфера движка таблиц (InnoDB buffer pool).