Bản sao của báo cáo năm 2015 của Alexey Lesovsky "Đi sâu vào số liệu thống kê nội bộ của PostgreSQL"
Tuyên bố miễn trừ trách nhiệm từ tác giả của báo cáo: Tôi xin lưu ý rằng báo cáo này được lập vào tháng 2015 năm 4 - hơn 9.4 năm đã trôi qua và rất nhiều thời gian đã trôi qua. Phiên bản 4 được thảo luận trong báo cáo không còn được hỗ trợ. Trong 5 năm qua, XNUMX bản phát hành mới đã được phát hành, trong đó có rất nhiều đổi mới, cải tiến và thay đổi về số liệu thống kê, đồng thời một số tài liệu đã lỗi thời và không phù hợp. Khi tôi xem xét, tôi đã cố gắng đánh dấu những nơi này để không đánh lừa bạn đọc. Tôi đã không viết lại những chỗ này, có rất nhiều chỗ, và kết quả là một báo cáo hoàn toàn khác sẽ xuất hiện.
PostgreSQL DBMS là một cơ chế khổng lồ và cơ chế này bao gồm nhiều hệ thống con, công việc phối hợp của chúng ảnh hưởng trực tiếp đến hiệu suất của DBMS. Trong quá trình hoạt động, số liệu thống kê và thông tin về hoạt động của các thành phần được thu thập, cho phép bạn đánh giá hiệu quả của PostgreSQL và thực hiện các biện pháp để cải thiện hiệu suất. Tuy nhiên, có rất nhiều thông tin này và nó được trình bày ở dạng khá đơn giản. Xử lý thông tin này và giải thích nó đôi khi là một nhiệm vụ hoàn toàn không hề nhỏ và "vườn thú" của các công cụ và tiện ích có thể dễ dàng gây nhầm lẫn ngay cả với một DBA nâng cao.
Chào buổi chiều Tên tôi là Aleksey. Như Ilya đã nói, tôi sẽ nói về thống kê PostgreSQL.
Thống kê hoạt động PostgreSQL. PostgreSQL có hai số liệu thống kê. Thống kê hoạt động, sẽ được thảo luận. Và lịch trình thống kê về phân phối dữ liệu. Tôi sẽ nói cụ thể về số liệu thống kê hoạt động PostgreSQL, cho phép chúng tôi đánh giá hiệu suất và bằng cách nào đó cải thiện nó.
Tôi sẽ cho bạn biết cách sử dụng hiệu quả số liệu thống kê để giải quyết nhiều vấn đề khác nhau mà bạn gặp phải hoặc có thể gặp phải.
Điều gì sẽ không có trong báo cáo? Trong báo cáo, tôi sẽ không chạm vào số liệu thống kê của bộ lập lịch, bởi vì. đây là một chủ đề riêng cho một báo cáo riêng về cách dữ liệu được lưu trữ trong cơ sở dữ liệu và cách người lập kế hoạch truy vấn có ý tưởng về các đặc điểm định tính và định lượng của dữ liệu này.
Và sẽ không có đánh giá công cụ, tôi sẽ không so sánh sản phẩm này với sản phẩm khác. Sẽ không có quảng cáo. Bỏ cái này đi.
Tôi muốn cho bạn thấy rằng sử dụng số liệu thống kê là hữu ích. Nó là cần thiết. Sử dụng nó một cách không sợ hãi. Tất cả những gì chúng ta cần là SQL đơn giản và kiến thức cơ bản về SQL.
Và chúng ta sẽ nói về việc chọn số liệu thống kê nào để giải quyết vấn đề.
Nếu chúng ta nhìn vào PostgreSQL và chạy một lệnh trên hệ điều hành để xem các quy trình, chúng ta sẽ thấy một "hộp đen". Chúng ta sẽ xem một số process thực hiện công việc gì đó, và qua tên gọi chúng ta có thể hình dung đại khái chúng đang làm gì ở đó, chúng đang làm gì. Nhưng, thực chất đây là hộp đen, chúng ta không thể nhìn vào bên trong.
Chúng ta có thể xem tải CPU trong top, chúng ta có thể thấy việc sử dụng bộ nhớ bởi một số tiện ích hệ thống, nhưng chúng ta sẽ không thể xem bên trong PostgreSQL. Đối với điều này, chúng ta cần các công cụ khác.
Và tiếp tục hơn nữa, tôi sẽ cho bạn biết thời gian được sử dụng ở đâu. Nếu chúng ta trình bày PostgreSQL dưới dạng sơ đồ như vậy, thì sẽ có thể trả lời thời gian được sử dụng ở đâu. Đây là hai điều: đó là việc xử lý các yêu cầu của máy khách từ các ứng dụng và các tác vụ nền mà PostgreSQL thực hiện để duy trì hoạt động của nó.
Nếu chúng ta bắt đầu nhìn vào góc trên cùng bên trái, chúng ta có thể thấy các yêu cầu của khách hàng được xử lý như thế nào. Yêu cầu đến từ ứng dụng và phiên máy khách được mở để tiếp tục làm việc. Yêu cầu được chuyển đến bộ lập lịch. Người lập kế hoạch xây dựng một kế hoạch truy vấn. Gửi nó hơn nữa để thực hiện. Có một số loại dữ liệu I/O khối được liên kết với các bảng và chỉ mục. Dữ liệu cần thiết được đọc từ đĩa vào bộ nhớ trong một khu vực đặc biệt gọi là "bộ đệm dùng chung". Kết quả truy vấn, nếu là cập nhật, xóa, được ghi vào nhật ký giao dịch trong WAL. Một số thông tin thống kê đi vào nhật ký hoặc bộ sưu tập thống kê. Và kết quả của yêu cầu được trả lại cho khách hàng. Sau đó, khách hàng có thể lặp lại mọi thứ với một yêu cầu mới.
Chúng ta có gì với các tác vụ nền và quy trình nền? Chúng tôi có một số quy trình giúp cơ sở dữ liệu hoạt động và chạy bình thường. Các quy trình này cũng sẽ được đề cập trong báo cáo: đó là autovacuum, checkpointer, các quy trình liên quan đến sao chép, trình ghi nền. Tôi sẽ chạm vào từng người trong số họ khi tôi báo cáo.
Các vấn đề với số liệu thống kê là gì?
- Rất nhiều thông tin. PostgreSQL 9.4 cung cấp 109 số liệu để xem dữ liệu thống kê. Tuy nhiên, nếu cơ sở dữ liệu lưu trữ nhiều bảng, lược đồ, cơ sở dữ liệu, thì tất cả các số liệu này sẽ phải được nhân với số lượng bảng, cơ sở dữ liệu tương ứng. Đó là, thậm chí còn có nhiều thông tin hơn. Và rất dễ bị chết đuối trong đó.
- Vấn đề tiếp theo là số liệu thống kê được đại diện bởi bộ đếm. Nếu chúng ta nhìn vào những thống kê này, chúng ta sẽ thấy các quầy không ngừng tăng lên. Và nếu nhiều thời gian đã trôi qua kể từ khi số liệu thống kê được đặt lại, chúng ta sẽ thấy hàng tỷ giá trị. Và họ không nói với chúng tôi bất cứ điều gì.
- Không có lịch sử. Nếu bạn gặp sự cố nào đó, thứ gì đó đã rơi cách đây 15-30 phút, bạn sẽ không thể sử dụng số liệu thống kê và xem điều gì đã xảy ra cách đây 15-30 phút. Đây là vấn đề.
- Việc thiếu một công cụ tích hợp trong PostgreSQL là một vấn đề. Các nhà phát triển hạt nhân không cung cấp bất kỳ tiện ích nào. Họ không có bất cứ điều gì như thế. Họ chỉ đưa ra số liệu thống kê trong cơ sở dữ liệu. Sử dụng nó, đưa ra yêu cầu với nó, bất cứ điều gì bạn muốn, sau đó làm điều đó.
- Vì không có công cụ nào được tích hợp trong PostgreSQL nên điều này gây ra một vấn đề khác. Rất nhiều công cụ của bên thứ ba. Mọi công ty có ít nhiều bàn tay trực tiếp đều cố gắng viết chương trình của riêng mình. Và kết quả là, cộng đồng có rất nhiều công cụ mà bạn có thể sử dụng để làm việc với số liệu thống kê. Và trong một số công cụ có một số tính năng, trong các công cụ khác không có tính năng nào khác hoặc có một số tính năng mới. Và một tình huống phát sinh là bạn cần sử dụng hai hoặc ba hoặc bốn công cụ chồng lên nhau và có các chức năng khác nhau. Điều này rất khó chịu.
Điều gì xảy ra sau đây? Điều quan trọng là có thể lấy số liệu thống kê trực tiếp để không phụ thuộc vào các chương trình hoặc bằng cách nào đó tự cải thiện các chương trình này: thêm một số chức năng để nhận được lợi ích của bạn.
Và bạn cần có kiến thức cơ bản về SQL. Để lấy một số dữ liệu từ số liệu thống kê, bạn cần thực hiện các truy vấn SQL, tức là bạn cần biết cách chọn, tham gia được thực hiện.
Số liệu thống kê cho chúng ta biết một số điều. Chúng có thể được chia thành các loại.
- Loại đầu tiên là các sự kiện diễn ra trong cơ sở dữ liệu. Đây là khi một số sự kiện xảy ra trong cơ sở dữ liệu: truy vấn, truy cập bảng, tự động hút chân không, cam kết, thì đây đều là các sự kiện. Bộ đếm tương ứng với các sự kiện này được tăng lên. Và chúng ta có thể theo dõi những sự kiện này.
- Loại thứ hai là thuộc tính của các đối tượng như bảng, cơ sở dữ liệu. Họ có tài sản. Đây là kích thước của các bảng. Chúng ta có thể theo dõi sự tăng trưởng của các bảng, sự tăng trưởng của các chỉ mục. Chúng ta có thể thấy những thay đổi trong động lực học.
- Và hạng mục thứ ba là thời gian dành cho sự kiện. Yêu cầu là một sự kiện. Nó có thước đo thời lượng cụ thể của riêng nó. Bắt đầu ở đây, kết thúc ở đây. Chúng tôi có thể theo dõi nó. Hoặc là thời gian đọc một khối từ đĩa hoặc ghi. Những điều này cũng được theo dõi.
Nguồn số liệu thống kê được trình bày như sau:
- Trong bộ nhớ dùng chung (bộ đệm dùng chung) có một phân đoạn để đặt dữ liệu tĩnh ở đó, cũng có những bộ đếm liên tục tăng lên khi một số sự kiện xảy ra hoặc một số thời điểm phát sinh trong hoạt động của cơ sở dữ liệu.
- Tất cả các bộ đếm này không có sẵn cho người dùng và thậm chí không có sẵn cho quản trị viên. Đây là những thứ cấp thấp. Để truy cập chúng, PostgreSQL cung cấp một giao diện dưới dạng các hàm SQL. Chúng tôi có thể thực hiện các lựa chọn được chọn bằng cách sử dụng các chức năng này và nhận một số loại số liệu (hoặc bộ số liệu).
- Tuy nhiên, không phải lúc nào việc sử dụng các hàm này cũng thuận tiện, vì vậy các hàm là cơ sở cho các dạng xem (VIEWs). Đây là những bảng ảo cung cấp số liệu thống kê trên một hệ thống con cụ thể hoặc trên một số tập sự kiện trong cơ sở dữ liệu.
- Các dạng xem tích hợp (VIEW) này là giao diện người dùng chính để làm việc với số liệu thống kê. Chúng có sẵn theo mặc định mà không cần bất kỳ cài đặt bổ sung nào, bạn có thể sử dụng chúng ngay lập tức, xem, lấy thông tin từ đó. Và cũng có những đóng góp. Đóng góp là chính thức. Bạn có thể cài đặt gói postgresql-contrib (ví dụ: postgresql94-contrib), tải mô-đun cần thiết trong cấu hình, chỉ định tham số cho nó, khởi động lại PostgreSQL và bạn có thể sử dụng nó. (Ghi chú. Tùy thuộc vào bản phân phối, trong các phiên bản đóng góp gần đây, gói này là một phần của gói chính).
- Và có những đóng góp không chính thống. Chúng không được cung cấp cùng với bản phân phối PostgreSQL tiêu chuẩn. Chúng phải được biên dịch hoặc cài đặt như một thư viện. Các tùy chọn có thể rất khác nhau, tùy thuộc vào ý tưởng của nhà phát triển đóng góp không chính thức này.
Trang trình bày này hiển thị tất cả các chế độ xem (VIEW) đó và một số chức năng có sẵn trong PostgreSQL 9.4. Như chúng ta có thể thấy, có rất nhiều trong số họ. Và khá dễ nhầm lẫn nếu bạn lần đầu trải nghiệm.
Tuy nhiên, nếu chúng ta chụp ảnh trước Как тратится время на PostgreSQL và tương thích với danh sách này, chúng tôi nhận được hình ảnh này. Mỗi chế độ xem (VIEWs) hoặc mỗi chức năng, chúng ta có thể sử dụng cho mục đích này hay mục đích khác để có được số liệu thống kê phù hợp khi chúng ta chạy PostgreSQL. Và chúng ta đã có thể nhận được một số thông tin về hoạt động của hệ thống con.
Điều đầu tiên chúng ta sẽ xem xét là pg_stat_database. Như chúng ta có thể thấy, đây là một đại diện. Nó chứa rất nhiều thông tin. Thông tin đa dạng nhất. Và nó cung cấp kiến thức rất hữu ích về những gì chúng ta đang diễn ra trong cơ sở dữ liệu.
Chúng ta có thể lấy gì từ đó? Hãy bắt đầu từ những điều đơn giản nhất.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Điều đầu tiên chúng ta có thể xem xét là tỷ lệ phần trăm truy cập vào bộ đệm. Tỷ lệ phần trăm truy cập bộ nhớ cache là một số liệu hữu ích. Nó cho phép bạn ước tính lượng dữ liệu được lấy từ bộ đệm bộ đệm dùng chung và lượng dữ liệu được đọc từ đĩa.
Rõ ràng là chúng ta càng có nhiều bộ đệm thì càng tốt. Chúng tôi đánh giá số liệu này dưới dạng phần trăm. Và, ví dụ: nếu chúng tôi có tỷ lệ phần trăm các lượt truy cập vào bộ nhớ cache này lớn hơn 90%, thì điều này là tốt. Nếu nó giảm xuống dưới 90%, thì chúng tôi không có đủ bộ nhớ để giữ dữ liệu nóng trong bộ nhớ. Và để sử dụng dữ liệu này, PostgreSQL buộc phải truy cập vào đĩa và điều này chậm hơn so với khi dữ liệu được đọc từ bộ nhớ. Và bạn cần nghĩ đến việc tăng bộ nhớ: tăng bộ đệm dùng chung hoặc tăng bộ nhớ sắt (RAM).
select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;Những gì khác có thể được thực hiện từ hiệu suất này? Bạn có thể thấy sự bất thường xảy ra trong cơ sở dữ liệu. Điều gì được thể hiện ở đây? Có các cam kết, khôi phục, tạo tệp tạm thời, kích thước, bế tắc và xung đột của chúng.
Chúng tôi có thể sử dụng yêu cầu này. SQL này khá đơn giản. Và chúng ta có thể thấy dữ liệu này cho chính mình.
Và đây là các giá trị ngưỡng. Chúng tôi xem xét tỷ lệ cam kết và khôi phục. Cam kết là một xác nhận thành công của giao dịch. Rollbacks là một sự lùi lại, tức là giao dịch đã thực hiện một số công việc, làm căng cơ sở dữ liệu, xem xét điều gì đó và sau đó xảy ra lỗi và kết quả của giao dịch bị loại bỏ. I E. số lượng rollback liên tục tăng là xấu. Và bằng cách nào đó bạn nên tránh chúng và chỉnh sửa mã để điều này không xảy ra.
Xung đột có liên quan đến sao chép. Và họ cũng nên tránh. Nếu bạn có một số truy vấn được thực thi trên bản sao và phát sinh xung đột, thì bạn cần phân tích những xung đột này và xem điều gì sẽ xảy ra. Chi tiết có thể được tìm thấy trong các bản ghi. Và giải quyết xung đột để yêu cầu ứng dụng hoạt động mà không gặp lỗi.
Bế tắc cũng là một tình huống xấu. Khi các yêu cầu tranh giành tài nguyên, một yêu cầu truy cập một tài nguyên và lấy khóa, yêu cầu thứ hai truy cập tài nguyên thứ hai và cũng lấy khóa, sau đó cả hai yêu cầu truy cập tài nguyên của nhau và bị chặn chờ người hàng xóm giải phóng khóa. Đây cũng là một tình huống có vấn đề. Chúng cần được giải quyết ở cấp độ viết lại ứng dụng và tuần tự hóa quyền truy cập vào tài nguyên. Và nếu bạn thấy rằng các bế tắc của mình không ngừng tăng lên, bạn cần xem chi tiết trong nhật ký, phân tích các tình huống phát sinh và xem vấn đề là gì.
Các tệp tạm thời (temp_files) cũng không tốt. Khi một yêu cầu của người dùng không có đủ bộ nhớ để chứa dữ liệu hoạt động, tạm thời, nó sẽ tạo một tệp trên đĩa. Và tất cả các thao tác mà anh ta có thể thực hiện trong bộ đệm tạm thời trong bộ nhớ, anh ta bắt đầu thực hiện trên đĩa. Nó chậm. Điều này làm tăng thời gian thực hiện truy vấn. Và ứng dụng khách đã gửi yêu cầu tới PostgreSQL sẽ nhận được phản hồi sau đó một chút. Nếu tất cả các thao tác này được thực hiện trong bộ nhớ, Postgres sẽ phản hồi nhanh hơn nhiều và khách hàng sẽ ít phải chờ đợi hơn.
pg_stat_bgwriter - Khung nhìn này mô tả hoạt động của hai hệ thống con nền PostgreSQL: checkpointer и background writer.
Để bắt đầu, hãy phân tích các điểm kiểm soát, cái gọi là. checkpoints. Điểm kiểm tra là gì? Điểm kiểm tra là một vị trí trong nhật ký giao dịch cho biết rằng tất cả các thay đổi dữ liệu đã cam kết trong nhật ký được đồng bộ hóa thành công với dữ liệu trên đĩa. Quá trình này, tùy thuộc vào khối lượng công việc và cài đặt, có thể kéo dài và chủ yếu bao gồm việc đồng bộ hóa các trang bẩn trong bộ đệm dùng chung với các tệp dữ liệu trên đĩa. Nó dùng để làm gì? Nếu PostgreSQL luôn truy cập vào đĩa và lấy dữ liệu từ đó và ghi dữ liệu trên mỗi lần truy cập, thì nó sẽ bị chậm. Do đó, PostgreSQL có một phân đoạn bộ nhớ, kích thước của phân đoạn này phụ thuộc vào các tham số trong cấu hình. Postgres phân bổ dữ liệu hoạt động trong bộ nhớ này để xử lý hoặc truy vấn thêm. Trong trường hợp yêu cầu thay đổi dữ liệu, chúng được thay đổi. Và chúng tôi nhận được hai phiên bản của dữ liệu. Một cái nằm trong bộ nhớ, cái kia nằm trên đĩa. Và định kỳ bạn cần đồng bộ hóa dữ liệu này. Chúng tôi cần những gì đã thay đổi trong bộ nhớ được đồng bộ hóa với đĩa. Điều này đòi hỏi một trạm kiểm soát.
Điểm kiểm tra đi qua bộ đệm được chia sẻ, đánh dấu các trang bẩn mà chúng cần cho điểm kiểm tra. Sau đó, nó bắt đầu lần thứ hai đi qua bộ đệm dùng chung. Còn những trang bị đánh dấu checkpoint thì anh ấy đã đồng bộ rồi. Do đó, dữ liệu đã được đồng bộ hóa với đĩa.
Có hai loại điểm kiểm soát. Một điểm kiểm tra được thực hiện khi hết thời gian chờ. Điểm kiểm tra này hữu ích và tốt - checkpoint_timed. Và có các trạm kiểm soát theo yêu cầu - checkpoint required. Điểm kiểm tra như vậy xảy ra khi chúng tôi có một bản ghi dữ liệu rất lớn. Chúng tôi đã ghi lại rất nhiều nhật ký giao dịch. Và PostgreSQL tin rằng nó cần đồng bộ hóa tất cả những điều này càng nhanh càng tốt, tạo một điểm kiểm tra và tiếp tục.
Và nếu bạn nhìn vào số liệu thống kê pg_stat_bgwriter và xem những gì bạn có checkpoint_req lớn hơn nhiều so với checkpoint_timed, thì điều này thật tệ. Tại sao xấu? Điều này có nghĩa là PostgreSQL luôn bị căng thẳng khi cần ghi dữ liệu vào đĩa. Điểm kiểm tra theo thời gian chờ ít căng thẳng hơn và được thực hiện theo lịch trình nội bộ và có thể kéo dài theo thời gian. PostgreSQL có khả năng tạm dừng trong công việc và không làm căng hệ thống con đĩa. Điều này hữu ích cho PostgreSQL. Và các yêu cầu được thực hiện trong thời gian kiểm tra sẽ không gặp phải căng thẳng do hệ thống con đĩa đang bận.
Và có ba thông số để điều chỉnh điểm kiểm tra:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Chúng cho phép bạn kiểm soát hoạt động của các điểm kiểm soát. Nhưng tôi sẽ không tập trung vào chúng. Ảnh hưởng của họ là một vấn đề riêng biệt.
Lưu ý: Phiên bản 9.4 được xem xét trong báo cáo không còn phù hợp. Trong các phiên bản hiện đại của PostgreSQL, tham số checkpoint_segments thay thế bởi các thông số min_wal_size и max_wal_size.
Hệ thống con tiếp theo là trình ghi nền - background writer. Anh ta đang làm gì vậy? Nó chạy liên tục trong một vòng lặp vô tận. Nó quét các trang vào bộ đệm dùng chung và xóa các trang bẩn mà nó tìm thấy vào đĩa. Bằng cách này, nó giúp con trỏ thực hiện ít công việc hơn trong quá trình điểm kiểm tra.
Anh ấy cần gì nữa? Nó cung cấp nhu cầu về các trang sạch trong bộ đệm dùng chung nếu chúng đột ngột được yêu cầu (với số lượng lớn và ngay lập tức) để chứa dữ liệu. Giả sử một tình huống phát sinh khi yêu cầu yêu cầu các trang sạch và chúng đã ở trong bộ đệm dùng chung. postgres backend anh ấy chỉ việc lấy và sử dụng, anh ấy không phải tự dọn dẹp bất cứ thứ gì. Nhưng nếu đột nhiên không có trang nào như vậy, chương trình phụ trợ sẽ tạm dừng và bắt đầu tìm kiếm các trang để xóa chúng vào đĩa và sử dụng chúng theo nhu cầu của chính nó - điều này ảnh hưởng tiêu cực đến thời gian thực hiện yêu cầu hiện tại. Nếu bạn thấy rằng bạn có một tham số maxwritten_clean lớn, điều này có nghĩa là trình viết nền không thực hiện công việc của nó và bạn cần tăng các tham số bgwriter_lru_maxpagesđể anh ta có thể làm nhiều việc hơn trong một chu kỳ, xóa nhiều trang hơn.
Và một chỉ số rất hữu ích khác là buffers_backend_fsync. Phần phụ trợ không thực hiện fsync vì nó chậm. Chúng chuyển fsync lên con trỏ ngăn xếp IO. Con trỏ kiểm tra có hàng đợi riêng, nó xử lý định kỳ fsync và đồng bộ hóa các trang trong bộ nhớ với các tệp trên đĩa. Nếu hàng đợi con trỏ lớn và đầy, thì phần phụ trợ buộc phải tự thực hiện fsync và điều này làm chậm phần phụ trợ, tức là khách hàng sẽ nhận được phản hồi muộn hơn mức có thể. Nếu bạn thấy rằng bạn có giá trị này lớn hơn XNUMX, thì đây đã là một vấn đề và bạn cần chú ý đến cài đặt của trình ghi nền và cũng đánh giá hiệu suất của hệ thống con đĩa.
Lưu ý: _Văn bản sau mô tả các quan điểm thống kê liên quan đến sao chép. Hầu hết các tên chức năng và chế độ xem đã được đổi tên trong Postgres 10. Bản chất của việc đổi tên là để thay thế xlog trên wal и location trên lsn trong tên chức năng/chế độ xem, v.v. Ví dụ cụ thể, chức năng pg_xlog_location_diff() được đổi tên thành pg_wal_lsn_diff()._
Chúng tôi cũng có rất nhiều ở đây. Nhưng chúng tôi chỉ cần các mục liên quan đến vị trí.
Nếu chúng ta thấy rằng tất cả các giá trị đều bằng nhau, thì điều này là lý tưởng và bản sao không bị tụt hậu so với bản chính.
Vị trí thập lục phân này ở đây là vị trí trong nhật ký giao dịch. Nó liên tục tăng nếu có một số hoạt động trong cơ sở dữ liệu: chèn, xóa, v.v.
сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());Nếu những điều này khác nhau, thì có một số loại độ trễ. Lag là độ trễ của bản sao so với bản gốc, tức là dữ liệu khác nhau giữa các máy chủ.
Có ba lý do cho sự chậm trễ:
- Đó là hệ thống con đĩa không thể xử lý ghi đồng bộ hóa tệp.
- Đây có thể là lỗi mạng hoặc quá tải mạng khi dữ liệu không có thời gian để đến bản sao và nó không thể sao chép nó.
- Và bộ xử lý. Bộ xử lý là một trường hợp rất hiếm. Và tôi đã thấy điều đó hai hoặc ba lần, nhưng điều đó cũng có thể xảy ra.
Và đây là ba truy vấn cho phép chúng tôi sử dụng số liệu thống kê. Chúng tôi có thể ước tính số tiền được ghi lại trong nhật ký giao dịch của mình. Có một chức năng như vậy pg_xlog_location_diff và chúng tôi có thể ước tính độ trễ sao chép theo byte và giây. Chúng tôi cũng sử dụng giá trị từ chế độ xem này (VIEWs) cho việc này.
Lưu ý: _Thay vì pg_xlog_locationdiff(), bạn có thể sử dụng toán tử trừ và trừ một vị trí khỏi một vị trí khác. Thoải mái.
Với độ trễ tính bằng giây, có một khoảnh khắc. Nếu không có hoạt động nào trên bản gốc, giao dịch đã ở đó khoảng 15 phút trước và không có hoạt động nào, đồng thời nếu xem xét độ trễ này trên bản sao, chúng ta sẽ thấy độ trễ là 15 phút. Điều này đáng ghi nhớ. Và nó có thể dẫn đến trạng thái sững sờ khi bạn xem đoạn lag này.
pg_stat_all_tables là một chế độ xem hữu ích khác. Nó hiển thị số liệu thống kê trên các bảng. Khi chúng tôi có các bảng trong cơ sở dữ liệu, có một số hoạt động với nó, một số hành động, chúng tôi có thể lấy thông tin này từ chế độ xem này.
select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;Điều đầu tiên chúng ta có thể xem xét là quét bảng tuần tự. Bản thân con số sau những đoạn văn này không nhất thiết là xấu và không có nghĩa là chúng ta cần phải làm điều gì đó.
Tuy nhiên, có một số liệu thứ hai - seq_tup_read. Đây là số hàng được trả về từ quá trình quét tuần tự. Nếu số trung bình vượt quá 1, 000, 10, 000, thì đây đã là dấu hiệu cho thấy bạn có thể cần tạo chỉ mục ở đâu đó để truy cập theo chỉ mục hoặc có thể tối ưu hóa các truy vấn sử dụng các lần quét tuần tự như vậy để điều này không xảy ra.
Một ví dụ đơn giản - giả sử một yêu cầu có OFFSET lớn và GIỚI HẠN đáng giá. Ví dụ: 100 hàng trong một bảng được quét và sau đó 000 hàng bắt buộc được lấy và các hàng được quét trước đó sẽ bị loại bỏ. Đây cũng là một trường hợp xấu. Và những yêu cầu như vậy cần phải được tối ưu hóa. Và đây là một truy vấn SQL đơn giản mà bạn có thể xem và đánh giá các số nhận được.
select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;Kích thước bảng cũng có thể được lấy bằng bảng này và sử dụng các chức năng bổ sung pg_total_relation_size(), pg_relation_size().
Nói chung, có siêu lệnh dt и di, mà bạn có thể sử dụng trong PSQL và cũng có thể xem kích thước bảng và chỉ mục.
Tuy nhiên, việc sử dụng các hàm giúp chúng tôi xem xét kích thước của các bảng, thậm chí có tính đến các chỉ mục hoặc không tính đến các chỉ mục và đã đưa ra một số ước tính dựa trên sự phát triển của cơ sở dữ liệu, tức là. cường độ như thế nào và đã rút ra một số kết luận về tối ưu hóa kích thước.
Viết hoạt động. Kỷ lục là gì? Hãy nhìn vào hoạt động UPDATE – hoạt động cập nhật các hàng trong bảng. Trên thực tế, cập nhật là hai hoạt động (hoặc thậm chí nhiều hơn). Thao tác này đang chèn phiên bản hàng mới và đánh dấu phiên bản hàng cũ là lỗi thời. Sau đó, autovacuum sẽ đến và dọn sạch các phiên bản lỗi thời này của các dòng, hãy đánh dấu vị trí này là có sẵn để sử dụng lại.
Ngoài ra, cập nhật không chỉ là cập nhật một bảng. Nó vẫn là một bản cập nhật chỉ mục. Nếu bạn có nhiều chỉ mục trên bảng, thì với bản cập nhật, tất cả các chỉ mục mà các trường được cập nhật trong truy vấn tham gia cũng sẽ cần được cập nhật. Các chỉ mục này cũng sẽ có các phiên bản hàng lỗi thời cần được dọn sạch.
select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;Và do thiết kế của nó, CẬP NHẬT là một hoạt động nặng nề. Nhưng chúng có thể được thực hiện dễ dàng hơn. Ăn hot updates. Chúng đã xuất hiện trong PostgreSQL phiên bản 8.3. Còn đây là cái gì? Đây là một bản cập nhật nhẹ không khiến các chỉ mục được xây dựng lại. Nghĩa là, chúng tôi đã cập nhật bản ghi, nhưng chỉ bản ghi trong trang (thuộc về bảng) được cập nhật và các chỉ mục vẫn trỏ đến cùng một bản ghi trong trang. Có một chút logic công việc thú vị như vậy, khi chân không xuất hiện, thì nó có những chuỗi này hot xây dựng lại và mọi thứ tiếp tục hoạt động mà không cần cập nhật các chỉ mục và mọi thứ diễn ra ít lãng phí tài nguyên hơn.
Và khi bạn có n_tup_hot_upd lớn, nó rất tốt. Điều này có nghĩa là các bản cập nhật nhẹ chiếm ưu thế và điều này rẻ hơn đối với chúng tôi về mặt tài nguyên và mọi thứ đều ổn.
ALTER TABLE table_name SET (fillfactor = 70);Cách tăng âm lượng hot updaterụng trứng? chúng ta có thể sử dụng fillfactor. Nó xác định kích thước của không gian trống dành riêng khi điền một trang vào bảng bằng cách sử dụng INSERT. Khi các phần chèn vào bảng, chúng sẽ lấp đầy hoàn toàn trang, không để lại khoảng trống trong đó. Sau đó, một trang mới được đánh dấu. Dữ liệu được điền vào một lần nữa. Và đây là hành vi mặc định, fillfactor = 100%.
Chúng tôi có thể đặt hệ số lấp đầy thành 70%. Đó là, với các phần chèn, một trang mới đã được phân bổ, nhưng chỉ 70% trang được lấp đầy. Và chúng ta còn 30% dự trữ. Khi bạn cần cập nhật, rất có thể nó sẽ xảy ra trên cùng một trang và phiên bản mới của hàng sẽ vừa trên cùng một trang. Và hot_update sẽ được thực hiện. Điều này làm cho nó dễ dàng hơn để viết trên bảng.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Hàng đợi Autovacuum. Autovacuum là một hệ thống con mà có rất ít số liệu thống kê trong PostgreSQL. Chúng tôi chỉ có thể thấy trong các bảng trong pg_stat_activity chúng tôi có bao nhiêu chân không tại thời điểm này. Tuy nhiên, rất khó để hiểu nó có bao nhiêu bàn trong hàng đợi khi đang di chuyển.
Lưu ý: _Kể từ Postgres 10, tình hình theo dõi chân không chân không đã được cải thiện rất nhiều - chế độ xem pg_stat_progress đã xuất hiệnchân không, giúp đơn giản hóa đáng kể vấn đề giám sát autovacuum.
Chúng ta có thể sử dụng truy vấn đơn giản hóa này. Và chúng ta có thể thấy khi nào nên tạo chân không. Nhưng, làm thế nào và khi nào chân không nên bắt đầu? Đây là những phiên bản cũ của chuỗi mà tôi đã nói trước đó. Cập nhật đã xảy ra, phiên bản mới của hàng đã được chèn. Một phiên bản lỗi thời của chuỗi đã xuất hiện. Bàn pg_stat_user_tables có một tham số như vậy n_dead_tup. Nó hiển thị số hàng "chết". Và ngay sau khi số lượng hàng chết vượt quá một ngưỡng nhất định, autovacuum sẽ xuất hiện trên bảng.
Và ngưỡng này được tính như thế nào? Đây là một tỷ lệ phần trăm rất cụ thể trên tổng số hàng trong bảng. Có một tham số autovacuum_vacuum_scale_factor. Nó xác định tỷ lệ phần trăm. Giả sử 10% + có ngưỡng cơ sở bổ sung là 50 dòng. Và những gì sẽ xảy ra? Khi chúng tôi có nhiều hàng chết hơn "10% + 50" của tất cả các hàng trong bảng, chúng tôi sẽ đặt bảng ở trạng thái tự động hút chân không.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Tuy nhiên, có một điểm. Ngưỡng cơ bản cho các tham số av_base_thresh и av_scale_factor có thể được chỉ định riêng lẻ. Và, theo đó, ngưỡng sẽ không phải là toàn cầu, mà là riêng cho bảng. Do đó, để tính toán, ở đó bạn cần sử dụng các mánh khóe và thủ thuật. Và nếu bạn quan tâm, bạn có thể xem trải nghiệm của các đồng nghiệp của chúng tôi từ Avito (liên kết trên trang trình bày không hợp lệ và đã được cập nhật trong văn bản).
Họ viết cho có tính đến những điều này. Có một chiếc khăn lau chân trên hai tấm. Nhưng anh ấy nghĩ một cách chính xác và khá hiệu quả cho phép chúng tôi đánh giá xem chúng tôi cần nhiều chân không ở đâu cho những bàn có ít.
Những gì chúng tôi có thể làm gì về nó? Nếu chúng tôi có một hàng đợi dài và autovacuum không thể đối phó, thì chúng tôi có thể tăng số lượng nhân viên hút bụi hoặc đơn giản là làm cho máy hút bụi tích cực hơnđể nó kích hoạt sớm hơn, xử lý bảng thành từng phần nhỏ. Và do đó, hàng đợi sẽ giảm. - Điều chính ở đây là theo dõi tải trên đĩa, bởi vì. Điều chân không không miễn phí, mặc dù với sự ra đời của các thiết bị SSD / NVMe, vấn đề đã trở nên ít được chú ý hơn.
pg_stat_all_indexes là số liệu thống kê về chỉ mục. Cô ấy không lớn. Và chúng ta có thể lấy thông tin về việc sử dụng các chỉ mục từ nó. Và ví dụ, chúng ta có thể xác định những chỉ mục nào chúng ta có thêm.
Như tôi đã nói, cập nhật không chỉ cập nhật các bảng mà còn cập nhật các chỉ mục. Theo đó, nếu chúng ta có nhiều chỉ mục trên bảng, thì khi cập nhật các hàng trong bảng, chỉ mục của các trường được lập chỉ mục cũng cần được cập nhật và nếu chúng tôi có các chỉ mục không được sử dụng mà không có lần quét chỉ mục nào, thì chúng sẽ bị treo với chúng tôi như một chấn lưu. Và bạn cần phải thoát khỏi chúng. Đối với điều này, chúng ta cần một lĩnh vực idx_scan. Chúng tôi chỉ nhìn vào số lần quét chỉ mục. Nếu các chỉ mục không có lần quét nào trong khoảng thời gian lưu trữ thống kê tương đối dài (ít nhất 2-3 tuần), thì rất có thể đây là những chỉ mục xấu, chúng ta cần loại bỏ chúng.
Lưu ý: Khi tìm kiếm các chỉ mục không sử dụng trong trường hợp cụm sao chép trực tuyến, bạn cần kiểm tra tất cả các nút của cụm, bởi vì số liệu thống kê không phải là toàn cầu và nếu chỉ mục không được sử dụng trên bản gốc, thì nó có thể được sử dụng trên các bản sao (nếu có tải).
Hai liên kết:
Đây là những ví dụ truy vấn nâng cao hơn về cách tra cứu các chỉ mục không sử dụng.
Liên kết thứ hai là một truy vấn khá thú vị. Có một logic rất không tầm thường trong đó. Tôi đề nghị nó để xem xét.
Những gì khác nên được tóm tắt bởi các chỉ số?
-
Các chỉ mục không sử dụng là xấu.
-
Họ chiếm không gian.
-
Làm chậm hoạt động cập nhật.
-
Công việc phụ cho chân không.
Nếu chúng tôi loại bỏ các chỉ mục không sử dụng, thì chúng tôi sẽ chỉ làm cho cơ sở dữ liệu tốt hơn.
Quan điểm tiếp theo là pg_stat_activity. Đây là một tương tự của tiện ích ps, chỉ có trong PostgreSQL. Nếu như ps'ohm bạn xem các tiến trình trong hệ điều hành, sau đó pg_stat_activity sẽ cho bạn thấy hoạt động bên trong PostgreSQL.
Chúng ta có thể lấy gì từ đó?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Chúng ta có thể thấy hoạt động tổng thể đang diễn ra trong cơ sở dữ liệu. Chúng tôi có thể thực hiện một triển khai mới. Mọi thứ bùng nổ ở đó, các kết nối mới không được chấp nhận, các lỗi đang đổ dồn vào ứng dụng.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Chúng tôi có thể chạy một truy vấn như thế này và xem tổng tỷ lệ phần trăm kết nối so với giới hạn kết nối tối đa và xem chúng tôi có nhiều kết nối nhất với ai. Và trong trường hợp cụ thể này, chúng tôi thấy người dùng đó cron_role đã mở 508 kết nối. Và một cái gì đó đã xảy ra với anh ta. Bạn cần phải đối phó với nó và xem. Và rất có thể đây là một loại số lượng kết nối bất thường.
Nếu chúng tôi có tải OLTP, các truy vấn phải nhanh, rất nhanh và không nên có các truy vấn dài. Tuy nhiên, nếu có những yêu cầu dài hạn, thì trong ngắn hạn, không có gì phải lo lắng, nhưng về lâu dài, các truy vấn dài sẽ gây hại cho cơ sở dữ liệu, chúng làm tăng hiệu ứng phình to của các bảng khi xảy ra hiện tượng phân mảnh bảng. Cả hai truy vấn phình to và dài cần phải được xử lý.
select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;Xin lưu ý: với yêu cầu như vậy, chúng tôi có thể xác định các yêu cầu và giao dịch dài. Chúng tôi sử dụng chức năng clock_timestamp() để xác định thời gian làm việc. Các yêu cầu dài mà chúng tôi tìm thấy, chúng tôi có thể ghi nhớ chúng, thực hiện chúng explain, nhìn vào kế hoạch và bằng cách nào đó tối ưu hóa. Chúng tôi thực hiện các yêu cầu dài hạn hiện tại và tiếp tục.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Giao dịch xấu là nhàn rỗi trong giao dịch và nhàn rỗi trong giao dịch (bị hủy bỏ) giao dịch.
Nó có nghĩa là gì? Giao dịch có nhiều trạng thái. Và một trong những trạng thái này có thể mất bất cứ lúc nào. Có một trường để xác định trạng thái state theo quan điểm này. Và chúng tôi sử dụng nó để xác định trạng thái.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Và, như tôi đã nói ở trên, hai trạng thái này nhàn rỗi trong giao dịch và nhàn rỗi trong giao dịch (bị hủy bỏ) là xấu. Nó là gì? Đây là khi ứng dụng mở một giao dịch, thực hiện một số hành động và tiến hành công việc của nó. Giao dịch vẫn mở. Nó bị treo, không có gì xảy ra trong đó, nó cần một kết nối, khóa trên các hàng đã thay đổi và có khả năng vẫn làm tăng sự phình to của các bảng khác, do kiến trúc của công cụ giao dịch Postrges. Và những giao dịch như vậy cũng nên bị loại bỏ, vì chúng nói chung là có hại, trong mọi trường hợp.
Nếu bạn thấy rằng bạn có hơn 5-10-20 trong số chúng trong cơ sở dữ liệu của mình, thì bạn cần lo lắng và bắt đầu làm điều gì đó với chúng.
Ở đây chúng tôi cũng sử dụng cho thời gian tính toán clock_timestamp(). Chúng tôi thực hiện các giao dịch, chúng tôi tối ưu hóa ứng dụng.
Như tôi đã nói ở trên, khóa là khi hai hoặc nhiều giao dịch tranh giành một hoặc một nhóm tài nguyên. Đối với điều này, chúng tôi có một lĩnh vực waiting với giá trị boolean true hoặc false.
Đúng - điều này có nghĩa là quá trình đang chờ, cần phải làm gì đó. Khi một quá trình đang chờ, thì máy khách khởi tạo quá trình đó cũng đang chờ. Máy khách trong trình duyệt ngồi và cũng đợi.
Lưu ý: _Bắt đầu từ Postgres 9.6, trường waiting đã xóa và thay thế bằng hai trường thông tin khác wait_event_type и wait_event._
Phải làm gì? Nếu bạn thấy đúng trong một thời gian dài, thì bạn nên loại bỏ những yêu cầu như vậy. Chúng tôi chỉ thực hiện các giao dịch như vậy. Chúng tôi viết cho các nhà phát triển những gì cần được tối ưu hóa bằng cách nào đó để không có cuộc chạy đua về tài nguyên. Và sau đó các nhà phát triển tối ưu hóa ứng dụng để điều này không xảy ra.
Và trường hợp cực đoan, nhưng đồng thời có khả năng không gây tử vong là sự xuất hiện của bế tắc. Hai giao dịch đã cập nhật hai tài nguyên, sau đó chúng truy cập lại vào các tài nguyên đối diện. PostgreSQL trong trường hợp này tự thực hiện và loại bỏ giao dịch để giao dịch khác có thể tiếp tục hoạt động. Đây là một tình huống bế tắc và cô ấy không hiểu chính mình. Do đó, PostgreSQL buộc phải thực hiện các biện pháp cực đoan.
Và đây là hai truy vấn cho phép bạn theo dõi ổ khóa. Chúng tôi sử dụng quan điểm pg_locks, cho phép bạn theo dõi các ổ khóa nặng.
Và liên kết đầu tiên là chính văn bản yêu cầu. Nó khá dài.
Và liên kết thứ hai là một bài báo về ổ khóa. Nó rất hữu ích để đọc, nó rất thú vị.
Vậy chúng ta thấy gì? Chúng tôi thấy hai yêu cầu. Giao dịch với ALTER TABLE là một giao dịch chặn. Nó đã bắt đầu, nhưng chưa kết thúc và ứng dụng đã đăng giao dịch này đang thực hiện những việc khác ở đâu đó. Và yêu cầu thứ hai là cập nhật. Nó đợi bảng thay đổi kết thúc trước khi tiếp tục công việc của mình.
Đây là cách chúng tôi có thể tìm ra ai đã nhốt ai, ai đang giữ ai và chúng tôi có thể giải quyết vấn đề này thêm nữa.
Mô-đun tiếp theo là pg_stat_statements. Như tôi đã nói, đó là một mô-đun. Để sử dụng nó, bạn cần tải thư viện của nó trong cấu hình, khởi động lại PostgreSQL, cài đặt mô-đun (bằng một lệnh), sau đó chúng ta sẽ có một giao diện mới.
Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;Chúng ta có thể lấy gì từ đó? Nếu chúng ta nói về những điều đơn giản, chúng ta có thể lấy thời gian thực hiện truy vấn trung bình. Thời gian đang tăng lên, điều đó có nghĩa là PostgreSQL đang phản hồi chậm và cần phải làm gì đó.
Chúng ta có thể thấy các giao dịch ghi tích cực nhất trong cơ sở dữ liệu làm thay đổi dữ liệu trong bộ đệm dùng chung. Xem ai cập nhật hoặc xóa dữ liệu ở đó.
Và chúng ta chỉ có thể xem xét các số liệu thống kê khác nhau cho những yêu cầu này.
Chúng tôi pg_stat_statements dùng để xây dựng báo cáo. Chúng tôi đặt lại số liệu thống kê mỗi ngày một lần. Hãy tích lũy nó. Trước khi đặt lại số liệu thống kê vào lần tới, chúng tôi xây dựng một báo cáo. Đây là một liên kết đến báo cáo. Bạn có thể xem nó.
Chúng ta đang làm gì? Chúng tôi tính toán số liệu thống kê tổng thể cho tất cả các yêu cầu. Sau đó, đối với mỗi truy vấn, chúng tôi tính đóng góp riêng của nó vào thống kê tổng thể này.
Và những gì chúng ta có thể thấy? Chúng ta có thể thấy tổng thời gian thực hiện của tất cả các yêu cầu thuộc một loại cụ thể so với nền của tất cả các yêu cầu khác. Chúng ta có thể xem xét việc sử dụng CPU và I/O trong mối liên hệ với bức tranh tổng thể. Và đã để tối ưu hóa các yêu cầu này. Chúng tôi đang xây dựng các truy vấn hàng đầu dựa trên báo cáo này và đã suy nghĩ kỹ về những gì cần tối ưu hóa.
Chúng ta có gì đằng sau hậu trường? Vẫn còn một vài bài dự thi mà tôi đã không xem xét, vì thời gian có hạn.
Có pgstattuple cũng là một mô-đun bổ sung từ gói đóng góp tiêu chuẩn. Nó cho phép bạn đánh giá bloat bảng, cái gọi là. phân mảnh bảng. Và nếu độ phân mảnh lớn, bạn cần loại bỏ nó, sử dụng các công cụ khác nhau. Và chức năng pgstattuple hoạt động trong một thời gian dài. Và càng nhiều bảng, nó sẽ hoạt động càng lâu.
Đóng góp tiếp theo là pg_buffercache. Nó cho phép bạn kiểm tra các bộ đệm dùng chung: cường độ và các trang bộ đệm của bảng được sử dụng. Và nó chỉ cho phép bạn xem xét các bộ đệm được chia sẻ và đánh giá những gì đang xảy ra ở đó.
Mô-đun tiếp theo là pgfincore. Nó cho phép bạn thực hiện các thao tác bảng cấp thấp thông qua một cuộc gọi hệ thống mincore(), tức là nó cho phép bạn tải bảng vào bộ đệm dùng chung hoặc hủy tải bảng. Và nó cho phép, trong số những thứ khác, kiểm tra bộ đệm trang của hệ điều hành, nghĩa là bảng chiếm bao nhiêu trong bộ đệm trang, trong bộ đệm dùng chung và chỉ cho phép bạn đánh giá tải trên bảng.
Mô-đun tiếp theo là pg_stat_kcache. Nó cũng sử dụng cuộc gọi hệ thống getrusage(). Và nó thực hiện nó trước và sau khi yêu cầu được thực hiện. Và trong số liệu thống kê thu được, nó cho phép chúng tôi ước tính số lượng yêu cầu của chúng tôi dành cho I / O của đĩa, tức là các hoạt động với hệ thống tệp và xem xét mức sử dụng bộ xử lý. Tuy nhiên, mô-đun này còn non trẻ (khe-khe) và để hoạt động, nó yêu cầu PostgreSQL 9.4 và pg_stat_statements, mà tôi đã đề cập trước đó.
-
Khả năng sử dụng số liệu thống kê là hữu ích. Bạn không cần phần mềm của bên thứ ba. Bạn có thể nhìn, thấy, làm gì đó, biểu diễn.
-
Sử dụng số liệu thống kê rất dễ dàng, đó là SQL đơn giản. Bạn đã thu thập một yêu cầu, biên dịch nó, gửi nó, xem xét nó.
-
Thống kê giúp trả lời câu hỏi. Nếu bạn có câu hỏi, bạn chuyển sang số liệu thống kê - xem xét, đưa ra kết luận, phân tích kết quả.
-
Và thử nghiệm. Rất nhiều yêu cầu, rất nhiều dữ liệu. Bạn luôn có thể tối ưu hóa một số truy vấn hiện có. Bạn có thể tạo phiên bản yêu cầu của riêng mình phù hợp với bạn hơn phiên bản gốc và sử dụng nó.
tài liệu tham khảo
Các liên kết hợp lệ đã được tìm thấy trong bài báo, dựa trên đó, đã có trong báo cáo.
Tác giả viết thêm
(anh)
Người thu thập thống kê
Chức năng quản trị hệ thống
đóng góp mô-đun
Tiện ích SQL và ví dụ mã sql
Cảm ơn tất cả sự quan tâm của bạn!
Nguồn: www.habr.com