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到数据库,如果正常返回则表示连接当前可用,返回调用方;如果报错,则连接不可用,则寻找下一个可用连接或新建一个连接,再次检测成功后返回。