MySQL โหลด CPU สูง

ฉันมีปัญหากับ MariaDB เมื่อเชื่อมต่อการวิเคราะห์ ~ 200 mysql เริ่มแสดงโหลด 132% และผลัก CPU หลักไปที่ประมาณ 40% ทำให้เว็บช้ามาก ฉันใช้ centos 7 ( 8GB ram , 2 คอร์ , NVME SSD + Nginx + php-fpm )

[ลูกค้า]
#password = รหัสผ่านของคุณ
พอร์ต = 3306
ซ็อกเก็ต = /var/lib/mysql/mysql.sock

[มายเอสคิวลด์]
wait_timeout=300
interactive_timeout = 300
binlog_cache_size = 128K
thread_stack = 256K
join_buffer_size = 2048K
query_cache_type = 1
max_heap_table_size = 512M
พอร์ต = 3306
ซ็อกเก็ต = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
ข้ามล็อคภายนอก
key_buffer_size = 384M
max_allowed_packet = 100G
table_open_cache = 384
sort_buffer_size = 1024K
net_buffer_length = 4K
read_buffer_size = 1024K
read_rnd_buffer_size = 768K
myisam_sort_buffer_size = 16M
thread_cache_size = 128
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 8M
tmp_table_size = 512M
#sql-mode=NO_ENGINE_SUBSTITUTION

#ข้ามชื่อแก้ไข
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=ผสม
รหัสเซิร์ฟเวอร์ = 1
expier_logs_days = 10
slow_query_log=1
slow-query-log-file=/home/abc.com.com/logs/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=เปิด


innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:ขยายอัตโนมัติ
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 1G
innodb_log_file_size = 128M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 2
innodb_write_io_threads = 2
innodb_buffer_pool_instances = 1


[mysqldump]
เร็ว
max_allowed_packet = 500M

[มายเอสคิวแอล]
ไม่มีการรีแฮชอัตโนมัติ

