Mysql снизить нагрузку на процессор

Обновлено: 04.07.2024

Конфигурация mariaDB (10-я)

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
join_buffer_size = 64K
key_buffer_size = 1M
max_allowed_packet = 1M
table_open_cache = 1000
sort_buffer_size = 64K
read_buffer_size = 64K
read_rnd_buffer_size = 64K
query_cache_size = 10M
query_cache_limit = 10M
net_buffer_length = 2K
thread_stack = 64K
thread_cache_size = 5
max_connections = 20
max_user_connections = 10
skip-innodb
skip-bdb
skip-networking
default-storage-engine=myisam
query-cache-type=0
performance_schema = 0
server-id = 1

[myisamchk]
key_buffer_size = 1M
sort_buffer_size = 1M

С веб-сервером все нормально, nginx работает исправно.

Может быть PHP-FPM настроен как то неправильно, т.к. идет запрос на файл PHP и после через него уже к MYSQL.
Пробовал во время CPU 100%, перегрузить сервис PHP-FPM, буквально на несколько секунд спадает процессор и снова подымается, помогает только перезапуск MySQL.

Я глубоко извиняюсь, я плохо знаком с настройкой MySQL, возможно она выглядит у меня не очень вменяемо )
Использую MyISAM, в mysqltuner.pl показано отключить innoDB я вот отключил, и key_buffer_size тоже показало увеличить.

