tuning-primer.sh [TEMP TABLES]

เริ่มเรื่องจากการรัน tuning-primer.sh เพื่อดูการทำงาน/parameters ของ MySQL เป็นอย่างไรบ้าง ?
พบว่ามีข้อความสีแดงแจ้ง notice เรื่องการ create temporary tables on Disk ในสัดส่วนที่เยอะ หลังจากเพิ่ง restart MySQL ไปเพื่อ enable slow_query_log_file ประมาณ 5 นาทีได้ ตามรูป

tuning-primer.sh result with parameter tmp_table_size, max_heap_table_size

ที่ใช้งานอยู่ยังเป็น MySQL version: 5.5.54-log MySQL Community Server (GPL) by Remi บน vps Centos 5.11 i386 ไม่ได้ลงเครื่องใหม่ที 😯 ลองค้นหาจะปรับลดค่านี้ได้อย่างไรบ้าง ?

ก็ไปเจอว่าลอง double value tmp_table_size, max_heap_table_size บน /etc/my.cnf หากเป็น mariadb ก็ mariadb-server.cnf หรือ ไฟล์ cnf ที่สร้างขึ้นมาเองก็แล้วแต่นะครับ

ลองปรับเป็นเพิ่มเป็น 2 เท่า / 4 เท่าก็ไม่ช่วยลดจำนวนการ Created_tmp_disk_tables หลังจาก restart ด้านล่าง

อธิบายเพิ่ม Created_tmp_tables คือจำนวนที่สร้าง tmp_tables ทั้งหมด (disk+memory)

ดังนั้นคิดว่าค่านี้ไม่ช่วยในการลดจำนวน Created_tmp_disk_tables ก็ค้นหาต่อ
พบว่า query ใดก็ตามที่มีการใช้งานที่ต้อง sort ข้อมูล เช่น function distinct,group by, order by ที่มีการ rand()
[อันนี้ไม่ได้หาข้อมูลแบบละเอียด ว่ามี fn อะไรบ้าง เอาหลักๆ แค่นี้พอนะ เพราะแค่เกริ่นนำ ค่อย explain query ดูกันเองนะ]
ก็ต้องมีการใช้ temporary table เข้ามาช่วยในการทำงาน หาก output result ใช้ memory

  • น้อยกว่า min(max_heap_table_size,tmp_table_size) ก็สร้างบน memory
  • หากมากกว่าก็สร้างบน disk

ยกเว้นว่ามี columns เป็น type BLOB or TEXT จะสร้างบน disk เท่านั้น

WordPress Tables contain BLOB or TEXT

เนื่องจาก vps นี้ใช้งาน MySQL เฉพาะ WordPress  เท่านั้น ดังนั้นเลยไปดูกันว่า wp มี table ไหนที่มี column เป็น 2 data type ข้างต้น

ผมใช้ prefix=’mr_’ และ wordpress 4.9.1  ใหม่สุดตอนนี้เลย ไปดู WordPress Table Overview ตอนนี้เป็น 4.4.2 ไม่เป็นไรเราต้องการดูหลักๆ ว่าอันไหนเป็น 12 tables default  (ไม่ใช่ plugin ที่ติดตั้งเพิ่ม)

  • wp_commentmeta
  • wp_comments
  • wp_links
  • wp_options
  • wp_postmeta
  • wp_posts
  • wp_terms
  • wp_termmeta
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_usermeta
  • wp_users

บรื๋อ… 😕 ของ WordPress เองก็มีการใช้ data type: text สำหรับเก็บข้อมูล 64k bytes เลย
ไม่เป็นไร…เราค่อยไปดูต่อว่าจะมี top query อะไรบ้าง ? ใช้เวลาเท่าไหร่ / tuning ได้อย่างไร ? หรือเป็น query พวกที่ต้องใช้ tmp_tables หรือไม่ ?

สำหรับกรณีหากมีค่า Created_tmp_disk_tables สูง + query ทำงานช้า + การปรับแก้ data type column ยุ่งแน่ เพราะต้องตามมาปรับแก้อยู่เรื่อยไป หากมี WordPress / Plugin version ใหม่ ให้อัพเดท หรือติดตั้ง Plugin ใหม่ก็ดี
แนวทางแก้ไขผ่าน OS คือ mount tmpfs บน tmpdir (ใน /etc/my.cnf) เลย โดยมีรายละเอียดเพิ่มเติมดังนี้

  • ไม่ใช้ filesystem ramfs เพราะว่าจะขยาย size ไปเรื่อยๆ จน RAM เต็มทำให้กระทบงานอย่างอื่น
  • ต้องประมาณขนาด output result จาก query ต่าง ๆที่เกิดขึ้น เพื่อกัน RAM มา mount fs ให้เพียงพอ
  • ตัวอย่างของผม vps RAM 2GB, db ขนาดแค่ 10MB และใน code คงไม่มีการเขียนแบบ SQL cartesian join (All to All join)
    • เริ่มต้นสร้าง tmpfs ที่ 100MB ให้ใหญ่ไว้ก่อน แต่ไม่ถึงขนาดเว่อร์มากไปครับ
    • จากนั้น monitor ขนาด filesystem ผ่าน shell script ง่ายๆ หรืออะไรก็แล้วแต่
    • ค่อยปรับเปลี่ยนลดขนาดให้เหมาะสมต่อไป ผ่าน mount -o remount เพื่อความปลอดภัยควร freeze db (ปิด service เลย/ปิดการ access db ผ่าน app) ก่อนทำการ downsize filesystem ต่อไป

