Регистрация Войти
Рейтинг:2
Mahmoud Moussa avatar Server

MariaDB сходит с ума на 500% процессорного времени

флаг br
Mahmoud Moussa

ГЛОБАЛЬНЫЙ СТАТУС

MariaDB [(нет)]> ПОКАЗАТЬ ГЛОБАЛЬНЫЙ СТАТУС;
+--------------------------------------------- ----------------------------+------------------------------------ --------------+
| Имя_переменной | Значение |
+--------------------------------------------- ----------------------------+------------------------------------ --------------+
| Прерванные_клиенты | 10 |
| Прерванные_соединения | 17 |
| Доступ_отказано_ошибки | 2808 |
| Acl_column_grants | 0 |
| Acl_database_grants | 255 |
| Acl_function_grants | 0 |
| Acl_procedure_grants | 0 |
| Acl_proxy_users | 1 |
| Acl_role_grants | 0 |
| Acl_роли | 0 |
| Acl_table_grants | 2 |
| Acl_users | 253 |
| Aria_pagecache_blocks_not_flushed | 0 |
| Aria_pagecache_blocks_unused | 15706 |
| Aria_pagecache_blocks_used | 40 |
| Aria_pagecache_read_requests | 287044 |
| Aria_pagecache_reads | 20253 |
| Aria_pagecache_write_requests | 36614 |
| Ария_pagecache_writes | 36593 |
| Ария_транзакция_log_syncs | 1 |
| Binlog_commits | 0 |
| Binlog_group_commits | 0 |
| Binlog_group_commit_trigger_count | 0 |
| Binlog_group_commit_trigger_lock_wait | 0 |
| Binlog_group_commit_trigger_timeout | 0 |
| Binlog_snapshot_file | |
| Binlog_snapshot_position | 0 |
| Binlog_bytes_writing | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| занятое_время | 0,000000 |
| байт_получено | 243371501 |
| байт_отправлено | 2355355672 |
| Com_admin_commands | 2293 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_assign_to_keycache | 0 |
| Com_begin | 0 |
| COM_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 92 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_compound_sql | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_role | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_temporary_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 8354 |
| Com_delete_multi | 0 |
| Ком_до | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_role | 0 |
| Com_drop_сервер | 0 |
| Com_drop_table | 0 |
| Com_drop_temporary_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_immediate | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_get_diagnostics | 0 |
| Ком_грант | 0 |

488 рядов в наборе (0,00 сек)

MariaDB [(нет)]>

мой.cnf

[root@host ~]# кошка /etc/my.cnf
#
# Эта группа читается как клиентом, так и сервером
# используйте его для опций, влияющих на всё
#
[клиент-сервер]

#
# включить все файлы из каталога config
#
!includedir /etc/my.cnf.d

[mysqld]
лог-ошибка=/var/lib/mysql/s102.halabtech.net.err
схема производительности = 0
innodb_file_per_table=1
механизм хранения по умолчанию = MyISAM
max_allowed_packet = 268435456
open_files_limit=40000
пропустить имя-решить

sql_mode=''

локальный файл = 0
connect_timeout = 25
ожидание_тайм-аут = 30
интерактивный_тайм-аут = 30
журнал медленных запросов = 1
long_query_time = 5
slow_query_log_file="/var/log/mysql-slow.log"

key_buffer_size = 128M
thread_stack = 128 КБ
thread_cache_size = 8
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M
innodb_buffer_pool_size = 2G

Спецификации сервера:

Процессор Intel(R) Core(TM) i7-8700 с тактовой частотой 3,20 ГГц

Память: 4107488k/68927488k доступно (7792k кода ядра, 1900528k отсутствует, 1353716k зарезервировано, 5950k данных, 1984k init)

Используемый размер файловой системы Доступно Использование % Установлено на
devtmpfs 32G 0 32G 0% /dev
tmpfs 32G 0 32G 0% /dev/shm
tmpfs 32G 28M 32G 1%/запуск
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/md2 437G 273G 142G 66% /
/dev/md1 488M 401M 62M 87% /загрузка
tmpfs 6.3G 0 6.3G 0% /выполнить/пользователь/0
tmpfs 6.3G 0 6.3G 0% /выполнить/пользователь/987
tmpfs 6.3G 0 6.3G 0% /выполнить/пользователь/1034

ОБНОВИТЬ

МЕДЛЕННЫЙ ДАМП ЗАПРОСА MYSQL: (Настоящие имена в базе данных изменены)

Чтение журнала медленных запросов mysql из /var/log/mysql-slow.log
Количество: 2 Время = 20,58 с (41 с) Блокировка = 0,00 с (0 с) Rows_sent = 4089261,5 (8178523), Rows_examined = 4089261,5 (8178523), Rows_affected = 0,0 (0), root[root]@localhost
  SELECT /*!40001 SQL_NO_CACHE */ * FROM `hb_udownloads`

