Thanks for the suggestions. I will change the tables to in innodb and change my
php coding. Then I will revisit the benchmarking with hopefully improving
results. Thanks again!
Regards,
Alvin
On Aug 16, 2011, at 7:57 PM, Johnny Withers <joh...@pixelated.net> wrote:
> This is a poor benchmark because the query never changes. Query cache takes
> over after first request.
>
> Sent from my iPad
>
> On Aug 16, 2011, at 4:28 PM, "Alvin Ramos" <alvin.ra...@reachsmart.com> wrote:
>
>> I know my previous email was vague, it was sent via smartphone. I’ve got a
>> simple PHP page pulling information from one of larger database tables:
>>
>>
>>
>> PHP Code:
>>
>>
>>
>> <html>
>>
>> <head>
>>
>> <basefront face="Arial">
>>
>> </head>
>>
>>
>>
>> <body>
>>
>>
>>
>> <?php
>>
>>
>>
>> // set server access variables
>>
>> $host = "127.0.0.1";
>>
>> $user = "web";
>>
>> $pass = "password";
>>
>> $db = "md ";
>>
>>
>>
>> // open connections to database
>>
>> $connect = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
>>
>>
>>
>> // select database to use
>>
>> mysql_select_db($db) or die ("Unable to select database!");
>>
>>
>>
>> // create SQL query string
>>
>> $query = "SELECT * FROM members limit 1000";
>>
>>
>>
>> //execute query and obtain result set
>>
>> $result = mysql_query($query) or die ("Error in query: $query. " .
>> mysql_error());
>>
>>
>>
>> // are there any rows in the result?
>>
>> if (mysql_num_rows($result) > 0)
>>
>> {
>>
>> // yes
>>
>> // iterate through result set
>>
>> // format query results as table
>>
>> echo "<table cellpadding=10 border=1>";
>>
>> while($row = mysql_fetch_assoc($result))
>>
>> {
>>
>> echo "<tr>";
>>
>> echo "<td>" . $row['member_id'] . "</td>";
>>
>> echo "<td>" . $row['fname'] . "</td>";
>>
>> echo "</tr>";
>>
>> }
>>
>> echo "</table>";
>>
>> }
>>
>> else
>>
>> {
>>
>> // no
>>
>> // print status message
>>
>> echo "NO rows found!";
>>
>> }
>>
>>
>>
>> // close connection
>>
>> mysql_close($connect);
>>
>>
>>
>> ?>
>>
>>
>>
>> </body>
>>
>> </html>
>>
>>
>>
>> I’ve got apache benchmark then running 5 concurrent connections 10,000
>> times. I changed the $host to the IP for the 5.5 server then to the 5.1
>> server and here are one of my many results:
>>
>>
>>
>> 5.1 results:
>>
>>
>>
>> Server Software: Apache/2.2.3
>>
>> Server Hostname: aramos.dev
>>
>> Server Port: 80
>>
>>
>>
>> Document Path: /mysqlfetch51.php
>>
>> Document Length: 35808 bytes
>>
>>
>>
>> Concurrency Level: 5
>>
>> Time taken for tests: 3263.909079 seconds
>>
>> Complete requests: 10000
>>
>> Failed requests: 0
>>
>> Write errors: 0
>>
>> Total transferred: 359640000 bytes
>>
>> HTML transferred: 358080000 bytes
>>
>> Requests per second: 3.06 [#/sec] (mean)
>>
>> Time per request: 1631.955 [ms] (mean)
>>
>> Time per request: 326.391 [ms] (mean, across all concurrent requests)
>>
>> Transfer rate: 107.60 [Kbytes/sec] received
>>
>>
>>
>> Connection Times (ms)
>>
>> min mean[+/-sd] median max
>>
>> Connect: 0 1 2.3 1 155
>>
>> Processing: 593 1629 699.7 1524 13580
>>
>> Waiting: 574 1611 699.7 1506 13562
>>
>> Total: 595 1630 699.7 1526 13580
>>
>>
>>
>> Percentage of the requests served within a certain time (ms)
>>
>> 50% 1526
>>
>> 66% 1725
>>
>> 75% 1856
>>
>> 80% 1944
>>
>> 90% 2215
>>
>> 95% 2559
>>
>> 98% 4339
>>
>> 99% 4741
>>
>> 100% 13580 (longest request)
>>
>>
>>
>> 5.5 results:
>>
>>
>>
>> erver Software: Apache/2.2.3
>>
>> Server Hostname: aramos.dev
>>
>> Server Port: 80
>>
>>
>>
>> Document Path: /mysqlfetch.php
>>
>> Document Length: 35808 bytes
>>
>>
>>
>> Concurrency Level: 5
>>
>> Time taken for tests: 3400.300474 seconds
>>
>> Complete requests: 10000
>>
>> Failed requests: 0
>>
>> Write errors: 0
>>
>> Total transferred: 359640000 bytes
>>
>> HTML transferred: 358080000 bytes
>>
>> Requests per second: 2.94 [#/sec] (mean)
>>
>> Time per request: 1700.150 [ms] (mean)
>>
>> Time per request: 340.030 [ms] (mean, across all concurrent requests)
>>
>> Transfer rate: 103.29 [Kbytes/sec] received
>>
>>
>>
>> Connection Times (ms)
>>
>> min mean[+/-sd] median max
>>
>> Connect: 0 1 2.7 1 168
>>
>> Processing: 595 1697 724.8 1598 14505
>>
>> Waiting: 577 1679 724.8 1580 14486
>>
>> Total: 596 1698 724.8 1600 14506
>>
>>
>>
>> Percentage of the requests served within a certain time (ms)
>>
>> 50% 1600
>>
>> 66% 1799
>>
>> 75% 1939
>>
>> 80% 2028
>>
>> 90% 2314
>>
>> 95% 2640
>>
>> 98% 4387
>>
>> 99% 4805
>>
>> 100% 14506 (longest request)
>>
>>
>>
>>
>>
>> I’ve ran tests even against our web sites and its slower than the 5.1
>> server. Any suggestions, anything I should change on the 5.5 server? The
>> hardware and OS is identical from the 5.1 server. Thanks!
>>
>>
>>
>> Alvin Ramos
>>
>>
>>
>> From: w...@pythian.com [mailto:w...@pythian.com] On Behalf Of Singer X.J.
>> Wang
>> Sent: Tuesday, August 16, 2011 4:08 PM
>> To: Alvin Ramos
>> Cc: Prabhat Kumar; Reindl Harald; mysql@lists.mysql.com
>> Subject: Re: shall i jump from 5.1 to 5.5
>>
>>
>>
>> Are you doing concurrent workloads?
>>
>>
>>
>> On Tue, Aug 16, 2011 at 16:04, Alvin Ramos <alvin.ra...@reachsmart.com>
>> wrote:
>>
>> I've been running some bench marking between 5.1 and 5.5 myself and haven't
>> notice any huge performance improvements on 5.5. Even though white papers
>> claim it put performs 5.1. Any noticing the same or have some input in my
>> findings?
>>
>> Regards,
>> Alvin
>>
>>
>> On Aug 16, 2011, at 3:55 PM, Prabhat Kumar <aim.prab...@gmail.com> wrote:
>>
>>> correct. you have to understand the problem first.
>>> but still its recommendable to always use latest stable version.
>>>
>>> On Tue, Aug 16, 2011 at 10:02 AM, Reindl Harald
>>> <h.rei...@thelounge.net>wrote:
>>>
>>>>
>>>>
>>>> Am 16.08.2011 17:59, schrieb Luis Daniel Lucio Quiroz:
>>>>> as far as my readings, they claim that 5.5 is the best
>>>>>
>>>>> my question is, shall i jump from 5.1 to 5.5.
>>>>>
>>>>> right now i have a performance problem, would 5.5 help me in that?
>>>>>
>>>>> Regards,
>>>>>
>>>>> LD
>>>>>
>>>> why do you believe without any information you will get
>>>> a useful answer? "i have a performance problem" is simply
>>>> NO information if you even do not tell which storage engine
>>>> and wich sort of problem in which context
>>>>
>>>> if you should update can nobody answer for you because we
>>>> do not know if you have any crappy apps / scripts which
>>>> would have troubles?
>>>>
>>>> we have upgraded some hundret webspaces and two dbmail-servers
>>>> in februray becaus we know our self written applications and
>>>> having test-environments, if you can do this can nobody say
>>>>
>>>>
>>>
>>>
>>> --
>>> Best Regards,
>>>
>>> Prabhat Kumar
>>> MySQL DBA
>>>
>>> My Blog: http://adminlinux.blogspot.com
>>> My LinkedIn: http://www.linkedin.com/in/profileprabhat
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=w...@singerwang.com
>>
>>
>>
>> --
>> The best compliment you could give Pythian for our service is a referral.
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=alvin.ra...@reachsmart.com
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
