Bài viết này giúp anh em hiểu được cơ chế xử lý của PostgreSQL khi có nhiều transaction cùng chỉnh sửa một Objects. Và kết quả của cơ chế xử lý này sẽ tạo ra các Dead Tuples trong bảng, điều này ảnh hưởng tới hiệu năng trong thực tế ra sao. Dead Tuples là gì, làm sao để xử lý nó?

Tôi sẽ giúp anh em tìm hiểu ngay sau đây

1. Tìm hiểu bản chất xử lý các giao dịch đồng thời trong PostgreSQL

Trong môi trường thực tế, có nhiều SESSION sẽ cùng thực hiện các thao tác trên một bảng, thâm chí là cùng 1 bản ghi. Ví dụ

• Bảng thông tin lưu SẢN PHẨM trong một cửa hàng giao dịch ONLINE, cùng thời điểm 9h có 3 người dùng cùng thực hiện thao tác có sử dụng thông tin của bảng SẢN PHẨM.
• Người dùng TRẦN QUỐC HUY thực hiện một báo cáo thống kê xem còn bao nhiêu sản phẩm trong kho
• Người dùng LAN ANH thực hiện việc cập nhật thông tin một sản phẩm
• Người dùng TÙNG thực hiện thêm mới một SẢN PHẨM

Rõ ràng một điều ở đây:

• Tại cùng một thời điểm, cơ sở dữ liệu sẽ phải xử lý cả các THÔNG TIN CŨ (dữ liệu hiện tại) và các thông tin MỚI (các dữ liệu ĐANG BỊ CHỈNH SỬA).

Vậy PostgreSQL sẽ lưu các thông tin này đồng thời ra sao, chúng sẽ được xử lý như thế nào?

Tôi sẽ giúp anh em làm rõ vấn đề này

Về cơ chế hoạt động, PostgreSQL sẽ lưu cả DỮ LIỆU HIỆN TẠI và DỮ LIỆU ĐANG BỊ THAY ĐỔI ở chung một chỗ luôn. Hiểu một cách đơn giản thì tất cả thông tin này đều lưu chung trong chính TABLE của PostgreSQL.

PostgreSQL thiết kế thêm 2 cột lưu thông tin với các Table. Đây là 2 cột “ẩn”, anh em phải chỉ định tường minh trong các câu lệnh SELECT thì mới thấy được nhé

• Cột XMIN: lưu lại TRANSACTION ID đã thực hiện INSERT hoặc UPDATE bản ghi
• Cột XMAX: lưu lại TRANSACTION ID đã thực hiện DELETE bản ghi.

Tôi sẽ làm 1 DEMO để anh em dễ hiểu hơn

Tạo bảng phục vụ việc kiểm thử wecommit_test

create table wecommit_test
(id int,
name varchar(20));

Thêm dữ liệu vào bảng

insert into wecommit_test values(1, 'Tran Quoc Huy')

Kiểm tra thông tin bảng lúc này

• Ghi chú: Trong bảng WECOMMIT_TEST mặc dù chúng ta không hề thiết kế có cột XMIN và XMAX nhưng hệ thống vẫn tự động thêm 2 cột này

select xmin, xmax, * from wecommit_test

Giải thích

• XMIN = 9613007 thể hiện Transaction ID = 9613007 đã INSERT bản ghi ID = 1, Name= ‘Tran Quoc Huy’
• XMAX = 0 thể hiện bản ghi này được khởi tạo lần đầu.

Bây giờ ta tiếp tục thực hiện thêm dữ liệu, lần này tôi sẽ thực hiện một transaction với 2 câu lệnh INSERT cùng lúc

begin transaction
insert into wecommit_test values(2, 'Wecommit2');
insert into wecommit_test values(3, 'Wecommit3');

select xmin, xmax, * from wecommit_test

Giải thích ý nghĩa

• Bây giờ anh em sẽ thấy 2 bản ghi mới (ID =1 và ID=2) có chung một giá trị XMIN=9613008. Điều này có nghĩa là 2 bản ghi này được tạo ra bởi cùng 1 transaction, và transaction này có transaction id là 9613008
• xmax của cả 2 bản ghi mới đề bằng 0 nghĩa là 2 bản ghi này được Insert vào lần đầu.

Tôi tiếp tục thực hiện lệnh UPDATE (vẫn ở trong transaction với 2 câu lệnh DELETE trên nhé anh em)

update wecommit_test
set name='Wecommit1'
where id=1

Kiểm tra lại thông tin của bảng lúc này xem thế nào nhé

