best practise to do many connections mysqli

Question

I received max_user_connections error this days. and I was wondering if I am doing something wrong.

I have a config.php file with mysqli connection script:

$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');

so pages where I need to get something in mysqli I include config.php. here is an example:

example.php

<?php
     include_once("config.php");

     $stmt = $mysqli->prepare("select...");
     $stmt->execute();
     $stmt->bind_result(...,...);
     while($stmt->fetch()) {
         ...
     }
     $stmt->close();
?>

some html <p> <img>...

<?php
     $stmt = $mysqli->prepare("select...");
     $stmt->execute();
     $stmt->bind_result(...,...);
     while($stmt->fetch()) {
       ...
     }
     $stmt->close();
?>

some html <p> <img>...

<?php
      $stmt = $mysqli->prepare("select...");
      $stmt->execute();
      $stmt->bind_result(...,...);
      while($stmt->fetch()) {
        ...
      }
      $stmt->close();
?>

So, my question is: is it the best practise to do selects like this? should I close mysqli connect after each select and open again? or do selects on the top together without separete than with some html in the middle?

there is nothing wrong with your code in regard this error message. So the problem is somewhere else — Your Common Sense, Mar 15 '16 at 11:57
It could also be a high number of concurrent users than max_user_connections. — Mihai, Mar 15 '16 at 11:59
This has been closed as a duplicate of http://stackoverflow.com/questions/4079531/mysqli-error-user-already-has-more-than-max-user-connections-active-connectio however the question here is explicitly about coding practices for reducing connection usage while the only discussion on the proposed duplicate is about increasing the configured limit. — symcbean, Mar 15 '16 at 12:01
Technically, you should use `php-fpm` to serve `php` pages, and avoid `mod_php` with Apache at all costs, especially in year 2016. Disconnecting will have no impact on the behavior of your site, because you can still hit `max_user_connections`. With `php-fpm` this problem is almost nonexistent because amount of connections to MySQL equals to amount of child processes, which is always small number and doesn't rise to no end like with `mod_php`. I'm not here to bash the technology, but the problem you're having stems from the infrastructure you're using. — Mjh, Mar 15 '16 at 12:01
@Mjh: Why should one use php-fpm rather than mod_php? Can you provide authoritative sources? There is no difference in mysql connection usage between mod_php and php-fpm unless you happen to be serving a large volume of static content *and* are instantiating mysql connections which are never used. — symcbean, Mar 15 '16 at 12:28
@symcbean - there is significant difference. `php-fpm` isn't closing MySQL connections when its done serving a request. Apache can spawn as many workers or threads as there's user connections, which increases number of connections to MySQL. `php-fpm` keeps the number of connections constant. Among various other reasons such as scaling (which isn't the problem here), `php-fpm` is simply a better choice that alleviates a number of issues. One of those being connecting to MySQL and keeping it optimal. — Mjh, Mar 15 '16 at 12:37
@Mjh: sorry, but you obviously do not understand these architectures nor how they relate to the problem being discussed here. As per my previous comment there are a couple of specific edge cases where the connection issue would be mitigated by switching to PHP-FPM - but even in these cases, the right solution is to fix the code anti-pattern, not change the architecture. — symcbean, Mar 15 '16 at 12:43
@symcbean - silly comments like "you don't understand" have no place here, do they? I do understand perfectly, which is why I'm making a *comment* and not posting an answer, since I know that architectural change is not always feasible. If anything, you don't understand what architecture switch offers. The problem is establishing too many connections. With `php-fpm`, that issue is easily mitigated forever. I'm not trying to discuss code, but the underlying architectural problem where web server opens a new connection for each client request. Fix that, fix any future problem related to it. — Mjh, Mar 15 '16 at 13:47

symcbean · Accepted Answer · 2016-03-15T12:29:27.583

the best practise to do selects like this?

I hate it when people use the term "best practice" it's usually a good indicator they don't know what they are talking about.

The advantage of your approach is that its nice and simple. But as you've discovered, it does not scale well.

In practice its quite hard to measure, but in most applications the MySQL connection is unused for most of the lifecycle of the script. Hence there are usually big wins to be made by delaying the opening of connection and closing it as soon as possible.

The former can be done by decorating the mysqli class, the connect method just stores the credentials while anything which needs to talk to the database should call the wrapped connect method when it needs access to the database. However typically the yeild of this approach is low unless your code creates a lot of database connections which are never used (in which case a cheaper solution would be to increase the connection limit).

It can take a long time after the last query is run before the connection is closed down. Explicitly closing the connection addresses this, but requires a lot of code changes.

do not open and close a connection for each query. Although it will result in a reduced number of connections to the databasee, the performance will suck

The biggest win usually comes from optimizing your queries - reduced concurrency and a better user experience.

A script that takes "a long time" while serving a web request should be never run at all. — Your Common Sense, Mar 15 '16 at 12:09
thank you for your answer! a friend told me to create more than 1 mysql user, so each time I change the user in config.php... don't know if it can solve this problem... — RGS, Mar 15 '16 at 12:57
Changing the username isn't going to have any impact on the number of connections, nor the limit - its an absolute limit, not a per-user limit. It would be useful for AB testing and to ensure you've completely migrated out of the existing automatic connection model though. — symcbean, Mar 15 '16 at 13:01

best practise to do many connections mysqli

1 Answers1