Количество: 3 Время = 15,38 с (46 с) Блокировка = 0,00 с (0 с) Rows_sent = 81,0 (243), Rows_examined = 10026558,0 (30079674), Rows_affected = 0,0 (0), server***_fusion[server***_fusion] @локальный хост
  SELECT *,(выберите количество (id) из tbl_swkey
  где group_id=tbl_keygroup.id и used=0) как продано (выберите количество (id) из tbl_swkey
  где group_id=tbl_keygroup.id и used=1), как используется FROM tbl_keygroup
  упорядочить по `displayorder` по возрастанию

Количество: 1 Время = 8,68 с (8 с) Блокировка = 0,00 с (0 с) Rows_sent = 0,0 (0), Rows_examined = 4090482,0 (4090482), Rows_affected = 0,0 (0), support***_res[support***_res] @локальный хост
  ОБНОВЛЕНИЕ hb_udownloads УСТАНОВИТЬ `upackage_id` = 0, ГДЕ upackage_id = 403775

Количество: 1 Время = 5,64 с (5 с) Блокировка = 0,00 с (0 с) Rows_sent = 0,0 (0), Rows_examined = 4088258,0 (4088258), Rows_affected = 0,0 (0), support***_res[support***_res] @локальный хост
  ВЫБЕРИТЕ udownload_id КАК retval ОТ hb_udownloads, ГДЕ user_id = 415064 И file_id = 78499 ОГРАНИЧЕНИЕ 1

Количество: 1 Время = 5,58 с (5 с) Блокировка = 0,00 с (0 с) Rows_sent = 0,0 (0), Rows_examined = 4088256,0 (4088256), Rows_affected = 0,0 (0), support***_res[support***_res] @локальный хост
  ВЫБЕРИТЕ udownload_id КАК retval ОТ hb_udownloads, ГДЕ user_id = 208286 И file_id = 202629 ОГРАНИЧЕНИЕ 1

Количество: 1 Время = 5,35 с (5 с) Блокировка = 0,00 с (0 с) Rows_sent = 0,0 (0), Rows_examined = 4088255,0 (4088255), Rows_affected = 0,0 (0), support***_res[support***_res] @локальный хост
  ВЫБЕРИТЕ udownload_id КАК retval ОТ hb_udownloads, ГДЕ user_id = 235082 И file_id = 473624 ОГРАНИЧЕНИЕ 1

Количество: 1 Время = 5,21 с (5 с) Блокировка = 0,00 с (0 с) Rows_sent = 0,0 (0), Rows_examined = 4088254,0 (4088254), Rows_affected = 0,0 (0), support***_res[supporth***_res] @локальный хост
  ВЫБЕРИТЕ udownload_id КАК retval ОТ hb_udownloads, ГДЕ user_id = 61350 И file_id = 493488 ОГРАНИЧЕНИЕ 1

Количество: 1 Время = 5,17 с (5 с) Блокировка = 0,00 с (0 с) Rows_sent = 0,0 (0), Rows_examined = 4088259,0 (4088259), Rows_affected = 0,0 (0), support***_res[support***_res] @локальный хост
  ВЫБЕРИТЕ udownload_id КАК retval ОТ hb_udownloads, ГДЕ user_id = 338554 И file_id = 439150 ОГРАНИЧЕНИЕ 1

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

190
3 + 8
digijay avatar
флаг mx
digijay
Посмотрите свой журнал медленных запросов: `/var/log/mysql-slow.log`.Вы можете использовать `mysqldumpslow` ([см. здесь](https://mariadb.com/kb/en/mysqldumpslow/)) для получения сводки. Вы получаете какие-либо намеки? Пожалуйста [добавьте их к своему вопросу](https://serverfault.com/posts/1074131/edit)
0
Ответить
флаг ua
Rick James
Медленный журнал: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog Высокая загрузка ЦП обычно подразумевает отсутствие адекватных индексов и/или плохую формулировку запросов.
2
Ответить
флаг ua
Rick James
У вас действительно запущено около 60 ГБ программ? Кажется, что MariaDB использует менее 6 ГБ?
0
Ответить
Mahmoud Moussa avatar
флаг br
Mahmoud Moussa
@digijay Я добавил вывод mysqldumpslow в сообщения, пожалуйста, проверьте его. Большое спасибо.
1
Ответить
Mahmoud Moussa avatar
флаг br
Mahmoud Moussa
@RickJames Привет, Рик! Большое спасибо за ваш ответ, я добавил дамп медленного запроса, не могли бы вы проверить его и сказать мне, в чем именно проблема?
0
Ответить
Mahmoud Moussa avatar
флаг br
Mahmoud Moussa
@RickJames Да, у меня на сервере установлено 64 ГБ ОЗУ. Есть ли у вас какие-либо предложения? Большое спасибо.
0
Ответить
флаг ws
symcbean
Вам действительно, ДЕЙСТВИТЕЛЬНО нужно добавить некоторые индексы.
0
Ответить
Wilson Hauck avatar
флаг jp
Wilson Hauck
Запрос дополнительной информации - после того, как вы добавили предложенные индексы. Любые устройства SSD или NVME на хост-сервере MySQL? Опубликуйте на pastebin.com и поделитесь ссылками. Из вашего корня входа SSH, текстовые результаты: B) ПОКАЗАТЬ ГЛОБАЛЬНЫЙ СТАТУС; минимум через 24 часа UPTIME C) ПОКАЗАТЬ ГЛОБАЛЬНЫЕ ПЕРЕМЕННЫЕ; D) ПОКАЗАТЬ ПОЛНЫЙ СПИСОК ПРОЦЕССОВ; И Необязательная очень полезная информация, если таковая имеется, включает: htop ИЛИ top для наиболее активных приложений, ulimit -a для списка ограничений Linux/Unix, iostat -xm 5 3 для IOPS по устройствам и количеству ядер/процессоров, для анализа настройки рабочей нагрузки сервера, чтобы предоставить предложения.
0
Ответить
Рейтинг:3
Phill W. avatar Server
флаг cn
Phill W.

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