select xmin, xmax, * from wecommit_test

Chỗ này có điều thú vị

• Anh em thấy rằng bây giờ bản ghi có ID=1 có giá trị XMIN= 9613008, điều này nghĩa là bản ghi này được tạo ra bởi transaction 9613008, giá trị này giống với 2 bản ghi ID=2 và ID =3 vì chúng ta đang thực hiện trong cùng 1 block transaction.
• Chỗ hay ở đây là XMAX = 0. Điều này cho thấy bản ghi ID = 1 đã được khởi tạo mới.
• Việc UPDATE bản chất ở đây là POSTGRESQL thực hiện INSERT mới bản ghi !!

Lưu ý ở đây rằng: TRANSACTION hiện tại tôi chưa kết thúc (chưa thực hiện COMMIT hay ROLLBACK).

Giả sử bây giờ có 1 SESSION khác đồng thời kết nối vào database lúc này và thực hiện thao tác trên bảng WECOMMIT_TEST, thì chuyện gì sẽ xảy ra?

Tôi sẽ cùng anh em làm rõ vấn đề này ngay bây giờ

Thực hiện tạo một kết nối với đến PostgreSQL và thưc hiện kiểm tra thông tin trên bảng WECOMMIT_TEST

select xmin, xmax, * from wecommit_test

Phân tích kết quả thu được

• Thứ nhất: SESSION hiện tại chỉ nhất thấy bảng WECOMMIT_TEST có một bản ghi duy nhất, trong khi SESSION đang cập nhật dữ liệu bên trên thấy table WECOMMIT_TEST có tới 3 bản ghi. Điều này để đảm bảo việc nhất quán của dữ liệu (tính chất mặc định của Database: các session nếu không ở trong transactiond dang sửa đổi dữ liệu thì sẽ chỉ nhìn thấy các dữ liệu chưa được chỉnh sửa).
• Thứ hai: Bản ghi ID = 1 bây giờ có thông tin cột XMIN và XMAX thể hiện
  • XMIN = 9613007 vẫn như cũ, thể hiện bản ghi ID = 1 này được tạo bởi TRANSACTION ID = 9613007.
  • XMAX = 9613008 thể hiện bản ghi này đang bị thay đổi bởi TRANSACTION ID = 9613008.

Nếu anh em có câu hỏi: Chúng ta có thể kiểm tra được Transaction ID hiện tại của mình không?

Câu trả lời: Hoàn toàn có thể, nếu anh em muốn kiểm nghiệm có thể thực hiện lệnh sau nhé

SELECT txid_current();

Khi thực hiện anh em sẽ thấy kết quả như sau

SELECT txid_current();

Nhìn tổng thể thì chúng ta

Ghi chú:

• Tôi thêm thời gian để anh em dễ hình dung hơn về thứ tự thực hiện của các lệnh trong DEMO.

Bây giờ chuyện gì xảy ra nếu tôi thực hiện ROLLBACK TRANSACTION_ID=9613008 ?

Tôi sẽ thực hiện và kiểm tra kết quả cho anh em nhé

Kêt quả như sau khi thực hiện trên TRANSACTION 9613009

select xmin, xmax, * from wecommit_test

Giá trị của XMAX vẫn là 9613008, điều này cho thấy mặc dù việc UPDATE bản ghi ID = 1 đã bị HỦY (ROLLBACK), nhưng PostgreSQL vẫn lưu lại đã từng có TRANSACTION có thực hiện UPDATE trên bản ghi này.

Bản chất việc chúng ta nhìn thấy bảng WECOMMIT_TEST lúc này chỉ còn 1 ROW duy nhất, nhưng thực sự PostgreSQL vẫn phải lưu trữ cả 4 bản ghi, trong đó 3 bản thi ở trạng thái “DEAD” (nói đơn giản là không thể nhìn thấy các ông này vì vô dụng rồi)

Các thông tin này anh em có thể kiểm chứng thông qua pg_stat_user_table, dưới đây là câu lệnh tôi thực hiện kiểm tra tỷ lệ các bản ghi (DEAD và LIVE TUPLE) trong bảng WECOMMIT_TEST

SELECT
n_live_tup AS live_tuples,
n_dead_tup AS dead_tuples
FROM
pg_stat_user_tables
where relname='wecommit_test'

Tại đây anh em đã hiểu rõ hơn về cơ chế quản lý dữ liệu trong PostgreSQL, cơ chế này theo thuật ngữ gọi là Multi-Version Concurrency Control (hay viết tắt là MVCC).

