Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Which queries are slowing down my database?

    We run a MySQL database hosted by a 3rd-party provider. We don't have direct access to the database server machine - my only access is through a copy of MySQL Administrator running on my workstation here in the office. As it's a remote connection, it can't give me the server logs.

    Looking at the Key Efficiency, I'm a bit concerned that the key buffer usage is constantly hovering around 70-80%, although the Connection usage rarely exceeds 15%.

    We run quite a few websites off this one database instance, and I'm at a bit of a loss as to finding out where the inefficiencies lie.

    Is there a way I can find out which queries / tables are getting hit the most, and therefore start optimising these first?

  2. #2
    Join Date
    Jan 2002
    Posts
    189
    Hmm. 21 views, no replies. Clearly, I'm either asking the wrong question or asking it in the wrong way. Can anyone suggest a better approach?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you overlooked another reason why there was no answer posted -- none of the people who looked at the thread knows the answer

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2004
    Location
    Broomfield, Colorado
    Posts
    16
    With mysql administrator, then you should be able look at the server connections section to view the queries that are running. see the info section. The output isn't perfect, so you are probably better off running

    show full processlist \G

    ...and looking at the full queries. Look for ones with long running times.


    If you want to get an idea of hot tables, you might use a query tool and issue:

    show open tables where in_use <> 0 ;

    since you can connect with mysql admin you should be able to connect with any of the common query tools.

Posting Permissions

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