Question about database connections

[Mudge]

Hi,

My hosting provider only allows me to use 50 connections to my MySQL
database that my Web site will use.

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?

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?

[Markus Ernst]

Re: Question about database connections

"Mudge" <mark1822@hotma il.com> schrieb im Newsbeitrag
news:YF5Hc.4428 7$XM6.5528@attb i_s53...[color=blue]
> Hi,
>
> My hosting provider only allows me to use 50 connections to my MySQL
> database that my Web site will use.
>
> 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?
>
> 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?[/color]

I am not absolutely sure but I think it is your first point (50 connections
at a time). To be absolutely sure you might contact your provider's support
department.

--
Markus

[Gordon Burditt]

Re: Question about database connections

>My hosting provider only allows me to use 50 connections to my MySQL[color=blue]
>database that my Web site will use.[/color]

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.
[color=blue]
>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?[/color]

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.
[color=blue]
>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?[/color]

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

[Mudge]

Re: Question about database connections

Gordon Burditt wrote:
[color=blue][color=green]
>>My hosting provider only allows me to use 50 connections to my MySQL
>>database that my Web site will use.[/color]
>
> 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.
>[color=green]
>>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?[/color]
>
> 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.
>[color=green]
>>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 ?[/color]
>
> 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[/color]

Thank you. This is very helpful. I've put this posting in my file for later
reference.