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

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

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;

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_read_requests   98231455
Innodb_buffer_pool_reads           1203344

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;

ขั้นตอนที่ 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: บันทึกแบบถาวรในไฟล์คอนฟิก

ระบบ	ไฟล์ที่แนะนำ
Ubuntu + MySQL	/etc/mysql/mysql.conf.d/mysqld.cnf
Ubuntu + MariaDB	/etc/mysql/mariadb.conf.d/50-server.cnf
AlmaLinux/Rocky + MariaDB	/etc/my.cnf.d/mariadb-server.cnf
AlmaLinux/Rocky + MySQL	/etc/my.cnf.d/mysql-server.cnf

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

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/

อ่านเพิ่มเติมเกี่ยวกับ innodb

• เอกสารทางการของ MariaDB
• นำเข้าไฟล์ SQL ขนาดใหญ่ผ่าน Command Line
• คู่มือการใช้งานทั้งหมดของ THAI DATA CLOUD