Re: Warning: mysql_query(): 4 is not a valid MySQL-Link resource

On Tue, 19 Feb 2008 09:07:50 +0100, AnrDaemon <anrdaemon@xxxxxxxxxxx> wrote:
Greetings, Rik Wasmus.
In reply to Your message dated Thursday, February 14, 2008, 23:46:14,

2. Don't explicitly close the connection -- you typically don't need
to anyway.

Not true. You should always close connections when completely through
with them. It's good programming practice, if nothing else. But it
also means you're releasing system resources you no longer need, making
them available to others.

Looks You have never used PHP as a module...
And database connection will be closed anyway when there are no other
scripts
using it.

If you don't, it will be open for the remainder of the script. On a high
traffic site, and a heavy page, hogging the connection for 0.5 seconds
while another script might need it is not a good thing... Maximum
connections to a database are usually limited.

Sorry if I misunderstood developer's manual, but it said if I use persistent
connections to database (while using PHP as module, of course), database
connection will never be closed even if I explicitly call *_close, while there
are more than one script using this database connection.

Persistent connections are NOT connections used by several script simultaniously. The overhead of connecting etc. is bypassed by leaving the connection open, however, only one process is able to use it at a time (think of the havoc otherwise created by user defined variables / unexpected or wrong results from mysql_insert_id() etc.). Only when the script ends or mysql_close() is called is the connection free for another process to use. In that regard, mysql_pconnect will NOT help you by 'sharing' a connection.

Especially on database servers with a lot of different users, there will usually be more connections open with persistent connections then normal ones, possible leading to problems with the maximum allowed connections to that database. Using persistent connections is only advisable under very particular circumstances, and even then you should test wether it is of use:

1. Connecting to the database has to have significant overhead (database server usuallly not the same as the server running the script).
2. There are only a very limited amount of users to the database (different users will NOT have the same connection, so different users all issuing a pconnect will drastically increase the total number of connections open to the database).
3. All scripts use the exact same variables (character sets, user defined variables are initiated etc.), or have to set all of them explicitly after connecting.
4. There has to be significantly high enough traffic to prevent overhead from idling connections.
5. Temporary tables are well known & documented, or explicitly destroyed in each script (keeping in mind that this should be done even when the script doesn't finish, so in PHP, you'll have to register a shutdown function).
6. Locking and transactions shouldn't be needed (if they are, they'll have to be very carefully executed and always released/rollback'ed on exit).
7. The maximum connections allowed by the database server has to be tweaked very carefully.

Even if all of the above applies, testing wether it is usefull should still be done, and is somwehat difficult, as you'll have to test it for below average load, average load, and peak load. If the database server and the server running the script are the same, which they usually are in the bulk of shared hosting environments, usually connecting to the database has so little overhead one shouldn't even consider it.
--
Rik Wasmus
.

Relevant Pages

  • Re: How can I open an SQL database and be the only one who has access to it?
    ... you can determine how SS permits access to your server. ... SINGLE_USER allows one user at a time to connect to the database. ... controlled by the termination clause of the ALTER DATABASE statement. ... To allow multiple connections, the database must be changed to ...
    (microsoft.public.data.ado)
  • Re: handling db connection error
    ... When a database is in standby, you can't do anything with it. ... connection open (instead of transaction), and it looks like you are trying ... In a T-SQL script, you assume there is already a connection ... To handle connections robustly, ...
    (microsoft.public.sqlserver.programming)
  • Re: ORA-12518, 04030 errors after 9i-10G upgrade.
    ... All went ok until users started connecting in 100+ numbers, database is ... set up for dedicated server connections, this was never a problem in 9i. ... This did not solve problems so bounced database and decreased SGA to ... out of process memory errors. ...
    (comp.databases.oracle.server)
  • Re: ORA-12518, 04030 errors after 9i-10G upgrade.
    ... All went ok until users started connecting in 100+ numbers, database is ... set up for dedicated server connections, this was never a problem in 9i. ... out of process memory errors. ... that Oracle is having issues with OS resources. ...
    (comp.databases.oracle.server)
  • Re: ORA-12518, 04030 errors after 9i-10G upgrade.
    ... New server has 6GB ram. ... All went ok until users started connecting in 100+ numbers, database is ... set up for dedicated server connections, this was never a problem in 9i. ... This did not solve problems so bounced database and decreased SGA to ...
    (comp.databases.oracle.server)