Percona Toolkit

vps ผมเป็น Centos 5.11 i386 เลยเลือก download percona-toolkit-3.0.5_i386.tar.gz ตามนี้

Download Percona Toolkit 3.0.5 Linux-Generic i386

ตรวจสอบ checksum กันหน่อยผ่าน md5sum

pt-query-digest

จริงๆ ที่สนใจคือ pt-query-digest ที่ไว้สำหรับ Analyze MySQL queries from logs, processlist, and tcpdump.

ดูรายละเอียดการใช้งาน pt-query-digest  ก่อนเริ่มต้นใช้งานต้องไปเปิด slow query logs บน MySQL ก่อน

Enabling MySQL Slow Query

long_query_time = 0 เพื่อให้เก็บทุก query ไว้ใน logfile ไม่ควรเปิดใช้งานตลอด เก็บข้อมูลบางช่วงเวลา แล้ว disble slow query หรือเปลี่ยน long_query_time ให้เป็นค่าที่มากขึ้นเช่น 0.5, 1 คือ 0.5, 1 second เป็นต้น

หากไม่มีไฟล์ /var/log/mysqld-slow.log ต้องสร้างและเปลี่ยน owner ไฟล์ แล้ว restart mysqld service ดังนี้

pt-query-digest with mysqld-slow.log

ในชุดของ Percona Toolkit ประกอบด้วยหลาย tools โดยทดลองใช้งานคำสั่ง pt-show-grants ผลลัพธ์ตามด้านล่าง

เห็นว่ามีการ grant [email protected] เพิ่มขึ้นมา เพื่อไว้ทดสอบ pt-query-digest กับ tcpdump ในกรณีที่ไม่ต้องการ restart service MySQL โดยต้องมีการแก้ code wp-config.php ตรง DB_HOST จาก localhost เป็น 127.0.0.1 เพราะว่า หากเป็น localhost เวลา capture packet ด้วย tcpdump จะไม่มี packet ที่จับได้ นั่นแสดงว่า connection วิ่งผ่าน Unix Socket

pt-query-digest with tcpdump

-i lo => lo คือ loopback interface เพราะว่าผม bind-address mysql ที่ 127.0.0.1 หรืออาจระบุเป็น any คือ ทุก interface เลย
-c 1000 คือ count จับแค่ 1000 packet พอ สำหรับการจับจริงอาจปรับเพิ่มเพื่อดูการใช้งานยาวๆ ได้เช่น 100000 เป็นต้น

pt-query-digest output

มาดูผลลัพธ์ทั้ง pt-query-digest จาก mysqld slowlog และ pt-query-digest จาก tcpdump

จะเห็นว่า output มีบอกข้อมูลดังต่อไปนี้

  • Profile Rank อันดับ 1 – 20 , ลำดับถัดไปเป็น MISC กับค่า response time และ จำนวนการ call
  • Query 1 คือ Query ของ Rank อันดับ 1 บอกพวก Execute time, Query_time distribution, Explain Query เป็นต้น

1) เรื่อง Created_tmp_disk_tables ผมใช้ tmpfs แก้ปัญหาไปก่อนตามด้านบน ไม่อยากไปรื้อ alter table แก้ column อะไรครับ

2) ท้ายนี้ pt-query-digest เป็นเครื่องมือไว้ดู MySQL Top Query จาก mysql slow query log หรือ tcpdump log ในรูปแบบ command line

3) สำหรับ MySQL Top Query Graph ที่มีการใช้งาน pt-query-digest ขอแนะนำ Anemometer  แต่ว่ามันก็เก็บข้อมูลใน MySQL ด้วย หากเก็บใน db เดียวกันจะทำให้ช้าลงไปมั้ย – หากมันช้าหรือมีปัญหาอยู่เดิม !  ควรแยกเครื่อง/แยก db สำหรับลง Anemometer ? ไว้ต่อกันบทความหน้าแล้วกันนะครับ

 

Analyze slow MySQL queries with pt-query-digest