Использование CPU MySQL 200%

На моем сервере MySQL использование CPU превышает 200%. Я запускаю Java war файл на сервере Tomcat, который использует Hibernate JPA для выполнения операций с MySQL. Я включил журналы медленных SQL-запросов, но ничего не превышает 2 секунд, однако количество выполняемых запросов высоко из-за Spring Boot JPA.

ОС:  Ubuntu 18.04.1 LTS

Аппаратные характеристики: 
память: 16GB 
Архитектура: x86_64 
Режим работы CPU: 32-битный, 64-битный 
Порядок байтов: Little Endian 
CPU(s): 4 
Список работающих CPU(s): 0-3 
Поток(и) на ядро: 1 
Ядро(ра) на сокет: 4 
Сокет(ы): 1 
NUMA узел(ы): 1 
ID производителя: GenuineIntel 
Семейство процессоров: 6 
Модель: 79 
Название модели: Intel(R) Xeon(R) CPU E5-2686 v4 @ 2.30GHz 
Частота CPU: 2300.033

Файл mysqld.cnf

key_buffer_size     = 128M  

max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
max_connections = 500
query_cache_limit = 8M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size=8G
innodb_buffer_pool_instances=8
innodb_lru_scan_depth=100

Когда я запускаю команду top в Ubuntu, он показывает, что MySQL иногда использует более 200% CPU.

> top

23:19:47 up 4:42, 1 user, load average: 0.86, 0.86, 0.83 Tasks: 167 total, 1 running, 116 sleeping, 0 stopped, 0 zombie %Cpu(s): 45.9 us, 4.8 sy, 0.0 ni, 47.5 id, 0.6 wa, 0.0 hi, 0.8 si, 0.4 st KiB Mem : 16424600 total, 4822956 free, 4830684 used, 6770960 buff/cache KiB Swap: 0 total, 0 free, 0 used. 11290580 avail Mem

PID   USER   PR  NI  VIRT     RES     SHR   S  %CPU   %MEM  TIME+     COMMAND
8675  mysql  20  0   3262732  856304  16244 S  162.8  5.2   74:18.57  mysqld

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

[--] Пропущена проверка версии для скрипта MySQLTuner
Пожалуйста, введите свой административный логин MySQL: perimetrix
Пожалуйста, введите свой административный пароль MySQL: [OK] В настоящее время работающая поддерживаемая версия MySQL 5.7.27-0ubuntu0.18.04.1
[OK] Работает на 64-битной архитектуре

-------- Рекомендации по журналам ------------------------------------------------------------------
[--] Журнал: /database/mysql/mypm-aws.err(0B)
[!!] Журнал /database/mysql/mypm-aws.err не существует
[!!] Журнал /database/mysql/mypm-aws.err недоступен для чтения.

-------- Статистика механизма хранения -----------------------------------------------------------------
[--] Статус: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Данные в таблицах InnoDB: 8.0G (Таблицы: 1130)
[OK] Всего фрагментированных таблиц: 0

-------- Метрики анализа производительности --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] Нет обновлений статистики во время запроса INFORMATION_SCHEMA.

