Multiple connections with the Bank

6

In a PHP system that communicates with BD (MySQL) to retrieve the information and return it to the user who accesses it, is it necessary to establish multiple connections with the Bank? Or just one?

    
asked by anonymous 20.12.2014 / 19:04

3 answers

4

It says the PHP manual that ...

  

The connection to the server will be closed as soon as script execution   unless it has been previously closed using   explicitly ...

True, but not 100%. Let's not forget the server side, there may be settings like Connection Lifetime or Connection pooling .

Simplistically, an instance will have a connection to the database. Nowadays and as of version 5 of PHP, the use of the PDO library, implicitly allows to use the management of the connection to the database, reusing the same "connection" to each HTTP Request .

In this way and in the case of simple access of an instance to a database is peaceful, but the geographical location of both points will interfere, as the time elapses between Request .

If we go a little deeper into the matter we realize that dealing with creating a connection is a slow process. This is why instead of making a new connection for each request, the SQL server uses a POOL from, say, 100 active / permanent connections.

In practice you in PHP when you need a connection, request it (instantiating the PDO) and use it, and use it ... You can adjust the size of the POOL to change the way your application behaves. Maior POOL = mais conexões = mais "threads" allowing you to do more "things" at the same time, but this can also overwhelm the server side.

When writing these lines comes a recent project to memory where ...

An instance of the PDO object will match a connection to the database, but multiple instances can use the same connections when the implementation "jumps" permanently between database such as a mechanism of Sharding , more precisely in FEDERATED TABLE . This is a very simple mechanism because the base stays where the reuse of connections is up to the server to manage, and and might be a little clever and give some help.

In practice it results in instances of the PDO between "consecutive requets". Again and in practice the mechanism of an instance of the "object" for a connection is maintained, but not necessarily distinct connections if for example a server hosts multiple databases or in cases where the request uses the same server as a request previous.

The features are not endless and that's why we call the server a SQL server / service, as well as "tidying" the information, it also has many other tasks such as managing and optimizing the connections.     

22.12.2014 / 12:48
3

Only one per database. Normally you will open the connection at the beginning of the request processing, and this will be reused until the end.

Something like:

  • Starts script execution;
  • Calls the configuration script;
  • Includes many other required scripts;
  • Open connection to the bank;
  • Performs queries;
  • Generates the request response (HTML, XML, Json, JPG, or whatever);
  • Terminates the connection;
  • End of script.
  • In some cases you may want the connection to be persistent across multiple requests, but I do not know a case where this will be useful. Check the driver documentation used to verify support for this feature.

    Some of the driver features you use may influence the connection lifecycle.

    For example, the PDO driver will keep the connection open until the object's destructor is called (assigning% object_to% to object reference).

    Other drivers, such as null will close the connection or when a pg call happens or when the script ends. Otherwise, calls to pg_close will reuse created connections as long as the connection string is the same.

    Note: I know you've put the pg_connect tag in your question, but it's somewhat generic. I ended up quoting PostgreSQL because I had more experience with it.

        
    20.12.2014 / 19:20
    2

    In your code only one is enough.

    What can happen is that your application is called multiple times by the same user or different users. They would be several instances of the application, each one will establish a connection with the database itself. So in this scenario the database will be linked to multiple data communication sessions with the application instances.

    But each instance only needs to have a connection, no matter how many different things you want to do with the database.

    This does not mean that you can not have multiple connections. If there is a reason to have several, there is no problem either.

    In some cases the connection remains open but in the standby state, then when the same instance sends a new connection that has been "closed" it takes advantage of the existing connection.

        
    20.12.2014 / 19:23