Содержание

Оптимизация и настройка производительности СУБД MySQL 📚

1 ноября 2019

Хостинг

14 306

0

Время чтения ≈ 11 минут

От скорости работы баз данных (БД) зависит быстрота отклика сайта. Ведь замедленная обработка запросов влияет на PHP, следовательно — накапливается огромное количество операций, с которыми сервер может не справиться.

Управлять данным процессом позволяет использование систем управления базами данных или СУБД. Одной из самых широко применяемых СУБД является MySQL — ПО с открытым исходным кодом, созданное компанией MySQL AB (Oracle) ещё в 1995 году.  Оптимизация MySQL позволяет избежать проблем с производительностью сервера и значительно ускорить интернет-ресурс.

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

Зачем оптимизировать работу MySQL

  • Увеличение скорости обработки и выполнения запросов.  Скорость работы сайта прямо пропорционально зависит от времени обработки и выполнения SQL-запроса к базе данных, которое должно быть минимальным.
  • Предотвращение перегрузки сервера. При перегрузке сервера работа web-ресурса или приложения будет нестабильной. Хостер может заблокировать ресурс, чтобы последний не нарушал работу всего сервера, на котором также работают и другие сайты.
  • Уменьшение времени ожидания загрузки web-страницы. Когда идет огромное количество SQL-запросов к базе данных, происходит существенное замедление работы сайта, что недопустимо для коммерческого или представительского интернет-ресурса.
  • Экономия ресурсов хостинга. Если MySQL не оптимизирована, то происходит значительный перерасход использования ресурсов сервера (процессорного времени, оперативной памяти). На этом основании хостер имеет право заблокировать работу ресурса.
  • Возможность масштабировать ресурс. При расширении сайта будет невозможно обеспечить хорошее качество его работы. Например, арендатор VPS загрузил на сервер интернет-магазин, в котором 100 видов товаров. Через некоторое время бизнес расширяется и появляется возможность предложить потребителю 10000 разновидностей. После загрузки и доработки интернет-магазин начинает работать медленно, постоянно происходят ошибки при помещении товаров в корзину и т. д.

Какие ресурсы желательно оптимизировать

  • Средне и высоконагруженные сайты с посещаемостью от 10 000 человек в сутки.
  • Сервера разработчиков веб-пиложений и сервисов.
  • Интернет-магазины с базой товаров, превышающей 100 единиц.
  • Высоконагруженные прокси/VPN сервера на основе VPS.
  • Системы работы с финансами и бухгалтерские приложения (продукты «1 C», ПланФакт, «Моё дело», Livesklad).
  • Приложения для мониторинга сервисов компьютерных сетей и серверов (Zabbix, Prometheus, Grafana).
  • Сайты с высокой посещаемостью и регистрациями, превышаемыми 100 пользователей в сутки.
  • Контент-биржи (Text. ru, Advego, Copylancer).
  • Видео- и фотохостинговые ресурсы (YouTube, Vimeo, Imgur).
  • Рекламно-баннерные площадки (myTarget, Adfox).
  • Финансово-экономические игры с возможностью вывода денег (Your Real Town, Surfer Money).

Скорость работы баз данных

Чтобы оптимизация СУБД MySQL дала результат, нужно начать с анализа работы баз данных. Настройки сервиса содержатся в файле /etc/my.cnf.

С помощью настроек можно проверить, какие запросы выполняются медленно и что можно ускорить. Для этого в раздел [mysqld] добавляется следующий запрос:

log-slow-queries=/var/log/mariadb/slow_queries.log
long_query_time=5

Информация указана в строчках:

log-slow-queries=/var/log/mariadb//slow_queries.log
long_query_time=2

Во второй обозначено минимальное время внесения запроса в лог — 2 секунды.

Чтобы увидеть актуальные данные, сервер перезапускается. Сведения находятся в логе:

# systemctl restart mariadb
# tail -f /var/log/mariadb/slow-queries.log

В полученном списке будут представлены все запросы, время выполнения которых превышает указанный показатель. К примеру, больше 10 секунд может выполняться запрос:

SELECT option_name, option_value
FROM wp_options
WHERE autoload = 'yes'

Если при его выполнении в MySQL операция происходит более 3 секунд, запрос может считаться медленным.

Сайт будет работать корректно при условии, что таких запросов немного. Но если у него постоянная нагрузка, количество необработанных запросов будет постепенно расти. Пропорционально им скорость ответа возрастет до нескольких минут.

Решить эту проблему может оптимизация таблиц или кода. В последнем случае из него убираются все сложные запросы.

Это длительный процесс, описать который в рамках одной статьи крайне затруднительно. Поэтому здесь мы подробно рассмотрим именно первый вариант.

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

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

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

# wget http://mysqltuner.pl/ -O mysqltuner.pl
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

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

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

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

Дополнительная настройка производительности

Чтобы оптимизация базы данных MySQL дала результат, понадобится следовать рекомендациям, представленным в сообщениях утилиты.

Рабочие параметры

Параметры, указанные в MySQL по умолчанию, рассчитаны на очень маленькие базы данных. Это значит, что они не предусматривают высокую нагрузку, что в том числе касается и самой техники.

Для MariaDB

Для настройки производительности вручную необходимо выполнить изменения указанных ниже параметров в файле конфигурации наиболее популярной версии MySQL MariaDB — /etc/mysql/conf.d/app.cnf.  

  • tmp_table_size — максимальная величина ОЗУ (оперативной памяти), выделяемая для хранения временных таблиц. Последние создаются при формировании SQL-запросов, которые осуществляют выбор данных из таблицы для дальнейших операций над ними. Значение необходимо выбирать экспериментальным путем, руководствуясь соотношением tmp_table_size = объем_ОЗУ * 0,75 (tmp_table_size = 64 М).
  • max_heap_table_size — максимальный размер таблицы, которая хранится в ОЗУ. Рекомендуемое значение параметра max_heap_table_size должно быть равным величине tmp_table_size.
  • query_cache_size — объем ОЗУ, выделяемого под кеш SQL-запросы. Его рекомендуется отключить для проектов с большим количеством записей, т.
    е. query_cache_size=0.
  • query_cache_type —параметр, активирующий или деактивирующий службу управления кешем в MySQL. Его рекомендуется отключить, установив query_cache_type в 0, т. е. query_cache_type = 0.
  • join_buffer_size — значение количества объема ОЗУ, которое выделяется для объединения таблиц баз данных. Рекомендуемое значение 8М.
  • sort_buffer_size — объем ОЗУ, выделяемый для выполнения операций сортировки данных. Рекомендуется установить значение, равное 10М.
  • max_connections — опция, определяющее максимальное число соединений с БД, приобретает значение, когда возникает сообщение об ошибке «Too many connections». Повышать это значение следует постепенно, вплоть до исчезновения ошибки.
