Phần trước đã tìm hiểu về một mớ lý thuyết của Vacuum, phần này sẽ xem qua về reindex và tập trung vào practice với vacuum để kiểm nghiệm thực tế thế nào. Let’s begin.
1) Reindex
REINDEX, nghe phát hiểu luôn ý nghĩa, đó là thực hiện build lại index cho index/table/database…
REINDEX INDEX index_name;
REINDEX TABLE table_name;
REINDEX SCHEMA schema_name;
REINDEX DATABASE database_name;
Về cơ bản, reindex build lại index table từ đầu, giống kết quả của việc drop và create index. Tuy nhiên cơ chế locking giữa hai cách thực thi có đôi chút khác biệt.
Trước khi tìm hiểu kĩ hơn, cùng trả lời câu hỏi vì sao cần reindex. Có 3 lý do chính ta cần thực hiện reindex:
- Thứ nhất, không có gì là hoàn hảo. Index table hoàn toàn có khả năng bị lỗi do hardware failure hoặc… PostgreSQL bugs
. Reindex như một cách recovery hệ thống trong trường hợp này.
- Thứ hai, index cũng là table, cũng sẽ có dead tuple. Nếu quá nhiều dead tuple thì quá trình scan cũng ảnh hưởng đến performance và tốn disk space.
- Cuối cùng, khi thực hiện alter index, ví dụ từ một column thành hai column.
Vậy sự khác biệt giữa reindex và drop & create index là gì?
Với reindex:
- Hệ thống sẽ tạo lại index table sử dụng shared lock. Có thể concurrent read nhưng không thể concurrent write.
Với drop & create:
- DROP thực hiện exclusive lock trên table, đảm bảo không thể concurrent read/write tránh inconsistence data.
- Sau đó CREATE thực hiện shared lock trên table, đảm bảo không có concurrent write để thực hiện build index table.
Như vậy, reindex đâu đó sẽ cho performance tốt hơn so với việc xóa đi và tạo lại, tuy nhiên cost cho nó khá lớn. Do vậy chỉ khi không nghĩ ra cách nào khác thì thực hiện reindex.
2) Practice
Sử dụng data từ bài trước.
Trước tiên cần install extension pgstattuple để theo dõi các thông số liên quan đến dead tuple:
CREATE EXTENSION pgstattuple;
Kiểm tra thông tin tuple của table ENGINEER:
SELECT * FROM pgstattuple('engineer');
| table_len | tuple_count | dead_tuple_count | free_space | free_percent |
|---|---|---|---|---|
| 9150464 | 100000 | 0 | 44572 | 0.49 |
Còn một vài column khác tuy nhiên chỉ cần quan tâm đến những column sau:
- table_len: dung lượng table.
- tuple_count: tổng số lượng record hiện có trong table, bằng số lượng record insert vào table.
- dead_tuple_count: số lượng dead tuple. Do chưa thực hiện DELTE/UPDATE nên giá trị = 0.
- free_space: dung lượng còn trống trong các page của table.
- free_percent: tỉ lệ free_space/table_len.
Tiến hành delete 100 record trong table ENGINEER:
DELETE FROM engineer WHERE id IN (SELECT id FROM engineer ORDER BY id LIMIT 100);
Query lại statistic của table ENGINEER:
SELECT * FROM pgstattuple('engineer');
| table_len | tuple_count | dead_tuple_count | free_space | free_percent |
|---|---|---|---|---|
| 9150464 | 99900 | 100 | 44572 | 0.49 |
Như vậy đã có 100 dead tuple, vẫn nằm trong table. Free space không có gì thay đổi.
Thực hiện vacuum và kiểm tra lại xem thế nào:
VACUUM engineer;
SELECT * FROM pgstattuple('engineer');
| table_len | tuple_count | dead_tuple_count | free_space | free_percent |
|---|---|---|---|---|
| 9150464 | 99900 | 0 | 53168 | 0.58 |
Ta biết rằng vacuum không thực sự giải phóng disk space, mà chỉ clear dead tuple, dự trữ space cho future tuple. Như vậy, table len không đổi, dead tuple clear về 0 và free space đã tăng.
Nếu lý thuyết đúng như trên, khi thực hiện insert thêm record thì expect table len không đổi, tuple count tăng và free space giảm:
INSERT INTO engineer(first_name, last_name, gender, country_id, created)
VALUES('Vacuum', 'Test', 1, 1, CURRENT_TIMESTAMP);
SELECT * FROM pgstattuple('engineer');
| table_len | tuple_count | dead_tuple_count | free_space | free_percent |
|---|---|---|---|---|
| 9150464 | 99901 | 0 | 53104 | 0.58 |
Cuối cùng, tiến hành vacuum full để giải phóng disk space. Expect table len giảm do đã xóa 100 records, free space cũng giảm xuống xung quanh giá trị khi insert ban đầu là 44572:
VACUUM FULL engineer;
SELECT * FROM pgstattuple('engineer');
| table_len | tuple_count | dead_tuple_count | free_space | free_percent |
|---|---|---|---|---|
| 9142272 | 99901 | 0 | 45344 | 0.5 |
3) Khi nào nên sử dụng vacuum full?
Câu trả lời sẽ dựa trên mục đích của câu lệnh và mục đích mà bạn cần.
Cả 2 đều giải phóng unused disk space, giảm thiểu phân mảnh, giúp quá trình scan table/index table mượt mà hơn, đem lại performance tốt hơn. Tuy nhiên cost để xử lý là khá cao.
Do vậy, có một vài lưu ý khi quyết định sử dụng hay không:
- Vacuum full với table có số lượng record nhỏ không đem lại nhiều hiệu quả.
- Do vậy, chỉ thực sự cần thiết khi table có tần suất UPDATE/DELETE cực nhiều và số lượng record cực lớn. Mặc định PostgreSQL đã enable autovacuum nên với những table bình thường không cần quá lo lắng.
- Đồng nghĩ với việc đó, quá trình vacuum full có thể diễn ra trong một khoảng thời gian dài, ảnh hưởng đến business. Do vậy nếu có thực hiện thì nên lựa lúc nửa đêm hoặc giờ thấp điểm.
- Đừng sử dụng quá thường xuyên, nó không đem lại hiệu quả nhiều. Kinh nghiệm thực tế once per month là đủ. Lưu ý rằng đó là những table có tần suất modify data cực lớn mới cần sử dụng.
- Cố gắng tránh vacuum full. Thay vì thế hãy tunning các thông số autovacuum và thực hiện vacuum là đủ.
- Chỉ nên dùng khi… hết cách. Hoặc bạn muốn nghịch hệ thống… cũng không có vấn đề gì với mình
Reference
- https://github.com/postgres/postgres
- https://www.postgresql.org/docs/13/mvcc.html
- https://stackify.com/postgresql-performance-tutorial/
- https://wiki.postgresql.org/wiki/Performance_Optimization
- https://en.wikipedia.org/wiki/Multiversion_concurrency_control
- https://www.geeksforgeeks.org/difference-between-shared-lock-and-exclusive-lock/
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
