ProHoster > Blog > Administrim > pg_stat_statements + pg_stat_activity + loq_query = pg_ash?

pg_stat_statements + pg_stat_activity + loq_query = pg_ash?

Si një shtesë të shkurtër në artikull Përpjekje për të krijuar një analog ASH për PostgreSQL.

Detyra

Është e nevojshme të lidhni historinë e prezantimeve pg_stat_statements, pg_stat_activity. Si rezultat, duke përdorur historinë e planeve të ekzekutimit nga tabela shërbyese log_query, mund të merrni shumë informacion të dobishëm për të përdorur gjatë procesit të zgjidhjes së incidenteve të performancës dhe optimizimit të pyetjeve.

Kujdes.

Për shkak të vazhdimit të testimit dhe zhvillimit, artikulli nuk mund të pretendojë se përshkruan një zgjidhje të gatshme industriale.

Kritika dhe komentet mbi implementimin priten dhe janë të mirëseardhura.

Të dhënat hyrëse

Tabela history_pg_stat_activity

--ACTIVITY_HIST.HISTORY_PG_STAT_ACTIVITY
DROP TABLE IF EXISTS activity_hist.history_pg_stat_activity;
CREATE TABLE activity_hist.history_pg_stat_activity
(
  timepoint timestamp pa zonë kohe ,
  datid             oid  , 
  datname           name ,
  pid               integer,
  usesysid          oid    ,
  usename           name   ,
  application_name  text   ,
  client_addr       inet   ,
  client_hostname   text   ,
  client_port       integer,
  backend_start     timestamp pa zonë kohe ,
  xact_start        timestamp pa zonë kohe ,
  query_start       timestamp pa zonë kohe ,
  state_change      timestamp pa zonë kohe ,
  wait_event_type   text ,                     
  wait_event        text ,                   
  state             text ,                  
  backend_xid       xid  ,                 
  backend_xmin      xid  ,                
  query             text ,               
  backend_type      text ,
  queryid           bigint
);

Tabela pg_stat_db_queries

CREATE TABLE pg_stat_db_queries
(
  database_id integer ,
  queryid bigint ,
  query text ,
  max_time double precision
);

Pamja e materializuar mvw_pg_stat_queries

CREATE MATERIALIZED VIEW public.mvw_pg_stat_queries AS
 SELECT t.queryid,
    t.max_time,
    t.query
   FROM public.dblink('LINK1'::text, 'SELECT queryid , max_time , query FROM pg_stat_statements WHERE dbid=(SELECT oid FROM  pg_database WHERE datname=current_database() ) AND max_time >= 0 '::text) t(queryid bigint, max_time double precision, query text)
  WITH NO DATA;

Tabela log_query

CREATE TABLE log_query
(
  id  integer ,
  queryid  bigint ,
  query_md5hash  text ,
  database_id  integer ,
  timepoint  timestamp pa zonë kohe , 
  query  text ,
  explained_plan  text[] , 
  plan_md5hash  text ,
  explained_plan_wo_costs  text[] ,
  plan_hash_value  text ,
  ip  text,
  port  text , 
  pid  integer 
);

Algoritmi i përgjithshëm

Përditësoni tabelën pg_stat_db_queries

Përditësoni pamjen materiale mvw_pg_stat_queries

KRIJONI O RITRAJONI FUNKSIONIN refresh_pg_stat_queries_list( database_id int) KTHEN BOOLEAN AS $$
DECLARE
 rezultat BOOLEAN ;
 regjistri_bazes record ;  
BEGIN   
  ZGJEDH *
  NË
  FROM endpoint e BASHKOHUNI database d NË e.id = d.endpoint_id 
  KU d.id = database_id  ;  
  
  NË QOFSE JO regjistri_bazes.is_need_monitoring ATËHERË RAISE NOTICE 'Nuk ka nevojë për monitorim PËR database_id=%',database_id; kthe TRUE ; FUND IF ;
  
  EKZEKUTO 'ZGJEDH dblink_connect(''LINK1'',''host='||regjistri_bazes.host||' port=5432 dbname='||regjistri_bazes.name||
		                                         ' user='||regjistri_bazes.s_name||' password='||regjistri_bazes.s_pass|| ' '')';
   
  RITRAJTO MATERIALIZUAR VIEW mvw_pg_stat_queries ;
  
  PERFORM dblink_disconnect('LINK1');  

  KTHE rezultat;
FUND
$$ GJUHA plpgsql;