Вот текущий mysqltunner:

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED -InnoDB +MRG_MyISAM
[--] Data in MyISAM tables: 4M (Tables: 37)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[OK] Total fragmented tables: 0

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 10s (9 q [0.900 qps], 8 conn, TX: 12K, RX: 1K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 27.0M global + 384.0K per thread (20 max threads)
[OK] Maximum possible memory usage: 34.5M (14% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 5% (1/20)
[!!] Key buffer size / total MyISAM indexes: 1.0M/1.1M
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 87% (1 created / 8 connections)
[OK] Table cache hit rate: 100% (62 open / 0 opened)
[OK] Open file limit used: 1% (24/2K)
[OK] Table locks acquired immediately: 100% (72 immediate / 72 locks)
[!!] Connections aborted: 25%

Если смотреть на картинку, криминального ничего не вижу на ней. Одно ядро выделено на машину? Если это так, и оно загружено чуть более чем наполовину (если верить LA) за последнюю минуту - то беспокоиться тут вообще не о чем. Что по мне, так лучше при возможности выделить машине ресурсов с некоторым запасом. Больше чем сможет откусить, всё равно не съест, зато при решения авральных задач в ступор не впадает сразу.

У меня в общем сейчас такая ситуация, в БД 27 таблиц, примерно в 40% из них содержится около 1000-10000 записей (строка с 2 значениями, IP и DATA), всего около 100 000 записей в этой БД.

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

Потому при проблемах СУБД с индексами приходится сталкиваться практически всегда - и при первоначальном осмотре, и при разного рода восстановлении, и при оптимизации запросов и таблиц.

Предложение увеличить размер буфера ключей - было. Сделали? Понаблюдали за результатом? Что изменилось?

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

Если включите sloq query log, то сможете наловить еще запросов, которые можно оптимизировать.

miksoft
И мой вопрос насчет роста интенсивности внешних запросов вы пропустили.

Не увидел, какой вопрос был?

[quot vkle]Предложение увеличить размер буфера ключей - было. Сделали? Понаблюдали за результатом? Что изменилось?/quot] Сервер был выключен, сейчас включил, после отдыха CPU на 100% не сразу подымается, через некоторое время как проработает.

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

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

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

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

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

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

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

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:

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

Буквально за несколько минут скрипт выдаст полную статистику по работе 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 при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных - его надежнее отключить.

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

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

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

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

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

systemctl stop mariadb

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

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

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

systemctl start mariadb

systemctl status mariadb

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

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

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

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

Выводы

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

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

С ростом посещаемости сайта увеличивается нагрузка на CPU и MySQL. И примерно через неделю Timeweb начинает настойчиво просить дать им больше денег перейти на другой тариф. Что ж, их можно понять: сайт потребляет больше ресурсов, чем заложено по тарифу. Но прежде чем доплачивать, давайте попробуем избавиться от нагрузки.

В интернете есть множество статей с десятками пунктов, как уменьшить нагрузку. Что там только не пишут: и lazyload (Google PageSpeed рекомендует), и gzip-сжатие, и написание директив в robots.txt, и выставление кэша в .htaccess для статических файлов (хотя на дворе 2019 год и 95% хостеров делают это за вас), и многие другие мелочи.

Нет, сделать lazyload - это супер, и обработку картинок на стороне сервера тоже круто, и обязательно собрать все стили в один CSS-файл, и скрипты в футер + async. Но в плане уменьшения нагрузки это не поможет. Действительно рабочих методов лишь несколько.

1. Включить кэширование.

Для Joomla я использую компонент Jotcache. Каждый сам для себя решает, какое время кэширования ставить. Так как у меня новостной сайт, я ставлю 5 минут. Но если скорость добавления и обновления информации не играет ощутимой роли (например, для сайтов-статейников), можно ставить и 30-90 минут.

P.S. Стандартные встроенные компоненты тут не помогут. Кэширование должно сократить нагрузку в 2-3 раза.

2. Сократить количество запросов к БД.

Каждый включенный компонент, модуль, плагин или расширение - это дополнительные запросы к базе данных. Особенно, если это связано с любым контентом. Криво написанные модули могут делать десятки запросов к БД. Например, модуль "последних записей" может делать не 1 запрос к БД, а столько, сколько записей выводится.

Например, этот делает около 20 запросов:

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

3. Заблокировать чересчур активных ботов.

Нужно зайти в /ваш_сайт/access_log и посмотреть, кто там и что там. Если находим слишком активных ботов, от которых нет никакого толку, блокируем их.

Для этого заходим в .htaccess и дописываем такие строки:

PycURL, Aport, ia_archiver - это названия ботов. Каждая строка в списке заканчивается [OR], а последняя - просто названием бота.

Кстати, некоторые еще предлагают игнорировать ботов через robots.txt, но большинство обходит эти директивы.

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

У моего сайта сейчас не сезон, посещаемость - 200 человек в сутки. Но в access_log 14700 записей. Из них:

Semrush, Ahrefs, Megaindex, Linkpad, Moz и т.д. - просто парсят ваш сайт с целью узнать внешние ссылки и их направление. Кого блокировать, а кого оставлять - на ваш выбор.

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

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

По умолчанию конфигурация mysql подразумевает следующие значения параметров

[mysqld]
set-variable = max_connections=500
safe-show-databases

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

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

Дефолтные настройки во всех случаях нужно менять, без задаваемых непосредственно настроек сервер баз данных не сможет узнать как много оперативной памяти и ресурсов процессора используется. Работа серовера с 256Мб и 16Гб RAM сильно отличаются, для того чтобы обеспечить максимальную производительность MySQL нужна тонкая настройка параметров.

Переменные

MySQL CLI:

SSH (root)

mysqladmin variables или mysqladmin var

mysqladmin variables | grep name-of-variable

Параметры кэширования запросов, которые могут задаваться в my.cnf

thread_cache_size (default 0)

table_cache/tables_open_cache(default 64)

количество открытых таблиц для всех потоков

query_cache_limit (default 1M)

максимальный размер запроса который может быть помещен к кеш

query_cache_size (default 0)

поскольку здесь 0, следующий параметр работать не будет
4-8М хорошие значения, количество памяти для кэширования запросов

query_cache_type (default 1=on)

0=off, 2=on if needed

Буферы MySQL

key_buffer_size (default 8M)

буфер для индексов таблиц MYISAM, 24-48М нормальные значения

read_buffer_size (default 128K)

последовательный буфер потоков

join_buffer_size (default 128K)

Другие переменные

Переменные, использование которых не требуется

thread_concurrency
используется только на Solaris, на linux mysql 5.5+ при использовании переменной MySQL не сможет запуститься

innodb_thread_concurrency
похожая на предыдущую переменная, является, тем не менее, совсем другой и относится к innodb

Логирование MySQL

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

Есть три вида логов: логи ошибок, общие логи и логи медленных запросов.

Error-логи
По умолчанию включены

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

Логирование включается путем раскомментирования соответствующих директив к my.cnf

Включение ведения логов в MySQL 5.0:

Логи медленных запросов:

В большинстве случаев на файлы логов устанавливаются права 600 и владелец mysql:mysql

Включение ведения логов в MySQL 5.1.29+:

Логи медленных запросов:

Файлы логов при этом будут созданы автоматически

отображает только один процесс, индивидуально каждый процесс отслеживать возможности нет

предоставляет полную информацию о процессах MySQL

Образцы конфигурационных файлов MySQL

Найти их можно в каталоге /usr/share/mysql

my-huge.cnf
my-large.cnf
my.small.cnf
my-innodb-heavy-4G.cnf
my-medium.cnf

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

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

Инструменты диагностики MySQL

Общая оптимизация

mysqltuner.pl

Сервер баз данных должен работать минимум 24 часа до запуска данной утилиты

mysqlidxchk

chmod 755 mysqlidxchk* mysqlidxchk

Парсинг лог-файлов

mysqlsla

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

Использование (логирование должно быть включено)

./mysqlsla -lt general /var/lib/mysql/general.log

Формирование отчетов о статусе

mysqlreport

Утилита интерпретирует вывод SHOW STATUS и составляет на основании собранной статистики отчет о работе MySQL

Исопльзование
./mysqlreport

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

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

Читайте также: