極其緩慢的 mySql 恢復

當我問這個問題時，請耐心等待，這看起來與 DBA@SE 上已有的問題相似。

在目前配置中，我們有一個伺服器（一個具有 4 GB RAM 的 Linux 四核機器）——數據庫和應用程序伺服器都託管在該伺服器上。有兩個 MySql 數據庫 - Prod和Staging在同一台伺服器上。一些表在 MyIasm 引擎上，而一些在 InnoDB 上。

root     10119     1  0  1233  1012   3 Mar03 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe ...
mysql    10233 10119  9 238047 108896 0 Mar03 ?        12:11:11 /usr/local/mysql/libexec/mysqld ...

每天都會在Staging db中提取和恢復Prod db 的轉儲。總大小接近 1 GB，一些表的行數超過百萬。

• 通過 mySqlDump 進行轉儲速度很快，不到一分鐘（完整轉儲 - 不僅僅是來自二進制日誌的增量）。諸如關閉 auto_commit、foreign_key_checks 等優化已添加到轉儲中。

• 幾天后，恢復部分（到Staging DB）變得非常糟糕，因為它需要大約 1 小時並且使應用程序癱瘓。（在我的開發盒上進行相同的恢復需要不到 4 分鐘 - 配置不同，沒有人訪問它）

  • key_buffer_size 為 512Mb$$ ~12.5 % of the total RAM $$. table_cache=256 是次優的（但在那段時間沒有太多的請求）。讀取緩衝區大小=1M。sort_buffer_size=64M
  • 在恢復腳本中，在插入數據之前，每個表都被鎖定，數據被插入，然後表被解鎖。
  • 啟用了慢查詢日誌（並且大多數來自轉儲的查詢都在那裡找到了位置） - 高 I/O 會影響性能嗎？

我試圖找出根本原因，以便可以減輕它。由於我對伺服器的訪問權限有限，我正在嘗試收集可能的問題，以便將它們帶到託管服務提供商處。你能給我一些我應該看的指針嗎？

這段時間內的 CPU 使用率和 I/O 峰值。

謝謝你。

$$ Edit $$

• 增加 innodb_buffer_pool_size 後，還原時間在一定程度上減少了。

This is the sample dump, that gets generated:
SET FOREIGN_KEY_CHECKS=0;
SET unique_checks=0;
SET AUTOCOMMIT=0;

// The scripts go here:
DROP TABLE IF EXISTS `access_tokens`;
CREATE TABLE `access_tokens` ( ... );
LOCK TABLES `access_tokens` WRITE;
INSERT INTO `access_tokens` VALUES (...) // All of the data is in a single insert
UNLOCK TABLES;

...
// Script for other tables

SET FOREIGN_KEY_CHECKS=1;
SET unique_checks=1;
COMMIT;
SET AUTOCOMMIT=1;

您還沒有包括您的伺服器配置，所以我正在寫我能想到的所有內容。

你已經有 innodb_buffer_pool_size 所以跳過它。

在登台伺服器上使用以下

innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M

在 mysqldump 上使用以下選項

--disable-keys
--extended-insert
--add-locks

您應該以這種方式使用更改日誌大小。

這可能完全不合時宜，但您是否確認磁碟系統工作正常？聽起來它剛剛開始發生，我假設您的數據庫在短時間內沒有增長那麼多。

RAID 磁碟故障或標準驅動器故障可能會導致各種寫入錯誤/延遲。

不是真正的 DBA 答案，但值得一試。

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