ClientException:HTTP 500 和 OperationalError: (OperationalError) (1040, 'Too many connections')

以你之姓@ 2022-05-29 09:26 146阅读 0赞

# 1 问题 #

使用nova命令时出现错误：

root@openstack-ctl:~# nova service-list
    ERROR (ClientException): The server has either erred or is incapable of performing the requested operation. (HTTP 500) (Request-ID: req-4e4fb330-2798-414a-b86e-e33835014ee7)
    root@openstack-ctl:~# 
    root@openstack-ctl:~# tailf /var/log/nova/nova-api.log
    2018-03-21 13:44:31.105 5447 TRACE nova.api.openstack   File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 526, in get_connection
    2018-03-21 13:44:31.105 5447 TRACE nova.api.openstack     self.connection = self.__connect()
    2018-03-21 13:44:31.105 5447 TRACE nova.api.openstack   File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 538, in __connect
    2018-03-21 13:44:31.105 5447 TRACE nova.api.openstack     connection = self.__pool._creator()
    2018-03-21 13:44:31.105 5447 TRACE nova.api.openstack   File "/usr/lib/python2.7/dist-packages/oslo_db/sqlalchemy/compat/handle_error.py", line 200, in connect
    2018-03-21 13:44:31.105 5447 TRACE nova.api.openstack     raise original_exception
    2018-03-21 13:44:31.105 5447 TRACE nova.api.openstack OperationalError: (OperationalError) (1040, 'Too many connections') None None
    2018-03-21 13:44:31.105 5447 TRACE nova.api.openstack 
    2018-03-21 13:44:31.171 5447 INFO nova.api.openstack [req-4e4fb330-2798-414a-b86e-e33835014ee7 54613e7ec86a4eea885f5efeed5de107 a18ac5cb662d404ca0611b9e3768f9b7 - - -] http://192.168.4.15:8774/v2/a18ac5cb662d404ca0611b9e3768f9b7/os-services returned with HTTP 500
    2018-03-21 13:44:31.173 5447 INFO nova.osapi_compute.wsgi.server [req-4e4fb330-2798-414a-b86e-e33835014ee7 54613e7ec86a4eea885f5efeed5de107 a18ac5cb662d404ca0611b9e3768f9b7 - - -] 192.168.4.15 "GET /v2/a18ac5cb662d404ca0611b9e3768f9b7/os-services HTTP/1.1" status: 500 len: 359 time: 0.1559889

# 2 解决 #

出现此错误的原因，一种是访问量确实很高，MySQL服务器顶不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max\_connections值过小。

openstack环境中数据库信息：

分析原因

[my][][sql][]的默认连接上只有100，也就是说连接数据超过100 就会有可能出现 Too Many Connections  
   
修改my.cnf配置文件添加并需要重启：

\[mysqld\]

wait\_timeout = 600  
interactive\_timeout = 600

查询MySQL的最大连接数：

<table style="border-spacing:0px;background-color:rgb(255,255,255);max-width:100%;width:auto;color:rgb(51,51,51);font-family:'Helvetica Neue', Helvetica, Arial, 'Hiragino Sans GB', 'Microsoft Yahei', sans-serif;font-size:14px;"> 
 <tbody> 
  <tr> 
   <td style="padding:6px 12px;vertical-align:top;">&nbsp;代码如下</td> 
   <td align="center" style="padding:6px 12px;vertical-align:top;">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="padding:6px 12px;vertical-align:top;"><p style="margin-bottom:10px;">&nbsp;&nbsp;&nbsp; mysql&gt; show variables like 'max_connections';<br>&nbsp;&nbsp;&nbsp; +-----------------+-------+<br>&nbsp;&nbsp;&nbsp; | Variable_name | Value |<br>&nbsp;&nbsp;&nbsp; +-----------------+-------+<br>&nbsp;&nbsp;&nbsp; | max_connections | 100 |<br>&nbsp;&nbsp;&nbsp; +-----------------+-------+<br>&nbsp;&nbsp;&nbsp; 1 row in set (0.00 sec)</p></td> 
  </tr> 
 </tbody> 
</table>

查询MySQL响应的最大连接数：

<table style="border-spacing:0px;background-color:rgb(255,255,255);max-width:100%;width:auto;color:rgb(51,51,51);font-family:'Helvetica Neue', Helvetica, Arial, 'Hiragino Sans GB', 'Microsoft Yahei', sans-serif;font-size:14px;"> 
 <tbody> 
  <tr> 
   <td style="padding:6px 12px;vertical-align:top;">&nbsp;代码如下</td> 
   <td align="center" style="padding:6px 12px;vertical-align:top;">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="padding:6px 12px;vertical-align:top;"><p style="margin-bottom:10px;">&nbsp;&nbsp;&nbsp; mysql&gt; show global status like 'max_used_connections';<br>&nbsp;&nbsp;&nbsp; +----------------------+-------+<br>&nbsp;&nbsp;&nbsp; | Variable_name | Value |<br>&nbsp;&nbsp;&nbsp; +----------------------+-------+<br>&nbsp;&nbsp;&nbsp; | Max_used_connections | 5 |<br>&nbsp;&nbsp;&nbsp; +----------------------+-------+<br>&nbsp;&nbsp;&nbsp; 1 row in set (0.00 sec)</p></td> 
  </tr> 
 </tbody> 
</table>