Plotësoni tabelën pg_stat_db_queries

KRIJONI O RITRAJONI FUNKSIONIN refresh_pg_stat_db_queries( ) KTHEN BOOLEAN AS $$
DECLARE
 rezultat BOOLEAN ;
 regjistri_bazes record ;  
 pg_stat_rec record ;
BEGIN 
  TRUNCATE pg_stat_db_queries;
  
  
  PËR regjistri_bazes NË
  ZGJEDH *
  FROM database d 
  LOOP
  
    NË QOFSE JO regjistri_bazes.is_need_monitoring ATËHERË RAISE NOTICE 'Nuk ka nevojë për monitorim PËR database_id=%',regjistri_bazes.id; VAZHDO ; FUND IF ;
   
    PERFORM refresh_pg_stat_queries_list( regjistri_bazes.id ) ; 
	
	PËR pg_stat_rec NË
	ZGJEDH * 
	FROM mvw_pg_stat_queries 
	LOOP
	  SHTO NË pg_stat_db_queries
	  ( database_id , queryid , query , max_time )
	  VLERAT
	  ( regjistri_bazes.id , pg_stat_rec.queryid , pg_stat_rec.query , pg_stat_rec.max_time);
	FUND LOOP;     
  FUND LOOP; 

  KTHE TRUE;
FUND
$$ GJUHA plpgsql;

Si rezultat, tabela përmban tekste të normalizuara të pyetjeve, queryid, kohën maksimale të ekzekutimit të pyetjeve deri në këtë moment (përdoret për monitorim).

Doldoni log_query dhe formoni historinë e planeve të ekzekutimit.

Teksti aktual i pyetjes merret nga log-fajlli. Log-fajlli nga host-i synues në host-in e monitorimit në pjesë, me skenarin bash, përmes cron. Për të kursyer hapësirë dhe për shkak të thjeshtësisë së detyrës së kopjimit të një pjese të fajllit të tekstit nga host-i në host, skenari nuk është paraqitur.

Parse log-fajllin dhe nxirrni tekstin e pyetjes

#!/bin/bash
#########################################################
# upload_log_query.sh
# Upload table table from dowloaded aws file 
# version 12.0
###########################################################  
echo 'TIMESTAMP:'$(date +%c)' Upload log_query table '

source_file=$1
echo 'source_file='$source_file

database_id=$2
echo 'database_id='$database_id

database_name=$3
echo 'database_name='$database_name


beginer=' '
first_line='1'
let "line_count=0"
sql_line=' '
sql_flag=' '    
space=' '
cat $source_file | while read line
do
  #first line will be passed
  if [[ $line_count == '0' ]]; then 
    let "line_count++" 
    continue 
  fi
  
  line="$space$line"
  #echo 'line='$line

  if [[ $first_line == "1" ]]; then
    beginer=`echo $line | awk -F" " '{ print $1}' `
    first_line='0'
  fi

  current_beginer=`echo $line | awk -F" " '{ print $1}' `
  #echo 'current_beginer='$current_beginer
  #echo 'beginer='$beginer
  

  if [[ $current_beginer == $beginer ]]; then
   if [[ $sql_flag == '1' ]]; then
     sql_flag='0' 
     #echo 'TIMESTAMP:'$(date +%c)' Upload log_query table : SQL STATEMENT ='"$sql_line"

     log_date=`echo $sql_line | awk -F" " '{ print $1}' `
     #echo 'log_date='$log_date

     log_time=`echo $sql_line | awk -F" " '{ print $2}' `
     #echo 'log_time='$log_time
     
     duration=`echo $sql_line | awk -F" " '{ print $5}' `
     #echo 'duration='$duration

	 connect=`echo $sql_line | awk -F" " '{ print $3}' `
	 userdb=`echo $connect | awk -F":" '{ print $3}' `
	 userdb2=$userdb'@'
	 db_port_log=`echo $connect | awk -F"@" '{ print $2}' `
	 log_database_name=`echo $db_port_log | awk -F":" '{ print $1}' `
	 
	 
	 #echo 'connect='$connect
	 #echo 'userdb='$userdb
	 #echo 'userdb2='$userdb2
	 #echo 'db_port_log='$db_port_log
	 #echo 'log_database_name='$log_database_name
	 
	 if [[  "$log_database_name" != "$database_name" ]];
	 then
	   echo '*** database_name '$log_database_name' from log is not equal '$database_name' CONTINUE '
	   continue;
	 fi
	 
     #replace ' to ''
     sql_modline=`echo "$sql_line" | sed 's/'''/''''''/g'`
     sql_line=' '
	 #echo '*********************************log_query start'
     #echo 'pid_str='$pid_str 
	 #echo 'ip_port='$ip_port 
	 #echo 'database_id='$database_id 
	 #echo 'log_date='$log_date
	 #echo 'log_time='$log_time
	 #echo 'duration='$duration
	 #echo 'sql_modline='$sql_modline
     if ! psql -U monitor -d monitor -v ON_ERROR_STOP=1 -A -t -q -c "select log_query( '$pid_str' , '$ip_port' , $database_id , '$log_date' , '$log_time' , '$duration' , '$sql_modline' )"
     then
        echo 'FATAL_ERROR - log_query '
        exit 1
     fi
	 #echo '**********************************log_query finish'

    fi #if [[ $sql_flag == '1' ]]; then

    let "line_count=line_count+1"
    #echo 'line_count= '$line_count
    #echo $line

    #check=`echo $line | awk -F" " '{ print $8}' `
    #check_sql=${check^^}    

    #echo 'check_sql='$check_sql
    
	if [[ ${line^^} =~ "SELECT" ]]; 
	then 
	 if [[ $line =~ "duration:" ]];
	 then
	    test_statement=`echo $line | awk -F" " '{ print $8}'` 
		is_select=${test_statement^^}		
		
		#echo 'test_statement='$test_statement
		#echo 'is_select='$is_select
		
        if [[ $is_select == 'SELECT' ]]; 
        then		
		  sql_flag='1'    
		  sql_line="$sql_line$line"
		  ip_port=`echo $sql_line | awk -F":" '{ print $4}' `
		  pid_str=`echo $sql_line | awk -F":" '{ print $6}' `
		fi
	 fi	 
    fi
  else       
    #echo $line
    #echo 'sql_flag ='$sql_flag

    if [[ $sql_flag == '1' ]]; then
      sql_line="$sql_line$line"
    fi   
    
  fi #if [[ $current_beginer == $beginer ]]; then

