0

I'm planning to use persistent connection in mysqli by including the following code at the top of every database handler script:

$db = new mysqli('p:localhost', 'db_user', 'db_user_pass', 'db_name');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

The advantage I see here is that my web application will use only a single connection with the mysql server and hence my app will never show a "Too many connections" error even if there are 10,000 simultaneous users. However, since there will only be a single mysql persistent connection for all the database calls by any script(or different instances of the same script), the SQL queries will queue up instead of getting processed by thousands of parallel mysql connections.

First of all, please correct me if I'm not understanding things correctly. Secondly, if I'm correct, how do I address the issue. On one side is a "Too many connections" problem(if I don't use a persistent connection) and on the other side is a "queueing up of queries" problem(if I use a persistent connection).

iamlegend
  • 169
  • 3
  • 15
  • There is no problem at all with "queries queueing up". – Charlotte Dunois Sep 06 '14 at 19:03
  • But why so? Is it that even if I create 1000 mysql connections and request 1000 select queries in parallel, MySQL will anyhow process one query at a time? – iamlegend Sep 06 '14 at 19:06
  • If you have a persistent connection, you'll only have one connection and request 1000 queries parallel. MySQL will always return the result for the requested query from the process it requested (yes, for every visitor there is one webserver/php process). – Charlotte Dunois Sep 06 '14 at 19:13
  • Well, what I'm trying to ask here is: (Scenario 1)-> 1000 parallel queries, 1000 parallel connections, 1ms per query : Total time: 1ms (Scenario 2)-> 1000 parallel queries, 1 persistent connection, 1ms per query: Total time: 1000ms (Question)-> How/When is scenario 2 better? – iamlegend Sep 06 '14 at 19:34

1 Answers1

1

To solve a "Too many connections" problem you could, quite quickly, set the MySQL variable max_connections higher. If you use phpMyAdmin you can use the following SQL command to see the maximum amount of connections:

show variables like "max_connections";

Mine is 500. You can change it with:

set global max_connections = 1000;

But you will need SUPER privilege(s) for this. After that I would check whether your scripts close all connections in a timely fashion. Look at the processes in phpMyAdmin. Anything hanging around there for more that a few seconds?

As for your persistent connection, this is useful when the database server is on another machine and network than the PHP-webserver. If they are not, the connection time may be negligible, in which case there is real no advantage to persistent connections. There's also the danger of creating too many persistent connections, especially with the Apache server. If you start to use a persistent connection, watch the number of connections like a hawk. Also check the wait_timeout variable:

SHOW VARIABLES LIKE "wait_timeout";

Make it a lot smaller if it's too big. Make sure your server is configured correctly to deal with persistent connections.

To keep it short; In MySQL/PHP there often isn't much of a difference between 1000 'parallel' queries or 1000 'parallel' connections. In the end all the queries need to be processed, and your server is either up to the task or not. Renting a better server might be the best advise I can give.

MySQL is simply not doing that much in parallel. With special tools, and a lot of work, you can let it do amazing things. For instance with: http://shardquery.com

KIKO Software
  • 15,283
  • 3
  • 18
  • 33
  • Alright, point taken. Performance on 1000 connections or 1 persistent connection is more or less same (except for the connection overhead in non-persistent connections). But I dont understand this part "There's also the danger of creating too many persistent connections". Why so? If I'm including the same persistent connection script on top of every script, I should always be working with one and only one persistent connection for the whole php application. When will a "too many persistent connections" issue arise? – iamlegend Sep 07 '14 at 08:41
  • In a perfect world you would be right; Each PHP script would pick up your single persistent connection, and everything would be fine. This is not what happens in practice. For instance, if you use PHP in CGI mode, starting a new process for each PHP request, the persistent connection simply won't be reused. Or read this: http://stackoverflow.com/questions/3332074/what-are-the-disadvantages-of-using-persistent-connection-in-pdo This talks about PDO, not MySQLi, but most of it applies. – KIKO Software Sep 07 '14 at 09:30
  • The web startup I'm working at, gets a spike in number of concurrent web users from 5000 on a normal day to 10,000 on weekends. This Saturday the traffic was so high that we started getting a "too many connections" error intermittently. Our CTO fixed this by simply increasing the max_connections value on the DB servers. I want to know if using one persistent connection is a better solution here? We're using apache + mod_php, not CGI. – iamlegend Sep 07 '14 at 11:39
  • That's a lot of concurrent connections. It's outside my experience. Perhaps if you rephrased your question, just like you did now, more people will answer? Tell them what type of hosting you use now, what your real problem is, and what you've tried. Then there's always cloudhosting, like http://aws.amazon.com/websites where the infrastructure is ready for an onslaught. I'll leave it at this, I don't have anymore more to contribute. – KIKO Software Sep 07 '14 at 14:38
  • Thanks! It took me time to arrive at at this question. I'm posting a new question. By the way, we're already using AWS with multiple DB and WebApp servers! – iamlegend Sep 07 '14 at 14:55