Shulou(Shulou.com)06/01 Report--

This article mainly introduces "log instance analysis in Greenplum database". In daily operation, I believe that many people have doubts about log instance analysis in Greenplum database. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "log instance analysis in Greenplum database". Next, please follow the editor to study!

Greenplum accepts external add, delete, modify and query operations through Master. By default, all add, delete, modify and query operations are recorded in the corresponding file under $MASTER_DATA_DIRECTORY/pg_log.

1. Sometimes when you look at the Greenplum Master log, you will find some LOG information similar to the following:

2020-09-09 20 CST,0,con10,cmd1,seg-1,dx4,sx1 09CST,0,con10,cmd1,seg-1,dx4,sx1 56.760795 CST, "gposs5", "postgres", "gposs5", p32314, "CST,0,con10,cmd1,seg-1,dx4,sx1 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", 0Ma, "postgres.c", 1590the2020-09-09 20jewel 0949 CST 56.769845 CST, "gposs5", "postgres", p32314LTS 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", 0memorials, "postgres.c", 15902020-09-09 20 sets 09statement 56.771312 CST, "gposs5", "postgres", p32314 camera th591984576, "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 ", 050," cdbgang.c ", 1710

So why do these logs come into being? Let's recreate a few scenarios with a simple piece of python code.

two。 The code section is as follows:

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)

This code is normally executed. The log generated by the background database is as follows, and the query statement is normally executed by begin;; commit

2020-09-09 20 CST,0,con41,cmd1,seg-1,dx26,sx1 30 CST,0,con41,cmd1,seg-1,dx26,sx1 30.898275 CST, "gposs5", "postgres", "p4116", "64416", 2020-09-09 20:30:30 CST,0,con41,cmd1,seg-1,dx26,sx1, "LOG", "00000", "statement: BEGIN", "BEGIN", "0parting,"postgres.c", 1590 people 2020-09-09 20 series 30 CST,0,con41,cmd1,seg-1,dx26,sx1 30.909131 CST, "gposs5", "postgres", p4116th591984576 "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'", "postgres.c", 1590d20-09-09 20 select 30 30. 909766 CST, "gposs5", "postgres", p4116, 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", 0meme, "postgres.c", 1590

Error scenario 1:

Let's comment out the commit part of the code as follows:

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)

Execute the above code, you can get the query results normally, but in fact, we did not close normally after we opened the transaction, and then directly shut down the database connection, which is a bit rough. At this point, the database log is as follows:

2020-09-09 20 CST,0,con42,cmd1,seg-1,dx27,sx1 34shui 31.100551 CST, "gposs5", "postgres", p4869Magre 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", "0Jing,"postgres.c", 1590Ma 2020-09-09 20 CST,0,con42,cmd1,seg-1,dx27,sx1 34ma 31.116187 CST, "gposs5", "postgres", p4869THFI 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'", "postgres.c", 1590d2020-09-09 20 woman 34 sets 31.117199 CST, "gposs5", "postgres", p4869rem 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 ", 050," cdbgang.c ", 1710

The previous session was reset because its gang was disconnected, the database resets the session.

Error scenario 2:

Let's release the commit part of the code and comment out the code that connects to the 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)

This is a complete transaction, just that we did not close the connection, and we can perform some other operations in the code, but in the end, the link must be turned off, otherwise the database resources will be exhausted:

2020-09-09 20 CST,0,con44,cmd1,seg-1,dx28,sx1 3815 CST 43.982689 CST, "gposs5", "postgres", p5655 people 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", 0paramerie, "postgres.c", 1590jin2020-09 20purl 3843.996448 CST, "gposs5", "postgres", p5655Menthflix 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'", "postgres.c", 1590d2020-09-09 20MAV 3843.996787 CST, "gposs5", "postgres", p5655 people 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", 0meme, "postgres.c", 1590

Error scenario 3:

This is an interesting and most noteworthy error scenario. As follows, we comment out both commit and close operations:

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)

At this point, the code executes normally, and the console returns the correct results. The backend database log is as follows, with only begin and sql queries and no corresponding commit statements.

2020-09-09 20 CST,0,con47,cmd1,seg-1,dx32,sx1 42V CST 15.271391 CST, "gposs5", "postgres", p6347 people 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", 0paramerie, "postgres.c", 1590jin2020-09-09 20purl 42purl 15.284936 CST, "gposs5", "postgres", p6347THFI 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'", 0Perry, "postgres.c", 1590

At this point, we query the activity view and find that the transaction is not closed properly:

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 | in transaction | f | 2020-09-09 2020 42purl 15.2848552408 | 2020-09-09 2042purl 15.2686961908 | 10.211.55.2 | 49861 | 2020-09-09 20pur42pur15.2712531508 | | | 0 | unknown | 12094 | postgres | 3096 | 25 | 10 | gposs5 | select * from pg_stat_activity | | | f | 2020-09-09 20 rows 45 rows | 2020-09-09 20 rows | 2020-09-09 20 rows |-1 | GetWord ("- 1"); "20" |

Then this scenario is interesting. If you do not submit or close the connection, you can only leave it to fate at this time. In case of a DDL statement, the table will be locked immediately, and all queries after DDL will wait for the lock to be suspended.

As can be seen from the above examples, in python, if there is no commit shown, some of the above prompt logs will be generated, which can be extended to any language or framework that regards the query as a separate transaction, which requires us to develop a good habit of database submission when writing code.

Note: the above problems are repeated on gpdb5.x. In newer versions of 5.x and 6.x, there is an idle in transaction processing parameter for handling transactions that do not end normally at the database level. In any case, the best way to deal with it is to use the code correctly to access the database.

At this point, the study on "log instance analysis in Greenplum database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.