前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >解读年度数据库性能:PostgreSQL的日志文件和数据加载

解读年度数据库性能:PostgreSQL的日志文件和数据加载

作者头像
数据和云
发布2019-08-19 11:44:13
9630
发布2019-08-19 11:44:13
举报
文章被收录于专栏:数据和云

墨墨导读:本文主要介绍PostgreSQL的日志文件参数及注意事项,从csv日志中载入数据库。通过灵活的数据加载方式,让SQL在处理很多问题上更加简捷便利。

运行日志参数


1.1 运行日志主要参数

运行日志主要相关的参数如下,默认没有开启的话没有log目录,开启后会自动生成。

参数

可选值/说明

log_destination = 'csvlog'

# stderr, csvlog, syslog, and eventlog ,csvlog requires logging_collector to be on一般选择这个,可以将csv日志导入数据库中查看

logging_collector = on

# Enable capturing of stderr and csvlog into log files选scv格式的日志必须设置这个参数on,修改需要重启

log_directory = 'log'

日志输出目录

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log

日志文件名字格式

log_file_mode = 0600

# creation mode for log files 日志文件权限

log_truncate_on_rotation = on

设置重用日志

log_rotation_age = 1d

多长时间重用日志

log_rotation_size = 10MB

日志达到多大重用

log_min_messages = warning

# debug5,debug4,debug3,debug2,debug1,info,notice,warning,error,log,fatal,panic

log_min_duration_statement = 60

慢sql记录(超过多长时间的sql)

log_checkpoints = on

记录checkpoint操作

log_connections = on

记录会话连接操作

log_disconnections = on

记录会话断开操作

log_duration = on

记录sql执行时间

log_lock_waits = on

# log lock waits >= deadlock_timeout 记录时间长的阻塞

log_statement = 'ddl'

# none, ddl, mod, all 记录ddl

1.2 注意事项

  • 设置csv格式日志的话一定要设置logging_collector 为 on
  • pg10版本的运行日志一般在$PGDATA/log目录下
  • log目录是开启运行日志后自动生成的
  • 可以通过log_rotation_age来设置多久重新生成一个日志文件
  • 可以通过log_rotation_size来设置多大的日志来重新生成日志文件
  • 上面两个都需要配合log_truncate_on_rotation 为 on来使用
  • 可以开启log_duration来记录sql执行时间
  • 可以开启log_statement来记录数据库ddl

1.3 csv日志载入数据库

Oracle有外部表,pg也有fdw。oracle可以用外部表的方式将alert日志载入到数据库中用SQL来查看。PG可以用copy命令将csv日志载入到数据库中用SQL来查看。这种方式都可以很方便得用sql来查询想要的日志内容。这种方式的有点是显而易见的,就是可以很容易得用SQL来查询和过滤日志,pg的日志文件可以截断分割成若干小文件,可以载入自己需要的日志。而Oracle的alert通常会很大。

缺点也是显而易见的,如果数据库挂了就不能用这种方式来查看日志。而且pg的csv日志不容易直接阅读。

1.3.1 创建日志表

创建了一个数据库和新的表来载入日志