Chúng ta thử cùng đào sâu hơn chỗ này nhé.

Một số câu hỏi anh em có thể có như

• Nếu một Table có quá nhiều DEAD TUPLES thì có ảnh hưởng tới hiệu năng trong PostgreSQL không?
• PostgreSQL sẽ làm gì với các DEAD TUPLES nhỉ?

Các nội dung này tôi sẽ phân tích ngay sau đây

2. Đánh giá hiệu năng của cơ sở dữ liệu PostgreSQL

2.1. Giả lập Table 10 triệu rows và kịch bản đánh giá hiệu năng

CREATE TABLE wecommit_orders (
    order_id serial PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE,
    total_amount DECIMAL
);

INSERT INTO wecommit_orders (customer_name, order_date, total_amount)
SELECT
'Customer' || id AS customer_name,
MAKE_DATE(2023, 1, 1) + (random() * 365)::integer AS order_date,
random() * 1000 AS total_amount
FROM generate_series(1, 10000000) id;

2.2. Đánh giá tình trạng lưu trữ của bảng khi vừa khởi tạo dữ liệu

Kiểm tra dung lượng của Table sau khi vừa Insert 10.000 bản ghi

SELECT pg_size_pretty(pg_relation_size('wecommit_orders')) ;

Kiểm tra tình trạng lưu trữ các bản ghi (TUPLE) hiện tại của bảng

SELECT
n_live_tup AS live_tuples,
n_dead_tup AS dead_tuples
FROM
pg_stat_user_tables
where relname='wecommit_orders'

2.3. Thực hiện kịch bản khiến bảng phát sinh nhiều Dead Tuples do Update, Delete

— Thực hiện Update 5 triệu  bản ghi trong bảng WECOMMIT_ORDERS

UPDATE wecommit_orders SET total_amount = total_amount * 2 WHERE order_id % 2 = 0;

Cùng kiểm tra xem sau câu lệnh DELETE này thì tình trạng table WECOMMIT_ORDERS như thế nào

SELECT
n_live_tup AS live_tuples,
n_dead_tup AS dead_tuples
FROM
pg_stat_user_tables
where relname='wecommit_orders'

Như vậy với việc UPDATE 5.000.000 bản ghi, PostgreSQL đã làm như sau:

• Tạo mới 5.000.000 bản ghi, như vậy tổng số LIVE TUPLES vẫn ghi nhận là 10.000.000
• 5.000.000 bản ghi bị UPDATE sẽ bị đánh dấu thành DEAD TUPLES

— Thực hiện Delete 2 triệubản ghi bản ghi trong bảng WECOMMIT_ORDERS

DELETE FROM wecommit_orders WHERE order_id % 5 = 0;

Kiểm tra sau câu lệnh Delete thì bảng WECOMMIT_ORDERS như thế nào nhé

SELECT
n_live_tup AS live_tuples,
n_dead_tup AS dead_tuples
FROM
pg_stat_user_tables
where relname='wecommit_orders'

Như vậy khi chúng ta thực hiện DELETE 2.000 bản ghi, PostgreSQL đã làm như sau

• 2.000.000 bản ghi bị DELETE đã bị đánh dấu thành DEAD TUPLES
• Số lượng LIVE TUPLES bây giờ chỉ còn 10.000.000 – 2.000.000 = 8.000.000

Kiểm tra dung lượng của bảng WECOMMIT_ORDERS tại thời điểm này

SELECT pg_size_pretty(pg_relation_size('wecommit_orders')) ;

Tại đây anh em thấy, mặc dù số lượng bản ghi trong Table ít đi (chỉ còn 8.000.000 bản ghi so với 10.000.000 bản ghi lúc ban đầu), nhưng dung lượng của bảng đã tăng hơn 1.5 lần (977 MB so với 651 MB)

2.4. Thực hiện phân tích hiệu năng câu lệnh làm việc trên PostgreSQL

select * from Wecommit_Orders where total_amount > 15 and total_amount < 17

Tôi sẽ thực hiện phân tích chiến lược thực thi của câu lệnh trên sử dụng EXPLAIN ANALYZE

• Trường hợp anh em chưa biết về chiến lược thực thi thì có thể đọc các bài viết của tôi về vấn đề này:
  • Đọc bài viết SQL Execution Plans trong tối ưu Database: Đọc tại đây

explain analyze select * from Wecommit_Orders where total_amount > 15 and total_amount < 17

Chiến lược thực thi của câu lệnh như sau

