Shulou(Shulou.com)05/31 Report--

This article focuses on "analyzing the configuration parameters related to the PostgreSQL log log_XXX". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "Analysis of PostgreSQL log-related configuration parameters log_XXX" bar!

Overview

Query pg_settings on the database of the new initdb, and the parameters associated with log in PostgreSQL include:

[test@localhost] $psqlExpanded 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=# |

There are 29 parameters that start with log_. The following parses these parameters from the dimensions of where, when and what. This section is the third part, which introduces what to log.

What to log

Debug_print_parse

Whether to print the query tree in the analysis phase. Off- does not print by default.

Debug_print_rewritten

Whether to print the query tree in the query rewriting phase. Off- does not print by default.

Debug_print_plan

Whether to print the plan tree of the plan phase. Off- does not print by default.

Debug_pretty_print

Do you print in an elegant way? Default is on

Log_checkpoints

Whether to record checkpoint information. Default is off.

When checkpoint occurs, PG records relevant information

Checkpoint_timeout = 1min # range 30s-1dcheckpoint_completion_target = 0.5 # checkpoint target duration, 0.0-1.0#checkpoint_flush_after = 256kB # measured in pages, 0 disables#checkpoint_warning = 30s # 0 disableslog_checkpoints = on

Insert data, and when the checkpoint occurs, you can see the output of information about the checkpoint

2019-12-30 14 duration 2715 CST 03.618 CST [2224] LOG: duration: 106.374 ms statement: insert into tbl select x memorials c1' | x from generate_series (1p10 000) x Ten 2019-12-30 14 14 LOG 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

Record login information, including when there is a connection request, which user is connected to which database; if the connection fails, the relevant information will also be recorded.

# modify configuration information [test@localhost ~] $grep 'log_connections' $PGDATA/postgresql.conf# "postgres-c log_connections=on". Some parameters can be changed at run timelog_connections = on [test@localhost ~] $pg_ctl reloadserver signaled [test@localhost ~] $# Log output 2019-12-30 14 test@localhost 33 test@localhost 02.527 CST [2634] LOG: connection received: host= [local] 2019-12-30 14 test@localhost 33 CST [2634] LOG: connection authorized: user=test database=testdb application_name=psql

Log_disconnections

Record disconnected login information

# [test@localhost ~] $grep 'log_disconnections' $PGDATA/postgresql.conflog_disconnections = on [test@localhost ~] $# Log output 2019-12-30 14 log_disconnections' 57.646 CST [2734] LOG: disconnection: session time: 04.885 user=test database=testdb host= [local]

Log_duration

Record the execution time, only record the execution time, no other superfluous information. The default is on.

2019-12-30 14 duration 3615 49 CST [2224] LOG: duration: 12.178 ms

Log_error_verbosity

The log diagnostic information level when an error occurs. Options include: terse, default, or verbose. The default value is default.

Using verbose, you can see which line of the source file for easy diagnosis

# create a test object [local:/data/run/test]: 5000 test@testdb=# create view vw_tbl as select * from tbl CREATE VIEW [local:/data/run/test]: 5000 test@testdb=# terse2019-12-30 14 test@testdb=# 41V 30.395 CST [2163] LOG: parameter "log_error_verbosity" changed to "terse" 2019-12-30 41V 41V 39.689 CST [2224] ERROR: cannot drop table tbl because other objects depend on it###default2019-12-30 14V 4227.106 CST [2163] LOG: parameter "log_error_verbosity" changed to "default" 20119- 12-30 14 cannot drop table tbl because other objects depend on it2019 42cannot drop table tbl because other objects depend on it2019 33.287 CST [2224] ERROR: cannot drop table tbl because other objects depend on it2019-12-30 14V 42cannot drop table tbl because other objects depend on it2019 33.287 CST [2224] DETAIL: view vw_tbl depends on table tbl2019-12-30 14V 42V 33.287 CST [2224] HINT: Use DROP. CASCADE to drop the dependent objects too.###verbose2019-12-30 14 LOG 43 log_error_verbosity 29.654 CST [2163] LOG: 00000: parameter "log_error_verbosity" changed to "verbose" 2019-12-30 14 LOG 43 LOG 29.654 CST [2163] ProcessConfigFileInternal Guc-file.l:4562019-12-30 14-ERROR CST [2224] ERROR: 2BP01: cannot drop table tbl because other objects depend on it2019-12-30 14-ERROR 43-32. 790 CST [2224] DETAIL: view vw_tbl depends on table tbl2019-12-30 14-ERROR 43-32 CST [2224] HINT: Use DROP. CASCADE to drop the dependent objects too.2019-12-30 14 LOCATION 4315 32.790 CST [2224] LOCATION: reportDependentObjects, dependency.c:1196