代码语言:javascript
复制
postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "pg12".
test=# CREATE TABLE pg_log
test-# (
test(#   log_time timestamp(3) with time zone,
test(#   user_name text,
test(#   database_name text,
test(#   process_id integer,
test(#   connection_from text,
test(#   session_id text,
test(#   session_line_num bigint,
test(#   command_tag text,
test(#   session_start_time timestamp with time zone,
test(#   virtual_transaction_id text,
test(#   transaction_id bigint,
test(#   error_severity text,
test(#   sql_state_code text,
test(#   message text,
test(#   detail text,
test(#   hint text,
test(#   internal_query text,
test(#   internal_query_pos integer,
test(#   context text,
test(#   query text,
test(#   query_pos integer,
test(#   location text,
test(#   application_name text,
test(#   PRIMARY KEY (session_id, session_line_num)
test(# );

CREATE TABLE
test=#

1.3.2 查看日志文件名字

代码语言:javascript
复制
[pg12@whf307 ~]$ cd $PGDATA/log
[pg12@whf307 log]$ ls -rtl
total 24
-rw------- 1 pg12 pg12  166 May 30 13:32 postgresql-2019-05-30_133202.log
-rw------- 1 pg12 pg12  496 May 30 13:32 postgresql-2019-05-30_133202.csv
-rw------- 1 pg12 pg12    0 May 30 13:32 postgresql-2019-05-30_133254.log
-rw------- 1 pg12 pg12  170 May 30 13:32 postgresql-2019-05-30_133254.csv
-rw------- 1 pg12 pg12  166 May 30 13:33 postgresql-2019-05-30_133324.log
-rw------- 1 pg12 pg12 6566 May 30 16:16 postgresql-2019-05-30_133324.csv
-rw------- 1 pg12 pg12    0 May 31 00:00 postgresql-2019-05-31_000000.log
-rw------- 1 pg12 pg12    0 May 31 00:00 postgresql-2019-05-31_000000.csv
[pg12@whf307 log]$
[pg12@whf307 log]$ pwd
/soft/pg_data/log
[pg12@whf307 log]$

1.3.3 载入到数据库

代码语言:javascript
复制
[pg12@whf307 log]$ psql  test
psql (12beta1)
Type "help" for help.
test=# \d
        List of relations
 Schema |  Name  | Type  | Owner 
--------+--------+-------+-------
 public | pg_log | table | pg12
(1 row)

test=# copy pg_log from '/soft/pg_data/log/postgresql-2019-05-30_133324.csv' with csv; 
COPY 32

1.3.4 查看日志

这样就可以用sql来查看了。执行一个普通查询

代码语言:javascript
复制
test=# select relfilenode from pg_class where relname='pg_log';
 relfilenode 
-------------
       16385
(1 row)

载入最新的日志。这里可以重复载入,不会覆盖之前的数据。

代码语言:javascript
复制
[pg12@whf307 log]$ ls -rtl
total 32
-rw------- 1 pg12 pg12  166 May 30 13:32 postgresql-2019-05-30_133202.log
-rw------- 1 pg12 pg12  496 May 30 13:32 postgresql-2019-05-30_133202.csv
-rw------- 1 pg12 pg12    0 May 30 13:32 postgresql-2019-05-30_133254.log
-rw------- 1 pg12 pg12  170 May 30 13:32 postgresql-2019-05-30_133254.csv
-rw------- 1 pg12 pg12  166 May 30 13:33 postgresql-2019-05-30_133324.log
-rw------- 1 pg12 pg12 6566 May 30 16:16 postgresql-2019-05-30_133324.csv
-rw------- 1 pg12 pg12    0 May 31 00:00 postgresql-2019-05-31_000000.log
-rw------- 1 pg12 pg12 4545 May 31 00:37 postgresql-2019-05-31_000000.csv
[pg12@whf307 log]$ psql test
psql (12beta1)
Type "help" for help.
test=# copy pg_log from '/soft/pg_data/log/postgresql-2019-05-31_000000.csv' with csv; 
COPY 28

再次查看日志

代码语言:javascript
复制
test=# SELECT COUNT(*) FROM PG_LOG;
 count 
-------
    60
(1 row)

test=# select log_time at time zone 'UTC' ,database_name,connection_from,query from pg_log where log_time>to_timestamp('2019-05-31 14:35:00','yyyy-mm-dd hh24:mi:ss');
        timezone         | database_name | connection_from |                           query                           
-------------------------+---------------+-----------------+-----------------------------------------------------------
 2019-05-31 06:35:42.843 | test          | [local]         | 
 2019-05-31 06:35:57.582 | test          | [local]         | 
 2019-05-31 06:36:54.369 | test          | [local]         | selectt relfilenode from pg_class where relname='pg_log';
 2019-05-31 06:36:58.002 | test          | [local]         | 
 2019-05-31 06:37:00.192 | test          | [local]         | 
 2019-05-31 06:37:11.651 |               | [local]         | 
 2019-05-31 06:37:11.651 | test          | [local]         | 
(7 rows)

可以看到记录数变成了60,之前的记录没有被覆盖,我们可以一直使用该表,可以用sql来查看sql,数据库,登录时间等等的所有日志。

查看日志起始结束时间

代码语言:javascript
复制
test=# select min(log_time)  at time zone 'UTC',max(log_time)  at time zone 'UTC' from pg_log;
        timezone         |        timezone         
-------------------------+-------------------------
 2019-05-30 19:33:24.892 | 2019-05-31 06:37:11.651
(1 row)

有了灵活的数据加载方式,让SQL处理很多问题更加简捷便利。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-08-14,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 数据和云 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
文件存储
文件存储(Cloud File Storage,CFS)为您提供安全可靠、可扩展的共享文件存储服务。文件存储可与腾讯云服务器、容器服务、批量计算等服务搭配使用,为多个计算节点提供容量和性能可弹性扩展的高性能共享存储。腾讯云文件存储的管理界面简单、易使用,可实现对现有应用的无缝集成;按实际用量付费,为您节约成本,简化 IT 运维工作。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档