MySQL Query Optimization 2 : เครื่องมือช่วย

เป็นแบบนี้ไหมครับ รู้ว่าปัญหาอยู่ที่ MySQL นี่แหละ แต่เป็นตรงไหนล่ะ เรามาดูเครื่องมือช่วยกันดีกว่า

mysqltuner
ตัวนี้สำหรับ admin โดยมันจะวิเคราะห์ข้อมูลต่างๆ ของ MySQL server ในขณะนั้นแล้วแนะนำว่าควรปรับค่าอะไร อย่างไร อันเนื่องมาจากว่าไม่มีสูตรตายตัวว่าควรปรับแต่งอย่างไร ซึ่งขึ้นอยู่กับข้อมูล และการใช้งานจริงด้วย

การติดตั้ง
# wget mysqltuner.pl -O mysqltuner.pl

เป็นการติดตั้งที่เท่มาก คือตัวนี้เป็น perl script ซึ่งปกติมันจะใช้ extension เป็น .pl เค้าเลยไปจดชื่อ domain เป็น mysqltuner.pl ไว้ คำสั่งนี้จึงหมายถึง ดาวน์โหลดหน้าเว็บหลักของ http://mysqltuner.pl/ ซึ่งมันจะให้ข้อมูลเป็น script รุ่นล่าสุดเสมอ เอามาบันทึกไว้ในชื่อ mysqltuner.pl

จากนั้นกำหนดให้ execute ได้
# chmod +x mysqltuner.pl

การใช้ สั่ง ./mysqltuner.pl แล้วใส่ชื่อแอดมินของ MySQL ปกติคือ root และรหัสผ่าน ก็จะได้ผลลัพธ์คล้ายๆ แบบนี้


# ./mysqltuner.pl


 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: 


-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.58-1~dotdeb.1-log
[OK] Operating on 64-bit architecture


-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 30G (Tables: 670)
[--] Data in MEMORY tables: 45M (Tables: 8)
[!!] Total fragmented tables: 130


-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned


