ปรับแต่ง MySQL/MariaDB เบื้องต้นด้วย innodb_buffer_pool_size

MySQL และ MariaDB ที่ติดตั้งจากแพ็กเกจมาตรฐานตั้งค่าหน่วยความจำไว้ต่ำมาก innodb_buffer_pool_size ซึ่งเป็นพื้นที่ใน RAM สำหรับเก็บข้อมูลและ Index ของตาราง InnoDB มีค่าเริ่มต้นเพียง 128 MB ถ้าฐานข้อมูลของคุณใหญ่กว่านั้น ทุก Query จะต้องอ่านจากดิสก์ซ้ำแล้วซ้ำอีก ทำให้เว็บช้าทั้งที่ RAM ของเซิร์ฟเวอร์ยังว่างอยู่มาก

บทความนี้อธิบายวิธีคำนวณขนาด Buffer Pool ที่เหมาะกับเครื่อง ตรวจว่าค่าปัจจุบันพอหรือไม่ ปรับค่าทั้งแบบออนไลน์และแบบถาวร และค่าที่เกี่ยวข้องอีกเล็กน้อยที่ควรดูพร้อมกัน เป็นการปรับแต่งเบื้องต้นที่ได้ผลมากที่สุดสำหรับเว็บไซต์ทั่วไป เช่น WordPress หรือร้านค้าออนไลน์

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

• MySQL 8.0/8.4 หรือ MariaDB 10.x/11.x บน Linux และสิทธิ์ root
• ทราบขนาด RAM ของเครื่องและบริการอื่นที่รันอยู่ด้วย (Web Server, PHP-FPM, Redis ฯลฯ)
• ตารางส่วนใหญ่ใช้ Engine InnoDB (ค่าเริ่มต้นของทั้ง MySQL และ MariaDB รุ่นปัจจุบัน)

ขั้นตอนที่ 1: ดูข้อมูลพื้นฐานของเครื่อง

free -h
nproc

               total        used        free      shared  buff/cache   available
Mem:           7.7Gi       2.1Gi       1.2Gi        60Mi       4.4Gi       5.3Gi
Swap:          2.0Gi          0B       2.0Gi

ดูคอลัมน์ total และ available ถ้าเครื่องใช้ Swap อยู่มากแล้ว ต้องแก้เรื่อง RAM ไม่พอก่อน การเพิ่ม Buffer Pool จะยิ่งทำให้แย่ลง ดู ตรวจสอบ CPU, RAM และ Load Average ของ Linux Server

ขั้นตอนที่ 2: ดูขนาดข้อมูล InnoDB และค่าปัจจุบัน

sudo mysql

SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024) AS innodb_mb
FROM information_schema.tables
WHERE engine = 'InnoDB';

SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb;

ตัวอย่างผลลัพธ์ข้อมูล 1,450 MB แต่ Buffer Pool มีเพียง 128 MB แปลว่ามีข้อมูลน้อยกว่า 10% เท่านั้นที่อยู่ใน RAM ได้ Buffer Pool ในอุดมคติควรใหญ่กว่าขนาดข้อมูลที่ใช้งานบ่อยเล็กน้อย ไม่จำเป็นต้องใหญ่กว่าข้อมูลทั้งหมด

ขั้นตอนที่ 3: วัดอัตรา Cache Hit

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_read_requests   98231455
Innodb_buffer_pool_reads           1203344

read_requests คือจำนวนครั้งที่ขออ่านข้อมูล ส่วน reads คือจำนวนครั้งที่ไม่พบใน RAM และต้องไปอ่านจากดิสก์ คำนวณอัตราพลาดได้ดังนี้

