优化Amazon MySQLTuner建议/反馈和建议

Question

目前，我正试图优化我继承的数据库，以提高速度。

服务器是一个Amazon db.m3.大型实例：

• 2个VCPU
• 7.5GB内存
• 500 GB硬盘

我目前的策略是做以下工作：

1. 删除所有dev数据库并将它们推送到不同的服务器上。
2. 在下面的有问题的表中添加aggr_id列的唯一索引(如何在不中断生产的情况下正确地做到这一点？)
3. 将MyISAM表切换到InnoDB以防止锁定(这意味着什么？)

我写这篇文章是为了获得关于当前方法的反馈，以及任何其他的想法。无论如何，我不是DBA，只是一个web开发人员。欢迎任何和所有的反馈，谢谢提前。我一定会回复并批准答案的！我还将编辑这篇文章，并提供所获得的任何见解和资源。

。

有几个问题表都有相同的格式：

| xxxxx | CREATE TABLE `xxxxx` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aggr_id` int(11) NOT NULL,
  `craft_id` int(11) NOT NULL,
  `task_year` smallint(8) unsigned NOT NULL,
  `hc1` mediumint(8) unsigned DEFAULT NULL,
  ......
  `hc365` mediumint(8) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`,`task_year`),
  KEY `IDX_AGGRLABORDAY` (`task_year`,`aggr_id`,`craft_id`)
) ENGINE=MyISAM AUTO_INCREMENT=50480037 DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(task_year)
(PARTITION p1 VALUES LESS THAN (2012) ENGINE = MyISAM,
 PARTITION p2 VALUES LESS THAN (2013) ENGINE = MyISAM,
 PARTITION p3 VALUES LESS THAN (2014) ENGINE = MyISAM,
 PARTITION p4 VALUES LESS THAN (2015) ENGINE = MyISAM,
 PARTITION p5 VALUES LESS THAN (2016) ENGINE = MyISAM,
 PARTITION p6 VALUES LESS THAN (2017) ENGINE = MyISAM,
 PARTITION p7 VALUES LESS THAN (9999) ENGINE = MyISAM) */

以下是对有问题的查询的解释：

explain DELETE FROM xxxxx WHERE aggr_id = 3000010;
+----+-------------+---------------------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table                     | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+---------------------------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | xxxxx                     | ALL  | NULL          | NULL | NULL    | NULL | 46611048 | Using where |
+----+-------------+---------------------------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.05 sec)

以下是MySQLTuner的结果：

[--] Assuming 7680 MB of physical memory
[!!] Assuming 0 MB of swap space (use --forceswap to specify)
[OK] Currently running supported MySQL version 5.6.21-log

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 274G (Tables: 75)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in InnoDB tables: 7G (Tables: 227)
[!!] Total fragmented tables: 35

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 107d 15h 56m 50s (110M q [11.901 qps], 3M conn, TX: 14149B, RX: 108B)
[--] Reads / Writes: 58% / 42%
[--] Total buffers: 5.4G global + 1.5M per thread (604 max threads)
[OK] Maximum possible memory usage: 6.2G (83% of installed RAM)
[OK] Slow queries: 0% (17K/110M)
[OK] Highest usage of available connections: 29% (177/604)
[OK] Key buffer size / total MyISAM indexes: 16.0M/49.6G
[OK] Key buffer hit rate: 99.4% (25B cached / 155M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 2% (133K temp sorts / 6M sorts)
[!!] Temporary tables created on disk: 62% (7M on disk / 11M total)
[OK] Thread cache hit rate: 74% (945K created / 3M connections)
[!!] Table cache hit rate: 0% (1K open / 674K opened)
[OK] Open file limit used: 3% (2K/65K)
[!!] Table locks acquired immediately: 78%
[!!] InnoDB  buffer pool / data size: 5.3G/7.7G
[!!] InnoDB log waits: 1
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    query_cache_type (=1)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 2000)
    innodb_buffer_pool_size (>= 7G)
    innodb_log_buffer_size (>= 8M)

Answer 1

(我写这封信是因为我看到了你们的观点，请在采取行动前先读到最后。我一件一件地开发出最好的答案--希望你能学到一些东西。

DELETE FROM xxxxx WHERE aggr_id = 3000010;

必须扫描每个分区中的每一行。这意味着它必须执行大量的I/O操作，这将需要很长的时间，不管是什么调优。

如果这是一个常见的查询，那么添加

INDEX(aggr_id)

更好的是改变

KEY `IDX_AGGRLABORDAY` (`task_year`,`aggr_id`,`craft_id`)

通常，最好在索引结束时使用“分区键”(task_year)，而不是开始。在这种情况下，您可以简单地删除它。task_year的任何使用都将用于“剪枝”，然后密钥可以接管。删除该索引，并将以下内容添加到一个更改中：

KEY `IDX_AGGRLABORDAY` (`aggr_id`,`craft_id`)

注意:它会锁住桌子一段时间。

您应该切换到具有联机索引操作的InnoDB。

调谐器的建议似乎比平常更糟：

• 关闭查询缓存
• 引发tmp_table_size和max_heap_table_size不一定有用
• 您似乎使用的是InnoDB和MyISAM的混合物，所以我建议您使用innodb_buffer_pool_size =2500米和key_buffer_size =500米。(高buffer_pool设置需要MyISAM的数据缓存。)
• 至于tmp表，让我们看看您最慢的查询，以及slowest表。
• 忽略表碎片；不要打扰OPTIMIZEing。
• table_open_cache应该是几百。如果open_files_limit仅为1K，则操作系统中的某些内容限制了缓存。

如果aggr_id是唯一的，那么也许它应该是主键并消除id。请注意，由于分区的原因，您不能具有UNIQUEness约束。

请注意，这里有两件事交织在一起:切换到InnoDB和让aggr_id成为PK。如果你两者都做，那就做

PRIMARY KEY (aggr_id, craft_id, task_year)

也没有备用钥匙。理由：

• InnoDB确实需要一个PK。
• task_year (分区键)必须位于每个唯一(包括PK)键中
• 通常最好将分区键放在最后。
• 你显然需要craft_id还有别的原因吗？很可能应该删除它，特别是因为PK与InnoDB中的数据共存。(请出示选择(S)，希望craft_id；我们可以进一步讨论这一点。)

切换到InnoDB几乎总是“好”的。请参阅我的博客中的难点：http://mysql.rjweb.org/doc.php/myisam2innodb，在这里阅读有关分区问题的文章：http://mysql.rjweb.org/doc.php/partitionmaint --它可能会得出这样的结论:分区对您没有好处。

如果转到and，那么innodb_buffer_pool_size = 5G，key_buffer_size = 20M。