Open Stack

On 04/16/2015 06:40 PM, Matt Riedemann wrote:
> On 4/16/2015 12:27 PM, Jay Pipes wrote:
>> On 04/16/2015 09:54 AM, Sean Dague wrote:
>>> On 04/16/2015 05:20 PM, Qiming Teng wrote:
>>>>
>>>> Wondering if there is something misconfigured in my devstack
>>>> environment, which was reinstalled on RHEL7 about 10 days ago.
>>>> I'm often running into mysql connections problem as shown below:
>>>>
>>>> $ mysql
>>>> ERROR 1040 (HY000): Too many connections
>>>>
>>>> When I try dump the mysql connection list, I'm getting the followng
>>>> result after a 'systemctl restart mariadb.service':
>>>>
>>>> $ mysqladmin processlist | grep nova | wc -l
>>>> 125
>>>>
>>>> Most of the connections are at Sleep status:
>>>>
>>>> $ mysqladmin processlist | grep nova | grep Sleep | wc -l
>>>> 123
>>>>
>>>> As for the workload, I'm currently only running two VMs in a multi-host
>>>> devstack environment.
>>>>
>>>> So, my questions:
>>>>
>>>>    - Why do we have so many mysql connections from nova?
>>>>    - Is it possible this is caused some misconfigurations?
>>>>    - 125 connections in such a toy setup is insane, any hints on
>>>> nailing
>>>>      down the connections to the responsible nova components?
>>>>
>>>> Thanks.
>>>>
>>>> Regards,
>>>>    Qiming
>>>
>>> No, that's about right. It's 1 connection per worker. By default most
>>> daemons start 1 worker per processor. Each OpenStack service has a bunch
>>> of daemons. It all adds up pretty quick.
>>
>> And just to add to what Sean says above, there's nothing inherently
>> wrong with sleeping connections to MySQL. What *is* wrong, however, is
>> that the default max_connections setting in my.cnf is 150. :( I
>> frequently recommend upping that to 2000 or more on any modern hardware
>> or decent sized VM.
>>
>> Best,
>> -jay
>
> What do you consider a decent sized VM?  In devstack we default
> max_connections for postgresql to 200 because we were having connection
> timeout failures in the gate for pg back in the day:
>
> http://git.openstack.org/cgit/openstack-dev/devstack/tree/lib/databases/postgresql#n15
>
> But we don't change this for mysql:
>
> http://git.openstack.org/cgit/openstack-dev/devstack/tree/lib/databases/mysql
>
> I think the VMs in the gate are running 8 VCPU + 8 GB RAM, not sure
> about disk.

An 8 vCPU VM will have 80 connections to MySQL consumed by the 
nova-conductor (8 processes with a 10 connection pool in each process). 
There may be 10-12 other connections from various other Nova services, 
but the total number of MySQL connections that Nova would consume should 
not be more than around 90 or so. That said, Cinder, Keystone, Neutron, 
Glance and other services will also consume MySQL connections which 
could push things near 150.

Easy way to test this is just to run:

  mysql -uroot -p$PASS -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"

before and after the OpenStack services are started.

Long term, I think it's wise *not* to use connection pooling for MySQL 
backends. As Clint mentioned in an earlier response, the process of 
connecting to MySQL is *extremely* lightweight, and the way that we use 
the database -- i.e. not using stored procedures or user functions -- 
means that the total amount of memory consumed per connection thread is 
very low. It doesn't really benefit OpenStack to pool MySQL connections 
(it does for PostgreSQL, however), and the drawback to pooling 
connections is that services like nova-conductor maintain long-lived 
connection threads that other services cannot use while maintained in 
the connection pool.

Best,
-jay

Best,
-jay