MySQL (MariaDB) กับขีดจำกัดการเชื่อมต่อสูงสุด

บางครั้งผู้อ่านที่เป็นโปรแกรมเมอร์ได้เขียนโค้ดอย่างรอบคอบ มีการตรวจสอบแล้วว่าใช้สูงสุดแค่ 1 connection ต่อ 1 ผู้ใช้เท่านั้น และผู้ใช้งานบนเว็บจริงๆที่ online บน production site ก็ไม่ได้มากมายเกินร้อย แต่ทำไมถึงขึ้นปัญหาเกี่ยวกับการเชื่อมต่อถึงขีดจำกัดได้.
ปัญหานี้ผู้เขียนเองก็เพิ่งจะมีโอกาสได้พบเป็นครั้งแรกจากการใช้ shared hosting. ที่ต้องบอกว่าพบเป็นครั้งแรกเพราะโดยทั่วไปที่พบมักเป็นปัญหาอื่น เช่น ผู้ใช้อื่นที่ใช้ host ร่วมกันเขียนโค้ดกินทรัพยากรเช่น RAM จน server ล่ม แม้แต่ HTML ก็เรียกดูไม่ได้, หรือบางกรณีก็แย่งกันใช้งาน DB server จน down หรือ table พังไปทั้งหมด ทำให้เรียกได้แต่ HTML หรือ PHP ที่ไม่ใช้ DB เป็นต้น.

ทางผู้เขียนจะขออธิบายและแสดงผลให้ดูตามลำดับของปัญหาดังนี้

Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1040] Too many connections

ปัญหานี้เกิดจากมีการเรียกใช้งานหรือเปิดการเชื่อมต่อไปยังเซิร์ฟเวอร์ฐานข้อมูล โดยรวมทั้งเซิร์ฟเวอร์แล้วจำนวนมากเกินกว่าจำนวนสูงสุดของ max_connections ที่ทาง web hosting กำหนดไว้ให้. ทดสอบได้บนเครื่องของเราเอง เริ่มจากการตรวจสอบโดยใช้คำสั่งนี้บน phpMyAdmin หรือโปรแกรมใดๆที่รันคำสั่งบน MySQL, MariaDB ได้.

SHOW VARIABLES WHERE `variable_name` = 'max_connections'

เมื่อทดลองแล้ว จะได้ผลลัพธ์คือตัวเลขของจำนวนการเชื่อมต่อสูงสุดที่ทาง host นั้นๆอนุญาตให้โดยรวมทั้ง server. จากการทดลองบน localhost ของตัวเองคือ 151. หมายความว่า MariaDB ที่ติดตั้งบนเครื่องของผู้เขียนจะอนุญาตให้เชื่อมต่อโดยรวมได้สูงสุด 151 และถ้าหากตัวเลขนี้เป็นผลจากการรันบน web hosting จริงๆ ก็หมายความว่า web hosting นั้นๆอนุญาตให้โดยรวมต่อทั้ง server เป็นจำนวนเท่านั้นไม่ว่าผู้ใช้จะมีกี่รายก็ตาม.

ต่อมาให้เราใช้โค้ด PHP ต่อไปนี้ทดลองรันแล้วดูผลลัพธ์ โดยก่อนทดลอง จำเป็นต้องเปิดแสดง error ให้หมดเสียก่อน.

<?php
$dsn = 'mysql:dbname=test;host=localhost;charset=utf8mb4';
$user = 'user';
$password = 'pass';
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_STRINGIFY_FETCHES => true,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
];

$dbh = new PDO($dsn, $user, $password, $options);
for ($i = 2; $i <= 151; ++$i) {
    ${'dbh' . $i} = new PDO($dsn, $user, $password, $options);
}// endfor;


$sql = 'SHOW STATUS WHERE `variable_name` LIKE \'Threads%\'';
$Sth = $dbh->prepare($sql);
$Sth->execute();
$result = $Sth->fetchAll();
unset($sql, $Sth);
var_dump($result);
unset($result);

สิ่งที่ผู้อ่านต้องทำจากโค้ดด้านบนคือ แก้ไขค่าต่างๆ เช่น dbname, host, user, password ให้ตรงกับที่ใช้งาน จากนั้นแก้ไขตัวเลข 151 ให้เท่ากันกับตัวเลขที่ได้รับจากค่าจำนวนการเชื่อมต่อสูงสุด ของ MySQL, MariaDB ด้านบน. ทำการทดลองรันครั้งแรกควรจะได้ผลปกติไม่มี error ใดๆ และให้แก้ไขจากตัวเลข 151 ไปเป็นตัวเลขที่มากกว่าจำนวนการเชื่อมต่อสูงสุดของคุณ เช่น 152 สำหรับเครื่องของผม แล้วทดลองรันอีกครั้ง โดยคราวนี้จะเกิด error ขึ้นคือ Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1040] Too many connections.

Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1203] User user already has more than 'max_user_connections' active connections

