VÔi natuke praktilist tetrisoloogiat.
KÔik uus on hÀsti unustatud vana.
Epigrammid.
Ălesande seadmine
On vajalik perioodiliselt laadida praegune PostgreSQL logifail AWS-ist kohalikule Linuxi hostile. Mitte reaalajas, vaid ĂŒtleme, et vĂ€ikese viivitusega.
Logifaili vÀrskendamise laadimise periood on 5 minutit.
Logifail AWS-is vahetatakse iga tunni jÀrel.
Kasutatavad tööriistad
Logifaili laadimiseks hostile kasutatakse bash-skripti, mis kutsub AWS API "».
Parameetrid:
- âdb-instance-identifier: Instantsi nimi AWS-is;
- âlog-file-name: praeguse genereeritud logifaili nimi
- âmax-item: Ăldine elementide arv, mis tagastatakse kĂ€su vĂ€ljundis.Laaditava faili osa suurus.
- âstarting-token: Algse osa mĂ€rgistus
Antud konkreetses olukorras tekkis logide laadimise ĂŒlesanne töötamise kĂ€igus
Ja lihtsalt â huvitav ĂŒlesanne, et harjutada ja mitmekesistada tööaega.
Eeldan, et ĂŒlesanne on igapĂ€evasuses juba lahendatud. Kuid kiire Google'i otsing lahendusi ei nĂ€idanud ning sĂŒgavamale otsimisele polnud erilist huvi. Igatahes â hea treening.
Ălesande formaliseerimine
LÔplik logifail koosneb muutliku pikkusega ridadest. Graafiliselt saab logifaili esitada umbes nii:
Kas see hakkab natuke tuttav tunduma? Miks siin on mingit seost «tetrisega»? Siin ongi seos.
Kui kujutada graafiliselt vĂ”imalikke variatsioone, mis tekivad jĂ€rgmise faili laadimisel (kerguse huvides eeldame, et sel juhul on read ĂŒhtlase pikkusega), saame standardsed tetrise kujundid:
1) Fail on tÀielikult laaditud ja on lÔplik. Portsjoni suurus on suurem kui lÔplik faili suurus:
2) Failil on jÀtk. Portsjoni suurus on vÀiksem kui lÔpliku faili suurus:
3) Fail on eelneva faili jÀtk ning sellel on jÀtk. Portsjoni suurus on vÀiksem kui lÔpu faili jÀÀk:
4) Fail on eelneva faili jÀtk ja on lÔplik. Portsjoni suurus on suurem kui lÔpu faili jÀÀk:
Ălesanne on koguda ristkĂŒlik vĂ”i mĂ€ngida tetrise uuel tasemel.
Probleemid, mis tekivad ĂŒlesande lahendamise kĂ€igus
1) Kleepida rida kahest portsjonist
Nagu nii, suuri probleeme ei tekkinud. TĂŒĂŒpiline ĂŒlesanne algkursuse programmeerimisest.
Optimaalne portsjoni suurus
Ja see on veidi huvitavam.
Kahjuks ei ole vÔimalik kasutada nihket algselt mÀÀratud intervalli pÀrast mÀrki:
Nagu te juba teate, kasutatakse valikut âstarting-token, et mÀÀrata, kust alustada lehekĂŒlgede sirvimist. See valik vĂ”tab vastu String-tĂŒĂŒpi vÀÀrtusi, mis tĂ€hendab, et kui proovite lisada nihke vÀÀrtust jĂ€rgmise tokeni stringi ette, ei vĂ”eta seda valikut nihkena arvesse.
Ja seetÔttu tuleb lugeda osade kaupa.
Kui lugeda suurte osadega, on lugemiste arv minimaalne, kuid maht maksimaalne.
Kui lugeda vÀikeste osadega, siis vastupidi, lugemiste arv on maksimaalne, kuid maht minimaalne.
SeetĂ”ttu, et vĂ€hendada liiklust ja lahenduse ĂŒldist ilu, tuli vĂ€lja mĂ”elda mingi lahendus, kahjuks natuke sarnane toeks.
Kuna illustreerimiseks vaatame logifaili laadimise protsessi kahel tugevalt lihtsustatud juhul. Lugemiste arv mÔlemas olukorras sÔltub osa suurusest.
1) Laadime vÀikeste osadega:
2) Laadime suurte osadega:
Nagu tavaliselt, optimaalne lahendus on keskel..
Osa suurus on minimaalne, kuid lugemise kÀigus saab suurust suurendada, et vÀhendada lugemiste arvu.
Peab mĂ€rkima, et tĂ€ielikult ĂŒlesanne optimaalse lugemistera suuruse valimine ei ole veel lahendatud ja vajab sĂŒvitsi minevat töötlemist ja analĂŒĂŒsi. VĂ”ib-olla natuke hiljem.
Ăldine rakenduse kirjeldus
Kasutatavad teenustabelid
CREATE TABLE endpoint
(
id SERIAL ,
host text
);
TABLE database
(
id SERIAL ,
âŠ
last_aws_log_time text ,
last_aws_nexttoken text ,
aws_max_item_size integer
);
last_aws_log_time â viimati ĂŒles laaditud logifaili ajatempli vormingus YYYY-MM-DD-HH24.
last_aws_nexttoken â viimati ĂŒles laaditud osa tekstimĂ€rk.
aws_max_item_size - empiiriliselt mÀÀratud algne osa suurus.
Skripti tÀistekst
download_aws_piece.sh
#!/bin/bash
#########################################################
# download_aws_piece.sh
# downloan piece of log from AWS
# version HABR
let min_item_size=1024
let max_item_size=1048576
let growth_factor=3
let growth_counter=1
let growth_counter_max=3
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:''STARTED'
AWS_LOG_TIME=$1
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:AWS_LOG_TIME='$AWS_LOG_TIME
database_id=$2
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:database_id='$database_id
RESULT_FILE=$3
endpoint=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE_DATABASE -A -t -c "select e.host from endpoint e join database d on e.id = d.endpoint_id where d.id = $database_id "`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:endpoint='$endpoint
db_instance=`echo $endpoint | awk -F"." '{print toupper($1)}'`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:db_instance='$db_instance
LOG_FILE=$RESULT_FILE'.tmp_log'
TMP_FILE=$LOG_FILE'.tmp'
TMP_MIDDLE=$LOG_FILE'.tmp_mid'
TMP_MIDDLE2=$LOG_FILE'.tmp_mid2'
current_aws_log_time=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -c "select last_aws_log_time from database where id = $database_id "`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:current_aws_log_time='$current_aws_log_time
if [[ $current_aws_log_time != $AWS_LOG_TIME ]];
then
is_new_log='1'
if ! psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -q -c "update database set last_aws_log_time = '$AWS_LOG_TIME' where id = $database_id "
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - update database set last_aws_log_time .'
exit 1
fi
else
is_new_log='0'
fi
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:is_new_log='$is_new_log
let last_aws_max_item_size=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -c "select aws_max_item_size from database where id = $database_id "`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: last_aws_max_item_size='$last_aws_max_item_size
let count=1
if [[ $is_new_log == '1' ]];
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: START DOWNLOADING OF NEW AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 2
fi
else
next_token=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -c "select last_aws_nexttoken from database where id = $database_id "`
if [[ $next_token == '' ]];
then
next_token='0'
fi
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: CONTINUE DOWNLOADING OF AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--starting-token $next_token
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 3
fi
line_count=`cat $LOG_FILE | wc -l`
let lines=$line_count-1
tail -$lines $LOG_FILE > $TMP_MIDDLE
mv -f $TMP_MIDDLE $LOG_FILE
fi
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
grep -v NEXTTOKEN $LOG_FILE > $TMP_FILE
if [[ $next_token == '' ]];
then
cp $TMP_FILE $RESULT_FILE
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: NEXTTOKEN NOT FOUND - FINISH '
rm $LOG_FILE
rm $TMP_FILE
rm $TMP_MIDDLE
rm $TMP_MIDDLE2
exit 0
else
psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -q -c "update database set last_aws_nexttoken = '$next_token' where id = $database_id "
fi
first_str=`tail -1 $TMP_FILE`
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
head -$lines $TMP_FILE > $RESULT_FILE
###############################################
# MAIN CIRCLE
let count=2
while [[ $next_token != '' ]];
do
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: count='$count
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: START DOWNLOADING OF AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--starting-token $next_token
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 4
fi
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
TMP_FILE=$LOG_FILE'.tmp'
grep -v NEXTTOKEN $LOG_FILE > $TMP_FILE
last_str=`head -1 $TMP_FILE`
if [[ $next_token == '' ]];
then
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_FILE >> $RESULT_FILE
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: NEXTTOKEN NOT FOUND - FINISH '
rm $LOG_FILE
rm $TMP_FILE
rm $TMP_MIDDLE
rm $TMP_MIDDLE2
exit 0
fi
if [[ $next_token != '' ]];
then
let growth_counter=$growth_counter+1
if [[ $growth_counter -gt $growth_counter_max ]];
then
let last_aws_max_item_size=$last_aws_max_item_size*$growth_factor
let growth_counter=1
fi
if [[ $last_aws_max_item_size -gt $max_item_size ]];
then
let last_aws_max_item_size=$max_item_size
fi
psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -q -c "update database set last_aws_nexttoken = '$next_token' where id = $database_id "
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
#############################
#Get middle of file
head -$lines $TMP_FILE > $TMP_MIDDLE
line_count=`cat $TMP_MIDDLE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_MIDDLE > $TMP_MIDDLE2
cat $TMP_MIDDLE2 >> $RESULT_FILE
first_str=`tail -1 $TMP_FILE`
fi
let count=$count+1
done
#
#################################################################
exit 0
Skripti lÔigud koos mÔningate selgitustega:
Skripti sisendparameetrid:
- Logifaili nime ajatemplet vormingus YYYY-MM-DD-HH24: AWS_LOG_TIME=$1
- Andmebaasi ID: database_id=$2
- Kogutud logifaili nimi: RESULT_FILE=$3
Saada viimati ĂŒles laaditud logifaili ajatemple:
current_aws_log_time=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -c "select last_aws_log_time from database where id = $database_id "`Kui viimati ĂŒles laaditud logifaili ajatemple ei klapi sisendparameetriga â laaditakse uus logifail:
kui [[ $current_aws_log_time != $AWS_LOG_TIME ]];
siis
is_new_log='1'
kui ! psql -h ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -c "update database set last_aws_log_time = '$AWS_LOG_TIME' where id = $database_id "
siis
echo '***download_aws_piece.sh -FATAL_ERROR - update database set last_aws_log_time .'
exit 1
fi
muidu
is_new_log='0'
fi
Saame nexttokeni mÀrgi vÀÀrtuse laetud failist:
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
Laadimise lĂ”petamise mĂ€rgiks on nexttokeni tĂŒhi vÀÀrtus.
TsĂŒklis loeme faililĂ”ike, samal ajal sidudes ridu ja suurendades tĂŒki suurust:
Peamine tsĂŒkkel
# MAIN CIRCLE
let count=2
while [[ $next_token != '' ]];
do
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: count='$count
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: START DOWNLOADING OF AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--starting-token $next_token
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 4
fi
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
TMP_FILE=$LOG_FILE'.tmp'
grep -v NEXTTOKEN $LOG_FILE > $TMP_FILE
last_str=`head -1 $TMP_FILE`
if [[ $next_token == '' ]];
then
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_FILE >> $RESULT_FILE
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: NEXTTOKEN NOT FOUND - FINISH '
rm $LOG_FILE
rm $TMP_FILE
rm $TMP_MIDDLE
rm $TMP_MIDDLE2
exit 0
fi
if [[ $next_token != '' ]];
then
let growth_counter=$growth_counter+1
if [[ $growth_counter -gt $growth_counter_max ]];
then
let last_aws_max_item_size=$last_aws_max_item_size*$growth_factor
let growth_counter=1
fi
if [[ $last_aws_max_item_size -gt $max_item_size ]];
then
let last_aws_max_item_size=$max_item_size
fi
psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -q -c "update database set last_aws_nexttoken = '$next_token' where id = $database_id "
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
#############################
#Get middle of file
head -$lines $TMP_FILE > $TMP_MIDDLE
line_count=`cat $TMP_MIDDLE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_MIDDLE > $TMP_MIDDLE2
cat $TMP_MIDDLE2 >> $RESULT_FILE
first_str=`tail -1 $TMP_FILE`
fi
let count=$count+1
done
Mis edasi?
Nii et, esialgne vahe-ĂŒlesanne â 'laadida logifail pilvest' on lahendatud. Mida teha laetud logiga?
Esiteks on vajalik logifail Àra siftida ja eraldada sealt ise pÀringud.
Ălesanne ei ole eriti keeruline. Lihtne bash-skript teeb selle tĂ€ielikult Ă€ra.
upload_log_query.sh
#!/bin/bash
#########################################################
# upload_log_query.sh
# Upload table table from dowloaded aws file
# version HABR
###########################################################
echo 'TIMESTAMP:'$(date +%c)' Upload log_query table '
source_file=$1
echo 'source_file='$source_file
database_id=$2
echo 'database_id='$database_id
beginer=' '
first_line='1'
let "line_count=0"
sql_line=' '
sql_flag=' '
space=' '
cat $source_file | while read line
do
line="$space$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}' `
if [[ $current_beginer == $beginer ]]; then
if [[ $sql_flag == '1' ]]; then
sql_flag='0'
log_date=`echo $sql_line | awk -F" " '{ print $1}' `
log_time=`echo $sql_line | awk -F" " '{ print $2}' `
duration=`echo $sql_line | awk -F" " '{ print $5}' `
#replace ' to ''
sql_modline=`echo "$sql_line" | sed 's/'''/''''''/g'`
sql_line=' '
################
#PROCESSING OF THE SQL-SELECT IS HERE
if ! psql -h ENDPOINT.rds.amazonaws.com -U USER -d DATABASE -v ON_ERROR_STOP=1 -A -t -c "select log_query('$ip_port',$database_id , '$log_date' , '$log_time' , '$duration' , '$sql_modline' )"
then
echo 'FATAL_ERROR - log_query '
exit 1
fi
################
fi #if [[ $sql_flag == '1' ]]; then
let "line_count=line_count+1"
check=`echo $line | awk -F" " '{ print $8}' `
check_sql=${check^^}
#echo 'check_sql='$check_sql
if [[ $check_sql == 'SELECT' ]]; then
sql_flag='1'
sql_line="$sql_line$line"
ip_port=`echo $sql_line | awk -F":" '{ print $4}' `
fi
else
if [[ $sql_flag == '1' ]]; then
sql_line="$sql_line$line"
fi
fi #if [[ $current_beginer == $beginer ]]; then
done
NĂŒĂŒd, kui logifailist on pĂ€ring eraldatud, on vĂ”imalik sellega töötada.
Ja kasulikke vÔimalusi avaneb mitu.
Eraldatud pÀringud tuleb kuhugi salvestada. Selleks kasutatakse teenustabelit. log_query
LOOJA TABEL log_query
(
id SERIAL ,
queryid bigint ,
query_md5hash text mitte ,
database_id integer mitte ,
timepoint timestamp ilma ajavööndita mitte,
duration kahekordne tÀpsus mitte ,
query text mitte ,
selgitus_plaan text[],
plaani_md5hash text ,
selgitus_plaan_ilma_kuludeta text[],
plaani_hash_vÀÀrtus text ,
baasi_id integer ,
ip text ,
port text
);
ALTER TABLE log_query LISATA PEAMISED VĂTI (id);
ALTER TABLE log_query LISATA PIIRANG queryid_timepoint_unique_key UNIQUE (queryid, timepoint );
ALTER TABLE log_query LISATA PIIRANG query_md5hash_timepoint_unique_key UNIQUE (query_md5hash, timepoint );
LOOJA INDEX log_query_timepoint_idx ON log_query (timepoint);
LOOJA INDEX log_query_queryid_idx ON log_query (queryid);
ALTER TABLE log_query LISATA PIIRANG database_id_fk VĂLIS KLĂĂN (database_id) VIITEEKS database (id) KUSTUTAMISEL CASCADE ;
KĂ€ideldud töötlus toimub plpgsql funktsioonis âlog_query».
log_query.sql
--log_query.sql
--verison HABR
CREATE OR REPLACE FUNCTION log_query( ip_port text ,log_database_id integer , log_date text , log_time text , duration text , sql_line text ) RETURNS boolean AS $$
DECLARE
result boolean ;
log_timepoint timestamp without time zone ;
log_duration double precision ;
pos 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 ;
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 ;
BEGIN
result = TRUE ;
RAISE NOTICE '***log_query';
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 );
SELECT e.host , d.name , d.owner_pwd
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;
pos = position ('SELECT' in UPPER(sql_line) );
log_query = substring( sql_line from pos 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 );
--Explain execution plan--
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' dbname='||database_rec.name||' user=DATABASE 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
)
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
);
activity_string = 'New query has logged '||
' database_id = '|| log_database_id ||
' query_md5hash='||log_md5hash||
' , timepoint = '||to_char(log_timepoint,'YYYYMMDD HH24:MI:SS');
RAISE NOTICE '%',activity_string;
PERFORM pg_log( log_database_id , 'log_query' , activity_string);
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE '*** unique_violation *** query already has logged';
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
RAISE NOTICE 'log_query with query_md5hash = % and timepoint = % has already has a QUERYID = %',log_md5hash,log_timepoint , log_queryid ;
RETURN result;
END IF;
------------------------------------------------
RAISE NOTICE 'Update queryid';
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;
--SELECT log_query_rec.query like pg_stat_query INTO found_flag ;
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 = ' updated queryid = '||pg_stat_history_rec.queryid||
' for log_query with id = '||log_query_rec.id
;
RAISE NOTICE '%',activity_string;
EXIT ;
END IF ;
END LOOP ;
RETURN result ;
END
$$ LANGUAGE plpgsql;
Töötlemisel kasutatakse teenustabelit pg_stat_db_queries, mis sisaldab praeguste pĂ€ringute hetkeseisu tabelist pg_stat_history (Teenustabeli kasutamine on kirjeldatud siin â )
TABLE pg_stat_db_queries
(
database_id integer,
queryid bigint ,
query text ,
max_time double precision
);
TABLE pg_stat_history
(
âŠ
database_id integer ,
âŠ
queryid bigint ,
âŠ
max_time double precision ,
âŠ
);
Funktsioon vÔimaldab teostada mitmeid kasulikke vÔimalusi pÀringute töötlemiseks logifailist. Nimelt:
VĂ”imalus nr 1 â PĂ€ringute tĂ€itmise ajalugu
VĂ€ga kasulik jĂ”udlusprobleemi lahendamise alguses. Alustuseks tutvuda ajalooga â millal just aeglustumine algas?
SeejĂ€rel, klassikaliselt â otsida vĂ€liseid pĂ”hjuseid. VĂ”ib-olla on andmebaasi koormus jĂ€rsult tĂ”usnud ja konkreetne pĂ€ring pole selles sĂŒĂŒdi.
Lisada uus kirje tabelisse log_query
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 );
SELECT e.host , d.name , d.owner_pwd
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 = to_number(duration,'99999999999999999999D9999999999');
pos = position ('SELECT' in UPPER(sql_line) );
log_query = substring( sql_line from pos 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);
RAISE NOTICE 'log_query=%',log_query ;
log_md5hash = md5( log_query::text );
--Explain execution plan--
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' dbname='||database_rec.name||' user=DATABASE 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
)
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
);
VĂ”imalus nr 2 â Salvestada pĂ€ringute tĂ€itmisplaane
Sellega vĂ”ib tekkida vastuvĂ€ide-tĂ€psustus-kommentaar: «Aga autoexplain on ju olemas». See on olemas, kuid mis kasu, kui tĂ€itmisplaan salvestatakse samasse logifaili ja selleks, et seda edaspidiseks analĂŒĂŒsiks sĂ€ilitada, tuleb logifaili analĂŒĂŒsida?
Mulle oli aga vaja:
esiteks: salvestada tÀitmisplaan jÀlgimise andmebaasi teenustabelisse;
teiseks: omada vÔimalust vÔrrelda tÀitmisplaane omavahel, et kohe nÀha, et pÀringu tÀitmisplaan on muutunud.
Eri tĂ€itmisparameetrite jaoks on pĂ€ring olemas. Saada ja salvestada selle tĂ€itmisplaan, kasutades EXPLAINi â see on elementaarne ĂŒlesanne.
Rohkem veel, kasutades vĂ€ljendit EXPLAIN (COSTS FALSE), on vĂ”imalik saada plaani karkass, mida kasutatakse plaani hash-vÀÀrtuse saamiseks, mis aitab jĂ€rgneva tĂ€itmisplaani muudatuste ajaloo analĂŒĂŒsimisel.
Saada tÀitmisplaani mall
--Selgita tÀitmisplaani--
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' dbname='||database_rec.name||' user=DATABASE 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');
VĂ”imalus nr 3 â KĂŒsimuste logi kasutamine jĂ€lgimiseks
Kuna jÔudluse mÔÔdikud on seadistatud mitte pÀringu tekstile, vaid selle ID-le, tuleb logifailis olevad pÀringud siduda jÔudluse mÔÔdikutega seotud pÀringutega.
Noh, vÀhemalt selleks, et teada saada tÀpne jÔudlusjuhtumi esinemise aeg.
Nii et kui jĂ”udlusjuhtum genereerib pĂ€ringu ID, on saadud konkreetne pĂ€ring koos konkreetsete parameetrite vÀÀrtuste ja tĂ€pse tĂ€itmise ning kestuse ajaga. Selle teabe saamine kasutades ainult vaadet pg_stat_statements â ei ole vĂ”imalik.
Leida pÀringu queryid ja vÀrskendada kirjet tabelis log_query
VALI *
LOG_QUERY_REC-ile
LOG_QUERY-ıdan
WHERE QUERY_MD5HASH = LOG_MD5HASH JA AJAPUNKTI = LOG_TIMPOINT ;
LOG_QUERY_REC.QUERY=REGEXP_REPLACE(LOG_QUERY_REC.QUERY,';+','','G');
FOR PG_STAT_HISTORY_REC IN
SELEKTI
QUERYID ,
QUERY
FROM
PG_STAT_DB_QUERIES
WHERE
DATABASE_ID = LOG_DATABASE_ID JA
QUERYID NULL EI OLE
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;
--SELECT LOG_QUERY_REC.QUERY LIKE PG_STAT_QUERY INTO FOUND_FLAG ;
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 JA AJAPUNKTI = LOG_TIMPOINT ;
ACTIVITY_STRING = ' UPDATED QUERYID = ' || PG_STAT_HISTORY_REC.QUERYID ||
' LOG_QUERY ID KASI = ' || LOG_QUERY_REC.ID
;
RAISE NOTICE '%',ACTIVITY_STRING;
EXIT;
END IF ;
END LOOP ;
JÀrelsÔna
Kirjeldatud meetod leidis lĂ”puks rakendust , lubades saada rohkem teavet analĂŒĂŒsimiseks pĂ€ringute jĂ”udlusprobleemide lahendamisel.
Kuigi, loomulikult, minu isikliku arvamuse kohaselt tuleb laaditava portsjoni valiku ja suuruse muutmise algoritmi kallal veel tööd teha. Ălesanne ei ole veel ĂŒldiselt lahendatud. Ilmselt oleks see huvitav.
Aga see on juba hoopis teine luguâŠ
Allikas: habr.com
