[Sqlrelay-discussion] [Fwd: Hei ... performance of SQLRelay]

[Ady W. <ady...@re...>]

-------- Original Message --------
Subject: 	Hei ... performance of SQLRelay
Date: 	Mon, 13 Mar 2006 18:01:08 +0700
From: 	Ady Wicaksono <ady...@in...>
To: 	sql...@li...



Today i try to benchmark SQLRelay performance and found that this tools 
is not good in performance.

I try with PHP SQLRelay using this script
<?
    $id = substr(substr(uniqid(mktime(),4),16),0,8).chr(rand(65,90));
   
$con=sqlrcon_alloc("127.0.0.1",9000,"/tmp/sqlrelay.socket","username","password",0,1);
    $cur=sqlrcur_alloc($con);

   sqlrcur_sendQuery($cur,"INSERT INTO testaja.images (i)  VALUES 
('$pin')");
   sqlrcon_endSession($con);
   sqlrcur_free($cur);
   sqlrcon_free($con);
?>

Hit with apache bencmark
ab -n 30000 -c 100 http://localhost/smshdlr/local6288/sqlrelay.php

and found that around 200 - 300rows/seconds could be inserted.

+----------+---------------------+
| count(*) | j                   |
+----------+---------------------+
|      299 | 2006-03-13 17:37:16 |
|      297 | 2006-03-13 17:37:17 |
|      302 | 2006-03-13 17:37:18 |
|      267 | 2006-03-13 17:37:19 |
|      287 | 2006-03-13 17:37:20 |
|      288 | 2006-03-13 17:37:21 |
|      236 | 2006-03-13 17:37:22 |
|      260 | 2006-03-13 17:37:23 |
|      259 | 2006-03-13 17:37:24 |
|      251 | 2006-03-13 17:37:25 |
|      246 | 2006-03-13 17:37:26 |
|      259 | 2006-03-13 17:37:27 |
|      276 | 2006-03-13 17:37:28 |
|      229 | 2006-03-13 17:37:29 |
|      256 | 2006-03-13 17:37:30 |
|      259 | 2006-03-13 17:37:31 |
|      239 | 2006-03-13 17:37:32 |
|      239 | 2006-03-13 17:37:33 |
|      268 | 2006-03-13 17:37:34 |
|      258 | 2006-03-13 17:37:35 |
|      226 | 2006-03-13 17:37:36 |
|      259 | 2006-03-13 17:37:37 |
|      297 | 2006-03-13 17:37:38 |
|      284 | 2006-03-13 17:37:39 |
|      232 | 2006-03-13 17:37:40 |
|      306 | 2006-03-13 17:37:41 |
|      241 | 2006-03-13 17:37:42 |
|      268 | 2006-03-13 17:37:43 |
|      285 | 2006-03-13 17:37:44 |
|      248 | 2006-03-13 17:37:45 |
|      215 | 2006-03-13 17:37:46 |
|      227 | 2006-03-13 17:37:47 |
|      204 | 2006-03-13 17:37:48 |
|      248 | 2006-03-13 17:37:49 |
|      269 | 2006-03-13 17:37:50 |
|      235 | 2006-03-13 17:37:51 |
|      242 | 2006-03-13 17:37:52 |
|      229 | 2006-03-13 17:37:53 |
|      236 | 2006-03-13 17:37:54 |
|      205 | 2006-03-13 17:37:55 |
|      274 | 2006-03-13 17:37:56 |
|      235 | 2006-03-13 17:37:57 |
|      293 | 2006-03-13 17:37:58 |


I use MySQL -  PHP API and found that it's faster

<?
       $c = mysql_pconnect("dbhost","username","userpassword");
       $r = mysql_query ("INSERT INTO testaja.images (i)  VALUES 
('$pin')",$c);
       mysql_close($c);
?>

And found that more than 1000 rows/seconds could be inserted :)
+----------+---------------------+
| count(*) | j                   |
+----------+---------------------+
|     1195 | 2006-03-13 17:51:27 |
|     1317 | 2006-03-13 17:51:28 |
|     1341 | 2006-03-13 17:51:29 |
|     1289 | 2006-03-13 17:51:30 |
|     1243 | 2006-03-13 17:51:31 |
|     1239 | 2006-03-13 17:51:32 |
|     1323 | 2006-03-13 17:51:33 |
|     1283 | 2006-03-13 17:51:34 |
+----------+---------------------+

Any explanation about this issue?

SQLRelay i use is:
1. connection is using unix sockect
2. Configuration is like this

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
       <instance id="dbisat" port="9000" socket="/tmp/sqlrelay.socket" 
dbase="mysql" connections="350" maxconnections="450" 
maxqueuelength="12000" growby="2" ttl="60"  runasuser="sqlrelay" 
runasgroup="sqlrelay" authier="listener" handoff="pass" 
idleclienttimeout="-1">
               <users>
                       <user user="username" password="userpassword"/>
               </users>
               <connections>
                       <connection connectionid="db1" 
string="user=xxxx;password=xxxxx;db=xxx;host=xxxx;" metric="1" 
behindloadbalancer="no"/>
               </connections>
       </instance>
</instances>