Icc編譯MySQL性能調研_MySQL

1 測試環境

1.1使用icc編譯器編譯MySQL5.0

在configure前需要通過CC，CXX等變量改變編譯器為icc。具體命令如下：

CC=icc CXX=icpc CFLAGS="-O3 -unroll2 -no-gcc –restrict -fPIC" CXXFLAGS="-O3 -unroll2 -no-gcc –restrict -fPIC" ./configure ……

上述需要注意的是-fPIC參數，如果不添加這個參數，編譯過程中會出現“could not read symbols：bad value”錯誤。

1.2使用icc編譯器編譯MySQL5.1

在編譯mysql5.1時，除了像編譯5.0那樣修改CC，CXX參數外，還需要修改mysql5.1（5.1.40）mysys/stacktrace.c中的代碼，以避免編譯過程中出現重定義錯誤。該錯誤是由于icc編譯器和gcc編譯支持的代碼特性不同引起的。

char __attribute__ ((weak)) *

my_demangle(const char *mangled_name __attribute__((unused)),

int *status __attribute__((unused)))

{

return NULL;

}

改為：

#if defined(__INTEL_COMPILER)

#pragma weak my_demangle=my_demangle_null

char *my_demangle_null(const char *mangled_name, int *status)

{

return NULL;

}

#else

char __attribute__ ((weak)) *my_demangle(const char *mangled_name, int *status)

{

return NULL;

}

#endif /* !__INTEL_COMPILER */

1.3測試機器及環境

測試機有4顆CPU，16G內存。Icc編譯的mysql和gcc編譯的mysql同時安裝在這臺機器上，以避免由于機器差異而引起的性能差異。兩個mysql的配置文件是相同的，以避免cache等參數的不同，引起的性能差異。

2 正確性測試方法及結果

2.1 正確性測試方法

本節是驗證icc編譯的mysql在程序邏輯和行為上的正確性。測試方法是選取某數據庫的數據和兩條典型SQL，分別在icc編譯的mysql和gcc編譯的mysql上執行。對比它們的輸出來驗證：icc編譯的mysql執行結果是否和gcc編譯的mysql的執行結果一致。測試包含對InnoDB和MyISAM兩種引擎的分別測試。測試使用的兩個SQL：

SQL1：select * from tb_customer where urldomain like "%.net" and status=3；

SQL2：update tb_customer set cust_prov=20 where pose_id=178;

針對SQL2，在執行完SQL2后，使用“select cust_prov from tb_customer where pose_id=178;”觀察輸出來驗證SQL2執行的正確性。

2.2 正確性測試結果

下表中的數據是相關測試結果：

表1

通過上表可以看出，icc編譯的mysql在上述兩個SQL上執行結果完全一致。通過本節測試可以證明icc編譯的mysql在程序邏輯和行為上的正確性。

3 性能測試方法

本節整個測試分成兩部分：1使用sql-bench對icc編譯的mysql和gcc編譯的mysql進行對比測試；2使用mysqlslap、某數據庫數據對兩個編譯版本的mysql進行對比測試。

3.1使用sql-bench的測試方法

Sql-bench是一些通用的測試benchmark的集合，這些benchmark覆蓋了多種SQL操作。它們的特點是測試表中的數據量不是太大，測試用的SQL操作豐富。測試方法：運行兩個sql-bench，以相同的bench-mark測試icc編譯的mysql和gcc編譯的mysql。測試中包含針對InnoDB和MyISAM兩種引擎的分別測試。

在測試過程中統計top中cpu信息和相關mysql進程內存占用信息，然后取均值。這些值均是以占機器總cpu時間，總物理內存的百分比的形式給出。以獲得icc編譯的mysql和gcc編譯的mysql資源占用的比較。同時統計相關SQL集合的執行時間，以獲得兩個編譯版本在執行時間(Qphotoshop/ target=_blank class=infotextkey>PS)上的對比。

3.2使用mysqlslap的測試方法

測試工具是mysqlslap，測試數據庫是某數據庫。測試中包含針對InnoDB和MyISAM兩種引擎的分別測試。

對于InnoDB引擎：測試腳本是從上述數據庫一天的全日制中抽取了10000條update和select類型的SQL。這些SQL組成了全測試腳本。在這個測試腳本的基礎上，從中挑選了3個有代表性的SQL作為3個獨立的測試腳本。

對于MyISAM引擎，從上面的測試腳本中挑選了4個有代表性的SQL，將它們對應的表轉化成了MyISAM引擎進行測試。

