Lời chúc mừng.
Tên tôi là Vanya và tôi là nhà phát triển Java. Tình cờ là tôi làm việc rất nhiều với PostgreSQL - thiết lập cơ sở dữ liệu, tối ưu hóa cấu trúc, hiệu suất và chơi một chút DBA vào cuối tuần.
Gần đây tôi đã dọn dẹp một số cơ sở dữ liệu trong microservice của chúng tôi và viết một thư viện java , điều này làm cho công việc này trở nên dễ dàng hơn, giúp tôi tiết kiệm thời gian và giúp tôi tránh được một số lỗi phổ biến mà các nhà phát triển mắc phải. Đó là thư viện này mà chúng ta sẽ nói về ngày hôm nay.
Từ chối trách nhiệm
Phiên bản chính của PostgreSQL tôi làm việc là 10. Tất cả các truy vấn SQL tôi sử dụng cũng đã được thử nghiệm trên phiên bản 11. Phiên bản được hỗ trợ tối thiểu là 9.6.
thời tiền sử
Mọi chuyện bắt đầu cách đây gần một năm với một tình huống kỳ lạ đối với tôi: việc tạo ra một chỉ số mang tính cạnh tranh một cách bất ngờ đã kết thúc với một lỗi. Bản thân chỉ mục, như thường lệ, vẫn ở trong cơ sở dữ liệu ở trạng thái không hợp lệ. Phân tích nhật ký cho thấy sự thiếu hụt . Và chúng ta bắt đầu... Tìm hiểu sâu hơn, tôi phát hiện ra một loạt vấn đề trong cấu hình cơ sở dữ liệu và xắn tay áo lên, bắt đầu khắc phục chúng bằng ánh mắt lấp lánh.
Vấn đề thứ nhất - cấu hình mặc định
Có lẽ mọi người đã khá mệt mỏi với phép ẩn dụ về Postgres, có thể chạy trên máy pha cà phê, nhưng... cấu hình mặc định thực sự đặt ra một số câu hỏi. Ở mức tối thiểu, nó đáng được chú ý đến bảo trì_work_mem, temp_file_limit, câu lệnh_timeout и lock_timeout.
Trong trường hợp của chúng ta bảo trì_work_mem là 64 MB mặc định và temp_file_limit khoảng 2 GB - đơn giản là chúng tôi không có đủ bộ nhớ để tạo chỉ mục trên một bảng lớn.
Do đó, trong pg-chỉ số-sức khỏe Tôi đã sưu tầm một loạt , theo tôi, các tham số cần được cấu hình cho từng cơ sở dữ liệu.
Vấn đề thứ hai - chỉ mục trùng lặp
Cơ sở dữ liệu của chúng tôi tồn tại trên ổ SSD và chúng tôi sử dụng HA-cấu hình với nhiều trung tâm dữ liệu, máy chủ chính và n-số lượng bản sao Dung lượng ổ đĩa là tài nguyên rất quý giá đối với chúng tôi; nó không kém phần quan trọng so với hiệu suất và mức tiêu thụ CPU. Do đó, một mặt, chúng ta cần các chỉ mục để đọc nhanh, mặt khác, chúng ta không muốn thấy các chỉ mục không cần thiết trong cơ sở dữ liệu, vì chúng ngốn dung lượng và làm chậm quá trình cập nhật dữ liệu.
Và bây giờ, đã khôi phục mọi thứ và đã thấy đủ , Tôi quyết định tổ chức một cuộc thanh trừng “khủng”. Hóa ra các nhà phát triển không thích đọc tài liệu về cơ sở dữ liệu. Họ không thích nó lắm. Do đó, có hai lỗi điển hình phát sinh - chỉ mục được tạo thủ công trên khóa chính và chỉ mục “thủ công” tương tự trên một cột duy nhất. Thực tế là chúng không cần thiết - Postgres sẽ tự làm mọi thứ. Các chỉ mục như vậy có thể được xóa một cách an toàn và chẩn đoán đã xuất hiện cho mục đích này .
Vấn đề ba - chỉ số giao nhau
Hầu hết các nhà phát triển mới làm quen đều tạo chỉ mục trên một cột. Dần dần, sau khi trải nghiệm kỹ lưỡng hoạt động kinh doanh này, mọi người bắt đầu tối ưu hóa các truy vấn của mình và thêm các chỉ mục phức tạp hơn bao gồm một số cột. Đây là cách các chỉ mục trên cột xuất hiện A, A + B, A + B + C và như thế. Hai chỉ số đầu tiên trong số này có thể được loại bỏ một cách an toàn vì chúng là tiền tố của chỉ số thứ ba. Điều này cũng tiết kiệm rất nhiều dung lượng ổ đĩa và có các chẩn đoán cho việc này .
Vấn đề thứ tư - khóa ngoại không có chỉ mục
Postgres cho phép bạn tạo các ràng buộc khóa ngoại mà không cần chỉ định chỉ mục hỗ trợ. Trong nhiều trường hợp, đây không phải là vấn đề và thậm chí có thể không biểu hiện rõ ràng... Hiện tại...
Với chúng tôi cũng vậy: chỉ là tại một thời điểm nào đó, một công việc, chạy theo lịch trình và xóa cơ sở dữ liệu về các đơn đặt hàng thử nghiệm, bắt đầu được máy chủ chính “thêm” vào cho chúng tôi. CPU và IO bị lãng phí, yêu cầu chậm lại và hết thời gian chờ, dịch vụ là 500. Phân tích nhanh cho thấy các truy vấn như:
delete from <table> where id in (…)Tất nhiên, trong trường hợp này, có một chỉ mục theo id trong bảng mục tiêu và rất ít bản ghi bị xóa theo điều kiện. Có vẻ như mọi thứ đều ổn, nhưng than ôi, không phải vậy.
Người tuyệt vời đã đến giải cứu giải thích phân tích và nói rằng ngoài việc xóa các bản ghi trong bảng mục tiêu, còn có kiểm tra tính toàn vẹn tham chiếu và trên một trong các bảng liên quan, kiểm tra này không thành công quét tuần tự do thiếu chỉ số phù hợp. Thế là chẩn đoán ra đời .
Vấn đề thứ năm – giá trị null trong chỉ mục
Theo mặc định, Postgres bao gồm các giá trị null trong chỉ mục btree, nhưng chúng thường không cần thiết ở đó. Vì vậy, tôi siêng năng cố gắng loại bỏ những giá trị rỗng này (chẩn đoán ), tạo chỉ mục một phần trên các cột có thể rỗng theo loại where <A> is not null. Bằng cách này, tôi có thể giảm kích thước của một trong các chỉ mục của chúng tôi từ 1877 MB xuống 16 KB. Và ở một trong các dịch vụ, kích thước cơ sở dữ liệu đã giảm tổng cộng 16% (4.3 GB về số lượng tuyệt đối) do loại trừ các giá trị null khỏi chỉ mục. Tiết kiệm rất nhiều dung lượng ổ đĩa với những sửa đổi rất đơn giản. 🙂
Vấn đề thứ sáu – thiếu khóa chính
Do tính chất của cơ chế một tình huống như thế này có thể xảy ra khi kích thước bảng của bạn tăng nhanh do số lượng lớn bản ghi đã chết. Tôi đã ngây thơ tin rằng điều này sẽ không đe dọa chúng tôi và điều này sẽ không xảy ra với cơ sở của chúng tôi, bởi vì chúng tôi, ồ!!!, là những nhà phát triển bình thường... Tôi thật ngu ngốc và ngây thơ làm sao...
Một ngày nọ, một cuộc di chuyển tuyệt vời đã lấy và cập nhật tất cả các bản ghi trong một bảng lớn và được sử dụng tích cực. Chúng tôi bất ngờ nhận được +100 GB cho kích thước bảng. Thật là xấu hổ, nhưng những sai lầm của chúng tôi không dừng lại ở đó. Sau khi chế độ tự động hút chân không trên bàn này kết thúc 15 giờ sau đó, rõ ràng là vị trí thực tế sẽ không quay trở lại. Chúng tôi không thể ngừng dịch vụ và CHÂN KHÔNG ĐẦY ĐỦ, vì vậy chúng tôi quyết định sử dụng . Và rồi hóa ra là pg_repack không biết cách xử lý các bảng không có khóa chính hoặc ràng buộc về tính duy nhất khác và bảng của chúng tôi không có khóa chính. Thế là chẩn đoán ra đời .
Trong phiên bản thư viện 0.1.5 Khả năng thu thập dữ liệu từ hàng loạt bảng và chỉ mục cũng như phản hồi kịp thời đã được thêm vào.
Vấn đề bảy và tám - chỉ mục không đủ và chỉ mục không được sử dụng
Hai chẩn đoán sau đây là: и – xuất hiện ở dạng cuối cùng tương đối gần đây. Vấn đề là chúng không thể được lấy và thêm vào.
Như tôi đã viết, chúng tôi sử dụng cấu hình với một số bản sao và tải đọc trên các máy chủ khác nhau về cơ bản là khác nhau. Kết quả là, tình huống xảy ra là một số bảng và chỉ mục trên một số máy chủ thực tế không được sử dụng và để phân tích, bạn cần thu thập số liệu thống kê từ tất cả các máy chủ trong cụm. Điều này cũng cần thiết trên mọi máy chủ trong cụm; bạn không thể chỉ thực hiện việc này trên máy chủ chính.
Cách tiếp cận này cho phép chúng tôi tiết kiệm vài chục gigabyte bằng cách xóa các chỉ mục không bao giờ được sử dụng, cũng như thêm các chỉ mục bị thiếu vào các bảng hiếm khi được sử dụng.
Như một kết luận
Tất nhiên, đối với hầu hết tất cả các chẩn đoán, bạn có thể định cấu hình . Bằng cách này, bạn có thể nhanh chóng thực hiện kiểm tra trong ứng dụng của mình, ngăn lỗi mới xuất hiện và sau đó sửa dần các lỗi cũ.
Một số chẩn đoán có thể được thực hiện trong các thử nghiệm chức năng ngay sau khi triển khai di chuyển cơ sở dữ liệu. Và đây có lẽ là một trong những tính năng mạnh mẽ nhất trong thư viện của tôi. Một ví dụ về việc sử dụng có thể được tìm thấy trong .
Sẽ rất hợp lý nếu chỉ thực hiện kiểm tra các chỉ mục không được sử dụng hoặc bị thiếu, cũng như sự phình to, chỉ trên cơ sở dữ liệu thực. Các giá trị thu thập được có thể được ghi lại trong hoặc gửi đến hệ thống giám sát.
Tôi thực sự hy vọng rằng pg-chỉ số-sức khỏe sẽ hữu ích và có nhu cầu. Bạn cũng có thể đóng góp vào sự phát triển của thư viện bằng cách báo cáo các vấn đề bạn tìm thấy và đề xuất các chẩn đoán mới.
Nguồn: www.habr.com