ปัญหานี้เกิดจากมีการเรียกใช้งานหรือเปิดการเชื่อมต่อไปยังเซิร์ฟเวอร์ฐานข้อมูล จำนวนต่อ account บน host เกินจำนวนสูงสุดที่ทาง web hosting ได้กำหนดไว้ให้. ทดสอบได้บนเครื่องของเราเอง โดยการแก้ไข my.cnf หรือ my.ini ให้มีค่า max_user_connections ต่ำๆ เช่น 10 เป็นต้น. จากนั้นใช้โค้ดเดียวกันกับด้านบนทดลองรันแล้วจะได้ผลออกมาคือ Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1203] User user already has more than 'max_user_connections' active connections.

กลับมาที่กรณีที่มีปัญหากับ web hosting นั้นๆ 🐤 จากการทดลองด้วยตัวเองเหล่านี้ ทำให้พบว่าฝ่าย support นั้นไม่มีความรู้ความเข้าใจ จึงได้ยกคำตอบที่ไม่เกี่ยวกันมาตอบปัญหา เช่นไปยกเอาค่า Threads_created มาบอกว่าเป็นจำนวนการเชื่อมต่อสูงสุดของ server ซึ่งไม่ตรงตามที่อธิบายโดยทั้ง MySQL และ MariaDB เอง อีกทั้งยังยกเรื่อง performance ปริมาณการโหลด ซึ่งไม่เกี่ยวกันมาอีก เพราะปัญหาจำนวนการเชื่อมต่อที่มากเกินอนุญาตนั้น อาจมีผลกับการที่ server ทำงานหนักมากและจำนวนอนุญาตไว้มาก หรือจำนวนการเชื่อมต่อ อนุญาตไว้เพียงเล็กน้อยและ server ยังไม่ทันได้ทำงานหนักเลยก็เป็นไปได้ทั้งสองแบบ.

สรุป

ดังนั้นสิ่งที่ผู้อ่านควรตระหนักเมื่อเลือกใช้ shared hosting (อาจจะกรณีเช่า host ใหม่หรือย้ายไป host ใหม่) ในเรื่องที่เกี่ยวกับประเด็นนี้ก็คือ จำนวน Threads_connected. ตรวจสอบโดยใช้คำสั่ง MySQL ต่อไปนี้.

SHOW STATUS WHERE `variable_name` = 'Threads_connected'

จำนวนของค่านี้ ให้เราทดลองเรียกใช้เมื่อยังไม่ได้เปิดเว็บให้ online เต็มที่. กล่าวคือติดตั้งโดเมนทดลองแล้วเรียกใช้เพียงคนเดียว จำนวน Threads_connected จะสะท้อนทั้งโฮสท์ว่ามีคนใช้งานร่วมกับเราอยู่เท่าไหร่.

ต่อมาคือจำนวน max_connections. ให้ตรวจสอบค่านี้โดยใช้คำสั่ง SQL ด้านบนในหัวข้อ Too many connections. จำนวนค่านี้จะหมายถึงจำนวนสูงสุดต่อทั้ง server ที่ทางผู้ให้บริการกำหนดไว้ ซึ่งถ้าจำนวน Threads_connected ปริ่มๆจะเต็มอยู่แล้ว ก็พิจารณาขอย้าย server โดยให้เหตุผลตามนี้ไปว่ามันใกล้จะเต็มหรืออาจขอยกเลิกบริการและขอคืนเงินตามเงื่อนไข.

สุดท้ายคือจำนวน max_user_connections. ให้ตรวจสอบค่านี้โดยใช้คำสั่ง SQL ต่อไปนี้.

SHOW VARIABLES WHERE `variable_name` = 'max_user_connections'

คำสั่งนี้จะเป็นจำนวนการเชื่อมต่อฐานข้อมูลสูงสุดต่อ 1 ผู้ใช้คือ account ของคุณบน shared hosting. การตรวจสอบจะทำได้ยากสักหน่อยเพราะจำนวน Threads_connected จะเป็นจำนวนรวมต่อทั้ง server ซึ่งคุณอาจใช้โค้ดด้านบนตรวจสอบก็ได้ โดยกำหนดจำนวน loop ของ for ให้พอดีหรือเกิน max_user_connections เพื่อจะได้ทราบว่าสูงสุดที่เกินนั้นตรงความจริงหรือไม่.