Log_hostname

Whether to record the host name, default is off.

Log_line_prefix

The prefix before each line of the log. The default is'% m [% p]', and wildcards that can be used include:

% 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

%% -'%'

Modify this parameter to the log output of'% m% upright% d% p% ritual reload:

2019-12-30 15 CST 2031. 707 CST @ [2163] LOG: 00000: parameter "log_line_prefix" changed to "% m% upright% d [% p]% r" 2019-12-30 15 V 20 31. 707 CST @ [2163] LOCATION: ProcessConfigFileInternal Guc-file.l:4562019-12-30 15 ERROR CST test@testdb [2224] [local] ERROR: 2BP01: cannot drop table tbl because other objects depend on it2019-12-30 15 local 20 ERROR 40.148 CST test@testdb [2224] [local] DETAIL: view vw_tbl depends on table tbl2019-12-30 15 ERROR 20 CST test@testdb [2224] [local] HINT: Use DROP. CASCADE to drop the dependent objects too.2019-12-30 15 local 20 LOCATION 40.148 CST test@testdb [2224] [local]: reportDependentObjects, dependency.c:1196

Log_lock_waits

Record the lock for which the wait time exceeds deadlock_timeout (default is 1s).

2019-12-30 15 CST 27 parameter 06.637 CST @ [2163] LOG: 00000: parameter "log_lock_waits" changed to "on" 2019-12-30 15 local 2718. 637 CST @ [2163] LOCATION: ProcessConfigFileInternal, guc-file.l:4562019-12-30 15 15 exec_simple_query: 18.462 CST test@testdb [2224] [local] LOG: 00000: duration: 0.542 ms2019-12-30 1515 race 2718.462 CST test@testdb [local] exec_simple_query: Postgres.c:12892019-12-30 15 LOG 27 postgres.c:12892019 36.559 CST test@testdb [5749] [local] LOG: 00000: duration: 0.639 ms2019-12-30 15 15 LOG 27 Swiss 36.559 CST test@testdb [5749] [local] LOCATION: exec_simple_query Postgres.c:12892019-12-30 15 local 27 CST test@testdb [2224] [local] LOG: 00000: duration: 3.592 ms2019-12-30 15 Swiss 27 postgres.c:12892019 44.470 CST test@testdb [2224] [local] LOCATION: exec_simple_query Postgres.c:12892019-12-30 15 local 27 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 222019-12-30 1515 DETAIL: Process holding the lock. Wait queue: 5749.2019-12-30 15 local 27confusion 58.024 CST test@testdb [5749] [local] LOCATION: ProcSleep, proc.c:1493...

Log_statement

Record which statements, optional include none, ddl, mod, all.

Set this parameter to all and log_min_duration_statement to 600,

Execute 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)

Although select 1; executes quickly and does not exceed 600ms, it also appears in the log because the parameter is set to all

