SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow Remote MySql

    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

  2. #2
    SitePoint Zealot cholmon's Avatar
    Join Date
    Mar 2004
    Location
    SC
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by teamwhatever View Post
    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?
    Drew C King: PHP Developer
    <?= $short_tags++ ?>

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cholmon View Post
    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

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    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).


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •