Re: [Sqlrelay-discussion] [Fwd: Hei ... performance of SQLRelay]
Brought to you by:
mused
Menu
▾
▴
Re: [Sqlrelay-discussion] [Fwd: Hei ... performance of SQLRelay]
|
From: Ady W. <ady...@re...> - 2006-03-14 04:10:13
|
Hei,
I try to move SQLRelay to the MySQL Machine and connection between
SQLRelay to MySQL become unix socket
and found that the performance is boost :D
Look at the result for the same testing, around 1000sms/seconds could be
inserted :D
Thx
+----------+---------------------+
| count(*) | j |
+----------+---------------------+
| 482 | 2006-03-14 11:06:43 |
| 1056 | 2006-03-14 11:06:44 |
| 1104 | 2006-03-14 11:06:45 |
| 1048 | 2006-03-14 11:06:46 |
| 1112 | 2006-03-14 11:06:47 |
| 1013 | 2006-03-14 11:06:48 |
| 1071 | 2006-03-14 11:06:49 |
| 1129 | 2006-03-14 11:06:50 |
| 1083 | 2006-03-14 11:06:51 |
| 1035 | 2006-03-14 11:06:52 |
| 1056 | 2006-03-14 11:06:53 |
| 1075 | 2006-03-14 11:06:54 |
| 1146 | 2006-03-14 11:06:55 |
| 1134 | 2006-03-14 11:06:56 |
| 1032 | 2006-03-14 11:06:57 |
| 1056 | 2006-03-14 11:06:58 |
| 1046 | 2006-03-14 11:06:59 |
| 1099 | 2006-03-14 11:07:00 |
| 1027 | 2006-03-14 11:07:01 |
| 1076 | 2006-03-14 11:07:02 |
| 1116 | 2006-03-14 11:07:03 |
| 1080 | 2006-03-14 11:07:04 |
| 1114 | 2006-03-14 11:07:05 |
| 1128 | 2006-03-14 11:07:06 |
| 1060 | 2006-03-14 11:07:07 |
| 1092 | 2006-03-14 11:07:08 |
Ady Wicaksono wrote:
> The MySQL and SQLRelay stay on different host.
>
> SQLRelay itself is running on the same machine with webserver + PHP
>
> I don't think the memory is the problem, since we gave 2 Gbytes Memory
> (big enough right).
>
> I try to move SQLRelay to the MySQL machine, and retest again
>
> Thx
>
> David Muse wrote:
>
>> Is the instance of mysql running on the same machine or on another
>> machine? If it's running on the same machine, then try configuring SQL
>> Relay to use a unix socket to connect to mysql rather than host/port.
>>
>> For example, to connect to mysql running on the local machine, you could
>> use a connection string like
>> "socket=/var/lib/mysql/mysql.sock;user=xxxxx;password=xxxxx;db=xxxxx"
>> rather than
>> "host=xxxx;user=xxxx;password=xxxx;db=xxxxx"
>>
>> If the mysql database isn't running on the local host and connecting
>> directly to is is that much faster, then you may have uncovered a bug.
>> I'd expect SQL Relay to be a little slower (like maybe 10%) due to
>> having 1 more layer in the middle, but that much slower. Most of my
>> tests have shown it to be almost as fast as going directly against
>> MySQL.
>>
>> One thing that I noticed, you have 350 database connections running,
>> which is a lot, especially if you're running SQL Relay on the same
>> machine as the web server. Is it possible that the machine you're
>> running SQL Relay on is out of (or nearly out of) memory?
>>
>> Dave
>> dav...@fi...
>>
>> On Mon, 2006-03-13 at 18:13 +0700, Ady Wicaksono wrote:
>>
>>
>>> -------- 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>
>>>
>>>
>>>
>>>
>>>
>>> -------------------------------------------------------
>>> This SF.Net email is sponsored by xPML, a groundbreaking scripting
>>> language
>>> that extends applications into web and mobile media. Attend the live
>>> webcast
>>> and join the prime developer group breaking into this new coding
>>> territory!
>>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
>>>
>>> _______________________________________________
>>> Sqlrelay-discussion mailing list
>>> Sql...@li...
>>> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>>>
>>>
>>
>>
>>
>>
>> -------------------------------------------------------
>> This SF.Net email is sponsored by xPML, a groundbreaking scripting
>> language
>> that extends applications into web and mobile media. Attend the live
>> webcast
>> and join the prime developer group breaking into this new coding
>> territory!
>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
>> _______________________________________________
>> Sqlrelay-discussion mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>>
>>
>>
>>
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting
> language
> that extends applications into web and mobile media. Attend the live
> webcast
> and join the prime developer group breaking into this new coding
> territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
|
