แก้ Too many connections และวางแผน max_connections

ข้อความ ERROR 1040: Too many connections ทำให้เว็บล่มทั้งระบบทันที และวิธีแก้ที่คนทำเป็นอันดับแรกคือเพิ่ม max_connections ซึ่งบางครั้งทำให้แย่ลง เพราะแต่ละการเชื่อมต่อใช้หน่วยความจำ การตั้งสูงเกินไปทำให้เครื่องหมดหน่วยความจำและถูกระบบฆ่าทิ้งทั้งกระบวนการ

คู่มือนี้หาสาเหตุที่แท้จริงก่อน แล้วคำนวณค่าที่เหมาะสมจากหน่วยความจำที่มีจริง พร้อมตั้งค่าฝั่งแอปซึ่งมักเป็นต้นเหตุที่แท้จริง

สิ่งที่ต้องเตรียม

• MySQL หรือ MariaDB พร้อมสิทธิ์ผู้ดูแล
• ข้อมูลว่ามีเว็บเซิร์ฟเวอร์กี่เครื่องและแต่ละเครื่องเปิดการเชื่อมต่อเท่าไร

เตรียมช่องทางสำรอง MySQL สำรองการเชื่อมต่อไว้หนึ่งช่องสำหรับผู้ใช้ที่มีสิทธิ์ SUPER เสมอ ทำให้ผู้ดูแลเข้าได้แม้การเชื่อมต่อเต็ม

mysql -u root -p -e "SHOW PROCESSLIST;"

ขั้นตอนที่ 1: ดูสถานะปัจจุบัน

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Connection_errors_max_connections';
SHOW STATUS LIKE 'Aborted_connects';

ตัวเลขที่บอกอะไรได้

• Max_used_connections คือจุดสูงสุดที่เคยใช้ เทียบกับ max_connections เพื่อดูว่าเหลือที่ว่างแค่ไหน
• Connection_errors_max_connections ถ้ามากกว่าศูนย์แปลว่าเคยเต็มจริง
• Threads_connected คือจำนวนปัจจุบัน

ดูเวลาที่เคยแตะจุดสูงสุด

SHOW STATUS LIKE 'Max_used_connections_time';

ขั้นตอนที่ 2: ดูว่าการเชื่อมต่อเหล่านั้นทำอะไรอยู่

SELECT command, COUNT(*) AS n
FROM information_schema.processlist
GROUP BY command ORDER BY n DESC;

-- การเชื่อมต่อที่ไม่ได้ทำอะไรเลยนาน ๆ
SELECT id, user, host, db, time, state, LEFT(info, 80) AS query
FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 60
ORDER BY time DESC;

หากการเชื่อมต่อส่วนใหญ่อยู่ในสถานะ Sleep แปลว่าปัญหาไม่ใช่ฐานข้อมูลช้า แต่คือแอปเปิดการเชื่อมต่อแล้วไม่ปิด นี่คือสาเหตุที่พบบ่อยที่สุด

-- จัดกลุ่มตามต้นทาง เพื่อหาว่าเครื่องไหนเปิดมากที่สุด
SELECT SUBSTRING_INDEX(host, ':', 1) AS client, COUNT(*) AS n
FROM information_schema.processlist
GROUP BY client ORDER BY n DESC;

ขั้นตอนที่ 3: คำนวณค่าที่เหมาะสม

หน่วยความจำที่ MySQL ใช้แบ่งเป็นสองส่วน ส่วนกลางที่ใช้ร่วมกัน และส่วนที่จัดสรรต่อการเชื่อมต่อ สูตรคร่าว ๆ

SELECT
  ROUND((@@innodb_buffer_pool_size + @@key_buffer_size)/1024/1024) AS global_mb,
  ROUND((@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
       + @@join_buffer_size + @@binlog_cache_size + @@thread_stack)/1024/1024, 2) AS per_conn_mb,
  @@max_connections;

หน่วยความจำสูงสุดที่อาจใช้ = global_mb + (per_conn_mb × max_connections) ค่านี้ต้องน้อยกว่า RAM ที่มี หักส่วนของระบบปฏิบัติการและโปรแกรมอื่นแล้ว

ตัวอย่าง เครื่อง 8 GB ที่ตั้ง buffer pool 4 GB และแต่ละการเชื่อมต่อใช้ 4 MB

• เหลือให้การเชื่อมต่อประมาณ 2.5 GB หลังหักระบบปฏิบัติการและ Nginx
• 2560 MB หารด้วย 4 MB ได้ประมาณ 640
• ตั้งเผื่อความปลอดภัยที่ 300-400 ซึ่งมากพอสำหรับเว็บทั่วไปอยู่แล้ว

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
max_connections = 400
max_connect_errors = 100000

# ปิดการเชื่อมต่อที่ไม่ได้ใช้งานเอง
wait_timeout = 300
interactive_timeout = 600

sudo systemctl restart mysql

ปรับชั่วคราวโดยไม่ต้องรีสตาร์ตได้ เหมาะกับตอนฉุกเฉิน

SET GLOBAL max_connections = 400;

ขั้นตอนที่ 4: ตั้ง wait_timeout ให้เหมาะสม

ค่าเริ่มต้นคือ 28800 วินาที หรือ 8 ชั่วโมง ซึ่งนานเกินไปมาก การเชื่อมต่อที่แอปลืมปิดจะค้างอยู่ทั้งวัน

ตั้งเป็น 300 วินาทีเหมาะกับเว็บทั่วไป แต่ต้องระวังสองเรื่อง หนึ่ง หากแอปใช้ Connection Pool ค่านี้ต้องมากกว่าเวลาที่ Pool ตรวจสอบการเชื่อมต่อ ไม่เช่นนั้นแอปจะได้การเชื่อมต่อที่ถูกปิดไปแล้วและเกิดข้อผิดพลาด สอง งานที่ทำงานนาน เช่น การสำรองข้อมูล ให้ตั้งค่าเฉพาะการเชื่อมต่อนั้นแทน

-- ตั้งเฉพาะเซสชันปัจจุบัน
SET SESSION wait_timeout = 28800;

sudo nano /etc/php/8.3/fpm/pool.d/www.conf

pm = dynamic
pm.max_children = 50

const pool = mysql.createPool({
  host: '10.0.0.21',
  connectionLimit: 20,
  waitForConnections: true,
  queueLimit: 0,
  idleTimeout: 60000,
  enableKeepAlive: true,
})

sudo apt install -y proxysql
sudo systemctl enable --now proxysql

watch -n 5 "mysql -e \"SHOW STATUS WHERE Variable_name IN ('Threads_connected','Threads_running','Max_used_connections')\""