This section describes how to query SQL statements whose execution takes a long time, leading to poor system performance.
gsql -d postgres -p 8000
postgres is the name of the database, and 8000 is the port number of the CN.
If information similar to the following is displayed, the connection has been established:
gsql ((Gauss200 V100R008C00 build 50dc16a6) compiled at 2018-03-02 05:49:21 commit 1071 last mr 1373) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. postgres=#
SELECT current_timestamp - query_start AS runtime, datname, usename, query FROM pg_stat_activity where state != 'idle' ORDER BY 1 desc;
After the query, query statements are returned as a list, ranked by execution time in descending order. The first result is the query statement that has the longest execution time in the system. The returned result contains the SQL statement invoked by the system and the SQL statement run by users. Find the statements that were run by users and took a long time.
SELECT query FROM pg_stat_activity WHERE current_timestamp - query_start > interval '1 days';
SET track_activities = on;
The database collects the running information about active queries only if the parameter is set to on.
Viewing pg_stat_activity is used as an example here.
SELECT datname, usename, state FROM pg_stat_activity; datname | usename | state | ----------+---------+--------+ postgres | omm | idle | postgres | omm | active | (2 rows)
If the state column is idle, the connection is idle and requires a user to enter a command.
To identify only active query statements, run the following command:
SELECT datname, usename, state FROM pg_stat_activity WHERE state != 'idle';
SELECT datname, usename, state, query FROM pg_stat_activity WHERE waiting = true;
The command output lists a query statement in the block state. The lock resource requested by this query statement is occupied by another session, so this query statement is waiting for the session to release the lock resource.
Only when the query is blocked by internal lock resources, the waiting field is true. In most cases, block happens when query statements are waiting for lock resources to be released. However, query statements may be blocked because they are waiting to write in files or for timers. Such blocked queries are not displayed in the pg_stat_activity view.