2019-12-30 15 received SIGHUP 32 reloading configuration files2019 41.934 CST @ [2163] LOG: 00000: received SIGHUP, reloading configuration files2019-12-30 15 15 reloading configuration files2019 32 CST @ [2163] LOCATION: SIGHUP_handler, postmaster.c:26352019-12-30 15 reloading configuration files2019 32 log_min_duration_statement 41.939 CST @ [2163] LOG: 00000: parameter "log_min_duration_statement" changed to "600" 2019-12-30 15 15 reloading configuration files2019 32 CST @ [2163] LOCATION: Guc-file.l:4562019-12-30 15 parameter 32 CST test@testdb 41.939 CST @ [2163] LOG: 00000: parameter "log_statement" changed to "all" 2019-12-30 15v 32 CST test@testdb: 41.939 CST @ [2163] LOCATION: ProcessConfigFileInternal, guc-file.l:4562019-12-30 15 15 CST test@testdb [2224] [local] LOG: 00000: statement: select 1 2019-12-30 15 exec_simple_query 32 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:10452019-12-30 15 15 exec_simple_query 32 local 51.932 CST test@testdb [2224] [local] LOG: 00000: duration: 0.375 ms2019-12-30 15 local 32 CST test@testdb [2224] [local] LOCATION: exec_simple_query Postgres.c:12892019-12-30 15 local 3322 CST test@testdb [2224] [local] LOG: 00000: statement: select pg_sleep (1) 2019-12-30 15 exec_simple_query 33 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:10452019-12-30 15 15 CST test@testdb 333.691 CST test@testdb [2224] [local] LOG: 00000: duration: 1005.297 ms2019-12-30 1533 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:1289

Set this parameter to none and execute the same SQL. There is only select pg_sleep (1) in the log output.

2019-12-30 15 reloading configuration files2019 35 CST 06.740 CST @ [2163] LOG: 00000: received SIGHUP, reloading configuration files2019-12-30 15 15 CST @ [2163] LOCATION: SIGHUP_handler, postmaster.c:26352019-12-30 15 15 log_statement 35 CST @ [2163] LOG: 00000: parameter "log_statement" changed to "none" 2019-12-30 15 Switzerland 356.743 CST @ [2163] LOCATION: Guc-file.l:4562019-12-30 15 duration 359.995 CST test@testdb [2224] [local] LOG: 00000: duration: 0.325 ms2019-12-30 15 duration 359.995 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:12892019-12-30 1515 CST test@testdb [2224] [local] LOG: 00000: duration: 1001.645 ms statement: select pg_sleep (1) 2019-12-30 15 exec_simple_query 3512.441 CST test@testdb [2224] [local] LOCATION: exec_simple_query, postgres.c:1296

Log_replication_commands

Whether to record the copy command.

Log_temp_files

Whether to record temporary files whose size exceeds the configuration size of this parameter. Can be used for diagnostics when performing mass data sorting or when using temporary tables.

2019-12-30 15 reloading configuration files2019 40 CST 37.992 CST @ [2163] LOG: 00000: received SIGHUP, reloading configuration files2019-12-30 15 15 V 40 parameter 37.992 CST @ [2163] LOCATION: SIGHUP_handler, postmaster.c:26352019-12-30 15 15 V 37.993 CST @ [2163] LOG: 00000: parameter "log_replication_commands" changed to "on" 2019-12-30 1540 log_replication_commands CST @ [2163] LOCATION: Guc-file.l:4562019-12-30 15 parameter 40 log_temp_files 37.993 CST @ [2163] LOG: 00000: parameter "log_temp_files" changed to "1024" 2019-12-30 1515 local 37.993 CST @ [2163] LOCATION: ProcessConfigFileInternal, guc-file.l:456### Log output 2019-12-30 15V 42V 07.897 CST test@testdb [2224] [local] LOG: 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp2224.4" Size 295075842019-12-30 15 local 42purl 07.897 CST test@testdb [2224] [local] LOCATION: ReportTemporaryFileUsage, fd.c:1285

Log_timezone

Whether to record time zone information.

At this point, I believe you have a deeper understanding of "analyzing the configuration parameters related to the PostgreSQL log log_XXX". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.