done

Plotësoni tabelën log_query

--log_query.sql
--shto një kërkesë të re në tabelën log_query
CREATE OR REPLACE FUNCTION log_query( pid_str text , ip_port text ,log_database_id integer , log_date text , log_time text , duration text , sql_line text   ) RETURNS boolean AS $$
DECLARE
  rezultat boolean ;
  log_timepoint timestamp without time zone ;
  log_duration double precision ; 
  poz integer ;
  log_query text ;
  activity_string text ;
  log_md5hash text ;
  log_explain_plan text[] ;
  
  log_planhash text ;
  log_plan_wo_costs text[] ; 
  
  database_rec record ;
  
  pg_stat_query text ; 
  test_log_query text ;
  metric_rec record;
  log_query_rec record;
  found_flag boolean;
  
  pg_stat_history_rec record ;
  port_start integer ;
  port_end integer ;
  client_ip text ;
  client_port text ;
  log_queryid bigint ;
  log_query_text text ;
  pg_stat_query_text text ; 
  current_pid_str text ;
  current_pid integer;
  pid_start_pos integer ;
  pid_finish_pos integer ;
  
BEGIN
  rezultat = TRUE ;    
  
  IF ip_port != '[local]' THEN
    port_start = position('(' in ip_port);
    port_end = position(')' in ip_port);
    client_ip = substring( ip_port from 1 for port_start-1 );
    client_port = substring( ip_port from port_start+1 for port_end-port_start-1 );
  ELSE 
    client_ip = 'local';
	client_port = 'local';
  END IF; 
  
  pid_start_pos = position('[' in pid_str);
  pid_finish_pos = position(']' in pid_str);
  current_pid_str=substring( pid_str from 2 for pid_finish_pos - pid_start_pos -1 );
  current_pid = to_number(current_pid_str , '999999999999');
  
  SELECT e.host , d.name , d.owner_pwd , d.owner_user
  INTO database_rec
  FROM database d JOIN endpoint e ON e.id = d.endpoint_id
  WHERE d.id = log_database_id ;
  
  log_timepoint = to_timestamp(log_date||' '||log_time,'YYYY-MM-DD HH24-MI-SS');
  log_duration = duration::double precision ; 
    
  poz = position ('SELECT' in UPPER(sql_line) );
  log_query = substring( sql_line from poz for LENGTH(sql_line));
  log_query = regexp_replace(log_query,' +',' ','g');
  log_query = regexp_replace(log_query,';+','','g');
  log_query = trim(trailing ' ' from log_query);
  
  log_md5hash = md5( log_query::text );
  
  --Shpjego planin e ekzekutimit--
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' port=5432 dbname='||database_rec.name||' user='||database_rec.owner_user||' password='||database_rec.owner_pwd||' '')'; 
  
  log_explain_plan = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN '||log_query ) AS t (plan text) );
  log_plan_wo_costs = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN ( COSTS FALSE ) '||log_query ) AS t (plan text) );
  
  PERFORM dblink_disconnect('LINK1');
  --------------------------
  BEGIN
	INSERT INTO log_query
	(
		query_md5hash ,
		database_id , 
		timepoint ,
		duration ,
		query ,
		explained_plan ,
		plan_md5hash , 
		explained_plan_wo_costs , 
		plan_hash_value , 
		ip , 
		port ,
		pid
	) 
	VALUES 
	(
		log_md5hash ,
		log_database_id , 
		log_timepoint , 
		log_duration , 
		log_query ,
		log_explain_plan , 
		md5(log_explain_plan::text) ,
		log_plan_wo_costs , 
		md5(log_plan_wo_costs::text),
		client_ip , 
		client_port , 
		current_pid
		
	);
	activity_string = 	'Kërkesa e re është regjistruar '||
						' database_id = '|| log_database_id ||
						' query_md5hash='||log_md5hash||
						' , timepoint = '||to_char(log_timepoint,'YYYYMMDD HH24:MI:SS');
	PERFORM pg_log( log_database_id , 'log_query' , activity_string);  

	EXCEPTION
	  WHEN unique_violation THEN
		activity_string = 	'EXCEPTION *** kërkesa tashmë është regjistruar '||
							' database_id = '|| log_database_id ||
							' query_md5hash='||log_md5hash||
							' , timepoint = '||to_char(log_timepoint,'YYYYMMDD HH24:MI:SS');					 
        PERFORM pg_log( log_database_id , 'log_query' , activity_string);
	END;

	SELECT 	queryid
	INTO   	log_queryid
	FROM 	log_query 
	WHERE 	query_md5hash = log_md5hash AND
			timepoint = log_timepoint;

	IF log_queryid IS NOT NULL 
	THEN 
	  RETURN rezultat;
	END IF;
	
	------------------------------------------------
	SELECT * 
	INTO log_query_rec
	FROM log_query
	WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ; 
	
	log_query_rec.query=regexp_replace(log_query_rec.query,';+','','g');

	FOR pg_stat_history_rec IN
	 SELECT 
            queryid ,
  	    query 
	 FROM 
       pg_stat_db_queries 
     WHERE  
	   database_id = log_database_id AND
       queryid is not null 
	LOOP
	  pg_stat_query = pg_stat_history_rec.query ; 
	  pg_stat_query=regexp_replace(pg_stat_query,'n+',' ','g');
	  pg_stat_query=regexp_replace(pg_stat_query,'t+',' ','g');
	  pg_stat_query=regexp_replace(pg_stat_query,' +',' ','g');
	  pg_stat_query=regexp_replace(pg_stat_query,'$.','%','g');
	
	  log_query_text = trim(trailing ' ' from log_query_rec.query);
	  pg_stat_query_text = pg_stat_query;   
	  
	  IF (log_query_text LIKE pg_stat_query_text) THEN
		found_flag = TRUE ;
	  ELSE
		found_flag = FALSE ;
	  END IF;	  

	  IF found_flag THEN
	    
		UPDATE log_query SET queryid = pg_stat_history_rec.queryid WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
		activity_string = 	' identifikimi i kërkesës është azhurnuar = '||pg_stat_history_rec.queryid||
		                    ' për log_query me id = '||log_query_rec.id;					
		EXIT ;
	  END IF ;	  
	END LOOP ;	
  RETURN rezultat ;
