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

pg_stat_statements + pg_stat_activity + loq_query = pg_ash ?

En complément bref de l'article Tentative de création d'un équivalent d'ASH pour PostgreSQL.

La tĂąche

Il est nĂ©cessaire de lier l'historique des vues pg_stat_statements et pg_stat_activity. En consĂ©quence, en utilisant l'historique des plans d'exĂ©cution de la table de service log_query, il est possible d'obtenir de nombreuses informations utiles pour rĂ©soudre les incidents de performance et optimiser les requĂȘtes.

Avertissement.

En raison de la poursuite des tests et du dĂ©veloppement, cet article ne peut prĂ©tendre Ă  la description d'une solution industrielle prĂȘte.

Les critiques et commentaires concernant la mise en Ɠuvre sont les bienvenus et attendus.

Données d'entrée

Table 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 without time zone ,
  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 without time zone ,
  xact_start        timestamp without time zone ,
  query_start       timestamp without time zone ,
  state_change      timestamp without time zone ,
  wait_event_type   text ,                     
  wait_event        text ,                   
  state             text ,                  
  backend_xid       xid  ,                 
  backend_xmin      xid  ,                
  query             text ,               
  backend_type      text ,
  queryid           bigint
);

Table pg_stat_db_queries

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

Vue matérialisée 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;

Table log_query

CREATE TABLE log_query
(
  id  integer ,
  queryid  bigint ,
  query_md5hash  text ,
  database_id  integer ,
  timepoint  timestamp without time zone , 
  query  text ,
  explained_plan  text[] , 
  plan_md5hash  text ,
  explained_plan_wo_costs  text[] , 
  plan_hash_value  text ,
  ip  text,
  port  text , 
  pid  integer 
);

Algorithme général

Mettre Ă  jour la table pg_stat_db_queries

Mettre à jour la vue matérialisée mvw_pg_stat_queries

CREATE OR REPLACE FUNCTION refresh_pg_stat_queries_list( database_id int) RETURNS BOOLEAN AS $$
DECLARE
 result BOOLEAN ;
 database_rec record ;  
BEGIN   
  SELECT *
  INTO database_rec
  FROM endpoint e JOIN database d ON e.id = d.endpoint_id 
  WHERE d.id = database_id  ;  
  
  IF NOT database_rec.is_need_monitoring THEN RAISE NOTICE 'AUCUN BESOIN DE SURVEILLANCE POUR database_id=%',database_id; return TRUE ; END IF ;
  
  EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' port=5432 dbname='||database_rec.name||
		                                         ' user='||database_rec.s_name||' password='||database_rec.s_pass|| ' '')';
   
  REFRESH MATERIALIZED VIEW mvw_pg_stat_queries ;
  
  PERFORM dblink_disconnect('LINK1');  

  RETURN result;
END
$$ LANGUAGE plpgsql;

Remplir la table pg_stat_db_queries

CRÉER OU REMPLACER LA FONCTION refresh_pg_stat_db_queries( ) RETOURNE BOOLEAN AS $$
DÉCLARE
 result BOOLEAN ;
 base_de_données_rec enregistrement ;  
 pg_stat_rec enregistrement ;
DÉBUT 
  TRONQUER pg_stat_db_queries;
  
  
  POUR base_de_données_rec DANS
  SÉLECTIONNER *
  DE la base_de_données d 
  BOUCLE
  
    SI PAS base_de_données_rec.is_need_monitoring ALORS SOULÈVE AVIS 'PAS BESOIN DE SURVEILLER POUR database_id=%',base_de_données_rec.id; CONTINUER ; FIN SI ;
   
    EFFECTUER refresh_pg_stat_queries_list( base_de_données_rec.id ) ; 
	
	POUR pg_stat_rec DANS
	SÉLECTIONNER * 
	DE mvw_pg_stat_queries 
	BOUCLE
	  INSÉRER DANS pg_stat_db_queries
	  ( database_id , queryid , query , max_time )
	  VALEURS
	  ( base_de_données_rec.id , pg_stat_rec.queryid , pg_stat_rec.query , pg_stat_rec.max_time);
	FIN BOUCLE;     
  FIN BOUCLE; 

  RETOURNER TRUE;
