Re: [openstack-dev] [nova] Mysql db connection leaking?
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 __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova] Mysql db connection leaking?
On 04/17/2015 05:26 AM, Qiming Teng wrote: On Thu, Apr 16, 2015 at 01:27:51PM -0400, 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 Thanks, guys. So the key takeaway for me is: - 100~200 mysql connections is not big problem, provided those connections are sleeping; Yep, correct, for MySQL. - Tuning mysql to support larger number of user connections is a must; Yes, because the default is a measly 150 max_connections. - Number of mysql connections is not proportional to the number of VMs, it is more related to the number of cores, number of workers etc. Yes. nova-compute workers (which are on each compute host that houses VMs) actually do not contact the database directly. Instead, they communicate with the nova-conductor service, which itself communicates with the database. The nova-conductor service by default will maintain a number of processes equal to the number of cores on the VM or baremetal machine. Each of these processes will keep a pool of connections to MySQL. In addition to nova-conductor, there are various other Nova services that also make connections to MySQL. Best, -jay __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova] Mysql db connection leaking?
On Thu, Apr 16, 2015 at 01:27:51PM -0400, 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 > Thanks, guys. So the key takeaway for me is: - 100~200 mysql connections is not big problem, provided those connections are sleeping; - Tuning mysql to support larger number of user connections is a must; - Number of mysql connections is not proportional to the number of VMs, it is more related to the number of cores, number of workers etc. Regards, Qiming __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova] Mysql db connection leaking?
> Can you show us the full listing for > > mysqladmin processlist > > as 125 seems high for a toy setup? How many nova instances are > running? I have a (single node) devstack setup with 2 nova instances > running and I have 9 mysql connections to the nova db. > > Regards > > Mark Hi, Mark, Below is the full list as of now. Two instances running on a two-host devstack environment. +--+--+-+--+-+--+---+--+--+ | Id | User | Host| db | Command | Time | State | Info | Progress | +--+--+-+--+-+--+---+--+--+ | 2183 | root | localhost:44303 | glance | Sleep | 1567 | | | 0.000| | 2185 | root | localhost:44695 | glance | Sleep | 2646 | | | 0.000| | 2270 | root | localhost:46238 | glance | Sleep | 967 | | | 0.000| | 2281 | root | localhost:46387 | glance | Sleep | 3907 | | | 0.000| | 2287 | root | localhost:46483 | glance | Sleep | 1477 | | | 0.000| | 2320 | root | localhost:47122 | glance | Sleep | 457 | | | 0.000| | 2336 | root | localhost:47743 | glance | Sleep | 427 | | | 0.000| | 2346 | root | localhost:47874 | glance | Sleep | 217 | | | 0.000| | 2349 | root | localhost:47964 | heat | Sleep | 508 | | | 0.000| | 2350 | root | localhost:47965 | heat | Sleep | 508 | | | 0.000| | 2351 | root | localhost:48018 | glance | Sleep | 97 | | | 0.000| | 2352 | root | localhost:48040 | heat | Sleep | 476 | | | 0.000| | 2353 | root | localhost:48101 | heat | Sleep | 444 | | | 0.000| | 2354 | root | localhost:48191 | heat | Sleep | 411 | | | 0.000| | 2355 | root | localhost:48263 | heat | Sleep | 380 | | | 0.000| | 2356 | root | localhost:48371 | heat | Sleep | 349 | | | 0.000| | 2357 | root | localhost:48389 | glance | Sleep | 298 | | | 0.000| | 2358 | root | localhost:48450 | heat | Sleep | 316 | | | 0.000| | 2359 | root | localhost:48527 | heat | Sleep | 285 | | | 0.000| | 2360 | root | localhost:48617 | heat | Sleep | 253 | | | 0.000| | 2361 | root | localhost:48673 | glance | Sleep | 2437 | | | 0.000| | 2362 | root | localhost:48697 | heat | Sleep | 222 | | | 0.000| | 2363 | root | localhost:48820 | heat | Sleep | 189 | | | 0.000| | 2364 | root | localhost:48821 | heat | Sleep | 189 | | | 0.000| | 2365 | root | localhost:48908 | heat | Sleep | 156 | | | 0.000| | 2366 | root | localhost:48909 | heat | Sleep | 156 | | | 0.000| | 2367 | root | localhost:49007 | heat | Sleep | 123 | | | 0.000| | 2368 | root | localhost:49008 | heat | Sleep | 123 | | | 0.000| | 2369 | root | localhost:49214 | glance | Sleep | 307 | | | 0.000| | 2370 | root | localhost:49294 | heat | Sleep | 26 | | | 0.000| | 2371 | root | localhost:49295 | heat | Sleep | 26 | | | 0.000| | 2372 | root | localhost:49368 | heat | Sleep | 506 | | | 0.000| | 2373 | root | localhost:49463 | heat | Sleep | 475 | | | 0.000| | 2374 | root | localhost:49464 | heat | Sleep | 475 | | | 0.000| | 2375 | root | localhost:49546 | heat | Sleep | 443 | | | 0.000| | 2376 | root | localhost:49551 | nova | Sleep | 152 | | | 0.000| | 2377 | root | localhost:49643 | heat | Sleep | 410 | | | 0.000| | 2378 | root | localhost:49729 | heat | Sleep | 377 | | | 0.000| | 2379 | root | localhost:49788 | heat | Sleep | 346 | | | 0.000| | 2380 | root | localhost:49808 | glance | Sleep | 1537 | | | 0.000| | 2381 | root | localhost:49867 | heat | Sleep | 315 | | | 0.000| | 2382 | root | localhost:49955 | heat | Sleep | 284 | | | 0.000| | 2383 | root | localhost:50030 | heat | Sleep | 252 | | | 0.000| | 2384 | root | localhost:50097 | heat | Sleep | 218 | | | 0.000| | 2385 | root | localhost:50193 | heat | Sleep | 187 | | | 0.000| | 2386 | root | localhost:50194 | heat | Sleep | 187 | | | 0.000| | 2387 | root | localhost:50303 | heat | Sleep | 152 | | | 0.000| | 2388 | root | localhost:50304 | heat | Sleep | 152 | | | 0.000| | 2389 | root | localhost:50418 | glance | Sleep | 2698 | | | 0.000| | 2390 | root | localhost:50621 | glance | Sleep | 1207 | | | 0.000| | 2391 | root | localhost:50797 | glance | Sleep | 157 | | | 0.000| | 2392 | root | localhost:50942 | glance | Sleep | 2227 | | | 0.0
Re: [openstack-dev] [nova] Mysql db connection leaking?
On 17/04/15 09:20, 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? Can you show us the full listing for mysqladmin processlist as 125 seems high for a toy setup? How many nova instances are running? I have a (single node) devstack setup with 2 nova instances running and I have 9 mysql connections to the nova db. Regards Mark __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova] Mysql db connection leaking?
Excerpts from Matt Riedemann's message of 2015-04-16 15:40:30 -0700: > > 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 > > > > __ > > OpenStack Development Mailing List (not for usage questions) > > Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe > > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev > > > > 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. > Note that pgsql's connection model is vastly different than mysql's. For mysql, each connection doing nothing uses very little RAM. For pgsql, IIRC, each one is a forked process. __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova] Mysql db connection leaking?
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 __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev 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. -- Thanks, Matt Riedemann __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova] Mysql db connection leaking?
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 __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [nova] Mysql db connection leaking?
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. -Sean -- Sean Dague http://dague.net __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
[openstack-dev] [nova] Mysql db connection leaking?
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 __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