END
$$ LANGUAGE plpgsql;

Si pasqyra përmban tekstin aktual të pyetjes, planet e ekzekutimit, hash-vlerën e planit të ekzekutimit dhe hash-vlerën e tekstin e pyetjes.

Mbushni vlerën queryid në tabelën history_pg_stat_activity.

update_history_pg_stat_activity_by_queryid.sql

--update_history_pg_stat_activity_by_queryid.sql
KRIJO O RIVENDOS FUNKSIONIN update_history_pg_stat_activity_by_queryid() KTHE boolean SI $$
SHKELQIM
  rezultati boolean ;
  historia_pg_stat_activity_rec regjistër ; 
  pg_stat_query tekst ;
  pg_stat_query_text tekst ;
  pg_stat_history_rec regjistër;
  found_flag boolean;
  historia_pg_stat_activity_query tekst ; 
  query_text tekst ;
  activity_string tekst ; 
  
KRUJENI
  RISING NOTICE '***update_history_pg_stat_activity_by_queryid';
  
  rezultati = TRUE ;
  
  PËR historia_pg_stat_activity_rec NË 
  ZGJEDH DISTI NË (query) SI pyetje
  NGA activity_hist.history_pg_stat_activity
  KU queryid ËSHTË NULL
  LOOP
		historia_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_rec.query,'n+',' ','g');
		historia_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_query,'t+',' ','g');
		historia_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_query,' +',' ','g');
		historia_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_query,';','','g');
		query_text = trim(trailing ' ' from historia_pg_stat_activity_query);
		
		PËR pg_stat_history_rec NË
		ZGJEDH 
			queryid ,
			pyetje 
		Nga 
			--pg_stat_history
			pg_stat_db_queries
		KU  
			queryid s'ka NULL 
		GRUPI NGA queryid , query 
		LOOP
			pg_stat_query = pg_stat_history_rec.query ; 
			pg_stat_query=regexp_replace(pg_stat_query,'n+',' ','g');
			pg_stat_query=regexp_replace(pg_stat_query,'t+',' ','g');
			pg_stat_query=regexp_replace(pg_stat_query,' +',' ','g');
			pg_stat_query=regexp_replace(pg_stat_query,'$.','%','g');	
			
			pg_stat_query_text = pg_stat_query; 
	  
			NËSË (query_text LIKE pg_stat_query_text) ATËHERË
				found_flag = TRUE ;
			NDYSHIM
				found_flag = FALSE ;
			KREJT KJO;	  
			
			NËSË found_flag 
			ATËHERË
				PËRDITËSO aktiviteti_hist.history_pg_stat_activity
				SET queryid = pg_stat_history_rec.queryid
				KU regexp_replace(regexp_replace(regexp_replace(regexp_replace(query,'n+',' ','g'),'t+',' ','g'),' +',' ','g'),';','','g') 
				      LIKE query_text||'%' ;
		
				activity_string = 	'history_pg_stat_activity është përditësuar nga queryid = '||pg_stat_history_rec.queryid;
				RISING NOTICE '%',activity_string;	
				
				PËRFORM pg_log( 999 , 'update_history_pg_stat_activity_by_queryid' , aktiviteti_string); 
				
				EXIT ;
				
			KREJT KJO; 	  
		END LOOP ;
		
		NËSË NUK found_flag 
		ATËHERË
			activity_string = 'KËSHILLIM : Queryid nuk u gjet për pyetjen : '||query_text ;
			
			RISING NOTICE '%',activity_string;	
				
			PËRFORM pg_log( 999 , 'update_history_pg_stat_activity_by_queryid' , aktiviteti_string); 
		KREJT KJO;
		
	RISING NOTICE 'PËRMBAJTE log_query nëse pyetja nuk është regjistruar në log-file';
		
  END LOOP;
  
  KTHENI rezultatin ;
KREJT
$$ GJUHA plpgsql;

Si pasojë, tabela përmban vlerën queryid përkatëse të vlerës queryid të pyetjes.

Përfundimi

Duke lidhur pg_stat_activity, pg_stat_statements, log_query, mund të merrni shumë informacione të dobishme për pyetjen, veçanërisht:

  • Historia e planeve të ekzekutimit.
  • Historia e kohës CPU të pyetjes.
  • Historia e pritjeve të pyetjes.

Të dhëna dhe shumë raporte shtesë do të përshkruhen në artikullin e ardhshëm.

Zhvillimi

Duke lidh informacionin ekzistues me historinë e paraqitjes pg_locks, mund të merrni informacion në lidhje me se cilën bllokim konkret priste kërkesa dhe më e rëndësishmja, cili proces (kërkesë) e mbante këtë bllokim.

Zgjidhja e kësaj detyre do të përshkruhet në artikullin e ardhshëm. Tani po bëhet testimi dhe përmirësimi.

Burimi: habr.com

Blini hostim të besueshëm për faqe interneti me mbrojtje DDoS, serverë VPS VDS 🔥 Blini hostim të besueshëm për faqe interneti me mbrojtje DDoS, serverë VPS VDS - ProHoster