MySQL crash recovery恢復慢分析
mysql中的資料庫比較多的時候,異常啟動時間很慢
l_data/xb_doublewrite 2023-02-10T03:10:13.386776Z 0 [Note] InnoDB: Opened 3 undo tablespaces 2023-02-10T03:10:13.386799Z 0 [Note] InnoDB: 3 undo tablespaces made active 2023-02-10T03:10:13.387079Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2023-02-10T03:10:13.420026Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 5566077531443 2023-02-10T03:10:13.420054Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 5566077531864 2023-02-10T03:10:13.420635Z 0 [Note] InnoDB: Database was not shutdown normally! 2023-02-10T03:10:13.420648Z 0 [Note] InnoDB: Starting crash recovery. 2023-02-10T03:10:13.474616Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /data/mysql_data/xb_doublewrite, size 7864320 bytes 2023-02-10T03:10:13.705255Z 0 [Note] InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 2023-02-10T03:10:14.318639Z 0 [Note] InnoDB: Apply batch completed 2023-02-10T03:10:14.318686Z 0 [Note] InnoDB: Last MySQL binlog file position 0 704652105, file name mysql-bin.004248 2023-02-10T03:18:00.657774Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2023-02-10T03:18:00.657826Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
可以看到,在Apply batch completed後,等待了8分鐘才開始清理臨時表空間,看下為什麼會怎麼慢
在輸出Last MySQL binlog file position後,會啟動purge sys
恢復需要checkpoint後的trx list
然後需要建立檢查系統表
接下來的表空間檢查和儲存表空間的最大id,這個在恢復的過程中比較耗時,開啟每個表空間的時候,會讀取每個header 頁,驗證字典
The 'validate' flag indicates that when a tablespace is opened, we also read the header page and validate the contents to the data dictionary. This is time consuming, especially for databases with lots of ibd files. So only do it after a crash and not forcing recovery. Open rw transactions at this point is not a good reason to validate. */ bool validate = recv_needed_recovery && srv_force_recovery == 0; dict_check_tablespaces_and_store_max_id(validate);
在校驗的時候,會掃描sys_tablespace和sys_table中的記錄,sys_tablespace中的掃描會過濾掉file-per-table tablespaces /* Open all general tablespaces found in SYS_TABLESPACES. */ ulint max1 = dict_check_sys_tablespaces(validate); /* Open all tablespaces referenced in SYS_TABLES. This will update SYS_TABLESPACES and SYS_DATAFILES if it finds any file-per-table tablespaces not already there. */ ulint max2 = dict_check_sys_tables(validate);
在fil_ibd_open中,檔案有三個位置,預設位置,資料字典位置,和遠端位置
Datafile df_default; /* default location */ Datafile df_dict; /* dictionary location */ RemoteDatafile df_remote; /* remote location */
在校驗時候,針對這三個可能的位置檔案,都進行了檔案開啟,進行了第一個頁的校驗。
/* Read and validate the first page of these three tablespace locations, if found. */ valid_tablespaces_found += (df_remote.validate_to_dd(id, flags, for_import) == DB_SUCCESS) ? 1 : 0; valid_tablespaces_found += (df_default.validate_to_dd(id, flags, for_import) == DB_SUCCESS) ? 1 : 0; valid_tablespaces_found += (df_dict.validate_to_dd(id, flags, for_import) == DB_SUCCESS) ? 1 : 0;
這裡可以進行最佳化吧,先判斷這3個位置是不是同一個,相同的話,就執行一次第一個頁的校驗即可。另外這個函式中是順序一個個表進行校驗的,可以按庫級別進行並行,加快恢復的速度。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25719946/viewspace-2934756/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL InnoDB Update和Crash Recovery流程MySql
- postgres crash recovery
- mysql效能測試庫的一次CRASH恢復MySql
- MySQL:Innodb crash recovery一些程式碼MySql
- Crash recovery和Media recovery的區別
- 【Oracle】-Difference between Instance recovery and Crash RecoveryOracle
- Overview of Instance and Crash RecoveryView
- 【MySQL】崩潰恢復問題解決:Forcing InnoDB RecoveryMySql
- MySQL 崩潰恢復過程分析MySql
- SQL Server進行Crash RecoverySQLServer
- 資料恢復:FonePaw Data Recovery for Mac資料恢復Mac
- Recovery恢復出廠設定方法
- 使用Recovery ID恢復密碼(ZT)密碼
- office文件恢復軟體(magic office recovery)
- oracle media recovery介質恢復實驗-Oracle
- oracle media recovery介質恢復小記Oracle
- Backup And Recovery User's Guide-恢復的取消(並行介質恢復)GUIIDE並行
- iPhone資料恢復工具:Cisdem iPhone Recovery for MaciPhone資料恢復Mac
- 硬碟資料恢復工具:Eassiy Data Recovery for mac硬碟資料恢復Mac
- 第4章 資料庫恢復 Database Recovery資料庫Database
- oracle scn與備份恢復backup recovery(一)Oracle
- oracle instance recovery例項恢復小記Oracle
- VMware Live Recovery 9.0 - 多雲實時恢復
- 【Mongo】mongorestore恢復資料特別慢GoREST
- MySQL斷電恢復的一點簡單分析MySql
- 【Mysql】完全恢復與不完全恢復MySql
- EaseUS Data Recovery Wizard Mac資料恢復軟體Mac資料恢復
- iPhone資料恢復工具:TunesKit iPhone Data Recovery for MaciPhone資料恢復Mac
- 使用Percona Data Recovery Tool for InnoDB恢復資料
- SQL Server的幾種恢復模式(recovery models)SQLServer模式
- Mysql備份恢復MySql
- MySQL恢復過程MySql
- mysql恢復drop表MySql
- Mysql密碼恢復MySql密碼
- mysql資料恢復MySql資料恢復
- mysql 備份恢復MySql
- 【MySQL】MySQL備份和恢復MySql
- MySQL 非常規恢復與物理備份恢復MySql