"Seq Scan on wecommit_orders (cost=0.00..349998.10 rows=24487 width=31) (actual time=0.357..4748.262 rows=12044 loops=1)"
" Filter: ((total_amount > '15'::numeric) AND (total_amount < '17'::numeric))"
" Rows Removed by Filter: 7987956"
"Planning Time: 0.106 ms"
"Execution Time: 4751.208 ms"

Giải thích kết quả

• Câu lệnh có chi phí thực hiện COST = 349998.10
• Giải thuật để lấy được dữ liệu mong muốn là SEQ SCAN  – Có nghĩa là quét toàn bộ các BLOCK DỮ LIỆU trong PostgreSQL
• Thời gian thực thi của câu lệnh ước tính là 4751.208 ms
• Tất cả các kỹ thuật phân tích và tối ưu SQL này đều có trong chương trình Từ điển tối ưu 100x hiệu năng của tôi. Các anh em có thể đăng ký trải nghiệm miễn phí bằng cách CLICK form đăng ký này nhé.

2.5. Xử lý phân mảnh (DEAD TUPLES) trong PostgreSQL và đánh giá lại hiệu năng

Trong PostgreSQL, để thực hiện dọn dẹp các ĐEAD TUPLES chúng ta có thể thực hiện VACUUM.

Cách thức thực hiện chủ động xử lý (hay còn lại xử lý “bằng cơm” ) như sau:

vacuum wecommit_orders

Kiểm tra lại tình trạng của bảng sau khi đã thực hiện VACUUM

SELECT
n_live_tup AS live_tuples,
n_dead_tup AS dead_tuples
FROM
pg_stat_user_tables
where relname='wecommit_orders'

Wow, toàn bộ DEAD TUPLES đã bị dọn dẹp (trước đây có 7.000.000 DEAD TUPLES, bây giờ giá trị này đã về 0).

Thực hiện kiểm tra hiệu năng của câu lệnh SQL sau khi đã dọn dẹp DEAD TUPLES

Chúng ta kiểm tra tiếp dung lượng của Index và Table thì có thay đổi gì không nhé

SELECT pg_size_pretty(pg_relation_size('wecommit_orders')) AS data_size;

Thực hiện kiểm tra chiến lược thực thi câu lệnh sử dụng EXPLAIN ANALYZE

explain analyze select * from Wecommit_Orders where total_amount > 15 and total_amount < 17

Kết quả như sau:

“Seq Scan on wecommit_orders (cost=0.00..244999.00 rows=13059 width=31) (actual time=0.285..1829.652 rows=12044 loops=1)”
” Filter: ((total_amount > ’15’::numeric) AND (total_amount < ’17’::numeric))”
” Rows Removed by Filter: 7987956″
“Planning Time: 0.167 ms”
“Execution Time: 1831.047 ms”

Chúng ta nhận thấy điều gì ở kết quả:

PostgreSQL dự đoán chi phí thực hiện của câu lệnh SQL đã cải thiện, chỉ còn 244999.00

Thời gian ước tính được cải thiên còn 1831.047 ms

2.6. Làm thế nào tối ưu bảng có DEAD TUPLES một cách triệt để hơn nữa?

Mặc dù loại bỏ DEAD TUPLES sẽ khiến hiệu năng được cải thiện, nhưng làm sao để loại bỏ hoàn toàn các khoảng trống dữ liệu do DEAD TUPLES tạo ra?

Anh em có thể sử dụng phương án VACCUM FULL

Cùng thực hiện nhé

Lưu ý:

• Sử dụng VACUUM FULL sẽ gây LOCK nhé anh em, vì thế khi áp dụng trong thực tế cần có chiến lược phù hợp với từng database nhé anh em.
• Vấn đề thực hiện VACUUM trong thực tế sẽ phải kết hợp giữa việc “thủ công” và tiến trình tự động VACUUM của PostgreSQL. Các bài toán và tình huống thực tế này tôi thường xuyên tư vấn trong chương trình Tối ưu cơ sở dữ liệu “Từ điển tối ưu 100x hiệu năng”. Các anh em có thể đăng ký trải nghiệm miễn phí chương trình bằng cách CLICK vào Form đăng ký sau nhé.

2.7. Tổng hợp kết quả trước và sau khi tối ưu DEAD TUPLES

4. Nếu bạn muốn liên hệ với tôi

Tác giả: Trần Quốc Huy – Founder & CEO Wecommit.

Các bạn có thể liên hệ, thảo luận các kiến thức về tối ưu cùng tôi qua kênh FB cá nhân của tôi

Facebook: https://www.facebook.com/tranquochuy.toiuu/