MariaDB phát điên với thời gian cpu 500%

TÌNH TRẠNG TOÀN CẦU

MariaDB [(không có)]> HIỂN THỊ TRẠNG THÁI TOÀN CẦU;
+--------------------------------------------- -------------+------------------------------------ --------------+
| Tên_biến | Giá trị |
+--------------------------------------------- -------------+------------------------------------ --------------+
| Aborted_clients | 10 |
| Aborted_connects | 17 |
| Access_denied_errors | 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_roles | 0 |
| Acl_table_grant | 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 |
| Aria_pagecache_writes | 36593 |
| Aria_transaction_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_write | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| bận_thời gian | 0,000000 |
| Bytes_received | 243371501 |
| Số byte đã gửi | 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 |
| Sự kiện tạo_sự kiện | 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_xóa_đa | 0 |
| Com_do | 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_server | 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 |
| Com_grant | 0 |

488 hàng trong tập hợp (0,00 giây)

MariaDB [(không có)]>

my.cnf

[root@host ~]# mèo /etc/my.cnf
#
# Nhóm này được cả máy khách và máy chủ đọc
# sử dụng nó cho các tùy chọn ảnh hưởng đến mọi thứ
#
[máy khách-máy chủ]

#
# bao gồm tất cả các tệp từ thư mục cấu hình
#
!includedir /etc/my.cnf.d

[mysqld]
log-error=/var/lib/mysql/s102.halabtech.net.err
hiệu suất-lược đồ = 0
innodb_file_per_table=1
default-storage-engine=MyISAM
max_allowed_packet=268435456
open_files_limit=40000
bỏ qua tên-giải quyết

sql_mode=''

local-infile=0
connect_timeout=25
wait_timeout=30
Interactive_timeout=30
truy vấn chậm-log=1
long_query_time=5
slow_query_log_file="/var/log/mysql-slow.log"

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

Thông số máy chủ:

CPU Intel(R) Core(TM) i7-8700 @ 3.20GHz

Bộ nhớ: 4107488k/68927488k khả dụng (7792k kernel code, 1900528k vắng mặt, 1353716k dự trữ, 5950k dữ liệu, 1984k init)

Kích thước hệ thống tệp được sử dụng Sẵn có Sử dụng % Được gắn trên
devtmpfs 32G 0 32G 0%/dev
tmpfs 32G 0 32G 0%/dev/shm
tmpfs 32G 28M 32G 1%/lần chạy
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/md2 437G 273G 142G 66%/
/dev/md1 488M 401M 62M 87%/boot
tmpfs 6.3G 0 6.3G 0%/chạy/người dùng/0
tmpfs 6.3G 0 6.3G 0%/chạy/người dùng/987
tmpfs 6.3G 0 6.3G 0%/chạy/người dùng/1034

CẬP NHẬT

MYSQL SLOW QUERY DUMP: (Tên thật của cơ sở dữ liệu đã thay đổi)

Đọc nhật ký truy vấn chậm mysql từ /var/log/mysql-slow.log
Đếm: 2 Thời gian=20,58 giây (41 giây) Khóa=0,00 giây (0 giây) Rows_sent=4089261,5 (8178523), Rows_examined=4089261,5 (8178523), Rows_affected=0,0 (0), root[root]@localhost
  CHỌN /*!40001 SQL_NO_CACHE */ * TỪ `hb_udownloads`

Đếm: 3 Thời gian=15,38 giây (46 giây) Khóa=0,00 giây (0 giây) Rows_sent=81,0 (243), Rows_examined=10026558.0 (30079674), Rows_affected=0,0 (0), server***_fusion[server***_fusion] @localhost
  CHỌN *, (chọn số lượng (id) từ tbl_swkey
  trong đó group_id=tbl_keygroup.id và used=0) là đã bán,(chọn số lượng(id) từ tbl_swkey
  trong đó group_id=tbl_keygroup.id và used=1) như được sử dụng TỪ tbl_keygroup
  sắp xếp theo `displayorder` asc

