[RESOLVED] MySQL Performance Benchmark

ncracraf

I'm building a proof-of-concept website using PHPv5.05, MySQLv5.0.15, and Apachev2.0.55. Although I just started the project, I'm already noticing some potential performance issues. This is my first time building an application using PHP and MySQL so I'm not sure if this performance is typical or not...

I have a VERY simple query: "SELECT * from tablename"; where tablename has only 20 sample records and one primary key. Running the query directly against the database returns in less than one second. Populating the data on a web page takes between 5-7 seconds.

My PHP code is as follows:

// Build SQL string
  $sqlstr = "SELECT * FROM tablename" ;

  // Select database; note - database connection is performed in an include file
  mysql_select_db($db, $db_conn);
  $rs = mysql_query($sqlstr, $db_conn) or die(mysql_error()) ;

  // Retrieve result set 
  $row_rs = mysql_fetch_assoc($rs);

My HTML code to display the records is as follows:

<table width="522" border="0">
  <tr>
    <td width="514"><table width="524" border="0">
      <tr>
        <td colspan="6" >results </td>
      </tr>
     [code=php]<?php do { ?>

<?php echo $row_rs['VAL1']; ?>

</td>
<td>

<?php echo $row_rs['VAL2']; ?>

</td>
<td>

<?php echo $row_rs['VAL3']; ?>

</td>
</tr>

<?php } while ($row_rs = mysql_fetch_assoc($rs)); ?>

</table></td>
</tr>
</table>[/code]

I'm currently building the application on my personal development machine running XP Professional, SP2. I have not made any changes to the my.ini file for cacheing, block size, etc.

What's especially puzzling is that several days ago this page was populated in about 1 second. I hadn't made any code or configuration changes. I restarted the MySQL service to flush the cache, if there was one. After restarting the service, performace was still 1 second. Since yesterday, however, performance has returned to 5-7 seconds for completing the code above.

I'm studying section 7 of the MySQL guide but, in the meantime, any thoughts on this random performance issue would be greatly appreciated.

Thanks.

Roger_Ramjet

Just forget about it. 😃

What you are getting is just the kind of crap you should expect from MickeySoft operating systems and products. Worry about performance once you have the go-ahead and are into capacity planning and architecture design.
Could be any number of reasons for the deterioration in performance. Watch your network connection and you will see all sorts of unnneccessary activity - XP looking for a DNS server to resolve 'localhost' 😕 , network browser (NETBIOS :eek: ) looking for a WINS server even when WINS is not installed, and god knows what other crap to suck up your processing power and user time. Just accept that MS operating systems are a joke and use a *NIX server and Apache for your live system. Then you will have a platform that can be monitored and tuned for performance in a consistent manner.

ncracraf

I moved my files to the hosting platform for the first time today. I'm using Linux instead of XP with the provider. I'm not sure if it's the different OS's or the fact that I was performance benchmarking on a local development machine. Nonetheless, performance on the server (using Linux) is extremely fast.

Thanks for the feedback.

Roger_Ramjet

That is normal, UNIX outperforms M$ at every level, if only because you have complete control over NIX components and configuration while all M$ operating systems are full of crap that no-one understands or can control, not even the plonkers at M$ who wrote the code.