A i ʻole kahi wahi o ka tetrisology i hoʻohana ʻia.
Poina loa nā mea hou a pau.
Epigraphs.
Ka hoʻokumu ʻana i ka pilikia
Pono e hoʻoiho i ka faila log PostgreSQL i kēia manawa mai ke ao AWS i ka host Linux kūloko. ʻAʻole i ka manawa maoli, akā, e ʻōlelo mākou, me ka lohi iki.
ʻO 5 mau minuke ka lōʻihi o ka hoʻoiho ʻana i ka faila log.
Hoʻololi ʻia ka faila log, ma AWS, i kēlā me kēia hola.
Nā mea hana i hoʻohana ʻia
No ka hoʻouka ʻana i ka faila log i ka mea hoʻokipa, hoʻohana ʻia kahi palapala bash e kāhea ana i ka AWS API "
Nā Pālākiō:
- --db-instance-identifier: Instance inoa ma AWS;
- --log-file-name: inoa o ka waihona log i hana ʻia i kēia manawa
- --max-item: ʻO ka huina o nā mea i hoʻihoʻi ʻia ma ka puka o ke kauoha.Ka nui o ka ʻāpana o ka faila i hoʻoiho ʻia.
- --hoʻomaka-token: Hoʻomaka hōʻailona hōʻailona
ʻAe, a maʻalahi - he hana hoihoi no ke aʻo ʻana a me nā ʻano like ʻole i nā hola hana.
Manaʻo wau ua hoʻoholo ʻia ka pilikia ma muli o ka hana maʻamau. Akā ʻaʻole ʻōlelo ʻo Google wikiwiki i nā hoʻonā, a ʻaʻohe makemake e ʻimi i ka hohonu. ʻO kēlā me kēia hihia, he hoʻomaʻamaʻa maikaʻi.
Hoʻomaʻamaʻa i ka hana
ʻO ka faila log hope he pūʻulu laina o ka lōʻihi like ʻole. Ma ke kiʻi, hiki ke hōʻike ʻia ka faila log e like me kēia:
Ke hoʻomanaʻo nei paha ia iā ʻoe i kekahi mea? He aha ka "tetris"? A eia ka mea.
Inā mākou e hōʻike i nā koho e hiki mai ana i ka hoʻouka ʻana i ka faila aʻe ma ke kiʻi (no ka maʻalahi, i kēia hihia, e like ka lōʻihi o nā laina), loaʻa mākou nā helu tetris maʻamau:
1) Hoʻoiho ʻia ka faila a pau loa. ʻOi aku ka nui o ka puʻupuʻu ma mua o ka nui o ka faila hope:
2) He hoʻomau ka waihona. ʻOi aku ka liʻiliʻi o ka ʻāpana ma mua o ka nui o ka faila hope:
3) He hoʻomau ka waihona o ka faila mua a he hoʻomau. ʻOi aku ka nui o ka puʻupuʻu ma mua o ka nui o ke koena o ka faila hope:
4) He hoʻomau ka faila i ka faila mua a ʻo ka hope loa. ʻOi aku ka nui o ka puʻupuʻu ma mua o ka nui o ke koena o ka faila hope:
ʻO ka hana e hōʻuluʻulu i kahi rectangle a pāʻani paha iā Tetris ma kahi pae hou.
Nā pilikia e kū mai ana i ka wā e hoʻoponopono ai i ka pilikia
1) Hoʻopili i kahi kaula o 2 ʻāpana
Ma keʻano laulā,ʻaʻohe pilikia kūikawā. He hana maʻamau mai ka papa papahana mua.
ʻO ka nui o ka lawelawe ʻana
Akā, he mea hoihoi iki kēia.
ʻO ka mea pōʻino, ʻaʻohe ala e hoʻohana ai i kahi offset ma hope o ka lepili chunk hoʻomaka:
E like me kāu i ʻike ai i ke koho - hoʻomaka-token hoʻohana ʻia e kuhikuhi i kahi e hoʻomaka ai i ka paginating. Lawe kēia koho i nā waiwai String ʻo ia hoʻi inā e hoʻāʻo ʻoe e hoʻohui i kahi waiwai offset i mua o ke kaula Next Token, ʻaʻole e noʻonoʻo ʻia ke koho ma ke ʻano he offset.
A no laila, pono ʻoe e heluhelu i nā ʻāpana ʻāpana.
Inā heluhelu ʻoe i nā ʻāpana nui, a laila liʻiliʻi ka helu o nā heluhelu, akā ʻoi aku ka nui o ka leo.
Inā heluhelu ʻoe i nā ʻāpana liʻiliʻi, a laila ʻoi aku ka nui o ka helu o nā heluhelu, akā e liʻiliʻi ka nui.
No laila, i mea e hōʻemi ai i ke kaʻa a no ka nani o ka hoʻonā, pono wau e hana i kekahi ʻano o ka hopena, ʻo ia hoʻi, ʻo ka mea pōʻino, he ʻano like me ke koʻokoʻo.
No ka hoʻohālikelike, e noʻonoʻo kākou i ke kaʻina hana o ka hoʻoiho ʻana i kahi faila log ma 2 mau mana maʻalahi loa. ʻO ka helu o nā heluhelu ma nā hihia ʻelua e pili ana i ka nui o ka ʻāpana.
1) Hoʻouka i nā ʻāpana liʻiliʻi:
2) Hoʻouka i nā ʻāpana nui:
E like me ka mea maʻamau, aia ka hopena maikaʻi loa ma ka waena.
He liʻiliʻi ka nui o ka ʻāpana, akā i ke kaʻina o ka heluhelu ʻana, hiki ke hoʻonui ʻia ka nui e hōʻemi i ka helu o nā heluhelu.
Pono e hoʻomaopopo ʻaʻole i hoʻopau piha ʻia ka pilikia o ke koho ʻana i ka nui maikaʻi loa o ka ʻāpana heluhelu a pono e noiʻi hohonu a me ka nānā ʻana. Ma hope iki paha.
ʻO ka wehewehe nui o ka hoʻokō
Nā papa lawelawe i hoʻohana ʻia
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- эмпирическим путем, подобранный начальный размер порции.
kikokiko piha o ka palapala
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
Nā ʻāpana palapala me kekahi mau wehewehe:
Nā ʻāpana hoʻokomo kikokikona:
- Paʻa manawa o ka inoa waihona moʻolelo ma YYYY-MM-DD-HH24 format: AWS_LOG_TIME=$1
- ID waihona: database_id=$2
- ʻOhi ʻia ka inoa waihona waihona: RESULT_FILE=$3
E kiʻi i ka hōʻailona manawa o ka waihona moʻolelo i hoʻoili ʻia:
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 "`
Inā ʻaʻole i kūlike ke kaha manawa o ka faila i hoʻouka ʻia me ka ʻāpana hoʻokomo, hoʻouka ʻia kahi faila log hou:
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
Loaʻa iā mākou ka waiwai o ka lepili nexttoken mai ka faila i hoʻouka ʻia:
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
ʻO ka hōʻailona o ka hopena o ka hoʻoiho ʻana ʻo ia ka waiwai ʻole o nexttoken.
Ma kahi loop, helu mākou i nā ʻāpana o ka faila, ma ke ala, e hui pū i nā laina a hoʻonui i ka nui o ka ʻāpana:
Loop Nui
# 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
He aha ka hope?
No laila, ua hoʻoholo ʻia ka hana waena mua - "hoʻoiho i ka faila log mai ke ao". He aha ka hana me ka log download?
Pono ʻoe e hoʻokaʻawale i ka faila log a wehe i nā noi maoli mai ia mea.
ʻAʻole paʻakikī loa ka hana. Maikaʻi ka maʻalahi bash-script.
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
I kēia manawa hiki iā ʻoe ke hana me ka nīnau i unuhi ʻia mai ka faila log.
A he nui nā mea kūpono.
Pono e mālama ʻia nā nīnau i ʻōlelo ʻia ma kekahi wahi. No kēia, hoʻohana ʻia kahi papa lawelawe. 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 ;
Hoʻohana ʻia ka noi i ʻōlelo ʻia plpgsql hana "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;
I ka hana ʻana, hoʻohana ʻia ka papaʻaina lawelawe pg_stat_db_queriesHe kiʻi paʻi o nā nīnau i kēia manawa mai ka papaʻaina pg_stat_history (Hōʻike ʻia ka hoʻohana papa ʻaina ma ʻaneʻi −
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 ,
…
);
Hāʻawi ka hana iā ʻoe e hoʻokō i kekahi mau hiʻohiʻona pono no ka hoʻoili ʻana i nā noi mai kahi faila log. ʻO ia hoʻi:
Manawa #1 - Moʻolelo Hoʻokō Nīnau
Pono loa no ka hoʻomaka ʻana i kahi hanana hana. ʻO ka mua, e kamaʻāina me ka mōʻaukala - a i ka manawa hea i hoʻomaka ai ka lohi?
A laila, e like me ka poʻe kahiko, e ʻimi i nā kumu o waho. Malia paha ua hoʻonui nui ʻia ka ukana waihona a ʻaʻohe mea pili i ka noi kikoʻī.
Hoʻohui hou i ka papa inoa 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
);
Hiʻona #2 - E mālama i nā hoʻolālā hoʻokō nīnau
I kēia manawa, hiki ke kū mai kahi manaʻo kū'ē-hōʻike: "Akā ua loaʻa ka autoexplain". ʻAe, ʻo ia, akā he aha ka manaʻo inā mālama ʻia ka hoʻolālā hoʻokō i loko o ka faila log like a i mea e mālama ai no ka nānā hou ʻana, pono ʻoe e parse i ka faila log?
Eia naʻe, pono iaʻu:
mua: mālama i ka hoʻolālā hoʻokō ma ka papaʻaina lawelawe o ka ʻikepili nānā;
ʻO ka lua: hiki ke hoʻohālikelike i nā hoʻolālā hoʻokō me kekahi i mea e ʻike koke ai ua loli ka hoʻolālā hoʻokō nīnau.
Loaʻa kahi noi me nā ʻāpana hoʻokō kikoʻī. ʻO ka loaʻa ʻana a me ka mālama ʻana i kāna hoʻolālā hoʻokō me ka hoʻohana ʻana iā EXPLAIN he hana haʻahaʻa.
Eia kekahi, me ka hoʻohana ʻana i ka ʻōlelo EXPLAIN (COSTS FALSE), hiki iā ʻoe ke kiʻi i ke ʻano o ka hoʻolālā, e hoʻohana ʻia e kiʻi i ka waiwai hash o ka hoʻolālā, e kōkua i ka nānā ʻana o ka mōʻaukala hoʻololi o ka hoʻolālā hoʻokō.
E kiʻi i kahi hoʻolālā hoʻokō
--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');
Loaʻa #3 - Ke hoʻohana nei i ka Log Query no ka nānā ʻana
No ka mea, ʻaʻole i hoʻonohonoho ʻia nā ana hana no ka kikokikona noi, akā no kāna ID, pono ʻoe e hoʻohui i nā noi mai ka faila log me nā noi i hoʻonohonoho ʻia nā ana hana.
ʻAe, ma ka liʻiliʻi i mea e loaʻa ai ka manawa kūpono o kahi hanana hana.
No laila, i ka wā e loaʻa ai kahi hanana hana no kahi ID noi, e loaʻa kahi kuhikuhi i kahi noi kikoʻī me nā koina kikoʻī kikoʻī a me ka manawa hoʻokō pololei a me ka lōʻihi o ka noi. E kiʻi i ka ʻike i hāʻawi ʻia me ka hoʻohana wale ʻana i ka ʻike pg_stat_statements - ua kapu.
E huli i ka queryid o ka hulina a hoʻololi i ke komo ʻana ma ka papa 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 ;
Ma hope o ka'ōlelo
ʻO ka hopena, ua ʻike ʻia ke ʻano i wehewehe ʻia i kona hoʻohana ʻana ma
ʻOiai, ʻoiaʻiʻo, i koʻu manaʻo pilikino, pono nō e hana i ka algorithm no ke koho ʻana a hoʻololi i ka nui o ka ʻāpana i hoʻoiho ʻia. ʻAʻole i hoʻoholo ʻia ka pilikia ma ka hihia maʻamau. E hoihoi paha ia.
Akā, he moʻolelo ʻokoʻa loa kēlā...
Source: www.habr.com