測試方法：使用mysqlslap，同樣的測試腳本，對icc編譯的mysql和gcc編譯的mysql進行測試。在全腳本測試過程中統計top中cpu信息和相關mysql進程內存占用信息，然后取均值。同時統計相關SQL集合的執行時間。對于后續的單獨SQL測試，由于這些SQL資源消耗比較小，執行時間都比較短，沒有采集執行它們時的資源消耗。

4 性能測試結果及分析

4.1使用sql-bench性能測試結果及分析

使用sql-bench的測試結果如下：

表2

對于InnoDB引擎，sql-bench測試結果顯示整體上icc編譯的mysql在執行時間上較gcc編譯的mysql沒有優勢，相反還有劣勢。但分析測試過程中的各種SQL，發現基于InnoDB表primary key的更新，查找操作icc編譯的mysql較gcc編譯的mysql還是有優勢的。但是對于基于InnoDB輔助索引的查找和更新，icc編譯的mysql性能不如gcc編譯的mysql。這應該和InnoDB數據存儲方式聚簇索引相關，基于primary key的操作直接可以定位需要的數據；但是基于輔助索引的操作，則需要輔助索引和primary key兩次才能定位，這中間是大量的隨機讀，增加IO負載。

對于MyISAM引擎，不存在上述問題，所有索引中直接存放著數據行的物理位置。從sql-bench測試結果上看，icc編譯的mysql優勢明顯，整體在執行時間上減少了13.9%。同時從測試中每個階段上來看，在insert，select階段，icc較gcc分別減少13.8%，26.1%。

在對于InnoDB和MyISAM測試過程中，統計系統cpu信息和相關進程占用內存的信息。Icc編譯的mysql在cpu開銷上和gcc編譯的mysql相差不多；內存使用上icc版mysql較gcc版要少。通過iostat觀察磁盤利用率絕大部分時間里保持在5%以下，而cpu的user使用率在20%以上。尤其是在MyISAM測試過程中insert和select測試階段，磁盤利用率大部分時間保持在3%以下。通過這些數據可以看出CPU相關的計算操作是這個測試中較主要的方面，而IO負載對測試結果影響較少。

通過這個測試，可以看出icc編譯的mysql對于MyISAM引擎優化效果明顯；對于InnoDB基于primary key的操作有優化效果。但是對于InnoDB基于輔助索引的操作，icc編譯的mysql存在劣勢。同時，在IO負載不大，CPU負載相對較大的環境中，icc可以發揮優勢。

4.2 使用mysqlslap的測試結果及分析

4.2.1 使用某數據庫數據，InnoDB引擎測試結果及分析

4.2.1.1 對于全腳本回放測試結果比較及分析

整個腳本中的SQL均是InnoDB引擎的。從整個腳本回放測試的結果比較來看，icc編譯出的mysql并沒有顯現出優勢，執行時間上比gcc編譯出的mysql慢。

表3

表4

表4是icc編譯的mysql和gcc編譯的mysql在測試過程中資源使用情況的對比。從表中數據可以看出，icc編譯的mysql在cpu，內存開銷上較gcc編譯的mysql要小。同時需要注意的是在cpu花在系統kernel內的時間上，icc編譯的mysql明顯多于gcc編譯的mysql。懷疑底層系統由gcc編譯和上層icc編譯的應用程序配合有問題。在全腳本測試的過程中，通過iostat觀察IO負載情況，發現磁盤利用率大部分時間保持在50%以上，一部分時間會在90%以上。說明這種情況下，IO負載是比較大的。

4.2.1.2 特定的3個SQL測試結果及分析

在做完整個全腳本測試比較之后，我分析了腳本中包含的SQL。把它們歸納歸類，然后對每一種類型的SQL進行對比測試。從每種SQL的執行計劃、執行過程來分析該SQL在icc編譯的mysql和gcc編譯的mysql表現出來的不同執行時間。從這些信息分析icc編譯的mysql性能具有優勢的方面。下面對3種具有代表性的SQL的測試結果。

SQL1：update tst_report_orderinfo_stat t,tst_userposmap_info t1,tst_postree_info t2 set t.posid=t2.posid where t.submitor_id=t1.ucid and t1.posid=t2.posid and t1.dataowner=1 and t2.postype=3 and t.finance_arr_date=t1.stat_date and t2.stat_date=t1.stat_date ;

表5

將上述SQL稍微改造一下，以獲得該SQL的執行計劃（該執行計劃和上面update操作相似）：

select t.posid，t2.posid from tst_report_orderinfo_stat t,tst_userposmap_info t1,tst_postree_info t2 where t.submitor_id=t1.ucid and t1.posid=t2.posid and t1.dataowner=1 and t2.postype=3 and t.finance_arr_date=t1.stat_date and t2.stat_date=t1.stat_date ;

