Get the latest version MTK.

wget https://cdn-mogdb.enmotech.com/mtk/v2.6.3/mtk_2.6.3_linux_amd64.tar.gz
tar -xvf mtk_2.6.3_linux_amd64.tar.gz

Gernerate MTK trial license online

The trial license lasts for 1 month, every mail address can only gernerate one license, except the mail address domain is “enmotech.com”, using “enmotech.com” mail can repeatly genarate license. So if the clients want to try MTK more after 1 month, should contact the sales or pre-sales from Enmotech, to ask for another 1 month license.

[kamus@altlinux10 mtk_2.6.3_linux_amd64]$ ./mtk license gen
License File Not Found (default license.json)
The License code is invalid, start applying
✔ Email: kamus@enmotech.com█
Start applying for email kamus@enmotech.com authorization.
Start parsing the interface to return data.
Successful application for authorization. Please check the mail and save it as license.json.

When get the mail, upload the attached license.json file to the MTK directory. Use mtk -v to check the license validation.

[kamus@altlinux10 mtk_2.6.3_linux_amd64]$ ./mtk -v
Using license file: /home/kamus/mogdb-tools/mtk_2.6.3_linux_amd64/license.json
Name: kamus@enmotech.com
Expiry: 2022-10-24 12:08:58.751194162 +0800 +0800
License key verified!
License checks OK!

MMMMMMMM               MMMMMMMMTTTTTTTTTTTTTTTTTTTTTTTKKKKKKKKK   KKKKKKK
M:::::::M             M:::::::MT:::::::::::::::::::::TK:::::::K   K:::::K
M::::::::M           M::::::::MT:::::::::::::::::::::TK:::::::K   K:::::K
M:::::::::M         M:::::::::MT:::::TT:::::::TT:::::TK:::::::K   K::::::K
M::::::::::M       M::::::::::MTTTTTT T:::::T TTTTTTKK::::::K K:::::KKK
M:::::::::::M     M:::::::::::M       T:::::T         K:::::K K:::::K
M:::::::M::::M   M::::M:::::::M       T:::::T         K::::::K:::::K
M::::::M M::::M M::::M M::::::M       T:::::T         K:::::::::::K
M::::::M M::::M::::M M::::::M       T:::::T         K:::::::::::K
M::::::M   M:::::::M   M::::::M       T:::::T         K::::::K:::::K
M::::::M   M:::::M   M::::::M       T:::::T         K:::::K K:::::K
M::::::M     MMMMM     M::::::M       T:::::T       KK::::::K K:::::KKK
M::::::M               M::::::M     TT:::::::TT     K:::::::K   K::::::K
M::::::M               M::::::M     T:::::::::T     K:::::::K   K:::::K
M::::::M               M::::::M     T:::::::::T     K:::::::K   K:::::K
MMMMMMMM               MMMMMMMM     TTTTTTTTTTT     KKKKKKKKK   KKKKKKK

Release version: v2.6.3
Git Commit hash: da0ed8ee
Git Commit Date: 2022-09-22T01:17:49Z
Git Tag       : v2.6.3
Build timestamp: 20220922011907

Install Oracle instant client

MTK needs Oracle instant client to migrate Oracle objects to MogDB, in this tutorial, we will download Oracle Instant Client for Linux x86-64 Basic Package. Unzip the downloaded file, set the proper LD_LIBRARY_PATH parameter.

export LD_LIBRARY_PATH=/home/kamus/instantclient_21_7:$LD_LIBRARY_PATH

We will migrate a sample schema “customer_orders” to MogDB in this tutorial. the db-sample-schemas scipts for Oracle can be downloaded from github.

image-20220924140451621

Initialize migration project

./mtk init-project -s oracle -t mogdb -n ora2mogdb

Modify MTK configuration file

Modify the example MTK confiuration file stored in project_name_dir/config directory, check MTK documentation for the details of every parameter. The essenssial config sections for MTK is source, target, object .

