标簽
PostgreSQL , Greenplum , csvlog , gp_toolkit
https://github.com/digoal/blog/blob/master/201808/20180824_01.md#%E8%83%8C%E6%99%AF 背景
由于GP為分布式資料庫，當檢視它的一些日志時，如果到伺服器上檢視，會非常的繁瑣，而且不好排查問題。
例如這是某個節點的日志
2018-08-23 15:34:30.004070 CST,"digoal","postgres",p12775,th-1756479360,"127.0.0.1","52802",2018-08-23 15:34:29 CST,0,con7,cmd4,seg-1,,dx4,,sx1,"LOG","00000","statement: COPY part FROM '/tmp/dss-data/part.csv' WITH csv DELIMITER '|';",,,,,,"COPY part FROM '/tmp/dss-data/part.csv' WITH csv DELIMITER '|';",0,,"postgres.c",1590,
2018-08-23 15:34:30.004098 CST,"digoal","postgres",p12775,th-1756479360,"127.0.0.1","52802",2018-08-23 15:34:29 CST,0,con7,cmd4,seg-1,,dx4,,sx1,"ERROR","25P02","current transaction is aborted, commands ignored until end of transaction block",,,,,,"COPY part FROM '/tmp/dss-data/part.csv' WITH csv DELIMITER '|';",0,,"postgres.c",1669,
2018-08-23 15:34:30.004113 CST,"digoal","postgres",p12775,th-1756479360,"127.0.0.1","52802",2018-08-23 15:34:29 CST,0,con7,cmd4,seg-1,,dx4,,sx1,"LOG","00000","An exception was encountered during the execution of statement: COPY part FROM '/tmp/dss-data/part.csv' WITH csv DELIMITER '|';",,,,,,,0,,,,
Greenplum提供了gp_toolkit.gp_log...視圖，用來彙聚日志，友善檢視。
postgres=# \dv gp_toolkit.gp_log*
List of relations
Schema | Name | Type | Owner | Storage
------------+------------------------+------+--------+---------
gp_toolkit | gp_log_command_timings | view | digoal | none
gp_toolkit | gp_log_database | view | digoal | none
gp_toolkit | gp_log_master_concise | view | digoal | none
gp_toolkit | gp_log_system | view | digoal | none
(4 rows)
https://github.com/digoal/blog/blob/master/201808/20180824_01.md#gp_toolkitgp_log_-%E8%A7%86%E5%9B%BE gp_toolkit.gp_log_* 視圖
- gp_log_command_timings (隻輸出會話，PID，時間，如果關注運作較長時間的詳細資訊，可根據會話，PID在gp_log_system中定位)
This view uses an external table to read the log files on the master and report the execution time of SQL commands executed in a database session.
The use of this view requires superuser permissions.
- gp_log_master_concise (隻有master節點的日志)
This view uses an external table to read a subset of the log fields from the master log file.
The use of this view requires superuser permissions.
- gp_log_system (彙聚master,segment,mirror節點的日志，含所有資料庫)
This view uses an external table to read the server log files of the entire Greenplum system (master, segments, and mirrors) and lists all log entries.
Associated log entries can be identified by the session id (logsession) and command id (logcmdcount).
The use of this view requires superuser permissions.
- gp_log_database (彙聚master,segment,mirror節點的日志，含目前資料庫)
This view uses an external table to read the server log files of the entire Greenplum system (master, segments, and mirrors) and lists log entries associated with the current database.
Associated log entries can be identified by the session id (logsession) and command id (logcmdcount).
The use of this view requires superuser permissions.
https://github.com/digoal/blog/blob/master/201808/20180824_01.md#%E4%BD%BF%E7%94%A8%E4%B8%BE%E4%BE%8B 使用舉例
select * from gp_toolkit.gp_log_database limit 1000;
-[ RECORD 5 ]--+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
logtime | 2018-08-24 02:42:24.084998+08
loguser | digoal
logdatabase | postgres
logpid | p1598
logthread | th1426366592
loghost | 127.0.0.1
logport | 47598
logsessiontime | 2018-08-24 02:41:42+08
logtransaction | 0
logsession | con8
logcmdcount | cmd6
logsegment | seg-1
logslice |
logdistxact | dx8
loglocalxact |
logsubxact | sx1
logseverity | LOG
logstate | 00000
logmessage | statement: SELECT n.nspname as "Schema",
| c.relname as "Name",
| CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
| pg_catalog.pg_get_userbyid(c.relowner) as "Owner", CASE c.relstorage WHEN 'h' THEN 'heap' WHEN 'x' THEN 'external' WHEN 'a' THEN 'append only' WHEN 'v' THEN 'none' WHEN 'c' THEN 'append only columnar' END as "Storage"
|
| FROM pg_catalog.pg_class c
| LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
| WHERE c.relkind IN ('r','')
| AND c.relstorage IN ('h', 'a', 'c','')
| AND n.nspname <> 'pg_catalog'
| AND n.nspname <> 'information_schema'
| AND n.nspname !~ '^pg_toast'
| AND pg_catalog.pg_table_is_visible(c.oid)
| ORDER BY 1,2;
logdetail |
loghint |
logquery |
logquerypos |
logcontext |
logdebug | SELECT n.nspname as "Schema",
| c.relname as "Name",
| CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
| pg_catalog.pg_get_userbyid(c.relowner) as "Owner", CASE c.relstorage WHEN 'h' THEN 'heap' WHEN 'x' THEN 'external' WHEN 'a' THEN 'append only' WHEN 'v' THEN 'none' WHEN 'c' THEN 'append only columnar' END as "Storage"
|
| FROM pg_catalog.pg_class c
| LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
| WHERE c.relkind IN ('r','')
| AND c.relstorage IN ('h', 'a', 'c','')
| AND n.nspname <> 'pg_catalog'
| AND n.nspname <> 'information_schema'
| AND n.nspname !~ '^pg_toast'
| AND pg_catalog.pg_table_is_visible(c.oid)
| ORDER BY 1,2;
logcursorpos | 0
logfunction |
logfile | postgres.c
logline | 1590
logstack |
-[ RECORD 6 ]--+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
logtime | 2018-08-24 02:43:12.031548+08
loguser | postgres
logdatabase | postgres
logpid | p1705
logthread | th1426366592
loghost | [local]
logport |
logsessiontime | 2018-08-24 02:43:12+08
logtransaction | 0
logsession | con9
logcmdcount |
logsegment | seg-1
logslice |
logdistxact |
loglocalxact |
logsubxact | sx1
logseverity | FATAL
logstate | 28000
logmessage | no pg_hba.conf entry for host "[local]", user "postgres", database "postgres", SSL off
logdetail |
loghint |
logquery |
logquerypos |
logcontext |
logdebug |
logcursorpos | 0
logfunction |
logfile | auth.c
logline | 602
logstack |
postgres=# select * from gp_toolkit.gp_log_command_timings order by logsession,logcmdcount limit 100;
logsession | logcmdcount | logdatabase | loguser | logpid | logtimemin | logtimemax | logduration
------------+-------------+-------------+----------+--------+-------------------------------+-------------------------------+-----------------
con10 | cmd1 | postgres | digoal | p14843 | 2018-08-24 05:43:49.156082+08 | 2018-08-24 05:43:49.156082+08 | 00:00:00
con10 | cmd2 | postgres | digoal | p14843 | 2018-08-24 05:43:49.158241+08 | 2018-08-24 05:43:49.158241+08 | 00:00:00
con10 | cmd3 | postgres | digoal | p14843 | 2018-08-24 05:43:51.984189+08 | 2018-08-24 05:43:51.984189+08 | 00:00:00
con10 | cmd4 | postgres | digoal | p14843 | 2018-08-24 05:43:51.987823+08 | 2018-08-24 05:43:51.987823+08 | 00:00:00
con10 | cmd5 | postgres | digoal | p14843 | 2018-08-24 05:43:53.144745+08 | 2018-08-24 05:43:53.159263+08 | 00:00:00.014518
con10 | cmd6 | postgres | digoal | p14843 | 2018-08-24 05:43:56.584268+08 | 2018-08-24 05:43:56.584268+08 | 00:00:00
con10 | cmd7 | postgres | digoal | p14843 | 2018-08-24 05:43:56.586394+08 | 2018-08-24 05:43:56.586394+08 | 00:00:00
con10 | cmd8 | postgres | digoal | p14843 | 2018-08-24 05:43:58.292289+08 | 2018-08-24 05:43:58.294961+08 | 00:00:00.002672
con100 | cmd1 | postgres | postgres | p8206 | 2018-08-24 05:18:03.279438+08 | 2018-08-24 05:18:03.280025+08 | 00:00:00.000587
select * from gp_toolkit.gp_log_master_concise limit 1000;
logtime | 2018-08-24 02:40:34.295103+08
logdatabase | template1
logsession |
logcmdcount | cmd21
logseverity | LOG
logmessage | statement:
| SELECT oid, fsname
| FROM pg_filespace
| ORDER BY fsname;
https://github.com/digoal/blog/blob/master/201808/20180824_01.md#csvlog%E5%AD%97%E6%AE%B5%E5%90%AB%E4%B9%89 csvlog字段含義
對于PostgreSQL來說，csvlog字段含義可以在代碼，或手冊中找到。
1、手冊介紹
https://www.postgresql.org/docs/devel/static/runtime-config-logging.htmlCREATE TABLE postgres_log
(
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text,
PRIMARY KEY (session_id, session_line_num)
);
2、通過file_fdw外部表，PG也可以在SQL中檢視日志
https://www.postgresql.org/docs/devel/static/file-fdw.htmlCREATE EXTENSION file_fdw;
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE pglog (
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text
) SERVER pglog
OPTIONS ( filename '/home/josh/data/log/pglog.csv', format 'csv' );
3、通過源碼檢視csvlog釋義
src/backend/utils/error/elog.c
/*
* Constructs the error message, depending on the Errordata it gets, in a CSV
* format which is described in doc/src/sgml/config.sgml.
*/
static void
write_csvlog(ErrorData *edata)
{
...
https://github.com/digoal/blog/blob/master/201808/20180824_01.md#greenplum-csvlog%E9%87%8A%E4%B9%89 Greenplum CSVLOG釋義
https://greenplum.org/docs/5100/ref_guide/gp_toolkit.html#topic16https://github.com/digoal/blog/blob/master/201808/20180824_01.md#table-11-gp_log_command_timings-view Table 11. gp_log_command_timings view
| Column | Description |
|---|---|
| logsession | The session identifier (prefixed with "con"). |
| logcmdcount | The command number within a session (prefixed with "cmd"). |
| logdatabase | The name of the database. |
| loguser | The name of the database user. |
| logpid | The process id (prefixed with "p"). |
| logtimemin | The time of the first log message for this command. |
| logtimemax | The time of the last log message for this command. |
| logduration | Statement duration from start to end time. |
https://github.com/digoal/blog/blob/master/201808/20180824_01.md#table-13-gp_log_master_concise-view Table 13. gp_log_master_concise view
| logtime | The timestamp of the log message. |
| logmessage | Log or error message text. |
https://github.com/digoal/blog/blob/master/201808/20180824_01.md#table-12-gp_log_database-view Table 12. gp_log_database view
| The associated process id (prefixed with "p"). | |
| logthread | The associated thread count (prefixed with "th"). |
| loghost | The segment or master host name. |
| logport | The segment or master port. |
| logsessiontime | Time session connection was opened. |
| logtransaction | Global transaction id. |
| logsegment | The segment content identifier (prefixed with "seg" for primary or "mir" for mirror. The master always has a content id of -1). |
| logslice | The slice id (portion of the query plan being executed). |
| logdistxact | Distributed transaction id. |
| loglocalxact | Local transaction id. |
| logsubxact | Subtransaction id. |
| logseverity | LOG, ERROR, FATAL, PANIC, DEBUG1 or DEBUG2. |
| logstate | SQL state code associated with the log message. |
| logdetail | Detail message text associated with an error message. |
| loghint | Hint message text associated with an error message. |
| logquery | The internally-generated query text. |
| logquerypos | The cursor index into the internally-generated query text. |
| logcontext | The context in which this message gets generated. |
| logdebug | Query string with full detail for debugging. |
| logcursorpos | The cursor index into the query string. |
| logfunction | The function in which this message is generated. |
| logfile | The log file in which this message is generated. |
| logline | The line in the log file in which this message is generated. |
| logstack | Full text of the stack trace associated with this message. |
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)