0

I have been researching this for a while but got no convinced answer.

From mysql tutorial, the default connections number is less than two hundred, and it says max_connection_num can be set to 2000 in Linux box as long as you have enough resource. I think this number is far from enough in real world deployment as there might be millions people visit your website at the same time.

There are couple of articles talking about how to optimize to reduce time cost by each query. But none of them tells me how this issue is root caused. I think there must be some mechanism like queue to prevent massive connections from happening simultaneously. otherwise you will finally get "too connection" exception.

anyone has some expertise in this area? thank you.

kenwarner
  • 28,650
  • 28
  • 130
  • 173
zx_wing
  • 1,918
  • 3
  • 26
  • 39
  • http://en.wikipedia.org/wiki/MySQL_Cluster – Kaii Jun 26 '12 at 18:39
  • The short answer to your question is a "connection pool", to manage the connections to the database. http://dev.mysql.com/tech-resources/articles/connection_pooling_with_connectorj.html Lots of other implementations, DBCP, C3PO, et al. A big issue is the high cost of connection churning, that is repeatedly creating and dropping database connections. – spencer7593 Jun 26 '12 at 18:51

3 Answers3

2

There are several options.

  1. Connection pooling
  2. As you mentionned: queuing. If too many clients connect at the same time, then the application layer should handle this exception, put the request to sleep for a short period of time and try again. Requests lasting more than a couple of seconds should usually be banned in such a high traffic environment.
  3. Load balancing through replication and/or clustering
RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • emm, I am using Hibernate JPA with Spring, I am surprised at "too connection" exception. My assumption is the JPA implementation should use connection pool internally, now it seems not. So with pool, what will happen when there is no available connection? In my memory, I used to play with JDBC with Apache connection pool, I still got some exception saying cannot get connection. If so, that doesn't work. Ideally it should queue these requests and serves them whenever a connection is free. Just like behavior of some thread pool – zx_wing Jun 26 '12 at 21:28
  • and for long time query, there is no way to anticipate how much time a query will cost – zx_wing Jun 26 '12 at 21:30
  • I have no idea, whatsoever, how JPA or Hibernate or Spring handles connections, I am sorry. MySQL offers a form of queuing at TCP level, but I wouldn't rely on it too much. Your libraries may or may not implement these mechanisms. You may want to post another question about how these specific frameworks deal with pooling or queuing. As for query execution duration, yes you can estimate it by looking at the actual SQL code generated by your libraries. You can also monitor the actual execution times with MySQL slow query log (only after they ran, though). – RandomSeed Jun 26 '12 at 21:59
  • 1
    @zx_wing You should have mentioned programming language/technologies you use in original post. Also it matters when you get "too many connections" exception. Specific pool implementation based on it's settings can initialize all connections when it starts, or start with one connection and allocate more when needed. Quite possible you have got exception when connection pool was initializing or growing. Number of connections available to your Java pool will depend in MySQL settings. – Andy Jun 27 '12 at 16:00
  • @zx_wing There are frameworks which can help your to monitor performance of SQL queries. One of them is http://jamonapi.sourceforge.net – Andy Jun 27 '12 at 16:05
  • @Andy, the jamonapi.sourceforge.net is helpful.I didn't mention my ORM middleware because I was told there was specific queue middleware for db connections. Maybe what the guy mentioned as queue is actually connection pool – zx_wing Jun 28 '12 at 18:07
0

Normally, your application is supposed to reuse connections already established. However, the language you chose to implement your application introduces limitations. If you use Java or .Net you can have pool of connections. For PHP it is not the case, you can check this discussion

Community
  • 1
  • 1
Andy
  • 1,618
  • 11
  • 13
  • I definitely disagree with the linked discussion. PHP persistent connections allow most of what is expected from a pool of connections (namely, reuse connections across multiple processes). – RandomSeed Jun 26 '12 at 20:34
  • 1
    @YaK According to official doc "Note, that these kind of links only work if you are using a module version of PHP." Even if you use PHP as a module, can you have 10 concurrent transactions started in 10 different connections in PHP? – Andy Jun 26 '12 at 22:30
  • Yes you can. I just tested it with mysql_pconnect(). Thanks for raising this question, I had never actually verified. – RandomSeed Jun 26 '12 at 22:55
  • 1
    The mysqli extension has a runtime parameter, but I don't think the other extensions provide such a facility. http://www.php.net/manual/en/mysqli.configuration.php#ini.mysqli.max-persistent – RandomSeed Jun 27 '12 at 14:43
  • @YaK Thank you. To summarize, discussion which I posted link to is not all wrong. :) – Andy Jun 27 '12 at 15:41
  • minorgod wrote: "Persistent connections are nothing like connection pooling."; darpet wrote: "There is no connection pooling in php.". I *definitely* disagree with these statements. I see where you are coming from. I do agree that this is a very primitive form of pooling, but it is *a* form of pooling nonetheless. I consider the "reusing of connections across processes" is the most important behaviour that describes a connection pool, the main element which does improve performances. The definition of connection pooling is vage IMHO, this is why I wrote "I disagree", not "this is wrong". – RandomSeed Jun 27 '12 at 17:19
  • A few other statements in the other discussion are indeed wrong: "A persistent connection in php will only be reused if you make multiple db connects within the same reques", "apache releases all resources when the request ends for the current request". – RandomSeed Jun 27 '12 at 17:24
0

If you exceed the max_connection_num, you do get a too many connections error. But if you really have 1 million users at your web server at the exact same time, you can't handle that with one server anyway, 1 million concurrent connections really requires a very big farm to handle.

However, the clients to your database is a webapp, that webapp usually connects to the database through abstractions called a connection pool, which does limit the number of connections to the database on the client side as long as all the database connections goes through that same pool.

nos
  • 223,662
  • 58
  • 417
  • 506