Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Employee PHP MySQL persistent, cached, pooled connection - for how long?
+2 Vote Up -0 Vote Down

It is a common question: what is the lifespan of a persistent, pooled, cached, however-you-call-it PHP MySQL connection? What about CGI, FastCGI, web server module? The answer is always the same. Standard I/O file handles, including socket connections, are bound to processes. The lifespan of a persistent, pooled, cached, however-it-is-to-be-called-in-context-x connection is that of the PHP process. Depending on the web server deployment model, a PHP process handles one or multiple web requests. Read on only if puzzled.

A process

Do you have any colleguages around? Please, ask them for "APUX". In case of no reply, get a copy and study. To become a master PHP developer you will have to master todays frameworks (Zend Framework, Symfony, Dojo, …) and yesterdays. Yesterdays framework is still dominant. Yesterdays framework is your operating system. "APUX" stands for Advanced Programming in the UNIX Environment.

Building blocks of a process   Task state and identifier PID = 112, parent, children, … Credentials User-ID, group-ID, … Signal handler … Standard I/O Handles (descriptors) Memory Heap, stack, BSS, … … …

Standard I/O file handles, including socket connections, are bound to processes. The process may happen to execute the PHP interpreter (interpiler ). PHP may open a socket connection to MySQL. If the process ends, the connection handle to MySQL goes away. Like the memory of a process is free’d, the connection handle is released as well.

A PHP process   … … Standard I/O Connection handle to MySQL … …

Webserver using CGI

If running PHP via CGI, the MySQL connection pooling/caching/persisting story pretty much ends here. For every (n = 1) web request the webserver starts and stops a process to handle the (n = 1) web request. Any process memory allocated is free’d at the end of the web request. Any standard I/O handle opened is closed. The lifespan of a MySQL connection is that of a single web request, a single script execution.

  • Accept HTTP web request
    • Start worker process (PHP)
    • Execute *.php script through worker
    • Shutdown worker process
  • Send worker’s reply to client
  • A webserver using FastCGI

    Once I talk FastCGI, I get questions. I used grey to highlight the differences. The difference is minimal.

  • Accept HTTP web request
    • Start worker process (PHP), if none cached
    • Execute *.php script through worker
    • Shutdown worker process, if work limit exceeded
  • Send worker’s reply to client
  • For every n > 1 web requests the webserver starts and stops a process to handle the (n > 1) web requests. One PHP process handles n web requests. Process memory is available for the duration of n web requests. Standard I/O handles can be kept open for the duration of n web requests. A pooled, cached, persisted MySQL connection is such an I/O handle. It can be reused (at least) n - 1 times by a worker process. Given that every worker handles many thousand requests, it is often reused many thousand times. Things sum up quickly. The time for many thousand connection establishments is saved.

    Compared to CGI there is no difference but the value of n. Simple as that.

    Web server FastCGI process … FastCGI process Standard I/O Handles (descriptors) … Standard I/O Handles (descriptors) Memory Heap, stack, BSS, … … Memory Heap, stack, BSS, … … … … … …

    Whether you got 1 or 1,000 worker processes makes no difference: standard I/O file handles, including socket connections, are bound to processes. How many connection pools will you have with 1,000 worker processes…?

    A (puristic) threaded web server

    A threaded web server is a game changer. Threads are execution units within a process. Threads share the resources of a process, for example, process memory or open file handles. Let’s assume the web server operates like this.

  • Load PHP into web server process
    • Accept HTTP web request
    • Execute *.php script using worker thread
    • Send worker’s reply to client
  • Shutdown web server process
  • How many processes? One. How many MySQL connections pools would you have with such a web server? One. How many web requests will be served by a "worker" process? n > 1. How many times can a pooled, cached or persistent MySQL connection be used? At least as many times as web requests are served by the web server process (n>1). If you want to optimize for connection reuse, this is the best you can get.

    Threads share the resources of a process, for example, process memory or open file handles? That is a case for APUX or any similar lecture…

    Beyond CGI, FastCGI and plain threaded web servers

    As an APUX reader you know tracks beyond the main roads. In case you do not plan to ever read APUX, stop here. Otherwise you get puzzled again.

    Standard I/O file handles, including socket connections, are bound to processes. True, but child processes inherit descriptors. True, but what about send_fd() from APUX to send a descriptor from one process to another? True, but what about using a deamon? Yes, if we control the process creation, the process life and know what other processes there are, we can break out.

    However, CGI and FastCGI isolate worker processes from each other. Process starting, stopping and caching is beyond the control of the worker processes. End of the road.

    From within a PHP worker process we have no simple way of keeping a MySQL/DatabaseX connections open at the end of the process. Plus, we do not know what other worker processes there are. We cannot share anything easily. Not being able to do it easily usually means becoming slow or adding dependencies and requirements.

    uWSGI

    If, for example, you want to deploy an extra deamon process or load a web server module, then… - but that is beyond PHP itself. Outside of PHP there is no one standard web server to extend. If uWSGI was popular in the PHP world, one might want to have a closer look at it. Maybe one could come up with something around the master process or attach-daemon… But, again, that is external to the PHP MySQL extensions.

    Any more questions on the Connection Multiplexing Plugin? I still have not given a direct reply to the one question in the announcement blog post. However, you can now answer it yourself, can’t you? Regarding keep alive - there is no ping, it is a prototype…


    PHP mysqlnd connection multiplexing plugin . More about PHP mysqlnd

    Happy hacking!

    @Ulf_Wendel 

    Votes:

    You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.