FIN
$$ LANGAGE plpgsql;

En consĂ©quence, la table contient des textes de requĂȘtes normalisĂ©s, queryid, et le temps d'exĂ©cution maximum de la requĂȘte Ă  l'heure actuelle (utilisĂ© pour le monitoring).

Remplissage de log_query et formation de l'historique des plans d'exécution.

Le texte actuel de la requĂȘte est extrait du fichier log. Le fichier log est transfĂ©rĂ© de l'hĂŽte cible vers l'hĂŽte de monitoring par morceaux, Ă  l'aide d'un script bash, via cron. Pour Ă©conomiser de l'espace et en raison de la simplicitĂ© de la tĂąche de copie d'un morceau de fichier texte de l'hĂŽte Ă  l'hĂŽte, le script n'est pas fourni.

Analyse du fichier log et extraction du texte de la requĂȘte.

#!/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

Remplissage de la table log_query.

--log_query.sql
--insĂ©rer une nouvelle requĂȘte dans la table log_query
CRÉER OU REMPLACER LA FONCTION log_query( pid_str texte , ip_port texte , log_database_id entier , log_date texte , log_time texte , duration texte , sql_line texte ) RENVERSER boolean AS $$
DÉCLARE
  résultat boolean ;
  log_timepoint horodatage sans fuseau horaire ;
  log_duration double précision ; 
  pos entier ;
  log_query texte ;
  activity_string texte ;
  log_md5hash texte ;
  log_explain_plan texte[] ;
  
  log_planhash texte ;
  log_plan_wo_costs texte[] ; 
  
  database_rec enregistrement ;
  
  pg_stat_query texte ; 
  test_log_query texte ;
  metric_rec enregistrement;
  log_query_rec enregistrement;
  found_flag boolean;
  
  pg_stat_history_rec enregistrement ;
  port_start entier ;
  port_end entier ;
  client_ip texte ;
  client_port texte ;
  log_queryid biginteger ;
  log_query_text texte ;
  pg_stat_query_text texte ; 
  current_pid_str texte ;
  current_pid entier ;
  pid_start_pos entier ;
  pid_finish_pos entier ;
  
DÉBUT
  résultat = TRUE ;    
  
  SI ip_port != '[local]' ALORS
    port_start = position('(' dans ip_port);
    port_end = position(')' dans ip_port);
    client_ip = substring( ip_port Ă  partir de 1 pour port_start-1 );
    client_port = substring( ip_port Ă  partir de port_start+1 pour port_end-port_start-1 );
  SINON 
    client_ip = 'local';
	client_port = 'local';
  FIN SI; 
  
  pid_start_pos = position('[' dans pid_str);
  pid_finish_pos = position(']' dans pid_str);
  current_pid_str=substring( pid_str Ă  partir de 2 pour pid_finish_pos - pid_start_pos -1 );
  current_pid = to_number(current_pid_str , '999999999999');
  
  SÉLECTIONNER e.host , d.name , d.owner_pwd , d.owner_user
  DANS database_rec
  DE database d JOINDRE endpoint e SUR e.id = d.endpoint_id
  OÙ d.id = log_database_id ;
  
  log_timepoint = to_timestamp(log_date||' '||log_time,'YYYY-MM-DD HH24-MI-SS');
  log_duration = duration::double précision ; 
    
  pos = position ('SELECT' dans UPPER(sql_line) );
  log_query = substring( sql_line Ă  partir de pos pour LONGUEUR(sql_line));
  log_query = regexp_replace(log_query,' +',' ','g');
  log_query = regexp_replace(log_query,';+','','g');
  log_query = trim(trailing ' ' de log_query);
  
  log_md5hash = md5( log_query::texte );
  
  --Expliquer le plan d'exécution--