Для InnoDB

Дополнительной оптимизации MySQL способствует настройка параметров наиболее популярной подсистемы MySQL для работы с таблицами InnoDB.

  • innodb_buffer_pool_size — если используются только таблицы InnoDB, необходимо установить максимально возможное значение с учетом технических характеристик системы. Оно должно быть 70-80% от доступной оперативной памяти. Например, при ОЗУ в 32 Гб: innodb_buffer_pool_size = 24G.
  • innodb_log_file_size
    — чем выше показатель, тем быстрее работают записи, то есть большее их количество помещается в файл лога. Поскольку файла всегда два, параметр задает значение только для одного. Например, innodb_log_file_size = 512M в сумме даст 1 G.
  • innodb_log_buffer_size — определяет размер буфера транзакций и изменяется только в том случае, если используются большие поля (TEXT, BLOB). 1М по умолчанию хватает в большинстве случаев.
  • innodb_flush_log_at_trx_commit — повышает пропускную способность записи данных в базу. Решает, сбрасывает ли MySQL каждую операцию в файл лога. В большинстве случаев подойдет вариант innodb_flush_log_at_trx_commit = 2. Следует учитывать два типа значения, где 1 — сохранность данных играет первую роль, 2 — небольшая потеря данных не критична благодаря дублированию.

Объединение таблиц

Часто проблема при работе с объемными базами данных возникает при выборке — попытке объединения (JOIN) столбцов из разных таблиц.

Чтобы ускорить JOIN больших таблиц MySQL, необходимо убедиться в том, что все столбцы проиндексированы. Скорость операции будет выше, если объединяются столбцы одного типа. При JOIN типов DECIMAL и INT, сервер не сможет использовать как минимум один индекс.

После завершения оптимизации тестирование проводится с помощью клиента MySQL:

# mysql
> USE база_данных;
> SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'

Первая операция может занять много времени, но последующие проверки происходят почти мгновенно. При оптимизации необходимо учитывать все советы утилиты.

Типы объединений

MySQL позволяет выполнять 3 типа объединений записей в двух таблицах (table_A и table_B):

  • внутреннее;
  • левостороннее внешнее;
  • правостороннее внешнее.
Пример № 1 — внутреннее

При внутреннем объединении двух таблиц происходит выборка общих записей. Для этой цели используется ключевое слово INNER.

Чтобы выполнить SQL-запрос, нужно запустить терминал, а затем активировать консоль MySQL командой:

sudo mysql -u имя_пользователя -p

Далее необходимо ввести пароль и SQL-запрос: SELECT * FROM tableA INNER JOIN tableB ON tableA.name = tableB.name, где «name» — имя поля для таблицы А.

Пример № 2 — левостороннее

Суть объединения заключается в миграции данных из таблицы «table_B», которых нет в таблице «table_A», в последнюю. Используется комбинация зарезервированных слов LEFT-OUTER.

SQL-запрос будет иметь такой вид: SELECT * FROM tableA LEFT OUTER JOIN tableB ON tableA.name = tableB.name. Если в «tableA» больше полей, чем в «tableB», то в первую запишутся значения NULL.

Пример № 3 — правостороннее

Главной является таблица «tableB». Для объединения необходимо применить ключевые слова RIGHT-OUTER.

В консоли СУБД нужно набрать SQL-запрос: SELECT * FROM tableB RIGHT OUTER JOIN tableA ON tableA.name = tableB.name. Недостающие поля заполняются константой NULL.

Заключение

Грамотная настройка и оптимизация MySQL позволяет достичь оптимально высоких показателей работы сервера и приложений, развернутых на его основе. Этому процессу способствует запуск скриптов, которые могут быстро обнаружить проблемы, влияющие на производительность базы данных.

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

Нужен производительный хостинг для высоконагруженных проектов на MySQL? Выбирайте VPS от Eternalhost — сервер на быстрых SDD, гарантированные ресурсы, запуск за 5 минут.

Оцените материал:

[Всего голосов: 3    Средний: 4/5]

Оптимизация производительности MySQL — Losst

MySQL — это одна из самых популярных реляционных систем управления базами данных, которая используется для обеспечения большинства веб-сайтов в интернете. От скорости записи и получения данных из таблиц зависит скорость работы сайта, в целом, так как, если на один запрос будет уходить больше секунды, то это будет тормозить работу php, а в следствии скоро накопиться столько запросов, что сервер не сможет их обработать.

В сегодняшней статье мы поговорим о том, как выполняется оптимизация производительности mysql. Какие программы для этого лучше использовать и как это работает.

Содержание статьи:

Скорость работы MySQL

Оптимизация без аналитики бессмысленна. Перед тем как переходить к оптимизации давайте посмотрим как работает база данных сейчас, есть ли запросы, которые выполняются очень медленно. Все настройки вашего сервиса mysql находятся в файле /etc/my. cnf. Чтобы включить отображение медленных запросов добавьте такие строки в my.cnf, в секцию [mysqld]:

log-slow-queries=/var/log/mariadb/slow_queries.log
long_query_time=5

Здесь первая строка включает запись лога медленных запросов, вторая указывает, что минимальное время запроса для внесения его в этот лог — две секунды. Еще можно включить в лог запросы, которые не используют индексы:

log-queries-not-using-indexes=1

Но это уже необязательно для проверки скорости и используется больше для отладки кода и правильности создания таблиц. Дальше перезапустите сервер баз данных и посмотрите лог:

systemctl restart mariadb

tail -f /var/log/mariadb/slow-queries.log

Мы можем видеть, что есть запросы, которые выполняются больше, чем 10 секунд. Это, например, запрос

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Можно его выполнить отдельно, в консоли mysql:

 

