Architecture needed to reduce mysql connections in AJAX/PHP/MYSQL

Question

BACKGROUND:

I am passing variables through AJAX to php file. The php file connects to a server and retrieves the result which it passes back to javascript. This happens every time a user clicks on the request button (about every 5 secs). Thus for each user, the php file (and so the mysql connection) is called once every 5 secs.

ISSUE:

As is apparent above, the number of mysql connections are impractically high.

QUESTION:

Is there a better architecture where instead of having so many mysql connections, I can rather have fewer connections.

enter image description here

I have read a little bit about mysql_pconnect. But what happens if I have to upgrade since I read somewhere that mysqli doesnt support it? How many queries can a single mysql_pconnect handle? If anyone suggests mysql_pconnect then how to implement it?

Which version of PHP do you have. pconnect is deprecated as of 5.5.0, check the [man](http://php.net/manual/en/function.mysql-pconnect.php) page — fredrik, Mar 12 '13 at 06:46
My version supports pconnect (5.1). Thats why I was wondering what happens when I need to upgrade. — user1517108, Mar 12 '13 at 06:50
When you upgrade beyond 5.5 you would need to migrate from pconnect to another method. — fredrik, Mar 12 '13 at 06:52

score 1 · Answer 1 · edited May 23 '17 at 10:25

1

What you are looking for is singleton design pattern for database connections. But it has it's trade off too. Example code for singleton design for database would be as below.

define('DB_NAME', "test");
define('DB_USER', "root");
define('DB_PASS', "rootpass");
define('DB_HOST', "localhost");

class Connection{
    private static $connection_;

    private function __construct(){
        $con = mysql_connect(DB_HOST, DB_USER, DB_PASS);
        mysql_select_db(DB_NAME, $con);
        Connection::$connection_ =  $con;
    }

    public static function getConnection(){
        if( !Connection::$connection_ )
            new Connection;
        return Connection::$connection_;
    }

}

$con = Connection::getConnection();

php singleton database connection, is this code bad practice?

How static vs singleton classes work (databases)

You can find tons of example and information if you google. Hope this helps

edited May 23 '17 at 10:25

Community

1
1

answered Mar 12 '13 at 06:44

Techie

44,706
42
157
243

Yes. Thanks for showing me a starting point. Will help myself for the rest of the info. – user1517108 Mar 12 '13 at 06:51
This will not work for @user1517108, since you have PHP 5.1. Singletons weren't possible in PHP berfore 5.3. According to the man page for the static keyword – fredrik Mar 12 '13 at 06:56
1

@user1517108 pconnect is essentially a connection pool. Managed by PHP. Normal connection pooling is not possible, since the pool would be destroyed when the script terminates... there is no way to have persistent user defined objects in PHP 5.1. – fredrik Mar 12 '13 at 07:12

score 1 · Accepted Answer · answered Mar 12 '13 at 07:01

1

Is there a better architecture where instead of having so many
mysql connections, I can rather have fewer connections.

Don't really know, but I think that for you the proposed pconnect is the best available option. Unless you have either mysqli or PDO_mysql available now?

I have read a little bit about mysql_pconnect.
But what happens if I have to upgrade since I read somewhere that mysqli doesnt support it?

You would probably need to change method when upgrading beyond PHP 5.5.

How many queries can a single mysql_pconnect handle?

Unlimited, as long as the connection is kept alive. If there are no available free connections a new one is created.

If anyone suggests mysql_pconnect then how to implement it?

Change your current mysql_connect calls to mysql_pconnect. That should be all.

answered Mar 12 '13 at 07:01

fredrik

6,483
3
35
45

I dont think changin gto mysql_pconnect should do it as it will not solve the problem of lowering count of multiple connections. Somehow I need to have a method to save the connection identifier and reuse it in future requests to the php file. Dont you think so? – user1517108 Mar 12 '13 at 07:05
[pconnect](http://php.net/manual/en/function.mysql-pconnect.php) will save the connection identifier for you. `Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use ( mysql_close() will not close links established by mysql_pconnect()).` – fredrik Mar 12 '13 at 07:06
but when the php is called again will it not create a new pconnect rather than re-use the older pconnect? – user1517108 Mar 12 '13 at 07:07
According to the man page, it should reuse old ones. If they exist. Assuming that the username/password and other connection options are the same that it. check the [man](http://php.net/manual/en/function.mysql-pconnect.php) page – fredrik Mar 12 '13 at 07:09
oh.. you are right. silly mistake of mine not to check it myself. – user1517108 Mar 12 '13 at 07:11
Thanks for the solution. Sadly I run php version less than 5.3 and my webhost does not allow persistent connection. I have asked a new question regarding thid issue at http://stackoverflow.com/questions/15355561/mysql-php-how-to-implement-connection-pool-with-php-v-5-2-and-no-persistent-conn – user1517108 Mar 12 '13 at 07:28

Architecture needed to reduce mysql connections in AJAX/PHP/MYSQL

2 Answers2