EXÉCUTER 'SÉLECTIONNER 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 ( SÉLECTIONNER * DE dblink('LINK1', 'EXPLAIN '||log_query ) AS t (plan texte) );
  log_plan_wo_costs = ARRAY ( SÉLECTIONNER * DE dblink('LINK1', 'EXPLAIN ( COSTS FALSE ) '||log_query ) AS t (plan texte) );
  
  EFFECTUER dblink_disconnect('LINK1');
  --------------------------
  DÉBUT
	INSÉRER DANS log_query
	(
		query_md5hash ,
		database_id , 
		timepoint ,
		duration ,
		query ,
		explained_plan ,
		plan_md5hash , 
		explained_plan_wo_costs , 
		plan_hash_value , 
		ip , 
		port ,
		pid
	) 
	VALEURS 
	(
		log_md5hash ,
		log_database_id , 
		log_timepoint , 
		log_duration , 
		log_query ,
		log_explain_plan , 
		md5(log_explain_plan::texte) ,
		log_plan_wo_costs , 
		md5(log_plan_wo_costs::texte),
		client_ip , 
		client_port , 
		current_pid
		
	);
	activity_string = 	'Nouvelle requĂȘte enregistrĂ©e '||
						' database_id = '|| log_database_id ||
						' query_md5hash='||log_md5hash||
						' , timepoint = '||to_char(log_timepoint,'YYYYMMDD HH24:MI:SS');
	EFFECTUER pg_log( log_database_id , 'log_query' , activity_string);  

	EXCEPTION
	  QUAND unique_violation ALORS
		activity_string = 	'EXCEPTION *** la requĂȘte a dĂ©jĂ  Ă©tĂ© enregistrĂ©e '||
							' database_id = '|| log_database_id ||
							' query_md5hash='||log_md5hash||
							' , timepoint = '||to_char(log_timepoint,'YYYYMMDD HH24:MI:SS');					 
        EFFECTUER pg_log( log_database_id , 'log_query' , activity_string);
	FIN;

	SÉLECTIONNER 	queryid
	DANS   	log_queryid
	DE 	log_query 
	OÙ 	query_md5hash = log_md5hash ET
			timepoint = log_timepoint;

	SI log_queryid EST NON NULL 
	ALORS 
	  RETOURNER résultat;
	FIN SI;
	
	------------------------------------------------
	SÉLECTIONNER * 
	DANS log_query_rec
	DE log_query
	OÙ query_md5hash = log_md5hash ET timepoint = log_timepoint ; 
	
	log_query_rec.query=regexp_replace(log_query_rec.query,';+','','g');

	POUR pg_stat_history_rec DANS
	 SÉLECTIONNER 
            queryid ,
  	    query 
	 DE 
       pg_stat_db_queries 
     OÙ  
	   database_id = log_database_id ET
       queryid est non nul 
	BOUCLE
	  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 ' ' de log_query_rec.query);
	  pg_stat_query_text = pg_stat_query;   
	  
	  SI (log_query_text LIKE pg_stat_query_text) ALORS
		found_flag = TRUE ;
	  SINON
		found_flag = FALSE ;
	  FIN SI;	  

	  SI found_flag ALORS
	    
		METTRE À JOUR log_query FIXER queryid = pg_stat_history_rec.queryid OÙ query_md5hash = log_md5hash ET timepoint = log_timepoint ;
		activity_string = 	' queryid mis Ă  jour = '||pg_stat_history_rec.queryid||
		                    ' pour log_query avec id = '||log_query_rec.id;					
		SORTIR ;
	  FIN SI ;	  
	FIN BOUCLE ;	
  RETOURNER résultat ;
FIN
$$ LANGAGE plpgsql;

En consĂ©quence, la table contient le texte de la requĂȘte actuelle, les plans d'exĂ©cution, la valeur de hachage du plan d'exĂ©cution et la valeur de hachage du texte de la requĂȘte.

