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?

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?

you overlooked another reason why there was no answer posted -- none of the people who looked at the thread knows the answer

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.