PostgreSQL에서 external (foreign) 테이블 생성 방법 및 이를 이용한 fulltime.sh 포팅 방안

PostgreSQL은 file_fdw 익스텐션을 이용해서 external 테이블을 생성할 수 있습니다.

1. file_fdw 익스텐션을 이용한 external 테이블 생성 방법


-- file_fdw 익스텐션을 생성합니다.
test=#  create extension file_fdw;

-- 서버를 생성합니다.
test=# create server srv_file_fdw foreign data wrapper file_fdw;

-- foreign 테이블을 생성합니다.
test=#  create foreign table op_perf_report
(
  overhead      numeric,
  command       varchar(100),
  shared_obj    varchar(100),
  symbol        varchar(100)
)
server srv_file_fdw
options (
    format 'csv',
    header 'true',
    delimiter ',',
    filename '/var/tmp/perf_report.txt');

 

2. external 테이블을 이용한 fulltime.sh 포팅


#!/bin/sh
#
# Name    : fulltime.sh
version=3e
# Purpose : To see PostgreSQL kernel function CPU details TOGETHER.
# Orig    : 17-Oct-2013
# Latest  : 31-Oct-2013
# Modify  : 31-Aug-2016 by Siyeon.Kim <siyeon70@gmail.com>
# Author  : Craig Shallahamer <craig@orapub.com>
#           Frits Hoogland <frits.hoogland@gmail.com>
# Warrenty: There is absolutely no warrenty.
#           Use at your own and your organization's risk.
#           It's all on you, not me!

# Usage   : ./fulltime.sh
#           ./fulltime.sh PID INTERVAL CYCLES
#
# This has been developed on Linux.
# You break out with a cntrl-c

# Here is the general idea:
#
# Help user Identify the PID to profile
# Initial setup
# Loop
#   start PostgreSQL kernel cpu details
#   sleep x
#   stop PostgreSQL kernel cpu gathering
#   do some cool math and other neat stuff
#   display results
# End Loop

# Set the key variables
#
# use this for virtualised hosts:
#PERF_SAMPLE_METHOD='-e cpu-clock'
# use this for physical hosts:
PERF_SAMPLE_METHOD='-e cycles'

refresh_time=3
samples_remaining=999 # this is useful for longer single samples

workdir=$PWD
perf_file=perf_report.txt

clear

# if problems with perf
echo "If unable to execute perf, do as root:"
echo "   echo 0 > /proc/sys/kernel/perf_event_paranoid"

# perf sample method
echo ""
echo "The perf sample method is set to: $PERF_SAMPLE_METHOD"
echo "Use cpu-clock for virtualised hosts, cycles for physical hosts"

###
# ctrl_c routine
###
ctrl_c() {
        PGPASSWORD=oracle psql test -p 5433 -Upostgres <<eof0>& /dev/null
        drop foreign table op_perf_report;
        drop table op_timing;
EOF0

        echo ""
        echo ""
        echo "Thanks for using FULLTIME v${version}!"
        echo ""
        echo "To see the latest Call Graph, press ENTER or to exit press CNTRL-C."
        echo ""
        echo "The Call Graph file is callgraph.txt"
        read x
        perf report -g -i callgraph.pdata > callgraph.txt 2>/dev/null
        echo ""
        more callgraph.txt
        exit
}