Здесь тоже измеряется время, и мы видим результат — три секунды. Это очень много. И еще ничего, если такие запросы приходят редко, если ваш сайт постоянно под нагрузкой, то тремя секундами вы не отделаетесь, количество необработанных запросов будет расти, а скорость ответа увеличиваться до нескольких минут. Можно пойти двумя путями — оптимизировать код, убрать сложные запросы, или же нужна оптимизация mysql на сервере.

Оптимизация MySQL

Конфигурация MySQL достаточно сложная, но, к счастью, вам не нужно в нее сильно углубляться. Есть специальный скрипт под названием MySQLTunner, который анализирует работу MySQL и дает советы какие параметры нужно изменить и какие значения для них установить. Скрипт поддерживает большинство версий MariaDB, MySQL и Percona XtraDB. Нам понадобится загрузить три файла с помощью wget:

wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent. com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

Первый из них — это сам скрипт, написанный на Perl, второй и третий — база данных простых паролей и уязвимостей. Они позволяют обнаружить проблемы с безопасностью. Дальше можно переходить к тестированию. Я использую сервер с настройками mysql по умолчанию, установленными панелью управления VestaCP.

perl ./mysqltuner.pl

Буквально за несколько минут скрипт выдаст полную статистику по работе MySQL. Количеству запросов, занимаемому объему памяти и эффективности работы буферов. Вы можете ознакомиться со всем этим, чтобы лучше понять в чем причина проблем. Проблемные места обозначены красными восклицательными знаками. Например, здесь мы видим, что размер буфера движка таблиц InnoDB (InnoDB buffer pool) намного меньше, чем должен быть для оптимальной работы:

Кроме того, в самом конце вывода утилита предоставит список рекомендаций как исправить ситуацию. Мы рассмотрим все сообщения утилиты из этого примера и почему нужно использовать именно их, а не другие.

Все параметры нужно добавлять в /etc/my.cnf. Еще раз замечу, что вы не копируете статью, а смотрите что вам выдала утилита. Начнем с query-cache.

query_cache_size=0
query_cache_type=0
query_cache_limit=1M

Скрипт рекомендует отключить кэш запросов. Query Cache — это кэш вызовов SELECT. Когда базе данных отправляется запрос, она выполняет его и сохраняет сам запрос и результат в этом кэше. И все бы ничего, но при использовании его вместе с InnoDB при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных — его надежнее отключить.

tmp_table_size=16M
max_heap_table_size=16M

Оба параметра устанавливают размер памяти, которая используется для внутренних временных таблиц MySQL. Утилита рекомендует использовать объем больше 16 мегабайт, просто установите это ваше значение для обоих переменных, если у вас достаточно памяти, то можно выделить 32 или даже 64. Но важно чтобы оба значения совпадали, иначе будет использоваться минимальное.

thread_cache_size=16

Этот параметр отвечает за количество потоков, которые будут закэшированны. После того, как работа с подключением будет завершена, база данных не разорвет его, а закэширует, если количество кэшированных потоков не превышает ограничение. Утилита рекомендует больше четырех, например, 16.

skip-name-resolve=1

Указывает, что не нужно пытаться определить доменное имя для подключений извне. Ускоряет работу, так как не тратится время на DNS запросы.

innodb_buffer_pool_size=800M

Этот параметр определяет размер буфера InnoDB в оперативной памяти, от этого размера очень сильно зависит скорость выполнения запросов. Значение зависит от размера ваших таблиц и количества данных в них. Если памяти недостаточно, запросы будут обрабатываться дольше. У меня используется стандартный объем 128, а нужно больше 652.

innodb_log_file_size=200M

Размер файла лога innodb должен составлять 25% от размера буфера. В случае 800 мегабайт это будет 200М. Но тут есть одна проблема. Чтобы изменить размер лога нужно выполнить несколько действий. Поскольку мы изменили все нужные параметры перейдем к перезагрузке сервера. Для нашего лога нужно остановить сервис:

systemctl stop mariadb

Затем переместите файлы лога в /tmp:

mv /var/lib/mysql/ib_logfile[01] /tmp

И запустите сервис:

systemctl start mariadb

Когда размер лога меняется сервис видит поврежденный лог, выдает ошибку и не запускается. Поэтому сначала нужно удалить старый. После этого смотрите есть ли сообщения об ошибках:

systemctl status mariadb

Тестирование результата

Готово оптимизация базы данных mysql завершена, теперь тестируем тот же запрос через клиент mysql:

mysql

> USE база_данных;
> SELECT option_name, option_value FROM wpfc_options WHERE autoload = 'yes';

Первый раз он выполняется долго, может даже дольше чем обычно, но все последующие разы буквально мгновенно. Результат с более 3 секунд до 0,15. А если брать статистику из slow-log, то от более 12. Если в выводе утилиты для вас были предложены и другие оптимизации, то их тоже стоит применить.

Выводы

Как видите, оптимизация mysql это достаточно просто благодаря такому скрипту, но, в то же время, такая операция может очень сильно помочь, особенно высоконагруженным проектам. Еще лучше ускорить работу может только оптимизация запросов mysql. Не забывайте время от времени проверять параметры, чтобы быть уверенным что все в порядке. Если у вас остались вопросы, спрашивайте в комментариях!

На завершение лекция про производительность MySQL от Percona:

Обнаружили ошибку в тексте? Сообщите мне об этом. Выделите текст с ошибкой и нажмите Ctrl+Enter.

Настройка производительности MySQL: 14 советов по оптимизации

Введение

MySQL — популярное приложение базы данных с открытым исходным кодом, которое хранит и структурирует данные осмысленным и легкодоступным способом. В больших приложениях огромный объем данных может привести к проблемам с производительностью.

В этом руководстве содержится несколько советов по настройке, позволяющих повысить производительность базы данных MySQL .