SELECT ROUND(100 * r.v / q.v, 2) AS miss_percent
FROM (SELECT VARIABLE_VALUE AS v FROM performance_schema.global_status
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') r,
     (SELECT VARIABLE_VALUE AS v FROM performance_schema.global_status
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') q;

บน MariaDB ให้เปลี่ยน performance_schema.global_status เป็น information_schema.GLOBAL_STATUS (หรือคำนวณด้วยมือจากตัวเลขข้างบน) ตัวอย่างนี้ได้ประมาณ 1.2% ถ้าเกิน 1% อย่างต่อเนื่องหลังเครื่องทำงานมาหลายชั่วโมง แปลว่า Buffer Pool เล็กเกินไป ค่านี้นับสะสมตั้งแต่เริ่มบริการ จึงควรวัดหลังจากฐานข้อมูลทำงานมาระยะหนึ่ง ไม่ใช่ทันทีหลังรีสตาร์ต

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

หลักคิดคือให้ Buffer Pool ใหญ่ที่สุดเท่าที่ทำได้ โดยยังเหลือ RAM ให้ระบบปฏิบัติการ บริการอื่น และหน่วยความจำต่อการเชื่อมต่อของฐานข้อมูลเอง

ไม่ต้องตั้งให้ใหญ่กว่าขนาดข้อมูล InnoDB ในขั้นตอนที่ 2 มากนัก ถ้าข้อมูลทั้งหมดมี 1.5 GB การตั้ง 6 GB ไม่ได้ช่วยอะไร ตั้งราว 2 GB ก็พอและมีที่ให้ข้อมูลโตได้ ตัวเลขในตารางเป็นจุดเริ่มต้น ควรดูผลจริงหลังปรับเสมอ

หมายเหตุ: ถ้าตั้งค่าสูงเกินไปจนระบบไม่มี RAM เหลือ Linux จะเรียก OOM Killer มาหยุดโปรเซสที่ใช้หน่วยความจำมากที่สุด ซึ่งมักเป็นฐานข้อมูลเอง ทำให้เว็บล่มกะทันหัน ปรับขึ้นทีละขั้นดีกว่าตั้งสูงครั้งเดียว

ขั้นตอนที่ 5: ปรับแบบออนไลน์ (ไม่ต้องรีสตาร์ต)

ทั้ง MySQL 8 และ MariaDB 10.2 ขึ้นไปปรับ Buffer Pool ขณะทำงานได้ ตัวอย่างตั้งเป็น 2 GB

SET GLOBAL innodb_buffer_pool_size = 2147483648;

ระบบจะปรับขนาดในเบื้องหลัง ตรวจความคืบหน้าด้วย

SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

เมื่อเสร็จจะแสดงข้อความว่าการปรับขนาดเสร็จสิ้น (ข้อความต่างกันเล็กน้อยตามเวอร์ชัน) MySQL จะปัดค่าให้เป็นผลคูณของ innodb_buffer_pool_chunk_size x innodb_buffer_pool_instances อัตโนมัติ ค่าที่ได้จริงจึงอาจต่างจากที่สั่งเล็กน้อย ค่าที่ตั้งด้วย SET GLOBAL จะหายเมื่อรีสตาร์ต จึงต้องทำขั้นตอนถัดไปด้วย

ขั้นตอนที่ 6: บันทึกแบบถาวรในไฟล์คอนฟิก

ก่อนแก้ ค้นว่ามีการตั้งค่านี้อยู่ที่ไหนแล้วหรือยัง เพื่อไม่ให้ประกาศซ้ำ

sudo grep -rn "innodb_buffer_pool_size" /etc/mysql /etc/my.cnf /etc/my.cnf.d 2>/dev/null

สำรองไฟล์เดิม แล้วเพิ่มใต้ [mysqld]

sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

[mysqld]
innodb_buffer_pool_size = 2G

รีสตาร์ตในช่วงที่มีผู้ใช้น้อย (ชื่อบริการ mysql, mariadb หรือ mysqld ตามระบบ)

sudo systemctl restart mysql

ค่าอื่นที่ควรดูพร้อมกัน

ขนาด Redo Log

เมื่อ Buffer Pool ใหญ่ขึ้น Redo Log ที่เล็กเกินไปทำให้ต้อง Flush ข้อมูลลงดิสก์บ่อย MySQL 8.0.30 ขึ้นไปใช้ innodb_redo_log_capacity (ค่าเริ่มต้น 100 MB) ส่วน MariaDB และ MySQL รุ่นก่อนหน้าใช้ innodb_log_file_size สำหรับเว็บทั่วไป ค่าประมาณ 256 MB ถึง 1 GB ก็เพียงพอ

# MySQL 8.0.30 ขึ้นไป
innodb_redo_log_capacity = 512M

# MariaDB
innodb_log_file_size = 512M

max_connections

การเชื่อมต่อแต่ละรายการใช้หน่วยความจำของตัวเองนอกเหนือจาก Buffer Pool การตั้ง max_connections สูงเกินจริง เช่น 1,000 บนเครื่อง RAM 4 GB เพิ่มความเสี่ยง RAM หมดเมื่อมีผู้ใช้พุ่งสูง ดูจำนวนสูงสุดที่เคยใช้จริงด้วย

SHOW GLOBAL STATUS LIKE 'Max_used_connections';

แล้วตั้ง max_connections ให้สูงกว่าค่านี้พอสมควร แทนการตั้งสูงไว้ก่อน

innodb_dedicated_server (MySQL 8 เท่านั้น)

สำหรับเครื่องที่รัน MySQL อย่างเดียว ตั้ง innodb_dedicated_server = ON ให้ MySQL คำนวณ Buffer Pool และ Redo Log จาก RAM ของเครื่องเอง ห้ามใช้บนเครื่องที่มีเว็บหรือบริการอื่นรวมอยู่ด้วย เพราะจะจอง RAM มากเกินไป MariaDB ไม่มีตัวเลือกนี้

ตรวจสอบผลลัพธ์

SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_gb;

ต้องได้ค่าตามที่ตั้งหลังรีสตาร์ต จากนั้นทิ้งไว้หนึ่งถึงสองวันทำการแล้ววัดอัตราพลาดตามขั้นตอนที่ 3 อีกครั้ง ควรลดลงต่ำกว่า 1% และดูว่า free -h ยังมี available เหลือพอ ไม่มีการใช้ Swap เพิ่ม ถ้าเปิด Slow Query Log ไว้ จะเห็น Query ช้าน้อยลงด้วย ดู เปิด Slow Query Log เพื่อหา Query ที่ทำให้ฐานข้อมูลช้า

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

ฐานข้อมูลเริ่มไม่ขึ้นหลังแก้คอนฟิก

มักเกิดจากพิมพ์หน่วยผิด (ต้องเป็น 2G หรือ 2048M ไม่ใช่ 2GB) หรือตั้งค่าเกิน RAM ที่มี ดูข้อความด้วย sudo journalctl -u mysql -n 50 แก้ค่าหรือคืนไฟล์ .bak แล้วเริ่มใหม่

ฐานข้อมูลถูกหยุดกะทันหันเป็นระยะ

ตรวจว่าเป็น OOM Killer หรือไม่ด้วย sudo journalctl -k | grep -i "out of memory" ถ้าใช่ ให้ลด Buffer Pool หรือ max_connections หรือเพิ่ม RAM ของเครื่อง การเพิ่ม Swap ช่วยกันล่มได้ระดับหนึ่งแต่ไม่ได้แก้ต้นเหตุ ดู เพิ่ม Swap File บน Linux Server

ปรับแล้วแต่เว็บยังช้าเหมือนเดิม

ถ้าอัตราพลาดต่ำอยู่แล้วตั้งแต่แรก หน่วยความจำไม่ใช่คอขวด ให้หาต้นเหตุจาก Query ที่ขาด Index หรือจากฝั่ง PHP และ Web Server แทน

ค่าที่ตั้งไม่มีผล ยังเป็น 128 MB

มีการประกาศค่านี้ซ้ำในไฟล์ที่ถูกอ่านทีหลัง ใช้คำสั่ง grep ในขั้นตอนที่ 6 หาให้ครบ หรือดูค่าทั้งหมดที่อ่านได้จากไฟล์คอนฟิกด้วย sudo my_print_defaults mysqld ค่าที่อยู่ท้ายสุดคือค่าที่มีผล

หากเครื่องต้องการ RAM เพิ่มหลังปรับจูนแล้ว สามารถดูวิธีปรับสเปคได้ที่ การ Upgrade/Downgrade สเปค Cloud Server แบบอัตโนมัติบน TDC หรือติดต่อทีมงาน THAI DATA CLOUD ได้ที่ https://thaidata.cloud/contact/