慢SQL诊断

背景信息

在SQL语句执行性能不符合预期时，可以查看SQL语句执行信息，便于事后分析SQL语句执行时的行为，从而诊断SQL语句执行出现的相关问题。

前提条件

  • 数据库实例运行正常。
  • 查询SQL语句信息，需要正确设置GUC参数track_stmt_stat_level。
  • 只能用系统管理员和监控管理员权限进行操作。
  1. 执行命令查看数据库实例中SQL语句执行信息
  2. select * from dbe_perf.get_global_full_sql_by_timestamp(start_timestamp, end_timestamp); 
  3. 例如：
  4. select * from DBE_PERF.get_global_full_sql_by_timestamp('2020-12-01 09:25:22', '2020-12-31 23:54:41');
  5. -[ RECORD 1 ]--------+---------------------------------------------------------------------------------------------------------------
  6. ---------------------------------------------------------------
  7. node_name            | dn_6001_6002_6003
  8. db_name              | postgres
  9. schema_name          | "$user",public
  10. origin_node          | 1938253334
  11. user_name            | user_dj
  12. application_name     | gsql
  13. client_addr          |
  14. client_port          | -1
  15. unique_query_id      | 3671179229
  16. debug_query_id       | 72339069014839210
  17. query                | select name, setting from pg_settings where name in (?)
  18. start_time           | 2020-12-19 16:19:51.216818+08
  19. finish_time          | 2020-12-19 16:19:51.224513+08
  20. slow_sql_threshold   | 1800000000
  21. transaction_id       | 0
  22. thread_id            | 139884662093568
  23. session_id           | 139884662093568
  24. n_soft_parse         | 0
  25. n_hard_parse         | 1
  26. query_plan           | Datanode Name: dn_6001_6002_6003
  27.                      | Function Scan on pg_show_all_settings a  (cost=0.00..12.50 rows=5 width=64)
  28.                      |   Filter: (name = '***'::text)
  29. ...
  31. 执行命令查看数据库实例中慢SQL语句执行信息
  32. select * from dbe_perf.get_global_slow_sql_by_timestamp(start_timestamp, end_timestamp);
  33. 例如：
  34. select * from DBE_PERF.get_global_slow_sql_by_timestamp('2020-12-01 09:25:22', '2020-12-31 23:54:41');
  35. -[ RECORD 1 ]--------+---------------------------------------------------------------------------------------------------
  36. node_name            | dn_6001_6002_6003
  37. db_name              | postgres
  38. schema_name          | "$user",public
  39. origin_node          | 1938253334
  40. user_name            | user_dj
  41. application_name     | gsql
  42. client_addr          |
  43. client_port          | -1
  44. unique_query_id      | 2165004317
  45. debug_query_id       | 72339069014839319
  46. query                | select * from DBE_PERF.get_global_slow_sql_by_timestamp(?, ?);
  47. start_time           | 2020-12-19 16:23:20.738491+08
  48. finish_time          | 2020-12-19 16:23:20.773714+08
  49. slow_sql_threshold   | 10000
  50. transaction_id       | 0
  51. thread_id            | 139884662093568
  52. session_id           | 139884662093568
  53. n_soft_parse         | 10
  54. n_hard_parse         | 8
  55. query_plan           | Datanode Name: dn_6001_6002_6003
  56.                      | Result  (cost=1.01..1.02 rows=1 width=0)
  57.                      |   InitPlan 1 (returns $0)
  58.                      |     ->  Seq Scan on pgxc_node  (cost=0.00..1.01 rows=1 width=64)
  59.                      |           Filter: (nodeis_active AND ((node_type = '***'::"char") OR (node_type = '***'::"char")))
  60. ...
  62. 查看当前主节点SQL语句执行信息
  63. select * from statement_history;
  64. 例如：
  65. select * from statement_history;
  66. -[ RECORD 1 ]--------+---------------------------------------------------------------------------------------------------------------
  67. ---------------------------------------------------------------
  68. db_name              | postgres
  69. schema_name          | "$user",public
  70. origin_node          | 1938253334
  71. user_name            | user_dj
  72. application_name     | gsql
  73. client_addr          |
  74. client_port          | -1
  75. unique_query_id      | 3671179229
  76. debug_query_id       | 72339069014839210
  77. query                | select name, setting from pg_settings where name in (?)
  78. start_time           | 2020-12-19 16:19:51.216818+08
  79. finish_time          | 2020-12-19 16:19:51.224513+08
  80. slow_sql_threshold   | 1800000000
  81. transaction_id       | 0
  82. thread_id            | 139884662093568
  83. session_id           | 139884662093568
  84. n_soft_parse         | 0
  85. n_hard_parse         | 1
  86. query_plan           | Datanode Name: dn_6001_6002_6003
  87.                      | Function Scan on pg_show_all_settings a  (cost=0.00..12.50 rows=5 width=64)
  88.                      |   Filter: (name = '***'::text)
  89. ...
  91. 查看当前备节点SQL语句执行信息
  92. select * from dbe_perf.standby_statement_history(is_only_slow, start_timestamp, end_timestamp);
  93. 例如：
  94. select * from dbe_perf.standby_statement_history(true, '2022-08-01 09:25:22', '2022-08-31 23:54:41');
  95. db_name              | postgres
  96. schema_name          | "$user",public
  97. origin_node          | 0
  98. user_name            | user_dj
  99. application_name     | gsql
  100. client_addr          |
  101. client_port          | -1
  102. unique_query_id      | 1660376009
  103. debug_query_id       | 281474976710740
  104. query                | select name, setting from pg_settings where name in (?)
  105. start_time           | 2022-08-19 16:19:51.216818+08
  106. finish_time          | 2022-08-19 16:19:51.224513+08
  107. slow_sql_threshold   | 1800000000
  108. transaction_id       | 0
  109. thread_id            | 140058747205376
  110. session_id           | 140058747205376
  111. n_soft_parse         | 0
  112. n_hard_parse         | 1
  113. query_plan           | Datanode Name: sgnode
  114.                      | Function Scan on pg_show_all_settings a  (cost=0.00..12.50 rows=5 width=64)
  115.                      |   Filter: (name = '***'::text)
  116. ...