PostgreSQL 쿼리의 성능 모니터링. 파트 1 - 보고

엔지니어 - 라틴어에서 번역됨 - 영감을 받음.
엔지니어는 무엇이든 할 수 있습니다. (c) R. 디젤.
서문.

또는 데이터베이스 관리자가 프로그래밍 과거를 기억해야 하는 이유에 대한 이야기.

머리말

모든 이름이 변경되었습니다. 경기는 무작위입니다. 자료는 전적으로 저자의 개인적인 의견입니다.

보증 부인: 계획된 기사 시리즈에는 사용된 테이블과 스크립트에 대한 상세하고 정확한 설명이 없습니다. 자료는 "있는 그대로" 즉시 사용할 수 없습니다.
첫째, 재료의 양이 많기 때문에
둘째, 실제 고객의 생산 기반을 가진 날카로움 때문입니다.
따라서 가장 일반적인 형태의 아이디어와 설명만 기사에 제공됩니다.
아마도 미래에 시스템은 GitHub에 게시하는 수준으로 성장할 수도 있고 그렇지 않을 수도 있습니다. 시간이 보여 줄 것이다.

이야기의 시작-모든 것이 어떻게 시작되었는지 기억하십니까".
그 결과 가장 일반적인 용어로 "PostgreSQL 성능을 향상시키는 방법 중 하나로 합성»

이 모든 것이 왜 필요한가요?

글쎄요, 먼저 은퇴의 영광스러운 날을 기억하면서 자신을 잊지 않기 위해.
둘째, 작성된 내용을 체계화합니다. 이미 나 자신을 위해 때때로 나는 혼란스러워지기 시작하고 개별 부분을 잊어 버립니다.

글쎄, 가장 중요한 것은 갑자기 누군가에게 유용 할 수 있으며 바퀴를 재발 명하지 않고 갈퀴를 모으지 않는 데 도움이 될 수 있습니다. 즉, 업장을 개선하십시오 (Khabrovsky 아님). 이 세상에서 가장 가치 있는 것은 아이디어입니다. 가장 중요한 것은 아이디어를 찾는 것입니다. 그리고 아이디어를 현실로 번역하는 것은 이미 순전히 기술적인 문제입니다.

그럼 천천히 시작해 볼까요...

문제의 공식화.

있다 :

PostgreSQL(10.5), 혼합 로드(OLTP+DSS), 중간에서 가벼운 로드, AWS 클라우드에서 호스팅.
데이터베이스 모니터링이 없으며 인프라 모니터링은 최소 구성에서 표준 AWS 도구로 제공됩니다.

필수 :

데이터베이스의 성능과 상태를 모니터링하고, 무거운 데이터베이스 쿼리를 최적화하기 위한 초기 정보를 찾고 확보합니다.

솔루션에 대한 간략한 소개 또는 분석

우선 엔지니어의 이점과 문제점을 비교 분석하는 관점에서 문제 해결 옵션을 분석하고 직원 목록에 있어야 할 사람들이 관리의 이점과 손실을 처리하도록합시다.

옵션 1 - "주문형 작업"

우리는 모든 것을 그대로 둡니다. 고객이 데이터베이스 또는 응용 프로그램의 상태, 성능에 만족하지 않는 경우 전자 메일을 보내거나 티켓 상자에 인시던트를 생성하여 DBA 엔지니어에게 알립니다.
알림을 받은 엔지니어는 문제를 이해하고 솔루션을 제공하거나 문제를 보류하고 모든 것이 저절로 해결되기를 바라며 어쨌든 모든 것이 곧 잊혀질 것입니다.
진저브레드와 도넛, 타박상과 혹진저브레드와 도넛:
1. 특별히 할 일이 없다
2. 나가서 더러워질 기회는 항상 있습니다.
3. 혼자 쓸 수 있는 시간이 많다.
타박상 및 범프:
1. 조만간 고객은 이 세상의 존재와 보편적 정의의 본질에 대해 생각하고 다시 한 번 스스로에게 질문할 것입니다. 왜 내가 그들에게 돈을 지불하고 있습니까? 결과는 항상 동일합니다. 유일한 질문은 고객이 지루해하고 작별 인사를 할 때입니다. 그리고 피더가 비어 있습니다. 슬프다.
2. 엔지니어의 개발은 제로입니다.
3. 작업 일정 및 적재의 어려움

옵션 2 - "탬버린과 함께 춤을 추고 신발을 신고 신어보세요"

