Greenplum数据库中日志实例分析
这篇文章主要介绍“Greenplum数据库中日志实例分析”，在日常操作中，相信很多人在Greenplum数据库中日志实例分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Greenplum数据库中日志实例分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
在西藏等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供网站设计制作、网站建设 网站设计制作定制制作,公司网站建设,企业网站建设,成都品牌网站建设,网络营销推广,外贸网站建设,西藏网站建设费用合理。
Greenplum通过Master接受外部增删改查操作，默认情况下，所有增删改查操作都会记录到$MASTER_DATA_DIRECTORY/pg_log下对应的文件中。
1.有时候查看Greenplum Master日志的时候，会发现有一些LOG信息类似如下：
2020-09-09 20:09:56.760795 CST,"gposs5","postgres",p32314,th-591984576,"10.211.55.2","61196",2020-09-09 20:09:56 CST,0,con10,cmd1,seg-1,,dx4,,sx1,"LOG","00000","statement: BEGIN",,,,,,"BEGIN",0,,"postgres.c",1590, 2020-09-09 20:09:56.769845 CST,"gposs5","postgres",p32314,th-591984576,"10.211.55.2","61196",2020-09-09 20:09:56 CST,0,con10,cmd2,seg-1,,dx4,,sx1,"LOG","00000","statement: select 2020-09-08",,,,,,"select 2020-09-08",0,,"postgres.c",1590, 2020-09-09 20:09:56.771312 CST,"gposs5","postgres",p32314,th-591984576,"10.211.55.2","61196",2020-09-09 20:09:56 CST,0,con11,,seg-1,,dx4,,sx1,"LOG","00000","The previous session was reset because its gang was disconnected (session id = 10). The new session id = 11",,,,,,,0,,"cdbgang.c",1710,
那么这些日志为何会产生呢？下面我们通过一段简单的python代码来复现几个场景。
2.代码部分如下：
update1_sql = "select %s" conn1 = Tools.gp_connect() cur1 = conn1.cursor() cur1.execute(update1_sql % "'2020-09-08'") row = cur1.fetchone() cur1.close() print(row[0]) conn1.commit() conn1.close() time.sleep(1000000)
此段代码为正常执行的代码，后台数据库产生的日志如下，有begin;正常执行查询语句;commit;
2020-09-09 20:30:30.898275 CST,"gposs5","postgres",p4116,th-591984576,"10.211.55.2","64416",2020-09-09 20:30:30 CST,0,con41,cmd1,seg-1,,dx26,,sx1,"LOG","00000","statement: BEGIN",,,,,,"BEGIN",0,,"postgres.c",1590, 2020-09-09 20:30:30.909131 CST,"gposs5","postgres",p4116,th-591984576,"10.211.55.2","64416",2020-09-09 20:30:30 CST,0,con41,cmd2,seg-1,,dx26,,sx1,"LOG","00000","statement: select '2020-09-08'",,,,,,"select '2020-09-08'",0,,"postgres.c",1590, 2020-09-09 20:30:30.909766 CST,"gposs5","postgres",p4116,th-591984576,"10.211.55.2","64416",2020-09-09 20:30:30 CST,0,con41,cmd4,seg-1,,dx26,,sx1,"LOG","00000","statement: COMMIT",,,,,,"COMMIT",0,,"postgres.c",1590,
错误场景一：
我们把代码中的commit部分注释掉，如下：
update1_sql = "select %s" conn1 = Tools.gp_connect() cur1 = conn1.cursor() cur1.execute(update1_sql % "'2020-09-08'") row = cur1.fetchone() cur1.close() print(row[0]) #conn1.commit() conn1.close() time.sleep(1000000)
执行上面代码，可以正常获取查询结果，但是实际上我们开启事务后并没有正常关闭，然后就直接把数据库连接关了，有点粗暴啊。此时，数据库日志如下：
2020-09-09 20:34:31.100551 CST,"gposs5","postgres",p4869,th-591984576,"10.211.55.2","65043",2020-09-09 20:34:31 CST,0,con42,cmd1,seg-1,,dx27,,sx1,"LOG","00000","statement: BEGIN",,,,,,"BEGIN",0,,"postgres.c",1590, 2020-09-09 20:34:31.116187 CST,"gposs5","postgres",p4869,th-591984576,"10.211.55.2","65043",2020-09-09 20:34:31 CST,0,con42,cmd2,seg-1,,dx27,,sx1,"LOG","00000","statement: select '2020-09-08'",,,,,,"select '2020-09-08'",0,,"postgres.c",1590, 2020-09-09 20:34:31.117199 CST,"gposs5","postgres",p4869,th-591984576,"10.211.55.2","65043",2020-09-09 20:34:31 CST,0,con43,,seg-1,,dx27,,sx1,"LOG","00000","The previous session was reset because its gang was disconnected (session id = 42). The new session id = 43",,,,,,,0,,"cdbgang.c",1710,
The previous session was reset because its gang was disconnected，数据库重置了该会话。
错误场景二：
我们把代码中的commit部分放开，把连接close的代码注释掉：
update1_sql = "select %s" conn1 = Tools.gp_connect() cur1 = conn1.cursor() cur1.execute(update1_sql % "'2020-09-08'") row = cur1.fetchone() cur1.close() print(row[0]) conn1.commit() # conn1.close() time.sleep(1000000)
此时是一个完整的事务，只是说我们没有把该连接关掉，还可以在该代码中执行一些其他的操作，但是最终，这个链接是一定要关掉的，否则会耗尽数据库资源：
2020-09-09 20:38:43.982689 CST,"gposs5","postgres",p5655,th-591984576,"10.211.55.2","49315",2020-09-09 20:38:43 CST,0,con44,cmd1,seg-1,,dx28,,sx1,"LOG","00000","statement: BEGIN",,,,,,"BEGIN",0,,"postgres.c",1590, 2020-09-09 20:38:43.996448 CST,"gposs5","postgres",p5655,th-591984576,"10.211.55.2","49315",2020-09-09 20:38:43 CST,0,con44,cmd2,seg-1,,dx28,,sx1,"LOG","00000","statement: select '2020-09-08'",,,,,,"select '2020-09-08'",0,,"postgres.c",1590, 2020-09-09 20:38:43.996787 CST,"gposs5","postgres",p5655,th-591984576,"10.211.55.2","49315",2020-09-09 20:38:43 CST,0,con44,cmd4,seg-1,,dx28,,sx1,"LOG","00000","statement: COMMIT",,,,,,"COMMIT",0,,"postgres.c",1590,
错误场景三：
这个场景比较有意思，也是最值得我们注意的一个错误场景，如下，我们把commit和close操作都注释掉：
update1_sql = "select %s" conn1 = Tools.gp_connect() cur1 = conn1.cursor() cur1.execute(update1_sql % "'2020-09-08'") row = cur1.fetchone() cur1.close() print(row[0]) # conn1.commit() # conn1.close() time.sleep(1000000)
此时，该代码也能正常执行，控制台也有正确的结果返回。后台数据库日志如下，只有begin和sql查询，没有对应的commit语句。
2020-09-09 20:42:15.271391 CST,"gposs5","postgres",p6347,th-591984576,"10.211.55.2","49861",2020-09-09 20:42:15 CST,0,con47,cmd1,seg-1,,dx32,,sx1,"LOG","00000","statement: BEGIN",,,,,,"BEGIN",0,,"postgres.c",1590, 2020-09-09 20:42:15.284936 CST,"gposs5","postgres",p6347,th-591984576,"10.211.55.2","49861",2020-09-09 20:42:15 CST,0,con47,cmd2,seg-1,,dx32,,sx1,"LOG","00000","statement: select '2020-09-08'",,,,,,"select '2020-09-08'",0,,"postgres.c",1590,
此时我们查询活动视图，发现该事务没有正常关闭：
postgres=# select * from pg_stat_activity; datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port | application_name | xact_start | waiting_reason | rsgid | rsgname | rsgqueueduration -------+----------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+---------------------------- ---+-------------+-------------+------------------+-------------------------------+----------------+-------+---------+------------------ 12094 | postgres | 6347 | 47 | 10 | gposs5 |in transaction | f | 2020-09-09 20:42:15.284855+08 | 2020-09-09 20:42:15.268696+ 08 | 10.211.55.2 | 49861 | | 2020-09-09 20:42:15.271253+08 | | 0 | unknown | 12094 | postgres | 3096 | 25 | 10 | gposs5 | select * from pg_stat_activity; | f | 2020-09-09 20:45:39.149973+08 | 2020-09-09 20:25:17.0897+08 | | -1 | psql | 2020-09-09 20:45:39.149973+08 | | 0 | unknown | (2 rows)
那么这个场景就有意思了，如果你不提交，也不关闭连接，此时只能听天由命了，万一来一个DDL语句，马上就会锁表，所有DDL后面的查询都会等待锁而处于假死状态。
从上面的几个例子可以看到，在python里面，如果没有显示的提交，便会产生上面的一些提示日志，这种情况，可以扩展到任何将查询作为一个单独事务的语言或框架中，这就要求我们在平时代码编写时，养成良好的数据库提交习惯。
备注：以上问题是在gpdb5.x上面复现的，在5.x的较新版本及6.x中，存在一个idle in transaction处理参数，用于在数据库层面上处理这种未正常结束的事务。无论怎样，最好的处理方式还是正确的使用代码来访问数据库。
到此，关于“Greenplum数据库中日志实例分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注创新互联网站，小编会继续努力为大家带来更多实用的文章！
分享文章：Greenplum数据库中日志实例分析
网址分享：http://myzitong.com/article/gegieo.html