CODEWITHSUNDEEP
mysqlconnectionscalability
zx_wing
zx_wing

Reputation: 1966

How does mysql handle massive connections in real world?

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.

Upvotes: 1

Views: 4953

Answers (3)

RandomSeed
RandomSeed

Reputation: 29769

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

Upvotes: 2

nos
nos

Reputation: 229204

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.

Upvotes: 0

Andy
Andy

Reputation: 1618

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

Upvotes: 0

Related Questions