User Resource Monitoring

Important Notes

• The CPU, I/O, and memory usage of all jobs on fast and slow lanes (simple jobs on fast lanes and complex jobs on slow lanes) can be monitored.
• Currently, fast lane jobs have no memory or CPU limits. They may use too many resources and go over the resource limit.
• In the DN monitoring view, I/O, memory, and CPU display the resource usage and limits of resource pools.
• In the CN monitoring view, I/O, memory, and CPU display the total resource usage and limit of all DN resource pools in the cluster.
• The DN monitoring information is updated every 5 seconds. CNs collect monitoring information from DNs every 5 seconds. Because each instance updates or collects user monitoring information independently, the monitoring information update time on each instance may be different.
• The auxiliary thread automatically invokes the persistence function every 30 seconds to make user monitoring data persistent. So, normally, you don't have to do this.
• When there are a large number of users and a large cluster, querying such real-time views will cause network latency due to the real-time communication overhead between CNs and DNs.
• Resources are not monitored for an initial administrator.

Procedure

• Query all users' resource quotas and real-time resource usage.

  1	SELECT * FROM PG_TOTAL_USER_RESOURCE_INFO;

  The result view is as follows:

  1 2 3 4 5 6 7

  username | used_memory | total_memory | used_cpu | total_cpu | used_space | total_space | used_temp_space | total_temp_space | used_spill_space | total_spill_space | read_kbytes | write_kbytes | read_counts | write_counts | read_speed | write_speed -----------------------+-------------+--------------+----------+-----------+------------+-------------+-----------------+------------------+------------------+-------------------+-------------+--------------+-------------+--------------+------------+------------- perfadm | 0 | 0 | 0 | 0 | 0 | -1 | 0 | -1 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 usern | 0 | 17250 | 0 | 48 | 0 | -1 | 0 | -1 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 userg | 34 | 15525 | 23.53 | 48 | 0 | -1 | 0 | -1 | 814955731 | -1 | 6111952 | 1145864 | 763994 | 143233 | 42678 | 8001 userg1 | 34 | 13972 | 23.53 | 48 | 0 | -1 | 0 | -1 | 814972419 | -1 | 6111952 | 1145864 | 763994 | 143233 | 42710 | 8007 (4 rows)

  The I/O resource monitoring fields (read_kbytes, write_kbytes, read_counts, write_counts, read_speed, and write_speed) can be available only when the GUC parameter enable_user_metric_persistent is enabled.

  For details about each column, see PG_TOTAL_USER_RESOURCE_INFO.

• Query a user's resource quota and real-time resource usage.

  1	SELECT * FROM GS_WLM_USER_RESOURCE_INFO('username');

  The query result is as follows:

  1 2 3 4

  userid | used_memory | total_memory | used_cpu | total_cpu | used_space | total_space | used_temp_space | total_temp_space | used_spill_space | total_spill_space | read_kbytes | write_kbytes | read_counts | write_counts | read_speed | write_speed --------+-------------+--------------+----------+-----------+------------+-------------+-----------------+------------------+------------------+-------------------+-------------+--------------+-------------+--------------+------------+------------- 16407 | 18 | 1655 | 6 | 19 | 13787176 | -1 | 0 | -1 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 (1 row)

• Query all users' resource quotas and historical resource usage.

  1	SELECT * FROM GS_WLM_USER_RESOURCE_HISTORY;

  The query result is as follows:

  1 2 3 4 5

  username | timestamp | used_memory | total_memory | used_cpu | total_cpu | used_space | total_space | used_temp_space | total_temp_space | used_spill_space | total_spill_space | read_kbytes | write_kbytes | read_counts | write_counts | read_speed | write_speed -----------------------+-------------------------------+-------------+--------------+----------+-----------+------------+-------------+-----------------+------------------+------------------+-------------------+-------------+--------------+-------------+--------------+-------------+------------- usern | 2020-01-08 22:56:06.456855+08 | 0 | 17250 | 0 | 48 | 0 | -1 | 0 | -1 | 88349078 | -1 | 45680 | 34 | 5710 | 8 | 320 | 0 userg | 2020-01-08 22:56:06.458659+08 | 0 | 15525 | 33.48 | 48 | 0 | -1 | 0 | -1 | 110169581 | -1 | 17648 | 23 | 2206 | 5 | 123 | 0 userg1 | 2020-01-08 22:56:06.460252+08 | 0 | 13972 | 33.48 | 48 | 0 | -1 | 0 | -1 | 136106277 | -1 | 17648 | 23 | 2206 | 5 | 123 | 0

  For the system catalog in GS_WLM_USER_RESOURCE_HISTORY, data in the PG_TOTAL_USER_RESOURCE_INFO view is periodically saved to historical tables only when the GUC parameter enable_user_metric_persistent is enabled.

  For details about each column, see GS_WLM_USER_RESOURCE_HISTORY.