建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
分析PostgreSQL日志相關(guān)的配置參數(shù)log_XXX-創(chuàng)新互聯(lián)
本篇內(nèi)容主要講解“分析PostgreSQL日志相關(guān)的配置參數(shù)log_XXX”，感興趣的朋友不妨來看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“分析PostgreSQL日志相關(guān)的配置參數(shù)log_XXX”吧!
概覽
在新initdb的數(shù)據(jù)庫(kù)上查詢pg_settings，可查詢PostgreSQL中與log的參數(shù)包括：
[test@localhost ~]$ psql Expanded display is used automatically. psql (12.1) Type "help" for help. [local:/var/run/test]:5000 test@testdb=# select category,name,setting from pg_settings where name like 'log%' order by category; category | name | setting --------------------------------------+-----------------------------+-------------------------------- Reporting and Logging / What to Log | log_lock_waits | off Reporting and Logging / What to Log | log_checkpoints | off Reporting and Logging / What to Log | log_connections | off Reporting and Logging / What to Log | log_timezone | PRC Reporting and Logging / What to Log | log_temp_files | -1 Reporting and Logging / What to Log | log_disconnections | off Reporting and Logging / What to Log | log_duration | off Reporting and Logging / What to Log | log_error_verbosity | default Reporting and Logging / What to Log | log_statement | none Reporting and Logging / What to Log | log_replication_commands | off Reporting and Logging / What to Log | log_autovacuum_min_duration | -1 Reporting and Logging / What to Log | log_hostname | off Reporting and Logging / What to Log | log_line_prefix | %m [%p] Reporting and Logging / When to Log | log_min_duration_statement | -1 Reporting and Logging / When to Log | log_min_error_statement | error Reporting and Logging / When to Log | log_min_messages | warning Reporting and Logging / When to Log | log_transaction_sample_rate | 0 Reporting and Logging / Where to Log | log_destination | stderr Reporting and Logging / Where to Log | log_filename | postgresql-%Y-%m-%d_%H%M%S.log Reporting and Logging / Where to Log | logging_collector | off Reporting and Logging / Where to Log | log_truncate_on_rotation | off Reporting and Logging / Where to Log | log_rotation_size | 10240 Reporting and Logging / Where to Log | log_file_mode | 0600 Reporting and Logging / Where to Log | log_rotation_age | 1440 Reporting and Logging / Where to Log | log_directory | log Statistics / Monitoring | log_statement_stats | off Statistics / Monitoring | log_planner_stats | off Statistics / Monitoring | log_executor_stats | off Statistics / Monitoring | log_parser_stats | off (29 rows) [local:/var/run/test]:5000 test@testdb=#
log_打頭的參數(shù)有29個(gè)，下面從where、when、what這幾個(gè)維度來解析這些參數(shù)，本節(jié)是第三部分，介紹what to log。
What to log
debug_print_parse
是否打印分析階段的查詢樹，默認(rèn)為off-不打印
debug_print_rewritten
是否打印查詢重寫階段的查詢樹，默認(rèn)為off-不打印
debug_print_plan
是否打印計(jì)劃階段的計(jì)劃樹，默認(rèn)為off-不打印
debug_pretty_print
是否以優(yōu)雅的方式打印？默認(rèn)為on
log_checkpoints
是否記錄checkpoint信息，默認(rèn)為off。
在checkpoint發(fā)生時(shí)，PG會(huì)記錄相關(guān)的信息
checkpoint_timeout = 1min # range 30s-1d checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0 #checkpoint_flush_after = 256kB # measured in pages, 0 disables #checkpoint_warning = 30s # 0 disables log_checkpoints = on
插入數(shù)據(jù)，在檢查點(diǎn)發(fā)生時(shí)，可以看到檢查點(diǎn)的相關(guān)信息輸出
2019-12-30 14:27:03.618 CST [2224] LOG: duration: 106.374 ms statement: insert into tbl select x,'c1'||x from generate_series(1,10000) x; 2019-12-30 14:28:00.428 CST [2166] LOG: checkpoint starting: time 2019-12-30 14:28:06.387 CST [2166] LOG: checkpoint complete: wrote 59 buffers (0.4%); 0 WAL file(s) added, 0 removed, 0 recycled; write=5.949 s, sync=0.001 s, total=5.958 s; sync files=3, longest=0.001 s, average=0.000 s; distance=764 kB, estimate=764 kB
log_connections
記錄登錄信息，包括什么時(shí)候有連接請(qǐng)求，哪個(gè)用戶連接的是哪個(gè)數(shù)據(jù)庫(kù)；如連接失敗，也會(huì)記錄相關(guān)信息。
###修改配置信息 [test@localhost ~]$ grep 'log_connections' $PGDATA/postgresql.conf # "postgres -c log_connections=on". Some parameters can be changed at run time log_connections = on [test@localhost ~]$ pg_ctl reload server signaled [test@localhost ~]$ ###日志輸出 2019-12-30 14:33:02.527 CST [2634] LOG: connection received: host=[local] 2019-12-30 14:33:02.531 CST [2634] LOG: connection authorized: user=test database=testdb application_name=psql
log_disconnections
記錄連接斷開登錄信息
### [test@localhost ~]$ grep 'log_disconnections' $PGDATA/postgresql.conf log_disconnections = on [test@localhost ~]$ ###日志輸出 2019-12-30 14:34:57.646 CST [2734] LOG: disconnection: session time: 0:00:04.885 user=test database=testdb host=[local]
log_duration
記錄執(zhí)行時(shí)間，僅記錄執(zhí)行時(shí)間，沒有其他多余的信息。默認(rèn)為on。
2019-12-30 14:36:49.149 CST [2224] LOG: duration: 12.178 ms
log_error_verbosity
出現(xiàn)錯(cuò)誤時(shí)的日志診斷信息級(jí)別，可選項(xiàng)包括：terse, default, or verbose，默認(rèn)值為default
使用verbose，可以看到哪個(gè)源文件的哪一行，方便診斷
###創(chuàng)建測(cè)試對(duì)象 [local:/data/run/test]:5000 test@testdb=# create view vw_tbl as select * from tbl; CREATE VIEW [local:/data/run/test]:5000 test@testdb=# ###terse 2019-12-30 14:41:30.395 CST [2163] LOG: parameter "log_error_verbosity" changed to "terse" 2019-12-30 14:41:39.689 CST [2224] ERROR: cannot drop table tbl because other objects depend on it ###default 2019-12-30 14:42:27.106 CST [2163] LOG: parameter "log_error_verbosity" changed to "default" 2019-12-30 14:42:33.287 CST [2224] ERROR: cannot drop table tbl because other objects depend on it 2019-12-30 14:42:33.287 CST [2224] DETAIL: view vw_tbl depends on table tbl 2019-12-30 14:42:33.287 CST [2224] HINT: Use DROP ... CASCADE to drop the dependent objects too. ###verbose 2019-12-30 14:43:29.654 CST [2163] LOG: 00000: parameter "log_error_verbosity" changed to "verbose" 2019-12-30 14:43:29.654 CST [2163] LOCATION: ProcessConfigFileInternal, guc-file.l:456 2019-12-30 14:43:32.790 CST [2224] ERROR: 2BP01: cannot drop table tbl because other objects depend on it 2019-12-30 14:43:32.790 CST [2224] DETAIL: view vw_tbl depends on table tbl 2019-12-30 14:43:32.790 CST [2224] HINT: Use DROP ... CASCADE to drop the dependent objects too. 2019-12-30 14:43:32.790 CST [2224] LOCATION: reportDependentObjects, dependency.c:1196
log_hostname
是否記錄主機(jī)名稱，默認(rèn)為off。
log_line_prefix
每一行日志前的前綴。默認(rèn)為’%m [%p] ‘，可使用的通配符包括：
%a – application name
%u – user name
%d – database name
%r – remote host and port
%h – remote host
%p – process ID
%t – timestamp without milliseconds
%m – timestamp with milliseconds
%i – command tag
%e – SQL state
%c – session ID
%l – session line number
%s – session start timestamp
%v – virtual transaction ID
%x – transaction ID (0 if none)
%q – stop here in non-session processes
%% – ‘%’
把該參數(shù)修改為’%m %u@%d %p %r’，reload后的日志輸出：
2019-12-30 15:20:31.707 CST @ [2163] LOG: 00000: parameter "log_line_prefix" changed to "%m %u@%d [%p] %r " 2019-12-30 15:20:31.707 CST @ [2163] LOCATION: ProcessConfigFileInternal, guc-file.l:456 2019-12-30 15:20:40.148 CST test@testdb [2224] [local] ERROR: 2BP01: cannot drop table tbl because other objects depend on it 2019-12-30 15:20:40.148 CST test@testdb [2224] [local] DETAIL: view vw_tbl depends on table tbl 2019-12-30 15:20:40.148 CST test@testdb [2224] [local] HINT: Use DROP ... CASCADE to drop the dependent objects too. 2019-12-30 15:20:40.148 CST test@testdb [2224] [local] LOCATION: reportDependentObjects, dependency.c:1196
log_lock_waits
記錄等待時(shí)間超過deadlock_timeout（默認(rèn)為1s）的lock。
2019-12-30 15:27:06.637 CST @ [2163] LOG: 00000: parameter "log_lock_waits" changed to "on" 2019-12-30 15:27:06.637 CST @ [2163] LOCATION: ProcessConfigFileInternal, guc-file.l:456 2019-12-30 15:27:18.462 CST test@testdb [2224] [local] LOG: 00000: duration: 0.542 ms 2019-12-30 15:27:18.462 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:1289 2019-12-30 15:27:36.559 CST test@testdb [5749] [local] LOG: 00000: duration: 0.639 ms 2019-12-30 15:27:36.559 CST test@testdb [5749] [local] LOCATION: exec_simple_query, postgres.c:1289 2019-12-30 15:27:44.470 CST test@testdb [2224] [local] LOG: 00000: duration: 3.592 ms 2019-12-30 15:27:44.470 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:1289 2019-12-30 15:27:58.024 CST test@testdb [5749] [local] LOG: 00000: process 5749 still waiting for AccessShareLock on relation 16385 of database 16384 after 1000.615 ms at character 22 2019-12-30 15:27:58.024 CST test@testdb [5749] [local] DETAIL: Process holding the lock: 2224. Wait queue: 5749. 2019-12-30 15:27:58.024 CST test@testdb [5749] [local] LOCATION: ProcSleep, proc.c:1493 ...
log_statement
記錄哪些語(yǔ)句，可選項(xiàng)包括none, ddl, mod, all。
設(shè)置該參數(shù)為all，而log_min_duration_statement設(shè)置為600
執(zhí)行SQL
[local:/data/run/test]:5000 test@testdb=# select 1; ?column? ---------- 1 (1 row) [local:/data/run/test]:5000 test@testdb=# select pg_sleep(1); pg_sleep ---------- (1 row)
雖然select 1;執(zhí)行得很快，沒有超過600ms，但由于設(shè)置了該參數(shù)為all，因此也會(huì)在日志中出現(xiàn)
2019-12-30 15:32:41.934 CST @ [2163] LOG: 00000: received SIGHUP, reloading configuration files 2019-12-30 15:32:41.934 CST @ [2163] LOCATION: SIGHUP_handler, postmaster.c:2635 2019-12-30 15:32:41.939 CST @ [2163] LOG: 00000: parameter "log_min_duration_statement" changed to "600" 2019-12-30 15:32:41.939 CST @ [2163] LOCATION: ProcessConfigFileInternal, guc-file.l:456 2019-12-30 15:32:41.939 CST @ [2163] LOG: 00000: parameter "log_statement" changed to "all" 2019-12-30 15:32:41.939 CST @ [2163] LOCATION: ProcessConfigFileInternal, guc-file.l:456 2019-12-30 15:32:51.932 CST test@testdb [2224] [local] LOG: 00000: statement: select 1; 2019-12-30 15:32:51.932 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:1045 2019-12-30 15:32:51.932 CST test@testdb [2224] [local] LOG: 00000: duration: 0.375 ms 2019-12-30 15:32:51.932 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:1289 2019-12-30 15:33:02.686 CST test@testdb [2224] [local] LOG: 00000: statement: select pg_sleep(1); 2019-12-30 15:33:02.686 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:1045 2019-12-30 15:33:03.691 CST test@testdb [2224] [local] LOG: 00000: duration: 1005.297 ms 2019-12-30 15:33:03.691 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:1289
把該參數(shù)設(shè)置為none，執(zhí)行同樣的SQL，日志輸出中只有select pg_sleep(1);
2019-12-30 15:35:06.740 CST @ [2163] LOG: 00000: received SIGHUP, reloading configuration files 2019-12-30 15:35:06.740 CST @ [2163] LOCATION: SIGHUP_handler, postmaster.c:2635 2019-12-30 15:35:06.743 CST @ [2163] LOG: 00000: parameter "log_statement" changed to "none" 2019-12-30 15:35:06.743 CST @ [2163] LOCATION: ProcessConfigFileInternal, guc-file.l:456 2019-12-30 15:35:09.995 CST test@testdb [2224] [local] LOG: 00000: duration: 0.325 ms 2019-12-30 15:35:09.995 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:1289 2019-12-30 15:35:12.441 CST test@testdb [2224] [local] LOG: 00000: duration: 1001.645 ms statement: select pg_sleep(1); 2019-12-30 15:35:12.441 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:1296
log_replication_commands
是否記錄復(fù)制命令。
log_temp_files
是否記錄大小超過該參數(shù)配置大小的臨時(shí)文件。在執(zhí)行大批量數(shù)據(jù)排序或者使用臨時(shí)表時(shí)可以用于診斷。
2019-12-30 15:40:37.992 CST @ [2163] LOG: 00000: received SIGHUP, reloading configuration files 2019-12-30 15:40:37.992 CST @ [2163] LOCATION: SIGHUP_handler, postmaster.c:2635 2019-12-30 15:40:37.993 CST @ [2163] LOG: 00000: parameter "log_replication_commands" changed to "on" 2019-12-30 15:40:37.993 CST @ [2163] LOCATION: ProcessConfigFileInternal, guc-file.l:456 2019-12-30 15:40:37.993 CST @ [2163] LOG: 00000: parameter "log_temp_files" changed to "1024" 2019-12-30 15:40:37.993 CST @ [2163] LOCATION: ProcessConfigFileInternal, guc-file.l:456 ###日志輸出 2019-12-30 15:42:07.897 CST test@testdb [2224] [local] LOG: 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp2224.4", size 29507584 2019-12-30 15:42:07.897 CST test@testdb [2224] [local] LOCATION: ReportTemporaryFileUsage, fd.c:1285
log_timezone
是否記錄時(shí)區(qū)信息。
到此，相信大家對(duì)“分析PostgreSQL日志相關(guān)的配置參數(shù)log_XXX”有了更深的了解，不妨來實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
當(dāng)前名稱：分析PostgreSQL日志相關(guān)的配置參數(shù)log_XXX-創(chuàng)新互聯(lián)
標(biāo)題網(wǎng)址：http://www.dlmjj.cn/article/ddeooh.html
