GBase 8a空闲超时sleep优化配置方法
【摘要】 GBase 8a数据库集群，当外部连接长时间处于空闲状态(Sleep), 为了减少资源浪费，提供了Wait_timeout参数，Sleep超过该参数的连接会被自动kill掉。控制参数Wait_timeout，interactive_timeout参数默认值1000000秒。gbase> show variables like '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即可
如下修改参数为2秒，稍等再执行SQL,可以发现其ID已经变化，从226变成了230，期间有重新连接的信息。
gbase> show processlist;
+-----+-----------------+---------------------+-------+---------+--------+---------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+---------------------+-------+---------+--------+---------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 972348 | Waiting for cluster mutex | NULL |
| 3 | gbase | 192.168.6.138:45070 | gbase | Sleep | 226 | | NULL |
| 52 | gbase | 192.168.6.138:45072 | gbase | Sleep | 1101 | | NULL |
| 115 | root | localhost | NULL | Sleep | 369086 | | NULL |
| 116 | root | localhost | test | Sleep | 369005 | | NULL |
| 223 | gbase | 192.168.6.139:37612 | gbase | Sleep | 226 | | NULL |
| 226 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+-----+-----------------+---------------------+-------+---------+--------+---------------------------+------------------+
7 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=2;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> show variables like 'wait_timeout';
ERROR 2006 (HY000): GBase server has gone away
No connection. Trying to reconnect...
Connection id: 230
Current database: *** NONE ***
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| wait_timeout | 1000000 |
+---------------+---------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show processlist;
+-----+-----------------+---------------------+-------+---------+--------+---------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+---------------------+-------+---------+--------+---------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 972447 | Waiting for cluster mutex | NULL |
| 3 | gbase | 192.168.6.138:45070 | gbase | Sleep | 325 | | NULL |
| 52 | gbase | 192.168.6.138:45072 | gbase | Sleep | 1200 | | NULL |
| 115 | root | localhost | NULL | Sleep | 369185 | | NULL |
| 116 | root | localhost | test | Sleep | 369104 | | NULL |
| 223 | gbase | 192.168.6.139:37612 | gbase | Sleep | 325 | | NULL |
| 230 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+-----+-----------------+---------------------+-------+---------+--------+---------------------------+------------------+
全局参数global
需要设置另一个interactive_timeout，否则下次登录时，参数不生效。
gbase> set global wait_timeout=30;
Query OK, 0 rows affected (Elapsed: 00:00:00.25)
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>
gbase> ^CAborted
[gbase@gbase ~]$ gccli -uroot
GBase client 8.6.2.43-R33.129391.133425. Copyright (c) 2004-2024, 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.10)
gbase> ^CAborted
[gbase@gbase ~]$ gccli -uroot
GBase client 8.6.2.43-R33.129391.133425. Copyright (c) 2004-2024, 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 | 30 |
| 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 | 30 |
+------------------------------------+---------+
23 rows in set (Elapsed: 00:00:00.00)
解决方案
1、调高参数
默认值已经是100万秒，不建议一个空闲连接10多天无任何任务，建议根据实际的业务场景进行调整。
2、连接检测
一般是连接池的功能，在调用方获取连接时，先检测连接是否可用，常见是发送一个select 1到数据库，如果正常返回则表示连接当前可用，返回调用方；如果报错，则连接不可用，则寻找下一个可用连接或新建一个连接，再次检测成功后返回。
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）