###
# help_find_pid routine
#
# Let's help the user identifiy the Oracle session of interest
#
###
help_find_pid() {
PGPASSWORD=oracle psql test -p 5433 -Upostgres <<eof1 main="" select="" *perf="" profile*="" pid,="" substr(query,1,70)="" query="" from="" pg_stat_activity;="" eof1="" read="" "enter="" pid="" to="" profile="" }="" all="" below="" routine="" ###="" trap="" ctrl_c="" sigint="" [="" "$#"="" -eq="" ];="" then="" help_find_pid;="" else="" ospid="" refresh_time="$2" samples_remaining="$3" "command="" line="" arguments="" are:"="" "="" "ospid="" $ospid"="" "delay="" $refresh_time"="" "cycles="" $samples_remaining"="" fi="" echo="" ""="" the="" process="" is="" not="" there,="" exit="" #="" if="" !="" ps="" -p="" $ospid="" 0="">/dev/null; then ctrl_c; fi

PGPASSWORD=oracle psql test -p 5433 -Upostgres <<eof2>& /dev/null

drop foreign table op_perf_report;

create foreign table op_perf_report (
  overhead      numeric,
  command       varchar(100),
  shared_obj    varchar(100),
  symbol        varchar(100)
)
server srv_file_fdw
options (
    format 'csv',
    header 'true',
    delimiter ',',
    filename '/var/tmp/perf_report.txt'
);

EOF2

while [ $samples_remaining  -gt 0 ]
do
        echo "Gathering next $refresh_time second sample..."

        if ! ps -p $ospid >/dev/null; then ctrl_c; fi

        perf record -f $PERF_SAMPLE_METHOD -p $ospid sleep $refresh_time >& /dev/null &
        perf record -f $PERF_SAMPLE_METHOD -g -o callgraph.pdata -p $ospid sleep $refresh_time >& /dev/null   &
        wait

        if ! ps -p $ospid >/dev/null; then ctrl_c; fi

        perf report -t, > z ; cat z | grep -v '#' | sed '/^\s*$/d' > /var/tmp/perf_report.txt 2>/dev/null

        if ! ps -p $ospid >/dev/null; then ctrl_c; fi

        clear

        echo "Fulltime.sh v$version"

        PGPASSWORD=oracle psql test -p 5433 -Upostgres <<eof5 select="" query="" from="" pid="$ospid;" the="" is="" header="" '||pid||'="" '||usename||'="" '||current_timestamp-query_start="" as="" "basic_info",="" 'current="" '||substr(query,1,70)="" "query"="" pg_stat_activity="" this="" screen="" core="" output="" --="" 'cpu="" '||rpt.symbol="" item,="" overhead="" op_perf_report="" rpt="" where="" rpt.overhead=""> 2.0;

EOF5
        echo ""
        echo "To see the Call Graph, press ENTER or to exit press CNTRL-C."
        samples_remaining=`echo "$samples_remaining-1" | bc`

        echo "Samples remaining: $samples_remaining"

done

# If NO command line options, then prompt for call graph, else just exit.
#
if [ "$#" -eq 0 ]; then
        ctrl_c
fi

 

이전 포스팅에서 fulltime.sh (perf 명령어를 이용해서 오라클 Kernel function을 분석하는 쉘) 스크립트를 소개한바 있습니다. 해당 방법의 골자는 perf 명령어 수행결과 파일을 external 테이블로 지정한 후, 해당 테이블을 조회하는 것입니다. 따라서 fulltime.sh를 PostgreSQL용으로 포팅하기 위해서는 external 테이블을 이용해야 합니다. 단, PostgreSQL은 세션 레벨의 CPU Time 정보 (오라클의 v$sess_time_model)을 제공하지 않으므로 OS 레벨의 CPU 사용 정보만을 제공한다는 제약사항이 있습니다. 아래의 소스 중에서 눈 여겨 볼 것은 2가지 정도입니다.

1) foreign 테이블 생성을 위한 약간의 조작
foreign 테이블용으로 생성되는 파일 (/var/tmp/perf_report.txt)에는 주석(#) 및 공백 라인이 있을 경우에는 에러가 발생합니다. 따라서 다음과 같이 grep과 sed 명령어를 이용해서 해당 라인들을 제거합니다.
perf report -t, > z ; cat z | grep -v '#' | sed '/^\s*$/d' > /var/tmp/perf_report.txt 2>/dev/null
2) 패스워드 입력 없이 psql에 로그인 하는 방법
PGPASSWORD=oracle psql test -p 5433 -Upostgres <<EOF1

PostgreSQL용으로 포팅된 fulltime.sh 소스는 다음과 같습니다.
Source: PostgreSQL용 fulltime.sh

3. fulltime.sh 사용 예제


-- 아규먼트 없이 fulltime.sh를 수행하면 현재 수행하는 모든 프로세스 목록이 출력됩니다.
[root@pgserver ~]# ./fulltime.sh

  pid  |  query
