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 正确性测试结果 下表中的数据是相关测试结果： 正确性测试结果 | InnoDB | MyISAM | SQL1执行结果的MD5 | SQL2执行结果的MD5 | SQL1执行结果的MD5 | SQL1执行结果的MD5 | Icc编译的mysql | 6d48abf99ba07623 e98312079c4ae84f | a76c01d4047639de d05bc06d8b800e96 | 6d48abf99ba07623 e98312079c4ae84f | a76c01d4047639de d05bc06d8b800e96 | Gcc编译的mysql | 6d48abf99ba07623 e98312079c4ae84f | a76c01d4047639de d05bc06d8b800e96 | 6d48abf99ba07623 e98312079c4ae84f | a76c01d4047639de d05bc06d8b800e96 |
表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的测试结果如下： InnoDB | 执行时间 | Cpu%（us） | Cpu%（sy） | Cpu%（id） | Mem% | Icc编译的mysql | 1427s | 20.4 | 5.1 | 70.8 | 7.9 | Gcc编译的mysql | 1248s | 19.6 | 5.9 | 70.5 | 9.7 | Icc较gcc的优势 | -14.3% | - | - | - | - | MyISAM | 执行时间 | | | | | Icc编译的mysql | 502.69s | 19.1 | 8.0 | 71.5 | 4.4 | Gcc编译的mysql | 583.88s | 19.6 | 7.5 | 71.5 | 8.0 | Icc较gcc的优势 | 13.9% | - | - | - | - |
表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慢。
全脚本测试 | 执行时间 | Concurrency=1 | Concurrency=10 | Concurrency=20 | Icc编译的mysql | 230.34s | 736.70s | 1614.49s | Gcc编译的mysql | 197.34s | 623.70s | 1334.76s | Icc较gcc的优势 | -16.7% | -18.1% | -21.0% |
表3 全脚本测试 | Concurrency=1 | Concurrency=10 | Concurrency=20 | Cpu% | Mem% | Cpu% | Mem% | Cpu% | Mem% | us | sy | Id | Us | sy | Id | us | sy | id | Icc-mysql | 9.9 | 2.0 | 71.9 | 7.7 | 61.7 | 9.0 | 25.3 | 7.5 | 66.4 | 17.2 | 15.1 | 7.5 | Gcc-mysql | 10.1 | 1.2 | 72.6 | 10.7 | 60.6 | 3.1 | 30.3 | 11.3 | 80.1 | 7.2 | 10.6 | 11.5 |
表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 ; SQL1 | 执行时间 | Concurrency=1 | Concurrency=5 | Concurrency=10 | Icc编译的mysql | 35.06s | 95.05s | 168.87s | Gcc编译的mysql | 34.59s | 100.18s | 179.13s | Icc较gcc的优势 | -1% | 5.1% | 5.7% |
表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增加而增加。可以看出icc编译出的mysql在CPU运算方面的优势。 SQL2：select blacklist_id, company_name from td_blacklist where company_name like '%xxx%' and del_flag= 0;
SQL2 | 执行时间 | Concurrency=10 | Concurrency=50 | Concurrency=100 | Icc编译的mysql | 0.228s | 0.265s | 0.337s | Gcc编译的mysql | 0.227s | 0.287s | 0.365s | Icc较gcc的提升 | -0.4% | 8% | 8% |
表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) ; SQL3 | 执行时间 | Concurrency=1 | Concurrency=10 | Concurrency=50 | Icc编译的mysql | 52.30s | 79.37s | 557.23s | Gcc编译的mysql | 50.81s | 77.30s | 452.49s | Icc较gcc的提升 | -3% | -2.7% | -23.1% |
表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测试结果一致。 | 