When the MogDB database consumes a significant amount of system resources, such as nearly 100% CPU usage, how can we determine which session(s) in the database are using these resources?
In Oracle databases, diagnosing such issues typically involves associating v$session, v$process, and the OS process ID found using the top or ps commands. However, MogDB uses a thread model, and only one process ID is visible at the OS level. So, how do we pinpoint the problem?
Since MogDB uses a thread model, unlike PostgreSQL’s process model, checking network port access with the lsof command at the OS level will show multiple user sessions connected, but only the process ID will be displayed in the PID column, not the thread.
You can use htop. After opening htop, press F5 to display the process tree. The first PID is the process ID, and each line under the tree structure shows the corresponding thread ID for that process.
You can also use the ps command. The -L parameter displays threads, and the -o parameter specifies the columns of interest.
The rows in the comm column that display as “worker” are backend processes of user sessions in the database. Typically, user sessions that consume high CPU can be filtered to show only user sessions using the grep command.
How to Map OS Thread ID to Database Session
Suppose on this server, the thread with ID 18372 is consuming a lot of CPU. In MogDB, you can query the pg_os_threads view to find the session ID corresponding to this thread.
1 2 3 4 5 6
MogDB=# select*from pg_os_threads where lwpid=18372;
The pg_os_threads view records the relationship between lightweight thread IDs and session IDs. The lwpid column is the OS thread ID, and the pid column is the database session ID. For detailed information, refer to the MogDB documentation on PG_OS_THREADS.
If you have monadmin privileges, you can also query the os_threads view in the dbe_perf schema, which provides the same information.
After finding the database session ID, you can perform various actions, such as querying the dbe_perf.session_stat_activity view to get the application name, client IP address, and the SQL query being executed by that session.
1 2 3 4 5 6
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 (1row)
You can also query the dbe_perf.thread_wait_status view to get the current wait event of the session. Note that this view contains the lwtid field, which directly corresponds to the thread ID.
1 2 3 4 5 6
MogDB=# select lwtid, wait_status, wait_event from dbe_perf.thread_wait_status where sessionid=140545137571584;