Đếm: 1 Thời gian=8,68 giây (8 giây) Khóa=0,00 giây (0 giây) Rows_sent=0,0 (0), Rows_examined=4090482,0 (4090482), Rows_affected=0,0 (0), support***_res[support***_res] @localhost
  CẬP NHẬT hb_udownloads SET `upackage_id` = 0 WHERE upackage_id = 403775

Đếm: 1 Thời gian=5,64 giây (5 giây) Khóa=0,00 giây (0 giây) Rows_sent=0,0 (0), Rows_examined=4088258,0 (4088258), Rows_affected=0,0 (0), support***_res[support***_res] @localhost
  CHỌN udownload_id NHƯ retval TỪ hb_udownloads WHERE user_id = 415064 AND file_id = 78499 GIỚI HẠN 1

Đếm: 1 Thời gian=5,58 giây (5 giây) Khóa=0,00 giây (0 giây) Rows_sent=0,0 (0), Rows_examined=4088256.0 (4088256), Rows_affected=0,0 (0), support***_res[support***_res] @localhost
  CHỌN udownload_id NHƯ retval TỪ hb_udownloads WHERE user_id = 208286 AND file_id = 202629 GIỚI HẠN 1

Đếm: 1 Thời gian=5,35 giây (5 giây) Khóa=0,00 giây (0 giây) Rows_sent=0,0 (0), Rows_examined=4088255,0 (4088255), Rows_affected=0,0 (0), support***_res[support***_res] @localhost
  CHỌN udownload_id NHƯ retval TỪ hb_udownloads WHERE user_id = 235082 VÀ file_id = 473624 GIỚI HẠN 1

Đếm: 1 Thời gian=5,21 giây (5 giây) Khóa=0,00 giây (0 giây) Rows_sent=0,0 (0), Rows_examined=4088254,0 (4088254), Rows_affected=0,0 (0), support***_res[supporth***_res] @localhost
  CHỌN udownload_id NHƯ retval TỪ hb_udownloads WHERE user_id = 61350 VÀ file_id = 493488 GIỚI HẠN 1

Đếm: 1 Thời gian=5,17 giây (5 giây) Khóa=0,00 giây (0 giây) Rows_sent=0,0 (0), Rows_examined=4088259,0 (4088259), Rows_affected=0,0 (0), support***_res[support***_res] @localhost
  CHỌN udownload_id NHƯ retval TỪ hb_udownloads WHERE user_id = 338554 AND file_id = 439150 GIỚI HẠN 1

Vui lòng cho biết lý do tại sao đôi khi tôi gặp sự cố đột biến thực sự lớn đến 500% và nên làm gì để giải quyết vấn đề vì tôi gặp rất nhiều lỗi 50x. Xin vui lòng tha thứ cho tiếng Anh xấu của tôi quá. Cảm ơn bạn trước.

Mức sử dụng CPU cao thường có nghĩa là phải quét nhiều bảng trong bộ nhớ.

Nhìn vào một trong những truy vấn chậm của bạn:

Thời gian=5.64s (5s) Rows_sent=0.0 (0) Rows_examined=4088258.0 (4088258)
CHỌN udoad_id AS retval 
TỪ hb_udownloads 
Ở ĐÂU user_id = 415064  
VÀ file_id = 78499  
GIỚI HẠN 1

Truy vấn đang đọc toàn bộ bảng 4M hàng nhưng trả về nhiều nhất một trong số họ (và, trong trường hợp này, không có gì cả).
Bạn cần chỉ số trên bảng này để hỗ trợ truy vấn này. Một tổng hợp trên tên người dùng và file_id sẽ là một khởi đầu tốt.

Một sát thủ hiệu suất khác ở đây:

Thời gian=20,58 giây (41 giây) Rows_sent=4089261,5 (8178523) Rows_examined=4089261,5 (8178523)
CHỌN /*!40001 SQL_NO_CACHE */ * 
TỪ `hb_udownloads`

