A good starting point is the MySQL Slow Query Log instead of the general query log. You can set the
You'll want to log queries that aren't using indexes
Update
In your question, you state that the system is 'nice and responsive' over local network, but that you haven't done any performance tuning. The slow query log I pointed out will help you identify queries that are taking a long time to run (over 1 second, if configured that way). IMO, this is a great starting point.
The longer a query takes, it is much worse when the response has to be transmitted over a WAN.
One tool I've recently discovered is mk-tcp-model that analyzes output from tcpdump to help measure how long a request takes to respond. You can see how many request/responses are coming in and how long each takes. The best tuning over a WAN is to reduce the amount of requests you need to make.
Before you try to change the configuration file, the first thing to check is index use. This part:
JOINS
Current join_buffer_size = 4.00 M
You have had 1117666 queries where a join could not use an index properly
You have had 207168 joins without keys that check for key usage after each row
suggests that many queries cannot use indexes, probably because there are no indexes to use.
Ask for the slow query log, check which queries are slow and check/test if indexes can be added to improve performance.
A general advice is first, to have indexes on all columns that are used in Joins and second, indexes on columns that are used in WHERE
, GROUP BY
and ORDER BY
clauses. But for this, you may need compound indexes and of course you can't create an index for every column combination. That would take too much space and would make Insert, Delete and Update statements much slower. So, you'll have to check what are the most common queries and optimize those first.
There are some tools/services that can help you identify better and faster where the bottlenecks are. One such tool is Percona Toolkit (also known as mk-query-digest, from Maatkit).
A slow query that needs 10 seconds to complete is not good. But it doesn't really affect performance if you are executing it once per hour. A query that needs 60ms can be really bad if it is a simple UPDATE
and is executed several times per second. These tools can help you identify those because you can analyze the logs and find total running time, number of times a query has been run and various other figures.
For your example query:
SELECT menu_links.link_path AS link_path
, menu_links.mlid AS mlid
, menu_links.router_path AS router_path
, menu_links.updated AS updated
FROM
dr_menu_links menu_links
WHERE ( (updated = '1')
OR ( (router_path NOT IN ( 'rss.xml' , 'node' , 'checkout'
, 'import' , 'image_captcha', 'my-favourites'
, 'my-orders', 'print' , 'search'
) )
AND (external = '0')
AND (customized = '1')
)
)
possible good indexes are an index on (updated)
and one on (external, customized, router_path)
Best Answer
Finding Duplicate Indexes
Back in January 2012, @gbn answer a question about duplicate indexes where he presented 2 views that came from Ronald Bradford's blog. I combined the two views into a single query to present duplicate indexes as follows:
Obviously, the indexes with the least column per grouping need to be eliminated
Find unused indexes on the basis of selectivity.
I have not done much with unused indexes in my developer days. I try to make only necesssary indexes that match the following clauses:
In the event you have to cleanup a database by hunting down unused indexes, please read these:
Monitor the Server Parameters (What should be important parameters)
This is just a sample of the kind of global status values to monitor. Please read MySQL Documentation on the Server Status Variables.
Execute MySQL Server performance tuning script
Most straightforward script is mysqltuner.pl Just get it and run it
Slow logs
Slow logs can be quite helpful in a low-traffic environment. Unfortunately, I have seen too many occurrences of the following
Given this scenario, I have queries that work standalone with blazing speed grind to a halt when an inundiation of queries needing common tables.
IMHO the slow query log actually does you no good becase it records completed queries that are regarded as slow. What you really want to do is catch long-running queries in the act of being long-running. Therefore, I would recommend using pt-query-digest to pool the processlist (or tmpdump) for queries running amok. I wrote a post back in December 2011 on how to script a crontab job that polls the processlist every 20 minutes using mk-query-digest (pt-query-digest can be inserted in it place).