ผมจำครั้งแรกเห็น ActiveRecord ของ Rails ปี 2005 ตอนเขียน SQL queries ด้วยมือสำหรับ PHP app การใช้ database จาก tedious chore กลายเป็นง่าย และ - กล้าพูด - สนุก
...ถึงเจอ performance issues
ActiveRecord เองไม่ช้า ผมแค่หยุดสนใจ queries ที่รันจริง และพบว่า idiomatic database queries ที่ใช้ใน Rails CRUD apps บางอย่าง by-default scaling ไม่ดีกับ large datasets
บทความนี้อภิบาย 3 ตัวกระตุ้นใหญ่สุด แต่ก่อนมาพูดถึงการวัดว่า DB queries จะ scale ได้ดีไหม
วัดประสิทธิภาพ
ทุก DB query performant หาก dataset เล็กพอ จึงต้อง benchmark กับ production-sized database ตัวอย่างใช้ table faults ประมาณ 22,000 records
ใช้ postgres วัดประสิทธิภาพด้วย explain ตัวอย่าง:
# explain (analyze) select * from faults where id = 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Index Scan using faults_pkey on faults (cost=0.29..8.30 rows=1 width=1855)
(actual time=0.556..0.556 rows=0 loops=1)
Index Cond: (id = 1)
Total runtime: 0.626 ms
แสดง estimated cost (cost=0.29..8.30 rows=1 width=1855) และ actual time (actual time=0.556..0.556 rows=0 loops=1)
ถ้าชอบ format อ่านง่าย ขอให้ postgres พิมพ์ YAML
# explain (analyze, format yaml) select * from faults where id = 1;
QUERY PLAN
--------------------------------------
- Plan:
+ Node Type: "Index Scan"
+ Scan Direction: "Forward"
+ Index Name: "faults_pkey"
+ Relation Name: "faults"
+ Alias: "faults"
+ Startup Cost: 0.29
+ Total Cost: 8.30
+ Plan Rows: 1
+ Plan Width: 1855
+ Actual Startup Time: 0.008
+ Actual Total Time: 0.008
+ Actual Rows: 0
+ Actual Loops: 1
+ Index Cond: "(id = 1)"
+ Rows Removed by Index Recheck: 0
+ Triggers:
+ Total Runtime: 0.036
(1 row)
ตอนนี้ focus แค่ "Plan Rows" และ "Actual Rows"
- Plan Rows Worst case DB ต้อง loop กี่ rows ตอบ query
- Actual Rows ตอน execute DB loop จริงกี่ rows
ถ้า "Plan Rows" เป็น 1 เหมือนข้างบน query น่าจะ scale ดี ถ้า "Plan Rows" เท่าจำนวน rows ใน database หมายถึง "full table scan" ไม่ scale ดี
ตอนรู้จักวัดแล้ว มาดู common rails idioms กัน
Counting
โค้ดใน Rails views บ่อยครั้ง:
Total Faults <%= Fault.count %>
ได้ SQL ประมาณ:
select count(*) from faults;
ลอง explain:
# explain (analyze, format yaml) select count(*) from faults;
QUERY PLAN
--------------------------------------
- Plan:
+ Node Type: "Aggregate"
+ Strategy: "Plain"
+ Startup Cost: 1840.31
+ Total Cost: 1840.32
+ Plan Rows: 1
+ Plan Width: 0
+ Actual Startup Time: 24.477
+ Actual Total Time: 24.477
+ Actual Rows: 1
+ Actual Loops: 1
+ Plans:
- Node Type: "Seq Scan"
+ Parent Relationship: "Outer"
+ Relation Name: "faults"
+ Alias: "faults"
+ Startup Cost: 0.00
+ Total Cost: 1784.65
+ Plan Rows: 22265
+ Plan Width: 0
+ Actual Startup Time: 0.311
+ Actual Total Time: 22.839
+ Actual Rows: 22265
+ Actual Loops: 1
+ Triggers:
+ Total Runtime: 24.555
(1 row)
โห! count query ง่าย loop 22,265 rows — ทั้งตาราง! ใน postgres counts เสมอ loop ทั้ง record set
ลดขนาด record set ได้โดยเพิ่ม where conditions อาจลดได้พอที่ performance ยอมรับได้
ทางออกเดียว: cache count values Rails ทำให้ถ้า setup:
belongs_to :project, :counter_cache => true
ทางเลือกอื่นตอน check ว่า query คืน records หรือไม่ แทน Users.count > 0 ลอง Users.exists? query นี้ performant กว่า (ขอบคุณ Gerry Shaw ชี้ให้รู้)
Sorting
Index page เกือบทุก app มี ดึง newest 20 records แสดง อะไรจะซับซ้อน?
โค้ดโหลด records อาจแบบนี้:
@faults = Fault.order(created_at: :desc)
SQL:
select * from faults order by created_at desc;
Analyze:
# explain (analyze, format yaml) select * from faults order by created_at desc;
QUERY PLAN
--------------------------------------
- Plan:
+ Node Type: "Sort"
+ Startup Cost: 39162.46
+ Total Cost: 39218.12
+ Plan Rows: 22265
+ Plan Width: 1855
+ Actual Startup Time: 75.928
+ Actual Total Time: 86.460
+ Actual Rows: 22265
+ Actual Loops: 1
+ Sort Key:
- "created_at"
+ Sort Method: "external merge"
+ Sort Space Used: 10752
+ Sort Space Type: "Disk"
+ Plans:
- Node Type: "Seq Scan"
+ Parent Relationship: "Outer"
+ Relation Name: "faults"
+ Alias: "faults"
+ Startup Cost: 0.00
+ Total Cost: 1784.65
+ Plan Rows: 22265
+ Plan Width: 1855
+ Actual Startup Time: 0.004
+ Actual Total Time: 4.653
+ Actual Rows: 22265
+ Actual Loops: 1
+ Triggers:
+ Total Runtime: 102.288
(1 row)
DB sort ทั้ง 22,265 rows ทุกครั้ง query ไม่ดี!
โดย default ทุก "order by" ให้ record set sort real-time ไม่มี caching ไม่มี magic ช่วย
แก้ด้วย indexes กรณีง่ายเพิ่ม sorted index บน created_at column จะเร็วขึ้นเยอะ
ใน Rails migration:
class AddIndexToFaultCreatedAt < ActiveRecord::Migration
def change
add_index(:faults, :created_at)
end
end
รัน SQL:
CREATE INDEX index_faults_on_created_at ON faults USING btree (created_at);
ที่ท้าย (created_at) ระบุ sort order default ascending
Re-run sort query ไม่มี sorting step แค่อ่าน pre-sorted data จาก index
# explain (analyze, format yaml) select * from faults order by created_at desc;
QUERY PLAN
----------------------------------------------
- Plan:
+ Node Type: "Index Scan"
+ Scan Direction: "Backward"
+ Index Name: "index_faults_on_created_at"
+ Relation Name: "faults"
+ Alias: "faults"
+ Startup Cost: 0.29
+ Total Cost: 5288.04
+ Plan Rows: 22265
+ Plan Width: 1855
+ Actual Startup Time: 0.023
+ Actual Total Time: 8.778
+ Actual Rows: 22265
+ Actual Loops: 1
+ Triggers:
+ Total Runtime: 10.080
(1 row)
ถ้า sort หลาย columns ต้องสร้าง index sorted หลาย columns ใน migration:
add_index(:faults, [:priority, :created_at], order: {priority: :asc, created_at: :desc})
เริ่ม complex queries ให้รันผ่าน explain บ่อยๆ อาจพบการเปลี่ยน query เล็กน้อยทำให้ postgres ใช้ index sort ไม่ได้
Limits และ Offsets
Index pages ไม่ทุก item ใน database ใช้ pagination แสดง 10, 30, 50 items วิธีทั่วไป: limit และ offset ร่วมกัน ใน Rails:
Fault.limit(10).offset(100)
SQL:
select * from faults limit 10 offset 100;
Explain พบสิ่งแปลก: rows scanned = 110 = limit + offset
# explain (analyze, format yaml) select * from faults limit 10 offset 100;
QUERY PLAN
--------------------------------------
- Plan:
+ Node Type: "Limit"
+ ...
Plans:
- Node Type: "Seq Scan"
+ Actual Rows: 110
+ ...
เปลี่ยน offset 10,000 rows scanned = 10010 ช้าขึ้น 64x
# explain (analyze, format yaml) select * from faults limit 10 offset 10000;
QUERY PLAN
--------------------------------------
- Plan:
+ Node Type: "Limit"
+ ...
Plans:
- Node Type: "Seq Scan"
+ Actual Rows: 10010
+ ...
สรุปน่ากลัว: ตอน paginate หน้าต่อท้ายช้ากว่าหน้าแรก สมมติ 100 items/page ตัวอย่างข้างบน หน้า 100 ช้ากว่าหน้า 1 ถึง 13x
ทำไงดี?
ตรงๆ ผมหา perfect solution ไม่เจอ เริ่มดูว่า reduce dataset ได้ไหม ไม่ต้องมี 100s หรือ 1000s pages
หากลดไม่ได้ best bet: แทน offset/limit ด้วย where clauses
# ใช้ date range
Fault.where("created_at > ? and created_at < ?", 100.days.ago, 101.days.ago)
# ...หรือ id range
Fault.where("id > ? and id < ?", 100, 200)
สรุป
หวังว่าบทความนี้ convince คุณให้ใช้ postgres explain หา performance issues ของ db queries แม้ queries เรียบง่ายก็อาจทำลาย performance ดังนั้นคุ้มค่าที่จะเช็ค :)