source section: is the connection defination for source database, MTK needs to query Oracle database dictionary to get DDL, so typically we should use DBA user, the default system user will be OK.

target section: is the connection defination for target database.

object section: for migrating all the objects in one schema, we just need to put schema name in schemas section.

The mtk.json config file will looks like:

{
  "source": {
    "type": "oracle",
    "connect": {
      "version": "",
      "host": "119.3.182.31",
      "user": "system",
      "port": 15221,
      "password": "oracle",
      "dbName": "ORACLE21C",
      "charset": "",
      "clientCharset": ""
    }
  },
  "target": {
    "type": "mogdb",
    "connect": {
      "version": "",
      "host": "127.0.0.1",
      "user": "co",
      "port": 26000,
      "password": "Enmo@123",
      "dbName": "postgres",
      "charset": "",
      "clientCharset": ""
    }
  },
  "object": {
    "tables": [],
    "schemas": ["co"],
    "excludeTable": {
    },
    "tableSplit": {
    }
  },
  "dataOnly": false,
  "schemaOnly": false
}

We are planning to migrate all the objects in “CO” schema from Oracle database to the same user in MogDB, for testing purpose, we will not create a new database in MogDB, we create a new user “co” in default database postgres.

[omm@altlinux10 ~]$ gsql -d postgres -p 26000 -r
gsql ((MogDB 3.0.2 build 9bc79be5) compiled at 2022-09-18 00:37:49 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=# create user co identified by "Enmo@123";
CREATE ROLE

Start migration

Now, we can start migration.

./mtk -c ora2mogdb/config/mtk.json

Check migration report

Migration result report will be generated in project report directory, both in pure text format and HTML format, till now the HTML report is in Chinese, so I put the text format result into this tutorial.

-----------------------
ObjectName Type Summary
-----------------------

+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
| Type | StartTime | EndTime | Status | Total Num | Success Num | Warring Num | Failed Num |Failed(Invalid) Num | Time |
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+
|Schema |2022-09-24 15:12:36|2022-09-24 15:12:36|finish |1 |1 |0 |0 |0 |282 ms |
|Sequence |2022-09-24 15:12:36|2022-09-24 15:12:36|finish |0 |0 |0 |0 |0 |210 ms |
|ObjectType |2022-09-24 15:12:36|2022-09-24 15:12:36|finish |0 |0 |0 |0 |0 |356 ms |
|Queue |2022-09-24 15:12:36|2022-09-24 15:12:37|finish |0 |0 |0 |0 |0 |177 ms |
|Table |2022-09-24 15:12:37|2022-09-24 15:12:47|finish |7 |7 |0 |0 |0 |9 s 952 ms |
|TableData |2022-09-24 15:12:47|2022-09-24 15:12:53|finish |7 |7 |0 |0 |0 |6 s 743 ms |
|Index |2022-09-24 15:12:53|2022-09-24 15:12:53|finish |7 |7 |0 |0 |0 |1 ms |
|Constraint |2022-09-24 15:12:53|2022-09-24 15:12:53|finish |24 |23 |0 |1 |0 |51 ms |
|DBLink |2022-09-24 15:12:53|2022-09-24 15:12:53|finish |0 |0 |0 |0 |0 |67 ms |
|View |2022-09-24 15:12:53|2022-09-24 15:12:54|finish |4 |2 |0 |2 |0 |723 ms |
|MaterializedView |2022-09-24 15:12:54|2022-09-24 15:12:54|finish |0 |0 |0 |0 |0 |138 ms |
|Function |2022-09-24 15:12:54|2022-09-24 15:12:54|finish |0 |0 |0 |0 |0 |113 ms |
|Procedure |2022-09-24 15:12:54|2022-09-24 15:12:55|finish |0 |0 |0 |0 |0 |109 ms |
|Package |2022-09-24 15:12:55|2022-09-24 15:12:55|finish |0 |0 |0 |0 |0 |77 ms |
|Trigger |2022-09-24 15:12:55|2022-09-24 15:12:55|finish |0 |0 |0 |0 |0 |404 ms |
|Synonym |2022-09-24 15:12:55|2022-09-24 15:12:55|finish |0 |0 |0 |0 |0 |74 ms |
|TableDataCom |2022-09-24 15:12:55|2022-09-24 15:12:56|finish |7 |7 |0 |0 |0 |810 ms |
|AlterSequence |2022-09-24 15:12:56|2022-09-24 15:12:56|finish |0 |0 |0 |0 |0 |71 ms |
|CollStatistics |2022-09-24 15:12:56|2022-09-24 15:12:56|finish |7 |7 |0 |0 |0 |29 ms |
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+--------------------|-------------+

We can see all the tables and table data are successfully migrated to MogDB without any error, but for constraint, there is 1 failed, and for view, there are 2 failed.

The failed constraint is a JSON check constraint. MogDB dosn’t has this type of constraint.

image-20220924153609138

The failed views are about grouping_id function and json_table function which MogDB not implemented yet.

image-20220924154621375

Run the sample queries

/* 5 products with the highest revenue
With their corresponding order rank */
select p.product_name,
count(*) number_of_orders,
sum ( oi.quantity * oi.unit_price ) total_value,
rank () over (
order by count(*) desc
) order_count_rank
from products p
join order_items oi
on p.product_id = oi.product_id
group by p.product_name
order by sum ( oi.quantity * oi.unit_price ) desc
fetch first 5 rows only;
MogDB=> select p.product_name,
MogDB-> count(*) number_of_orders,
MogDB-> sum ( oi.quantity * oi.unit_price ) total_value,
MogDB-> rank () over (
MogDB(> order by sum ( oi.quantity * oi.unit_price ) desc
MogDB(> ) revenue_rank
MogDB-> from products p
MogDB-> join order_items oi
MogDB-> on p.product_id = oi.product_id
MogDB-> group by p.product_name
MogDB-> order by count(*) desc
MogDB-> fetch first 5 rows only;
product_name | number_of_orders | total_value | revenue_rank
-----------------------+------------------+-------------+--------------
Girl's Trousers (Red) | 148 | 15794.76 | 1
Boy's Hoodie (Grey) | 100 | 3754.08 | 35
Men's Pyjamas (Blue) | 100 | 3274.61 | 36
Men's Coat (Red) | 98 | 4230.30 | 31
Boy's Socks (White) | 98 | 3081.12 | 38
(5 rows)

Conclusion

Migrating tables/table data/indexes from Oracle to MogDB normally has no issue, but for views/procedures/functions/packages, we still have to made some modification to the source code.

闪回查询可以让使用者查询到以往某个时间点上的表中的内容,这对于某些场合是非常有用处的,甚至是救命的功能。比如如果不小心误删除了表中的数据,就可以通过闪回查询查到删除时间点之前的数据,这样可以直接将这些数据再恢复回来。

比如这样一个让人惋惜的场景(delete的SQL请千万不要在生产环境中运行)。

现在你有这么一张表,记录了账户姓名和账户金额。

MogDB=# select * from accounts;
name | amount
------+--------
A   |   100
B   |   100
C   |   100
D   |     99
(4 rows)

你本来想执行一条SQL,去删除金额等于99元的账户信息。正常执行的话,应该删除1条记录。为了演示效果,先用select来代替delete。

MogDB=#  select * from accounts where amount=99;
name | amount
------+--------
D   |     99
(1 row)

但是在键盘上减号“-”和等号“=”紧挨着,你的手指太胖了,误按到了减号,所以现在你给数据库发出去的命令是这样的。

delete from accounts where amount-99;

为了演示,还是用select来代替delete。

#  select * from accounts where amount-99;
name | amount
------+--------
A   |   100
B   |   100
C   |   100
(3 rows)

恐怖的事情发生了,除了金额真的等于99的那条数据没有返回,其它所有的数据都返回了。也就意味着,如果这是上面的那条delete命令,你删除了表中所有金额不等于99的账户。

好消息是,在MogDB 3.0中,已经修改了对减号这种危险语法的校验,现在执行同样的SQL,将会报错。

MogDB=# delete from accounts where amount-99;
ERROR: argument of WHERE must be type boolean, not type integer
LINE 1: delete from accounts where amount-99;
                                  ^

但是在社区版的openGauss3.0或者MySQL或者MariaDB中,这样的危险语法仍然是可以正常执行的。

gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:28:23 commit 0 last mr  release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# select version();
                                        version
--------------------------------------------------------------------------------------------
(openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:28:23 commit 0 last mr release
(1 row)

omm=# create table accounts (name varchar2, amount int);
CREATE TABLE
omm=# insert into accounts values ('A',100),('B',100),('C',100),('D',99);
INSERT 0 4
omm=# delete from accounts where amount-99;
DELETE 3
omm=# select * from accounts;
name | amount
------+--------
D   |     99
(1 row)

不管是什么误操作,如果真的发生了,那么在MogDB 3.0中还有闪回查询可以用。

闪回查询及相关实现在MogDB 3.0中发生了一些变化。

  1. 只对存储引擎为ustore的表起作用,默认的astore已经不再支持闪回查询。
  2. 需要先手工设置undo_retention_time。该参数表示回滚段中旧版本数据的保留时间,等同于允许闪回查询的时间跨度。该参数默认是0,任何闪回查询都会遇到restore point not found报错。

创建一张ustore的accounts表。

MogDB=# create table accounts (name varchar2, amount int) with (storage_type=ustore);
CREATE TABLE

修改undo_retention_time参数,该参数单位是秒,86400表示是24小时。该参数允许最大值2147483647,相当于大约68年。当然你不会设置这么大,因为这会导致回滚段中保留过多旧数据。

MogDB=# alter system set undo_retention_time=86400;
ALTER SYSTEM SET

插入一些测试数据,就用前面提到的账户和金额。

MogDB=#  insert into accounts values ('A',100),('B',100),('C',100),('D',99);
INSERT 0 4

现在,因为某种误操作,你删除了所有金额不等于99的账户记录。

MogDB=# delete from accounts where amount<>99;
DELETE 3

MogDB=# select * from accounts;
name | amount
------+--------
D   |     99
(1 row)

当你发现这个失误的时候,可能是在1分钟以后,也可能是在1小时以后,这没关系,但是不要超过24小时(因为undo_retention_time设置了24小时),否则就找不回来了。

检查当前的时间戳,然后预估你执行误操作时候的时间戳,比如用当前时间戳减去5分钟,为了简便演示,我在发出删除命令之前,检查了系统的时间戳。

MogDB=# select sysdate;
      sysdate
---------------------
2022-09-02 13:19:24
(1 row)

发起闪回查询。

MogDB=# select * from accounts timecapsule TIMESTAMP to_timestamp('2022-09-02 13:19:24','YYYY-MM-DD HH24:MI:SS');
name | amount
------+--------
A   |   100
B   |   100
C   |   100
D   |     99
(4 rows)

真正的恢复步骤,可以是按照删除命令的条件,利用闪回查询将所有被误删除的记录创建为一张临时表,然后再从临时表中将所有记录插入回原表中。

MogDB=# create table tmp_accounts as select * from accounts timecapsule TIMESTAMP to_timestamp('2022-09-02 13:19:24','YYYY-MM-DD HH24:MI:SS') where amount<>99;
INSERT 0 3

MogDB=# select * from tmp_accounts;
name | amount
------+--------
A   |   100
B   |   100
C   |   100
(3 rows)

MogDB=# insert into accounts select * from tmp_accounts;
INSERT 0 3

MogDB=# select * from accounts;
name | amount
------+--------
D   |     99
A   |   100
B   |   100
C   |   100
(4 rows)

诊断需求

当MogDB数据库由于某种原因占用了较大的系统负载,比如CPU占用接近100%,那么如何知道到底是数据库里的哪个会话或者哪些会话占用了资源?
在Oracle数据库中,这样的问题诊断,通常都会关联 v$session, v$process, 以及操作系统top命令或者ps命令中查到的操作系统进程ID。
但是MogDB本身是线程模型,在操作系统上只能看到一个进程号,那么该如何定位问题?

因为MogDB使用的是线程模型,与PostgreSQL的进程模型不同,在操作系统级别如果用lsof命令查看网络端口的接入,虽然是会看到有多个用户会话接入,但是在PID列只会显示进程ID,无法对应到线程。

$ lsof -i 4 -a -p `pgrep -u omm3 mogdb`
COMMAND   PID USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
mogdb   12027 omm3    8u  IPv4 20313752      0t0  TCP *:biimenu (LISTEN)
mogdb   12027 omm3    9u  IPv4 20313753      0t0  TCP *:18001 (LISTEN)
mogdb   12027 omm3  325u  IPv4 28320946      0t0  TCP mogdb-kernel-0004:biimenu->softbank060132034173.bbtec.net:45206 (ESTABLISHED)
mogdb   12027 omm3  330u  IPv4 28316174      0t0  TCP mogdb-kernel-0004:biimenu->softbank060132034173.bbtec.net:45208 (ESTABLISHED)
mogdb   12027 omm3  336u  IPv4 28302815      0t0  TCP mogdb-kernel-0004:biimenu->softbank060132034173.bbtec.net:45210 (ESTABLISHED)
mogdb   12027 omm3  340u  IPv4 28323140      0t0  TCP mogdb-kernel-0004:biimenu->softbank060132034173.bbtec.net:45212 (ESTABLISHED)
mogdb   12027 omm3  360u  IPv4 28323141      0t0  TCP mogdb-kernel-0004:biimenu->softbank060132034173.bbtec.net:45214 (ESTABLISHED)
mogdb   12027 omm3  375u  IPv4 28305050      0t0  TCP mogdb-kernel-0004:biimenu->softbank060132034173.bbtec.net:45216 (ESTABLISHED)

如何获取线程ID

可以使用htop,打开htop界面以后,按F5进行进程树形显示,第一个PID是进程号,而树形结构下的每一行的PID都是该进程中对应的线程号。

也可以直接使用ps命令。-L参数表示显示线程,-o参数可以指定显示感兴趣的列。

# ps -Lp `pgrep -u omm3 mogdb` -o %cpu,tid,pid,ppid,cmd,comm
%CPU   TID   PID  PPID CMD                         COMMAND
 0.0 17847 17847     1 /opt/mogdb3/app/bin/mogdb - mogdb
 0.0 17848 17847     1 /opt/mogdb3/app/bin/mogdb - jemalloc_bg_thd
 0.0 17854 17847     1 /opt/mogdb3/app/bin/mogdb - mogdb
 0.0 17855 17847     1 /opt/mogdb3/app/bin/mogdb - syslogger
 0.0 17856 17847     1 /opt/mogdb3/app/bin/mogdb - reaper
 0.0 17857 17847     1 /opt/mogdb3/app/bin/mogdb - jemalloc_bg_thd
 0.0 17858 17847     1 /opt/mogdb3/app/bin/mogdb - jemalloc_bg_thd
 0.0 17860 17847     1 /opt/mogdb3/app/bin/mogdb - jemalloc_bg_thd
 0.0 17884 17847     1 /opt/mogdb3/app/bin/mogdb - checkpointer
 0.0 17885 17847     1 /opt/mogdb3/app/bin/mogdb - Spbgwriter
 0.1 17886 17847     1 /opt/mogdb3/app/bin/mogdb - pagewriter
 0.0 17887 17847     1 /opt/mogdb3/app/bin/mogdb - pagewriter
 0.0 17888 17847     1 /opt/mogdb3/app/bin/mogdb - pagewriter
 0.0 17889 17847     1 /opt/mogdb3/app/bin/mogdb - pagewriter
 0.0 17890 17847     1 /opt/mogdb3/app/bin/mogdb - pagewriter
 0.8 17891 17847     1 /opt/mogdb3/app/bin/mogdb - WALwriter
 0.0 17892 17847     1 /opt/mogdb3/app/bin/mogdb - WALwriteraux
 0.0 17893 17847     1 /opt/mogdb3/app/bin/mogdb - AVClauncher
 0.0 17894 17847     1 /opt/mogdb3/app/bin/mogdb - Jobscheduler
 0.0 17895 17847     1 /opt/mogdb3/app/bin/mogdb - asyncundolaunch
 0.0 17896 17847     1 /opt/mogdb3/app/bin/mogdb - globalstats
 0.0 17897 17847     1 /opt/mogdb3/app/bin/mogdb - applylauncher
 0.0 17898 17847     1 /opt/mogdb3/app/bin/mogdb - statscollector
 0.0 17899 17847     1 /opt/mogdb3/app/bin/mogdb - snapshotworker
 0.1 17900 17847     1 /opt/mogdb3/app/bin/mogdb - TrackStmtWorker
 0.0 17901 17847     1 /opt/mogdb3/app/bin/mogdb - 2pccleaner
 0.0 17902 17847     1 /opt/mogdb3/app/bin/mogdb - faultmonitor
 0.0 17904 17847     1 /opt/mogdb3/app/bin/mogdb - undorecycler
 0.0 18372 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18373 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18374 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18375 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18376 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18377 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18378 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18379 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18380 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18381 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18382 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18454 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 19475 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 19480 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 29529 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 30999 17847     1 /opt/mogdb3/app/bin/mogdb - worker

comm列中显示为woker的都是用户会话的数据库端后台进程,通常占用CPU较高的会是用户会话,因此可以用grep命令来过滤只显示用户会话。

#  ps -Lp `pgrep -u omm3 mogdb` -o %cpu,tid,pid,ppid,cmd,comm | grep -w worker
 0.0 18372 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18373 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18374 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18375 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18376 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18377 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18378 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18379 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18380 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18381 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18382 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 18454 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 19475 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 19480 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 29529 17847     1 /opt/mogdb3/app/bin/mogdb - worker
 0.0 30999 17847     1 /opt/mogdb3/app/bin/mogdb - worker

如何将操作系统线程ID跟数据库会话对应

假设在这台服务器上,线程ID=18372的线程占用了大量CPU。

在MogDB里查询pg_os_threads视图,可以获得该线程在数据库中对应的会话ID 。

MogDB=# select * from pg_os_threads where lwpid=18372;
 node_name |       pid       | lwpid | thread_name |         creation_time
-----------+-----------------+-------+-------------+-------------------------------
 dn_6001   | 140545137571584 | 18372 | dn_6001     | 2022-05-30 19:54:42.459129+08
(1 row)

pg_os_threads视图里记录了轻量级线程号和会话ID的关系,其中lwpid是操作系统线程ID,pid列是数据库会话ID。具体信息可以参考MogDB文档-PG_OS_THREADS
如果具备monadmin权限,也可以查询dbe_perf模式下的os_threads视图,信息是一样的。

找到数据库会话ID之后,就可以为所欲为了,比如可以查询dbe_perf.session_stat_activity视图来获取该会话的应用名,发起的客户端IP地址,还有该会话正在执行的SQL。

MogDB=# select application_name,client_addr,query from dbe_perf.session_stat_activity where pid=140545137571584;
 application_name | client_addr  |                          query
------------------+--------------+---------------------------------------------------------
 dn_6001          | 172.16.0.176 | SELECT cfg_value FROM bmsql_config  WHERE cfg_name = $1
(1 row)

也可以查询dbe_perf.thread_wait_status视图来获取会话的当前等待事件。BTW,实际上该视图中有lwtid字段,可以直接对应到线程ID。

MogDB=# select lwtid,wait_status,wait_event from dbe_perf.thread_wait_status where sessionid=140545137571584;
 lwtid | wait_status | wait_event
-------+-------------+------------
 18372 | wait cmd    | wait cmd
(1 row)