Slow Remote MySql

[teamwhatever]

Hi there,

We have a local (client) machine that runs a php application on windows using apache, the database for this application is on a remote server, which the client machine connects to via the servers ip number and the server accepts the connection from the client via the designated ip address (skip-resolve-address is set in MySql server and no dns addresses are used, only ip).

However, the php application when dealing with the data from the MySql server is still pretty slow. We've tried query cache, but still slow.

When I say slow, I mean page response and reloads take about 5 or 6 seconds. Is this expected from a remote server and using apache as we do for this application? When using a local MySql server the speed is pretty much instantaneous, but we need the remote server!

Also, in testing the remote MySql server with a program such as SQLyog, response to requests are very quick and queries take no time.

How could we go about testing for where the bottleneck is? Could it be apache and php (and where do we start looking for the problem? Local MySql server is nice and fast)?

Any help massively appreciated.

Thanks

[cholmon]

Also, in testing the remote MySql server with a program such as SQLyog, response to requests are very quick and queries take no time.

A few questions:

1) When you run the tests with SQLyog, are you running all the same queries that your web app seems to be crawling through?

2) For any given request to your PHP script, is there only one database connection open? Is it a regular connection or persistent?

3) How many SQL queries are you sending to the database server for each PHP request?

[teamwhatever]

A few questions:

1) When you run the tests with SQLyog, are you running all the same queries that your web app seems to be crawling through?

2) For any given request to your PHP script, is there only one database connection open? Is it a regular connection or persistent?

3) How many SQL queries are you sending to the database server for each PHP request?

Thanks for the reply. In answer to the questions:

1) I tested with 4 out of 7 of the heaviest queries (those with most joins, most columns, where clauses and sort ordering). The heavier queries didn't seem to be *that* much slower than a "select *" query from the smallest table (2 columns, 8 rows). What would be the best way to view these queries and their performance "live" in php whilst it executes? That I guess would give me the best answer.

2) There is only one database connection open, and it is not persistent, just a regular connection.

3) On average, the heaviest query loaded pages have about 7 queries, but even those pages with only 2 queries take the same sort of time to load, which is why I was thinking it may have been some kind of issue with the local apache/php set up, or with the remote MySql?

Thanks again

[teamwhatever]

Update:

I've managed to get the following results, and wondering whether I could push for even a little more?

8 queries:

local mysql server: - php parse time - (0.082s)
remote mysql server - php parse time - (0.419s)

There's obviously a time penalty due to MySql being on a remote server. skip-name-resolve is invoked but not query_cache which I understand might help improve matters a little bit more?

In anycase, could anyone with experience of remote mysql queries similar to this scenario comment whether these are decent speeds or can I trim them (is, say, 0.100ms possible on remote MySql connection?).

Thanks in advance

[longneck]

have you tried running a tracert or ping between the two locations to determine your network latency? your query time is always going to be at least 4 times greater than your connection latency. and there's nothing you can do about that without violating the laws of physics.

[teamwhatever]

have you tried running a tracert or ping between the two locations to determine your network latency? your query time is always going to be at least 4 times greater than your connection latency. and there's nothing you can do about that without violating the laws of physics.

Brilliant! Thank you, just the kind of information I'm looking for.

I did indeed run a tracert, and came back as a total of between 50-70 ms with 7 hops. So I guess the speed is probably as fast as it'll get without a faster internet connection (it's an interleave connection so obviously some slight latency just because of that).