[กันลืม] ปรับแต่ง MySql/Mariadb ให้โหลดข้อมูลได้เร็วขึ้น

เพื่อให้ดาต้าเบส (Database) ทำงานได้อย่างเต็มประสิทธิภาพและสามารถใช้ทรัพยาการของเครื่องเซิร์ฟเวอร์ (Server) ได้เต็มศักยภาพของเครื่องเมื่อติดตั้งเสร็จก็จะควรมีการปรับแต่งค่าให้เหมาะสมกับการใช้งาน

• สำหรับ Linux ให้แก้ไขไฟล์ /etc/mysql/my.cnf
• สำหรับ Windows (XAMPP) ให้แก้ไขไฟล์ C:\xampp\mysql\bin\my.ini

มาเริ่มกันเลย! ปัจจุบัน MySql/Mariadb เวอร์ชั่นใหม่จะใช้เอนจินแบบ InnoDB เป็นค่าเริ่มต้น ฉะนั้นเราจะโฟกัสความสำคัญเฉพาะการตั้งค่า InnoDB โดยเฉพาะค่า innodb_buffer_pool_size ควรกำหนดให้ไม่เกิน 70-80% ของขนาด RAM มีตัวย่างการค่าคอนเฟิกดังนี้

(สมมุติสเปกเครื่องเซิร์ฟเวอร์ CPU 4 core  RAM 4 GB)

# InnoDB Settings
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 1     # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size         = 1G    # Use up to 70-80% of RAM
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 8M
innodb_log_file_size            = 256M
innodb_stats_on_metadata        = 0

คำสั่งคิวรีที่ใช้ตรวจสอบค่าที่เหมาะสมของ innodb_buffer_pool_size

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

เมื่อใช้ดาต้าเบสไปได้ 1-2 สัปดาห์ ให้ใช้คำสั่งคิวรีนี้เพื่อใช้ตรวจสอบว่าหน่วยความจำ GB จริงที่ถูกใช้โดย InnoDB Data ใน InnoDB Buffer Pool ในขณะนี้ถูกใช้งานไปเท่าไร

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

และมีค่าอื่นๆ อีกเช่น

join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
read_rnd_buffer_size = 1M

เสร็จแล้วให้บักทึก และ Restart การทำงาน MySql/Mariadb ใหม่อีกครั้ง

แนะนำบทความเพื่อศึกษาเพิ่มเติม : Tuning MySQL : สำรวจตัวเองและเข้าใจตัวแปร

อ้างอิง

• https://gist.github.com/fevangelou/fb72f36bbe333e059b66
• https://qastack.in.th/dba/27328/how-large-should-be-mysql-innodb-buffer-pool-size
• https://www.bunyiam.com/%E0%B8%A1%E0%B8%B2%E0%B8%9B%E0%B8%A3%E0%B8%B1%E0%B8%9A%E0%B9%81%E0%B8%95%E0%B9%88%E0%B8%87-mysql-%E0%B9%83%E0%B8%AB%E0%B9%89%E0%B9%80%E0%B8%A3%E0%B9%87%E0%B8%A7%E0%B9%81%E0%B8%A3%E0%B8%87%E0%B8%AA/