Re: Question about database connections
From: Gordon Burditt (gordonb.3ex2d_at_burditt.org)
Date: 07/08/04
- Next message: Pedro Graca: "Re: CURL and $_SESSION problem"
- Previous message: Ulrich Schöbel: "Re: Matching of optional parts in regular expressions"
- In reply to: Mudge: "Question about database connections"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: 8 Jul 2004 16:18:43 GMT
>My hosting provider only allows me to use 50 connections to my MySQL
>database that my Web site will use.
I presume that means 50 simultaneous connections max. This is what
MySQL can be set to enforce. A connection is in use while the PHP
page is executing (between the time you open and close the connection
or the page exits) or, if you use persistent connections, the
connection is more or less permanent but can be used by one page
running at a time.
Persistent connections can speed things up BUT you have to watch out
for a few things:
- "stale" connections: a page can sometimes get a stale or
"hosed-up" connection that turns out to be useless. For
example, the database timed out the connection for inactivity,
the database got switched on the connection and the new uses
didn't expect that, or the connection is left holding a lock
the new user doesn't know he's supposed to release.
- One cached persistent connection per Apache instance: in
Apache 1.3, each instance of Apache stores its own persistent
connection. If the max number of Apache instances working on
your site is over 50, expect problems. Persistent connections
can INCREASE the number of connections in use because Apache
instances can hold the connection while working on some other
page.
If you have 500 users simultaneously staring at your web page and
occasionally making queries, and the queries run fairly fast, you
probably won't get anywhere near 50 simultaneous connections.
They'll be spending most of their time reading the page, filling
in a query, or reading the answer, and relatively little time
reloading. This may not apply if a query usually takes several
minutes worth of database crunching to deliver an answer.
>I don't know what this 50 connections means exactly. Does this mean that
>only 50 visitors to my Web site can access my database through my Web site
>at one time?
Roughly speaking, only 50 visitors can be LOADING the page at the
same time. And the loading time of non-PHP pages and graphics not
being run through a PHP page doesn't count. Note that if you DO
run graphics through PHP and use the database (sometimes done as
access control on pay sites or to try to prevent deep linking),
browsers often fetch multiple images in parallel, so you might end
up with several simultaneous connections from one guy with one
browser.
>Or does this mean that in my code I can only use 50 connections? and like
>have an identifier for each connection, like if I used persistant
>connections?
If you need 50 database connections for one page, something's
seriously wrong, unless this is the "which databases are up and
which are down status page" which tests each of them every time it
loads. (and then it should be opening one connection, closing it,
and repeat, never using more than one at one time.) There are
occasionally uses for having two or more connections on the same
page. Usually that is done for programming convenience because it
is expected that one of the databases might switch servers while
the other one stays put. For a production, high-volume application
I'd try really hard to avoid doing this. For low-use administrative
maintenance pages, it's less of an issue. (I had one page that did
a side-by-side comparison of the OLD database and the NEW database
while a conversion was in progress, largely to detect problems with
the conversion before going live with it. That was essentially a
throw-away page and was used only by me to quickly locate problems.)
Tips to minimize simultaneous connections:
- Open the connection as late as possible and preferably avoid doing it
at all if it's not necessary.
- Explicitly close the connection as soon as possible.
- Close one connection before opening another (presumably to a different
server), if practical. Better yet, keep re-using the same connection.
- If practical, fetch the data first, close the database, THEN output the
page.
- Avoid, if possible, doing anything time-consuming, like retrieving
another page from another server, with the connection open.
- Persistent connections often make the simultaneous connections worse
while saving processing cost of tearing them down and re-creating them.
If you've got a lot of users and a low connection limit, avoid using
persistent connections. On the other hand if you're running out of CPU
first, maybe you're better off using them.
Gordon L. Burditt
- Next message: Pedro Graca: "Re: CURL and $_SESSION problem"
- Previous message: Ulrich Schöbel: "Re: Matching of optional parts in regular expressions"
- In reply to: Mudge: "Question about database connections"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
