Drop in performance? Expose the real culprit through temporary files.

During routine system maintenance, Xiao Ming noticed that the performance of report generation was always abnormally degrading during a certain time period. After preliminary investigation, he found that the backend database was slowing down in returning results, causing the frontend business response speed to decrease.

The backend of this system was a data warehouse built with GBase 8a MPP Cluster, storing hundreds of terabytes of data. Many of the company's analytical workloads were supported by this data warehouse, so troubleshooting was understandably challenging. After several unsuccessful attempts to solve the issue on his own, Xiao Ming promptly reached out to the GBase technical team.

Technical expert Xiao Ji rushed to the scene upon receiving the request. After discussing with Xiao Ming, he learned that the performance degradation always occurred during a specific time window. So, when the performance dropped again, Xiao Ji started monitoring key database metrics such as CPU, memory, and disk I/O, and quickly discovered that disk I/O was exceptionally busy during that period.

Drawing on his extensive experience, Xiao Ji quickly deduced that it was likely due to some complex SQL queries producing excessively large intermediate result sets, which generated numerous temporary files in the /opt/gnode/tmpdata directory.These temporary files not only can double query response latency, but in severe cases may also fill up the disk space, paralyzing the entire cluster.

Sure enough, in the /opt/gnode/tmpdata directory, Xiao Ji found a large number of continuously growing temporary files. What was worse, dozens of complex SQL queries were executing concurrently. In such a scenario, using the traditional method of checking one by one via show processlist was like looking for a needle in a haystack.

suse103:/opt/gnode/tmpdata/cache_gbase # pwd/opt/gnode/tmpdata/cache_gbasesuse103:/opt/gnode/tmpdata/cache_gbase # lltotal 75497472-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT00000001965940xa3d8500.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT00000001CCF3A0xa3a61c0.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT00000002D97070x38ae3c0.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT0000000753F980x9626440.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT0000000FB81C50xbd28000.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT00000013199B0x17fc7f40.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT00000026185130x619a6c0.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT000000268BA140x3d14440.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT00000030F188E0x9626300.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT00000035DD840x17fc7e00.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT00000057D15420x38aea00.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT0000005C49BAC0x3d12a00.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT0000005F38C290xbd28140.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT00000069942570x619bac0.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT00000069F92D60xa3dbe80.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:25 GB_MAT00000078F35830xa3dbd40.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:26 GB_MAT00000101965940xa3d8500.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:26 GB_MAT00000101CCF3A0xa3a61c0.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:26 GB_MAT00000102D97070x38ae3c0.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:26 GB_MAT00000130F188E0x9626300.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:26 GB_MAT00000135DD840x17fc7e00.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:26 GB_MAT00000157D15420x38aea00.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:26 GB_MAT0000015C49BAC0x3d12a00.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:26 GB_MAT0000015F38C290xbd28140.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:26 GB_MAT00000169942570x619bac0.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:26 GB_MAT00000169F92D60xa3dbe80.express_tmp-rw-rw---- 1 gbase gbase 1979711488 Nov 8 14:26 GB_MAT00000178F35830xa3dbd40.express_tmp-rw-rw---- 1 gbase gbase 469762048 Nov 8 14:27 GB_MAT00000201965940xa3d8500.express_tmp-rw-rw---- 1 gbase gbase 469762048 Nov 8 14:27 GB_MAT00000201CCF3A0xa3a61c0.express_tmp-rw-rw---- 1 gbase gbase 402653184 Nov 8 14:27 GB_MAT00000202D97070x38ae3c0.express_tmp-rw-rw---- 1 gbase gbase 469762048 Nov 8 14:27 GB_MAT0000020753F980x9626440.express_tmp-rw-rw---- 1 gbase gbase 436207616 Nov 8 14:27 GB_MAT0000020FB81C50xbd28000.express_tmp-rw-rw---- 1 gbase gbase 436207616 Nov 8 14:27 GB_MAT00000213199B0x17fc7f40.express_tmp-rw-rw---- 1 gbase gbase 436207616 Nov 8 14:27 GB_MAT00000226185130x619a6c0.express_tmp-rw-rw---- 1 gbase gbase 469762048 Nov 8 14:27 GB_MAT00000269F92D60xa3dbe80.express_tmp-rw-rw---- 1 gbase gbase 469762048 Nov 8 14:27 GB_MAT00000278F35830xa3dbd40.express_tmpdrwxrwx--x 2 gbase gbase 6 Nov 1 17:34 HashJoindrwxrwx--x 2 gbase gbase 6 Nov 8 13:58 TrashCandrwxrwx--x 3 gbase gbase 21 Nov 1 17:34 tmp_materializedsuse103:/opt/gnode/tmpdata # pwd/opt/gnode/tmpdatasuse103:/opt/gnode/tmpdata # du -sh *97G cache_gbasesuse103:/opt/gnode/tmpdata #

System Performance Anomaly: GBase Captures the Culprit

Facing tens of thousands of temporary files, how to quickly pinpoint the "culprit SQL" became the key to solving the problem.

