MySql 8 หยุดทำงานแบบวนซ้ำโดยไม่มีข้อผิดพลาดเฉพาะและมีหน่วยความจำเพียงพอ

ฉันใช้ Ubuntu 20.04.03 กับ Mysql 8.0.27 ฉันได้ติดตั้ง LAMP ใหม่ตั้งแต่เริ่มต้นหลายครั้ง และในขณะนี้ ฉันมีไซต์ WordPress เพียง 3 ไซต์ แต่ฉันได้ทดสอบเพียง 1 ไซต์และเพียง 2 ไซต์เท่านั้น เพิ่ม RAM เป็น 2GB และ 3GB Swap

ดูเหมือนว่าจะไม่มีอะไรทำงาน เนื่องจาก Mysql 8.0.27 หยุดทำงาน ทำให้เกิดปัญหาการเชื่อมต่อฐานข้อมูลในทุก ๆ ไซต์ทุก ๆ คืน แม้ว่าจะเป็นไซต์ใหม่ทั้งหมดที่ไม่มีทราฟฟิกก็ตาม เมื่อฉันแก้ไขโพสต์หรือแม้แต่เรียกดูเว็บไซต์ใด ๆ เหล่านั้น MySql หยุดทำงานอีกครั้ง บางครั้งมันไม่ได้เริ่มต้นด้วย systemctl รีสตาร์ท mysql.

บันทึกข้อผิดพลาด Apache ไม่แสดงสิ่งที่สำคัญ:

/usr/sbin/mysqld (mysqld 8.0.27-0ubuntu0.20.04.1) เริ่มต้นเป็นกระบวนการ 1524639

การเริ่มต้น InnoDB ได้เริ่มต้นขึ้นแล้ว

การเริ่มต้น InnoDB สิ้นสุดลงแล้ว

กำลังเริ่มการกู้คืนข้อขัดข้อง XA... การกู้คืนข้อขัดข้อง XA เสร็จสิ้นแล้ว

มีการเปิดใช้งาน TLS เวอร์ชัน TLSv1 ที่เลิกใช้แล้วสำหรับแชนเนล mysql_main

มีการเปิดใช้งาน TLS เวอร์ชัน TLSv1.1 ที่เลิกใช้แล้วสำหรับแชนเนล mysql_main

ใบรับรอง CA ca.pem ลงนามด้วยตนเอง

ช่อง mysql_main กำหนดค่าให้รองรับ TLS ขณะนี้รองรับการเชื่อมต่อที่เข้ารหัสสำหรับช่องนี้

ปลั๊กอิน X พร้อมสำหรับการเชื่อมต่อ ที่อยู่การเชื่อมโยง: '127.0.0.1' พอร์ต: 33060, ซ็อกเก็ต: /var/run/mysqld/mysqlx.sock

ฉันตรวจสอบแล้วว่าที่จริงแล้วการกำหนดค่ากำลังโหลดเวอร์ชัน TLS ทั้งหมดตามที่ยอมรับได้ ดังนั้นจึงไม่มีข้อผิดพลาดจริง

วารสารctl -u mysql บันทึกสมุดรายวันล่าสุด:

28 มกราคม 10:29:37 www.ignicion.org systemd[1]: mysql.service: ล้มเหลวด้วยผลลัพธ์ 'สัญญาณ'
28 ม.ค. 10:29:37 www.ignicion.org systemd[1]: mysql.service: งานรีสตาร์ทตามกำหนดเวลา ตัวนับการรีสตาร์ทอยู่ที่ 5
28 มกราคม 10:29:37 www.ignicion.org systemd[1]: หยุดเซิร์ฟเวอร์ชุมชน MySQL
28 ม.ค. 10:29:37 น. www.ignicion.org systemd[1]: การเริ่มต้นเซิร์ฟเวอร์ชุมชน MySQL...
28 ม.ค. 10:29:43 www.ignicion.org systemd[1]: mysql.service: ออกจากกระบวนการหลัก, code=killed, status=9/KILL
28 มกราคม 10:29:43 www.ignicion.org systemd[1]: mysql.service: ล้มเหลวด้วยผลลัพธ์ 'สัญญาณ'
28 มกราคม 10:29:43 www.ignicion.org systemd[1]: ไม่สามารถเริ่ม MySQL Community Server
28 ม.ค. 10:29:43 www.ignicion.org systemd[1]: mysql.service: งานรีสตาร์ทตามกำหนดเวลา ตัวนับการรีสตาร์ทอยู่ที่ 6
28 มกราคม 10:29:43 www.ignicion.org systemd[1]: หยุดเซิร์ฟเวอร์ชุมชน MySQL
28 ม.ค. 10:29:43 น. www.ignicion.org systemd[1]: การเริ่มต้นเซิร์ฟเวอร์ชุมชน MySQL...
28 มกราคม 10:29:50 www.ignicion.org systemd[1]: เริ่มเซิร์ฟเวอร์ชุมชน MySQL

