0

I'm to developing a back-end for a demanding application that may generate a really high traffic and processing load on the server. To deal with the database connections, I came up with the following two strategies:

function connectToDB() {
    return new mysqli(HOST, USR, PWD, DB);
}
function myFunction() {
    $conn = connectToDB();
    // do some stuff..
    $conn->close();
}

The disadvantages of the preceding strategy, that I can think of, are the "so many memory allocations" and of course "many opening and closing connections". However, in the following strategy:

$conn = new mysqli(HOST, USR, PWD, DB);
function myFunction() {
    global $conn;
    // do some stuff..
    // not closing the connection, it will be persisted to the end of the script..
}
// ...
$conn->close();

It may reach the max_connections limit more likely than the previous method.

So, What is my way to go: to "close them immediately" or to "close them at the end of the script"? Do my concerns about the allocations or concurrent connection limits are just irrational? Or, do the strategies count at all?! Or maybe I'm completely lost in a desert of ignorance! Please shed some lights on this.

Thanks in advance :)

  • Closing and establishing the connection have create a over head. Why dont you opt Singleton design pattern for you DB class – Channaveer Hakari Oct 07 '17 at 13:43
  • Look at https://stackoverflow.com/questions/20492427/when-should-i-close-a-database-connection-in-php?rq=1 – Carey Oct 07 '17 at 13:44
  • @ChannaveerHakari Thanks for the reply :) But, the second strategy has nothing less than the singleton pattern, **performance wise** :) –  Oct 07 '17 at 13:49

1 Answers1

0

Destroy the connection when your script finishes. Alternatively, create a Database class and in the __destruct() method have something like this:

public function __destruct()
{
    mysqli_close($this->connection);
}

You shouldn't create and destruct a connection for every query.

Carey
  • 650
  • 1
  • 4
  • 16
  • Thank you, But I know how to handle it, I mean: the connections do _get closed_ ultimately (whether explicitly or implicitly.) Nevertheless, are you saying the `max_connections` limits trades off the allocation overhead?!! –  Oct 07 '17 at 13:54
  • What is max_connections set to on your server? If you're having issues with it, either raise it (within server limits) or setup slave/master mirroring & replication. Ultimately, this approach is preferred as establishing a connection multiple times is not a wise investment. – Carey Oct 07 '17 at 13:59
  • The `max_connections` in my current server is `150`; I think it's relatively trivial, or **Is it**??? What do mean by `slave/master mirroring & replication`? –  Oct 07 '17 at 14:02
  • I'll be blunt, with PHP you're better off trying to find performance improvements by moving to another language. Look at https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql, its basically copying a database to multiple instances and loadbalancing access to those databases. 150 is quite a low limit, see if you can increase it. – Carey Oct 07 '17 at 14:05
  • Why do I need `multiple copies of a MySQL data` or the `replication`!!!! I do not agree about PHP being SLOW!!!! –  Oct 07 '17 at 14:11
  • The entire problem is that you have too many connections to your database. You either increase the amount of connections you can have, or you increase the amount of database hosts you can connect to. There are faster languages than PHP, theres no getting around that. – Carey Oct 07 '17 at 14:16