Detailed explanation of PHP connection pool

The php script itself cannot be used as a connection pool, because the php script will release all memory resources after the interpretation and execution. Of course, the database connection used in it will also be released, but some middleware can also be used as a connection pool. Yes, as long as the relevant drivers for PHP are provided, you can create a PHP connection pool yourself, but you will never be able to create a 100% pure PHP connection pool. mysql_pconnect is a simulated connection pool built into PHP, but this mechanism is not implemented using PHP scripts. But one request can reuse the link, reducing the consumption caused by new.

1. <?php  
        class ConnecToDB  
        {    
            private static $instance=array();  
            //防止外部创建新的数据库连接类  
            private function _constuct(){}  
            static public function Connect()  
            {      
                //连接类不够100，创建新类  
                if(count(self::$instance)<100)  
                {  
                    $newDb=new self();  
                    self::$instance[]=$newDb;  
                    return $newDb::ConDB();  
                }  
                else  
                {      
                    //随机数保证数据库连接均衡  
                    $i=rand(0,99);  
                    $new_obj=self::$instance[$i];  
                    return $new_obj::ConDB();  
                }  
            }  
            static private function ConDB()  
            {  
                try  
                {  
                    $connec=mysql_connect("127.0.0.1","数据库账户","数据库密码");  
                    mysql_select_db("数据库名");//选择数据库    
       
                }  
                catch(Exception $e)  
                {  
                    $errors[]=$e->getMessage();  
                  }
   }

The main function of the connection pool is to save the time of opening the database. The connection pool mechanism opens N database connections in advance, caches them, and directly uses these opened connections when the database needs to be used, thus saving time. The existence of the connection pool basically eliminates the time and CPU overhead of database connection disconnection.
Connection pool solution:
1. pconnect (persistent connection): The principle of pconnect is similar to that of connection pool. The program closes the connection, but PHP does not really close it. When it is opened again, the available one is used directly. connect.
If Mysql occurs due to too much access, you should configure the max_connection value in my.cnf of the Mysql database service, such as max_connections = 2000.
2. mysql proxy.
3. memcache: a database cache implementation for mysql.
4. SQL Relay: An open source database pool connection proxy server. Supports Oracle, MySQL, mSQL, PostgreSQL, Sybase, MS SQL Server, IBM DB2, Sybase, SQLite, Lago, ODBC, MS Access, etc.
Installation and configuration [SQL SERVER](http://blog.sina.com.cn/s/blog_4dd475390100hbck.html), to install SQL Relay you need to install Rudiments first:
1. Install Rudiments:
# tar vxzf rudiments-0.28.2.tar.gz
# cd rudiments-0.28.2
# ./configure --prefix=/usr/local/rudiments
# make
# make install
2. Install SQL Relay:
# tar vxzf sqlrelay-0.36.4.tar.gz
# cd sqlrelay-0.36.4
# ./configure --prefix=/usr/local/sqlrelay --with-rudiments-prefix=/usr/local/rudiments --with-mysql-prefix=MySQL installation path --with-freetds-prefix=FreeTDS installation path --with-oracle-home=Oracle installation path --with -php-prefix=PHP installation path
# make
# make install
3. Set up PHP: modify extension_dir = "./" in php.ini, and change the above content to: extension_dir = "/usr /local/php/lib/php/extensions/no-debug-non-zts-20050922".
Modify according to the path of PHP installation. Not every version of PHP has this path. Add the following content extension=sql_relay.so in php.ini.
4. Modify the configuration file of SQL Relay
# cd /usr/local/sqlrelay/etc
# cp sqlrelay.conf.example sqlrelay.conf
Change the content of sqlrelay.conf to:

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
<instance id="msdetest" port="9000" socket="/tmp/msdetest.socket" dbase="freetds" connections="5" maxconnections="10" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="5" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass">
<users>
<user user="sa" password="sa"/>
</users>
<connections>
<connection connectionid="msdetest" string="server=msde;db=pubs;user=sa;password=sa;" metric="1"/>
</connections>
</instance>
</instances>

Start SQL Relay and test;
1. Start SQL Relay
# export PATH=$PATH:/usr/local/sqlrelay/bin
# sqlr-start - id msdetest
2. Use SQL tool:
# sqlrsh -id msdetest
You can directly enter the SQL statement to stop SQL Relay:# sqlr-stop msdetest
3. Test PHP and write a PHP file with the content As follows:

<?
$con=sqlrcon_alloc("msdetest",9000,"/tmp/msdetest.socket","sa","sa",0,1);
$cur=sqlrcur_alloc($con);
sqlrcur_sendQuery($cur,"select * from t_gifts");
for ($row=0; $row<sqlrcur_rowCount($cur); $row++) {
for ($col=0; $col<sqlrcur_colCount($cur); $col++) {
echo sqlrcur_getField($cur,$row,$col);
echo ",";
}
echo "<br>\n";
}
sqlrcur_free($cur);
sqlrcon_free($con);
?>

php+sqlrelay+mysql implements connection pool and read-write load balancing:
In order to effectively solve the bottleneck of concurrent access, use the master-slave mode of multiple databases To increase the number of concurrent visits to the web. The master-slave mode is for data synchronization.
sqlrelay solves the connection pool problem and achieves load balancing of read and write separation. sqlrelay configures three instances A/B/C. A is responsible for reading data from the master and slave, B is responsible for writing data, and only writes to the master, and C is the router responsible for scheduling applications. Does php connect to the database through A or B. In the actual configuration, since the master is responsible for read and write operations, in the configuration of instance A, the number of connections from the master can be slightly reduced, and the number of connections for reading data from the slave connection can be slightly increased to achieve balance.
Configuration and Application (http://blog.163.com/lgh_2002/blog/static/4401752620107393057989/):

1. MySQL master/slave configuration






##mster/slave configuration






master:192.168.1.51
slave:192.168 .1.50
1. Add
binlog-do-db=book book to the master configuration
/etc/my.cnf and ensure that
server-id=1
log-bin= mysql-bin
Authorize the rep user to perform replication operations
GRANT REPLICATION SLAVE ON book.* TO rep@192.168.1.50 IDENTIFIED BY '123456';

Restart the master service###2. Configure slave## #vi /etc/my.cnf###Set the following 4 lines###server-id = 2###master-host = 192.168.1.51###master-user = rep###master-password = 123456# ##Restart slave###3. Import the original data of the master into the slave. ###

二、sqlrelay配置
当前行业中比较流行的连接池解决方案几乎都不支持php，经过多番努力终于在找到了一个开源的连接池技术--------sqlrelay。
sqlreplay支持的语言：C C++ Perl Python PHP Ruby Java TCL Zope。
sqlreplay支持的数据库：Oracle MySQL mSQL PostgreSQL Sybase MS SQL Server IBM DB2 Interbase Sybase SQLite ODBC MS Access
基本思路：
1、配置2个实例用以最终处理业务
clubs-read
clubi-write
其中读取的 instance分别配置两个连接，且两个连接启动对等的连接数。
2、配置一个instance来调度读写操作，即clubr
通过router来区分读写连接不同的mysql数据库。

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
<!-- club Instance -->
<instance id="clubs" port="9002" socket="/tmp/clubs.socket" dbase="mysql" connections="10" maxconnections="20" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0">
<users>
<user user="club" password="edb:club"/>
</users>
<connections>
<connection connectionid="master51" string="host=192.168.1.51;port=3306;db=book;user=club;password=club;" metric="1" behindloadbalancer="no"/>
<connection connectionid="slave50" string="host=192.168.1.50;port=3306;db=book;user=club;password=club;" metric="1" behindloadbalancer="no"/>
</connections>
</instance>
<instance id="clubi" port="9003" socket="/tmp/clubi.socket" dbase="mysql" connections="10" maxconnections="40" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0">
<users>
<user user="club" password="edb:club"/>
</users>
<connections>
<connection connectionid="master51" string="host=192.168.1.51;port=3306;db=book;user=club;password=club;" metric="1" behindloadbalancer="no"/>
</connections>
</instance>

php.ini文件在系统中的优先级：PhpIniDir、注册表键值、环境变量%PHPRC%、PHP5的根目录（For CLI），或者WWW的根目录（For SAPI moudles）、Windows目录（C：\windows）。
PHP5特征：加入了面向对象机制、对于XML的复杂处理、异常处理机制。
PHP6特征：
支持Unicode：虽然Unicode占用较多的空间，但Unicode带来的便利性，远超过占用空间的缺点。PHP也可以在.ini文件中设定是否开启支持Unicode。
命名空间：命名空间是一种避免因函数或者类之间的命名冲突，而使你的函数和类以及方法无法读取，而不使用前缀命名惯例的一种方法。
PHP6.0令人激动的Web 2.0特性。
SOAP: 简单对象访问协议 (SOAP:Simple Object Access Protocol)SOAP 可以和现存的许多因特网协议和格式结合使用，包括：HTTP、SMTP、MIME、RPC。
XML: 从PHP 5.1版本开始,XMLReader和XMLWriter就已经包含在PHP内核,它可以让它可以让XML编程更加轻松。
Register Globals 将被移除：它虽满方便的，但是却忽略会带来程序上安全性的隐患，PHP4.3.x版开始时，此项默认设置值即是关闭状态，PHP6后PHP3将完全无法使用。
Magic Quotes 将消失：Magic Quotes主要是自动转义需要转义的字符，此项功能移除叶符合大多数PHP开发者的心声。
Safe Mode 取消。
’var’ 别名为 ‘public’：在类中的var声明变成public的别名，相信是为了兼容PHP5而作的决定，PHP6现在也可以称作为OO语言了。
通过引用返回将出错：现在透过引用返回编译器将会报错 例如$a =& new b()、function &c()，OO语言默认就是引用，所以不需要再使用&了。 
zend.ze1 compatbility mode 将被移去 Zend.ze1相容模式将被移去PHP5是为兼容旧有PHP4，所以在.ini中可选择是否开启相容模式。
Freetype 1 and GD 1 support 将不见这两个是很久的Libs，所以不再支持，GD1早已被现在的GD2取代了。
dl() 被移到 SAPI 中dl()主要是让设计师加载extension Libs，现在被移到 SAPI 中。
Register Long Array 去除从PHP5起默认是关闭，再PHP6中正式移除。
Extension的变更：如XMLReader、XMLWriter将不再是以Extension的方式出现，他们将被移入到PHP的核心之中默认是开启。
ereg extension将被放入PECL，代表着它将被移出PHP核心，这也是为了让路给新的正则表达式extension，此外，Fileinfo extension 也将被导入PHP的核心之中。
APC将被导入核心：这是一个提高PHP性能的功能，现在它将被放入PHP核心中，并且可以选择是否启用APC。
告别ASP风格的起始标签：原来是为了取悦ASP开发者转向使用PHP，现今已经不再需要这种做法了。
PHP6.0除了增加新特性，一些会给系统带来不稳定因素和安全隐患的特性也将被取消，取消列表：magic_quotes、register_globals、register_long_arrays、safe_mode、magic_quotes。

The above is the detailed content of Detailed explanation of PHP connection pool. For more information, please follow other related articles on the PHP Chinese website!