PHP+MYSQL 大量 INSERT 到資料庫的效能很差 ?

最近公司一個專案,就是頗析中選會九合一的資料,當然資料後續要做一些計算,於是還是選了關聯式的 Database => MariaDB。但若要每分鐘達成 100 萬筆以上資料的 INSERT 可能嗎 ?

效能瓶頸

根據我第一次用傳統的 CODING 方式,每頗析一筆封包然後下一次 PDO::excute() 指令去新增資料,在我的 I7 CPU 大概跑 30 萬筆要花上 6 分鐘以上。

但如果把封包集中起來用 batch INSERT 的方式,例如 INSERT INTO table( …. ) VALUES(…),(….),…. 這樣的方式,則可以縮短非常多的時間,跑 30 萬筆大概 40 秒左右,這樣距離 1 分鐘 100 萬筆的目標似乎不太可能。

問題在那 ?

首先我的環境是 Windows 7 單機上跑 PHP+MySQL,I7 CPU , 8G RAM。

我發現 Windows 或 Linux 都一樣,以 PHP 處理資訊塞到 MySQL 的 CPU LOADING 非常的少,只吃大約 10%,這代表了一件事情,PHP 下完 PDO::execute 指令後有一大半的時間在等 MySQL 回應,而MySQL本身也不吃資源,看來問題在於 socket 傳輸時等待的 blocking 時間太多了。這是同步式的執行方式缺陷。因此任我如何 Tunnning MySQL 的設定都無法大幅改善大量 INSERT 的速度。

怎麼改善 ?

有許多的方法可以讓 PHP 和 MySQL 吃光 CPU,例如使用 pthread , pcntl_fork 等多執行序或多行程的方式可以讓多個 PHP 任務同時跑,而每個任務都必須要開一條MySQL連線,這樣才能同時 INSERT 。

由於 pthread 或 fork 的方式有其環境限制,我這個人挺龜毛的,我採用 proc_open() 的方式可以同時執行 4 個 PHP 於 Windows 或 Linux,同時採用 pipe 的方式接收子 PHP 回傳的 stdout 及 stderr 訊息到我的主程式中。

這種方式,大概 10 秒就跑完 30 萬筆了,CPU 大概吃個 15% 左右,如果我想更快的話還可以多開些 PHP 行程哩,由於還在整理程式中,可能完整一點後會把 proc_open 執行多行程的方式 release 出來 ^^

發表迴響