DB Connection in PHP

Jeyaseelansarc · #1 (**permalink**) 04-03-2008, 10:07 PM

Hi,
I want to have multiple active connection in a single request in PHP scripts?

I hope that the connection can establish, but i need to calculate total time taken for the entire connections.

Can anyone help me to get this worked successfully?

Jeyaseelansarc · #2 (**permalink**) 04-03-2008, 11:10 PM

hi,
For the temporary solution i can calculate time in PHP scripts as follows

PHP Code:

  <?php

$time_start = microtime(true);

 
// Sleep for a while

$link = mysql_connect("localhost","user","pwd");

if($link)

    $time_end = microtime(true);

$time = $time_end - $time_start;

 
echo "$times seconds";

Jeyaseelansarc · #3 (**permalink**) 04-03-2008, 11:11 PM

Hi,
is there any pre-defined functions either in mysql or PHP to calculate this time?

Jeyaseelansarc · #4 (**permalink**) 04-04-2008, 02:25 AM

Hi,
As i have known, there are few functions to get whole server information in mysql from PHP.

For e.g

mysql_stat() -> to know status of DB

sureshbabu · #5 (**permalink**) 04-04-2008, 07:22 AM

what is the difference between mysql_connect() and mysql_pconnect()?

sureshbabu · #6 (**permalink**) 04-04-2008, 07:23 AM

what is meant by mysql_unbuffered_query()?

sureshbabu · #7 (**permalink**) 04-04-2008, 07:23 AM

what is the use of mysql_thread_id()?

Jeyaseelansarc · #8 (**permalink**) 04-04-2008, 11:55 PM

Quote:

Originally Posted by sureshbabu

what is the difference between mysql_connect() and mysql_pconnect()?

mysql_connect() Open a connection to a MySQL Server

mysql_pconnect() makes a persistent connection to the database which means a SQL link that do not close when the execution of your script ends.

Jeyaseelansarc · #9 (**permalink**) 04-04-2008, 11:56 PM

Persistent connections are links that do not close when the execution of your script ends. When a persistent connection is requested, PHP checks if there's already an identical persistent connection (that remained open from earlier) - and if it exists, it uses it. If it does not exist, it creates the link. An 'identical' connection is a connection that was opened to the same host, with the same username and the same password (where applicable).

Jeyaseelansarc · #10 (**permalink**) 04-04-2008, 11:57 PM

Quote:

Originally Posted by sureshbabu

what is meant by mysql_unbuffered_query()?

Actually mysql_unbuffered_query() sends a SQL query query to MySQL, without fetching and buffering the result rows automatically, as mysql_query() does. On the one hand, this saves a considerable amount of memory with SQL queries that produce large result sets. On the other hand, you can start working on the result set immediately after the first row has been retrieved: you don't have to wait until the complete SQL query has been performed.

Jeyaseelansarc · #11 (**permalink**) 04-04-2008, 11:58 PM

Quote:

Originally Posted by sureshbabu

what is the use of mysql_thread_id()?

mysql_thread_id() retrieves the current thread ID. If the connection is lost, and a reconnect with mysql_ping() is executed, the thread ID will change. This means only retrieve the thread ID when needed.

Falcon · #12 (**permalink**) 04-07-2008, 09:05 PM

Hi,

Thanks for your replay. what are the advantage and disadvantage of Persistent connections

Thanks
Falcon

Jeyaseelansarc · #13 (**permalink**) 04-08-2008, 12:13 AM

Advantage of presistent connection lies in reduced overhead

fewer TCP connections means lower response latency,
less overhead on the underlying networks,
less memory used for buffers,
and less CPU time used

A browser using a persistent connection can further optimize by pipelining requests.

Jeyaseelansarc · #14 (**permalink**) 04-08-2008, 12:14 AM

Disadvantage of persistent connection lies in the need to identify the beginning and end of each item sent over the connection

Jeyaseelansarc · #15 (**permalink**) 04-08-2008, 10:30 PM

This has to do with the way web servers work. There are three ways in which your web server can utilize PHP to generate web pages.