단락 1-모니터링 시스템이 필요한 이유는 무엇입니까? 모든 요청을 받게됩니다. 우리는 데이터 사전 및 동적 보기에 대한 모든 종류의 쿼리를 시작하고, 모든 종류의 카운터를 켜고, 모든 것을 테이블로 가져오고, 목록과 테이블을 주기적으로 분석합니다. 결과적으로 그래프, 표, 보고서가 아름답거나 그렇지 않습니다. 가장 중요한 것은 더 많을 것입니다.
단락 2-활동 생성-이 모든 것에 대한 분석을 실행하십시오.
단락 3-우리는 특정 문서를 준비하고 있습니다. 이 문서를 간단히 "데이터베이스를 어떻게 갖추나요?"라고 부릅니다.
단락 4-이 모든 장엄한 그래프와 그림을 본 고객은 유치한 순진한 자신감을 가지고 있습니다. 이제 모든 것이 곧 우리에게 도움이 될 것입니다. 그리고 쉽고 고통 없이 재정 자원을 사용합니다. 경영진은 또한 우리 엔지니어들이 열심히 일하고 있다고 확신합니다. 최대 로딩.
단락 5- 1단계를 정기적으로 반복합니다.
진저브레드와 도넛, 타박상과 혹진저브레드와 도넛:
1. 관리자와 엔지니어의 삶은 단순하고 예측 가능하며 활동으로 가득 차 있습니다. 모든 것이 윙윙 거리고 모두 바쁘다.
2. 고객의 삶도 나쁘지 않습니다. 그는 항상 당신이 약간의 인내심이 필요하고 모든 것이 잘 될 것이라고 확신합니다. 나아지지 않아, 음, 음 - 이 세상은 불공평해, 다음 생에서는 - 넌 운이 좋을 거야.
타박상 및 범프:
1. 조만간 같은 일을 하지만 조금 더 저렴한 유사한 서비스를 제공하는 더 똑똑한 공급자가 등장할 것입니다. 그리고 결과가 같다면 왜 더 많은 비용을 지불해야 할까요? 다시 피더가 사라질 것입니다.
2. 지루하다. 의미 있는 작은 활동이 얼마나 지루한지.
3. 이전 버전과 마찬가지로 - 개발이 없습니다. 그러나 엔지니어의 경우 마이너스는 첫 번째 옵션과 달리 여기에서 지속적으로 IDB를 생성해야 한다는 것입니다. 그리고 그것은 시간이 걸립니다. 사랑하는 사람의 이익을 위해 사용할 수 있습니다. 당신이 자신을 돌볼 수 없기 때문에 모두가 당신을 걱정합니다.

옵션 3-자전거를 발명할 필요 없이 구입하여 타면 됩니다.

다른 회사의 엔지니어는 고의로 맥주와 함께 피자를 먹습니다 (오, 90 년대 상트 페테르부르크의 영광스러운 시대). 만들어지고 디버깅되고 작동하는 모니터링 시스템을 사용합시다. 일반적으로 말해서 모니터링 시스템은 이점을 가져옵니다(적어도 작성자에게는).
진저브레드와 도넛, 타박상과 혹진저브레드와 도넛:
1. 이미 발명된 것을 발명하느라 시간을 낭비할 필요가 없습니다. 가지고 사용하십시오.
2. 모니터링 시스템은 바보가 작성한 것이 아니며 물론 유용합니다.
3. 작업 모니터링 시스템은 일반적으로 유용한 필터링 정보를 제공합니다.
타박상 및 범프:
1. 이 경우 엔지니어는 엔지니어가 아니라 다른 사람의 제품을 사용하는 사용자 또는 사용자입니다.
2. 고객은 일반적으로 이해하고 싶지 않은 것을 구매해야 할 필요성을 확신해야 하며, 일반적으로 해당 연도의 예산이 승인되었으며 변경되지 않을 것입니다. 그런 다음 별도의 리소스를 할당하고 특정 시스템에 맞게 구성해야 합니다. 저것들. 먼저 지불하고 지불하고 다시 지불해야합니다. 그리고 고객은 인색합니다. 이것이 이 삶의 규범입니다.

어떡해, 체르니셰프스키? 귀하의 질문은 매우 적절합니다. (와 함께)

이 특별한 경우와 현재 상황에서는 조금 다르게 할 수 있습니다. 자체 모니터링 시스템을 만들어 봅시다.

글쎄, 물론 시스템이 아니라 단어의 의미에서 이것은 너무 시끄럽고 주제 넘지 만 적어도 어떻게 든 자신을 더 쉽게 만들고 성능 문제를 해결하기 위해 더 많은 정보를 수집합니다. 상황에 처하지 않으려면 "거기 가세요. 어딘지 모르겠어요. 저걸 찾으세요. 뭐가 뭔지 모르겠어요."

