優化 Amazon RDS - MySQLTuner 建議 / 回饋和建議

我目前正在嘗試優化我繼承的數據庫以提高速度。
伺服器是一個 Amazon RDS db.m3.large實例：

• 2 個 VCPU
• 7.5 GB 記憶體
• 500 GB 硬碟

我目前的策略是執行以下操作：

1. 刪除所有開發數據庫並將它們推送到不同的伺服器
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)

（我正在寫這篇文章，因為我看到了你的觀點；請在採取行動之前閱讀到最後。我會逐步“開發”最佳答案——希望你能學到一些東西。）

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 可以接管。刪除該索引並在單個 ALTER 中添加以下內容：

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

注意：它會鎖定表一段時間。

您應該切換到具有線上 INDEX 操作的 InnoDB。

調諧器建議似乎比平時更糟：

• 關閉查詢記憶體
• 提高 tmp_table_size 和 max_heap_table_size 不一定有用
• 你似乎在使用 InnoDB 和 MyISAM 的混合，所以我推薦 innodb_buffer_pool_size = 2500M 和 key_buffer_size = 500M。（高 buffer_pool 設置使 MyISAM 的數據記憶體餓死。）
• 至於 tmp 表；讓我們看看您最慢的查詢，以及 SHOW CREATE TABLE。
• 忽略表碎片；不要打擾優化。
• table_open_cache 應該是幾百。如果 open_files_limit 只有 1K，那麼作業系統中的某些東西正在限制記憶體。

如果 aggr_id 可以設為 UNIQUE，那麼它可能應該是 PRIMARY KEY 並消除id。請注意，由於分區，您不能有 UNIQUEness 約束。

請注意，這裡有兩件事交織在一起：切換到 InnoDB 並使 aggr_id 成為 PK。如果你兩者都做，那麼做

PRIMARY KEY (aggr_id, craft_id, task_year)

並且沒有輔助鍵。理由：

• InnoDB 確實需要一個 PK。
• task_year（分區鍵）必須在每個唯一（包括 PK）鍵中
• 通常最好將分區鍵放在最後
• 您顯然出於其他原因需要craft_id？很可能它應該被刪除，特別是因為 PK 與 InnoDB 中的數據共存。（請顯示需要craft_id 的SELECT（s）；我們可以進一步討論。）

切換到 InnoDB 幾乎總是“好”的。請參閱我的部落格了解問題：http: //mysql.rjweb.org/doc.php/myisam2innodb 當您使用它時，請在此處閱讀有關 PARTITION 問題的資訊：http: //mysql.rjweb.org/doc.php/partitionmaint It可能會得出結論，分區對您沒有好處。

如果你去all-InnoDB，那麼innodb_buffer_pool_size = 5G and key_buffer_size = 20M。

引用自：https://dba.stackexchange.com/questions/90996