ฉันรู้ว่ามันเป็นปัญหาเกี่ยวกับหน่วยความจำ แต่ทำไม คือไม่ชอบให้เซิฟเวอร์ทำอะไรมาก ฉันได้รับการตรวจสอบกระบวนการและ Mysql เป็นหน่วยความจำที่ใช้ทั้งหมด ฐานข้อมูลใหม่เดียวกันนี้ทำงานได้ดีบน ubuntu 18 ดังนั้นฉันจึงไม่มีความคิดจริงๆ และไม่พบวิธีแก้ไขปัญหาที่เกี่ยวข้องที่ฉันพบในฟอรัม โซลูชันบางอย่างที่ฉันพบกำลังพูดถึงความเสียหายของฐานข้อมูล/ตาราง แต่นี่เป็นฐานข้อมูลใหม่ล่าสุดและการทำงานผิดพลาดของฮาร์ดแวร์ซึ่งถูกยกเลิกโดย Digital Ocean Support

ฉันจะขอบคุณข้อมูลเชิงลึกใด ๆ

อัปเดต #1 ไม่มีการสำรองข้อมูลหรืองานอื่นใดที่กำหนดไว้ มันเป็นเพียงการติดตั้งใหม่และฐานข้อมูลใหม่ นี่คือไฟล์กำหนดค่าของฉันใน Ubuntu 20: /etc/mysql/mysql.conf.d

[มายเอสคิวลด์]
ผู้ใช้ = mysql
ที่อยู่ผูก = 127.0.0.1
mysqlx-ผูกที่อยู่ = 127.0.0.1
key_buffer_size = 16M
myisam-recover-options = สำรองข้อมูล
log_error = /var/log/mysql/error.log
max_binlog_size = 100M
innodb_file_per_table = 1

และจากไฟล์บันทึกเคอร์เนลของฉัน (tail -100 /var/log/kern.log) ดูเหมือนว่าไฟล์ SIGKILL 9 สัญญาณฆ่าระยะ สัญญาณกำลังฆ่า mysql เนื่องจากการใช้หน่วยความจำมากเกินไป