Không bao giờ sử dụng "lựa chọn *" trong Mã sản xuất.
Luôn chỉ định các cột bạn muốn một cách rõ ràng. Tôi đoán rằng gần đây bảng này đã "phát triển" (tăng cột).
OK, không có mệnh đề where, dù sao thì điều này cũng sẽ được quét bảng, nhưng điều đó đặt ra câu hỏi - [ứng dụng] máy khách kém sẽ làm gì làm với 4 triệu hàng mà truy vấn này "ném" vào nó?

Một số truy vấn này có vẻ như cần được tối ưu hóa.

Ví dụ:

Rows_examined=10026558.0 (30079674), Rows_affected=0.0 (0), máy chủ***_fusion[máy chủ***_fusion]@localhost
  CHỌN *, (chọn số lượng (id) từ tbl_swkey
  trong đó group_id=tbl_keygroup.id và used=0) là đã bán,(chọn số lượng(id) từ tbl_swkey
  trong đó group_id=tbl_keygroup.id và used=1) như được sử dụng TỪ tbl_keygroup
  sắp xếp theo `displayorder` asc

Truy vấn này đang quét 30000000 hàng.

Có thể bạn có thể thay đổi cái này, ví dụ thành hai truy vấn:

CHỌN * TỪ ĐẶT HÀNG tbl_keygroup theo `displayorder` asc;
CHỌN số lượng (id) TỪ tbl_swkey Ở ĐÂU được sử dụng TRONG (0,1) NHÓM THEO được sử dụng;

Vì bản thân truy vấn liên quan đến một phép nối vô ích và hai lần quét toàn bộ bảng có thể giảm một nửa.

Phần lớn những gì bạn đã trình bày chỉ ra các vấn đề với tối ưu hóa truy vấn và tôi sẽ xem nhật ký truy vấn chậm đó để xác định cách tốt nhất để truy xuất dữ liệu đó với số lần quét bảng ít nhất.

Bạn cũng có thể muốn lập chỉ mục nhiều dữ liệu đó cho cùng một mục đích.

** Điều này sẽ làm cho cái đầu tiên nhanh hơn rất nhiều:

CHỌN k.*, s.sold, s.used
    TỪ ( CHỌN group_id,
                  TỔNG (đã sử dụng = 0) NHƯ đã bán,
                  TỔNG (đã sử dụng = 1) NHƯ đã sử dụng
               TỪ tbl_swkey
         ) Mông
    THAM GIA tbl_keygroup NHƯ k BẬT s.group_id = k.id
    ĐẶT HÀNG THEO thứ tự hiển thị

Và có

tbl_swkey: INDEX(group_id, đã sử dụng)
tbl_keygroup: INDEX(thứ tự hiển thị)

Nếu bạn cần thảo luận thêm về truy vấn này, vui lòng cung cấp HIỂN THỊ TẠO BẢNG cho cả hai bảng, kích thước bảng và GIẢI THÍCH CHỌN ....

** Cái này

CHỌN udoad_id AS retval
    TỪ hb_udownloads
    Ở ĐÂU user_id = 415064
      VÀ file_id = 78499
    GIỚI HẠN 1 

cần một chỉ mục tổng hợp 3 cột nhanh hơn rất nhiều:

INDEX(user_id, file_id, udoad_id)

Bạn không quan tâm bạn nhận được hàng phù hợp nào? Hoặc bạn nên thêm một ĐẶT BỞI? Nếu bạn thêm một ĐẶT BỞI, lời khuyên về chỉ mục của tôi có thể cần phải thay đổi.

** Điều này đến từ mysqldump?

CHỌN /*!40001 SQL_NO_CACHE */ * TỪ `hb_udownloads`

Nếu bạn không hài lòng với mức độ xâm lấn của các bãi chứa, có một số cuộc thảo luận trên dba.stackexchange.com về chủ đề đó.