how to support many sql queries on a web at the same time?

Question

The question is: if I have a webpage hosted somewhere. The backend environment is LAMP supported. For example: whenever a user opens a new page, it will create a new mysql connection to the MySQL database server and do a corresponding query. We all know that MySQL has a max_connection limit (100 for version <5.1 as default).

Of course, we could increase the max_connection number of mysql. Besides that, I would like to know if there are some existing ways (hopefully not too complicated) to support many(say around 30,000-40,000) users at the same time. Any articles or links would be appreciated!

Answer 1

Check out the techniques used by Wikipedia:

• distributed database
• dedicated database servers distinct from web and file servers
• short database queries so connection recycling occurs quickly
• constant attention to server performance with tuning and reengineering as needed

Answer 2

Regardless of SQL queries, your web server cannot handle 30,000 to 40,000 concurrent requests. Don't confuse the number of users with the number of simultaneous requests.

If you really do have that many simultaneous requests, you'd need to direct the web traffic across many servers at multiple data centers that are geographically distributed. It has nothing to do with PHP or SQL -- you wouldn't even be able to serve that many static HTML pages simultaneously, simply because network bandwidth couldn't handle it.

There's a lot to learn if you need to operate large-scale web sites. Check out Theo Schlossnagle's great presentation Scalable Internet Architectures and his book of the same title.