Greenplum數(shù)據(jù)庫中日志實(shí)例分析

這篇文章主要介紹“Greenplum數(shù)據(jù)庫中日志實(shí)例分析”，在日常操作中，相信很多人在Greenplum數(shù)據(jù)庫中日志實(shí)例分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Greenplum數(shù)據(jù)庫中日志實(shí)例分析”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

在西藏等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供網(wǎng)站設(shè)計(jì)制作、網(wǎng)站建設(shè) 網(wǎng)站設(shè)計(jì)制作定制制作,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),成都品牌網(wǎng)站建設(shè),網(wǎng)絡(luò)營銷推廣,外貿(mào)網(wǎng)站建設(shè),西藏網(wǎng)站建設(shè)費(fèi)用合理。

Greenplum通過Master接受外部增刪改查操作，默認(rèn)情況下，所有增刪改查操作都會(huì)記錄到$MASTER_DATA_DIRECTORY/pg_log下對應(yīng)的文件中。

1.有時(shí)候查看Greenplum Master日志的時(shí)候，會(huì)發(fā)現(xiàn)有一些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,

那么這些日志為何會(huì)產(chǎn)生呢？下面我們通過一段簡單的python代碼來復(fù)現(xiàn)幾個(gè)場景。

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)

此段代碼為正常執(zhí)行的代碼，后臺(tái)數(shù)據(jù)庫產(chǎn)生的日志如下，有begin;正常執(zhí)行查詢語句;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,

錯(cuò)誤場景一：

我們把代碼中的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)

執(zhí)行上面代碼，可以正常獲取查詢結(jié)果，但是實(shí)際上我們開啟事務(wù)后并沒有正常關(guān)閉，然后就直接把數(shù)據(jù)庫連接關(guān)了，有點(diǎn)粗暴啊。此時(shí)，數(shù)據(jù)庫日志如下：

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，數(shù)據(jù)庫重置了該會(huì)話。

錯(cuò)誤場景二：

我們把代碼中的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)

此時(shí)是一個(gè)完整的事務(wù)，只是說我們沒有把該連接關(guān)掉，還可以在該代碼中執(zhí)行一些其他的操作，但是最終，這個(gè)鏈接是一定要關(guān)掉的，否則會(huì)耗盡數(shù)據(jù)庫資源：

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,

錯(cuò)誤場景三：

這個(gè)場景比較有意思，也是最值得我們注意的一個(gè)錯(cuò)誤場景，如下，我們把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)

此時(shí)，該代碼也能正常執(zhí)行，控制臺(tái)也有正確的結(jié)果返回。后臺(tái)數(shù)據(jù)庫日志如下，只有begin和sql查詢，沒有對應(yīng)的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,

此時(shí)我們查詢活動(dòng)視圖，發(fā)現(xiàn)該事務(wù)沒有正常關(guān)閉：

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

那么這個(gè)場景就有意思了，如果你不提交，也不關(guān)閉連接，此時(shí)只能聽天由命了，萬一來一個(gè)DDL語句，馬上就會(huì)鎖表，所有DDL后面的查詢都會(huì)等待鎖而處于假死狀態(tài)。

從上面的幾個(gè)例子可以看到，在python里面，如果沒有顯示的提交，便會(huì)產(chǎn)生上面的一些提示日志，這種情況，可以擴(kuò)展到任何將查詢作為一個(gè)單獨(dú)事務(wù)的語言或框架中，這就要求我們在平時(shí)代碼編寫時(shí)，養(yǎng)成良好的數(shù)據(jù)庫提交習(xí)慣。

備注：以上問題是在gpdb5.x上面復(fù)現(xiàn)的，在5.x的較新版本及6.x中，存在一個(gè)idle in transaction處理參數(shù)，用于在數(shù)據(jù)庫層面上處理這種未正常結(jié)束的事務(wù)。無論怎樣，最好的處理方式還是正確的使用代碼來訪問數(shù)據(jù)庫。

到此，關(guān)于“Greenplum數(shù)據(jù)庫中日志實(shí)例分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！

當(dāng)前名稱：Greenplum數(shù)據(jù)庫中日志實(shí)例分析
文章源于：http://bm7419.com/article40/gegieo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供虛擬主機(jī)、品牌網(wǎng)站制作、軟件開發(fā)、企業(yè)網(wǎng)站制作、商城網(wǎng)站、外貿(mào)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

成都定制網(wǎng)站網(wǎng)頁設(shè)計(jì)