Kapa bonyane ba tetrisology e sebelisitsoeng.
Ntho e 'ngoe le e 'ngoe e ncha e lebetsoe ea khale.
Epigraphs.
Mokhoa oa bothata
Hoa hlokahala hore nako le nako u khoasolle faele ea hona joale ea PostgreSQL ho tloha lerung la AWS ho ea ho moamoheli oa Linux oa lehae. Eseng ka nako ea nnete, empa, na re ka re, ka tieho e nyane.
Nako ea ho jarolla lifaele tsa log ke metsotso e 5.
Faele ea log, ho AWS, e potoloha hora e 'ngoe le e 'ngoe.
Lisebelisoa tse sebelisitsoeng
Ho kenya faele ea log ho moamoheli, ho sebelisoa lengolo la bash le bitsang AWS API "".
Parameters:
- --db-instance-identifier: Lebitso la mohlala ho AWS;
- --log-file-name: lebitso la faele ea log e hlahisitsoeng hajoale
- --max-item: Palo eohle ea lintho tse khutlisitsoeng tlhahiso ea taelo.Boholo ba karolo ea faele e jarollotsoeng.
- --starting-token: Letshwao la ho qala
Tabeng ena, mosebetsi oa ho khoasolla li-log o hlahile nakong ea mosebetsi
E, 'me feela - mosebetsi o thahasellisang oa koetliso le mefuta-futa nakong ea lihora tsa mosebetsi.
Ke nka hore bothata bo se bo rarollotsoe ka lebaka la tloaelo. Empa Google e potlakileng ha ea ka ea fana ka maikutlo a tharollo, 'me ho ne ho se na takatso e itseng ea ho batla ka botebo. Leha ho le joalo, ke boikoetliso bo botle.
Ntlafatso ea mosebetsi
Faele ea ho qetela ea log ke sete ea mela ea bolelele bo fapaneng. Ka mokhoa o hlakileng, faele ea log e ka emeloa tjena:
Na e se e u hopotsa ho hong? "Tetris" ke eng? Mme ke eng.
Haeba re emela likhetho tse ka hlahang ha u kenya faele e latelang ka mokhoa o hlakileng (bakeng sa ho nolofatsa, tabeng ena, tlohela mela e be le bolelele bo lekanang), re fumana. lipalo tse tloaelehileng tsa tetris:
1) Faele e jarollotsoe ka botlalo 'me ke ea ho qetela. Boholo ba chunk bo boholo ho feta saese ea ho qetela ea faele:
2) Faele e na le tswelopele. Boholo ba chunk bo nyane ho feta saese ea ho qetela ea faele:
3) Faele ke tswelopele ya faele e fetileng mme e na le tswelopele. Boholo ba chunk bo ka tlase ho boholo ba faele ea ho qetela:
4) Faele ke tsoelo-pele ea faele e fetileng mme ke ea ho qetela. Boholo ba chunk bo boholo ho feta boholo ba faele ea ho qetela:
Mosebetsi ke ho bokella khutlonnetsepa kapa ho bapala Tetris boemong bo bocha.
Mathata a hlahang nakong ea ho rarolla bothata
1) Khomarela khoele ea likarolo tse peli
Ka kakaretso, ho ne ho se na mathata a itseng. Mosebetsi o tloaelehileng ho tloha thupelong ea pele ea lenaneo.
Saese e nepahetseng ea ho sebeletsa
Empa sena se thahasellisa haholoanyane.
Ka bomalimabe, ha ho na mokhoa oa ho sebelisa offset kamora ho qala chunk label:
Joalo ka ha u se u ntse u tseba khetho --starting-token e sebelisoa ho hlakisa hore na o qala ho etsa pagination hokae. Khetho ena e nka litekanyetso tsa String tse ka bolelang hore haeba u leka ho eketsa boleng ba offset ka pel'a mohala oa Next Token, khetho e ke ke ea nkoa e le ho fokotsa.
'Me kahoo, u tlameha ho bala ka likaroloana.
Haeba u bala ka likarolo tse kholo, joale palo ea ho bala e tla ba e fokolang, empa molumo o tla ba o phahameng.
Haeba u bala ka likaroloana tse nyenyane, ho fapana le hoo, palo ea ho bala e tla ba e kholo, empa molumo o tla ba o fokolang.
Ka hona, e le ho fokotsa sephethephethe le botle bo akaretsang ba tharollo, ke ile ka tlameha ho hlahisa mofuta o itseng oa tharollo, eo, ka bomalimabe, e shebahalang joaloka crutch.
Ho etsa mohlala, ha re nahaneng ka mokhoa oa ho khoasolla faele ea log ka mefuta e 2 e nolofalitsoeng haholo. Palo ea ho baloa maemong ka bobeli e itšetlehile ka boholo ba karolo.
1) Jala ka likarolo tse nyane:
2) Laela ka likarolo tse kholo:
Joalo ka tloaelo, tharollo e nepahetseng e bohareng.
Boholo ba karolo bo fokola, empa nakong ea ho bala, boholo bo ka eketsoa ho fokotsa palo ea ho bala.
E lokela ho hlokomeloa hore bothata ba ho khetha boholo bo nepahetseng ba karolo e baloang ha bo so rarolloe ka botlalo mme bo hloka boithuto bo tebileng le tlhahlobo. Mohlomong hamorao.
Tlhaloso e akaretsang ea ts'ebetsong
Litafole tsa tšebeletso tse sebelisitsoeng
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 — временная метка последнего загруженного лог-файла в формате YYYY-MM-DD-HH24.
last_aws_nexttoken — текстовая метка последней загруженной порции.
aws_max_item_size- эмпирическим путем, подобранный начальный размер порции.
Mongolo o felletseng oa mongolo
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
Likhechana tsa script tse nang le litlhaloso tse ling:
Litekanyetso tsa ho kenya mongolo:
- Setempe sa nako sa lebitso la faele ea log ka sebopeho sa YYYY-MM-DD-HH24: AWS_LOG_TIME=$1
- Lethathamo la ID: database_id=$2
- Lebitso la faele ea bolokoa e bokelletsoeng: RESULT_FILE=$3
Fumana setempe sa nako sa faele ea ho qetela e kentsoeng:
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 "`Haeba setempe sa nako sa faele ea ho qetela e laetsoeng e sa lumellane le parameter ea ho kenya, faele e ncha ea log e tla kenngoa:
if [[ $current_aws_log_time != $AWS_LOG_TIME ]];
then
is_new_log='1'
if ! 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 "
then
echo '***download_aws_piece.sh -FATAL_ERROR - update database set last_aws_log_time .'
exit 1
fi
else
is_new_log='0'
fi
Re fumana boleng ba label e latelang ho tsoa faeleng e laetsoeng:
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
Letšoao la pheletso ea download ke boleng bo se nang letho ba nexttoken.
Ka loop, re bala likarolo tsa faele, tseleng, re kopanya mela le ho eketsa boholo ba karolo:
Loop e ka Sehloohong
# 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
Ho latela eng?
Kahoo, mosebetsi oa pele oa bohareng - "download file ea log ho tloha leru" e rarolloa. Seo u lokelang ho se etsa ka log e jarollotsoeng?
Pele, o hloka ho hlahlobisisa faele ea log le ho ntša likopo tsa nnete ho eona.
Mosebetsi ha o boima haholo. Bash-script e bonolo ka ho fetisisa e sebetsa hantle.
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
Joale o ka sebetsa le potso e ntšitsoeng faeleng ea log.
'Me ho na le menyetla e mengata ea bohlokoa.
Lipotso tse hlophisitsoeng li tlameha ho bolokoa kae-kae. Bakeng sa sena, ho sebelisoa tafole ea litšebeletso. log_query
CREATE TABLE log_query
(
id SERIAL ,
queryid bigint ,
query_md5hash text not null ,
database_id integer not null ,
timepoint timestamp without time zone not null,
duration double precision not null ,
query text not null ,
explained_plan text[],
plan_md5hash text ,
explained_plan_wo_costs text[],
plan_hash_value text ,
baseline_id integer ,
ip text ,
port text
);
ALTER TABLE log_query ADD PRIMARY KEY (id);
ALTER TABLE log_query ADD CONSTRAINT queryid_timepoint_unique_key UNIQUE (queryid, timepoint );
ALTER TABLE log_query ADD CONSTRAINT query_md5hash_timepoint_unique_key UNIQUE (query_md5hash, timepoint );
CREATE INDEX log_query_timepoint_idx ON log_query (timepoint);
CREATE INDEX log_query_queryid_idx ON log_query (queryid);
ALTER TABLE log_query ADD CONSTRAINT database_id_fk FOREIGN KEY (database_id) REFERENCES database (id) ON DELETE CASCADE ;
Kopo e fetisitsoeng e sebetsoa plgsql mesebetsi "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;
Ha ho sebetsoa, tafole ea litšebeletso e sebelisoa pg_stat_db_queriesE na le senepe sa lipotso tse hlahang tafoleng pg_stat_history (Tšebeliso ea litafole e hlalositsoe mona − )
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 ,
…
);
Ts'ebetso e u lumella ho kenya tšebetsong likarolo tse ngata tsa bohlokoa bakeng sa ho sebetsana le likopo ho tsoa faeleng ea log. E leng:
Monyetla #1 - Nalane ea Phethahatso ea Lipotso
E thusa haholo ho qala ketsahalo ea ts'ebetso. Taba ea pele, itloaetse nalane - mme ho fokotseha ho qalile neng?
Joale, ho ea ka li-classics, sheba lisosa tsa ka ntle. E ka 'na ea e-ba feela hore mojaro oa database o eketsehile haholo' me kopo e khethehileng ha e amane le eona.
Kenya kenyo e ncha tafoleng ea 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
);
Karolo #2 - Boloka Meralo ea Phethahatso ea Lipotso
Tabeng ena, ho ka hlaha maikutlo a ho hanyetsa-tlhaloso: "Empa ho se ho ntse ho na le autoexplain". E, ho joalo, empa ntlha ea bohlokoa ke efe haeba moralo oa ts'ebetso o bolokiloe faeleng e le 'ngoe ea log,' me e le hore u e boloke bakeng sa tlhahlobo e eketsehileng, u tlameha ho phunya faele ea log?
Seo ke neng ke se hloka e ne e le:
pele: boloka moralo oa ts'ebetso tafoleng ea lits'ebeletso ea database ea ho beha leihlo;
ea bobeli: ho khona ho bapisa merero ea ho phethahatsa e mong le e mong e le ho bona hang-hang hore morero oa ho phethahatsa potso o fetohile.
Ho na le kopo e nang le li-parameter tse itseng tsa ts'ebetso. Ho fumana le ho boloka moralo oa eona oa ts'ebetso u sebelisa EXPLAIN ke mosebetsi oa mantlha.
Ho feta moo, ka ho sebelisa tlhaloso ea TLHALOSO (Litšenyehelo tsa BOHATA), u ka fumana moralo oa moralo, o tla sebelisoa ho fumana boleng ba hash ea moralo, e tla thusa tlhahlobisong e latelang ea histori ea phetoho ea moralo oa ts'ebetso.
Fumana template ea moralo oa ts'ebetso
--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');
Monyetla #3 - Ho Sebelisa Lebokose la Lipotso bakeng sa Tlhokomelo
Kaha metrics ea ts'ebetso ha ea lokisoa bakeng sa mongolo oa kopo, empa bakeng sa ID ea eona, o hloka ho amahanya likopo tse tsoang faeleng ea log le likopo tseo metrics ea ts'ebetso e lokiselitsoeng.
Hantle, bonyane e le hore ho be le nako e nepahetseng ea ketsahalo ea ts'ebetso.
Kahoo, ha ketsahalo ea ts'ebetso e etsahala bakeng sa ID ea kopo, ho tla ba le sehokelo sa kopo e ikhethileng e nang le litekanyetso tse ikhethileng tsa paramente le nako e nepahetseng ea ts'ebetso le nako ea kopo. Fumana lintlha tse fanoeng u sebelisa pono feela pg_stat_statements - e thibetsoe.
Fumana potso ea potso 'me u ntlafatse se kentsoeng lethathamong la log_query
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 ;
Afterword
Ka lebaka leo, mokhoa o hlalositsoeng o fumane ts'ebeliso ea ona ho , e u lumellang hore u be le tlhahisoleseling e eketsehileng bakeng sa tlhahlobo ha u rarolla liketsahalo tse hlahang tsa ts'ebetso ea lipotso.
Leha ho le joalo, ka maikutlo a ka, ho tla be ho ntse ho hlokahala ho sebetsa ho algorithm bakeng sa ho khetha le ho fetola boholo ba karolo e jarollotsoeng. Bothata ha bo so rarolloe tabeng e akaretsang. Mohlomong e tla ba monate.
Empa ke pale e fapaneng ka ho felletseng...
Source: www.habr.com