1

What is the best way to ensure I'm writing a scheme to minimize connection count to my MySQL database?

Currently, I have a ubuntu / PHP / apache setup on my server, and I'm using the following code with PDO to ensure a persistent connection:

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(
    PDO::ATTR_PERSISTENT => true
));

However, I seem to have 10 or more connections on at all times. I am not sure why this is. Quote from php.net:

Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

Does this mean I have to write $dbh = null to "close" and cache the connection?

bvpx
  • 1,227
  • 2
  • 16
  • 33
  • First things first: why do you want " to minimize connection count"? – Your Common Sense Oct 16 '13 at 07:43
  • It seems that with just a few users on my site, I am reaching maximum connections for my server. I want to scale my site to maybe thousands of people, so does this mean I'll need tens of thousands of connections to be my limit? Considering each person visiting the site may spawn 5-10 connections due to having multiple tabs open. – bvpx Oct 16 '13 at 08:04
  • For a regular site, one single connection is enough for 10 users. even each of them have 5 tabs opened. Do you realize how PHP works in general? – Your Common Sense Oct 16 '13 at 08:16
  • I do not know how PDO handles connections. I simply know that my site does not generate much traffic yet there are many connections established. I'm looking for a solution to a problem I'm having, not condescending questions that don't help whatsoever. – bvpx Oct 16 '13 at 08:36
  • Also, I got that piece of info from a comment in this very thread actually: `@bvpx: The number of connections will depend on how many PHP scripts you expect to be running simultaneously at any given time. I, for example, often click to open a number of pages in different tabs all at once. So, as one user, I could consume maybe 5 database connections. If all users are simultaneously connected to the site (and actively using it), you can hazard your own guess as to the resources required. – eggyal 55 mins ago` – bvpx Oct 16 '13 at 08:40
  • PDO has absolutely nothing to do with your problem. Yet how PHP actually works is essential to comprehend the problem. As of the comment you cited - to let you know, no user can be active in all his tabs at once. From the server point of view, one user remains one, no matter how many tabs he opened. this latter statement is another direct consequence from the understanding of how PHP works. – Your Common Sense Oct 16 '13 at 08:44
  • @YourCommonSense: If, within most browsers, I hold down the "command" key (on my Mac, else the "control" key on Windows or *nix) and then in quick succession click a bunch of links on a webpage, such as a few of the "Related" questions to the right of this page, a bunch of tabs will open that simultaneously go off to the server to fetch the respectively requested pages. Those page requests will each result in at least one database connection. They will be simultaneous. Thus my browsing habits often place a demand for a few simultaenous database connections upon the server. QED – eggyal Oct 16 '13 at 08:58
  • @eggyal well, if your site users do nothing but open tabs in large quantities - then, no doubt, your idea of "actively using the site" is right. If some of them take time to read what they opened - then, things not that catastrophic as you imagine. – Your Common Sense Oct 16 '13 at 09:06
  • @eggyal also, taking average PHP script execution time as 0.1 seconds, you have to be fairly dexterous to click 5 links in such a short time (reading link titles is not counted as it's apparently not the point of this tab opening sport) – Your Common Sense Oct 16 '13 at 09:26
  • @YourCommonSense: As ever, your attempt to reduce the world into your own narrow-minded ideas of how things "should" be done fails to appreciate that not everyone behaves as you would wish. I participate in no such "tab opening sport". I personally find that, having read an article, there are often a number of links that I wish to follow-up; I will then open those links in new tabs. I know that I am not the only person to do this, as my observations of other users (in both informal and formal usability testing environments), together with logfile analysis, has revealed to me. – eggyal Oct 16 '13 at 11:13
  • @YourCommonSense: As for script execution time, it is often the case that (due to effects of network latency on TCP connection setup), a number of links clicked within a couple of seconds of eachother will end up being invoked on the server within fractions of a second of eachtoher - this results in the oft-familiar sensation of waiting for a number pages to load, only to find that they suddenly proceed at once. – eggyal Oct 16 '13 at 11:16
  • @eggyal it seems you are confusing script execution time with page load time. You are not alone under such a delusion, yet it would be a good idea to learn the difference. – Your Common Sense Oct 16 '13 at 11:22
  • @YourCommonSense: No, I am not. I do not care for how long it takes for the page content to be transferred to my computer, or for how long my browser takes to render the results. I am merely talking about the delays between the link being clicked and the request finally being received at the server. – eggyal Oct 16 '13 at 11:45
  • @eggyal It seems you are right. A poor chap who is bound to use satellite internet connection and whose main occupation is to open tabs in large numbers without reading them indeed could be a problem - I have to admit that. Which makes a horde of such users a pure disaster. You managed to convince me. I am leaving in order to take urgent preparations on all my sites to prevent it. I have no idea how they worked before, consuming less than one connection per 10 users in average. An accident probably. Thank you for your insightful information. – Your Common Sense Oct 16 '13 at 12:02
  • @YourCommonSense: That is an utterly absurd straw man. – eggyal Oct 16 '13 at 12:13

2 Answers2

2

A persistent connection is, by definition, one that is not closed (i.e. it persists); the benefit is that PHP remains connected to the database for other scripts to use (i.e. there is no need for it to teardown and setup new database connections each time a script runs).

It is not necessary to write $dbh = null, as that is effectively implied on termination of the script's execution; you can however explicitly write it if you want to signal that you are finished with the connection earlier than at the end of the script (PHP's garbage collector will then, at some point, free up the resource and return it to the connection pool for other scripts' use).

If you want connections to get closed and destroyed at the end of each script, then don't use persistent connections!

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • @StBlade Nope it is not. Check out my answer/link as to why. – samayo Oct 16 '13 at 07:28
  • 2
    @StBlade: It is *conceivable* that persistent connections could expose some security issues: e.g. one script sets a particular database state and then terminates without unsetting that state (perhaps unexpectedly); the next script to use that connection would then be in a state that it did not expect. For example, the first connection might change the session timezone; then a second connection might test to see whether a login is allowed at the current time. Such problems can always be overcome by the second script validating the database state before performing database operation. – eggyal Oct 16 '13 at 07:29
  • @eggyal - might it be possible, though improbable, that even with validation there could be an unexpected state? This would happen in a dual core machine, for instance, where the two statements are running in parallel and the bit flip in the second connection happens after validation but before state change in the first connection? – bvpx Oct 16 '13 at 07:33
  • 1
    @bvpx: A connection cannot be simultaneously used by multiple scripts; it is allocated to a script from the pool when that script attempts to "connect" to MySQL and then returned to the pool when the script releases it (explicitly or impliedly). – eggyal Oct 16 '13 at 07:34
  • The scenario I was wondering about hypothetically involves two connections running scripts in parallel. – bvpx Oct 16 '13 at 07:36
  • 1
    @bvpx: Issues of that sort exist even without persistent connections and are the realm of transaction isolation. – eggyal Oct 16 '13 at 07:39
1

The 'I seem to have 10 more connections' does not seem a legitimate question or concern, because that attribute will just hold the database resource object, as long as PHP is running. Therefore, as @eggyal has said, if you want connection to get closed or destroyed, then don't use persistent connection.

Please read also:

Community
  • 1
  • 1
samayo
  • 16,163
  • 12
  • 91
  • 106
  • I'm getting an error about too many connections when my connections exceed 30, so having 10 connections with maybe 4 unique users on my site per day is a bit concerning for scalability. Unless the two are unrelated. – bvpx Oct 16 '13 at 07:34
  • 2
    @bvpx: As documented under [`Too many connections`](https://dev.mysql.com/doc/en/too-many-connections.html), you can adjust the maximum permitted number of connections by setting [`max_connections`](https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_connections): it seems likely that you are using a low-cost hosting package which has limited the number of connections according to your service level. If you need your application to scale beyond that which your hosting package supports, then you probably need to upgrade your hosting package. – eggyal Oct 16 '13 at 07:37
  • Last question, thanks for everything: is it normal to have 10+ database connections for such a small amount of users? I've heard there are "better ways" to manage connections, perhaps I've made a novice mistake somewhere in my PHP script? – bvpx Oct 16 '13 at 07:39
  • Well, we can't consult without knowing what your php code looks like. If you created your script, in very optimized, and highly efficient manner by following principles such as DRY, then I would guess you are alright, but not if otherwise – samayo Oct 16 '13 at 07:42
  • @bvpx: The number of connections will depend on how many PHP scripts you expect to be running simultaneously at any given time. I, for example, often click to open a number of pages in different tabs all at once. So, as one user, I could consume maybe 5 database connections. If all users are simultaneously connected to the site (and actively using it), you can hazard your own guess as to the resources required. – eggyal Oct 16 '13 at 07:42