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)
Please provide SHOW CREATE TABLE; the explain is useless without it.
OR is a performance killer in many contexts.
( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ...
Turn that into
JOIN ( SELECT id FROM p WHERE
( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ... ) x ON x.id = foo.id
Also needed (on p):
INDEX(pricelist, iln, id)
(With the CREATEs, I could be more specific.)
The idea behind this "trick" is to move the costly work of the OR into a subquery that returns the necessary ids. Plus the INDEX makes it so that it can do all that work in the INDEX.
Best Answer
Log slow queries- If your system has a ton of queries, it gets tougher to find out which queries are slowing your system. MySQL provides a tool to log slow queries for further analysis http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
EXPLAIN Extended command shows details about your queries when your have no idea what is happening http://dev.mysql.com/doc/refman/5.0/en/explain-extended.html
Procedure_Analyse() can help you in finding optimal data types:
http://www.mysqlperformanceblog.com/2009/03/23/procedure-analyse/
http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html