iT邦幫忙

2

mysql 大量匯出匯入

我有一個 mysql 的 sql 檔,大約 1.9G,當我做匯入該檔時,整個資料庫主機幾乎 hold 住了,其他人的操作會卡住,有沒有什麼辦法或參數設定可以解決大量匯入時的卡機問題?或者,只能利用三更半夜來做?

同樣的情況,匯出大資料庫時(mysqldump),也一樣有卡機的情形。

請教諸位先進,對於大資料庫的匯出匯入,有沒有什麼建議?

2 個回答

11
raytracy
iT邦大神 1 級 ‧ 2022-01-13 21:12:08
最佳解答

匯入和匯出卡住的原因不同..

匯入卡住, 是因為他連續執行 sql 檔案裡面的 insert 或 update 指令, 每一個指令都需要消耗好幾筆(或者幾十筆) I/O Write 指令, 即便資料量很少, 但是 I/O Write 次數很多, 代表你的 Disk 需要承受非常大量的 IOPS (?? Disk Write/sec) 而不是 Throughput (?? MB/Sec).

一般消費級 SATA 的單顆 HDD 的 IOPS 大約只有 150~200/Sec 之間, 企業級 SAS 大約在 200~250 左右, 多顆組成 RAID 可以向上增加 IOPS; 但是 MySQL import 產生的 IOPS, 可能瞬間高達 6,000~15,000 Write I/O, 這時候如果你沒有做 log 寫入分離, 同時磁碟子系統無法上衝到這個數字的話, 結果就是大家停下來等, 等你的 Disk 把剩餘的 Write 全部寫入完畢之後, 才能繼續作業....

匯出的狀況可能不太相同, 因為匯出的 select 指令, 他的 IOPS 可以被安排成循序大量執行, 只要你 Disk 的 Throughput 夠高, 不至於卡住 (當然, 不夠的話還是會卡). 但另外一個卡住你的真正原因是: mysqldump 匯出的時候, 他會先發動 Table lock, 也就是在匯出作業完成之前, 這個 Table 不能有人去操作他. 如此一來, 其他人想要存取的時候, 如果正好遇上你在 export 他要寫入的 Table, 就會卡住直到 timeout 或者你的匯出結束.

想問一下,log 寫入分離要怎麼寫呢?

ckp6250 iT邦好手 1 級 ‧ 2022-01-14 20:31:26 檢舉

感恩雷大的專業解析,茅塞頓開。
我的資料庫主機是 gcp 上的 vm , 硬碟是選 ssd , 4G RAM , 匯入匯出時,會卡住大家,看來只能往讀寫分離方向去做了。

5
一級屠豬士
iT邦大師 1 級 ‧ 2022-01-13 22:17:05
看更多先前的回應...收起先前的回應...
ckp6250 iT邦好手 1 級 ‧ 2022-01-14 20:35:05 檢舉

感恩殺豬大的指點名燈,我再一一測試看看,測完再報告。

以前的檔案都算小,不曾遇過問題,這回檔太大了,光 sql 檔就近 2G 了,再過幾年,又不知要膨脹到什麼地步,這不得不好好學習解決之道。

ckp6250 iT邦好手 1 級 ‧ 2022-01-14 22:08:22 檢舉

咦? mariadb 沒有 MySQL Pump ?

你可以用mysql 的 mysqlpump 去備份 Mariadb 看看.

--single-transaction 這個參數,你可以參考看看.mysqldump
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_single-transaction

ckp6250 iT邦好手 1 級 ‧ 2022-01-15 05:40:35 檢舉

感恩殺豬大,【--single-transaction】這個參數,確實有助於 mysqldump 不會卡住別人,很有助益。

但,匯入部份呢?匯入大資料庫是卡機問題,有什麼參數可用嗎?

你可以用mysql 的 mysqlpump 去備份 Mariadb 看看.
這個行不通,我有測試,我若要單獨安裝 mysqlpump 它會移除我的 mariadb-server , 不能並存,左右為難。

我猜你是用Ubuntu,建議可以先在別台安裝MySQL 把這些工具都安裝好.然後複製留起來,放到USB Disk或雲端.然後就可以另外copy到你要工作的機器上的不同目錄.使用時,搭配絕對路徑方式呼叫.
這裡有另外一個
https://github.com/mydumper/mydumper
這裡有一篇比較,percona 是在MySQL方面提供第三方工具與服務等的公司.
https://www.percona.com/blog/dump-performance-comparison-mysqldump-vs-mysql-shell-utilities-vs-mydumper/

裡面你可以看到還有一個 MySQL Shell, 這是Oracle在比較新的版本中提供的.這些因為是後續開發的,所以速度都比mysqldump 快很多.

你提到 匯入大資料庫是卡機問題,有什麼參數可用嗎?
這裡有個工具程式,MariaDB 也有的.
https://mariadb.com/kb/en/mysqlimport/
裡面參數除了 lock-tables default FALSE,
還有 low-priority default FALSE, 但這只是優先權調整而已.
這是匯入,不是 restore.匯入的守備範圍較小.

因為MySQL常用的備份格式是產生SQL Command的方式,會先產生DDL,
定義物件,再使用 insert 方式匯入資料.
另外的方式,較為繁瑣.
1.先使用 mysqldump備份DDL, 使用 --no-data 參數.
2.再使用 mysqldump 的 tab 參數,搭配其他選項(--fields-terminated-by 等)
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_tab
這個方式有一定的限制,不能遠端,要有file 權限.
3.匯入時使用上面提到的mysqlimport.
這樣的方式,只是一種輔助的方式.

以上先提出一些工具的作法,提供老哥參考.

ckp6250 iT邦好手 1 級 ‧ 2022-01-15 20:51:41 檢舉

感恩殺豬大提攜指教,我會一一測試,再提出報告,因為資料量實在不小,別的版友將來也許會用到。

我要發表回答

立即登入回答