Remplir la valeur queryid dans la table history_pg_stat_activity

update_history_pg_stat_activity_by_queryid.sql

--update_history_pg_stat_activity_by_queryid.sql
CREATE OR REPLACE FUNCTION update_history_pg_stat_activity_by_queryid() RETURNS boolean AS $$
DECLARE
  result boolean ;
  history_pg_stat_activity_rec record ; 
  pg_stat_query text ;
  pg_stat_query_text text ;
  pg_stat_history_rec record;
  found_flag boolean;
  history_pg_stat_activity_query text ; 
  query_text text ;
  activity_string text ; 
  
BEGIN
  RAISE NOTICE '***update_history_pg_stat_activity_by_queryid';
  
  result = TRUE ;
  
  FOR history_pg_stat_activity_rec IN 
  SELECT DISTINCT(query) AS query
  FROM activity_hist.history_pg_stat_activity
  WHERE queryid IS NULL
  LOOP
		history_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_rec.query,'n+',' ','g');
		history_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_query,'t+',' ','g');
		history_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_query,' +',' ','g');
		history_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_query,';','','g');
		query_text = trim(trailing ' ' from history_pg_stat_activity_query);
		
		FOR pg_stat_history_rec IN
		SELECT 
			queryid ,
			query 
		FROM 
			--pg_stat_history
			pg_stat_db_queries
		WHERE  
			queryid is not null 
		GROUP BY 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; 
	  
			IF (query_text LIKE pg_stat_query_text) THEN
				found_flag = TRUE ;
			ELSE
				found_flag = FALSE ;
			END IF;	  
			
			IF found_flag 
			THEN
				UPDATE activity_hist.history_pg_stat_activity
				SET queryid = pg_stat_history_rec.queryid
				WHERE regexp_replace(regexp_replace(regexp_replace(regexp_replace(query,'n+',' ','g'),'t+',' ','g'),' +',' ','g'),';','','g') 
				      LIKE query_text||'%' ;
		
				activity_string = 	'history_pg_stat_activity has updated by queryid = '||pg_stat_history_rec.queryid;
				RAISE NOTICE '%',activity_string;	
				
				PERFORM pg_log( 999 , 'update_history_pg_stat_activity_by_queryid' , activity_string); 
				
				EXIT ;
				
			END IF ;	  
		END LOOP ;
		
		IF NOT found_flag 
		THEN
			activity_string = 'WARNING : Not FOUND queryid for the query : '||query_text ;
			
			RAISE NOTICE '%',activity_string;	
				
			PERFORM pg_log( 999 , 'update_history_pg_stat_activity_by_queryid' , activity_string); 
		END IF ;
		
	RAISE NOTICE 'UPDATE log_query if query has not logged in log-file';
		
  END LOOP;
  
  RETURN result ;
END
$$ LANGUAGE plpgsql;

En consĂ©quence, la table contient la valeur queryid correspondant Ă  la valeur queryid de la requĂȘte.

Conclusion

En reliant pg_stat_activity, pg_stat_statements, log_query, on peut obtenir de nombreuses informations utiles sur la requĂȘte, notamment :

  • Historique des plans d'exĂ©cution.
  • Historique du temps CPU de la requĂȘte.
  • Historique des attentes de la requĂȘte.

Les données et de nombreux rapports supplémentaires seront décrits dans l'article suivant.

DĂ©veloppement

En liant les informations disponibles avec l'historique de prĂ©sentation de pg_locks, on peut obtenir des informations sur le type de verrouillage que la requĂȘte attendait et, surtout, quel processus (requĂȘte) dĂ©tenait ce verrouillage.

La solution à ce problÚme sera décrite dans l'article suivant. Actuellement, nous sommes en phase de test et de développement.

Source : habr.com

Acheter un hĂ©bergement fiable pour les sites avec protection DDoS, serveurs VPS VDS đŸ”„ Acheter un hĂ©bergement fiable pour les sites avec protection DDoS, serveurs VPS VDS | ProHoster