วันอาทิตย์ที่ 4 กันยายน พ.ศ. 2554

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 วินาที

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

1 ความคิดเห็น :

  1. เท่าที่ดู ตัว LIMI 65625, 25 น่าจะเป็นปัญหาใหญ่ คือเป็นหน้า archive ที่ลึกมากๆ คงจะมาจาก search engine crawler มากกว่าคนใช้จริง ดังนั้นก็น่าจะพอยอมรับได้ครับ

    แต่ก็ควรเน้นการเปลี่ยนวิธีการแสดงหน้า archive มากกว่า โดยใช้ condition แทน สำหรับหน้าที่เก่าเกินกว่า 20 page (LIMIT 500,25)

    ถ้าทดสอบ query นี้ได้ก็จะดีครับ ว่าช่วยเรื่อง performance หรือไม่

    SELECT sql_no_cache DISTINCT t.ID, t.Title,
    t.Description, t.Publisher_Name, m.Upload_Date
    FROM topic_media m
    LEFT JOIN topic t ON (t.ID = m.Topic_ID)
    WHERE
    m.Type = 'image'
    AND t.Status = 1
    AND t.Approve_State = 1
    ORDER BY m.Topic_ID
    LIMIT
    65625, 25 ;

    ตอบกลับลบ