獲得的執行計劃：

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t1

type: ALL

possible_keys: PRIMARY,tst_userposmap_info_stat_date_idx

key: NULL

key_len: NULL

ref: NULL

rows: 1157224

Extra: Using where

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: t2

type: eq_ref

possible_keys: PRIMARY,tst_postree_info_stat_date_idx

key: PRIMARY

key_len: 7

ref: xxx.t1.posid,xxx.t1.stat_date

rows: 1

Extra: Using where

*************************** 3. row ***************************

id: 1

select_type: SIMPLE

table: t

type: ref

possible_keys: index_report_finance_arr_date_idx,index_report_submiter_id

key: index_report_submiter_id

key_len: 4

ref: xxx.t1.ucid

rows: 16

Extra: Using where

3 rows in set (0.00 sec)

從執行計劃上可以看出，驅動表采取的全表掃描的方式取得數據，而不是通過索引。即使是Innodb也要加表鎖，所以在增加concurrency后，mysql也只能串行處理這些請求。這樣在第一次執行該SQL時需要從磁盤上取得相關數據，而在第一次以后再執行該SQL時，就不需要從磁盤上取得數據（數據會被緩存）。后續的SQL執行消耗的是CPU資源，從測試結果來看，icc編譯的mysql在concurrency=1沒有優勢；但是在concurrency>1后，逐漸顯現出優勢，并且優勢隨著concurrency增加而增加?？梢钥闯鰅cc編譯出的mysql在CPU運算方面的優勢。

SQL2：select blacklist_id, company_name from td_blacklist where company_name like '%xxx%' and del_flag= 0;

表6

本SQL的執行計劃為：

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: td_blacklist

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1589

Extra: Using where

從上述執行計劃可以看出該操作使用全表掃描過濾數據，這種方式是順序讀操作，并且涉及的行數只有1589行。IO操作的壓力不大，這要消耗應是CPU相關操作。從本條SQL的測試結果上看，在InnoDB引擎下，對于全表掃描的操作，icc編譯的mysql較gcc編譯的mysql沒有劣勢；在高并發下，icc編譯的mysql還有優勢。

SQL3：update tb_cust_app tc left join (select count(distinct f.cust_id) num, follow_id from tb_follow_assign f, tb_customer c where f.cust_id=c.cust_id and c.cust_stat_1<>5 group by follow_id) tf on tc.user_id = tf.follow_id set tc.ownered_size=ifnull(tf.num,0) ;

表7

將上述SQL稍微改造一下，以獲得該SQL的執行計劃：

select tc.ownered_size,ifnull(tf.num,0) from tb_cust_app tc left join (select count(distinct f.cust_id) num, follow_id from tb_follow_assignf, tb_customer c where f.cust_id=c.cust_id and c.cust_stat_1<>5 group by follow_id) tf on tc.user_id = tf.follow_id;

相關的執行計劃：

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: tc

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 4386

Extra:

*************************** 2. row ***************************

id: 1

select_type: PRIMARY

table:

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 2655

Extra:

*************************** 3. row ***************************

id: 2

select_type: DERIVED

table: f

type: index

possible_keys: Index_follow_assign_cust_id

key: Index_follow_assign_follow_id

key_len: 5

ref: NULL

rows: 362615

Extra:

*************************** 4. row ***************************

id: 2

select_type: DERIVED

table: c

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: xxx.f.cust_id

rows: 1

Extra: Using where

4 rows in set (3.00 sec)

從上述執行計劃可以看出，table f是按照索引順序進行全表的索引樹掃描，這就會造成很多的隨機讀（使用的索引不是primary key）。大量的隨機讀會造成比較大的IO壓力。從測試結果上看，icc編譯出的mysql與gcc編譯出的mysql相比，在執行時間存在一定的劣勢。前面的全腳本測試中存在比較多的這種SQL，因此全腳本回放測試中icc編譯出的mysql執行時間上比gcc編譯的mysql多。從本條SQL的執行計劃和測試結果上看，在InooDB引擎下，使用輔助索引，icc編譯出的mysql很可能出現劣勢，這和sql-bench測試結果一致。

SQL1：(select customerd, customername, companyname, realcompanyname from tb_shifen_customerwhere urldomain like "%.cn" and status=3 and accountm>0 limit 10) union (select customerd, customername, companyname, realcompanyname from tb_shifen_customer where urldomain like "%.cn" and status in (1,4,6) and status=3 and accountm<=0 and invalidate>=date_sub(curdate(),interval ? day) limit 10) union (select customerd, customername, companyname, realcompanyname from tb_shifen_customer where urldomain like "%.cn" and status in (1,4,6) and accountm>0 limit 10) union (select customerd, customername, companyname, realcompanyname from tb_shifen_customer where urldomain like "%.cn" and status=2 limit 10);

