Giám sát hiệu suất của các truy vấn PostgreSQL. Phần 1 - báo cáo

Kỹ sư - dịch từ tiếng Latinh - được truyền cảm hứng.
Một kỹ sư có thể làm bất cứ điều gì. (c) R. Diesel.
Văn bia.

Hoặc một câu chuyện về lý do tại sao một quản trị viên cơ sở dữ liệu cần nhớ quá khứ lập trình của mình.

lời tựa

Tất cả các tên đã được thay đổi. Các trận đấu là ngẫu nhiên. Tài liệu chỉ là quan điểm cá nhân của tác giả.

Từ chối bảo hành: trong loạt bài viết theo kế hoạch sẽ không có mô tả chi tiết và chính xác về các bảng và tập lệnh được sử dụng. Vật liệu không thể được sử dụng ngay lập tức "NGUYÊN TRẠNG".
Đầu tiên, do số lượng lớn vật liệu,
hai là do bén duyên với cơ sở sản xuất của khách hàng thật.
Do đó, chỉ những ý tưởng và mô tả ở dạng chung nhất sẽ được đưa ra trong bài viết.
Có thể trong tương lai hệ thống sẽ phát triển đến mức đăng lên GitHub, hoặc có thể không. Thời gian sẽ hiển thị.

Đầu truyện-Bạn có nhớ tất cả bắt đầu như thế nào không'.
Kết quả là chuyện gì đã xảy ra, nói một cách chung chung nhất - "Tổng hợp là một trong những phương pháp để cải thiện hiệu suất PostgreSQL»

Tại sao tôi cần tất cả những thứ này?

Thôi thì trước hết để không quên mình, nhớ lại những ngày huy hoàng khi về hưu.
Thứ hai, để hệ thống hóa những gì đã viết. Đối với bản thân tôi, đôi khi tôi bắt đầu bối rối và quên các phần riêng biệt.

Chà, và quan trọng nhất - đột nhiên nó có thể hữu ích cho ai đó và giúp không phát minh lại bánh xe và không thu thập một cái cào. Nói cách khác, hãy cải thiện nghiệp chướng của bạn (không phải Khabrovsky). Đối với điều có giá trị nhất trong thế giới này là ý tưởng. Điều chính là để tìm một ý tưởng. Và để biến ý tưởng thành hiện thực đã là một vấn đề kỹ thuật thuần túy.

Vì vậy, hãy bắt đầu từ từ ...

Công thức của vấn đề.

Có sẵn:

PostgreSQL(10.5), tải hỗn hợp (OLTP+DSS), tải trung bình đến nhẹ, được lưu trữ trên đám mây AWS.
Không có giám sát cơ sở dữ liệu, giám sát cơ sở hạ tầng được trình bày dưới dạng các công cụ AWS tiêu chuẩn trong một cấu hình tối thiểu.

Bắt buộc:

Theo dõi hiệu suất và trạng thái của cơ sở dữ liệu, tìm và có thông tin ban đầu để tối ưu hóa các truy vấn cơ sở dữ liệu nặng.

Giới thiệu ngắn gọn hoặc phân tích các giải pháp

Để bắt đầu, chúng ta hãy thử phân tích các phương án giải quyết vấn đề theo quan điểm phân tích so sánh lợi ích và khó khăn đối với kỹ sư, đồng thời để những người được cho là có tên trong danh sách nhân viên giải quyết lợi ích và tổn thất của sự quản lý.

Tùy chọn 1 - "Làm việc theo yêu cầu"

Chúng tôi để mọi thứ như nó vốn có. Nếu khách hàng không hài lòng với điều gì đó về sức khỏe, hiệu suất của cơ sở dữ liệu hoặc ứng dụng, họ sẽ thông báo cho các kỹ sư DBA bằng e-mail hoặc bằng cách tạo sự cố trong hộp yêu cầu.
Một kỹ sư, sau khi nhận được thông báo, sẽ hiểu vấn đề, đưa ra giải pháp hoặc gác lại vấn đề, hy vọng rằng mọi thứ sẽ tự giải quyết và dù sao đi nữa, mọi thứ sẽ sớm bị lãng quên.
Bánh gừng và bánh rán, vết bầm tím và da gàBánh gừng và bánh rán:
1. Không có gì thêm để làm
2. Luôn có cơ hội ra ngoài và làm bẩn.
3. Rất nhiều thời gian mà bạn có thể dành cho riêng mình.
Vết bầm tím và vết sưng:
1. Sớm hay muộn, khách hàng sẽ nghĩ về bản chất của sự tồn tại và công lý phổ quát trên thế giới này và một lần nữa tự đặt câu hỏi - tại sao tôi lại trả tiền cho họ? Hậu quả luôn là như vậy - câu hỏi duy nhất là khi khách hàng chán nản và vẫy tay chào tạm biệt. Và khay nạp trống. Thật là buồn.
2. Sự phát triển của một kỹ sư là con số không.
3. Khó khăn trong việc sắp xếp công việc và tải

