Using PGXC_STAT_ACTIVITY to Analyze SQL Statements That Are Being Executed to Handle DWS Performance Issues

Viewing Connection Information

• Set track_activities to on.

  SET track_activities = on;

  The database collects the running information about active queries only if this parameter is set to on.

• You can run the following SQL statements to check the current connection user, connection address, connection application, status, whether to wait for a lock, queuing status, and thread ID.

  1	SELECT coorname, usename,client_addr,application_name,state,waiting,enqueue,pid FROM PGXC_STAT_ACTIVITY WHERE DATNAME='Database name';

  The following command output is displayed:

  

  1	usename | client_addr | application_name | state | waiting | enqueue | pid

• End a session (only the system administrator has the permission). cn_name is the coorname value in the preceding command output, and pid is the corresponding PID.

  1	EXECUTE DIRECT ON(cn_name) 'SELECT pg_terminate_backend(pid)';

Viewing SQL Running Information

• Run the following command to obtain all SQL information that the current user has permission to view (if the current user has administrator or preset role permission, all user query information can be displayed):

  1	SELECT usename,state,query FROM PGXC_STAT_ACTIVITY WHERE DATNAME='Database name';

  If the value of state is active, the query column indicates the SQL statement that is being executed. In other cases, the query column indicates the previous query statement. If the value of state is idle, the connection is idle and waits for the user to enter a command. The following command output is displayed:

  1 2 3 4 5 6

  usename | state | query ---------+--------+--------------------------------------------------------------------------- leo | idle | select * from joe.mytable; dbadmin | active | SELECT usename,state,query FROM PGXC_STAT_ACTIVITY WHERE DATNAME='gaussdb'; joe | idle | GRANT SELECT ON TABLE mytable to leo; (3 rows)

Viewing Time-Consuming Statements

• Check the SQL statements that take a long time to execute.

  1	SELECT current_timestamp - query_start as runtime, datname, usename, query FROM PGXC_STAT_ACTIVITY WHERE state != 'idle' order by 1 desc;

  Query statements are returned and sorted by execution time length in descending order. The first record is the query statement that takes the longest time to execute.

  1 2 3 4 5

  runtime | datname | usename | query -----------------+----------+---------+----------------------------------------------------------------------------------------------------------------------------------------- 00:04:47.054958 | gaussdb | leo | insert into mytable1 select generate_series(1, 10000000); 00:00:01.72789 | gaussdb | dbadmin | SELECT current_timestamp - query_start as runtime, datname, usename, query FROM PGXC_STAT_ACTIVITY WHERE state != 'idle' order by 1 desc; (2 rows)

• Alternatively, you can set current_timestamp - query_start to be greater than a threshold to identify query statements that are executed for a duration longer than this threshold.

  1	SELECT query from PGXC_STAT_ACTIVITY WHERE current_timestamp - query_start > interval '2 days';

Querying Blocked Statements

• Run the following command to view blocked query statements:

  1	SELECT coorname, pid, datname, usename, state, query FROM PGXC_STAT_ACTIVITY WHERE state <> 'idle' and waiting=true;

  Run the following statement to end the blocked SQL session:

  1	EXECUTE DIRECT ON(cn_name) 'SELECT pg_terminate_backend(pid)';

  

  • In most cases, blocking is caused by internal locks and waiting=true is displayed. You can view the blocking in the pgxc_stat_activity view.
  • The blocked statements about file write and event schedulers cannot be viewed in the pgxc_stat_activity view.
• View information about the blocked query statements, tables, and schemas:

  1 2 3 4 5 6 7 8 9 10 11 12

  SELECT w.query as waiting_query, w.coorname as w_cn, w.pid as w_pid, w.usename as w_user, l.query as locking_query, l.pid as l_pid, l.usename as l_user, t.schemaname || '.' || t.relname as tablename from pgxc_stat_activity w join pg_locks l1 on w.pid = l1.pid and not l1.granted join pg_locks l2 on l1.relation = l2.relation and l2.granted join pgxc_stat_activity l on l2.pid = l.pid join pg_stat_user_tables t on l1.relation = t.relid where w.waiting;

  The session ID, CN name, user information, query status, as well as information about the tables and schemas that block the query statements are returned.

  After finding the blocked table or schema information, end the faulty session based on the session ID.

  1	EXECUTE DIRECT ON(cn_name) 'SELECT pg_terminate_backend(pid)';

  If t or true is returned, the session is ended.

  If information similar to the following is returned, the user is attempting to terminate the session, but the session will be reconnected rather than terminated.

  FATAL:  terminating connection due to administrator command 
  FATAL:  terminating connection due to administrator command 
  The connection to the server was lost. Attempting reset: Succeeded.

  

  If the PG_TERMINATE_BACKEND function is used by the gsql client to terminate the background threads of the session, the client will be reconnected automatically rather than be terminated.