• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1090
  • 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
 
Bernard S.CTOCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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