Apache/PHP/MySQL persistent connections have such a bad reputation because Apache handles each request as a child php process, each having 1 persistent connection. When visitors scale, MySQL reachs max connections limit from all the apache/php child processes, each with 1 persistent connection. There's also the issues of temporary tables, user variables, charsets, transactions and last-insert-id.
In my situation, we don't have to deal with the latter issues, because we are only READING from MySQL. There are no updates to the db: these are handled by another set of scripts on a different machine. The scripts we want to have persistent connections are the server-end of AJAX connections, returning JSON data.
Each pageview has 5 AJAX requests, so 6 different php child processes are started on the server for each page requested (5 ajax, 1 html). Ideally, I could have ONLY 1 connection from the php/ajax server to MySQL. This connection would be shared by all php child processes.
So, how can I do this? Should I use another webserver software other than apache? ngynx?
cheers
UPDATE: In this situation, the right way to connect to the MySQL server (http://bit.ly/15rBDP):
- using mysql native driver (mysqlnd) and mysqli
- each client reconnecting using the mysql-change-user function
- using MYSQLI-NO-CHANGE-USER-ON-PCONNECT in php config ('coz we don't need to cleanup).
UPDATE 2:
To clarify my question, what i want to have is: ALL php client processes, connecting through only ONE persistent connection. This connection is defined, ran and stored some how (my question), but all new php client processes know it and can use it. The problem with apache/php is that each php client process has 1 connection. If I serve 20,000 pages per minute, there will be 20,000 persistent connections. I want the 20,000 php child processes connecting to 1 unique, central, persistent connection to mysql.