이 옵션의 장단점은 무엇입니까?

장점 :
1. 흥미롭다. 음, 적어도 "데이터 파일 축소, 테이블스페이스 변경 등"이라는 상수보다 더 흥미롭습니다.
2. 이것은 새로운 기술과 새로운 개발입니다. 조만간 진저 브레드와 도넛을 제공 할 것입니다.
단점 :
1. 일을 해야 합니다. 많이 일하십시오.
2. 모든 활동의 의미와 관점을 정기적으로 설명해야 합니다.
3. 엔지니어가 사용할 수 있는 유일한 자원인 시간은 우주에 의해 제한되기 때문에 무언가를 희생해야 합니다.
4. 최악의 그리고 가장 불쾌한 것 - 결과적으로 "쥐도 개구리도 아닌 알 수 없는 작은 동물" 같은 쓰레기가 나올 수 있습니다.

위험을 감수하지 않는 사람은 샴페인을 마시지 않습니다.
그래서 재미가 시작됩니다.

일반적인 아이디어 - 회로도

(기사에서 가져온 그림 «PostgreSQL 성능을 향상시키는 방법 중 하나로 합성")

설명 :

• 대상 데이터베이스는 표준 PostgreSQL 확장 "pg_stat_statements"로 설치됩니다.
• 모니터링 데이터베이스에서 서비스 테이블 세트를 생성하여 초기 단계에서 pg_stat_statements 기록을 저장하고 향후 메트릭 및 모니터링을 구성합니다.
• 모니터링 호스트에서 티켓 시스템에서 인시던트를 생성하기 위한 스크립트를 포함하여 일련의 bash 스크립트를 생성합니다.

서비스 테이블

우선, 개략적으로 단순화된 ERD, 결국 발생한 일:

테이블에 대한 간략한 설명엔드 포인트 - 호스트, 인스턴스에 대한 연결 지점
데이터베이스 - 데이터베이스 옵션
pg_stat_history - 대상 데이터베이스의 pg_stat_statements 보기의 임시 스냅샷을 저장하기 위한 기록 테이블
metric_glossary - 성능 메트릭 사전
메트릭_구성 - 개별 메트릭 구성
메트릭 - 모니터링 중인 요청에 대한 특정 메트릭
metric_alert_history - 성능 경고의 역사
로그_쿼리 - AWS에서 다운로드한 PostgreSQL 로그 파일에서 구문 분석된 레코드를 저장하기 위한 서비스 테이블
기준 - 기준으로 사용되는 기간의 매개변수
체크 포인트 - 데이터베이스 상태 확인을 위한 메트릭 구성
checkpoint_alert_history - 데이터베이스 상태 확인 메트릭의 경고 기록
pg_stat_db_queries — 활성 요청의 서비스 테이블
활동 로그 — 활동 로그 서비스 테이블
함정_oid - 트랩 구성 서비스 테이블

1단계 - 성능 통계 수집 및 보고서 받기

테이블은 통계 정보를 저장하는 데 사용됩니다. pg_stat_history
pg_stat_history 테이블 구조

                                          테이블 "public.pg_stat_history" 열 | 유형 | 수정자 ---------------------+--------+---------------------------------------------------- id | 정수 | null이 아님 기본값 nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | 시간대가 없는 타임스탬프 | 데이터베이스 아이디 | 정수 | 디비드 | oid | 사용자 아이디 | oid | 쿼리ID | 비긴트 | 쿼리 | 텍스트 | 통화 | 비긴트 | 총_시간 | 배정밀도 | min_time | 배정밀도 | 최대_시간 | 배정밀도 | 평균_시간 | 배정밀도 | stddev_time | 배정밀도 | 행 | 비긴트 | 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 | 배정밀도 | 기준선 ID | 정수 | 인덱스: "pg_stat_history_pkey" 기본 키, Btree(id) "Database_idx" BTREE(DATABASE_ID) "Queryid_idx" BTREE(QUERYID) "SNAPSHOT_TIMESTAMP_IDX" BTREE(SNAPSHOTRE(SNAPSH _timestamp) 외래 키 제약 조건: "Database_id_fk" 외래 키(Database_id) 참조 Database(ID) On Delete Cascade

보시다시피 테이블은 누적 뷰 데이터일 뿐입니다. pg_stat_statements 대상 데이터베이스에서.

이 테이블의 사용법은 매우 간단합니다.

pg_stat_history 시간당 쿼리 실행의 누적 통계를 나타냅니다. 매시간 초에 테이블을 채운 후 통계 pg_stat_statements 재설정 pg_stat_statements_reset().
참고 : 기간이 1초 이상인 요청에 대해 통계가 수집됩니다.
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;

결과적으로 표에서 일정 시간이 지나면 pg_stat_history 우리는 테이블 내용의 스냅샷 세트를 갖게 될 것입니다. pg_stat_statements 대상 데이터베이스.

실제로보고

간단한 쿼리를 사용하면 매우 유용하고 흥미로운 보고서를 얻을 수 있습니다.

일정 기간 동안 집계된 데이터

주장

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 ;

DB 시간

to_char(간격 '1밀리초' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')

I/O 시간

to_char(간격 '1밀리초' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')

total_time별 TOP10 SQL

주장

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 | #| 쿼리ID| 호출| 호출 %| 총_시간(밀리초) | dbtime % +----+-----------+-----------+-----------+--------------------------------+---------- | 1| 821760255| 2| .00001|00:03:23.141(203141.681밀리초)| 5.42 | 2| 4152624390| 2| .00001|00:03:13.929( 193929.215ms.)| 5.17 | 3| 1484454471| 4| .00001|00:02:09.129( 129129.057ms.)| 3.44 | 4| 655729273| 1| .00000|00:02:01.869( 121869.981ms.)| 3.25 | 5| 2460318461| 1| .00000|00:01:33.113(93113.835밀리초)| 2.48 | 6| 2194493487| 4| .00001|00:00:17.377(17377.868밀리초)| .46 | 7| 1053044345| 1| .00000|00:00:06.156(6156.352밀리초)| .16 | 8| 3644780286| 1| .00000|00:00:01.063(1063.830ms.)| .03

총 I/O 시간별 TOP10 SQL

주장

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

---------------------------------------------------------------------------------------------------------------------------- | 총 I/O 시간별 TOP10 SQL | #| 쿼리ID| 호출| 호출 %| I/O 시간(ms)|db I/O 시간 % +----+-----------+-----------+-----------+--------------------------------+------------- | 1| 4152624390| 2| .00001|00:08:31.616(511616.592밀리초)| 31.06월 2일 | 821760255| 2| 00001| .00|08:27.099:507099.036(30.78밀리초)| 3 | 655729273| 1| 00000| .00|05:02.209:302209.137(18.35밀리초)| 4 | 2460318461| 1| 00000| .00|04:05.981:245981.117(14.93밀리초)| 5 | 1484454471| 4| 00001| .00|00:39.144:39144.221( 2.38ms.)| 6 | 2194493487| 4| 00001| .00|00:18.182:18182.816(1.10밀리초)| 7 | 1053044345| 1| 00000| .00|00:16.611:16611.722( 1.01ms.)| 8 | 3644780286| 1| 00000| .00|00:00.436:436.205(03밀리초)| .XNUMX

최대 실행 시간별 TOP10 SQL

주장

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 | #| 스냅샷| 스냅샷ID| 쿼리ID| 최대_시간(밀리초) +----+------------------+-----------+-----------+---------------------------------------- | 1| 05.04.2019/01/03 4169:655729273| 00| 02| 01.869:121869.981:2( 04.04.2019밀리초.) | 17| 00/4153/821760255 00:01| 41.570| 101570.841| 3:04.04.2019:16( 00밀리초) | 4146| 821760255/00/01 41.570:101570.841| 4| 04.04.2019| 16:00:4144( 4152624390밀리초) | 00| 01/36.964/96964.607 5:04.04.2019| 17| 00| 4151:4152624390:00( 01밀리초.) | 36.964| 96964.607/6/05.04.2019 10:00| 4188| 1484454471| 00:01:33.452( 93452.150밀리초.) | 7| 04.04.2019/17/00 4150:2460318461 | 00| 01| 33.113:93113.835:8( 04.04.2019밀리초.) | 15| 00/4140/1484454471 00:00| 11.892| 11892.302| 9:04.04.2019:16( 00밀리초) | 4145| 1484454471/00/00 11.892:11892.302| 10| 04.04.2019| 17:00:4152( 1484454471밀리초) | 00| 00/11.892/11892.302 XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX밀리초) | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX밀리초.)

SHARED 버퍼 읽기/쓰기에 의한 TOP10 SQL

주장

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 | #| 스냅샷| 스냅샷ID| 쿼리ID| 공유 블록 읽기| 공유 블록 쓰기 +----+------------------+-----------+-----------+--------------------+------- | 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 --------------------------------------------------------------------------------------

최대 실행 시간별 쿼리 분포 히스토그램

요청

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 ) ;

|------------------------------------------------------------------------------------------------------------------------------ | MAX_TIME 히스토그램 | 총 통화 수: 33851920 | 최소 시간 : 00:00:01.063 | MAX TIME : 00:02:01.869 ---------------------------------------------------------------------------------- | 최소 기간| 최대 기간| 호출 +-------------------+-----------------------------------+---------- | 00:00:01.063( 1063.830밀리초.) | 00:00:13.144( 13144.445밀리초) | 9 | 00:00:13.144( 13144.445밀리초) | 00:00:25.225( 25225.060밀리초) | 0 | 00:00:25.225( 25225.060밀리초) | 00:00:37.305(37305.675밀리초) | 0 | 00:00:37.305(37305.675밀리초) | 00:00:49.386( 49386.290밀리초.) | 0 | 00:00:49.386( 49386.290밀리초.) | 00:01:01.466(61466.906밀리초) | 0 | 00:01:01.466(61466.906밀리초) | 00:01:13.547( 73547.521밀리초.) | 0 | 00:01:13.547( 73547.521밀리초.) | 00:01:25.628( 85628.136밀리초.) | 0 | 00:01:25.628( 85628.136밀리초.) | 00:01:37.708( 97708.751밀리초.) | 4 | 00:01:37.708( 97708.751밀리초.) | 00:01:49.789( 109789.366밀리초.) | 2 | 00:01:49.789( 109789.366밀리초.) | 00:02:01.869( 121869.981밀리초.) | 0

초당 쿼리별 TOP10 스냅샷

요청

--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

|------------------------------------------------------------------------------------------------------------------------------ | QueryPerSeconds 숫자로 정렬된 TOP10 스냅샷 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | #| 스냅샷| 스냅샷ID| 호출| 총 dbtime| QPS | I/O 시간 | 입출력시간 % 1| 04.04.2019/20/04 4161:5758631| 00| 06| 30.513:390513.926:1573.396( 00밀리초)| 00| 01.470:1470.110:376( 2밀리초)| .04.04.2019 | 17| 00/4149/3529197 00:11| 48.830| 708830.618| 980.332:00:12( 47.834밀리초)| 767834.052| 108.324:3:04.04.2019(16밀리초)| 00 | 4143| 3525360/00/10 13.492:613492.351| 979.267| 00| 08:41.396:521396.555(84.988밀리초)| 4| 04.04.2019:21:03(4163밀리초)| 2781536 | 00| 03/06.470/186470.979 785.745:00| 00| 00.249| 249.865:134:5( 04.04.2019밀리초)| 19| 03:4159:2890362(00밀리초)| .03 | 16.784| 196784.755/776.979/00 00:01.441| 1441.386| 732| 6:04.04.2019:14( 00밀리초)| 4137| 2397326:00:04( 43.033밀리초)| .283033.854 | 665.924| 00/00/00.024 24.505:009| 7| 04.04.2019| 15:00:4139(2394416밀리초)| 00| 04:51.435:291435.010(665.116밀리초)| .00 | 00| 12.025/12025.895/4.126 8:04.04.2019| 13| 00| 4135:2373043:00( 04밀리초)| 26.791| 266791.988:659.179:00(00밀리초)| 00.064 | 64.261| 024/9/05.04.2019 01:03 | 4167| 4387191| 00:06:51.380( 411380.293밀리초)| 609.332| 00:05:18.847(318847.407밀리초)| .77.507 | 10| 04.04.2019/18/01 4157:1145596| 00| 01| 19.217:79217.372:313.004( 00밀리초)| 00| 01.319:1319.676:1.666( XNUMX밀리초)| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX밀리초)| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX밀리초)| XNUMX

QueryPerSeconds 및 I/O 시간을 사용한 시간별 실행 기록

주장

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

모든 SQL 선택의 텍스트

주장

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

합계

보시다시피 매우 간단한 방법으로 워크로드 및 데이터베이스 상태에 대한 많은 유용한 정보를 얻을 수 있습니다.

참고 :쿼리에서 queryid를 수정하면 별도의 요청에 대한 기록을 가져옵니다(공간을 절약하기 위해 별도의 요청에 대한 보고서는 생략됨).

따라서 쿼리 성능에 대한 통계 데이터를 사용할 수 있고 수집합니다.
첫 번째 단계 "통계 데이터 수집"이 완료됩니다.

두 번째 단계인 "성능 지표 구성"으로 진행할 수 있습니다.


그러나 그것은 또 다른 이야기입니다.

계속 될 ...

출처 : habr.com