Using PHP and MySQL as an example, let's assume that there are 1000 connections (clients) accessing the site and using functions that open connection to MySQL and make queries. How do PHP and MySQL process so many connections and queries at the same time?
If there are 1000 connected clients on the site making requests to MySQL, then there are 1000 MySQL connections?
P:
If there are 1000 connected clients on the site making requests to MySQL, then there are 1000 MySQL connections?
R: Only if the 1000 initiates exactly one navigation at a time on pages that use DB connection. After the page loads on the screen, the connection is now closed.
Usually connections are made at the beginning of the script that serves the page, and are closed shortly thereafter. It is normal for you to have 1000 people using your site simultaneously, but in practice there are only 15 or 20 concurrent connections. The time spent by the browser is much higher in viewing and reading the pages than by clicking on the links and loading data, and it is very unlikely that those clicks will occur at the same time.
I usually say this because the site nightmare is the persistent connection, which is opened in a script and held in some session object for other pages to use. This is a not recommended practice as it harms the performance of all DB server applications, but it is still possible to do so on many hosts.
There are many techniques for doing load distribution, when the application requires a lot of concurrent access, but then we are not talking about only 1000 concurrent connections. You can put a few more zeros in this number.
Here are some common things:
Database Mirroring / Replication
In this case, you have multiple DB servers, and when you change something on the principal, this data is copied to the slaves. The advantage of this method is that you divide clients across multiple servers, effectively multiplying capacity by the number of instances serving the DB. Disadvantage: Storage space is proportional to the number of servers.
Data distribution
Using techniques like map / reduce, you have data distributed across multiple machines, but without mirroring. Part is in one machine, part is in another, and your query is sent to several places at the same time, but only the "father of the child" returns the answer. In this case, you already need a more advanced data management project. The advantage of this method is that you do not occupy space with redundant data. The disadvantage is the greater complexity in the architecture. Example: Google.
Caches / buffers
You can retain some of the data in the application so that you do not query the server at all times. An example of this case would be a data paging where you load the data and keep in the application cache, or even in the browser, so when the person advances or back pages, the data does not need to be reloaded. Example: Listing a user's domains in the registry.br - your domains are loaded in a single JSON, and when you go forward or back page, you are only changing the view, not reloading the data - Note that this is also a kind of < in> early loading , see below.
Prediction / early loading
This case is a double-edged sword. Here you gain performance, but with the risk of loading data unnecessarily. The technique is to load more data than you need, for when the person moves to the next page, or roll a listing, the data is already there. The problem in this case is that you are usually always carrying more data than you need. The advantage is that you did this by taking up the time the user is analyzing the previous data.
If there are 1000 connected clients on the site making requests to MySQL, then there are 1000 MySQL connections?
Not necessarily. Even if your database server supports one thousand connections simultaneously making requests, it would be a great waste of resources if these requests were asking for equal data.
For example, if your site receives 1000 requests for data over a period of time, it is likely that many different requests are asking for the same data. It is still quite probable that during this certain period of time these data have not been modified. Considering this, it becomes a waste of resources, consult the bank looking for something already consulted previously and that has not changed. The solution to this type of situation is the caching of the data.
A database server with caching deployed, stores in memory the response of each new query as it is requested, so that a similar future request does not need to go to the search database the die. The data will already be available in memory and a new query only happens if a previously consulted data has been modified or if it has not been previously consulted and therefore is not in memory, it is not in cache .
So, on a server with caching system deployed, 1000 requests mean 1000 queries only if they ask for different data.
Short answer: It depends a lot on the structure, such as bandwidth, amount of RAM among other factors.
The maximum number of connections MySQL can support depends on the quality of the thread library on a platform, the amount of RAM available, the amount of RAM that is used for each connection, the workload of each connection, and the response time.
Linux or Solaris should be able to support anything between 500 ~ 1000 concurrent connections routinely and up to 10,000 connections if you have many gigabytes of RAM available and the workload of each is low or the response time is not very demanding.
Regarding PHP , I believe there is no limit, you can open n connections, though the database server is application server support.