Lựa chọn 2 - “Nhảy với trống lục lạc, mặc vào và đi giày”

Đoạn 1-Tại sao chúng tôi cần hệ thống giám sát, chúng tôi sẽ tiếp nhận mọi yêu cầu. Chúng tôi khởi chạy một loạt các loại truy vấn vào từ điển dữ liệu và chế độ xem động, bật tất cả các loại bộ đếm, đưa mọi thứ vào bảng, phân tích định kỳ danh sách và bảng, như vốn có. Kết quả là, chúng tôi có các biểu đồ, bảng, báo cáo đẹp hoặc không đẹp. Điều chính - đó sẽ là nhiều hơn, nhiều hơn nữa.
Đoạn 2-Tạo hoạt động-chạy phân tích tất cả điều này.
Đoạn 3-Chúng tôi đang chuẩn bị một tài liệu nhất định, chúng tôi gọi tài liệu này, đơn giản là - "làm thế nào để chúng tôi trang bị cơ sở dữ liệu."
Đoạn 4- Khách hàng, khi nhìn thấy tất cả sự lộng lẫy của các biểu đồ và số liệu, đang có một sự tự tin ngây thơ trẻ con - bây giờ mọi thứ sẽ sớm hoạt động với chúng tôi. Và, dễ dàng và không đau đớn chia tay với nguồn tài chính của họ. Quản lý cũng chắc chắn rằng các kỹ sư của chúng tôi đang làm việc chăm chỉ. Tải tối đa.
Đoạn 5- Lặp lại bước 1 thường xuyên.
Bánh gừng và bánh rán, vết bầm tím và da gàBánh gừng và bánh rán:
1. Cuộc sống của các nhà quản lý và kỹ sư rất đơn giản, có thể dự đoán trước và tràn ngập hoạt động. Mọi thứ đều ồn ào, mọi người đều bận rộn.
2. Cuộc sống của khách hàng cũng không tệ - anh ấy luôn chắc chắn rằng bạn cần kiên nhẫn một chút rồi mọi việc sẽ ổn thỏa. Không trở nên tốt hơn, tốt, tốt - thế giới này là không công bằng, trong kiếp sau - may mắn.
Vết bầm tím và vết sưng:
1. Sớm hay muộn, sẽ có một nhà cung cấp dịch vụ tương tự thông minh hơn sẽ làm điều tương tự, nhưng rẻ hơn một chút. Và nếu kết quả là như nhau, tại sao phải trả nhiều tiền hơn. Điều này một lần nữa sẽ dẫn đến sự biến mất của bộ nạp.
2. Thật nhàm chán. Làm thế nào nhàm chán bất kỳ hoạt động ít ý nghĩa.
3. Như trong phiên bản trước - không phát triển. Nhưng đối với một kỹ sư, điểm trừ là, không giống như tùy chọn đầu tiên, ở đây bạn cần liên tục tạo IDB. Và điều đó cần có thời gian. Mà có thể được chi tiêu vì lợi ích của người thân yêu của bạn. Vì bạn không thể tự chăm sóc bản thân, mọi người đều quan tâm đến bạn.

Phương án 3-Không cần phát minh ra xe đạp, bạn cần mua nó và đi.

Các kỹ sư từ các công ty khác cố tình ăn pizza với bia (ồ, thời kỳ huy hoàng của St. Petersburg những năm 90). Hãy sử dụng các hệ thống giám sát đã được tạo, sửa lỗi và đang hoạt động, và nói chung, chúng mang lại lợi ích (tốt, ít nhất là cho người tạo ra chúng).
Bánh gừng và bánh rán, vết bầm tím và da gàBánh gừng và bánh rán:
1. Không cần lãng phí thời gian để phát minh ra những gì đã được phát minh. Lấy và sử dụng.
2. Hệ thống giám sát không phải do kẻ ngu viết ra, và tất nhiên chúng rất hữu ích.
3. Hệ thống giám sát hoạt động thường cung cấp thông tin hữu ích đã được lọc.
Vết bầm tím và vết sưng:
1. Kỹ sư trong trường hợp này không phải là kỹ sư mà chỉ là người sử dụng sản phẩm của người khác, hoặc người sử dụng.
2. Khách hàng phải được thuyết phục về nhu cầu mua thứ mà anh ta thường không muốn hiểu và không nên hiểu, và nói chung ngân sách trong năm đã được phê duyệt và sẽ không thay đổi. Sau đó, bạn cần phân bổ một tài nguyên riêng, định cấu hình nó cho một hệ thống cụ thể. Những thứ kia. Trước tiên, bạn cần phải trả tiền, trả tiền và trả tiền một lần nữa. Và khách hàng là keo kiệt. Đây là chuẩn mực của cuộc sống này.