-------- Рекомендации по безопасности ------------------------------------------------------------------
[OK] Нет анонимных аккаунтов для любых пользователей базы данных
[!!] Не удалось выполнить: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (IF(plugin='mysql_native_password', authentication_string, password) = '' OR IF(plugin='mysql_native_password', authentication_string, password) IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket', 'auth_pam_compat')
[!!] Сбой выполнения SQL / код возврата: 256
[OK] У всех пользователей базы данных назначены пароли
[--] Ошибка #80860 MySQL 5.7: Избежать тестирования пароля, если validate_password активирован

-------- Рекомендации по безопасности CVE --------------------------------------------------------------
[--] Пропущено из-за неопределенной опции --cvefile

-------- Метрики производительности -----------------------------------------------------------------------
[--] Включен: 1d 2h 21m 0s (103M q [1K qps], 10K conn, TX: 959G, RX: 285G)
[--] Чтения / Записи: 99% / 1%
[--] Двоичное ведение журнала отключено
[--] Физическая память     : 15.7G
[--] Максимальная память MySQL: 8.7G
[--] Память других процессов: 0B
[--] Всего буферов: 8.2G глобальных + 1.1M на поток (макс. 500 потоков)
[--] Максимальное использование памяти P_S: 72B
[--] Максимальное использование памяти Galera GCache: 0B
[OK] Максимальное достигнутое использование памяти: 8.4G (53.51% установленной оперативной памяти)
[OK] Максимально возможное использование памяти: 8.7G (55.48% установленной оперативной памяти)
[OK] Общее возможное использование памяти с другими процессами совместимо с доступной памятью
[OK] Медленные запросы: 0% (0/103M)
[OK] Наивысшее использование доступных соединений: 40% (202/500)
[OK] Прерванные соединения: 0.06% (7/10980)
[!!] Обратное разрешение имен активно: для каждого нового соединения выполняется обратное разрешение имен, что может снижать производительность
[!!] Кэш запросов может быть отключен по умолчанию из-за конкуренции за мьютекс.
[!!] Эффективность кэша запросов: 0.0% (0 кэшировано / 101M выборок)
[OK] Удаления кэша запросов в день: 0
[OK] Сортировки, требующие временных таблиц: 0% (0 временных сортировок / 187K сортировок)
[!!] Соединения, выполняемые без индексов: 53751600
[OK] Временные таблицы, создаваемые на диске: 0% (108 на диске / 70K всего)
[OK] Коэффициент попаданий кэша потоков: 98% (202 создано / 10K соединений)
[!!] Коэффициент попаданий кэша таблиц: 2% (2K открыто / 83K открыто)
[OK] Лимит открытых файлов использован: 0% (0/5K)
[OK] Блокировки таблиц, полученные немедленно: 100% (237 немедленно / 237 блокировок)

-------- Схема производительности ------------------------------------------------------------------------
[--] Память, используемая P_S: 72B
[--] Схема Sys установлена.

-------- Метрики пула потоков ------------------------------------------------------------------------
[--] Статистика пула потоков отключена.

-------- Метрики MyISAM ----------------------------------------------------------------------------
[!!] Используемый буфер ключей: 18.2% (24M использовано / 134M кэш)
[OK] Размер буфера ключей / всего индексов MyISAM: 128.0M/43.0K
[OK] Коэффициент попаданий буфера ключей при чтении: 96.5% (258 кэшировано / 9 чтений)

-------- Метрики InnoDB ----------------------------------------------------------------------------
[--] InnoDB включен.
[--] Конкуренция потоков InnoDB: 0
[OK] InnoDB Datei для таблиц активирован
[!!] Буферный пул InnoDB / размер данных: 8.0G/8.0G
[!!] Соотношение размера файла журнала InnoDB / размера буфера пула InnoDB (1.171875 %): 48.0M * 2/8.0G должно быть равно 25%
[OK] Инстансы буфера пула InnoDB: 8
[--] Количество фрагментов буфера пула InnoDB: 64 для 8 инстансов пула буфера
[OK] Размер InnoDB_buffer_pool совпадает с размером InnoDB_buffer_pool_chunk и InnoDB_buffer_pool_instances
[OK] Эффективность буфера чтения InnoDB: 100.00% (13946932499 hits/ 13947178305 total)
[!!] Эффективность записи журнала InnoDB: 30.94% (117771 hits/ 380652 total)
[OK] Ожидания журнала InnoDB: 0.00% (0 ожиданий / 262881 записей)

-------- Метрики AriaDB ----------------------------------------------------------------------------
[--] AriaDB отключен.

-------- Метрики TokuDB ----------------------------------------------------------------------------
[--] TokuDB отключен.

-------- Метрики XtraDB ----------------------------------------------------------------------------
[--] XtraDB отключен.

-------- Метрики Galera ----------------------------------------------------------------------------
[--] Galera отключен.

-------- Метрики репликации -----------------------------------------------------------------------
[--] Синхронная репликация Galera: НЕТ
[--] Нет репликационного слейва для этого сервера.
[--] Формат бинарного журнала: ROW
[--] Поддержка XA включена: ВКЛ
[--] Полусинхронная репликация Master: Не активирована
[--] Полусинхронная репликация Slave: Не активирована
[--] Это автономный сервер

-------- Рекомендации ---------------------------------------------------------------------------
Общие рекомендации:
    Настройте свои аккаунты только с IP или подсетями, затем обновите свою конфигурацию с skip-name-resolve=1
    Подкорректируйте ваши запросы JOIN, чтобы всегда использовать индексы
    Постепенно увеличивайте table_open_cache, чтобы избежать пределов дескрипторов файлов
    Прочтите это перед увеличением table_open_cache более 64:
    Прочтите это перед увеличением для MariaDB: https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    Это проблема масштабируемости только для кэша таблиц MyISAM, InnoDB не затронут.
    Смотрите больше деталей здесь: https://bugs.mysql.com/bug.php?id=49177
    Эта ошибка уже исправлена в MySQL 5.7.9 и более новых версиях MySQL.
    Обратите внимание, что переменная open_files_limit (5000) должна быть больше, чем table_open_cache (2000).
    Перед изменением innodb_log_file_size и/или innodb_log_files_in_group прочтите это:
Переменные для настройки:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 8M, или используйте меньшие наборы результатов)
    join_buffer_size (> 256.0K, или всегда используйте индексы с JOIN)
    table_open_cache (> 2000)
    innodb_buffer_pool_size (>= 8.0G) если возможно.
    Размер файла журнала InnoDB должен быть (=1G), если возможно, так что общий размер файлов журнала InnoDB составляет 25% от размера буфера пула.

Я не могу найти, что вызывает проблему.

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

Я не могу найти, что вызывает проблему

mysqltuner.pl уже предоставил вам список проблем.

Обратное разрешение DNS – это ошибка, и ваш буферный пул innodb крошечный (но размер пула не способствует вашему высокому CPU). Hibernate и производительность никогда не идут в одном предложении (если только не сопроводить “плохим”) – но, вероятно, все еще есть возможность улучшения, связанная с управлением пулом JDBC. Но вы не сказали нам, как это настроено.

Я подозреваю, что в БД не хватает многих необходимых индексов. Лучший способ получить данные для этого – установить порог medlennih_zaprosov на 0 – но это создаст много операций ввода-вывода на диске (поэтому лучше сделать это временно), а затем взглянуть на то, на каких запросах ваша СУБД тратит большую часть своего времени. Используйте mysqldumpslow для обработки журналов.

Рекомендации для вашей группы параметров AWS (раздел my.cnf [mysqld])

innodb_buffer_pool_size=8G  # с 128M, чтобы использовать половину вашей ОЗУ для хранения данных/индексов
innodb_buffer_pool_instances=8  # с 1, чтобы снизить конфликт мьютексов
innodb_lru_scan_depth=100  # с 1024, чтобы сохранить 90% ЦП циклов, используемых для функции

Отказ от ответственности: Я автор контента веб-сайта, упомянутого в моем профиле, профиля сети, где у нас есть БЕСПЛАТНЫЕ утилиты и сценарии, доступные для загрузки для повышения производительности и других услуг.