2008. 10. 17. 11:07

SQLRelay for MySQL Connection Pool

728x90

※ MySQL이 static으로 설치된 경우 Drop-in Replacement Library는 사용할 수 없다.
※ MySQL의 wait_timeout이 28800(8시간)으로 기본값이 되어 있어 sqlrelay가 이 시간동안 유휴상태일 경우 죽는 문제가 있다고 함. MySQL에 주기적인 Ping Query를 요청하므로써 해결 가능함.


0. Current Directory

/root/src



1. Source Download



2. Extract Archive

# tar zxvf rudiments-0.31.tar.gz
# tar zxvf sqlrelay-0.39.2.tar.gz



3. Install rudiments

# cd rudiments-0.31
# ./configure
# make
# make install
# cd ..



4. Install sqlrelay

# cd sqlrelay-0.39.2
# ./configure --enable-small-code \
--enable-mysql-rpath \
--enable-freetds-rpath \ #freetds가 설치된 경우에만 입력(mssql)
--enable-php-rpath \
--with-php-ext-dir=/usr/local/apache/libexec


아래 사항을 확인해보자
***** Summary ***********************************************
 Version      : 0.39.2

 APIs         : C/C++       yes           Perl       yes
                Python      yes           Ruby       no
                PHP         yes           Java       no
                TCL         no            Zope       no

 Connections  : Oracle8     no            MySQL      dynamic       
                PostgreSQL  dynamic       SQLite     no     
                FreeTDS     dynamic       Sybase     no     
                ODBC        no            DB2        no     
                Firebird    no            MDB Tools  no     
*************************************************************

# make
# make install



5. Configure sqlrelay

# vi /usr/local/firstworks/etc/sqlrelay.conf
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
<!-- Regular SQL Relay Instance -->
<instance id="biz-relay" port="9000" socket="/tmp/test-relay.socket"
dbase="mysql" connections="3" maxconnections="
15" maxqueuelength="0"
growby="1" ttl="60" endofsession="commit" sessiontimeout="
600"
runasuser="
root" runasgroup="root" cursors="5" authtier="listener"
handoff="pass" deniedips="" allowedips="" debug="
listener_and_connection"
maxquerysize="65536" maxstringbindvaluelength="4000"
maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1"
listenertimeout="0" reloginatstart="false">

<users>
<user user="test" password="1234"/>
</users>
<connections>
<connection connectionid="testdb"
string="
host=localhost;user=test;password=1234;db=test;"
metric="1" behindloadbalancer="no"/>
</connections>
</instance>
</instances>

※ debug : none(남기지 않음), connection(접속로그만), listener_and_connection(쿼리까지 남음)



6.Edit bash_profile

# cd /root
# vi .bash_profile
PATH에 '/usr/local/firstworks/bin' 추가




7. Start&Stop sqlrelay

# sqlr-start -id biz-relay [-config /usr/local/firstworks/etc/sqlrelay.conf]
# sqlr-stop [biz-relay]

※ sqlr-stop시 instance-id를 입력하지 않으면 모든 instance가 종료된다.



8. Edit extension_dir & Add php module

# vi /usr/local/lib/php.ini
extesion_dir="/usr/local/apache/libexec" (수정)
extension=sql_relay.so (추가)




9. Add MySQL drop-in replacement library to bash script 'apachectl' (Optional)

# vi /usr/local/apache/bin/apachectl
LD_PRELOAD='/usr/local/firstworks/lib/libmysql50sqlrelay.so'
export LD_PRELOAD

※ 이 부분은 PHP의 확장모듈 함수를 쓰지 않고 MySQL 함수인 mysql_connect를 사용했을 경우 자동으로 sqlrelay로 모든 쿼리를 던져준다. 이 부분을 설정할 경우 직접 MySQL에 접속이 불가능하기 때문에 되도록이면 권장하지 않는다.



10. Restart apache

# /usr/local/apache/bin/apachectl restart




11. Test Source

test1.php
<?PHP
/**
* Drop-in Replacement Library
*/
echo 'Using Drop-in Replacement Library<br><hr>';

$dbconn = mysql_connect('localhost:9000', 'test', '1234');


if(!is_resource($dbconn)) {
echo 'no dbconn';
exit;
}

mysql_select_db('BIZ', $dbconn);
$rs = mysql_query("SELECT id, name FROM members", $dbconn);

echo '<xmp>';

while($data = mysql_fetch_assoc($rs)) {
print_r($data);
}
echo '</xmp>';
mysql_close($dbconn);
?>



 

test2.php
<?PHP
/**
* sql_relay module
*/
echo 'Using sql_relay module function<br><hr>';

if(!extension_loaded('sql_relay.so')) dl("sql_relay.so");
$con = sqlrcon_alloc('localhost', 9000, '', 'test', '1234', 0, 1);
$cur = sqlrcur_alloc($con);

if(!sqlrcur_sendQuery($cur, "SELECT id, name FROM members")) {
echo sqlrcur_errorMessage($cur);
echo "\n";
}
sqlrcon_endSession($con);

for($row=0; $row<sqlrcur_rowCount($cur); $row++) {
for($col=0; $col<sqlrcur_colCount($cur); $col++) {
echo sqlrcur_getField($cur,$row,$col);
echo ",";
}
echo "\n";
}

sqlrcur_free($cur);

sqlrcon_free($con);
?>

Trackback 1 Comment 0