Предварительные требования

  • Система Linux с установленной и работающей MySQL, Centos или Ubuntu
  • Существующая база данных.
  • Учетные данные администратора для операционной системы и базы данных.

    1. Сбалансируйте четыре основных аппаратных ресурса

    Хранилище

    Оцените свое хранилище. Если вы используете традиционные жесткие диски (HDD), вы можете перейти на твердотельные накопители (SSD) для повышения производительности.

    Используйте такой инструмент, как iotop или sar из пакета sysstat , чтобы контролировать скорость ввода/вывода вашего диска. Если использование диска намного превышает использование других ресурсов, рассмотрите возможность добавления дополнительного хранилища или перехода на более быстрое хранилище.

    Процессор

    Процессоры обычно считаются мерой того, насколько быстро работает ваша система. Используйте команду Linux top , чтобы узнать, как используются ваши ресурсы. Обратите внимание на процессы MySQL и требуемый процент использования процессора.

    Обновление процессоров обходится дороже, но если ваш процессор является узким местом, может потребоваться обновление.

    Память

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

    Как и в случае с другими узкими местами, если на вашем сервере постоянно не хватает памяти, вы можете обновить ее, добавив больше памяти. Если вам не хватает памяти, ваш сервер будет кэшировать хранилище данных (например, жесткий диск), чтобы действовать как память. Кэширование базы данных снижает производительность.

    Сеть

    Важно отслеживать сетевой трафик, чтобы убедиться, что у вас достаточно инфраструктуры для управления нагрузкой.

    Перегрузка сети может привести к задержке, потере пакетов и даже к сбоям в работе сервера. Убедитесь, что пропускная способность сети достаточна для нормального уровня трафика базы данных.

    2. Используйте InnoDB, а не MyISAM

    MyISAM — это старый стиль базы данных, используемый для некоторых баз данных MySQL. Это менее эффективная структура базы данных. более новый InnoDB поддерживает более продвинутые функции и имеет встроенный механизм оптимизации.

    InnoDB использует кластерный индекс и хранит данные на страницах, которые хранятся в последовательных физических блоках. Если значение слишком велико для страницы, InnoDB перемещает его в другое место, а затем индексирует значение. Эта функция помогает хранить важные данные в одном и том же месте на устройстве хранения, а это означает, что физическому жесткому диску требуется меньше времени для доступа к данным.

    3. Используйте последнюю версию MySQL

    Использование последней версии не всегда возможно для старых и устаревших баз данных. Но по возможности следует проверить используемую версию MySQL и обновить ее до последней.

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

    Программное обеспечение Настройка производительности MySQL

    Настройка производительности SQL — это процесс максимального увеличения скорости выполнения запросов к реляционной базе данных. Задача обычно включает в себя несколько инструментов и методов.

    Эти методы включают:

    • Настройка файлов конфигурации MySQL.
    • Написание более эффективных запросов к базе данных.
    • Структурирование базы данных для более эффективного извлечения данных.

    Примечание: При настройке параметров конфигурации лучше вносить небольшие пошаговые корректировки. Серьезная корректировка может перегрузить другое значение и ухудшить производительность. Кроме того, рекомендуется вносить по одному изменению за раз, а затем тестировать. Легче отслеживать ошибки или неверные настройки, когда вы изменяете только одну переменную за раз.

    4. Рассмотрите возможность использования автоматического средства повышения производительности

    Как и в большинстве программ, не все средства работают со всеми версиями MySQL. Мы рассмотрим три утилиты для оценки вашей базы данных MySQL и порекомендуем изменения для повышения производительности.

    Первый тюнинг-грунтовка. Этот инструмент немного старше и предназначен для MySQL 5.5 — 5.7. Он может анализировать вашу базу данных и предлагать настройки для повышения производительности. Например, может предложить поднять query_cache_size , если вам кажется, что ваша система не может обрабатывать запросы достаточно быстро, чтобы кэш оставался чистым.

    Вторым инструментом настройки, полезным для большинства современных баз данных SQL, является MySQLTuner. Этот скрипт ( mysqltuner.pl ) написан на Perl. Как и tuning-primer, он анализирует конфигурацию вашей базы данных в поисках узких мест и неэффективностей. В выходных данных показаны метрики и рекомендации:

    В верхней части выходных данных вы можете увидеть версию инструмента MySQLTuner и вашей базы данных.

    Скрипт работает с MySQL 8. x. Рекомендации по файлу журнала стоят первыми в списке, но если вы прокрутите вниз, вы увидите общие рекомендации по улучшению производительности MySQL.

    Третья утилита, которая у вас уже может быть, это phpMyAdmin Advisor . Как и две другие утилиты, она оценивает вашу базу данных и рекомендует корректировки. Если вы уже используете phpMyAdmin, советник — это полезный инструмент, который вы можете использовать в графическом интерфейсе.

    Примечание: Ознакомьтесь со списком лучших инструментов для оптимизации SQL-запросов и воспользуйтесь нашим углубленным анализом каждого из них, чтобы найти лучший для ваших задач.

    5. Оптимизация запросов

    Запрос — это закодированный запрос на поиск в базе данных данных, соответствующих определенному значению. Есть некоторые операторы запросов, выполнение которых по самой своей природе занимает много времени. Методы настройки производительности SQL помогают оптимизировать запросы для лучшего времени выполнения.

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

    Примечание: Рассмотрите возможность изучения архитектуры хранилища данных, которая отделяет рабочие базы данных от аналитических.

    Например, база данных OLTP требует быстрых транзакций и эффективной обработки запросов. Выполнение неэффективного запроса блокирует использование базы данных и останавливает обновление информации.

    Если в вашей среде используются автоматические запросы, такие как триггеры, они могут влиять на производительность. Проверяйте и завершайте процессы MySQL, которые со временем могут накапливаться.

    6. Используйте индексы там, где это уместно

    Многие запросы к базе данных используют подобную структуру:

     SELECT … WHERE

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

    7. Функции в предикатах

    Избегайте использования функции в предикате запроса. Например:

     SELECT * FROM MYTABLE WHERE UPPER(COL1)='123'Copy

    Нотация UPPER создает функцию, которая должна работать во время операции SELECT . Это удваивает работу, выполняемую запросом, и вам следует избегать этого, если это возможно.

    8. Избегайте подстановочных знаков % в предикатах

    При поиске в текстовых данных подстановочные знаки помогают сделать более широкий поиск. Например, чтобы выбрать все имена, начинающиеся с ch , создайте индекс для столбца имени и выполните:

     SELECT * FROM person WHERE name LIKE "ch%"

    Запрос сканирует индексы, что снижает стоимость запроса:

    Однако выполнение поиска имен с использованием подстановочных знаков в начале значительно увеличивает стоимость запроса, поскольку сканирование с индексированием не применяется к концам строк:

    Подстановочный знак в начале поиска не применяется индексация. Вместо этого при полном сканировании таблицы выполняется поиск по каждой строке отдельно, что увеличивает стоимость запроса в процессе. В примере запроса использование подстановочного знака в конце помогает снизить стоимость запроса за счет обработки меньшего количества строк таблицы.

    Способ поиска концов строк состоит в том, чтобы перевернуть строку, проиндексировать перевернутые строки и посмотреть на начальные символы. Размещение подстановочного знака в конце теперь ищет начало перевернутой строки, что делает поиск более эффективным.

    9. Укажите столбцы в функции SELECT

    Обычно используемое выражение для аналитических и исследовательских запросов: SELECT * . Выбор большего количества ресурсов, чем вам нужно, приводит к ненужной потере производительности и избыточности. Если вы укажете нужные столбцы, вашему запросу не нужно будет сканировать нерелевантные столбцы.

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

    Подводя итог, избегайте использования:

     SELECT * FROM table

    Вместо этого попробуйте:

     SELECT column1, column2 FROM table

    10. Используйте ORDER BY Соответственно

    9 0152 ORDER BY выражение сортирует результаты по указанный столбец. Его можно использовать для сортировки сразу по двум столбцам. Они должны быть отсортированы в том же порядке, по возрастанию или убыванию.

    Если вы попытаетесь отсортировать разные столбцы в другом порядке, это снизит производительность. Вы можете комбинировать это с индексом, чтобы ускорить сортировку.

    11. GROUP BY вместо SELECT DISTINCT

    Запрос SELECT DISTINCT пригодится при попытке избавиться от повторяющихся значений. Однако оператор требует большой вычислительной мощности.

    По возможности избегайте использования SELECT DISTINCT , так как это очень неэффективно и иногда приводит к путанице. Например, если таблица содержит информацию о клиентах со следующей структурой:

    id имя фамилия адрес город штат zip
    0 Джон Смит 652 Цветочная улица Лос-Анджелес CA
  • 1 Джон Смит 1215 Ocean Boulevard 9024 0 Лос-Анджелес Калифорния

    2 Martha Matthews 3104 Pico Boulevard Los Angeles CA
    3 Martha Jones 2712 Venice Boulevard Los Angeles CA

    Выполнение следующего запроса возвращает четыре результата:

     SELECT DISTINCT name, address FROM person

    Похоже, что оператор должен возвращать список различных имен вместе с их адресами. Вместо этого запрос просматривает и столбец имени и адреса. Хотя есть две пары клиентов с одинаковым именем, их адреса разные.

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

     SELECT name, address FROM person GROUP BY name

    Результат возвращает первое уникальное имя вместе с адресом, что делает утверждение менее двусмысленное. Для группировки по уникальным адресам 9Параметр 0007 GROUP BY просто изменится на адрес и быстрее вернет тот же результат, что и оператор DISTINCT .

    Подводя итог, избегайте использования:

     SELECT DISTINCT column1, column2 FROM table

    Вместо этого попробуйте использовать:

     SELECT column1, column2 FROM table GROUP BY column1

    12. JOIN, WHERE, UNION, DISTINCT

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

    Использование INNER JOIN — это стандартный подход к объединению таблиц. Большинство механизмов баз данных также допускают использование WHERE . Например, следующие два запроса выдают один и тот же результат:

     SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

    По сравнению с:

     SELECT * FROM table1, table2 WHERE table1.id = table2.id

    Теоретически у них одинаковое время работы.

    Выбор использования ПРИСОЕДИНЯЙТЕСЬ или ГДЕ запрос зависит от ядра базы данных. Хотя большинство механизмов имеют одинаковое время выполнения для двух методов, в некоторых системах баз данных один работает быстрее, чем другой.

    Примечание: Узнайте больше о MySQL JOINS и о том, как их использовать.

    Команды UNION и DISTINCT иногда включаются в запросы. Как и внешнее соединение, эти выражения можно использовать, если они необходимы. Однако они добавляют дополнительную сортировку и чтение базы данных. Если они вам не нужны, лучше найти более эффективное выражение.

    13. Используйте функцию EXPLAIN

    Современные базы данных MySQL включают функцию EXPLAIN .

    Добавление выражения EXPLAIN в начало запроса приведет к чтению и оценке запроса. Если есть неэффективные выражения или запутанные структуры, EXPLAIN поможет вам найти их. Затем вы можете изменить формулировку вашего запроса, чтобы избежать непреднамеренного сканирования таблицы или других ударов по производительности.

    14. Конфигурация сервера MySQL

    Эта конфигурация включает в себя внесение изменений в файл /etc/mysql/my.cnf . Действуйте с осторожностью и вносите незначительные изменения за один раз.

    query_cache_size — указывает размер кэша запросов MySQL, ожидающих выполнения. Рекомендуется начинать с небольших значений около 10 МБ, а затем увеличивать их до 100–200 МБ. При слишком большом количестве кешированных запросов вы можете столкнуться с каскадом запросов «Ожидание блокировки кеша». Если ваши запросы продолжают выполнять резервное копирование, лучше использовать EXPLAIN для оценки каждого запроса и поиска способов сделать их более эффективными.

    max_connection — Относится к количеству разрешенных подключений к базе данных. Если вы получаете сообщения об ошибках со ссылкой на « Слишком много подключений, », увеличение этого значения может помочь.

    innodb_buffer_pool_size — этот параметр выделяет системную память в качестве кэша данных для вашей базы данных. Если у вас есть большие блоки данных, увеличьте это значение. Обратите внимание на объем оперативной памяти, необходимый для запуска других системных ресурсов.

    innodb_io_capacity — эта переменная устанавливает скорость ввода/вывода с вашего устройства хранения. Это напрямую связано с типом и скоростью вашего накопителя. Жесткий диск со скоростью вращения 5400 об/мин будет иметь гораздо меньшую емкость, чем высокопроизводительный твердотельный накопитель или Intel Optane. Вы можете изменить это значение, чтобы оно лучше соответствовало вашему оборудованию.

    Заключение

    Теперь вы должны знать, как повысить производительность MySQL и настроить базу данных.

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

    Оптимизация таблиц MySQL помогает переупорядочить информацию на выделенном сервере хранения для повышения скорости ввода и вывода данных. Ознакомьтесь с нашим руководством по оптимизации таблиц MySQL.

    Советы по настройке производительности MySQL для оптимизации базы данных

    Follow @Cloudways

    Язык структурированных запросов (SQL) — это мощный инструмент для управления данными в реляционных базах данных, включая такие популярные системы, как MySQL, Postgres, Oracle, SQL Server и другие.

    Используя операторы SQL, разработчики могут легко выполнять различные функциональные операции с базами данных, такие как создание, обновление и удаление данных.

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

    В этом блоге мы рассмотрим инструменты настройки производительности MySQL, которые могут помочь специалистам по базам данных выявить узкие места, просмотреть планы выполнения запросов и устранить догадки. Приготовьтесь раскрыть весь потенциал вашей базы данных MySQL!

    Содержание

    1. Рекомендации по настройке производительности MySQL
    2. Преимущества настройки производительности MySQL
    3. Советы по настройке производительности сервера MySQL для эффективной оптимизации
    4. Меры безопасности для поддержания оптимальной производительности MySQL
    5. Конфигурация сервера и оборудование в настройке производительности MySQL
    6. Настройка производительности MySQL: кэш запросов в MySQL
    7. Средства мониторинга для анализа производительности MySQL
    8. Как Cloudways повышает производительность MySQL с помощью управляемых сервисов баз данных
    9. Подведение итогов!

    Откройте для себя решение для баз данных следующего уровня

    MariaDB превосходит MySQL по невероятной скорости, непревзойденной масштабируемости и набору дополнительных функций.

    Узнать больше

    Рекомендации по настройке производительности MySQL

    Оптимизация производительности базы данных и выполнения запросов MySQL имеет решающее значение для максимизации производительности системы. Однако настройка SQL может быть сложной и трудоемкой для разработчиков, требующей технических знаний для понимания и написания различных планов выполнения.

    Из-за проблем, связанных с неопределенностью подходов к настройке уникальных операторов SQL, в современных средах, управляемых данными, очень важно освоить настройку производительности базы данных.

    Преимущества настройки производительности MySQL

    Вот преимущества настройки производительности MySQL:

    • Оптимизация затрат: Выявление фактора, влияющего на производительность, позволяет избежать избыточного выделения ресурсов и снизить затраты за счет правильного выбора размера серверов.
    • Улучшенная производительность: Настройка базы данных для оптимизации производительности запросов MySQL приводит к значительным улучшениям производительности и расширенным функциональным возможностям.
    • Расширенный поиск данных: Оптимизированная база данных MySQL позволяет быстрее извлекать данные, уменьшая задержку и улучшая общее взаимодействие с пользователем.
    • Снижение нагрузки на задачи: Правильно настроенная база данных снижает нагрузку на ненужные задачи, оптимизируя системные ресурсы и повышая эффективность.
    • Оптимизация хранения данных: Информация, полученная в результате настройки производительности, может помочь определить, повысит ли производительность перемещение хранилища данных или добавление емкости сервера и насколько.
    • Повышенная масштабируемость: Настройка производительности обеспечивает лучшую масштабируемость, позволяя базе данных справляться с растущими нагрузками данных без ущерба для производительности.
    • Более высокая окупаемость инвестиций: Улучшенная производительность базы данных и оптимизированное использование ресурсов обеспечивают более высокую окупаемость инвестиций (ROI) за счет максимальной эффективности системы и снижения эксплуатационных расходов.

    Вам также может понравиться: Советы по производительности PHP для оптимизации ваших веб-сайтов

    Советы по настройке производительности сервера MySQL для эффективной оптимизации

    Оптимизация производительности MySQL имеет решающее значение для обеспечения эффективной и действенной работы базы данных, что влияет на бизнес-операции.

    В этом разделе рассматриваются некоторые методы настройки производительности MySQL, которые можно использовать для повышения скорости и эффективности запросов MySQL, снижения риска сбоев базы данных и оптимизации производительности базы данных.

    Оптимизация производительности MySQL с помощью стратегий индексирования

    Источник: devart

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

    Однако чрезмерное индексирование может отрицательно сказаться на производительности базы данных, замедляя запись и увеличивая использование дискового пространства. Поэтому важно создавать индексы стратегически на основе требований приложения, чтобы избежать чрезмерного индексирования и поддерживать оптимальную производительность.

    Избегайте использования функций в предикатах

    База данных не использует индекс, если в столбце есть предопределенная функция.

    Например:

     SELECT * FROM TABLE1 WHERE UPPER(COL1)='ABC'Copy

    Из-за функции UPPER() база данных не использует индекс COL1. Если нет никакого способа избежать этой функции в SQL, вы должны создать новый индекс на основе функции или создать настраиваемые столбцы в базе данных для повышения производительности.

    Избегайте использования подстановочного знака (%) в начале предиката

    Предикат LIKE ‘%abc’ вызывает полное сканирование таблицы. Например:

     SELECT * FROM TABLE1 WHERE COL1 LIKE '%ABC'Copy

    В большинстве случаев использование подстановочных знаков приводит к серьезным ограничениям производительности.

    Избегайте ненужных столбцов в предложении Select

    Вместо использования «SELECT *» всегда указывайте столбцы в предложении SELECT для повышения производительности MySQL. Поскольку ненужные столбцы создают дополнительную нагрузку на базу данных, замедляя ее работу, а также весь системный процесс.

    Использовать внутреннее соединение вместо внешнего

    Источник: devart

    Внешнее соединение использовать только при необходимости. Его использование без необходимости не только ограничивает производительность базы данных, но и ограничивает параметры оптимизации запросов MySQL, что приводит к более медленному выполнению операторов SQL.

    Используйте DISTINCT и UNION только в случае необходимости

    Использование операторов UNION и DISTINCT без какой-либо важной цели приводит к нежелательной сортировке и замедлению выполнения SQL. Вместо UNION использование UNION ALL повышает эффективность процесса и более точно повышает производительность MySQL.

    Где использовать «ORDER BY»

    Ключевое слово ORDER BY сортирует результирующий набор в предопределенных столбцах операторов. Хотя оператор дает преимущество администраторам базы данных для получения отсортированных данных, он также оказывает некоторое влияние на производительность при выполнении SQL. Поскольку запрос сначала должен отсортировать данные для создания окончательного набора результатов, что вызывает некоторую сложную операцию при выполнении SQL.

    Вам также может понравиться: Как соединить две таблицы в MySQL

    Оптимизация подзапросов MySQL

    Источник: alibabacloud

    Самый важный совет, который я могу дать вам по поводу подзапросов, заключается в том, что вы должны предпочесть соединение там, где это возможно, по крайней мере, в текущих версиях MySQL.

    Подзапросы являются предметом интенсивной работы команды оптимизаторов, и в будущих версиях MySQL может быть больше оптимизаций подзапросов.

    Проверьте, какие из оптимизаций попадут в выпущенный код и какую разницу они дадут. Я хочу сказать, что «предпочитаю объединение» не является советом, рассчитанным на будущее. Сервер становится умнее, и случаев, когда вы должны сказать ему, как что-то делать, а не какие результаты возвращать, становится все меньше.

    Оптимизация запросов

    Очереди могут влиять на производительность вашей базы данных прямо из ядра и могут проникать в базы данных вашего приложения без вашего ведома. Например, если вы устанавливаете статус для определенного элемента, чтобы «соответствующий процесс» мог получить к нему доступ, вы непреднамеренно создаете очередь. Что он делает, так это то, что он создает дополнительное время загрузки для доступа к ресурсу без какой-либо серьезной причины.

    Источник: devart

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

    Меры безопасности для поддержания оптимальной производительности MySQL

    Одним из важнейших аспектов поддержания оптимальной производительности MySQL является реализация строгих мер безопасности.

    Поскольку объем данных, которые организациям необходимо хранить и управлять, продолжает расти, обеспечение оптимальной производительности баз данных MySQL становится все более важным.

    Чтобы обеспечить оптимальную производительность MySQL, важно реализовать такие меры безопасности, как;

    Защита от несанкционированного доступа

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

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

    Предотвращение атак с внедрением SQL

    Атаки с внедрением SQL — это распространенные кибератаки, при которых вредоносные операторы SQL внедряются в запросы MySQL для получения несанкционированного доступа. Эти атаки могут вызвать проблемы с производительностью базы данных MySQL, сбои или даже полный сбой.

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

    Обеспечение целостности данных

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

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

    Требования соответствия

    Во многих отраслях существуют требования к безопасности данных и конфиденциальности, такие как Стандарт безопасности данных индустрии платежных карт (PCI DSS) и Общий регламент по защите данных (GDPR).

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

    Конфигурация сервера и оборудование в настройке производительности MySQL

    Для работы с базой данных необходимы четыре основных ресурса. ЦП, диск, память и сеть. Если какой-либо из них не работает должным образом, это в конечном итоге влияет на сервер базы данных и приводит к снижению производительности.

    Чтобы правильно понять основные ресурсы, вам нужно сосредоточиться на двух конкретных областях, а именно на выборе правильного оборудования и устранении неполадок с ним.

    • ЦП : ЦП выполняет запросы и обрабатывает данные в базе данных. Чтобы обеспечить оптимальную производительность MySQL, важно выбрать процессор с высокой тактовой частотой и несколькими ядрами.
    • RAM : Базы данных MySQL сильно зависят от оперативной памяти для кэширования данных и выполнения операций в памяти. Чтобы повысить производительность MySQL, важно убедиться, что на сервере достаточно оперативной памяти для размещения базы данных и рабочей нагрузки.
    • Хранилище : Тип хранилища, используемого для базы данных MySQL, также может существенно повлиять на производительность. Твердотельные накопители (SSD), как правило, быстрее традиционных жестких дисков (HDD) и могут повысить производительность баз данных MySQL.
    • Сеть : Скорость и надежность сетевого соединения между сервером MySQL и клиентами также могут влиять на производительность.
    • Конфигурация сервера : Наконец, конфигурация сервера также может значительно повлиять на производительность MySQL. Настройка MySQL для использования соответствующих размеров буферов, пулов потоков и других параметров может повысить производительность и уменьшить потребность в доступе к диску.

     

    Будь то бенчмаркинг PHP или устранение неполадок, всегда контролируйте производительность всех основных ресурсов. Качественно подтвердите, что они работают в соответствии с требованиями улучшения норм. Регулярное рассмотрение этого аудита позволит быстро решить основные возникающие проблемы.

    Запросы на разбиение на страницы

    Приложения с разбиением на страницы могут нагружать сервер, поскольку они часто группируют и сортируют данные способами, которые не используют индексы, а также используют LIMIT и смещение, которые создают и отбрасывают строки, вызывая ненужную работу для сервера.

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

    На стороне запроса вместо использования LIMIT со смещением выбор одной дополнительной строки и назначение ее в качестве начальной точки для следующего набора результатов может быть более эффективным. Например, если пользователь просмотрел строки с 101 по 120, запрос строк больше или равен 121 с ограничением 21 может использоваться для отображения следующей страницы.

    Настройка производительности MySQL: кэширование запросов в MySQL

    Другим важным аспектом измерения производительности является кэширование содержимого. MySQL обеспечивает кэширование запросов к базе данных, которое кэширует текст оператора SELECT и полученный результат.

    Следовательно, всякий раз, когда вы создаете дубликат базы данных, вы вызываете кеш запросов MySQL, он ответит вам и покажет результат из кеша, и ни один вызов не будет повторно анализироваться. Таким образом, вы можете максимизировать процесс оптимизации кэша MySQL.

    Чтобы настроить кеш запросов MySQL, вы должны добавить несколько настроек в MySQL. Прежде всего, вы должны проверить, доступен ли кэш запросов, с помощью следующей команды:

     mysql> SHOW VARIABLES LIKE 'have_query_cache';

    Это покажет результат YES. Это означает, что кеш MySQL работает нормально.

     +------------------+--------+