-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 7h 10m 37s (43M q [153.856 qps], 1M conn, TX: 20B, RX: 8B)
[--] Reads / Writes: 49% / 51%
[--] Total buffers: 1.6G global + 8.4M per thread (500 max threads)
[OK] Maximum possible memory usage: 5.7G (73% of installed RAM)
[OK] Slow queries: 3% (1M/43M)
[OK] Highest usage of available connections: 36% (180/500)
[OK] Key buffer size / total MyISAM indexes: 1000.0M/19.4G
[OK] Key buffer hit rate: 99.9% (2B cached / 4M reads)
[!!] Query cache efficiency: 19.2% (3M cached / 17M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (162 temp sorts / 1M sorts)
[OK] Temporary tables created on disk: 0% (52 on disk / 734K total)
[OK] Thread cache hit rate: 64% (686K created / 1M connections)
[!!] Table cache hit rate: 19% (1K open / 9K opened)
[OK] Open file limit used: 55% (16K/30K)
[OK] Table locks acquired immediately: 97% (224M immediate / 231M locks)


-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_limit (> 16M, or use smaller result sets)
    table_cache (> 1800)

ผลลัพธ์ก็อธิบายในตัวเองค่อนข้างชัดเจน บรรทัดที่นำหน้าด้วย [OK] ก็คือดีแล้ว ไม่มีปัญหาอะไร นำหน้าด้วย [--] คือเป็นข้อมูลแจ้งให้ทราบ ส่วน [!!] อันนี้ไม่ดีละ มีบางอย่างควรต้องพิจารณา ซึ่งจะมีสรุปด้านล่างว่าควรทำอะไร หรือปรับค่าตัวแปรอะไร เพื่อให้ประสิทธิภาพดีขึ้น แต่ไม่ต้องเชื่อมันทุกอย่างก็ได้ ฟังไว้เป็นข้อมูลประกอบก็พอ

ดูเพิ่มเติม http://mysqltuner.com/

mysql_slow_log_parser
ตัวนี้เหมาะสำหรับ developer ไว้วิเคราะห์ว่า query ใดเป็นตัวปัญหา โดยปกติเราสามารถกำหนดให้ MySQL บันทึก log ของ slow query ที่เกิดขึ้น แต่ปัญหาคือมันเยอะมากจนไม่รู้จะเริ่มดูตรงไหนก่อน ตัวนี้จะช่วยเอา slow log ทั้งหมดมารวบรวม จัดหมวดหมู่คำสั่งที่คล้ายกัน นำจำนวน รวมเวลาที่ใช้ทั้งหมด เวลาน้อยสุด เวลามากสุด เวลาเฉลี่ย แล้วเรียงลำดับตามเวลา query ทั้งหมดที่ใช้ไปจากมากไปน้อย

การติดตั้ง
ก่อนอื่นให้ตั้งค่าของ MySQL server ให้เก็บ slow log query ก่อน โดยแก้ไฟล์ /etc/mysql/my.cnf แล้ว uncomment และแก้บรรทัด 2 บรรทัดนี้

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 0

สังเกตว่าเราจะกำหนด long_query_time เป็น 0 นั่นคือให้เก็บ log ทุก query ไว้หมด เพื่อนำมาวิเคราะห์ จากนั้น restart mysql ด้วยคำสั่ง

# /etc/init.d/mysql restart

ดาวน์โหลด script mysql_slow_log_parser

# wget http://www.mysqlperformanceblog.com/files/utils/mysql_slow_log_parser

# chmod +x mysql_slow_log_parser

การใช้งาน

ถ้าเป็นล็อกที่เกิดในวันนี้ คือหลังจากที่ logrotate ทำงาน ให้สั่ง

# ./mysql_slow_log_parser /var/log/mysql/mysql-slow.log > mysql-slow-info.txt

ดูผลที่เก็บในไฟล์

# less mysql-slow-info.txt

ถ้าเป็นล็อกที่เกิดขึ้นในวันก่อนๆ ซึ่งถูก rotate และ compress ไปแล้ว

# zcat /var/log/mysql/mysql-slow.log.1.gz | ./mysql_slow_log_parser > mysql-slow-info.1.txt

ดูผลที่เก็บในไฟล์

# less mysql-slow-info.1.txt

ตัวอย่างไฟล์ mysql-slow.log

# User@Host: xxx[xxx] @ localhost [127.0.0.1]

# Query_time: 0.004879  Lock_time: 0.000031 Rows_sent: 1  Rows_examined: 2313

SET timestamp=1315178855;

SELECT COUNT(*) AS numRows FROM group_topics WHERE group_id=167 AND status='NORMAL';

# Time: 110905  6:27:37

# User@Host: xxx[xxx] @ localhost [127.0.0.1]

# Query_time: 0.182972  Lock_time: 0.000036 Rows_sent: 1  Rows_examined: 104030

SET timestamp=1315178857;

SELECT member_id  FROM member  WHERE LOWER(user_id)='xxxxx'    AND is_active='1'  LIMIT 1;

ตัวอย่างผลลัพธ์ หลังจากผ่าน parser

 Starting... 

### 53018 Queries 

### Total time: 204742.585691, Average time: 3.86175611473462

### Taking 0.000740  to 81.100860  seconds to complete

### Rows analyzed 100 - 1467

SET timestamp=XXX;

SELECT COUNT(*) AS total  FROM chat_msgs  WHERE owner_id=XXX AND is_unread='XXX';

SET timestamp=1315178847;

SELECT COUNT(*) AS total  FROM chat_msgs  WHERE owner_id=104170 AND is_unread='1';

### 29081 Queries 

### Total time: 90838.2220840001, Average time: 3.12362786988068

### Taking 0.000353  to 82.103217  seconds to complete

### Rows analyzed 100 - 4387

SET timestamp=XXX;

SELECT friend_id  FROM relation  WHERE member_id=XXX AND status='XXX';

SET timestamp=1315178835;

SELECT friend_id  FROM relation  WHERE member_id=235929 AND status='ACCEPT';

สังเกตว่ามันจะแปลงส่วน value ทั้ง string และตัวเลข ให้เป็น XXX ก่อน แล้วนำมาแยกนับ วิเคราะห์คำสั่งที่เหมือนกัน ก็จะทำให้ทราบว่าคำสั่งใดถูกใช้บ่อยแค่ไหน (กี่ query) ใช้เวลารวมทั้งหมดเท่าไหร่ (Total time:) ใช้เวลาเฉลี่ยต่อ query เท่าไหร่ (Average time:) ใช้เวลาน้อยสุดถึงมากสุดเท่าไหร่ (Taking ... to ... seconds to complete) จำนวน rows ของผลลัพธ์ โดยมันจะแสดง query ที่ใช้เวลารวมมากที่สุดก่อน ซึ่งถ้า optimize ได้ก็จะมีผลมากที่สุด

อ่อ ที่เห็นผลลัพธ์อันนี้ดูแย่ๆ คือ average สูง และช่วงเวลาที่ใช้ต่ำสุด - สูงสุดกว้างขนาดนี้ เพราะมีปัญหาเรื่อง capacity ของเครื่องไม่พอน่ะครับ จึงเร็วบ้าง ช้าบ้าง ไม่แน่ไม่นอน

MySQL Query Optimization 1

มีเหตุให้ต้องได้ช่วยฝ่ายอื่นแก้ปัญหาเรื่องประสิทธิภาพของเซิร์ฟเวอร์ลินุกซ์เลยขอบันทึกไว้หน่อยว่าเจออะไรบ้าง

เซิร์ฟเวอร์ให้บริการเว็บ ใช้ apache + php5 + mysql ตามปกติ อาการแรกที่เห็นคือเซิร์ฟเวอร์ไม่ตอบสนอง ถึงขนาด ssh เข้าไปยังต้องรอนานมาก และ timeout ไปหลายรอบกว่าจะเข้าได้ อาการนี้มักเกิดจากหน่วยความจำเต็ม จนต้อง swap หน่วยความจำบางส่วนลงดิสก์ ซึ่งบังเอิญหน่วยความจำที่มัน active อยู่ มันเกินหน่วยความจำจริง เลยเกิดการ swap ตลอดเวลา จน process ต่างๆ แทบจะทำงานอะไรไม่ได้ เพราะต้องรอหน่วยความจำจากดิสก์ก่อน

ทำไมหน่วยความจำจึงไม่พอ

เซิร์ฟเวอร์ตัวนี้ มีหน่วยความจำ 3GB ไม่ได้มากนัก แต่ก็ไม่น้อยเกินไป สาเหตุที่หน่วยความจำเต็มเกิดจาก apache httpd เกิดการแบ่งตัวเพื่อรับภาระงานจนเกินขนาดของหน่วยความจำ ซึ่งปกติเวลามี connection เข้ามา 1 อัน ต้องใช้ httpd 1 process ในการรับงาน แต่ละ process มีขนาดประมาณ 9-12MB กำหนดไว้สูงสุด 250 client ซึ่งเช็คแล้วพบว่าเต็ม 250 เลย จับคูณกันแล้วก็ราวๆ 3GB พอดี ยังไม่รวม mysql  และอื่นๆ

การเพิ่มหน่วยความจำไม่ใช่ทางออกที่ถูกต้อง จริงๆ หน่วยความจำเยอะๆ ดี แต่มันจะไม่ช่วยในกรณีนี้ เพราะเทียบได้กับการให้บริการอะไรสักอย่างของหน่วยงานหนึ่ง แล้วพบว่าที่นั่งรอไม่พอ เลยขยายห้องรอเป็นสองเท่า เพื่อให้คนเข้ามารอได้เป็นสองเท่า จริงๆ ต้องไปแก้ที่ต้นเหตุคือทำไมการให้บริการของหน่วยงานนั้นๆ ถึงได้ช้า

ทำไม apache httpd ถึงช้าจนเกิด connection ค้างมากถึง 250 อัน

ส่วนใหญ่ (รวมทั้งกรณีนี้ด้วย) คือเกิดสภาวะรอ mysql ตอบ query ที่เรียกไป

แล้วทำไม mysql มันช้านัก

ในหลายกรณี (รวมทั้งกรณีนี้ด้วย) คือการเรียกใช้ query ไม่ได้ถูก optimize ให้ทำงานได้ดีที่สุด

การตรวจสอบว่า query ใดทำให้ mysql ช้า

ให้เปิดใช้ฟีเจอร์เก็บล็อกของ query ที่ช้าโดยเพิ่ม

log_slow_queries = /var/log/mysql-slow.log

long_query_time = 5

เข้าไปใน section [mysqld] ในไฟล์ my.cnf ซึ่งน่าจะอยู่ใน /etc/mysql หรือ /etc

แล้ว restart mysql แล้วตามเฝ้าดูในไฟล์ /var/log/mysql-slow.log

หลักการ optimize query

อันแรกสุดคือลดการเกิด full table scan ให้มากที่สุด full table scan คือการที่จะหาคำตอบของ query จำเป็นต้อง scan อ่านทั้งตาราง ซึ่งจะช้ามาก และช้าขึ้นเรื่อยๆ เมื่อตารางใหญ่ขึ้น การลดอาการนี้อันแรกคือ ให้ทำ index ที่จำเป็นต้องใช้บ่อยๆ เสมอ โดยให้ดูที่ ORDER BY กับ WHERE ว่ามีการอ้างถึง column ใดบ้าง อีกอันที่เจอคือบางคนใช้ WHERE sss LIKE '%xxxx%' ในการเปรียบเทียบ string ทั้งๆ ที่ xxxx นั่นคือทั้งหมดที่อยู่ใน column นั้นๆ อยู่แล้ว การใช้ %xxxx% ทำให้เกิดการค้นหาตลอดตารางเพื่อหาทุกแถวที่เป็นไปได้ ที่จริงแล้วในกรณีนี้ใช้แค่ WHERE sss = 'xxxx' ก็ได้

อีกอันคือให้หลีกเลี่ยงการ JOIN ให้มากที่สุด อาจจะลองเลี่ยงไปใช้ sub query ก็ช่วยได้พอสมควร

ตัวอย่างการ optimize query

ของเดิม

SELECT topic.ID, topic.Title, topic.Description, topic.Publisher_Name, topic.Upload_Date

FROM topic

  LEFT JOIN topic_category ON ( topic.ID = topic_category.Topic_ID)

  LEFT JOIN topic_media ON (topic.ID = topic_media.Topic_ID)

WHERE

  topic.Status = 1

  AND topic_media.Status = 1

  AND topic.Approve_State = 1

  AND topic_media.Type like '%image%'

GROUP BY

  topic.ID

ORDER BY

  topic.ID DESC

LIMIT 65625, 25;

ใช้เวลา 13.20 วินาที

เปลี่ยน like '%...%' เป็น = '...'

SELECT topic.ID, topic.Title, topic.Description, topic.Publisher_Name, topic.Upload_Date

FROM topic

  LEFT JOIN topic_category ON ( topic.ID = topic_category.Topic_ID)

  LEFT JOIN topic_media ON (topic.ID = topic_media.Topic_ID)

WHERE

  topic.Status = 1

  AND topic_media.Status = 1

  AND topic.Approve_State = 1

  AND  topic_media.Type = 'image'

GROUP BY

  topic.ID

ORDER BY

  topic.ID DESC

LIMIT

  65625, 25;

ใช้เวลา 12.79 วินาที

สังเกตว่าใช้ GROUP BY แต่ไม่มีการใช้ aggregate function เช่น sum() หรือ count() แสดงว่าแค่ตั้งใจให้แสดงแถวแบบไม่ซ้ำ ลองเปลี่ยนเป็น DISTINCT

SELECT DISTINCT topic.ID, topic.Title, topic.Description, topic.Publisher_Name, topic.Upload_Date

FROM topic

  LEFT JOIN topic_category ON ( topic.ID = topic_category.Topic_ID)

  LEFT JOIN topic_media ON (topic.ID = topic_media.Topic_ID)

WHERE

  topic.Status = 1

  AND topic_media.Status = 1

  AND topic.Approve_State = 1

  AND  topic_media.Type = 'image'

ORDER BY

  topic.ID DESC

LIMIT

  65625, 25 ;

ใช้เวลา 10.65 วินาที

สังเกตพบว่ามีการ LEFT JOIN ตาราง topic_category แต่ไม่ได้เอามาใช้ทำอะไร งั้นตัดทิ้งไป

SELECT DISTINCT topic.ID, topic.Title, topic.Description, topic.Publisher_Name, topic.Upload_Date

FROM topic LEFT JOIN topic_media ON (topic.ID = topic_media.Topic_ID)

WHERE

  topic.Status = 1

  AND topic_media.Status = 1

  AND topic.Approve_State = 1

  AND  topic_media.Type = 'image'  

ORDER BY

  topic.ID DESC

LIMIT

   65625, 25 ;

ใช้เวลา 8.98 วินาที

เปลี่ยนจากการทำ LEFT JOIN เป็น sub query select

SELECT DISTINCT ID, Title, Description, Publisher_Name, Upload_Date

FROM topic

WHERE

  ID IN (SELECT Topic_ID FROM topic_media WHERE Status = 1 AND Type = 'image')

  AND Status = 1

  AND Approve_State = 1  

ORDER BY

  ID DESC

LIMIT

  65625, 25 ;

เหลือ 3.43 วินาที

ลองใช้ EXPLAIN SELECT เพื่อวิเคราะห์ว่ามีอะไรไม่เป็นไปตามที่ต้องการหรือไม่

EXPLAIN SELECT sql_no_cache DISTINCT ID, Title, Description, Publisher_Name, Upload_Date FROM topic WHERE ID IN (SELECT Topic_ID FROM topic_media WHERE Status = 1 AND Type = 'image') AND Status = 1 AND Approve_State = 1   ORDER BY ID DESC LIMIT 65625, 25  ;

+----+--------------------+-------------+----------------+----------------------+---------------+---------+-------+-------+-----------------------------+

| id | select_type        | table       | type           | possible_keys        | key           | key_len | ref   | rows  | Extra                       |

+----+--------------------+-------------+----------------+----------------------+---------------+---------+-------+-------+-----------------------------+

|  1 | PRIMARY            | topic       | ref            | Approve_State,Status | Approve_State | 1       | const | 75581 | Using where; Using filesort |

|  2 | DEPENDENT SUBQUERY | topic_media | index_subquery | Topic_ID,Type,Status | Topic_ID      | 8       | func  |     2 | Using where                 |

+----+--------------------+-------------+----------------+----------------------+---------------+---------+-------+-------+-----------------------------+

พบว่า มี Using filesort เพราะตอน query นั้น mysql ให้ความสำคัญกับการ look up (WHERE) ก่อน จึงใช้ index Approve_State หรือ Status ส่วน PRIMARY ที่จำเป็นต้องใช้ตอนเรียงลำดับ (ORDER BY) ไม่ได้ใช้ (ขณะ query สามารถเลือก index ได้อันเดียว) ทำให้ต้องเสียเวลาไปเรียงข้อมูลใหม่ก่อน ในกรณีนี้อาจจะลองบังคับให้ใช้ index ที่ต้องการ โดยเพิ่ม FORCE INDEX (keyname) หลังชื่อตาราง เช่น

EXPLAIN SELECT sql_no_cache DISTINCT ID, Title, Description, Publisher_Name, Upload_Date FROM topic FORCE INDEX (PRIMARY) WHERE ID IN (SELECT Topic_ID FROM topic_media WHERE Status = 1 AND Type = 'image') AND Status = 1 AND Approve_State = 1   ORDER BY ID DESC LIMIT 65625, 25  ;

+----+--------------------+-------------+----------------+----------------------+----------+---------+------+-------+-------------+

| id | select_type        | table       | type           | possible_keys        | key      | key_len | ref  | rows  | Extra       |

+----+--------------------+-------------+----------------+----------------------+----------+---------+------+-------+-------------+

|  1 | PRIMARY            | topic       | index          | NULL                 | PRIMARY  | 8       | NULL | 65650 | Using where |

|  2 | DEPENDENT SUBQUERY | topic_media | index_subquery | Topic_ID,Type,Status | Topic_ID | 8       | func |     2 | Using where |

+----+--------------------+-------------+----------------+----------------------+----------+---------+------+-------+-------------+

เมื่อเอา EXPLAIN ออก รันด้วยคำสั่งจริง 

SELECT sql_no_cache DISTINCT ID, Title, Description, Publisher_Name, Upload_Date 

FROM topic FORCE INDEX (PRIMARY) 

WHERE 

  ID IN (SELECT Topic_ID FROM topic_media WHERE Status = 1 AND Type = 'image') 

  AND Status = 1 

  AND Approve_State = 1   

ORDER BY 

  ID DESC 

LIMIT

 65625, 25  ;

ใช้เวลา 2.22 วินาที

ทั้งหมดนี้คือได้ผลลัพธ์เหมือนเดิมเป๊ะ