使用临时表导致系统登录慢的问题

适用版本

OceanBase 数据库 V3.2.4 BP5（oceanbase-3.2.4.5-105000012023081513）之前的版本。

问题现象

集群中存在业务租大量使用临时表（CGTT）的情况，业务租户登录很慢，甚至无法登录，OBProxy 日志中 COM_LOGIN 耗时很久或报 -4152 错误。

OB_MYSQL,,,COM_LOGIN,,failed,-4152,,1233729us,0us,0us,0us,Y0-7F9A1447AD60,,,Null value

问题原因

临时表中的数据只对本 session 可见，其生命周期随着 session 断开而终止。在 OceanBase 数据库 V3.2.4 BP5（oceanbase-3.2.4.5-105000012023081513）之前，由于 session id 可能存在复用的情况，会在登陆时对当前 session id 的数据进行检查，如果存在则需要额外进行一次清理。当同样的 session id 曾经执行过大量的临时表时，清理动作耗时较久，会导致登录缓慢甚至无法登录的问题。

对于因清理临时表导致登录缓慢的问题，可以从 observer.log 中分析判断，如下示例。

1. 19:42:11.23791 时租户 ttt 上有一次成功的登录，登录 session 的 proxy_sessid=65523。

   [2023-05-11 19:42:11.237915] INFO  [SERVER] obmp_connect.cpp:374 [3115205][0][Y0-0000000000000000-0-0] [lt=10] [dc=0] MySQL LOGIN(direct_client_ip="xx.xx.xx.15", client_ip=xx.xx.xx.14, tenant_name=ttt, tenant_id=1001, user_name=xxx, host_name=%, sessid=3224142387, proxy_sessid=65523, sess_create_time=0, from_proxy=true, from_java_client=false, capability=681288327, proxy_capability=2631, use_ssl=false, c/s protocol="OB_MYSQL_CS_TYPE", proc_ret=0, ret=0)
   

2. 搜索之前的日志，发现大量的清理临时表的操作 DELETE FROM TMP_TB WHERE SYS_SESSION_ID = 65523。

   [2023-05-11 19:41:51.517947] INFO  [SQL.SESSION] ob_sql_session_info.cpp:660 [3115205][0][Y0-0000000000000000-0-0] [lt=14] [dc=0] succeed to delete rows in oracle temporary table(sql=DELETE FROM "XXX"."XXX_TMP1" WHERE SYS_SESSION_ID = 65523 AND SYS_SESS_CREATE_TIME <> 1683805307139382 AND ROWNUM <= 1000, affect_rows=0)
   
   [2023-05-11 19:41:54.102287] INFO  [SERVER] ob_inner_sql_connection.cpp:706 [3115205][0][YB421E164672-0005F9E7619A936D-0-0] [lt=15] [dc=0] slow inner sql(last_ret=0, sql={ObIExecutor:, sql:"DELETE FROM __all_column_stat_v2 WHERE  tenant_id = 0 and table_id = 111783 ;"}, process_time=2560769)
   
   [2023-05-11 19:41:55.210163] INFO  [SERVER] ob_inner_sql_connection.cpp:706 [3115205][0][YB421E164672-0005F9E7619A936E-0-0] [lt=28] [dc=0] slow inner sql(last_ret=0, sql={ObIExecutor:, sql:"DELETE FROM __all_histogram_stat_v2 WHERE  tenant_id = 0 and table_id = 111783 ;"}, process_time=1107736)
   
   [2023-05-11 19:41:56.440965] INFO  [SERVER] ob_inner_sql_connection.cpp:706 [3115205][0][YB421E164672-0005F9E7619A9370-0-0] [lt=15] [dc=0] slow inner sql(last_ret=0, sql={ObIExecutor:, sql:"DELETE FROM "XXX"."XXX_TMP2" WHERE SYS_SESSION_ID = 65523 AND SYS_SESS_CREATE_TIME <> 1683805307139382 AND ROWNUM <= 1000"}, process_time=1228185)
   
   [2023-05-11 19:41:56.441142] INFO  [SQL.SESSION] ob_sql_session_info.cpp:660 [3115205][0][Y0-0000000000000000-0-0] [lt=14] [dc=0] succeed to delete rows in oracle temporary table(sql=DELETE FROM "XXX"."XXX_TMP2" WHERE SYS_SESSION_ID = 65523 AND SYS_SESS_CREATE_TIME <> 1683805307139382 AND ROWNUM <= 1000, affect_rows=0)
   
   [2023-05-11 19:41:57.942354] INFO  [SERVER] ob_inner_sql_connection.cpp:706 [3115205][0][YB421E164672-0005F9E7619A9376-0-0] [lt=29] [dc=0] slow inner sql(last_ret=0, sql={ObIExecutor:, sql:"DELETE FROM "XXX"."XXX_TMP3" WHERE SYS_SESSION_ID = 65523 AND SYS_SESS_CREATE_TIME <> 1683805307139382 AND ROWNUM <= 1000"}, process_time=1238371)
   
   [2023-05-11 19:41:57.942612] INFO  [SQL.SESSION] ob_sql_session_info.cpp:660 [3115205][0][Y0-0000000000000000-0-0] [lt=34] [dc=0] succeed to delete rows in oracle temporary table(sql=DELETE FROM "XXX"."XXX_TMP3" WHERE SYS_SESSION_ID = 65523 AND SYS_SESS_CREATE_TIME <> 1683805307139382 AND ROWNUM <= 1000, affect_rows=0)
   

解决方法

• 升级到 OceanBase 数据库 V3.2.4 BP5（oceanbase-3.2.4.5-105000012023081513）。

• 在 OceanBase 数据库 V3.2.4 BP5（oceanbase-3.2.4.5-105000012023081513）之前，尽量避免使用临时表，使用普通表来代替。