| Имя_переменной | Значение |


+------------------+--------+


| have_query_cache | ДА |


+------------------+-------+

     

    Теперь вы можете настроить размер и тип кэша запросов MySQL. Помните, что минимальный размер по умолчанию — 40 КБ. Максимальный размер может составлять 32 МБ. Вы можете установить MySQL query_cache_size с помощью следующей команды:

     mysql> SET GLOBAL query_cache_size = 40000;

    Тип кэша запросов может определять поведение всех подключений. Вы также можете отключить кэш запросов для таких запросов, как:

     mysql> SET SESSION query_cache_type = OFF;

    Вы также можете установить такие значения, как 0,1 и 2, для настройки состояния соединения.

    Использование Memcached для кэширования MySQL

    Memcached — это популярная система кэширования с открытым исходным кодом, которую можно использовать для кэширования результатов

    запросов или других данных в памяти. MySQL можно настроить для использования Memcached в качестве плагина, позволяющего кэшировать и быстро извлекать данные.

    Memcached сохраняет значения (v) с ключом (k) и извлекает значения (v) с ключом (k), даже не анализируя запросы к базе данных, и избегает всех этих неприятностей.

    Чтобы узнать больше о Memcached, вы можете прочитать руководство по настройке Memcache в PHP.

    Кэширование на уровне приложения

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

    Кэширование на уровне приложения может быть особенно полезно для приложений, которые часто извлекают одни и те же данные или выполняют сложные вычисления с данными, которые изменяются лишь время от времени. Это происходит, когда приложению необходимо получить данные из базы данных. Сначала он может проверить, кэшированы ли данные уже в памяти.

    Сеть доставки контента

    CDN обычно используются для кэширования статических ресурсов, таких как изображения, видео и файлы JavaScript, для повышения скорости и производительности веб-сайтов и приложений. Когда пользователь запрашивает контент с веб-сайта или приложения, ближайший к пользователю сервер CDN обслуживает контент.

    За счет кэширования содержимого в CDN можно снизить нагрузку на базу данных, что приведет к сокращению времени отклика и улучшению масштабируемости.

    Например, веб-сайт, на котором отображаются изображения продуктов, может хранить эти изображения в CDN, которая будет кэшировать изображения и быстро предоставлять их пользователям, снижая нагрузку на базу данных и повышая производительность веб-сайта.

    Инструменты мониторинга для анализа производительности MySQL

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

    MySQLTuner

    Источник: MySQLTuner

    MySQLTuner — это сценарий Perl, который помогает администраторам баз данных MySQL анализировать и оптимизировать производительность своих баз данных MySQL. MySQLTuner выполняет всесторонний анализ конфигурации сервера MySQL и предоставляет рекомендации по оптимизации определенных параметров конфигурации базы данных, таких как кэширование запросов, обработка потоков и размеры буферов.

    Однако важно отметить, что рекомендации, предоставленные MySQLTuner, должны быть тщательно рассмотрены и протестированы перед их внедрением в производственную среду.

    VividCortex

    Источник: SolarWinds

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

    VividCortex обеспечивает мониторинг нагрузки сервера, времени ответа на запросы и работоспособности базы данных в режиме реального времени, позволяя администраторам баз данных быстро выявлять проблемы с производительностью и устранять их до того, как они повлияют на приложение.

    MySQL Workbench

    Источник: MySQL Workbench

    MySQL Workbench — это IDE для управления и разработки баз данных MySQL. MySQL Workbench предоставляет инструменты для создания, визуализации и изменения схемы базы данных. Он предлагает мощные функции для управления пользователями, разрешениями и настройками безопасности, а также для мониторинга производительности и работоспособности сервера.

    MySQL Workbench включает в себя мощный редактор SQL с подсветкой синтаксиса, завершением кода и функциями отладки. Он также включает инструменты для анализа SQL-запросов, оптимизации производительности запросов и экспорта результатов запросов.

    Как Cloudways повышает производительность MySQL с помощью управляемых служб баз данных

    Cloudways использует MariaDB в качестве обратно совместимой улучшенной версии MySQL и предоставляет последние версии MariaDB на всех своих недавно запущенных серверах.

    Вот как вы можете выбрать нужную версию MariaDB:

    • Перейдите на вкладку «Настройки и пакеты» .
    • Нажмите раскрывающееся меню.
    • Выберите желаемую версию MariaDB одним щелчком мыши.

    Cloudways предлагает услуги управляемых баз данных, предоставляя предприятиям специальную группу экспертов для безопасного и оптимизированного управления базами данных. Его команда экспертов точно настраивает конфигурации баз данных для повышения производительности и оптимизирует запросы для сокращения времени отклика.

    Благодаря автомасштабированию предприятия могут регулировать ресурсы базы данных в зависимости от рабочей нагрузки, повышая производительность и экономя средства за счет использования только необходимых ресурсов.

    Сервисы управляемых баз данных Cloudways повышают производительность за счет различных функций, позволяя предприятиям сосредоточиться на основных задачах, в то время как команда Cloudways профессионально управляет своей базой данных.

    Размещайте веб-сайты PHP с легкостью [Начиная с $11 кредита]

    • Бесплатное размещение
    • Бесплатная резервная копия
    • PHP 8.0
    • Неограниченное количество сайтов

    ПОПРОБУЙТЕ СЕЙЧАС

    Подведение итогов!

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

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

    Если вы хотите добавить свои мысли по теме или задать несколько вопросов по ней, не стесняйтесь писать свои комментарии в разделе комментариев.

    В: Какова производительность MySQL?

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

    В: Как повысить производительность MySQL?

    Существует несколько способов повысить производительность MySQL. Вот несколько советов:

    • Оптимизируйте свои запросы
    • Индексируйте свои таблицы
    • Оптимизируйте схему базы данных
    • Оптимизируйте конфигурацию вашего сервера
    • Обновите свое оборудование
    • Использовать кэширование
    • Использовать CDN

    В: Быстро ли работает база данных MySQL?

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

    Поделитесь своим мнением в комментариях. КОММЕНТАРИЙ СЕЙЧАС

    Поделиться этой статьей

    Иншал Али

    Иншал работает контент-маркетологом в Cloudways. Обладая знаниями в области компьютерных наук, навыками работы с контентом и большим творческим потенциалом, он помогает бизнесу достичь неба и выйти за его пределы с помощью контента, который говорит на языке их клиентов. Помимо работы, вы будете видеть его в основном в каких-то онлайн-играх или на футбольном поле.

    No related posts.