Mysql

MySQL需要很長時間才能啟動

  • July 27, 2021

我試圖理解為什麼 mysql 需要 2000 萬才能啟動。

我正在執行 5.7.17-13-log,即使啟動了每個表的文件,我也有一個大的 ibdata1 文件(20GB)。這是它的內容:

# innochecksum -S /data/mysql/ibdata1
File::/data/mysql/ibdata1
================PAGE TYPE SUMMARY==============
#PAGE_COUNT     PAGE_TYPE
===============================================
 781526        Index page
1031230        Undo log page
  11836        Inode page
  25060        Insert buffer free list page
  22645        Freshly allocated page
    114        Insert buffer bitmap
    107        System page
      1        Transaction system page
      2        File Space Header
    116        Extent descriptor page
      3        BLOB page
      0        Compressed BLOB page
      0        Other type of page
===============================================

這是我在錯誤日誌中看到的內容:

2017-06-05T19:08:31.307808Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-06-05T19:08:31.719378Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /data/mysql/xb_doublewrite, size 31457280 bytes
2017-06-05T19:29:09.747406Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-06-05T19:29:09.747507Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-06-05T19:29:09.763021Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-06-05T19:29:09.869445Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-06-05T19:29:09.869470Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-06-05T19:29:09.869574Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 1238576ms. The settings might not be optimal. (flushed=0, during the time.)

我看到有人提到頁面清潔器後來花了 2000 萬,在啟動時聽起來很奇怪!

這是 mysqld 上的 sysdig 輸出,表明它一直在讀取 ibdata1:

11706 13:01:38.019123953 0 mysqld (94026) > pread fd=4(<f>/data/mysql/ibdata1) size=16384 pos=9304113152
11707 13:01:38.019163204 0 mysqld (94026) > switch next=0 pgft_maj=1 pgft_min=4116243 vm_size=45320516 vm_rss=14609036 vm_swap=0
11709 13:01:38.019292874 0 mysqld (94026) < pread res=16384 data=.......F...E..._...n....E..............6?a..=..h................................
11712 13:01:38.019337538 0 mysqld (94026) > pread fd=4(<f>/data/mysql/ibdata1) size=16384 pos=9232695296
11713 13:01:38.019355435 0 mysqld (94026) > switch next=0 pgft_maj=1 pgft_min=4116247 vm_size=45320516 vm_rss=14609036 vm_swap=0
11715 13:01:38.019399597 0 mysqld (94026) < pread res=16384 data=.......?...9...G...n....E..............5?1.j....................................
11718 13:01:38.040605854 0 mysqld (94026) > pread fd=4(<f>/data/mysql/ibdata1) size=16384 pos=9304522752
11719 13:01:38.040644306 0 mysqld (94026) > switch next=0 pgft_maj=1 pgft_min=4116438 vm_size=45320516 vm_rss=14609560 vm_swap=0
11721 13:01:38.040774631 0 mysqld (94026) < pread res=16384 data=69....._...F...n...n....E..............*?A...u. .....w..........................
11872 13:01:38.062582012 0 mysqld (94026) > pread fd=4(<f>/data/mysql/ibdata1) size=16384 pos=9232826368
11873 13:01:38.062633600 0 mysqld (94026) > switch next=0 pgft_maj=1 pgft_min=4116541 vm_size=45320516 vm_rss=14610084 vm_swap=0
11875 13:01:38.062748273 0 mysqld (94026) < pread res=16384 data=.te=...G...?...O...n.(.gE..............X?8.i.R.......f.c........................

更新 1

innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 32G
innodb_log_buffer_size         = 128M
innodb_buffer_pool_instances    = 8
innodb_max_dirty_pages_pct  = 5
innodb_io_capacity      = 2000
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

有任何想法嗎?

謝謝,

原來,mysql 會掃描所有 undo 以查找未送出的事務。

由於我在 ibdata 中有一個非常大的撤消空間,我轉而使用撤消表空間。設置撤消截斷選項使我的撤消表空間非常小。
現在我的數據庫在 2-3 百萬頂部打開!謝謝,

引用自:https://dba.stackexchange.com/questions/175549