[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 1M
read_buffer = 2M
Write_buffer = 2M

[mysqlhotcopy]
หมดเวลาโต้ตอบ

และจูนผ่าน mysqltuner

-------- สถิติเครื่องมือจัดเก็บข้อมูล --------------------------------------- --------------------------
[--] สถานะ: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA
[--] ข้อมูลในตาราง InnoDB: 48.8M (ตาราง: 21)
[ตกลง] ตารางที่แยกส่วนทั้งหมด: 0

-------- ตัวชี้วัดประสิทธิภาพการวิเคราะห์ --------------------------------------- -----------------------
[--] innodb_stats_on_metadata: ปิด
[ตกลง] ไม่มีการอัปเดตสถิติระหว่างการสืบค้น INFORMATION_SCHEMA

-------- คำแนะนำด้านความปลอดภัย ---------------------------------------- --------------------------
[ตกลง] ไม่มีบัญชีนิรนามสำหรับผู้ใช้ฐานข้อมูลใดๆ
[ตกลง] ผู้ใช้ฐานข้อมูลทั้งหมดได้รับการกำหนดรหัสผ่าน
[!!] ไม่มีรายการไฟล์รหัสผ่านพื้นฐาน!

-------- คำแนะนำด้านความปลอดภัย CVE --------------------------------------- -----------------------
[--] ข้ามเนื่องจาก --cvefile ตัวเลือกไม่ได้กำหนด

-------- การวัดประสิทธิภาพ ---------------------------------------- -------------------------------
[--] สูงสุด: 8m 0s (85K q [178.621 qps], 6K conn, TX: 41M, RX: 7M)
[--] อ่าน / เขียน: 98% / 2%
[--] เปิดใช้งานการบันทึกไบนารี (โหมด GTID: เปิด)
[--] หน่วยความจำกายภาพ : 7.6G
[--] หน่วยความจำ MySQL สูงสุด : 504.5G
[--] หน่วยความจำกระบวนการอื่น: 0B
[--] บัฟเฟอร์ทั้งหมด: 2.0G ทั่วโลก + 1.0G ต่อเธรด (สูงสุด 500 เธรด)
[--] P_S การใช้หน่วยความจำสูงสุด: 0B
[--] การใช้หน่วยความจำสูงสุดของ Galera GCache: 0B
[!!] การใช้หน่วยความจำสูงสุด: 95.5G (1250.25% ของ RAM ที่ติดตั้ง)
[!!] การใช้หน่วยความจำสูงสุดที่เป็นไปได้: 504.5G (6604.94% ของ RAM ที่ติดตั้ง)
[!!] การใช้หน่วยความจำโดยรวมที่เป็นไปได้กับกระบวนการอื่นเกินหน่วยความจำ
[ตกลง] ข้อความค้นหาช้า: 0% (0/85K)
[ตกลง] การใช้งานสูงสุดของการเชื่อมต่อที่มีอยู่: 18% (93/500)
[ตกลง] การเชื่อมต่อที่ถูกยกเลิก: 0.04% (3/6727)
[!!] การจำแนกชื่อเปิดใช้งานอยู่ : การจำแนกชื่อแบบย้อนกลับถูกสร้างขึ้นสำหรับการเชื่อมต่อใหม่แต่ละครั้ง และสามารถลดประสิทธิภาพการทำงานได้
[ตกลง] แคชการค้นหาถูกปิดใช้งานตามค่าเริ่มต้นเนื่องจากการโต้แย้ง mutex บนเครื่องมัลติโปรเซสเซอร์
[ตกลง] การเรียงลำดับที่ต้องการตารางชั่วคราว: 0% (0 การเรียงลำดับชั่วคราว / 700 การเรียงลำดับ)
[ตกลง] ไม่มีการรวมโดยไม่มีดัชนี
[!!] สร้างตารางชั่วคราวบนดิสก์: 88% (5K บนดิสก์ / รวม 6K)
[ตกลง] อัตราการเข้าถึงแคชของเธรด: 98% (สร้าง 93 ครั้ง / เชื่อมต่อ 6K)
[ตกลง] อัตราการเข้าถึงแคชของตาราง: 215% (เปิด 114 / เปิด 53)
[ตกลง] table_definition_cache(400) สูงกว่าจำนวนตาราง(261)
[ตกลง] ขีดจำกัดการเปิดไฟล์ที่ใช้: 0% (29/65K)
[ตกลง] ล็อคโต๊ะทันที: 100% (65K ทันที / 65K ล็อค)
[ตกลง] การเข้าถึงหน่วยความจำแคช Binlog: 100.00% (1144 หน่วยความจำ / 1144 ทั้งหมด)

-------- แผนการปฏิบัติงาน ---------------------------------------- --------------------------------
[--] สคีมาประสิทธิภาพถูกปิดใช้งาน
[--] หน่วยความจำที่ใช้โดย P_S: 0B

-------- ตัวชี้วัด ThreadPool ---------------------------------------- --------------------------------
[--] เปิดใช้งานสถิติ ThreadPool
[--] ขนาดเธรดพูล: 2 เธรด
[--] การใช้ค่าเริ่มต้นนั้นดีพอสำหรับเวอร์ชันของคุณ (10.0.38-MariaDB)

-------- เมตริก MyISAM ---------------------------------------- ------------------------------------
[!!] คีย์บัฟเฟอร์ที่ใช้: 18.7% (ใช้ไป 75M / แคช 402M)
[ตกลง] ขนาดบัฟเฟอร์คีย์ / ดัชนี MyISAM ทั้งหมด: 384.0M/123.0K
[ตกลง] อัตราการอ่านคีย์บัฟเฟอร์: 99.7% (355 แคช / 1 อ่าน)
[ตกลง] อัตราการเข้าถึงบัฟเฟอร์คีย์การเขียน: 100.0% (96 แคช / 96 เขียน)

-------- ตัวชี้วัด InnoDB ---------------------------------------- ------------------------------------
[--] เปิดใช้งาน InnoDB
[--] การทำงานพร้อมกันของเธรด InnoDB: 0
[ตกลง] เปิดใช้งานไฟล์ InnoDB ต่อตารางแล้ว
[ตกลง] พูลบัฟเฟอร์ InnoDB / ขนาดข้อมูล: 1.0G/48.8M
[ตกลง] Ratio InnoDB log file size / InnoDB Buffer pool size: 128.0M * 2/1.0G ควรเท่ากับ 25%
[ตกลง] อินสแตนซ์ของพูลบัฟเฟอร์ InnoDB: 1
[--] InnoDB Buffer Pool Chunk Size ไม่ได้ใช้หรือกำหนดในเวอร์ชันของคุณ
[ตกลง] ประสิทธิภาพบัฟเฟอร์การอ่าน InnoDB: 100.00% (422760044 ครั้ง/ รวม 422761190 ครั้ง)
[!!] ประสิทธิภาพ InnoDB Write Log: 46.23% (484 ครั้ง/ รวม 1,047 ครั้ง)
[ตกลง] การรอบันทึก InnoDB: 0.00% (0 รอ / 1531 เขียน)

-------- Aria เมตริก ---------------------------------------- --------------------------------------
[--] เปิดใช้งาน Aria Storage Engine
[ตกลง] ขนาดแคชเพจ Aria / ดัชนี Aria ทั้งหมด: 128.0M/0B
[ตกลง] อัตราการเข้าถึง Aria pagecache: 98.6% (แคช 146 ครั้ง / อ่าน 2 ครั้ง)

-------- TokuDB เมตริก ---------------------------------------- ------------------------------------
[--] TokuDB ถูกปิดใช้งาน

-------- ตัวชี้วัด XtraDB ---------------------------------------- ------------------------------------
[--] XtraDB ถูกปิดใช้งาน

-------- Galera เมตริก ---------------------------------------- ------------------------------------
[--] Galera ถูกปิดใช้งาน

-------- เมตริกการจำลองแบบ ---------------------------------------- -------------------------------
[--] การจำลองแบบซิงโครนัสของ Galera: NO
[--] ไม่มีการจำลองแบบทาสสำหรับเซิร์ฟเวอร์นี้
[--] รูปแบบ Binlog: ผสม
[--] รองรับ XA: เปิด
[--] ต้นแบบการจำลองแบบกึ่งซิงโครนัส: ไม่ได้เปิดใช้งาน
[--] ทาสการจำลองแบบกึ่งซิงโครนัส: ไม่ได้เปิดใช้งาน
[--] นี่คือเซิร์ฟเวอร์แบบสแตนด์อโลน

-------- ข้อแนะนำ ----------------------------------------- ----------------------------------
คำแนะนำทั่วไป:
    ควบคุมบรรทัดคำเตือนลงในไฟล์ /var/lib/mysql/dichvu.err
    ควบคุมบรรทัดข้อผิดพลาดลงในไฟล์ /var/lib/mysql/dichvu.err
    MySQL เริ่มต้นภายใน 24 ชั่วโมงที่ผ่านมา - คำแนะนำอาจไม่ถูกต้อง
    ลดรอยเท้าหน่วยความจำ MySQL โดยรวมของคุณเพื่อความเสถียรของระบบ
    อุทิศเซิร์ฟเวอร์นี้ให้กับฐานข้อมูลของคุณเพื่อประสิทธิภาพสูงสุด
    กำหนดค่าบัญชีของคุณด้วย ip หรือ subnets เท่านั้น จากนั้นอัปเดตการกำหนดค่าของคุณด้วยskip-name-resolve=1
    ตารางชั่วคราวมีขนาดใหญ่อยู่แล้ว - ลดขนาดชุดผลลัพธ์
    ลดการค้นหา SELECT DISTINCT ของคุณโดยไม่มีข้อ จำกัด
    ควรเปิดใช้งานสคีมาประสิทธิภาพเพื่อการวินิจฉัยที่ดีขึ้น
    ไม่ควรเปิดใช้งานสคีมาประสิทธิภาพสำหรับ MariaDB 10.0 สำหรับปัญหาด้านประสิทธิภาพ
ตัวแปรที่จะปรับ:
  *** การใช้หน่วยความจำสูงสุดของ MySQL นั้นสูงมากจนเป็นอันตราย ***
  *** เพิ่ม RAM ก่อนเพิ่มตัวแปรบัฟเฟอร์ MySQL ***
    performance_schema = เปิด เปิดใช้งาน PFS
    performance_schema = ปิด ปิดใช้งาน PFS

กรุณาให้คำแนะนำในกรณีนี้

ฟรี -m เมื่อ cpu โหลดสูง

              รวมใช้บัฟ/แคชที่ใช้ร่วมกันฟรี
เมม: 7821 1421 4755 431 1644 5715
สลับ: 1023 0 1023

tmp_table_size = 512M --สูงเกินไป; จำกัดไว้ที่ประมาณ 1% ของ RAM (สิ่งนี้จะไม่ส่งผลกระทบต่อ CPU มากนัก แต่จะช่วยหลีกเลี่ยงการสลับ)

CPU สูงหมายถึงข้อความค้นหาที่มีการจัดทำดัชนีไม่ดีหรือมีสูตรที่ไม่ดี ใช้สโลว์ล็อก มากกว่า: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

จากนั้นโพสต์บน stackoverflow.com มีคนเฉพาะ MySQL มากขึ้นที่นั่น