Phải làm gì, Chernyshevsky? Câu hỏi của bạn rất phù hợp. (Với)

Trong trường hợp cụ thể này và tình hình hiện tại, bạn có thể làm khác đi một chút - chúng ta hãy tạo hệ thống giám sát của riêng mình.

Tất nhiên, không phải là một hệ thống, theo nghĩa đầy đủ của từ này, điều này quá ồn ào và tự phụ, nhưng ít nhất bằng cách nào đó, hãy giúp bản thân bạn dễ dàng hơn và thu thập thêm thông tin để giải quyết các sự cố hiệu suất. Để không rơi vào tình huống - “đi đó, tôi không biết ở đâu, tìm cái đó, tôi không biết cái gì”.

Những ưu và nhược điểm của tùy chọn này là gì:

Ưu điểm:
1. Thật thú vị. Chà, ít nhất là thú vị hơn liên tục "thu nhỏ tệp dữ liệu, thay đổi không gian bảng, v.v."
2. Đây là những kỹ năng mới và sự phát triển mới. Mà trong tương lai, sớm hay muộn, sẽ cho bánh gừng và bánh rán xứng đáng.
Nhược điểm:
1. Phải làm việc. Làm việc rất nhiều.
2. Bạn sẽ phải thường xuyên giải thích ý nghĩa và quan điểm của mọi hoạt động.
3. Sẽ phải hy sinh một thứ gì đó, bởi vì nguồn tài nguyên duy nhất dành cho người kỹ sư - thời gian - bị giới hạn bởi Vũ trụ.
4. Điều tồi tệ nhất và khó chịu nhất - kết quả là, rác như "Không phải chuột, không phải ếch, mà là một con vật nhỏ chưa biết" có thể xuất hiện.

Ai không mạo hiểm một cái gì đó không uống rượu sâm banh.
Vì vậy, niềm vui bắt đầu.

Ý tưởng chung - sơ đồ

(Ảnh minh họa lấy từ bài viết «Tổng hợp là một trong những phương pháp để cải thiện hiệu suất PostgreSQL»)

Giải thích:

• Cơ sở dữ liệu đích được cài đặt với phần mở rộng PostgreSQL tiêu chuẩn “pg_stat_statements”.
• Trong cơ sở dữ liệu giám sát, chúng tôi tạo một tập hợp các bảng dịch vụ để lưu trữ lịch sử pg_stat_statements ở giai đoạn ban đầu và để định cấu hình các chỉ số và giám sát trong tương lai
• Trên máy chủ giám sát, chúng tôi tạo một tập hợp các tập lệnh bash, bao gồm các tập lệnh tạo sự cố trong hệ thống yêu cầu.

bảng dịch vụ

Để bắt đầu, một ERD được đơn giản hóa dưới dạng sơ đồ, cuối cùng điều gì đã xảy ra:

Mô tả ngắn gọn về các bảngthiết bị đầu cuối - máy chủ, điểm kết nối với thể hiện
cơ sở dữ liệu - tùy chọn cơ sở dữ liệu
pg_stat_history - bảng lịch sử để lưu trữ ảnh chụp nhanh tạm thời của chế độ xem pg_stat_statements của cơ sở dữ liệu đích
số_thuật ngữ - Từ điển đo lường hiệu suất
số liệu_config - cấu hình các số liệu cá nhân
số liệu - một số liệu cụ thể cho yêu cầu đang được theo dõi
số liệu_alert_history - lịch sử cảnh báo hiệu suất
log_query - bảng dịch vụ để lưu trữ các bản ghi được phân tích cú pháp từ tệp nhật ký PostgreSQL được tải xuống từ AWS
cơ sở - thông số của khoảng thời gian được sử dụng làm cơ sở
trạm kiểm soát - cấu hình các số liệu để kiểm tra trạng thái của cơ sở dữ liệu
điểm kiểm tra_alert_history - lịch sử cảnh báo về số liệu kiểm tra trạng thái cơ sở dữ liệu
pg_stat_db_queries — bảng dịch vụ của các yêu cầu đang hoạt động
hoạt động đăng nhập — bảng dịch vụ nhật ký hoạt động
bẫy_oid - bảng dịch vụ cấu hình bẫy

Giai đoạn 1 - thu thập số liệu thống kê hiệu suất và nhận báo cáo

