Efficency problems using remote connection to MySQL

[Adam McCormack]

Good morning!

I am on a project where we are migrating a site running on perl and mysql on IIS.

The current configuration is with everything on one server, both IIS and MySQL.
The new configuration has IIS on one server, and MySQL on another, both machines are approximately equivalent to the old (aka current) box power wise..

The problem I am having is that a given script (granting it is based on an ugly query with 7 joins) runs in a few seconds on the old box, but takes easily 10 times as long on the new system.

the biggest difference is that the new box is running MySQL 5.0 and the old box is on MySQL 4.1.7, however I dont believe that is the cause of the speed issues, as we slapped the site on yet a third box, which is running MYSQL 5.0, and it is returning the data very snappily (a few seconds).

A "first" pass at trying to make it work better was to increase the buffer sizes. I can not report what they set them to, but as I understand they have "maxed" out the buffers to try to speed it up (doing so, cut time to run backups of the DB by about half).

I am not the admin for any of these machines, (ahh, Sarbanes Oxley, wouldn't it be nice if SOX were to die a horrible fiery death?) but I can ask the admins anything, if anyone might have a suggestion as to what we could chase to determine what is wrong. I have not had to deal with something like this, previously, so any advice on what we might try would be a huge help!

I am sure I am leaving out all kinds of useful information, but this is the complete rundown of what I currently know (sorry, I am in a bit of a third person situation here, but I have decided to peruse it myself rather than continuing to wait on others who know more than I (besides, thats how I end up knowing more than them!))

Thanks!

Adam McCormack

[Atli]

I've edited the thread's title to better describe it's contents.
Please read the Posting Guidlines before posting

Moderator


I am more of a software guy so this is not really my area, but I would think the connection between the SQL server and the IIS server is the problem.
I mean, if the SQL server is running fine, and the IIS server is also running fine, and you know there are supposed to work fine together, the problem must be somwhere on the path between them.

How are they linked together?

[Adam McCormack]

Sorry about the title, I was interrupted probably a half dozen times while typing this up, and now realize that beyond the poor title, I left out some other test results.

I was in agreement on the issue being the network connection between the two machines, but there was another test we ran that eliminates that possibility.

We installed IIS on the machine that is hosting the DB so we could try the scripts without the network communication getting involved and it was still as slow as it was on the two machine tests.

I really am sorry I didn't get that in the first post.