แก้ 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;

ขั้นตอนที่ 5: แก้ที่ฝั่งแอป ซึ่งคือต้นเหตุที่แท้จริง

คำนวณจากด้านแอปแล้วตั้งให้ตรงกัน จำนวนการเชื่อมต่อสูงสุดที่แอปจะเปิด = จำนวนเครื่อง × จำนวน Worker ต่อเครื่อง × ขนาด Pool ต่อ Worker

PHP-FPM

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

pm = dynamic
pm.max_children = 50

PHP เปิดการเชื่อมต่อหนึ่งช่องต่อหนึ่ง Worker ดังนั้นเว็บสองเครื่องที่ตั้ง pm.max_children = 50 จะเปิดสูงสุด 100 ช่อง ตั้ง max_connections ให้มากกว่านี้พอสมควร

Node.js

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

อย่าตั้ง connectionLimit สูงเกินจำเป็น ฐานข้อมูลทำงานได้เร็วที่สุดเมื่อมีการเชื่อมต่อที่ทำงานพร้อมกันประมาณสองถึงสี่เท่าของจำนวนคอร์ ไม่ใช่ยิ่งมากยิ่งดี

ขั้นตอนที่ 6: ใช้ตัวรวมการเชื่อมต่อเมื่อจำเป็น

เมื่อมีเว็บเซิร์ฟเวอร์หลายเครื่องและแต่ละเครื่องเปิด Pool ของตัวเอง จำนวนรวมจะสูงมาก ProxySQL ช่วยรวมให้เหลือจำนวนที่ฐานข้อมูลรับไหว

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

แอปเชื่อมต่อไปที่ ProxySQL แทน แล้ว ProxySQL เปิดการเชื่อมต่อจริงไปยัง MySQL เพียงจำนวนที่กำหนด นอกจากนี้ยังแยกงานอ่านไปยังตัวสำรองได้ด้วย

ขั้นตอนที่ 7: เฝ้าดูต่อเนื่อง

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

ค่า Threads_running สำคัญกว่า Threads_connected มันคือจำนวนที่กำลังทำงานจริง หากค่านี้สูงกว่าจำนวนคอร์มาก แปลว่าฐานข้อมูลรับงานไม่ทัน ซึ่งการเพิ่ม max_connections จะยิ่งทำให้แย่ลง ต้องไปแก้ที่ Query แทน

ปัญหาที่พบบ่อย

เพิ่ม max_connections แล้วเครื่องหมดหน่วยความจำ

คำนวณตามขั้นตอนที่ 3 ใหม่ และตรวจว่า sort_buffer_size กับ join_buffer_size ไม่ได้ตั้งไว้สูงเกินไป ค่าเหล่านี้จัดสรรต่อการเชื่อมต่อ การตั้ง 16 MB บน 500 การเชื่อมต่อคือ 8 GB

การเชื่อมต่อเต็มทั้งที่ผู้ใช้น้อย

แอปเปิดแล้วไม่ปิด ตรวจสถานะ Sleep ตามขั้นตอนที่ 2 และตรวจว่าโค้ดปิดการเชื่อมต่อในทุกเส้นทาง รวมถึงเมื่อเกิดข้อผิดพลาด

ขึ้น Host is blocked because of many connection errors

เครื่องนั้นเชื่อมต่อล้มเหลวเกิน max_connect_errors ปลดบล็อกด้วย FLUSH HOSTS; แล้วหาสาเหตุว่าทำไมจึงล้มเหลว มักเป็นปัญหาเครือข่ายหรือรหัสผ่านผิด

ตั้ง wait_timeout สั้นแล้วแอปเจอ connection lost

Pool ของแอปถือการเชื่อมต่อที่ฐานข้อมูลปิดไปแล้ว ตั้งให้ Pool ตรวจสอบก่อนใช้งาน หรือตั้งอายุการเชื่อมต่อใน Pool ให้สั้นกว่า wait_timeout

ต้องการให้ทีมวิศวกรช่วยปรับจูนฐานข้อมูลให้รองรับทราฟฟิกจริง ติดต่อ THAI DATA CLOUD ได้ที่ https://thaidata.cloud/contact/