- Từ cảnh báo thiếu dung lượng lưu trữ, nhóm đã tiến hành dọn dẹp PostgreSQL và thu hồi hơn 70GB dung lượng chưa tối ưu/không sử dụng mà không xóa index hay dữ liệu
- Ban đầu, họ kiểm tra các kỹ thuật thu hồi phổ biến như index không dùng, bloat của bảng/index,
REINDEX CONCURRENTLY, pg_repack, và B-Tree deduplication của PostgreSQL 13
- Khác biệt mang tính quyết định là PostgreSQL, không giống Oracle, cũng lập chỉ mục cho giá trị NULL; khi đổi index toàn phần trên
cancelled_by_user_id — nơi phần lớn giá trị là NULL — thành partial index, kích thước giảm từ 769MB xuống dưới 5MB
- Tìm các index một cột có
pg_stats.null_frac cao để chọn ứng viên partial index với điều kiện WHERE column IS NOT NULL, rồi sau khi reset bộ đếm thống kê, dùng pg_stat_all_indexes để xác nhận mức sử dụng thực tế
- Trong Django, nullable
ForeignKey có thể tạo B-Tree index mặc định, nên cần chỉ định db_index=False và tự định nghĩa partial index bằng models.Index(..., condition=Q(...__isnull=False))
Dọn dẹp bắt đầu từ cảnh báo thiếu dung lượng lưu trữ
- Cứ vài tháng, hệ thống giám sát database lại phát cảnh báo thiếu dung lượng lưu trữ; thông thường cách xử lý là bổ sung storage
- Khi đó, do đang trong giai đoạn cách ly nên tải hệ thống thấp, là thời điểm phù hợp để thử các tác vụ dọn dẹp vốn nặng hơn bình thường
- Kết quả là thu hồi được hơn 70GB dung lượng chưa tối ưu/không sử dụng mà không xóa index hay dữ liệu
- Chỉ với việc tái cấu trúc index và bảng theo cách thông thường đã giảm được nhiều dung lượng, và sau đó một phát hiện ngoài dự kiến giúp loại bỏ thêm khoảng 20GB giá trị index không dùng
Những nguyên nhân được nghi ngờ trước tiên
-
Index không sử dụng
- Index không sử dụng được tạo để phục vụ hiệu năng truy vấn, nhưng vẫn chiếm dung lượng và có thể làm chậm
INSERT và UPDATE
- Trong
pg_stat_all_indexes, tìm các index có idx_scan = 0, idx_tup_read = 0, idx_tup_fetch = 0 để xác định các index chưa từng được scan hoặc fetch kể từ lần reset thống kê gần nhất
- Có những trường hợp nhìn bề ngoài như không dùng nhưng không nên xóa
- Khi optimizer của PostgreSQL sử dụng metadata chứ không trực tiếp dùng index
- Index đảm bảo ràng buộc unique hoặc primary key của các bảng đã lâu không được cập nhật
- Cần rà soát từng index trong danh sách để xác định index nào thực sự có thể xóa; bộ đếm thống kê được quản lý theo cách reset sau khi kiểm tra
- Trong trường hợp này, vì công việc tương tự đã được thực hiện định kỳ nên không có index không sử dụng nào để xóa
-
Bloat của index và bảng
- Khi PostgreSQL cập nhật một hàng, nó đánh dấu tuple cũ là dead và thêm tuple mới vào vị trí khác, nên có thể phát sinh bloat
- Bloat ảnh hưởng không chỉ đến bảng mà cả index, khiến hệ thống dùng nhiều dung lượng lưu trữ hơn mức thực sự cần thiết
- Việc ước tính bloat không đơn giản, nên sử dụng các truy vấn từ PostgreSQL wiki và
pgsql-bloat-estimation để ước tính bloat của bảng và B-Tree index
Cách giảm bloat của index
-
Tạo lại/tái cấu trúc index
- Để loại bỏ bloat của index, cần tạo lại index
- Nếu drop rồi create trực tiếp, có thể tạo index mới ở dạng đã tối ưu
REINDEX của PostgreSQL tái cấu trúc index hiện có tại chỗ- Cách tạo lại thông thường sẽ lock bảng và chặn thay đổi, nên trong môi trường production,
REINDEX INDEX CONCURRENTLY index_name phù hợp hơn
REINDEX CONCURRENTLY tạo một index mới có hậu tố _ccnew, đồng bộ các thay đổi, rồi khi hoàn tất sẽ thay thế index cũ và xóa index cũ- Nếu quá trình tái cấu trúc bị gián đoạn giữa chừng, index mới có thể còn lại ở trạng thái invalid và chiếm dung lượng, nên cần tìm và xóa các index có
_ccnew nhưng không có indisvalid
-
B-Tree deduplication trong PostgreSQL 13
- PostgreSQL 13 giới thiệu B-Tree Deduplication, giúp lưu trữ các giá trị trùng lặp trong B-Tree index hiệu quả hơn
- Cho đến PostgreSQL 12, dù có nhiều giá trị trùng lặp, từng giá trị trùng vẫn được lưu lặp lại trong leaf của index, có thể tốn nhiều dung lượng
- Khi deduplication được bật trong PostgreSQL 13, giá trị trùng lặp chỉ được lưu một lần, nên có thể ảnh hưởng lớn đến kích thước các index có nhiều giá trị trùng
- Trong PostgreSQL 13,
deduplicate_items = ON là mặc định; nếu nâng cấp từ phiên bản cũ, cần tái cấu trúc index bằng REINDEX để nhận được đầy đủ hiệu quả
- Trong ví dụ 1 triệu hàng, index trên cột không unique có nhiều giá trị trùng giảm từ 21MB xuống 6840kB khi áp dụng deduplication, còn index trên cột unique không đổi ở mức 21MB
- Trong trường hợp này, PostgreSQL 13 khi đó vẫn còn mới và nhà cung cấp cloud chưa hỗ trợ, nên không thể giảm dung lượng bằng deduplication
Bloat của bảng và pg_repack
-
Hạn chế khi loại bỏ bloat của bảng
- Bảng cũng có thể bị bloat và phân mảnh do dead tuple
- Khác với index, bảng khó có thể đơn giản tạo lại; cần tạo bảng mới, chuyển dữ liệu, đồng bộ, rồi tạo lại index, constraint và referential constraint
VACUUM FULL table_name có thể thu hồi dung lượng do bloat và dead tuple chiếm dụng, nhưng cần lock bảng- Việc tạo lại bảng và
VACUUM FULL có thể dẫn đến khối lượng phát triển lớn hoặc downtime
-
Sử dụng pg_repack
- pg_repack là một extension PostgreSQL dùng để tái cấu trúc bảng và index mà không cần downtime
- Để sử dụng, tạo extension bằng
CREATE EXTENSION pg_repack;, rồi chạy từ console theo dạng pg_repack -k --table table_name db_name
pg_repack tạo bảng mới, nạp dữ liệu gốc trong khi đồng bộ với dữ liệu mới, sau đó tái cấu trúc index và cuối cùng hoán đổi hai bảng- Khi áp dụng vào production, có một số ràng buộc
- Cần khoảng dung lượng lưu trữ bổ sung tương đương kích thước bảng và index mục tiêu tái cấu trúc
- Nếu repack thất bại hoặc bị dừng thủ công, các object trung gian có thể còn lại và cần dọn dẹp thủ công
- Nếu đã đang thiếu dung lượng lưu trữ, yêu cầu dung lượng bổ sung có thể khiến cách này không phù hợp, nên cần theo dõi dung lượng trống và lên kế hoạch trước
Phát hiện ngoài dự kiến: PostgreSQL cũng lập chỉ mục cho NULL
- Sau khi loại bỏ index không sử dụng và dọn bloat bảng/index bằng các kỹ thuật thông thường, vẫn còn dung lượng có thể giảm
- Một trong những bảng lớn nhất lưu dữ liệu giao dịch, đồng thời ghi nhận các trường hợp người dùng hủy sau khi thanh toán và được hoàn tiền
- Bảng giao dịch có foreign key cho cả người dùng mua hàng và người dùng hủy, và mỗi trường đều được định nghĩa B-Tree index
- Người dùng mua hàng có ràng buộc
NOT NULL, nên mọi hàng đều có giá trị
- Người dùng hủy là nullable, và vì việc hủy hiếm xảy ra nên phần lớn giá trị là NULL
- Dự kiến index người dùng hủy sẽ nhỏ hơn nhiều so với index người dùng mua hàng, nhưng kích thước thực tế lại bằng nhau
- Tác giả từng học rằng trong Oracle, NULLs are not indexed, nhưng trong PostgreSQL, giá trị NULL cũng được lập chỉ mục
- Index toàn phần hiện có như sau
CREATE INDEX transaction_cancelled_by_ix
ON transactions(cancelled_by_user_id);
- Giả thuyết được kiểm chứng bằng cách đổi sang partial index loại trừ giá trị NULL
DROP INDEX transaction_cancelled_by_ix;
CREATE INDEX transaction_cancelled_by_part_ix
ON transactions(cancelled_by_user_id)
WHERE cancelled_by_user_id IS NOT NULL;
- Sau khi reindex, kích thước index toàn phần là 769MB, và hơn 99% giá trị là NULL
- Partial index loại trừ NULL có kích thước dưới 5MB, tức giảm hơn 99% kích thước index
- Sau khi reset thống kê bảng và quan sát trong một thời gian, index mới được sử dụng như index cũ, và hơn 760MB indexed tuple không dùng đã được loại bỏ mà không làm giảm hiệu năng
Tìm ứng viên partial index
- Sau khi đạt kết quả tốt với một partial index, tác giả tìm các index tương tự bằng cách tìm index trên những cột có
pg_stats.null_frac cao
null_frac là tỷ lệ NULL trong các giá trị của cột đó theo ước tính của PostgreSQL- Truy vấn tìm ứng viên áp dụng các điều kiện sau
- Loại trừ primary key vì không thể là partial
- Loại trừ các index đã là partial index
- Loại trừ composite index
- Chỉ bao gồm các index có kích thước trên 10MB
- Trong ví dụ kết quả,
tx_cancelled_by_ix có kích thước 1418MB và null_frac là 96,15%, nên xuất hiện như một ứng viên có khả năng tiết kiệm khoảng 1363MB
- Việc loại trừ toàn bộ NULL khỏi index không phải lúc nào cũng có lợi
- Các truy vấn tìm NULL bằng điều kiện
IS NULL có thể hưởng lợi từ index trên giá trị NULL
- Không chỉ giá trị không NULL, cả những giá trị khác hầu như không được truy vấn cũng có thể bị loại trừ bằng partial index
- Con số 20GB trong tiêu đề khả thi vì cấu trúc hệ thống là nếu giảm khoảng 10GB ở database primary thì replica cũng giảm dung lượng lưu trữ tương tự
Migration sang partial index trong Django ORM
-
Nullable ForeignKey và index ngầm định
- Nếu không chỉ định
db_index=False, Django sẽ ngầm tạo B-Tree index cho trường models.ForeignKey
- Nếu không cấu hình gì cho nullable
ForeignKey lưu người dùng hủy, Django sẽ tạo index toàn phần
- Để tạo partial index, cần đặt
db_index=False cho trường FK và thêm models.Index có điều kiện vào Meta.indexes
class Transaction(models.Model):
cancelled_by_user = models.ForeignKey(
to=User,
null=True,
on_delete=models.CASCADE,
db_index=False,
)
class Meta:
indexes = (
models.Index(
fields=('cancelled_by_user_id', ),
name='%(class_name)s_cancelled_by_part_ix',
condition=Q(cancelled_by_user_id__isnull=False),
),
)
- Nullable foreign key là một ứng viên partial index tốt
- Để tránh việc tính năng ngầm định âm thầm tạo index, tác giả tạo Django check nhằm buộc luôn phải khai báo
db_index trên foreign key
-
Đổi index toàn phần sang partial index mà không downtime
- Khi thay index toàn phần bằng partial index, cần thực hiện theo từng bước để tránh downtime hoặc suy giảm hiệu năng
- Sửa model Django liên quan để đổi index toàn phần thành partial index, nhưng không chạy nguyên xi migration do Django tạo
- Migration đó có thể vô hiệu hóa ràng buộc FK, drop index toàn phần hiện có rồi tạo partial index mới, gây downtime và suy giảm hiệu năng
- Dùng
./manage.py sqlmigrate để tạo SQL, chỉ trích xuất phần CREATE INDEX, sửa thành CONCURRENTLY, rồi chạy thủ công trên database
- Vì index toàn phần cũ vẫn còn, các truy vấn vẫn có thể tiếp tục dùng index cũ trong lúc tạo partial index
- Để kiểm tra index partial mới có được dùng hay không, reset bộ đếm thống kê của index toàn phần bằng
pg_stat_reset_single_table_counters(<full index oid>)
- Sau đó quan sát
idx_scan, idx_tup_read, idx_tup_fetch trong pg_stat_all_indexes để so sánh mức sử dụng giữa index toàn phần và partial index
- Khi xác định partial index được sử dụng, drop index toàn phần và kiểm tra dung lượng lưu trữ thực sự thu hồi được
- Sau khi trạng thái database khớp với trạng thái model, đăng ký Django migration là đã chạy bằng
./manage.py migrate --fake
- Trong các môi trường ít cần cân nhắc downtime như dev, QA, staging, Django migration sẽ chạy bình thường và thay index toàn phần bằng partial index
Tổng kết
- Chỉ tối ưu disk, tham số lưu trữ và cấu hình là chưa đủ để cải thiện hiệu năng và dung lượng vượt một giới hạn nhất định
- Phần cải thiện cuối cùng nằm ở chính các object của database; trong trường hợp này là thay đổi định nghĩa index
- Quy trình đã áp dụng có thể rút gọn thành ba bước
- Loại bỏ index không sử dụng
- Repack bảng và index, và nếu có thể thì bật B-Tree deduplication
- Dùng partial index để chỉ lập chỉ mục những giá trị cần thiết
- Việc dọn dẹp như vậy có thể là một lựa chọn thực tế để có thêm vài ngày trước khi phải provision thêm storage
1 bình luận
Ý kiến trên Hacker News
Dù có nhiều dung lượng lưu trữ thì làm cho chỉ mục nhỏ lại vẫn là điều tốt. Như vậy nhiều dữ liệu hơn có thể nằm trong hot set.
Tuy nhiên, từ góc nhìn vận hành cơ sở dữ liệu ở quy mô TB, “chỉ cần gắn thêm dung lượng lưu trữ” cũng luôn là một lựa chọn hợp lý. Đặc biệt nếu không ở trên cloud; với phần cứng tự sở hữu, SSD NVMe doanh nghiệp khoảng $80/TB, còn RAM DDR4 khoảng $1.20/GB. Nếu ước tính 4 giờ kỹ thuật vào khoảng $1000, bạn có thể mua 800GB RAM hoặc 12TB lưu trữ
Chỉ riêng backup hằng tuần trong 1 năm cũng biến 1TB thành 50TB, kể cả dùng lưu trữ offline cũng vậy. Ở công ty chúng tôi, việc gắn thêm dung lượng cho DB live khá dễ nên đã làm như vậy suốt vài năm, nhưng việc di chuyển các bản backup nhiều TB ngày càng phiền phức, đến mức dù phải tốn chi phí kỹ thuật thì cũng cần giảm dữ liệu production
Nếu chỉ làm một lần, hạ tầng hiện có có thể hấp thụ các chi phí như chỗ trống trong rack hoặc khe PCIe dư, nhưng kiểu khấu hao này chỉ hoạt động tốt ở quy mô nhỏ. Ngoài ra, khi hệ thống càng lớn, do chi phí quản lý và độ trễ tăng lên, hiệu năng trên mỗi đơn vị thiết bị tất yếu sẽ giảm. Nếu cứ giải quyết bằng cách tiếp tục phình to hệ thống, toàn bộ hệ thống sẽ ngày càng ì ạch rồi có thể trở nên không thể vận hành. Ngược lại, giải pháp giảm tài nguyên hệ thống cần thiết sẽ nâng hiệu năng trên mỗi đơn vị tài nguyên và tự nó tạo ra một tài sản có chất lượng tốt hơn
Một tối ưu hóa giúp tiết kiệm 1TB hôm nay có thể giúp tiết kiệm 2TB sau vài năm, và chỉ vài quyết định như vậy cũng có thể tạo ra chênh lệch hơn một bậc độ lớn. Ở quy mô lớn hơn thì điều này càng đúng, và cuối cùng luôn phải cân bằng
Lựa chọn “ném phần cứng vào” đôi khi có thể khôn ngoan, nhưng trong đa số trường hợp, tôi cho rằng khó có thể biến máy chủ 128GB RAM hôm nay thành 1TB RAM vào ngày mai chỉ với $1K
Tuy nhiên, vì đây không phải chi phí một lần mà là chi phí hằng tháng nên phép tính phức tạp hơn một chút. Tất nhiên, nếu cơ sở dữ liệu tăng trưởng với tốc độ đều đặn thì việc provision một lần on-premises thực ra cũng gần giống chi phí hằng tháng
Bài viết có nhắc đến khử trùng lặp B-tree có trong PostgreSQL 13 nhưng không có trong bản 12 đang dùng, đồng thời nói rằng phần lớn giá trị ở một số chỉ mục khóa ngoại là NULL.
Tôi tò mò liệu khử trùng lặp B-tree có giúp được trong trường hợp này không. Nhìn mục 63.4.2 trong tài liệu PostgreSQL 13 thì có vẻ là có: “B-Tree deduplication is just as effective with ‘duplicates’ that contain a NULL value...” Tất nhiên có lẽ sẽ không hiệu quả bằng chỉ mục một phần mà bài viết đã áp dụng, nhưng vẫn thú vị.
https://www.postgresql.org/docs/13/btree-implementation.html
https://news.ycombinator.com/item?id=25989467
Bài này hữu ích không chỉ vì phần “phát hiện” chỉ mục một phần là điểm chính, mà còn vì nó tổng hợp khá toàn diện các kỹ thuật nên biết khi lo ngại PostgreSQL đang dùng không gian kém hiệu quả.
Tuy vậy, nếu là startup hoặc công ty giai đoạn đầu, điều đáng nhớ là với các vấn đề lưu trữ kiểu này, gần như lúc nào chọn gắn thêm dung lượng đĩa cũng tốt hơn so với bận tâm tối ưu kích thước. Lập trình viên thì đắt, còn đĩa thì rẻ
Trước đây khi làm ở một startup tăng trưởng nhanh, một trong các nhà sáng lập cứ khăng khăng chỉ tiếp tục nâng cấp một máy chủ duy nhất; hệ thống vẫn có dự phòng và backup, nhưng kiến trúc đơn giản nên dễ quản lý, gỡ lỗi và khôi phục. Trong 5 năm đầu của công ty, toàn bộ hệ thống chạy trên một máy chủ mà vẫn tăng trưởng theo cấp số nhân và phục vụ hàng triệu người dùng trên toàn thế giới. Sau khi chứng kiến điều đó, tôi tin chắc rằng chỉ nên nâng cấp khi cần, theo cách đơn giản và thẳng nhất
Dùng chỉ mục một phần phù hợp rõ ràng với use case, chẳng hạn khi đa số giá trị là NULL, gần với mô hình hóa đúng hơn và không nên xem đó là tối ưu hóa vội vàng hay lãng phí thời gian của lập trình viên
Trong bối cảnh tương tự, các script này khá hữu ích.
https://github.com/NikolayS/postgres_dba
Tôi đã có thể thu hồi khoảng 10% dung lượng lưu trữ của một bảng lớn, tức khoảng 100GB, bằng cách sắp xếp lại thứ tự cột
Tuần trước, chỉ với
reindexvàpg_repack, tôi đã thu hồi 200GB từ một DB 600GB. Bảng tệ nhất có dữ liệu 17GB nhưng chỉ mục 142GB, và sau khi reindex thì giảm còn 21GB.Bảng này bị lập chỉ mục quá mức, với nhiều chỉ mục trên nhiều tổ hợp cột. Một trong những nguyên nhân của chỉ mục khổng lồ có vẻ là phân bố dữ liệu. Từ năm 2015 đã liên tục có chèn, cập nhật, xóa; dữ liệu càng cũ càng có khả năng bị xóa cao hơn nên dữ liệu các năm gần đây nhiều hơn, nhưng vẫn còn khoảng 0.1% là dữ liệu năm 2015. Tôi nghĩ phân bố lệch với đuôi dài như vậy có thể đã khiến vacuum khó xử lý tình trạng phình to chỉ mục hơn
Thời điểm đó cũng đã được thảo luận: An unexpected find that freed 20GB of unused index space in PostgreSQL - https://news.ycombinator.com/item?id=25988871 - Tháng 2/2021, 78 bình luận
Rất khuyến nghị pganalyze.com để tìm các index không dùng, cơ hội tối ưu hóa và các truy vấn có độ trễ cao
Hóa ra câu “tiết kiệm 20GB chỉ bằng một mẹo lạ” thật sự không hề phóng đại. Trong các môi trường Oracle / SQL Server cũ, tôi thường nhận được nhiều yêu cầu muốn dùng một dạng index đặc thù của PostgreSQL mà gần như chưa từng nghe tới, nhưng tôi không biết rằng cả giá trị NULL cũng được index
Trong dự án quy mô lớn đầu tiên tôi tham gia, khi dataset lớn dần lên, đội ngũ không hiểu vì sao các tác vụ lại chậm đi
Với index, thời gian chèn trên mỗi bản ghi là log(n). Khi DB kiểm thử từ 1.000 bản ghi tăng lên gần 65k, thời gian chèn tăng 60% (2^10 so với 2^16). Thành công khiến mọi thứ chậm lại, và việc nâng cấp server cũng có giới hạn. Thêm vài index nữa vì một tính năng mơ hồ mà business muốn, thế là giờ mọi thứ tăng gấp đôi
Tôi cũng đang quản lý nhiều DB có hàng trăm triệu bản ghi và hơn 40 index trên mỗi bảng/collection
Tìm kiếm nhị phân trong một trang B-tree mất dưới 100 chu kỳ. Việc duyệt B-tree trên 100 triệu bản ghi vẫn nên ở mức micro giây, và phần tìm kiếm nhị phân bên trên cũng sẽ ở mức micro giây, nếu không phải vài trăm nano giây
Nguyên lý này có áp dụng khi một hoặc vài giá trị rất phổ biến không? Ví dụ 90% của một cột số nguyên là 0
where val = 0sẽ giữ lại 90% số hàng, nó có thể chọn quét bảng thay vì dùng index