表8

SQL2：select count(*) from tb_customer where urldomain like "%.cn";

表9

SQL3：select cust.cust_id,cust.cust_stat_1,cust.cust_stat_2,cust.cust_name, cust.cust_branch_name,cust.cust_input_type,cust.add_time,cust.cust_follow_num, cust.cust_trade_1,cust.cust_trade_2,dis.distribute_time from tb_customer cust left join tb_cust_distribute dis on cust.cust_id=dis.cust_id and dis.state=1 where cust.cust_id>0 and cust.cust_stat_1 in(8) and cust.pose_id=157 order by cust.cust_id desc limit 1170 , 15;

表10

上述3個類型的SQL是從測試庫上執行的讀操作中挑選出來的，相應的表的引擎改成了MyISAM引擎。這3個SQL涉及了掃表，索引掃描，排序等操作。從測試的結果上看，icc編譯的mysql對MyISAM引擎讀操作的優化效果明顯。從執行時間上看（Qphotoshop/ target=_blank class=infotextkey>PS）減少大概在10%-20%之間（Qphotoshop/ target=_blank class=infotextkey>PS增加10%-20%）。

SQL4：update tb_cust_app tc left join (select count(distinct f.cust_id) num, follow_id from tb_follow_assignf, tb_customer c where f.cust_id=c.cust_id and c.cust_stat_1<>5 group by follow_id) tf on tc.user_id = tf.follow_id set tc.ownered_size=ifnull(tf.num,0) ;

表11

SQL4同上一節的SQL3。在上一節InnoDB引擎下，icc編譯的mysql對于此SQL在執行時間上明顯慢于gcc編譯的mysql，也主要是因為該SQL導致innodb全腳本測試icc編譯的mysql慢于gcc編譯的mysql。但是對于MyISAM引擎，從測試結果上看，icc編譯的mysql明顯優于gcc編譯的mysql。從測試可以看出icc編譯的mysql對MyISAM寫操作也有優化效果，從執行時間上看（Qphotoshop/ target=_blank class=infotextkey>PS）減少大概在10%-20%之間（Qphotoshop/ target=_blank class=infotextkey>PS增加10%-20%）。

5 測試結論

從兩個維度上總結測試結論：1存儲引擎維度；2CPU，IO負載。

從存儲引擎維度：對于MyISAM引擎，從sql-bench，mysqlslap使用某數據庫數據測試結果上看，icc編譯的mysql無論從讀操作還是寫操作都有優化效果，SQL執行時間平均減少10%-20%。對于一些比較消耗CPU的SQL（比如排序等，執行時間較長的SQL）,在一定的并發下優化效果更明顯。

對于InnoDB引擎，從sql-bench，mysqlslap使用全腳本測試結果上看，icc編譯的mysql較gcc編譯的mysql從Qphotoshop/ target=_blank class=infotextkey>PS（SQL執行時間）沒有優勢，甚至是劣勢。同時從sql-bench，全腳本中的逐個SQL分析來看：對于利用primary key或者全表掃描的SQL，icc編譯的mysql有一些優化效果；對于利用輔助索引的SQL，icc編譯的mysql在執行時間上比gcc編譯的mysql慢。分析原因，InnoDB使用聚簇索引存儲數據，利用輔助索引時，還需要走一遍primary key，這中間會有比較多的隨機讀等操作。

從IO，CPU負載維度：通過測試中對于資源的統計和對比，icc編譯的mysql在用戶態cpu開銷上較gcc編譯的mysql小（相差不大）；在內核態cpu開銷要比gcc編譯的mysql多；在內存上開銷上icc編譯的mysql稍小。Icc對于CPU密集，IO負載不重的場景，優化效果明顯；對于IO負載較重的場景，icc編譯的mysql優化效果可能不明顯。

綜上所述：icc編譯的mysql用于MyISAM引擎，較gcc編譯的mysql優化效果明顯。對于InnoDB引擎，使用輔助索引等操作，icc編譯的mysql比gcc編譯的mysql在執行時間上要慢，存在劣勢。對于使用全表掃描、primary key的InnoDB操作，在低并發下，icc編譯的mysql在執行時間上不會慢，在高并發下icc編譯的mysql具有優勢。同時業務類型是CPU密集型，而不是IO密集型，有助于發揮icc編譯器的優化效果。