Глядя на один из ваших медленных запросов:

Время = 5,64 с (5 с) Rows_sent = 0,0 (0) Rows_examined = 4088258,0 (4088258)
ВЫБЕРИТЕ udownload_id AS retval 
ОТ hb_udownloads 
ГДЕ user_id = 415064  
И file_id = 78499  
ПРЕДЕЛ 1

Запрос читает всю таблицу строк 4M, но возвращает не более один из них (а в данном случае вообще ни одного).
Тебе нужно индексы в этой таблице для поддержки этого запроса. Составной на Логин пользователя и file_id было бы хорошим началом.

Еще один убийца производительности здесь:

Время = 20,58 с (41 с) Rows_sent = 4089261,5 (8178523) Rows_examined = 4089261,5 (8178523)
ВЫБЕРИТЕ /*!40001 SQL_NO_CACHE */ * 
ИЗ `hb_udownloads`

Никогда не используйте "Выбрать *" в Производственном кодексе.
Всегда укажите столбцы, которые вы хотите явно. Я предполагаю, что эта таблица в последнее время «растет» (увеличивает количество столбцов).
Хорошо, без предложения where это все равно будет сканировать таблицу, но возникает вопрос - что собирается делать бедный клиент [приложение] делать со строками 4M, которые этот запрос «бросает» на него?

0 + 0
Рейтинг:1
avatar Server
флаг ua
Rick James

** Это должно сделать первый намного быстрее:

ВЫБРАТЬ k.*, s.продано, s.использовано
    ОТ ( ВЫБЕРИТЕ group_id,
                  СУММА(используется = 0) КАК ПРОДАНО,
                  СУММ(используется = 1) КАК используется
               ОТ tbl_swkey
         ) АС
    ПРИСОЕДИНЯЙТЕСЬ к tbl_keygroup AS k ON s.group_id = k.id
    ЗАКАЗАТЬ ПО

И имеют

tbl_swkey: ИНДЕКС (id_группы, используется)
tbl_keygroup: ИНДЕКС (порядок отображения)

Если вам необходимо обсудить этот запрос, пожалуйста, предоставьте ПОКАЗАТЬ СОЗДАТЬ ТАБЛИЦУ для обеих таблиц, размеры таблиц и ОБЪЯСНИТЬ ВЫБРАТЬ....

** Этот

ВЫБЕРИТЕ udownload_id AS retval
    ОТ hb_udownloads
    ГДЕ user_id = 415064
      И file_id = 78499
    ПРЕДЕЛ 1

нужен составной индекс с 3 столбцами, чтобы быть намного быстрее:

ИНДЕКС(user_id, file_id, udownload_id)

Вам все равно, какой соответствующий ряд вы получите? Или вы должны добавить СОРТИРОВАТЬ ПО? Если вы добавите СОРТИРОВАТЬ ПО, мой совет по индексу, возможно, придется изменить.

** Это пришло от mysqldump?

SELECT /*!40001 SQL_NO_CACHE */ * FROM `hb_udownloads`

Если вас не устраивает инвазивность дампов, на dba.stackexchange.com есть несколько дискуссий на эту тему.

0 + 0
Рейтинг:1
Matthew Ife avatar Server
флаг jo
Matthew Ife

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

Например:

Rows_examined=10026558.0 (30079674), Rows_affected=0.0 (0), server***_fusion[server***_fusion]@localhost
  SELECT *,(выберите количество (id) из tbl_swkey
  где group_id=tbl_keygroup.id и used=0) как продано (выберите количество (id) из tbl_swkey
  где group_id=tbl_keygroup.id и used=1), как используется FROM tbl_keygroup
  упорядочить по `displayorder` по возрастанию

Этот запрос сканирует 30000000 строк.

Возможно, вы могли бы изменить это, например, на два запроса:

SELECT * FROM tbl_keygroup ORDER по `displayorder` asc;
SELECT count(id) FROM tbl_swkey WHERE used IN (0,1) GROUP BY used;

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

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

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

0 + 0
флаг Moldova
Admin
Этот вопрос на других языках:

Ответить или комментировать

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