Xiao Ji took decisive action. He first queried the session memory_usage_info table in performance_schema. This table plays an important role at the GN layer of the GBase 8a database. The temp_space field indicates the size of tmpdata space used, and the ID field represents the SQL execution ID (corresponding to the first field "ID" in show processlist).

gbase> select * from performance_schema.SESSION_MEMORY_USAGE_INFO where TEMP_SPACE>0;+---------+-----+---------+------+---------------------+-------------+| HOST | ID | CURRENT | PEAK | PEAK_TIMESTAMP | TEMP_SPACE |+---------+-----+---------+------+---------------------+-------------+| suse103 | 342 | 0 | 0 | 1970-01-01 08:00:00 | 86872424448 |+---------+-----+---------+------+---------------------+-------------+1 row in set (Elapsed: 00:00:00.00)gbase>

By querying this table, Xiao Ji obtained the ID of the SQL consuming a large amount of temporary space. Then, he found the corresponding SQL statement in the show processlist at the GN layer.

suse103:~ # gncli -uroot -e"show full processlist" | grep 342342 root 10.10.10.103:35972 ssbm Query 224 init SELECT /*10.10.10.103_179_2_2016-11-08_14:24:21*/ /*+ TID('78') */ `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_orderkey` AS `lo_orderkey`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_linenumber` AS `lo_linenumber`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_custkey` AS `lo_custkey`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_partkey` AS `lo_partkey`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_suppkey` AS `lo_suppkey`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_orderdate` AS `lo_orderdate`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_orderpriority` AS `lo_orderpriority`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_shippriority` AS `lo_shippriority`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_quantity` AS `lo_quantity`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_extendedprice` AS `lo_extendedprice`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_ordtotalprice` AS `lo_ordtotalprice`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_discount` AS `lo_discount`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_revenue` AS `lo_revenue`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_supplycost` AS `lo_supplycost`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_tax` AS `lo_tax`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_commitdate` AS `lo_commitdate`, `_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_shipmode` AS `lo_shipmode`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c1` AS `c1`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c2` AS `c2`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c3` AS `c3`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c4` AS `c4`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c5` AS `c5`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c6` AS `c6`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c7` AS `c7`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c8` AS `c8`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c9` AS `c9`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c10` AS `c10`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c11` AS `c11`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c12` AS `c12`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c13` AS `c13`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c14` AS `c14`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c15` AS `c15`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c16` AS `c16`, `_tmp_rht_n4_179_t9_2_1477982902_s`.`c17` AS `c17` FROM `gctmpdb`._tmp_rht_n4_179_t9_1_1477982902_s INNER JOIN `gctmpdb`._tmp_rht_n4_179_t9_2_1477982902_s ON (`_tmp_rht_n4_179_t9_1_1477982902_s`.`lo_linenumber` = `_tmp_rht_n4_179_t9_2_1477982902_s`.`c2`)suse103:~ #

Based on specific markers in the SQL statement from the node layer (such as IP address, timestamp, etc.) or the temporary table name, he further found the SQL session ID at the GC layer, successfully pinpointing the concrete SQL statement—the "culprit" causing the system performance degradation.

suse103:~ # gccli -uroot -e"show full processlist" | grep -v Sleep | grep 179 179 root 127.0.0.1:63945 ssbm Query 571 Sending task to gnodes select * from lineorder a,lineorder b where a.lo_linenumber=b.lo_linenumber

Finally, after in-depth communication with the business department to fully understand the business requirements, Xiao Ji assisted them in rewriting the SQL statement, successfully resolving the performance degradation issue and restoring normal system performance.

GBase Quick Tips

When executing SQL queries with large intermediate result sets on-site, a large number of temporary files may be generated under the tmpdata folder, causing heavy disk I/O and impacting overall cluster performance. If there are many concurrent SQL queries, using show processlist to troubleshoot is inefficient. How can we quickly and accurately identify the corresponding SQL by using the temporary files under tmpdata?

In this case, at the GN layer of the GBase 8a database, you can query the session memory_usage_info table in performance_schema.

gbase> select * from performance_schema.SESSION_MEMORY_USAGE_INFO;+---------+-----+---------+------+---------------------+------------+| HOST | ID | CURRENT | PEAK | PEAK_TIMESTAMP | TEMP_SPACE |+---------+-----+---------+------+---------------------+------------+| suse103 | 344 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 342 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 324 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 289 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 274 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 273 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 241 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 215 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 214 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 213 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 132 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 52 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 31 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 26 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 25 | 0 | 0 | 1970-01-01 08:00:00 | 0 || suse103 | 17 | 0 | 0 | 1970-01-01 08:00:00 | 0 |+---------+-----+---------+------+---------------------+------------+16 rows in set (Elapsed: 00:00:00.00)gbase> select * from performance_schema.SESSION_MEMORY_USAGE_INFO where TEMP_SPACE>0;Empty set (Elapsed: 00:00:00.00)

The temp_space field indicates the size of the tmpdata space used, and the ID field represents the SQL execution ID (corresponding to the first field "ID" in show processlist).

You can find the corresponding SQL in the show processlist at the GN layer, and then locate the corresponding GC layer SQL based on the node layer SQL.