-------+------------------------------------------
 32572 | select loop_insert_t1(10000000);
 32598 | INSERT INTO t1 SELECT '3199470'
   427 | INSERT INTO t1 SELECT '3216064'
 32751 | select loop_insert_t1(100000000);
   521 | select loop_insert_t1(100000000);
   522 | INSERT INTO t1 SELECT '3067927'
   669 | select loop_insert_t1(100000000);
   670 | INSERT INTO t1 SELECT '3064539'
   773 | select loop_insert_t1(100000000);
   774 | INSERT INTO t1 SELECT '3072957'
   868 | select loop_insert_t1(100000000);
   870 | INSERT INTO t1 SELECT '3092238'
  1138 | select loop_insert_t1(100000000);
  1139 | INSERT INTO t1 SELECT '3013273'
  1248 | select loop_insert_t1(100000000);
  1249 | INSERT INTO t1 SELECT '3015102'
  1341 | select loop_insert_t1(100000000);
  1342 | INSERT INTO t1 SELECT '3002411'
  1433 | select loop_insert_t1(100000000);
  1435 | INSERT INTO t1 SELECT '2992595'

-- PID를 입력하면, 해당 프로세스의 기본 정보 및 Kernel function 사용 내역을 확인할 수 있습니다.
Enter PID to profile : 32598

Fulltime.sh v3e
                          BASIC_INFO                            |                    QUERY
----------------------------------------------------------------+---------------------------------------------
 PID: 32598 USERNAME: postgres QUERY TIME(Sec): 00:00:00.000373 | CURRENT SQL: INSERT INTO t1 SELECT '101134'

                   item                    | overhead
-------------------------------------------+----------
 cpu : [k] _raw_spin_unlock_irqrestore     |    20.07
 cpu : [k] __do_softirq                    |     9.67
 cpu : [.] SearchCatCache                  |     2.60
(3 rows)

-- CTRL+C 이후에 엔터를 입력하면 Kernel Function Call Tree를 확인할 수 있습니다.
To see the Call Graph, press ENTER or to exit press CNTRL-C.
Samples remaining: 990
Gathering next 3 second sample...

# ========
# captured on: Tue Aug 30 12:43:14 2016
# hostname : pgserver
# os release : 3.8.13-35.3.1.el7uek.x86_64
# perf version : 3.8.13-35.3.1.el7uek.x86_64
# arch : x86_64
# nrcpus online : 2
# nrcpus avail : 2
# cpudesc : Intel(R) Core(TM) i7-6500U CPU @ 2.50GHz
# cpuid : GenuineIntel,6,78,3
# total memory : 3785700 kB
# cmdline : /usr/libexec/perf.3.8.13-35.3.1.el7uek.x86_64 record -f -e cycles -g -o callgraph.pdata -p 32572 sleep 3
# event : name = cpu-clock, type = 1, config = 0x0, config1 = 0x0, config2 = 0x0, excl_usr = 0, excl_kern = 0, excl_h
ost = 0, excl_guest = 1, precise_ip = 0, id = { 456 }
# HEADER_CPU_TOPOLOGY info available, use -I to display
# HEADER_NUMA_TOPOLOGY info available, use -I to display
# pmu mappings: software = 1, tracepoint = 2, breakpoint = 5
# ========
#
# Samples: 80  of event 'cpu-clock'
# Event count (approx.): 80
#
# Overhead   Command      Shared Object                           Symbol
# ........  ........  .................  ...............................
#
    21.25%  postgres  [kernel.kallsyms]  [k] finish_task_switch
            |
            --- finish_task_switch
                __schedule
                schedule
                schedule_hrtimeout_range_clock
                schedule_hrtimeout_range
                poll_schedule_timeout
                do_sys_poll
                sys_poll
                system_call_fastpath
                __poll_nocancel

    18.75%  postgres  [kernel.kallsyms]  [k] _raw_spin_unlock_irqrestore
            |
            --- _raw_spin_unlock_irqrestore
               |
               |--86.67%-- __wake_up_sync_key
               |          |
               |          |--61.54%-- pipe_write
               |          |          do_aio_write
               |          |          do_sync_write
               |          |          vfs_write
               |          |          sys_write
               |          |          system_call_fastpath
               |          |          __GI___libc_write
               |          |          0x2065726568742020
               |          |
               |           --38.46%-- sock_def_readable
               |                     unix_stream_sendmsg
               |                     sock_sendmsg

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s