28 มกราคม 18:12:26 www เคอร์เนล: [623011.971582] oom-kill:constraint=CONSTRAINT_NONE, 
nodemask= (null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/mysql.service,task=mysqld,pid=1669785,uid=113

28 ม.ค. 18:12:26 www เคอร์เนล: [623011.971617] หน่วยความจำไม่เพียงพอ: กระบวนการถูกฆ่า 166978 
5 (mysqld) total-vm:720836kB, anon-rss:292028kB, file-rss:804kB, shmem-rss:0kB, 
UID:113 pgtables:816kB oom_score_adj:0

28 มกราคม 18:12:26 www เคอร์เนล: [623012.005506] oom_reaper: กระบวนการเก็บเกี่ยว 1669785 (mysqld), ตอนนี้ anon-rss:0kB, file-rss:0kB, shmem-rss:0kB

ดังนั้นฉันจึงอ่านว่าฉันควรตรวจสอบการกำหนดค่าบัฟเฟอร์ Mysql และนั่นคือสิ่งที่ฉันอยู่ในขณะนี้

อัปเดต #2 แมว /proc/meminfo

MemTotal: 2030808 kB
MemFree: 54016 กิโลไบต์
หน่วยความจำที่มีอยู่: 3424 kB
บัฟเฟอร์: 240 กิโลไบต์
แคช: 285620 กิโลไบต์
สลับแคช: 44532 กิโลไบต์
ใช้งานอยู่: 1188660 กิโลไบต์
ไม่ใช้งาน: 495868 กิโลไบต์
ใช้งานอยู่(ไม่ใช้งาน): 1187984 kB
ไม่ใช้งาน (ไม่ใช้งาน): 495088 kB
ใช้งานอยู่(ไฟล์): 676 kB
ไม่ใช้งาน (ไฟล์): 780 kB
ไม่สามารถหลีกเลี่ยงได้: 19120 kB
ล็อค: 19120 kB
SwapTotal: 3145724 กิโลไบต์
สวอปฟรี: 0 กิโลไบต์
สกปรก: 0 กิโลไบต์
การเขียนกลับ: 0 kB
อานนท์หน้า: 1383352 kB
แมปแล้ว: 284872 กิโลไบต์
ชม: 276064 กิโลไบต์
KReclaimable: 47968 kB
พื้น: 171388 กิโลไบต์
SReclaimable: 47968 กิโลไบต์
SUnreclaim: 123420 กิโลไบต์
KernelStack: 7744 กิโลไบต์
PageTables: 59832 กิโลไบต์
NFS_Unstable: 0 กิโลไบต์
การตีกลับ: 0 กิโลไบต์
WritebackTmp: 0 กิโลไบต์
CommitLimit: 4161128 กิโลไบต์
Commit_AS: 9186644 กิโลไบต์
VmallocTotal: 34359738367 กิโลไบต์
VmallocUsed: 16512 กิโลไบต์
VmallocChunk: 0 กิโลไบต์
ประสิทธิภาพการทำงาน: 1808 kB
ฮาร์ดแวร์เสียหาย: 0 kB
AnonHugePages: 0 kB
ShmemHugePages: 0 กิโลไบต์
ShmemPmdMapped: 0 กิโลไบต์
FileHugePages: 0 กิโลไบต์
FilePmdMapped: 0 กิโลไบต์
CmaTotal: 0 กิโลไบต์
CmaFree: 0 กิโลไบต์
HugePages_Total: 0
HugePages_ฟรี: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
ขนาดหน้าใหญ่: 2048 kB
Hugetlb: 0 กิโลไบต์
DirectMap4k: 1335276 กิโลไบต์
DirectMap2M: 761856 กิโลไบต์

ps -aux --sort -rss|หัว -5

PID ของผู้ใช้ %CPU %MEM VSZ RSS TTY STAT คำสั่งเวลาเริ่มต้น
mysql 1715614 6.7 18.8 1763392 383344 ? SSL 22:21 0:01 /usr/sbin/mysqld
อะซิเตป+ 1686006 0.0 2.0 342620 41076 ? ส 19:44 0:02 /bin/php-cgi7.4
อะซิเตป+ 1686009 0.0 1.9 265576 39268 ? ส 19:44 0:02 /bin/php-cgi7.4
อะซิเตป+ 1686001 0.0 1.8 342152 38348 ? ส 19:44 0:04 /bin/php-cgi7.4

และเนื่องจากฉันคิดว่ามันเกี่ยวข้องกับตัวแปร mysql บางตัวฉันจึงรัน MySql Tunner และนี่คือคำแนะนำ:

ตัวแปรที่จะปรับ:
    innodb_buffer_pool_size (>= 391.3M) ถ้าเป็นไปได้
    innodb_log_file_size ควรเป็น (=16M) หากเป็นไปได้ ดังนั้นขนาดไฟล์บันทึกทั้งหมดของ InnoDB จะเท่ากับ 25% ของขนาดบัฟเฟอร์พูล

ดังนั้นฉันจะทดสอบเพื่อเพิ่มสิ่งนี้และจะโพสต์ผลลัพธ์

อัปเดต #3 เพิ่ม innodb_buffer_pool_size = 512M ถึงฉัน /etc/mysql/mysql.conf.d/mysqld.cnf ไฟล์และยังคงหยุดทำงาน บันทึกยังคงเหมือนเดิม :(

เป็นเรื่องปกติหรือไม่ที่หน่วยความจำ Total Large ของฉันถูกจัดสรรให้เป็นศูนย์เมื่อฉันรัน Show สถานะเครื่องยนต์ InnoDB;

----------------------
บัฟเฟอร์พูลและหน่วยความจำ
----------------------
** จัดสรรหน่วยความจำขนาดใหญ่ทั้งหมด 0 **
หน่วยความจำพจนานุกรมจัดสรร 1009720
บัฟเฟอร์พูลขนาด 32765
บัฟเฟอร์ฟรี 29298
หน้าฐานข้อมูล 3405
หน้าฐานข้อมูลเก่า 1276
แก้ไขหน้า db 0

แล้วตอนที่ฉันออกรายการล่ะ ตัวแปรเช่น 'innodb_%';

innodb_buffer_pool_size | 536870912
innodb_change_buffer_max_size | 25

ทำไมขนาดบัฟเฟอร์พูลไม่ตรงกันและสิ่งนี้คืออะไร innodb_change_buffer_max_size หมายถึง? ฉันควรตรวจสอบตัวแปรอะไรอีกบ้าง ขอบคุณอีกครั้ง

ตกลง. แก้ไขได้แล้ว. ไม่มีวิธีแก้ไขเฉพาะสำหรับพฤติกรรมประเภทนี้เนื่องจากเคอร์เนลที่ฆ่า mysql เนื่องจากใช้ RAM มากเกินไป และเมื่อคุณพยายามหาสาเหตุ ก็ไม่มีสาเหตุเฉพาะเจาะจง สิ่งเดียวที่คุณทำได้คือลองเล่นกับตัวแปรและทดสอบหลายๆ อย่าง สิ่งที่ฉันเรียนรู้:

• ฉันมีประสบการณ์สั้น ๆ ฉันไม่รู้ว่าไฟล์กำหนดค่า Myslq (/etc/mysql/mysql.conf.d/mysqlconf.d บน Ubuntu 20.04) ไม่แสดงตัวแปรมากนักเนื่องจากตัวแปรเหล่านี้ถูกตั้งค่าเป็นค่าเริ่มต้น ดังนั้นหากเรา ต้องการตั้งค่าอื่นเราต้องเพิ่มตัวแปรแต่ละตัวในไฟล์ปรับแต่งด้วยตนเอง

• ติดตั้งและเรียกใช้ mysql tuner (google it) เพื่อให้สามารถชี้ตำแหน่งที่จะเริ่มต้นสำหรับการตั้งค่าเฉพาะของคุณ ในกรณีของฉัน แนะนำให้เพิ่ม innodb_buffer_pool_size และ innodb_log_file_size ควรเท่ากับ 25% ของค่า innodb_buffer_pool_size เพื่อประสิทธิภาพที่ดีที่สุด เช่น innodb_buffer_pool_size = 1Gb innodb_log_file_size = 0.25Gb

• ฉันตั้งค่า innodb_buffer_pool_size = 512M เนื่องจากฉันมี RAM 2GB สิ่งนี้ทำให้เซิร์ฟเวอร์ใช้งานได้นานขึ้นเล็กน้อยก่อนที่จะหยุดทำงานอีกครั้ง นั่นคือจุดที่คุณต้องเริ่มเรียนรู้เพิ่มเติมเกี่ยวกับตัวแปร Mysql แต่ละตัวและทำการคำนวณตามขนาดฐานข้อมูลของคุณ

นี่คือไฟล์กำหนดค่า Mysql สุดท้ายของฉัน ดังนั้นคุณจึงลองกำหนดค่านี้ได้ฟรี เพิ่งรู้ว่าฐานข้อมูลของฉันมีขนาด 394MB ในขณะนี้:

[มายเอสคิวลด์]
ข้ามเข้าสู่ระบบถัง
ผู้ใช้ = mysql
ที่อยู่ผูก = 127.0.0.1
mysqlx-ผูกที่อยู่ = 127.0.0.1
myisam-recover-options = สำรองข้อมูล
log_error = /var/log/mysql/error.log
General_log = เปิด
general_log_file=/var/log/mysql/general.log

key_buffer_size = 1M
max_allowed_packet = 1M
thread_stack = 200K
thread_cache_size = 8
max_connect_errors = 100
max_connections = 100
#table_cache = 64
#thread_concurrency = 30
binlog_cache_size = 1M
net_buffer_length = 1M

default_storage_engine = InnoDB
innodb_buffer_pool_instances = 1 # ใช้ 1 อินสแตนซ์ต่อ 1GB ของขนาดพูล InnoDB
innodb_buffer_pool_size = 400M # ใช้ RAM มากถึง 70-80%
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 16M
innodb_stats_on_metadata = 0
#innodb_thread_concurrency = 0
innodb_read_io_threads = 40
innodb_write_io_threads = 40
innodb_buffer_pool_chunk_size = 10
innodb_lock_wait_timeout = 600
innodb_flush_log_at_trx_commit = 1
ความจุ innodb_io_=3000
innodb_io_capacity_max = 4000
innodb_buffer_pool_dump_pct = 80
innodb_flush_neighbors = 0
innodb_doublewrite = 0
innodb_change_buffer_max_size = 10
innodb_old_blocks_pct = 70
innodb_old_blocks_time = 5,000
innodb_use_native_aio = เปิด

# จำนวนวินาทีที่เซิร์ฟเวอร์รอกิจกรรมบนการเชื่อมต่อแบบโต้ตอบก่อนที่จะปิด
interactive_timeout = 600

# จำนวนวินาทีที่เซิร์ฟเวอร์รอกิจกรรมบนการเชื่อมต่อแบบไม่โต้ตอบก่อนที่จะปิด
wait_timeout = 600
net_read_timeout = 300
net_write_timeout = 300
connect_timeout = 1800

# การตั้งค่าตาราง
table_definition_cache = 1K
table_open_cache = 2K
table_open_cache = 2K
open_files_limit = 4000

max_heap_table_size = 100M
tmp_table_size=100M

#
# * การกำหนดค่าแบบสอบถามแคช
#
#query_cache_limit = 1M
#query_cache_size = 100M
#query_cache_size = 0
#query_cache_type = 0



# การตั้งค่าบัฟเฟอร์
join_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
sort_buffer_size = 128K
performance_schema = เปิด

หากขนาดฐานข้อมูลของฉันเพิ่มขึ้นตามเวลา (พวกเขาจะแน่นอน) ฉันจะต้องเพิ่มค่านี้:

key_buffer_size = 1M
max_connections = 100
innodb_buffer_pool_size=400M
join_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
sort_buffer_size = 128K

ฉันสามารถเพิ่ม innodb_buffer_pool_size เป็น 1Gb ได้ แต่นั่นหมายความว่าฉันจะต้องลดตัวแปรอื่นๆ และทดสอบอีกครั้งเพื่อทดสอบการตั้งค่าประสิทธิภาพที่ดีที่สุด (ฉันพูดยังไงถ้าคุณไม่รู้ จำเป็นต้องมีการวิจัยจำนวนมากเกี่ยวกับตัวแปรเหล่านี้) ในทางกลับกัน ฉันสามารถปล่อยให้ค่าเดียวกันนี้แม้ว่าฐานข้อมูลของฉันจะเพิ่มขึ้นตามเวลา แต่ Mysql จะเริ่มใช้หน่วยความจำ Swamp มากขึ้น ดังนั้นการสืบค้นจะช้าลงเล็กน้อยเมื่อเวลาผ่านไป

และอีกครั้ง คุณสามารถเพิ่ม RAM บนเซิร์ฟเวอร์ของคุณได้หากคุณสามารถจ่ายได้

หวังว่านี่จะช่วยใครก็ได้ฉันไม่มีประสบการณ์ดังนั้นฉันจึงโพสต์คำตอบนี้เพื่อเป็นข้อมูลอ้างอิงในอนาคตเช่นกัน :)