南大通用GBase 8a空闲超时参数Wait_timeout用法

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

参考其它超时参数

https://www.gbase8.cn/tag/%e8%b6%85%e6%97%b6