The first method is to use PHP as a CGI "wrapper". When run this way, an instance of the PHP interpreter is created and destroyed for every page request (for a PHP page) to your web server. Because it is destroyed after every request, any resources that it acquires (such as a link to an SQL database server) are closed when it is destroyed. In this case, you do not gain anything from trying to use persistent connections -- they simply don't persist.

The second, and most popular, method is to run PHP as a module in a multiprocess web server, which currently only includes Apache. A multiprocess server typically has one process (the parent) which coordinates a set of processes (its children) who actually do the work of serving up web pages. When a request comes in from a client, it is handed off to one of the children that is not already serving another client. This means that when the same client makes a second request to the server, it may be served by a different child process than the first time. When opening a persistent connection, every following page requesting SQL services can reuse the same established connection to the SQL server.

The last method is to use PHP as a plug-in for a multithreaded web server. Currently PHP 4 has support for ISAPI, WSAPI, and NSAPI (on Windows), which all allow PHP to be used as a plug-in on multithreaded servers like Netscape FastTrack (iPlanet), Microsoft's Internet Information Server (IIS), and O'Reilly's WebSite Pro. The behavior is essentially the same as for the multiprocess model described before. Note that SAPI support is not available in PHP 3.

Jeyaseelansarc · #16 (**permalink**) 04-08-2008, 10:30 PM

If persistent connections don't have any added functionality, what are they good for?

The answer here is extremely simple -- efficiency. Persistent connections are good if the overhead to create a link to your SQL server is high. Whether or not this overhead is really high depends on many factors. Like, what kind of database it is, whether or not it sits on the same computer on which your web server sits, how loaded the machine the SQL server sits on is and so forth. The bottom line is that if that connection overhead is high, persistent connections help you considerably. They cause the child process to simply connect only once for its entire lifespan, instead of every time it processes a page that requires connecting to the SQL server. This means that for every child that opened a persistent connection will have its own open persistent connection to the server. For example, if you had 20 different child processes that ran a script that made a persistent connection to your SQL server, you'd have 20 different connections to the SQL server, one from each child.

Note, however, that this can have some drawbacks if you are using a database with connection limits that are exceeded by persistent child connections. If your database has a limit of 16 simultaneous connections, and in the course of a busy server session, 17 child threads attempt to connect, one will not be able to. If there are bugs in your scripts which do not allow the connections to shut down (such as infinite loops), the database with only 16 connections may be rapidly swamped. Check your database documentation for information on handling abandoned or idle connections.

Jeyaseelansarc · #17 (**permalink**) 04-08-2008, 10:31 PM

There are a couple of additional caveats to keep in mind when using persistent connections. One is that when using table locking on a persistent connection, if the script for whatever reason cannot release the lock, then subsequent scripts using the same connection will block indefinitely and may require that you either restart the httpd server or the database server. Another is that when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does. In either case, you can use register_shutdown_function() to register a simple cleanup function to unlock your tables or roll back your transactions. Better yet, avoid the problem entirely by not using persistent connections in scripts which use table locks or transactions (you can still use them elsewhere).

Jeyaseelansarc · #18 (**permalink**) 04-08-2008, 10:32 PM

Persistent connections were designed to have one-to-one mapping to regular connections. That means that you should always be able to replace persistent connections with non-persistent connections, and it won't change the way your script behaves. It may (and probably will) change the efficiency of the script, but not its behavior!

Jeyaseelansarc · #19 (**permalink**) 04-08-2008, 10:34 PM

mysql_get_server_info

Retrieves the MySQL server version.

mysql_get_server_info() example

PHP Code:

  <?php

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

if (!$link) {

    die('Could not connect: ' . mysql_error());

}

printf("MySQL server version: %s\n", mysql_get_server_info());

?>

the output

Code:

MySQL server version: 4.0.1-alpha

Jeyaseelansarc · #20 (**permalink**) 04-08-2008, 10:36 PM

mysql_get_host_info

Describes the type of connection in use for the connection, including the server host name.

mysql_get_host_info() example

PHP Code:

  <?php

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

if (!$link) {

    die('Could not connect: ' . mysql_error());

}

printf("MySQL host info: %s\n", mysql_get_host_info());

?>

the output as

Code:

MySQL host info: Localhost via UNIX socket

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode