หรือเทตริวิทยาประยุกต์เล็กน้อย
ทุกสิ่งใหม่นั้นเก่าลืมไปแล้ว
Epigraphs
คำแถลงปัญหา
จำเป็นต้องดาวน์โหลดไฟล์บันทึก PostgreSQL ปัจจุบันเป็นระยะๆ จากระบบคลาวด์ AWS ไปยังโฮสต์ Linux ในเครื่อง ไม่ใช่ตามเวลาจริง แต่เราจะบอกว่ามีความล่าช้าเล็กน้อย
ระยะเวลาการดาวน์โหลดการอัปเดตไฟล์บันทึกคือ 5 นาที
ไฟล์บันทึกใน AWS จะมีการหมุนเวียนทุกๆ ชั่วโมง
เครื่องมือใช้แล้ว
ในการอัปโหลดไฟล์บันทึกไปยังโฮสต์ จะใช้สคริปต์ทุบตีที่เรียก AWS API "'
พารามิเตอร์:
- --db-instance-identifier: ชื่ออินสแตนซ์ใน AWS;
- --log-file-name: ชื่อของไฟล์บันทึกที่สร้างขึ้นในปัจจุบัน
- --max-item: จำนวนรายการทั้งหมดที่ส่งคืนในเอาต์พุตของคำสั่งขนาดก้อนของไฟล์ที่ดาวน์โหลด
- --starting-token: โทเค็นการเริ่มต้นโทเค็น
ในกรณีนี้ งานในการดาวน์โหลดบันทึกเกิดขึ้นในระหว่างการทำงาน
ใช่และเรียบง่าย - เป็นงานที่น่าสนใจสำหรับการฝึกอบรมและความหลากหลายในช่วงเวลาทำงาน
ฉันคิดว่าปัญหาได้รับการแก้ไขแล้วโดยอาศัยกิจวัตรประจำวัน แต่ Google อย่างรวดเร็วไม่ได้แนะนำวิธีแก้ไข และไม่มีความปรารถนาที่จะค้นหาในเชิงลึกมากกว่านี้ ไม่ว่าในกรณีใดมันเป็นการออกกำลังกายที่ดี
พิธีการของงาน
ไฟล์บันทึกสุดท้ายคือชุดของบรรทัดที่มีความยาวผันแปรได้ ในทางกราฟิก ไฟล์บันทึกสามารถแสดงได้ดังนี้:
มันเตือนคุณถึงบางสิ่งแล้วหรือยัง? เกี่ยวอะไรกับ "tetris"? และนี่คือสิ่งที่
หากเราแสดงตัวเลือกที่เป็นไปได้ที่เกิดขึ้นเมื่อโหลดไฟล์ถัดไปแบบกราฟิก (เพื่อความง่าย ในกรณีนี้ ให้บรรทัดมีความยาวเท่ากัน) เราจะได้ ตัวเลข tetris มาตรฐาน:
1) ไฟล์จะถูกดาวน์โหลดทั้งหมดและถือเป็นที่สิ้นสุด ขนาดก้อนใหญ่กว่าขนาดไฟล์สุดท้าย:
2) ไฟล์มีความต่อเนื่อง ขนาดก้อนเล็กกว่าขนาดไฟล์สุดท้าย:
3) ไฟล์นี้เป็นไฟล์ต่อเนื่องจากไฟล์ก่อนหน้าและมีความต่อเนื่อง ขนาดอันเล็กกว่าขนาดของส่วนที่เหลือของไฟล์สุดท้าย:
4) ไฟล์นี้เป็นไฟล์ต่อเนื่องจากไฟล์ก่อนหน้าและถือเป็นที่สิ้นสุด ขนาดก้อนใหญ่กว่าขนาดของส่วนที่เหลือของไฟล์สุดท้าย:
ภารกิจคือการประกอบสี่เหลี่ยมผืนผ้าหรือเล่น Tetris ในระดับใหม่
ปัญหาที่เกิดขึ้นระหว่างการแก้ปัญหา
1) กาวสตริง 2 ส่วน
โดยทั่วไปไม่มีปัญหาพิเศษ งานมาตรฐานจากหลักสูตรการเขียนโปรแกรมเบื้องต้น
ขนาดที่ให้บริการที่เหมาะสมที่สุด
แต่นี่น่าสนใจกว่าเล็กน้อย
น่าเสียดาย ไม่มีวิธีใดที่จะใช้การชดเชยหลังจากป้ายกำกับกลุ่มเริ่มต้น:
ดังที่คุณทราบแล้วว่าตัวเลือก --starting-token ใช้เพื่อระบุตำแหน่งที่จะเริ่มแบ่งหน้า ตัวเลือกนี้ใช้ค่าสตริงซึ่งหมายความว่าหากคุณพยายามเพิ่มค่าออฟเซ็ตหน้าสตริงโทเค็นถัดไป ตัวเลือกจะไม่ถือเป็นออฟเซ็ต
ดังนั้น คุณต้องอ่านเป็นส่วนๆ
หากคุณอ่านเป็นส่วนใหญ่ จำนวนการอ่านจะน้อยที่สุด แต่ปริมาณจะสูงสุด
หากคุณอ่านเป็นส่วนเล็ก ๆ จำนวนการอ่านจะสูงสุด แต่ปริมาณจะน้อยที่สุด
ดังนั้น เพื่อลดการจราจรและเพื่อความสวยงามโดยรวมของโซลูชัน ฉันจึงต้องคิดวิธีแก้ปัญหาบางอย่าง ซึ่งน่าเสียดายที่มันดูเหมือนไม้ค้ำ
เพื่ออธิบาย ลองพิจารณาขั้นตอนการดาวน์โหลดไฟล์บันทึกใน 2 เวอร์ชันที่ง่ายมาก จำนวนการอ่านในทั้งสองกรณีขึ้นอยู่กับขนาดของส่วน
1) โหลดเป็นส่วนเล็กๆ:
2) โหลดเป็นส่วนใหญ่:
ตามปกติ ทางออกที่ดีที่สุดจะอยู่ตรงกลาง.
ขนาดส่วนมีขนาดเล็ก แต่ในกระบวนการอ่าน ขนาดสามารถเพิ่มได้เพื่อลดจำนวนการอ่าน
ควรบันทึก ปัญหาในการเลือกขนาดที่เหมาะสมของส่วนการอ่านยังไม่ได้รับการแก้ไขอย่างสมบูรณ์ และต้องมีการศึกษาและวิเคราะห์อย่างลึกซึ้งยิ่งขึ้น อาจจะช้ากว่านี้เล็กน้อย
คำอธิบายทั่วไปของการใช้งาน
ตารางใช้บริการ
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- эмпирическим путем, подобранный начальный размер порции.
ข้อความทั้งหมดของสคริปต์
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
ส่วนของสคริปต์พร้อมคำอธิบาย:
พารามิเตอร์อินพุตสคริปต์:
- การประทับเวลาของชื่อไฟล์บันทึกในรูปแบบ YYYY-MM-DD-HH24: AWS_LOG_TIME=$1
- รหัสฐานข้อมูล: database_id=$2
- ชื่อไฟล์บันทึกที่รวบรวม: RESULT_FILE=$3
รับการประทับเวลาของไฟล์บันทึกที่อัปโหลดล่าสุด:
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 "`หากการประทับเวลาของไฟล์บันทึกที่โหลดล่าสุดไม่ตรงกับพารามิเตอร์อินพุต ระบบจะโหลดไฟล์บันทึกใหม่:
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
เราได้รับค่าของฉลาก nexttoken จากไฟล์ที่โหลด:
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
สัญญาณของการสิ้นสุดการดาวน์โหลดคือค่าว่างของ nexttoken
ในลูป เรานับส่วนต่างๆ ของไฟล์ ระหว่างทาง เชื่อมบรรทัดและเพิ่มขนาดของส่วน:
ลูปหลัก
# 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
อะไรต่อไป?
ดังนั้นงานขั้นกลางแรก - "ดาวน์โหลดไฟล์บันทึกจากคลาวด์" จึงได้รับการแก้ไข จะทำอย่างไรกับบันทึกที่ดาวน์โหลด
ก่อนอื่นคุณต้องแยกวิเคราะห์ไฟล์บันทึกและแยกคำขอจริงออกจากไฟล์
งานไม่ยากมาก สคริปต์ทุบตีที่ง่ายที่สุดทำได้ดี
อัพโหลด_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
ตอนนี้ คุณสามารถทำงานกับแบบสอบถามที่ดึงมาจากไฟล์บันทึก
และมีความเป็นไปได้ที่เป็นประโยชน์หลายประการ
แบบสอบถามแยกวิเคราะห์จะต้องเก็บไว้ที่ใดที่หนึ่ง สำหรับสิ่งนี้จะใช้ตารางบริการ 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 ;
คำขอแยกวิเคราะห์ได้รับการประมวลผลใน กรุณา ฟังก์ชั่น "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;
เมื่อประมวลผลจะใช้ตารางบริการ pg_stat_db_queriesที่มีภาพรวมของแบบสอบถามปัจจุบันจากตาราง pg_stat_history (อธิบายการใช้งานตารางไว้ที่นี่ - )
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 ,
…
);
ฟังก์ชันนี้อนุญาตให้คุณใช้คุณสมบัติที่มีประโยชน์มากมายสำหรับการประมวลผลคำขอจากไฟล์บันทึก คือ:
โอกาส #1 - ประวัติการดำเนินการสืบค้น
มีประโยชน์มากสำหรับการเริ่มต้นเหตุการณ์ประสิทธิภาพ ขั้นแรก ทำความคุ้นเคยกับประวัติศาสตร์ - และการชะลอตัวเริ่มต้นเมื่อใด
จากนั้นตามคลาสสิกให้มองหาสาเหตุภายนอก อาจเป็นเพราะโหลดฐานข้อมูลเพิ่มขึ้นอย่างมากและคำขอเฉพาะไม่มีส่วนเกี่ยวข้องกับมัน
เพิ่มรายการใหม่ในตาราง 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
);
คุณลักษณะ #2 - บันทึกแผนดำเนินการแบบสอบถาม
ณ จุดนี้ อาจมีการคัดค้าน-ชี้แจง-แสดงความคิดเห็น: “แต่มีคำอธิบายอัตโนมัติอยู่แล้ว". ใช่ จริงอยู่ แต่จะมีประโยชน์อะไรหากแผนปฏิบัติการถูกจัดเก็บไว้ในล็อกไฟล์เดียวกัน และเพื่อบันทึกเพื่อการวิเคราะห์เพิ่มเติม คุณต้องแยกวิเคราะห์ล็อกไฟล์
อย่างไรก็ตาม ฉันต้องการ:
อันดับแรก: จัดเก็บแผนการดำเนินการในตารางบริการของฐานข้อมูลการตรวจสอบ
ประการที่สอง: เพื่อให้สามารถเปรียบเทียบแผนการดำเนินการกับแต่ละอื่น ๆ เพื่อดูได้ทันทีว่ามีการเปลี่ยนแปลงแผนการดำเนินการแบบสอบถาม
มีคำขอพร้อมพารามิเตอร์การดำเนินการเฉพาะ การรับและจัดเก็บแผนการดำเนินการโดยใช้ EXPLAIN เป็นงานพื้นฐาน
ยิ่งไปกว่านั้น เมื่อใช้นิพจน์ EXPLAIN (COSTS FALSE) คุณจะได้รับกรอบงานของแผน ซึ่งจะใช้เพื่อรับค่าแฮชของแผน ซึ่งจะช่วยในการวิเคราะห์ประวัติการเปลี่ยนแปลงของแผนการดำเนินการในภายหลัง
รับเทมเพลตแผนการดำเนินการ
--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');
โอกาส #3 - การใช้บันทึกแบบสอบถามสำหรับการตรวจสอบ
เนื่องจากเมตริกประสิทธิภาพไม่ได้กำหนดค่าสำหรับข้อความคำขอ แต่สำหรับ ID คุณต้องเชื่อมโยงคำขอจากไฟล์บันทึกกับคำขอที่มีการกำหนดค่าเมตริกประสิทธิภาพ
อย่างน้อยก็เพื่อให้มีเวลาที่แน่นอนในการเกิดเหตุการณ์ประสิทธิภาพ
ดังนั้น เมื่อเกิดปัญหาด้านประสิทธิภาพสำหรับ ID คำขอ จะมีการอ้างอิงไปยังคำขอเฉพาะที่มีค่าพารามิเตอร์เฉพาะ และเวลาการดำเนินการและระยะเวลาที่แน่นอนของคำขอ รับข้อมูลที่กำหนดโดยใช้มุมมองเท่านั้น pg_stat_statements - เป็นสิ่งต้องห้าม
ค้นหารหัสแบบสอบถามของแบบสอบถามและอัปเดตรายการในตาราง 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 ;
เล่ม
เป็นผลให้วิธีการที่อธิบายได้พบการใช้งานใน ช่วยให้คุณมีข้อมูลเพิ่มเติมสำหรับการวิเคราะห์เมื่อแก้ไขเหตุการณ์ประสิทธิภาพการสืบค้นที่เกิดขึ้นใหม่
แม้ว่าในความเห็นส่วนตัวของฉันจะยังคงจำเป็นต้องทำงานกับอัลกอริทึมสำหรับการเลือกและเปลี่ยนขนาดของส่วนที่ดาวน์โหลด ปัญหายังไม่ได้รับการแก้ไขในกรณีทั่วไป มันอาจจะน่าสนใจ
แต่นั่นเป็นเรื่องราวที่แตกต่างไปจากเดิมอย่างสิ้นเชิง...
ที่มา: will.com