GBase 8a数据库集群，当外部连接处于空闲状态(Sleep), 为了减少资源浪费，提供了Wait_timeout参数，Sleep超过该参数的连接会被自动kill掉。
目录导航
参数
Wait_timeout，interactive_timeout
默认值
1000000秒。
gbase> show variables like 'wait_timeout';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| wait_timeout | 1000000 |
+---------------+---------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| interactive_timeout | 1000000 |
+---------------------+-------+
1 row in set (Elapsed: 00:00:00.00)
参数使用样例
除了修改配置文件，重启集群外，还可以用过set方式进行动态设置。
Session级别
建议用wait_timeout即可
如下修改参数为3秒，稍等再执行SQL,可以发现其ID已经变化，从6变成了9，期间有重新连接的信息。
gbase> show processlist;
+----+-----------------+-----------+--------+---------+------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------+---------+------+-----------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 863 | Waiting for next activation | NULL |
| 6 | root | localhost | testdb | Query | 0 | NULL | show processlist |
+----+-----------------+-----------+--------+---------+------+-----------------------------+------------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> show variables like 'wait_timeout';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| wait_timeout | 1000000 |
+---------------+---------+
1 row in set (Elapsed: 00:00:00.00)
gbase> set wait_timeout=3;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 3 |
+---------------+-------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show processlist;
ERROR 2006 (HY000): GBase server has gone away
No connection. Trying to reconnect...
Connection id: 9
Current database: testdb
+----+-----------------+-----------+--------+---------+------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------+---------+------+-----------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 896 | Waiting for next activation | NULL |
| 9 | root | localhost | testdb | Query | 0 | NULL | show processlist |
+----+-----------------+-----------+--------+---------+------+-----------------------------+------------------+
2 rows in set (Elapsed: 00:00:00.01)
gbase>
全局参数global
需要设置另一个interactive_timeout，否则下次登录时，参数不生效。
gbase> set global wait_timeout=60;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
gbase> show variables like '%timeout%';
+------------------------------------+---------+
| Variable_name | Value |
+------------------------------------+---------+
| _gbase_crash_dump_timeout | 60 |
| _gbase_net_alive_timeout | 92 |
| connect_timeout | 1000000 |
| delayed_insert_timeout | 300 |
| gbase_export_write_timeout | 300 |
| gbase_hdfs_client_timeout | 600 |
| gbase_loader_read_timeout | 300 |
| gcluster_async_connect_timeout | 120 |
| gcluster_connect_net_read_timeout | 1000000 |
| gcluster_connect_net_write_timeout | 1000000 |
| gcluster_lock_timeout | 0 |
| gcluster_send_client_data_timeout | 30 |
| gcluster_task_status_poll_timeout | 6000 |
| gcluster_wait_query_cancel_timeout | 200 |
| interactive_timeout | 1000000 |
| net_read_timeout | 1000000 |
| net_write_timeout | 1000000 |
| node_peer_connect_timeout | 5 |
| node_peer_read_timeout | 0 |
| node_peer_write_timeout | 0 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 1000000 |
+------------------------------------+---------+
23 rows in set (Elapsed: 00:00:00.00)
gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli
GBase client 8.6.2.43-R28 .125499. Copyright (c) 2004-2021, GBase. All Rights Reserved.
gbase> show variables like '%timeout%';
+------------------------------------+---------+
| Variable_name | Value |
+------------------------------------+---------+
| _gbase_crash_dump_timeout | 60 |
| _gbase_net_alive_timeout | 92 |
| connect_timeout | 1000000 |
| delayed_insert_timeout | 300 |
| gbase_export_write_timeout | 300 |
| gbase_hdfs_client_timeout | 600 |
| gbase_loader_read_timeout | 300 |
| gcluster_async_connect_timeout | 120 |
| gcluster_connect_net_read_timeout | 1000000 |
| gcluster_connect_net_write_timeout | 1000000 |
| gcluster_lock_timeout | 0 |
| gcluster_send_client_data_timeout | 30 |
| gcluster_task_status_poll_timeout | 6000 |
| gcluster_wait_query_cancel_timeout | 200 |
| interactive_timeout | 1000000 |
| net_read_timeout | 1000000 |
| net_write_timeout | 1000000 |
| node_peer_connect_timeout | 5 |
| node_peer_read_timeout | 0 |
| node_peer_write_timeout | 0 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 1000000 |
+------------------------------------+---------+
23 rows in set (Elapsed: 00:00:00.00)
gbase> set global interactive_timeout=60;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli
GBase client 8.6.2.43-R28 .125499. Copyright (c) 2004-2021, GBase. All Rights Reserved.
gbase> show variables like '%timeout%';
+------------------------------------+---------+
| Variable_name | Value |
+------------------------------------+---------+
| _gbase_crash_dump_timeout | 60 |
| _gbase_net_alive_timeout | 92 |
| connect_timeout | 1000000 |
| delayed_insert_timeout | 300 |
| gbase_export_write_timeout | 300 |
| gbase_hdfs_client_timeout | 600 |
| gbase_loader_read_timeout | 300 |
| gcluster_async_connect_timeout | 120 |
| gcluster_connect_net_read_timeout | 1000000 |
| gcluster_connect_net_write_timeout | 1000000 |
| gcluster_lock_timeout | 0 |
| gcluster_send_client_data_timeout | 30 |
| gcluster_task_status_poll_timeout | 6000 |
| gcluster_wait_query_cancel_timeout | 200 |
| interactive_timeout | 60 |
| net_read_timeout | 1000000 |
| net_write_timeout | 1000000 |
| node_peer_connect_timeout | 5 |
| node_peer_read_timeout | 0 |
| node_peer_write_timeout | 0 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 60 |
+------------------------------------+---------+
23 rows in set (Elapsed: 00:00:00.00)
解决方案
1、调高参数
默认值已经是100万秒，不建议一个空闲连接10多天无任何任务。
2、连接检测
一般是连接池的功能，在调用方获取连接时，先检测连接是否可用，常见是发送一个select 1到数据库，如果正常返回则表示连接当前可用，返回调用方；如果报错，则连接不可用，则寻找下一个可用连接或新建一个连接，再次检测成功后返回。