Một bảng được sử dụng để lưu trữ thông tin thống kê. pg_stat_history
cấu trúc bảng pg_stat_history

                                          Bảng "public.pg_stat_history" Cột | loại | Công cụ sửa đổi ----------------------+----------------------- --+---- -------------------------------- id | số nguyên | không null mặc định nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | dấu thời gian không có múi giờ | cơ sở dữ liệu_id | số nguyên | dbid | oit | người dùng | oit | truy vấn | lớn | truy vấn | văn bản | cuộc gọi | lớn | tổng_thời gian | độ chính xác gấp đôi | thời gian tối thiểu | độ chính xác gấp đôi | thời gian tối đa | độ chính xác gấp đôi | nghĩa_thời gian | độ chính xác gấp đôi | stddev_time | độ chính xác gấp đôi | hàng | lớn | shared_blks_hit | lớn | shared_blks_read | lớn | shared_blks_dirtied | lớn | shared_blks_write | lớn | local_blks_hit | lớn | local_blks_read | lớn | local_blks_dirtied | lớn | local_blks_write | lớn | temp_blks_read | lớn | temp_blks_write | lớn | blk_read_time | độ chính xác gấp đôi | blk_write_time | độ chính xác gấp đôi | đường cơ sở_id | số nguyên | Chỉ mục: "pg_stat_history_pkey" PRIMARY KEY, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Ràng buộc khóa ngoại: "database_id_fk" FOREIGN KEY (database_id) REFERENCES database(id) ) TRÊN XÓA CASCADE

Như bạn có thể thấy, bảng chỉ là dữ liệu dạng xem tích lũy pg_stat_statements trong cơ sở dữ liệu đích.

Việc sử dụng chiếc bàn này rất đơn giản.

pg_stat_history sẽ biểu thị số liệu thống kê tích lũy về việc thực hiện truy vấn trong mỗi giờ. Đầu mỗi giờ sau khi điền bảng thống kê pg_stat_statements thiết lập lại với pg_stat_statements_reset().
Lưu ý: số liệu thống kê được thu thập cho các yêu cầu có thời lượng hơn 1 giây.
Điền vào bảng pg_stat_history

--pg_stat_history.sql
CREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$
DECLARE
  endpoint_rec record ;
  database_rec record ;
  pg_stat_snapshot record ;
  current_snapshot_timestamp timestamp without time zone;
BEGIN
  current_snapshot_timestamp = date_trunc('minute',now());  
  
  FOR endpoint_rec IN SELECT * FROM endpoint 
  LOOP
    FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id 
	  LOOP
	    
		RAISE NOTICE 'NEW SHAPSHOT IS CREATING';
		
		--Connect to the target DB	  
	    EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
 
        RAISE NOTICE 'host % and dbname % ',endpoint_rec.host,database_rec.name;
		RAISE NOTICE 'Creating snapshot of pg_stat_statements for database %',database_rec.name;
		
		SELECT 
	      *
		INTO 
		  pg_stat_snapshot
	    FROM dblink('LINK1',
	      'SELECT 
	       dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) , 
           SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
	       FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() ) 
		   GROUP BY dbid
  	      '
	               )
	      AS t
	       ( dbid oid , calls bigint , 
  	         total_time double precision , 
	         rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written	 bigint ,
             local_blks_hit	 bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
             temp_blks_read	 bigint ,temp_blks_written bigint ,
             blk_read_time double precision , blk_write_time double precision	  
	       );
		 
		INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid , calls  ,total_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	    VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );		   
		  
        RAISE NOTICE 'Creating snapshot of pg_stat_statements for queries with min_time more than 1000ms';
	
        FOR pg_stat_snapshot IN
          --All queries with max_time greater than 1000 ms
	      SELECT 
	        *
	      FROM dblink('LINK1',
	        'SELECT 
	         dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
			 shared_blks_read ,shared_blks_dirtied ,shared_blks_written , 
             local_blks_hit , local_blks_read , local_blks_dirtied , 
			 local_blks_written , temp_blks_read , temp_blks_written , blk_read_time , 
			 blk_write_time
	         FROM pg_stat_statements 
			 WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 ) 
  	        '

	                  )
	        AS t
	         ( dbid oid , userid oid , queryid bigint ,query text , calls bigint , 
  	           total_time double precision ,min_time double precision	 ,max_time double precision	 , mean_time double precision	 ,  stddev_time double precision	 , 
	           rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written	 bigint ,
               local_blks_hit	 bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
               temp_blks_read	 bigint ,temp_blks_written bigint ,
               blk_read_time double precision , blk_write_time double precision	  
	         )
	    LOOP
		  INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid ,userid  , queryid  , query  , calls  ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	      VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,pg_stat_snapshot.min_time ,pg_stat_snapshot.max_time,pg_stat_snapshot.mean_time, pg_stat_snapshot.stddev_time ,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );
		  
        END LOOP;

        PERFORM dblink_disconnect('LINK1');  
				
	  END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id 
    
  END LOOP;

RETURN TRUE;  
END
$$ LANGUAGE plpgsql;

Kết quả là sau một thời gian nhất định trong bảng pg_stat_history chúng tôi sẽ có một bộ ảnh chụp nhanh nội dung của bảng pg_stat_statements cơ sở dữ liệu đích.

Trên thực tế báo cáo

Sử dụng các truy vấn đơn giản, bạn có thể nhận được các báo cáo khá hữu ích và thú vị.

Dữ liệu tổng hợp trong một khoảng thời gian nhất định

