"You can't tune your way out of performance problems". That being said, here is some tuning advice, followed by other ways to solve the issues you are facing.
"Thread cache hit rate: 49% (134K created / 264K connections)" and thread_cache_size=1
-- if *nix, set to 10. Otherwise the process creation may be noticeable.
"Highest connection usage: 100% (91/90)" -- Something outside of MySQL is causing this. Do you have connection pooling? Is the web server tuned to have too many connections? What web server?
"Query cache prunes per day: 737807" -- that is several per second! This is costly, especially with query_cache_size
being 128M. Recommend lowering to 50M. Better yet, if possible, change to DEMAIND
and use SQL_CACHE
and SQL_NO_CACHE
as appropriate for your SELECTs
. Usually an app has some cachable queries and a lot of non-cachable queries. The latter clutter the QC without providing any benefit.
A QC "prune" occurs whenever any write occurs -- and it deletes all QC entries for the table involved. Hence, the bigger the QC, the longer it takes. Hence, my recommendation to shrink the size.
The rest of the settings seem reasonable.
Don't worry about the rest of the mysqlTuner recommendations.
More tips on converting to InnoDB are here .
Do not use MyISAM; it is going away. And, in virtually all situations, InnoDB can run faster.
Assuming the 3GB includes both data and indexes, as seen via SHOW TABLE STATUS
, the 7G for buffer pool says that you have plenty of growing room before I/O may become an issue.
"Joomla app that makes Mysql down all the time" -- MySQL (even using MyISAM) may stall, hang, etc, but I question whether it every "crashed".
"too much CPU usage and too much queries without index, very large tables and non-optimized queries" -- Find one of the worst queries, provide EXPLAIN SELECT ...
, and provide SHOW CREATE TABLE
for each table involved. The solution may be as simple as adding a 'composite' index. And, since indexes work differently in MyISAM and InnoDB, this needs to be done (at least) after converting to InnoDB. (Start a new Question so we can focus on that query.)
Best Answer
You should index as per usage. If you drop all indexes for the benefit of a small % of your workload you will hit pain elsewhere. Run pt-duplicate-key-checker 1st and remove the redundant keys. Then review your queries with pt-query-digest and weed out the poorly performing queries by reindexing them or rewriting them. You could paste the output of EXPLAIN with your table structure and status for your problematic query for further help tuning on that.