Database performance tuning for App Dev

บทความนี้เกี่ยวกับการ tuning database สำหรับ Application Developer เพื่อเป็นแนวทาง และสร้างความเข้าใจในการใช้งาน database ให้มีประสิทธิภาพ ตัวอย่างนี้จะใช้ PostgreSQL ซึ่งสามารถ apply ใช้กับ database อื่นๆ ได้

เริ่มจาก connect ไปยัง PostgreSQL และสร้าง table financial พร้อมกับ insert ข้อมูล 100,000,000 random records ตาม script (สามารถใช้ PostgreSQL ที่ run ด้วย container ในบทความ Template for PostgreSQL HA with Patroni and HA test)

“แก้จำนวน random records ให้เหลือ 10,000,000 เพื่อที่จะไม่ต้องรอนานในแต่ละคำสั่ง”

$ psql -h localhost -p 5000 -U postgres
Password for user postgres:
psql (16.1, server 16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.

postgres=# create database test;
CREATE DATABASE

postgres=# \c test
$ psql (16.1, server 16.2 (Debian 16.2-1.pgdg120+2))
You are now connected to database "test" as user "postgres".

test=# CREATE TABLE transactions (id SERIAL PRIMARY KEY,
test(#   acc_from  TEXT   NOT NULL,
test(#   acc_to    TEXT   NOT NULL,
test(#   value     INT    NOT NULL);
CREATE TABLE

test=# INSERT INTO transactions (acc_from, acc_to, value) SELECT  'person' || CAST(FLOOR(RANDOM() * 10000) AS TEXT),  'person' || CAST(FLOOR(RANDOM() * 10000) AS TEXT),  FLOOR(RANDOM() * RANDOM() * 1000000) + 1 FROM GENERATE_SERIES(1, 100000000) AS i;

INSERT 0 100000000

test=#
test=# select count(*) from transactions;
   count
-----------
 100000000
(1 row)

test=# select * from transactions limit 1;
 id |  acc_from  |   acc_to   | value
----+------------+------------+-------
  1 | person1633 | person8258 |  2914

ทดสอบ query เพื่อให้ return ค่า account ที่ถูกโอนจาก person1633

test=# SELECT * FROM transactions WHERE acc_from='person1633' limit 2;
  id  |  acc_from  |   acc_to   | value
------+------------+------------+--------
    1 | person1633 | person8258 |   2914
 7276 | person1633 | person6042 | 214390
(2 rows)

PostgreSQL มี feature ที่ทำให้เราสามารถเห็น plan สำหรับ query ได้ด้วยการเพิ่ม EXPLAIN ไว้หน้า query statement

test=# EXPLAIN SELECT * FROM transactions WHERE acc_from='person1633';
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Gather  (cost=1000.00..1258126.36 rows=9974 width=28)
   Workers Planned: 2
   ->  Parallel Seq Scan on transactions  (cost=0.00..1256128.96 rows=4156 width=28)
         Filter: (acc_from = 'person1633'::text)
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(7 rows)

สิ่งที่น่าสนใจของ query นี้คือ plan จะ run ในแบบ “Sequential Scan” ด้วย Filter condition โดยการทำงานจะ loop ในทุกๆ row เพื่อเช็ค criteria ว่าตรงกับที่ต้องการค้นหาหรือไม่

นอกจากการใช้ EXPLAIN เพื่อดู plan แล้ว เราสามารถเพิ่ม option ANALYSE เพื่อให้ query ทำงานจริงและได้ข้อมูลเวลาที่ใช้ในการทำงาน query ออกมาด้วย

test=# EXPLAIN (ANALYSE) SELECT * FROM transactions WHERE acc_from='person1633';
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..1258126.36 rows=9974 width=28) (actual time=38.793..3755.765 rows=10037 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on transactions  (cost=0.00..1256128.96 rows=4156 width=28) (actual time=57.632..3710.918 rows=3346 loops=3)
         Filter: (acc_from = 'person1633'::text)
         Rows Removed by Filter: 33329988
 Planning Time: 0.249 ms
 JIT:
   Functions: 6
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 1.361 ms, Inlining 127.730 ms, Optimization 22.775 ms, Emission 19.870 ms, Total 171.737 ms
 Execution Time: 3757.601 ms
(12 rows)

ข้อมูล Execution Time: 3757.601 ms (3.8 sec) คือเวลาที่ใช้ในการ run query และข้อมูล actual time=57.632..3710.918 แบ่งเป็นเวลาสำหรับ first result คือ 57.632 ms และเวลาของผลลัพธ์ทั้งหมดคือ 3710.918 ms จากการที่ first result เป็นค่าที่ไม่มากนักคือ 58 ms เมื่อเทียบกับเวลาทั้งหมด โดยทั่วไป developer มักจะรอให้ได้ result ทั้งหมดเพื่อส่งกลับให้กับ Users แต่ถ้าเป็น application ลักษณะ streaming data จะได้ประโยชน์จากตรงนี้มาก

ค่าต่อไปที่ควรพิจารณาคือค่าประมาณ (predict) และค่าจริง (actual) ที่ได้จาก plan ตามข้อมูล result ค่า predict ได้ข้อมูลกลับมาจำนวน 9974 rows แต่ได้กลับมาจริง 10047 rows

อีกค่าที่เห็นได้จาก sub-query เป็นค่า predict ที่จะได้ผลลัพธ์กลับมาที่จำนวน 4156 rows แต่ได้กลับมาจริงที่จำนวน 3346 rows

ทั้งนี้ค่าดังกล่าวได้มาจากสถิติ (stat) ที่ควรจะไม่ต่างกันมากนัก ค่า stat ได้มาจาก PostgreSQL คาดการณ์และคำนวณกับ table ซึ่งเกิดขึ้นอัตโนมัติในทุกครั้งที่มีการ query บางครั้งค่าสถิติอาจจะไม่ล่าสุด เลยจำเป็นต้อง refresh เพื่อ update ค่าใหม่เสมอ และจำเป็นต้องทำทุกครั้งก่อนที่จะทำ performance tuning ด้วย cmd analyse

test=# analyse transactions;
ANALYZE

ทดลองดึงค่า plan และ result อีกรอบหลังจาก update stat

test=# EXPLAIN (ANALYSE) SELECT * FROM transactions WHERE acc_from='person1633';
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..1258126.26 rows=9973 width=28) (actual time=64.676..3620.414 rows=10037 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on transactions  (cost=0.00..1256128.96 rows=4155 width=28) (actual time=57.300..3593.221 rows=3346 loops=3)
         Filter: (acc_from = 'person1633'::text)
         Rows Removed by Filter: 33329988
 Planning Time: 0.588 ms
 JIT:
   Functions: 6
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.534 ms, Inlining 140.111 ms, Optimization 13.482 ms, Emission 14.104 ms, Total 168.231 ms
 Execution Time: 3707.546 ms
(12 rows)

ค่าที่ได้จะสังเกตุว่าค่า predict กับ actual time มีค่าที่ใกล้เคียงกัน อย่างไรก็ตาม query นี้ก็ยังใช้เวลาค่อนข้างนาน ถ้าดูที่ filter จะเห็นว่ามีถึง 33,329,988 rows ที่ถูก remove จาก filter ซึ่งทำให้เสียเวลาสำหรับการ query จากข้อมูลที่ได้รับจริงเพียง 3346 rows ถ้าเราสามารถจำกัดข้อมูลให้มีการ load เฉพาะบางส่วนของ data แทนที่จะดึงมาทั้งหมดก็น่าจะช่วยในการค้นหาได้เร็วยิ่งขึ้น ซึ่งตรงนี้คือการทำ index ของ table นั่นเอง

test=# CREATE INDEX transactions_from_idx ON transactions (acc_from);
CREATE INDEX
test=# ANALYSE transactions;
ANALYZE

ทดสอบดึงค่า plan และ stat อีกรอบหลังจากสร้าง index

test=# EXPLAIN (ANALYSE) SELECT * FROM transactions WHERE acc_from='person1633';
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on transactions  (cost=114.02..36486.62 rows=9994 width=28) (actual time=5.034..296.537 rows=10037 loops=1)
   Recheck Cond: (acc_from = 'person1633'::text)
   Heap Blocks: exact=9975
   ->  Bitmap Index Scan on transactions_from_idx  (cost=0.00..111.52 rows=9994 width=0) (actual time=2.109..2.109 rows=10037 loops=1)
         Index Cond: (acc_from = 'person1633'::text)
 Planning Time: 1.519 ms
 Execution Time: 297.486 ms
(7 rows)

จากข้อมูลใหม่จะเห็นว่า Execution Time ลดลงจาก 3757.601 ms เหลือ 297.486 ms เร็วขึ้นมากกว่า 12 เท่า

การใช้ Index จะเหมาะสำหรับ table ขนาดใหญ่ สำหรับ table ขนาดเล็กและ query ข้อมูลที่ตรงกับข้อมูลส่วนใหญ่นั้น การใช้วิธีการแบบ Sequential Scan อาจจะได้ความเร็วมากกว่า และการเลือกว่าจะใช้ index หรือไม่ควรมีข้อมูล stat ของ table ณ เวลานั้นด้วยเสมอ

กรณีที่ query มีความซับซ้อนมากขึ้น เราอาจจำเป็นต้องมี index มากกว่าหนึ่ง index

test=# EXPLAIN (ANALYSE) SELECT * FROM transactions WHERE acc_from='person1633' AND acc_to='person6042';
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on transactions  (cost=111.52..36509.10 rows=1 width=28) (actual time=3.898..34.561 rows=2 loops=1)
   Recheck Cond: (acc_from = 'person1633'::text)
   Filter: (acc_to = 'person6042'::text)
   Rows Removed by Filter: 10035
   Heap Blocks: exact=9975
   ->  Bitmap Index Scan on transactions_from_idx  (cost=0.00..111.52 rows=9994 width=0) (actual time=1.257..1.257 rows=10037 loops=1)
         Index Cond: (acc_from = 'person1633'::text)
 Planning Time: 0.304 ms
 Execution Time: 34.643 ms
(9 rows)

จะเห็นว่ามีการเรียกใช้ index เพื่อให้ได้ข้อมูลชุดแรก แล้วส่งต่อให้ heap scan เพื่อวนหา person6042 และมีการ filter ข้อมูลออกถึง 10035 rows กรณีนี้เราสามารถสร้าง index ที่ column acc_to เพื่อให้การ filter ทำงานได้เร็วขึ้น

test=# CREATE INDEX transactions_to_idx ON transactions (acc_to);
CREATE INDEX
test=# ANALYSE transactions;
ANALYZE

ทดสอบดึงค่า stat หลังจากสร้าง index

test=# EXPLAIN (ANALYSE) SELECT * FROM transactions WHERE acc_from='person1633' AND acc_to='person6042';
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on transactions  (cost=223.18..227.20 rows=1 width=28) (actual time=11.115..11.150 rows=2 loops=1)
   Recheck Cond: ((acc_from = 'person1633'::text) AND (acc_to = 'person6042'::text))
   Heap Blocks: exact=2
   ->  BitmapAnd  (cost=223.18..223.18 rows=1 width=0) (actual time=10.941..10.943 rows=0 loops=1)
         ->  Bitmap Index Scan on transactions_from_idx  (cost=0.00..111.33 rows=9969 width=0) (actual time=5.265..5.265 rows=10037 loops=1)
               Index Cond: (acc_from = 'person1633'::text)
         ->  Bitmap Index Scan on transactions_to_idx  (cost=0.00..111.60 rows=10004 width=0) (actual time=4.751..4.751 rows=9912 loops=1)
               Index Cond: (acc_to = 'person6042'::text)
 Planning Time: 1.313 ms
 Execution Time: 11.391 ms
(10 rows)

ผลลัพธ์ที่ได้มีการใช้ bitmap เพื่อ matching rows ของทั้งสอง index ที่สร้างขึ้นแล้วใช้ BitmapAnd เพื่อรวมผลลัพธ์ ซึ่งผลลัพธ์ที่ได้ Execution Time: 11.3191 ms ดีกว่าใช้แค่ index เดียว

ทั้งนี้ มีอีกวิธีที่จะทำให้ผลลัพธ์ดีกว่าการแยก index คือการใช้ compound index การใช้ index แบบนี้จะเป็นการสร้าง index จากการรวมกันของหลายๆ column

test=# CREATE INDEX transactions_from_to_idx ON transactions (acc_from, acc_to);
CREATE INDEX
test=# ANALYSE transactions;
ANALYZE

ทดสอบดึงค่า stat หลังจากสร้าง index

test=# EXPLAIN (ANALYSE) SELECT * FROM transactions WHERE acc_from='person1633' AND acc_to='person6042';
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using transactions_from_to_idx on transactions  (cost=0.57..8.59 rows=1 width=28) (actual time=1.159..1.172 rows=2 loops=1)
   Index Cond: ((acc_from = 'person1633'::text) AND (acc_to = 'person6042'::text))
 Planning Time: 0.582 ms
 Execution Time: 1.239 ms
(4 rows)

ผลลัพธ์ที่ได้จะเร็วกว่าเดิมมาก และ plan ที่ได้ก็ดูง่ายไม่ซับซ้อนด้วย

ต่อไปลองเปลี่ยน operation จาก AND เป็น OR หรือ UNION ALL เพื่อดูว่า transactions_from_to_idx จะถูกเรียกใช้หรือไม่

test=# EXPLAIN (ANALYSE) SELECT * FROM transactions WHERE acc_from='person1633' OR acc_to='person6042';
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on transactions  (cost=232.97..69698.17 rows=19978 width=28) (actual time=13.400..596.243 rows=19947 loops=1)
   Recheck Cond: ((acc_from = 'person1633'::text) OR (acc_to = 'person6042'::text))
   Heap Blocks: exact=19692
   ->  BitmapOr  (cost=232.97..232.97 rows=19979 width=0) (actual time=4.631..4.633 rows=0 loops=1)
         ->  Bitmap Index Scan on transactions_from_idx  (cost=0.00..111.60 rows=10004 width=0) (actual time=3.021..3.021 rows=10037 loops=1)
               Index Cond: (acc_from = 'person1633'::text)
         ->  Bitmap Index Scan on transactions_to_idx  (cost=0.00..111.38 rows=9975 width=0) (actual time=1.607..1.607 rows=9912 loops=1)
               Index Cond: (acc_to = 'person6042'::text)
 Planning Time: 0.510 ms
 Execution Time: 597.583 ms
(10 rows)

test=# EXPLAIN (ANALYSE) SELECT * FROM transactions WHERE acc_from='person1633' UNION ALL SELECT * FROM transactions WHERE acc_from='person6042';
                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=114.10..73143.22 rows=20008 width=28) (actual time=3.422..412.339 rows=19879 loops=1)
   ->  Bitmap Heap Scan on transactions  (cost=114.10..36521.59 rows=10004 width=28) (actual time=3.419..126.569 rows=10037 loops=1)
         Recheck Cond: (acc_from = 'person1633'::text)
         Heap Blocks: exact=9975
         ->  Bitmap Index Scan on transactions_from_idx  (cost=0.00..111.60 rows=10004 width=0) (actual time=1.444..1.444 rows=10037 loops=1)
               Index Cond: (acc_from = 'person1633'::text)
   ->  Bitmap Heap Scan on transactions transactions_1  (cost=114.10..36521.59 rows=10004 width=28) (actual time=4.545..284.231 rows=9842 loops=1)
         Recheck Cond: (acc_from = 'person6042'::text)
         Heap Blocks: exact=9789
         ->  Bitmap Index Scan on transactions_from_idx  (cost=0.00..111.60 rows=10004 width=0) (actual time=3.600..3.600 rows=9842 loops=1)
               Index Cond: (acc_from = 'person6042'::text)
 Planning Time: 0.531 ms
 Execution Time: 413.466 ms
(13 rows)

ผลลัพธ์ที่ได้จะพบว่าไม่สามารถใช้ compound index ในกรณีที่เป็นลักษณะการรวมผลลัพธ์ (OR , UNION) แต่ plan สามารถเลือกใช้ index ที่มีอยู่ แล้วใช้ BitmapOR เพื่อรวมผลลัพธ์

กรณีที่ไม่มี index เลยหรือมีแค่ index เดียว การทำงานจะกลับไปเป็นแบบ Sequential scan กับ data ทั้งหมด ซึ่งทำให้ได้ผลลัพธ์ช้ามาก ถึงแม้จะมี index เดียว แต่การเชื่อมด้วย OR ก็ยังเกิด Sequential scan ของ column ที่เหลือ

การมี index จะช่วยให้การอ่านสามารถทำได้อย่างรวดเร็ว แต่จะส่งผลเสียกับการเขียนที่ช้าลง เพราะต้องเสียเวลากับการ update ข้อมูล index ดังนั้น ต้องใช้ index ให้เหมาะสมกับงาน เช่น ใช้หลาย index สำหรับงาน Data Warehouse แต่งานที่เป็น online application ที่ต้องมีการเขียนบ่อยๆ ก็จะไม่เหมาะ เพราะจะส่งผลต่อ performance ของ write transaction

การสร้าง index เองก็ต้องระวังเรื่องความซ้ำซ้อน (overlapping)

test=# SELECT indexname, indexdef FROM pg_indexes WHERE tablename='transactions';
        indexname         |                                          indexdef
--------------------------+---------------------------------------------------------------------------------------------
 transactions_pkey        | CREATE UNIQUE INDEX transactions_pkey ON public.transactions USING btree (id)
 transactions_from_idx    | CREATE INDEX transactions_from_idx ON public.transactions USING btree (acc_from)
 transactions_to_idx      | CREATE INDEX transactions_to_idx ON public.transactions USING btree (acc_to)
 transactions_from_to_idx | CREATE INDEX transactions_from_to_idx ON public.transactions USING btree (acc_from, acc_to)
(4 rows)

การสร้าง index ของ postgresql จะใช้ btree ซึ่งเป็น default ของ SQL index type และ Sorted list ดังนั้นค่า index ของ column จะถูก sort ตามลำดับ ตัวอย่างที่ผ่านมาเมื่อเราใช้ compound index จะได้ผลลัพธ์ดีกว่าเพราะการ sort จะทำที่ column แรกแล้ว sort ที่ column ที่สองสัมพันธ์กับ column แรก การค้นหาในลักษณะนี้จะได้ผลลัพธ์เร็วกว่าเพราะข้อมูลถูกเรียงไว้ให้แล้ว เมื่อเทียบกับข้อมูลที่ถูกเรียงแต่ไม่สัมพันธ์กันเหมือนแบบแรก

ขณะเดียวกัน ถ้าเราดู index ที่สร้างขึ้นมาจะพบว่า index column acc_from ถูกสร้างไว้สองที่ คือ transactions_from_idx และ transactions_from_to_idx เราสามารถลดความซ้ำซ้อนนี้ได้ด้วยการ drop transactions_from_idx และให้ plan เลือกใช้ index ของ column acc_from จาก index transactions_from_to_idx

test=# DROP INDEX transactions_from_idx;
DROP INDEX

test=# EXPLAIN (ANALYSE) SELECT * FROM transactions WHERE acc_from='person1633';
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on transactions  (cost=234.10..36641.59 rows=10004 width=28) (actual time=18.149..224.544 rows=10037 loops=1)
   Recheck Cond: (acc_from = 'person1633'::text)
   Heap Blocks: exact=9975
   ->  Bitmap Index Scan on transactions_from_to_idx  (cost=0.00..231.60 rows=10004 width=0) (actual time=16.133..16.133 rows=10037 loops=1)
         Index Cond: (acc_from = 'person1633'::text)
 Planning Time: 0.793 ms
 Execution Time: 225.338 ms
(7 rows)

จากผลลัพธ์ที่ได้ plan มีการใช้ index transactions_from_to_idx และผลลัพธ์ Execution Time ก็ไม่ต่างจากการมี index transactions_from_idx

กรณีนี้ไม่สามารถใช้กับ transactions_to_idx เพราะ transactions_from_to_idx เป็นการ sort column acc_to ตาม column acc_from จึงไม่ได้เป็นการ sort แบบอิสระเหมือน column acc_from ที่ไม่สนใจ column acc_to ดังนั้นกรณีนี้จึงใช้ได้เฉพาะ column แรกของ compound index เท่านั้น

ตัวอย่างที่ผ่านมาเรา query ข้อมูลทุกอย่างใน record ด้วย ‘*’ จะพบว่ามีสองขั้นตอนคืออ่านข้อมูลที่ตรงกับ index แล้วจึงดึงข้อมูลจาก heap (main table) ซึ่งก็คือ Bitmap Heap Scan on transactions

ถ้าเราเปลี่ยนจากอ่านข้อมูลทุกอย่างใน record เป็นอ่านเฉพาะ index column

test=# EXPLAIN (ANALYSE) SELECT acc_from, acc_to FROM transactions WHERE acc_from='person1633';
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using transactions_from_to_idx on transactions  (cost=0.57..335.64 rows=10004 width=20) (actual time=0.460..10.586 rows=10037 loops=1)
   Index Cond: (acc_from = 'person1633'::text)
   Heap Fetches: 0
 Planning Time: 0.358 ms
 Execution Time: 11.276 ms
(5 rows)

ผลลัพธ์จะได้ Execution Time ที่น้อยมาก เพราะมีแค่ขั้นตอนเดียวคือ match ข้อมูลตาม index โดยไม่ต้องอ่านข้อมูลใน main table โดยใน plan จะเห็นว่ามีการใช้ Index Only Scan

การใช้ Index Only Scan จะได้ performance ที่ดีมากๆ โดยที่เราไม่ต้องกังวลว่า data ที่ได้มาจะตรงกับ main table หรือไม่ เพราะกระบวนการ update ใดๆ ข้อมูล index และ main table จะมีการ update ให้ตรงกันอยู่แล้ว แต่ถ้าเมื่อไรที่เราเพิ่ม column ที่ไม่ใช่ index ใน select statement จะทำให้ plan กับมาใช้ Bitmap Heap Scan ซึ่งจะทำให้ query กลับมาอ่านข้อมูลใน main table และจะทำให้ query เรากลับมาช้าเหมือนเดิม

test=# EXPLAIN (ANALYSE) SELECT acc_from, acc_to, value FROM transactions WHERE acc_from='person1633';
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on transactions  (cost=234.10..36641.59 rows=10004 width=24) (actual time=6.518..99.584 rows=10037 loops=1)
   Recheck Cond: (acc_from = 'person1633'::text)
   Heap Blocks: exact=9975
   ->  Bitmap Index Scan on transactions_from_to_idx  (cost=0.00..231.60 rows=10004 width=0) (actual time=4.501..4.502 rows=10037 loops=1)
         Index Cond: (acc_from = 'person1633'::text)
 Planning Time: 0.319 ms
 Execution Time: 100.680 ms
(7 rows)

วิธีที่จะจัดการปัญหานี้คือการใช้ Index-only queries (+INCLUDE) ซึ้งเป็นวิธีทั่วไปที่ support ใน database อื่นๆ นอกจาก PostgreSQL ด้วย

test=# DROP INDEX transactions_from_to_idx;
DROP INDEX
test=# CREATE INDEX transactions_from_to_idx
test-# ON transactions (acc_from, acc_to)
test-# INCLUDE (value);
CREATE INDEX
test=# ANALYSE transactions;
ANALYZE

test=# EXPLAIN (ANALYSE) SELECT acc_from, acc_to, value FROM transactions WHERE acc_from='person1633';
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using transactions_from_to_idx on transactions  (cost=0.57..422.66 rows=9948 width=24) (actual time=2.391..8.608 rows=10037 loops=1)
   Index Cond: (acc_from = 'person1633'::text)
   Heap Fetches: 0
 Planning Time: 4.168 ms
 Execution Time: 9.156 ms
(5 rows)

เมื่อทดสอบอีกครั้งจะพบว่า plan กลับมาใช้ index only scan และ Execution Time ทำงานได้เวลาที่ดีเช่นเดิม

ทีนี้อาจจะเกิดความสงสัยว่าเราน่าจะมีอีกวิธีคือใช้ compound index ให้มี column value เข้าไปด้วย เช่น CREATE INDEX transactions_from_to_idx ON transactions (acc_from, acc_to, value); วิธีนี้มีข้อเสียคือ column value จะต้องถูก sort ด้วย จะเป็นการเพิ่ม process เทียบกับการที่ไม่ต้อง sort จะทำให้ลดขั้นตอนและสูญเสียเวลาน้อยกว่า

วิธีการใช้ INCLUDE เป็นการบอก database ว่าไม่ต้อง order column ซึ่งจะช่วยลดงานในขั้นตอน rebalance ของ Tree

INCLUDE สามารถใช้กับ UNIQUE indices ได้ แต่ต้องไม่ถูกใช้ใน unique constraint

สิ่งที่ต้องระวังอีกอย่างคือ Cache Memory เนื่องจาก index เองก็มี limit เกี่ยวกับ data ที่จะจัดเก็บใน record การเก็บ data ทุกอย่างไว้ใน index จะทำให้ index ใหญ่เกินไป และส่งผลทั้งต่อการเขียน และอ่านได้ ต้องพิจารณาการใช้งานอย่างเหมาะสม

นอกจากการ Tuning ด้วย index แล้วต้องพิจารณาเรื่อง Pages และ Processor Cache เพราะการ load data จาก main table จะต้องอ่าน file ที่อยู่ใน Disk มาที่ RAM ที่ละส่วน (chunks) หรือเรียกว่า pages (blocks ของ data มีขนาดมาตรฐานที่ 4 kB) ปัจุบัน CPU รุ่นใหม่ๆ สามารถอ่านข้อมูลขนาดใหญ่ (megabytes) ได้จึงควร tuning ให้เหมาะสม

การบันทึกข้อมูล PostgreSQL ทำการเก็บข้อมูลของ Table ในรูปแบบของ pages โดยที่ 1 record จะอยู่ใน page เดียวเท่านั้น และใน 1 page อาจจะมีหลาย 100 records ขึ้นอยู่กับขนาดของ record

กรณีอ่านข้อมูล page ที่ต้องการจะถูกอ่านจาก Disk ไปยัง RAM โดยที่ performance จะขึ้นอยู่กับความเร็วการอ่านของ Disk เมื่อมีการเรียกใช้ page เดิม ข้อมูลจะถูกอ่านจาก RAM แทนที่จะอ่านจาก Disk อีกรอบ แต่ถ้า space ของ RAM มีน้อย database จะทำการลบข้อมูลจาก RAM เพื่อให้ได้เนื้อที่กลับมาในการ load page ใหม่เข้ามาที่ RAM การลบข้อมูล page เก่าจาก RAM และการอ่านจาก Disk ส่งผลต่อ performance โดยรวมของ database เป็นอย่างมาก

การเพิ่มขนาดของ RAM ให้เหมาะสมเช่น database ขนาดเล็กทุก pages อาจจะอยู่ใน RAM ทำให้ทำงานเร็ว แต่ถ้า data มีขนาดใหญ่จะเกิดการอ่านจาก disk และขณะเดียวกันก็ต้องลบ pages จาก RAM ด้วย performance ก็จะช้า จึงต้อง assign RAM ให้เหมาะสม

การ query ข้อมูล อย่างเช่น SELECT * FROM transactions WHERE acc_from=’person1633′; อาจจะต้องการข้อมูลจากทั้ง page 1 และ page 2 โดยแต่ละ page อาจจะมีหลาย 100 records ซึ่งจะใช้เวลามากถ้าต้องอ่านจาก page ทั้งหมด กลับกันถ้า query ต้องการข้อมูลจาก page เดียวก็จะได้ผลลัพธ์ที่ดีกว่า จึงเป็นที่มาว่าทำไม index-only ถึงเร็ว เพราะการที่ index ถูก sort จะทำให้โอกาสข้อมูลจะกระจายไปยังหลายๆ page นั้นน้อยกว่านั่นเอง

ย้อนกลับไปดู analysis เดิม จะพบข้อมูลที่น่าสนใจคือ

test=# EXPLAIN (ANALYSE) SELECT * FROM transactions WHERE acc_from='person1633';
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on transactions  (cost=234.10..36641.59 rows=10004 width=28) (actual time=18.149..224.544 rows=10037 loops=1)
   Recheck Cond: (acc_from = 'person1633'::text)
   Heap Blocks: exact=9975
   ->  Bitmap Index Scan on transactions_from_to_idx  (cost=0.00..231.60 rows=10004 width=0) (actual time=16.133..16.133 rows=10037 loops=1)
         Index Cond: (acc_from = 'person1633'::text)
 Planning Time: 0.793 ms
 Execution Time: 225.338 ms
(7 rows)

จะพบว่ามีการดึงข้อมูลจำนวน 10037 records จาก table transactions ที่กระจายอยู่ใน page จำนวน 9975 pages หมายความว่าข้อมูลที่ต้องการถูกกระจายไปยังทุกๆ page page ละ 1 record query จะเร็วขึ้นมากถ้าข้อมูลไม่กระจายและถูก pack อยู่ในไม่กี่ page

การจัดการปัญหานี้ค่อนข้างยาก วิธีที่ง่ายคือการแยก read/write ข้อมูล และการ archive data ที่ไม่มีการเปลี่ยนแปลง แยก node เฉพาะสำหรับงาน อีกข้อแนะนำคือการลดขนาดของ record ให้เล็กลงเพื่อให้ 1 page สามารถเก็บ records ได้มากที่สุด

การกระจายของข้อมูลใน table เองก็มีผลต่อ performance ของการอ่านข้อมูล สามารถทดสอบได้จากการสร้าง table ใหม่คือ transaction2 แต่ทำการ order column acc_from หลังจากนั้น create index และดูผลลัพธ์ของ Execution Time

test=# CREATE TABLE transactions2 (id INT PRIMARY KEY,
  acc_from  TEXT   NOT NULL,
  acc_to    TEXT   NOT NULL,
  value     INT    NOT NULL);

test=# INSERT INTO transactions2 (id, acc_from, acc_to, value)
  SELECT id, acc_from, acc_to, value FROM transactions
  ORDER BY acc_from;

test=# CREATE INDEX transactions2_from_idx ON transactions2 (acc_from);

test=# ANALYSE transactions2;
ANALYZE

test=# EXPLAIN (ANALYSE) SELECT * FROM transactions2 WHERE acc_from='person1633';

อีกปัจจัยที่มีผลต่อ performance ของ database คือ data fragment ที่เกิดจากการ delete หรือ update บาง record ใน table ทั้งนี้ข้อมูล master table จะมี column ID ที่บอกว่า record นั้นถูกเขียนที่ page number และ record index อะไร เพื่อให้อ่านข้อมูลได้ถูกที่ ซึ่งจริงๆ แล้วข้อมูลไม่ได้ถูกลบออกทันที แต่จะถูก mark ว่า record นั้นเป็น dead record และข้อมูลใหม่หรือข้อมูล update จะถูกเพิ่มเข้าไป PostgreSQL เพิ่มกระบวนการในตรวจสอบ dead record ที่อยู่ใน page ด้วยการ track ข้อมูล dead record จาก memory ซึ่งในกระบวนการอ่านข้อมูลจาก page ถ้าพบว่า page นั้นมี dead record ก็จะมีกระบวนการตรวจสอบเพิ่มเติม แต่ถ้าเช็คแล้ว page นั้นไม่มี dead record ก็จะ return ทั้ง page ได้เลย ทำให้ performance จะดีขึ้นมาก

กระบวนการที่จะ scan dead record แล้วนำ space มาใช้ใหม่เรียกว่า Vacuum โดยที่กระบวนการนี้จะทำให้สามารถเขียนข้อมูลใหม่ที่ตำแหน่ง dead record ได้ แต่ก็ต้องเป็น record ของ table เดียวกันถึงจะใช้ space นี้ได้พอดี

ดังนั้นจำเป็นต้อง run process Vacuum อย่างสม่ำเสมอ เพื่อให้ได้ space กลับมาใช้อีกครั้ง อีกอย่างคือการที่ page มี space ว่างเยอะจากการที่ space ไม่ได้ถูกใช้เพราะไม่มี record ของ table เดียวกันสร้างใหม่ ก็จะเกิด space ใน page มากเกินไป ทำให้ 1 page มีจำนวน record น้อยลง ส่งผลให้การอ่านต้องอ่านจากหลาย page ซึ่งก็จะส่งผลต่อ performance เป็นเหตุการณ์ data fragmentation เช่นเดียวกับ Disk ที่เราต้องใช้ defragmenter utiltity ในการลด fragmentation ของ drive

การป้องกันปัญหานี้ จะต้องหลีกเลี่ยงการใช้งาน live data (เปลี่ยนแปลงบ่อย) กับ achived data (data ที่ไม่ค่อยเปลี่ยนแปลง) ใน node เดียวกัน การแยก node จะทำให้ปัญหานี้น้อยลง

…end…

Tech Notes

Technology is changing over time like the human age, documenting the finding helps me remember my footstep

Database performance tuning for App Dev

Share this: