If you know that the MyISAM tables are not being actively queried, not only can you ignore recommendations for key_buffer_size, you could actually lower it.
What can you do with the extra RAM you just reclaimed? One or more of the following options:
After restarting mysqld, rerun mysqltuner and evaluate the resulting recommendations. Taking key_buffer_size out of the equation is enough in your instance.
(I'm writing this as I see your points; please read to the end before taking action. I 'develop' the best answer piece by piece -- hope you learn some things.)
DELETE FROM xxxxx WHERE aggr_id = 3000010;
must scan every row in every partition. That means it must do a lot of I/O, which will take a lot of time, regardless of the tuning.
If this is a common query, then add
INDEX(aggr_id)
Even better is change
KEY `IDX_AGGRLABORDAY` (`task_year`,`aggr_id`,`craft_id`)
Normally it is best to have the "partition key" (task_year) at the end of indexes, not the start. In this case, you may as well simply remove it. Any use of task_year will be for "pruning", then the KEY can take over. DROP that INDEX and ADD the following in a single ALTER:
KEY `IDX_AGGRLABORDAY` (`aggr_id`,`craft_id`)
Caution: It will lock the table for some time.
You ought to switch to InnoDB, which has online INDEX operations.
The tuner suggestions seem to be worse than usual:
- Leave the query cache off
- raising the tmp_table_size and max_heap_table_size is not necessarily useful
- You seem to be using a mixture of InnoDB and MyISAM, so I recommend innodb_buffer_pool_size = 2500M and key_buffer_size = 500M. (The high buffer_pool setting was starving the data cache for MyISAM.)
- As for the tmp tables; let's see your slowest query, plus the SHOW CREATE TABLE.
- Ignore table fragmentation; don't bother OPTIMIZEing.
- table_open_cache should be a few hundred. If open_files_limit is only 1K, then something in the OS is constraining the cache.
If aggr_id can be made UNIQUE, then perhaps it should be the PRIMARY KEY and eliminate id
. Note that you cannot have a UNIQUEness constraint because of partitioning.
Note that there are two things intertwined here: Switching to InnoDB and making aggr_id the PK. If you do both, then do
PRIMARY KEY (aggr_id, craft_id, task_year)
and have no secondary keys. The rationale:
- InnoDB really needs a PK.
- task_year (the partition key) must be in every unique (that includes PK) key
- It is usually best to put the partition key last
- You apparently need craft_id for some other reason? Quite possibly it should be removed, especially since the PK coexists with the data in InnoDB. (Please show the SELECT(s) that wants craft_id; we can discuss this further.)
Switching to InnoDB is almost always "good". See my blog for the gotchas:
http://mysql.rjweb.org/doc.php/myisam2innodb
While you are at it, read about PARTITION issues here:
http://mysql.rjweb.org/doc.php/partitionmaint
It may conclude that partitioning is doing you no good.
If you go to all-InnoDB, then innodb_buffer_pool_size = 5G and key_buffer_size = 20M.
Best Answer
Most monitoring services have small enough impact that they can (and should) be run on production servers.
mysqltuner
-- once in a while. (Tuning is not something that needs repeating very often.)SHOW GLOBAL STATUS
-- periodically. (Some services use this to build graphs.)long_query_time
. (This is my favorite tool for getting useful clues about performance problems.)performance_schema
(early versions had a noticeable impact)Here's what I request: http://mysql.rjweb.org/doc.php/mysql_analysis It includes
SHOW VARIABLES
andSHOW GLOBAL STATUS
, then run that against about 200 checks.