说明：本地环境没什么参考价值，但是就上面的数据而言，MySQL过去所响应的最大连接数小于其允许的最大连接数，所以不会出现1040错误。  
MySQL比较理想的最大连接数[计算][Link 1]方式为：

即最大连接数占上限连接数的85％左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

问题找到解决办法

1、mysql -u root -p 进入不了，同样出现上述错误。

2、修改/etc/mysql/my.cnf（ubuntu系统，其他系统在/etc/my.cnf

<table style="border-spacing:0px;background-color:rgb(255,255,255);max-width:100%;width:auto;color:rgb(51,51,51);font-family:'Helvetica Neue', Helvetica, Arial, 'Hiragino Sans GB', 'Microsoft Yahei', sans-serif;font-size:14px;"> 
 <tbody> 
  <tr> 
   <td style="padding:6px 12px;vertical-align:top;">&nbsp;代码如下</td> 
   <td align="center" style="padding:6px 12px;vertical-align:top;">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="padding:6px 12px;vertical-align:top;"><p style="margin-bottom:10px;">[mysqld]&nbsp;<br>port=3306&nbsp;<br>#socket=MySQL&nbsp;<br>skip-locking&nbsp;<br>set-variable = key_buffer=16K&nbsp;<br>set-variable = max_allowed_packet=1M&nbsp;<br>set-variable = thread_stack=64K&nbsp;<br>set-variable = table_cache=4&nbsp;<br>set-variable = sort_buffer=64K&nbsp;<br>set-variable = net_buffer_length=2K&nbsp;<br>set-variable = max_connections=1000</p></td> 
  </tr> 
 </tbody> 
</table>

3、重启

搞定了。

# 3 其它 #

openstack环境的数据库是双节点的Galera Cluster，因此要在每一个节点上都修改mysql的配置文件。修改后要重新启动集群。

To start the cluster, complete the following steps:

1.  Initialize the Primary Component on one cluster node. For servers that use `init`, run the following command:
    
        # service mysql start --wsrep-new-cluster
    
    For servers that use `systemd`, run the following command:
    
        # systemctl start mariadb --wsrep-new-cluster
2.  Once the database server starts, check the cluster status using the `wsrep_cluster_size` status variable. From the database client, run the following command:
    
        SHOW STATUS LIKE 'wsrep_cluster_size';
        
        +--------------------+-------+
        | Variable_name      | Value |
        +--------------------+-------+
        | wsrep_cluster_size | 1     |
        +--------------------+-------+
3.  Start the database server on all other cluster nodes. For servers that use `init`, run the following command:
    
        # service mysql start
    
    For servers that use `systemd`, run the following command:
    
        # systemctl start mariadb
4.  When you have all cluster nodes started, log into the database client of any cluster node and check the `wsrep_cluster_size`status variable again:
    
        SHOW STATUS LIKE 'wsrep_cluster_size';
        
        +--------------------+-------+
        | Variable_name      | Value |
        +--------------------+-------+
        | wsrep_cluster_size | 3     |
        +--------------------+-------+

When each cluster node starts, it checks the IP addresses given to the `wsrep_cluster_address` parameter. It then attempts to establish network connectivity with a database server running there. Once it establishes a connection, it attempts to join the Primary Component, requesting a state transfer as needed to bring itself into sync with the cluster.

Note

In the event that you need to restart any cluster node, you can do so. When the database server comes back it, it establishes connectivity with the Primary Component and updates itself to any changes it may have missed while down.

### Restarting the cluster[¶][Link 2] ###

Individual cluster nodes can stop and be restarted without issue. When a database loses its connection or restarts, the Galera Cluster brings it back into sync once it reestablishes connection with the Primary Component. In the event that you need to restart the entire cluster, identify the most advanced cluster node and initialize the Primary Component on that node.

To find the most advanced cluster node, you need to check the sequence numbers, or the `seqnos`, on the last committed transaction for each. You can find this by viewing `grastate.dat` file in database directory:

$ cat /path/to/datadir/grastate.dat
    
    # Galera saved state
    version: 3.8
    uuid:    5ee99582-bb8d-11e2-b8e3-23de375c1d30
    seqno:   8204503945773

Alternatively, if the database server is running, use the `wsrep_last_committed` status variable:

SHOW STATUS LIKE 'wsrep_last_committed';
    
    +----------------------+--------+
    | Variable_name        | Value  |
    +----------------------+--------+
    | wsrep_last_committed | 409745 |
    +----------------------+--------+

This value increments with each transaction, so the most advanced node has the highest sequence number and therefore is the most up to date.

参考：

1 [https://docs.openstack.org/ha-guide/shared-database-manage.html][https_docs.openstack.org_ha-guide_shared-database-manage.html]

2 [https://yq.aliyun.com/ziliao/59782][https_yq.aliyun.com_ziliao_59782]

[my]: https://edu.aliyun.com/jiaocheng/1107
[sql]: https://edu.aliyun.com/jiaocheng/1043
[Link 1]: https://www.aliyun.com/
[Link 2]: https://docs.openstack.org/ha-guide/shared-database-manage.html#restarting-the-cluster
[https_docs.openstack.org_ha-guide_shared-database-manage.html]: https://docs.openstack.org/ha-guide/shared-database-manage.html
[https_yq.aliyun.com_ziliao_59782]: https://yq.aliyun.com/ziliao/59782