Yêu cầu

SELECT 
  database_id , 
  SUM(calls) AS calls ,SUM(total_time)  AS total_time ,
  SUM(rows) AS rows , SUM(shared_blks_hit)  AS shared_blks_hit,
  SUM(shared_blks_read) AS shared_blks_read ,
  SUM(shared_blks_dirtied) AS shared_blks_dirtied,
  SUM(shared_blks_written) AS shared_blks_written , 
  SUM(local_blks_hit) AS local_blks_hit , 
  SUM(local_blks_read) AS local_blks_read , 
  SUM(local_blks_dirtied) AS local_blks_dirtied , 
  SUM(local_blks_written)  AS local_blks_written,
  SUM(temp_blks_read) AS temp_blks_read, 
  SUM(temp_blks_written) temp_blks_written , 
  SUM(blk_read_time) AS blk_read_time , 
  SUM(blk_write_time) AS blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;

Thời gian DB

to_char(khoảng thời gian '1 mili giây' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')

Thời gian vào/ra

to_char(khoảng '1 mili giây' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')

TOP10 SQL theo total_time

Yêu cầu

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(total_time)  AS total_time  	
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT 
GROUP BY queryid 
ORDER BY 3 DESC 
LIMIT 10

-------------------------------------------------- ------------------------------------ | TOP10 SQL THEO TỔNG THỜI GIAN THỰC HIỆN | #| truy vấn| cuộc gọi| cuộc gọi %| tổng_thời gian (ms) | dbtime % +----+----------+---------+----------+------ ---------- + ---------- | 1| 821760255| 2| .00001|00:03:23.141( 203141.681 ms.)| 5.42 | 2| 4152624390| 2| .00001|00:03:13.929( 193929.215 ms.)| 5.17 | 3| 1484454471| 4| .00001|00:02:09.129( 129129.057 ms.)| 3.44 | 4| 655729273| 1| .00000|00:02:01.869( 121869.981 ms.)| 3.25 | 5| 2460318461| 1| .00000|00:01:33.113( 93113.835 ms.)| 2.48 | 6| 2194493487| 4| .00001|00:00:17.377( 17377.868 ms.)| .46 | 7| 1053044345| 1| .00000|00:00:06.156( 6156.352 ms.)| .16 | 8| 3644780286| 1| .00000|00:00:01.063( 1063.830 mili giây)| .03

TOP10 SQL theo tổng thời gian I/O

Yêu cầu

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(blk_read_time + blk_write_time)  AS io_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY  queryid 
ORDER BY 3 DESC 
LIMIT 10

-------------------------------------------------- -------------------------------------- | TOP10 SQL THEO TỔNG THỜI GIAN I/O | #| truy vấn| cuộc gọi| cuộc gọi %| Thời gian I/O (ms)|db Thời gian I/O % +----+----------+----------+------ -----+--------------------------------+----------- -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06 Tháng Sáu | 2| 821760255| 2| .00001|00:08:27.099( 507099.036 ms.)| 30.78 | 3| 655729273| 1| .00000|00:05:02.209( 302209.137 ms.)| 18.35h4 | 2460318461| 1| 00000| 00|04:05.981:245981.117( 14.93 ms.)| 5 | 1484454471| 4| 00001| .00|00:39.144:39144.221( 2.38 ms.)| 6 | 2194493487| 4| 00001| .00|00:18.182:18182.816( 1.10 ms.)| 7 | 1053044345| 1| 00000| .00|00:16.611:16611.722( 1.01 ms.)| 8 | 3644780286| 1| 00000| .00|00:00.436:436.205( 03 mili giây)| .XNUMX

TOP10 SQL theo thời gian thực thi tối đa

Yêu cầu

SELECT 
  id AS snapshotid , 
  queryid , 
  snapshot_timestamp ,  
  max_time 
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 4 DESC 
LIMIT 10

-------------------------------------------------- ------------------------------------ | TOP10 SQL THEO THỜI GIAN THỰC HIỆN TỐI ĐA | #| ảnh chụp nhanh| ảnh chụp ID | truy vấn| max_time (ms) +------+-------------------+----------+--------- ---------------------------------------------------- | 1| 05.04.2019/01/03 4169:655729273| 00| 02| 01.869:121869.981:2( 04.04.2019 ms.) | 17| 00/4153/821760255 00:01| 41.570| 101570.841| 3:04.04.2019:16( 00 ms.) | 4146| 821760255/00/01 41.570:101570.841| 4| 04.04.2019| 16:00:4144( 4152624390 ms.) | 00| 01/36.964/96964.607 5:04.04.2019| 17| 00| 4151:4152624390:00( 01 ms.) | 36.964| 96964.607/6/05.04.2019 10:00| 4188| 1484454471| 00:01:33.452( 93452.150 ms.) | 7| 04.04.2019/17/00 4150:2460318461 | 00| 01| 33.113:93113.835:8( 04.04.2019 ms.) | 15| 00/4140/1484454471 00:00| 11.892| 11892.302| 9:04.04.2019:16( 00 ms.) | 4145| 1484454471/00/00 11.892:11892.302| 10| 04.04.2019| 17:00:4152( 1484454471 ms.) | 00| 00/11.892/11892.302 XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.) | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)

TOP10 SQL by SHARED bộ đệm đọc/ghi

Yêu cầu

SELECT 
  id AS snapshotid , 
  queryid ,
  snapshot_timestamp , 
  shared_blks_read , 
  shared_blks_written 
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( shared_blks_read > 0 OR shared_blks_written > 0 )
ORDER BY 4 DESC  , 5 DESC 
LIMIT 10

-------------------------------------------------- ------------------------------------ | TOP10 SQL BẰNG BỘ ĐỆM CHIA SẺ ĐỌC/VIẾT | #| ảnh chụp nhanh| ảnh chụp ID | truy vấn| khối chia sẻ đọc | khối chia sẻ ghi +-----+------------------+------+---------- -+----------------------+--------- | 1| 04.04.2019/17/00 4153:821760255| 797308| 0| 2| 04.04.2019 | 16| 00/4146/821760255 797308:0| 3| 05.04.2019| 01| 03 | 4169| 655729273/797158/0 4:04.04.2019| 16| 00| 4144| 4152624390 | 756514| 0/5/04.04.2019 17:00| 4151| 4152624390| 756514| 0 | 6| 04.04.2019/17/00 4150:2460318461| 734117| 0| 7| 04.04.2019 | 17| 00/4155/3644780286 52973:0| 8| 05.04.2019| 01| 03 | 4168| 1053044345/52818/0 9:04.04.2019| 15| 00| 4141| 2194493487 | 52813| 0/10/04.04.2019 16:00| 4147| 2194493487| 52813| 0 | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX -------------------------------------------------- -------------------------------------------------

Biểu đồ phân phối truy vấn theo thời gian thực hiện tối đa

yêu cầu

SELECT  
  MIN(max_time) AS hist_min  , 
  MAX(max_time) AS hist_max , 
  (( MAX(max_time) - MIN(min_time) ) / hist_columns ) as hist_width
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT ;

SELECT 
  SUM(calls) AS calls
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND
  database_id =DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND 
  ( max_time >= hist_current_min AND  max_time < hist_current_max ) ;

|------------------------------------------------- ---------------------------------------- | LỊCH SỬ MAX_TIME | TỔNG CUỘC GỌI : 33851920 | THỜI GIAN TỐI THIỂU : 00:00:01.063 | THỜI GIAN TỐI ĐA : 00:02:01.869 ------------------------------------------ ---------------------------- | thời lượng tối thiểu| thời lượng tối đa| cuộc gọi +----------------------------------+----------- ----------------------+------ | 00:00:01.063( 1063.830 ms.) | 00:00:13.144( 13144.445 ms.) | 9 | 00:00:13.144( 13144.445 ms.) | 00:00:25.225( 25225.060 ms.) | 0 | 00:00:25.225( 25225.060 ms.) | 00:00:37.305( 37305.675 ms.) | 0 | 00:00:37.305( 37305.675 ms.) | 00:00:49.386( 49386.290 ms.) | 0 | 00:00:49.386( 49386.290 ms.) | 00:01:01.466( 61466.906 ms.) | 0 | 00:01:01.466( 61466.906 ms.) | 00:01:13.547( 73547.521 ms.) | 0 | 00:01:13.547( 73547.521 ms.) | 00:01:25.628( 85628.136 ms.) | 0 | 00:01:25.628( 85628.136 ms.) | 00:01:37.708( 97708.751 ms.) | 4 | 00:01:37.708( 97708.751 ms.) | 00:01:49.789( 109789.366 ms.) | 2 | 00:01:49.789( 109789.366 ms.) | 00:02:01.869( 121869.981 ms.) | 0

TOP10 Ảnh chụp nhanh theo Truy vấn mỗi giây

yêu cầu

--pg_qps.sql
--Calculate Query Per Second 
CREATE OR REPLACE FUNCTION pg_qps( pg_stat_history_id integer ) RETURNS double precision AS $$
DECLARE
 pg_stat_history_rec record ;
 prev_pg_stat_history_id integer ;
 prev_pg_stat_history_rec record;
 total_seconds double precision ;
 result double precision;
BEGIN 
  result = 0 ;
  
  SELECT *
  INTO pg_stat_history_rec
  FROM 
    pg_stat_history
  WHERE id = pg_stat_history_id ;

  IF pg_stat_history_rec.snapshot_timestamp IS NULL 
  THEN
    RAISE EXCEPTION 'ERROR - Not found pg_stat_history for id = %',pg_stat_history_id;
  END IF ;  
  
 --RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id , 
 pg_stat_history_rec.snapshot_timestamp ;
  
  SELECT 
    MAX(id)   
  INTO
    prev_pg_stat_history_id
  FROM
    pg_stat_history
  WHERE 
    database_id = pg_stat_history_rec.database_id AND
	queryid IS NULL AND
	id < pg_stat_history_rec.id ;

  IF prev_pg_stat_history_id IS NULL 
  THEN
    RAISE NOTICE 'Not found previous pg_stat_history shapshot for id = %',pg_stat_history_id;
	RETURN NULL ;
  END IF;
  
  SELECT *
  INTO prev_pg_stat_history_rec
  FROM 
    pg_stat_history
  WHERE id = prev_pg_stat_history_id ;
  
  --RAISE NOTICE 'prev_pg_stat_history_id = % , prev_snapshot_timestamp = %', prev_pg_stat_history_id , prev_pg_stat_history_rec.snapshot_timestamp ;    

  total_seconds = extract(epoch from ( pg_stat_history_rec.snapshot_timestamp - prev_pg_stat_history_rec.snapshot_timestamp ));
  
  --RAISE NOTICE 'total_seconds = % ', total_seconds ;    
  
  --RAISE NOTICE 'calls = % ', pg_stat_history_rec.calls ;      
  
  IF total_seconds > 0 
  THEN
    result = pg_stat_history_rec.calls / total_seconds ;
  ELSE
   result = 0 ; 
  END IF;
   
 RETURN result ;
END
$$ LANGUAGE plpgsql;


SELECT 
  id , 
  snapshot_timestamp ,
  calls , 	
  total_time , 
  ( select pg_qps( id )) AS QPS ,
  blk_read_time ,
  blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( select pg_qps( id )) IS NOT NULL 
ORDER BY 5 DESC 
LIMIT 10

|------------------------------------------------- ---------------------------------------- | TOP10 Ảnh chụp nhanh được sắp xếp theo số QueryPerSeconds ------------------------------------------------ -------------------------------------------------- ------------------------------------------- | #| ảnh chụp nhanh| ảnh chụp ID | cuộc gọi| tổng thời gian db| QPS | Thời gian vào/ra | Thời gian I/O % +------+-------------------+----------+------- -------------------------------------- + ---------- -+----------------------------------+-------- | 1| 04.04.2019/20/04 4161:5758631| 00| 06| 30.513:390513.926:1573.396( 00 ms.)| 00| 01.470:1470.110:376( 2 ms.)| .04.04.2019 | 17| 00/4149/3529197 00:11| 48.830| 708830.618| 980.332:00:12( 47.834 ms.)| 767834.052| 108.324:3:04.04.2019( 16 ms.)| 00 | 4143| 3525360/00/10 13.492:613492.351| 979.267| 00| 08:41.396:521396.555( 84.988 ms.)| 4| 04.04.2019:21:03( 4163 ms.)| 2781536 | 00| 03/06.470/186470.979 785.745:00| 00| 00.249| 249.865:134:5( 04.04.2019 ms.)| 19| 03:4159:2890362( 00 ms.)| .03 | 16.784| 196784.755/776.979/00 00:01.441| 1441.386| 732| 6:04.04.2019:14( 00 ms.)| 4137| 2397326:00:04( 43.033 ms.)| .283033.854 | 665.924| 00/00/00.024 24.505:009 | 7| 04.04.2019| 15:00:4139( 2394416 ms.)| 00| 04:51.435:291435.010( 665.116 ms.)| .00 | 00| 12.025/12025.895/4.126 8:04.04.2019| 13| 00| 4135:2373043:00( 04 ms.)| 26.791| 266791.988:659.179:00( 00 ms.)| 00.064 | 64.261| 024/9/05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 ms.)| 609.332| 00:05:18.847( 318847.407 ms.)| .77.507 | 10| 04.04.2019/18/01 4157:1145596| 00| 01| 19.217:79217.372:313.004( 00 ms.)| 00| 01.319:1319.676:1.666( XNUMX ms.)| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX

Lịch sử thực thi hàng giờ với QueryPerSeconds và I/O Time

Yêu cầu

SELECT 
  id , 
  snapshot_timestamp ,
  calls , 	
  total_time , 
  ( select pg_qps( id )) AS QPS ,
  blk_read_time ,
  blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 2

|-----------------------------------------------------------------------------------------------
| HOURLY EXECUTION HISTORY  WITH QueryPerSeconds and I/O Time
-----------------------------------------------------------------------------------------------------------------------------------------------
| QUERY PER SECOND HISTORY
|    #|          snapshot| snapshotID|      calls|                      total dbtime|        QPS|                          I/O time| I/O time %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    1|  04.04.2019 11:00|       4131|       3747|  00:00:00.835(       835.374 ms.)|      1.041|  00:00:00.000(          .000 ms.)|       .000
|    2|  04.04.2019 12:00|       4133|    1002722|  00:01:52.419(    112419.376 ms.)|    278.534|  00:00:00.149(       149.105 ms.)|       .133
|    3|  04.04.2019 13:00|       4135|    2373043|  00:04:26.791(    266791.988 ms.)|    659.179|  00:00:00.064(        64.261 ms.)|       .024
|    4|  04.04.2019 14:00|       4137|    2397326|  00:04:43.033(    283033.854 ms.)|    665.924|  00:00:00.024(        24.505 ms.)|       .009
|    5|  04.04.2019 15:00|       4139|    2394416|  00:04:51.435(    291435.010 ms.)|    665.116|  00:00:12.025(     12025.895 ms.)|      4.126
|    6|  04.04.2019 16:00|       4143|    3525360|  00:10:13.492(    613492.351 ms.)|    979.267|  00:08:41.396(    521396.555 ms.)|     84.988
|    7|  04.04.2019 17:00|       4149|    3529197|  00:11:48.830(    708830.618 ms.)|    980.332|  00:12:47.834(    767834.052 ms.)|    108.324
|    8|  04.04.2019 18:01|       4157|    1145596|  00:01:19.217(     79217.372 ms.)|    313.004|  00:00:01.319(      1319.676 ms.)|      1.666
|    9|  04.04.2019 19:03|       4159|    2890362|  00:03:16.784(    196784.755 ms.)|    776.979|  00:00:01.441(      1441.386 ms.)|       .732
|   10|  04.04.2019 20:04|       4161|    5758631|  00:06:30.513(    390513.926 ms.)|   1573.396|  00:00:01.470(      1470.110 ms.)|       .376
|   11|  04.04.2019 21:03|       4163|    2781536|  00:03:06.470(    186470.979 ms.)|    785.745|  00:00:00.249(       249.865 ms.)|       .134
|   12|  04.04.2019 23:03|       4165|    1443155|  00:01:34.467(     94467.539 ms.)|    200.438|  00:00:00.015(        15.287 ms.)|       .016
|   13|  05.04.2019 01:03|       4167|    4387191|  00:06:51.380(    411380.293 ms.)|    609.332|  00:05:18.847(    318847.407 ms.)|     77.507
|   14|  05.04.2019 02:03|       4171|     189852|  00:00:10.989(     10989.899 ms.)|     52.737|  00:00:00.539(       539.110 ms.)|      4.906
|   15|  05.04.2019 03:01|       4173|       3627|  00:00:00.103(       103.000 ms.)|      1.042|  00:00:00.004(         4.131 ms.)|      4.010
|   16|  05.04.2019 04:00|       4175|       3627|  00:00:00.085(        85.235 ms.)|      1.025|  00:00:00.003(         3.811 ms.)|      4.471
|   17|  05.04.2019 05:00|       4177|       3747|  00:00:00.849(       849.454 ms.)|      1.041|  00:00:00.006(         6.124 ms.)|       .721
|   18|  05.04.2019 06:00|       4179|       3747|  00:00:00.849(       849.561 ms.)|      1.041|  00:00:00.000(          .051 ms.)|       .006
|   19|  05.04.2019 07:00|       4181|       3747|  00:00:00.839(       839.416 ms.)|      1.041|  00:00:00.000(          .062 ms.)|       .007
|   20|  05.04.2019 08:00|       4183|       3747|  00:00:00.846(       846.382 ms.)|      1.041|  00:00:00.000(          .007 ms.)|       .001
|   21|  05.04.2019 09:00|       4185|       3747|  00:00:00.855(       855.426 ms.)|      1.041|  00:00:00.000(          .065 ms.)|       .008
|   22|  05.04.2019 10:00|       4187|       3797|  00:01:40.150(    100150.165 ms.)|      1.055|  00:00:21.845(     21845.217 ms.)|     21.812

Văn bản của tất cả các lựa chọn SQL

Yêu cầu

SELECT 
  queryid , 
  query 
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid , query

Tổng

Như bạn có thể thấy, bằng các phương tiện khá đơn giản, bạn có thể nhận được rất nhiều thông tin hữu ích về khối lượng công việc và trạng thái của cơ sở dữ liệu.

Ghi chú:Nếu bạn sửa queryid trong các truy vấn, thì chúng tôi sẽ nhận được lịch sử cho một yêu cầu riêng biệt (để tiết kiệm dung lượng, các báo cáo cho một yêu cầu riêng biệt sẽ bị bỏ qua).

Vì vậy, dữ liệu thống kê về hiệu suất truy vấn có sẵn và được thu thập.
Giai đoạn đầu tiên "thu thập dữ liệu thống kê" đã hoàn thành.

Bạn có thể chuyển sang giai đoạn thứ hai - "định cấu hình số liệu hiệu suất".


Nhưng đó là một câu chuyện hoàn toàn khác.

Để được tiếp tục ...

Nguồn: www.habr.com