• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1086
  • Last Modified:

MySQL performance & network speed optimization

I have MySQL 5 installed on a Windows Server 2003 x64 box which is part of a small network.  It is running much more slowly than I would like, so I want to optimize any settings that would affect speed of both processing and communication.

When network clients (using Access front-end, myODBC driver) query the MySQL server for a recordset, or if I export a table from Access via ODBC to a MySQL catalog, network bandwidth utilization maxes at 0.5%-1.0% of 1gbps.  How can I improve this?

Queries of all sorts (passed-through directly against MySQL) simply run more slowly (by about 200-400%) than if I use MS Access alone.  What configuration settings should I use, given my setup below, to maximize usage of resources?

The tables are all presently myISAM.  I've done substantial index optimization.  The database server has 2 AMD Opterons, 3.5GB RAM, 4x15Krpm RAID/0.  Nothing but the OS and MySQL is running on this machine -- it is a dedicated database server, and I want it to perform as such.

Any help?
0
psk1
Asked:
psk1
  • 2
1 Solution
 
psk1Author Commented:
Queries appear to get hung up in the "Sending data" phase but this doesn't appear to be sending data to client -- CPU utilization is 100% of 1 core -- not sure why it says "Sending data"
0
 
fiboCommented:
It seems that the data is trapped in the journey over the network.
Just to be sure, you might want to check this.
Can you accumulate data, then send it?
I was thinking to something like changing your code from
---------------------------------
$query = ...;
$My_Data=array();
$res=@mysql_query($query, $id_link);
$nb=@mysql_num_rows($res) ;

if ($nb>0 ) {
      while ($ldonnees=mysql_fetch_row($res)){
         $My_Data[]=$ldonnees['0'];....
         echo 'This record value : [' . $ldonnees['0'] . '] -- [' . $ldonnees['1'] . ']<br>'; ...
      } ;
---------------------------------
to (code for php4 - in php5 you do not need the microtime_float function and can use an equivalent native function)
---------------------------------
...
function microtime_float() {  
         list($usec, $sec) = explode(" ", microtime());  
         return ((float)$usec + (float)$sec);
}
....
$query = ...;
$My_Data=array();
$time_1 = microtime_float();// ------------------
$res=@mysql_query($query, $id_link);
$nb=@mysql_num_rows($res) ;

if ($nb>0 ) {
      while ($ldonnees=mysql_fetch_row($res)){
         $My_Data[]=$ldonnees['0'];....
        // No data sent over the network
      } ;
$time_2 = microtime_float();// ------------------

$My_Data=array();
$time_3 = microtime_float();// ------------------
$res=@mysql_query($query, $id_link);
$nb=@mysql_num_rows($res) ;

if ($nb>0 ) {
      while ($ldonnees=mysql_fetch_row($res)){
         $My_Data[]=$ldonnees['0'];....
         echo 'This record value : [' . $ldonnees['0'] . '] -- [' . $ldonnees['1'] . ']<br>'; ...
      } ;
$time_4 = microtime_float();// ------------------

echo '<br><br><b>--------- Time to read data : ' .  ($time_2 - $time_1)*1000  
     . ' milliseconds<br>--------- Time to display data : '
     . ($time_4 - $time_3 - ($time_2 - $time_1) )*1000  . ' milliseconds -----------<b><br>';

---------------------------------
0
 
psk1Author Commented:
I'm sorry fibo, it's either too early in the morning for me or you're way over my head, or both.  

How would I accumulate data before sending it?

Where is that code supposed to go?  As I mentioned, I'm using Access as a front-end for pass-through queries to the MySQL server on the network.

Please note that this "Sending data" phase it often gets stuck in does not actually utilize any network bandwidth, but when it does send data over the network, max utilization is 1% of 1gbps.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now