P.Linux Laboratory

自己动手实现Multi-Master Replication

P.Linux — Tue, 14 Feb 2012 19:57:50 +0800

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/database/diy_multi_master_replication.html

首发：http://www.mysqlops.com/2012/02/14/diy_multi_master_replication.html

直到今天为止，MySQL依然只支持一个Slave从一个Master复制数据，虽然也可以做到一主多备(M->S)，双主复制(M<->M)等架构，但是局限性依然很大。
例如最近我们遇到一个问题，需要为线上的集群搭建在线延时备份，即从线上的双主集群中再延伸出一组Slave，以防重要集群主备都宕机。按照现在MySQL的架构，要搭建这种在线备份，只能启动相同数据的实例来实现，假设线上有128个实例在提供服务，那么我就需要128个实例来做这128个实例的复制，这个管理成本是巨大的。
之前我们也有个方案，利用Perl脚本来做，参见这篇文章：点我阅读。这个方案的最大问题就是管理不方便，没有可以监控的地方，也不能随便停止脚本等等，如果完善这些部分，代码量太大，几乎就实现了一个MySQL Replication，那还不如利用MySQL的管理部分，在MySQL里实现多Master。

通过研究源码，可以发现，MySQL管理每个复制通道，都是通过一个Master_info类（sql/rpl_mi.h中定义），start_slave/change_master/stop_slave/show_slave/end_slave这些函数都需要传入一个Master_info指针，这就给我们改造多Master提供了很大的便利，基本只需要为每个复制通道传入相应的Master_info即可。

除了找到函数入口，还需要让语法支持多主，否则CHANGE MASTER TO语句并不能支持多主。我修改了sql_yacc.yy，支持如下语法：
CHANGE MASTER ‘通道标识’ TO，START SLAVE ‘通道标识’，STOP SLAVE ‘通道标识’，SHOW SLAVE ‘通道标识’ STATUS。
这样就可以支持多Master的语法了。

另一个问题是怎么保存多个通道的信息，默认单通道的情况下，用master.info存Master的信息，用relay-log.info存复制应用的情况。所以存储文件的名称也要修改，我的方式是，master.info和relay-log.info在末尾加上通道标识后缀，例如名为”plx”的通道，会存成master.info.plx和relay-log.info.plx。Relay Log因为有序列，所以增加”-通道标识”在序列前。
还有一个问题就是，操作命令都是用通道标识来确定一个通道，那么肯定需要持久化正在用的通道名称，以及建立通道后可以用通道名获取相应的Master_info。于是我新建了一个MASTER_INFO_INDEX类（在sql/rpl_mi.h），里面包含一个通道标识和Master_info指针的对应HASH表，以及持久化需要的IO_CACHE，通过master.info.index这个文件来存已有的通道标识。
命名实例如下：

-rw-rw—- 1 mysql mysql 10 Feb 13 20:40 master.info.index
-rw-rw—- 1 mysql mysql 76 Feb 14 17:27 master.info.plx1
-rw-rw—- 1 mysql mysql 71 Feb 14 17:27 master.info.plx2
-rw-rw—- 1 mysql mysql 90 Feb 14 17:25 relay-log.info.plx1
-rw-rw—- 1 mysql mysql 90 Feb 14 17:27 relay-log.info.plx2

-rw-rw—- 1 mysql mysql 160 Feb 14 10:16 mysql-relay-bin-plx1.000011
-rw-rw—- 1 mysql mysql 83765425 Feb 14 17:27 mysql-relay-bin-plx1.000012
-rw-rw—- 1 mysql mysql 106 Feb 14 10:16 mysql-relay-bin-plx1.index
-rw-rw—- 1 mysql mysql 160 Feb 14 10:16 mysql-relay-bin-plx2.000014
-rw-rw—- 1 mysql mysql 83455792 Feb 14 17:27 mysql-relay-bin-plx2.000015
-rw-rw—- 1 mysql mysql 106 Feb 14 10:16 mysql-relay-bin-plx2.index

下载Patch在此：http://bugs.mysql.com/file.php?id=18020

有了多Master以后我们可以做什么呢？下面给两个应用场景。
第一个是一备多的备份。因为我们采用的分库策略，使我们一个集群会有很多个实例，每个实例里面有几个Schema，但是肯定不会重复。例如第一个实例是1～3号Schema。第二个实例就是4～6号Schema，所以binlog应用到一起并不会冲突数据。这是我们测试的在线备份方案。

第二个是跨机房的HA。为了容灾或者加速，很多公司都采用在不同机房部署数据库的方式，所以就涉及到数据同步。为了保证每个机房产生的数据不冲突，一般来说我们采用的是auto_increment_increment，auto_increment_offset这两个参数，可以控制步进。例如双MAster，我们会配置主库是奇数序列的ID，备库是偶数序列的ID，这样切换时就算有少量binlog还未应用，也不会导致数据冲突。跨机房以后，例如两个机房都有双Master，两个机房之间数据又需要同步，以前需要借助第三方脚本或者程序，有了多Master，按如下方式搭建，设置步进为4，就可以保证每个机房有双MAster HA，机房之间数据又可以同步。

已知缺陷：
1. 我还没做reset slave ‘通道标识’命令，就是复制通道还不能重置，只能CHANGE MASTER来改，不是做不了，因为暂时我们没这个需求，等稳定了再考虑这个细节。
2. 数据冲突没有检测。这个是无法解决的，我只是简单的调用了启动Slave的函数来启动多个复制线程，binlog取到本地应用，有数据冲突是不能事先检测的，执行到了才会报出来，可以设置skip-slave-error，对全局有效。其他复制相关的也是全局有效。

类似的文章

2012年01月23日 -- 一个InnoDB性能超过Oracle的调优Case (1)
2011年12月23日 -- 在Server层实现Kill Idle Transaction (1)
2011年11月29日 -- 如何杀掉空闲事务 (1)

一个InnoDB性能超过Oracle的调优Case

P.Linux — Mon, 23 Jan 2012 00:00:59 +0800

年前抽空到兄弟公司支援了一下Oracle迁移MySQL的测试，本想把MySQL调优到接近Oracle的性能即可，但经过 @何_登成 @淘宝丁奇 @淘宝褚霸 @淘伯松诸位大牛的指导和帮助（排名不分先后，仅按第一次为此CASE而骚扰的时间排序），不断修正方案，最终获得了比Oracle更好的性能，虽然是个特殊场景，但是我觉得意义是很广泛的，值得参考，遂记录于此。
所有涉及表结构和具体业务模型的部分全部略去，也请勿咨询，不能透露，敬请谅解。

一、测试模型：

包含12张业务表，每个事务包含12个SQL，每个SQL向一张表做INSERT，做完12个SQL即完成一个事务。

用一个C API编写的程序连接MySQL，不断执行如下操作

开始事务：START TRANSACTION;
每张表插入一行：INSERT INTO xxx VALUES (val1,val2,…); #一共12次
提交事务：COMMIT;

通过一个Shell脚本来启动32个测试程序并发测试

二、测试环境：

1. 机型：

R510
CPU：Intel(R) Xeon(R) CPU E5645 @ 2.40GHz 双路24线程
内存：6 * 8G 48G
存储：FusionIO 320G MLC

R910
CPU：Intel(R) Xeon(R) CPU E7530 @ 1.87GHz 四路48线程
内存：32* 4G 128G
存储：FusionIO 640G MLC

2. Linux配置：

单实例启动数据库：/boot/grub/menu.lst修改kernel启动参数增加numa=off
多实例启动数据库：numactl –cpunodebind=$BIND_NO –localalloc $MYSQLD

RHEL 5.4 with 2.6.18内置内核
RHEL 6.1 with 2.6.32淘宝版内核

fs.aio-max-nr = 1048576 #调整系统允许的最大异步IO队列长度
vm.nr_hugepages = 18000 #大页页数
vm.hugetlb_shm_group = 601 #允许使用大页的用户id，即mysql用户
vm.swappiness = 0 #不倾向使用SWAP

3. FusionIO配置：

启动配置：
/etc/modprobe.d/iomemory-vsl.conf
options iomemory-vsl use_workqueue=0 # 忽略Linux IO调度
options iomemory-vsl disable-msi=0 # 开启MSI中断
options iomemory-vsl use_large_pcie_rx_buffer=1 # 打开PCIE缓冲
options iomemory-vsl preallocate_memory=SN号 # 预分配管理内存

格式化配置：
fio-format -b 4K /dev/fct0 # 格式化设备为4K匹配NAND芯片页大小
mkfs.xfs -f -i attr=2 -l lazy-count=1,sectsize=4096 -b size=4096 -d sectsize=4096 -L data /dev/fioa # 调整XFS与FusionIO 4K页匹配，比较激进，需要更多稳定性测试认为这组参数充分安全

mount配置：
/dev/fioa on /data type xfs (rw,noatime,nodiratime,noikeep,nobarrier,allocsize=100M,attr2,largeio,inode64,swalloc) # FusionIO的逻辑Block是100M，所以设为100M的预扩展

4. MySQL版本和通用配置：

Percona 5.1.60-13.1 原版
Percona 5.1.60-13.1 修改版
* 允许自定义InnoDB AIO队列申请长度 (5.5_change_aio_io_limit.patch)
Percona 5.5.19-24.0 原版
* 允许innodb_flush_neighbor_pages=2来合并真正相邻的脏页合并
* Group Commit
Percona 5.5.18-23.0 修改版
* 允许自定义InnoDB AIO队列申请长度 (5.5_change_aio_io_limit.patch)
* 允许预先扩展数据文件 (5.5_innodb_extent_tablespace.patch，@淘宝丁奇贡献)
* Group Cimmit

innodb_buffer_pool_size=20G
sync_binlog=1
innodb_flush_log_at_trx_commit=1

测试并发：32

5. 修改补丁

#cat 5.5_change_aio_io_limit.patch

--- Percona-Server-5.5.18-23.0/storage/innobase/handler/ha_innodb.cc	2011-12-20 06:38:58.000000000 +0800
+++ Percona-Server-5.5.18-23.0-debug/storage/innobase/handler/ha_innodb.cc	2012-01-17 10:13:41.000000000 +0800
@@ -146,6 +146,7 @@
 static ulong innobase_commit_concurrency = 0;
 static ulong innobase_read_io_threads;
 static ulong innobase_write_io_threads;
+static ulong innobase_aio_pending_ios_per_thread; // Change AIO io_limit By P.Linux
 static long innobase_buffer_pool_instances = 1;
 
 static ulong innobase_page_size;
@@ -2870,6 +2871,7 @@
 	srv_n_file_io_threads = (ulint) innobase_file_io_threads;
 	srv_n_read_io_threads = (ulint) innobase_read_io_threads;
 	srv_n_write_io_threads = (ulint) innobase_write_io_threads;
+	srv_n_aio_pending_ios_per_thread = (ulint) innobase_aio_pending_ios_per_thread;
 
 	srv_read_ahead &amp;= 3;
 	srv_adaptive_flushing_method %= 3;
@@ -12282,6 +12284,11 @@
   "Number of background write I/O threads in InnoDB.",
   NULL, NULL, 4, 1, 64, 0);
 
+static MYSQL_SYSVAR_ULONG(aio_pending_ios_per_thread, innobase_aio_pending_ios_per_thread,
+  PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
+  "Number of AIO pending IOS per-thread in InnoDB.",
+  NULL, NULL, 4, 32, 4096, 0);
+
 static MYSQL_SYSVAR_LONG(force_recovery, innobase_force_recovery,
   PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
   "Helps to save your data in case the disk image of the database becomes corrupt.",
--- Percona-Server-5.5.18-23.0/storage/innobase/srv/srv0srv.c	2011-12-20 06:38:57.000000000 +0800
+++ Percona-Server-5.5.18-23.0-debug/storage/innobase/srv/srv0srv.c	2012-01-17 10:23:35.000000000 +0800
@@ -242,6 +242,7 @@
 UNIV_INTERN ulint	srv_n_file_io_threads	= ULINT_MAX;
 UNIV_INTERN ulint	srv_n_read_io_threads	= ULINT_MAX;
 UNIV_INTERN ulint	srv_n_write_io_threads	= ULINT_MAX;
+UNIV_INTERN ulint   srv_n_aio_pending_ios_per_thread = ULINT_MAX; // Change AIO io_limit By P.Linux
 
 /* Switch to enable random read ahead. */
 UNIV_INTERN my_bool	srv_random_read_ahead	= FALSE;
--- Percona-Server-5.5.18-23.0/storage/innobase/srv/srv0start.c	2011-12-20 06:38:57.000000000 +0800
+++ Percona-Server-5.5.18-23.0-debug/storage/innobase/srv/srv0start.c	2012-01-17 10:25:12.000000000 +0800
@@ -1475,14 +1475,16 @@
 
 	ut_a(srv_n_file_io_threads

#cat 5.5_innodb_extent_tablespace.patch

--- Percona-Server-5.5.18-23.0/sql/sql_yacc.yy	2011-12-20 06:38:58.000000000 +0800
+++ Percona-Server-5.5.18-23.0-debug/sql/sql_yacc.yy	2012-01-17 14:45:47.000000000 +0800
@@ -3878,6 +3878,14 @@
           { 
             Lex-&gt;alter_tablespace_info-&gt;ts_alter_tablespace_type= ALTER_TABLESPACE_DROP_FILE; 
           }
+        /* innodb_extent_tablespace By P.Linux */
+        | tablespace_name
+          SET
+          opt_ts_extent_size
+          {
+            Lex-&gt;alter_tablespace_info-&gt;ts_alter_tablespace_type= ALTER_TABLESPACE_ALTER_FILE;
+          }
+        /* End */
         ;
 
 logfile_group_info:
--- Percona-Server-5.5.18-23.0/sql/handler.h	2011-12-20 06:38:58.000000000 +0800
+++ Percona-Server-5.5.18-23.0-debug/sql/handler.h	2012-01-17 14:29:17.000000000 +0800
@@ -501,7 +501,8 @@
 {
   TS_ALTER_TABLESPACE_TYPE_NOT_DEFINED = -1,
   ALTER_TABLESPACE_ADD_FILE = 1,
-  ALTER_TABLESPACE_DROP_FILE = 2
+  ALTER_TABLESPACE_DROP_FILE = 2,
+  ALTER_TABLESPACE_ALTER_FILE = 3 // innodb_extent_tablespace By P.Linux
 };
 
 enum tablespace_access_mode
--- Percona-Server-5.5.18-23.0/storage/innobase/fil/fil0fil.c	2011-12-20 06:38:57.000000000 +0800
+++ Percona-Server-5.5.18-23.0-debug/storage/innobase/fil/fil0fil.c	2012-01-17 14:31:40.000000000 +0800
@@ -368,7 +368,8 @@
 Checks if a single-table tablespace for a given table name exists in the
 tablespace memory cache.
 @return	space id, ULINT_UNDEFINED if not found */
-static
+//static
+UNIV_INTERN // innodb_extent_tablespace By P.Linux
 ulint
 fil_get_space_id_for_table(
 /*=======================*/
@@ -4676,7 +4677,8 @@
 Checks if a single-table tablespace for a given table name exists in the
 tablespace memory cache.
 @return	space id, ULINT_UNDEFINED if not found */
-static
+//static
+UNIV_INTERN // innodb_extent_tablespace By P.Linux
 ulint
 fil_get_space_id_for_table(
 /*=======================*/
--- Percona-Server-5.5.18-23.0/storage/innobase/handler/ha_innodb.cc	2011-12-20 06:38:58.000000000 +0800
+++ Percona-Server-5.5.18-23.0-debug/storage/innobase/handler/ha_innodb.cc	2012-01-17 14:37:49.000000000 +0800
@@ -433,6 +434,12 @@
 /*=======================*/
 	uint	flags);
 
+/****************************************************************//**
+Alter tablespace supported in an InnoDB table. Allow setting extent space. */
+int innobase_alter_tablespace(handlerton *hton,
+                                THD* thd, st_alter_tablespace *alter_info);
+/* innodb_extent_tablespace By P.Linux */
+
 static const char innobase_hton_name[]= "InnoDB";
 
 /*************************************************************//**
@@ -2489,6 +2496,7 @@
         innobase_hton-&gt;flags=HTON_NO_FLAGS;
         innobase_hton-&gt;release_temporary_latches=innobase_release_temporary_latches;
 	innobase_hton-&gt;alter_table_flags = innobase_alter_table_flags;
+	innobase_hton-&gt;alter_tablespace= innobase_alter_tablespace; // innodb_extent_tablespace By P.Linux
 
 	ut_a(DATA_MYSQL_TRUE_VARCHAR == (ulint)MYSQL_TYPE_VARCHAR);
 
@@ -3146,6 +3155,33 @@
 		| HA_INPLACE_ADD_PK_INDEX_NO_READ_WRITE);
 }
 
+/****************************************************************//**
+Alter tablespace supported in an InnoDB table. Allow setting extent space. */
+int innobase_alter_tablespace(handlerton *hton,
+                                THD* thd, st_alter_tablespace *alter_info)
+{
+       if (alter_info-&gt;ts_alter_tablespace_type != ALTER_TABLESPACE_ALTER_FILE)
+       {
+               return HA_ADMIN_NOT_IMPLEMENTED;
+       }
+
+       ulint table_space= fil_get_space_id_for_table(alter_info-&gt;tablespace_name);
+
+       if (table_space == ULINT_UNDEFINED)
+       {
+               my_error(ER_WRONG_TABLE_NAME, MYF(0), alter_info-&gt;tablespace_name);
+               return EE_FILENOTFOUND;
+       }
+
+       ulint extent_size= alter_info-&gt;extent_size;
+       
+       ulint actual_size=0;
+       fil_extend_space_to_desired_size(&amp;actual_size, table_space, extent_size);
+
+       return 0;
+}
+/* innodb_extent_tablespace By P.Linux */
+
 /*****************************************************************//**
 Commits a transaction in an InnoDB database. */
 static
--- Percona-Server-5.5.18-23.0/storage/innobase/include/fil0fil.h	2011-12-20 06:38:57.000000000 +0800
+++ Percona-Server-5.5.18-23.0-debug/storage/innobase/include/fil0fil.h	2012-01-17 14:39:20.000000000 +0800
@@ -744,6 +744,18 @@
 /*============================*/
 	ulint		id);	/*!&lt; in: space id */
 
+/*******************************************************************//**
+Checks if a single-table tablespace for a given table name exists in the
+tablespace memory cache.
+@return        space id, ULINT_UNDEFINED if not found */
+UNIV_INTERN
+ulint
+fil_get_space_id_for_table(
+/*=======================*/
+       const char*     name);  /*!&lt; in: table name in the standard
+                               'databasename/tablename' format */
+/* innodb_extent_tablespace By P.Linux */
+
 /*************************************************************************
 Return local hash table informations. */

三、测试结果：

1. R910 Oracle单实例

测试人：童家旺，支付宝
TPS：稳定值2000,峰值2600 （我没参与测试，也没有报告，无法确定详情）
我的补充：Oracle已经是调优的过的，请相信我们的Oracle DBA不是吃素的。我把听Oracle DBA描述的只言碎语随便写下，Oracle跑到后面TPS也是有所下降，不是能一直100%稳定，最后CPU已经吃尽了，所以基本上再怎么优化提升的幅度会比较小。

2. R910 MySQL单实例 Percona 5.1.59 原版

测试人：帝俊，支付宝
TPS：峰值1500，无法稳定（具体不祥）
测试人描述：
目前的测试数据显示，由于MySQL在checkpoint上处理跟不上，不足以持续支持1.5K/s的事务数，10MB/s的redo量下的交易创建。该负载下，FIO的写出速度为160～190MB/s，写IOPS为2～2.3k，测试FIO的写吞吐量可以到600MB/s，写IOPS有8K+，需要进一步研究如何进一步提升系统的吞吐量。

3. R910 MySQL多实例 Percona 5.1.60-13.1原版

测试人：彭立勋，B2B
TPS：峰值500*4（无法稳定），谷值100，均值450＊4
重要配置：
innodb_page_size=4K # 修改数据页大小与FusionIO匹配
innodb_log_block_size=4K # 修改日志页大小于FusionIO匹配
innodb_log_file_size=1G
innodb_log_files_in_group=3
innodb_buffer_pool_size=20G
innodb_max_dirty_pages_pct=75
innodb_flush_method=ALL_O_DIRECT # 修改文件写入方式全部为O_DIRECT
innodb_read_io_threads=2
innodb_write_io_threads=10
innodb_io_capacity=20000
innodb_extra_rsegments=16
innodb_use_purge_thread=4
innodb_adaptive_flushing_method=3 # 采用Keep_average刷新方式
innodb_flush_neighbor_pages=0 # 不为了凑顺序IO刷相邻未修改的页
测试人描述：
每颗物理CPU绑定一个MySQL实例，四个实例同时接受测试。可以看到在测试过程中，IOPS抖动很大，在4K～17K之间抖动，可以判定，是Checkpoint机制不完善导致刷新间歇性繁忙，在IO闲置的时候不能充分发挥性能。但多实例可以提升整体TPS接近Oracle的均值，说明MySQL内部可能某些常量设置不合理，或者锁定力度太粗导致单实例不能充分发挥单机性能。

4. R910 MySQL多实例 Percona 5.1.60-13.1 修改版

测试人：彭立勋，B2B
TPS：峰值1200*4，谷值0，均值950*4
重要配置：（在测试3的基础上）
innodb_aio_pending_ios_per_thread=1024
测试人描述：
经过对测试3的分析，可以发现，InnoDB已经标记了很多Page到Flush_list，但是并没有被即时的回写，可以在INNODB_BUFFER_POOL_PAGES系统表中发现很页flush_type=2，即在Flush_list中。
经过review代码，发现InnoDB申请的AIO队列的长度只有256，由常量OS_AIO_N_PENDING_IOS_PER_THREAD（os0file.h）定义。将此常量修改为InnoDB的参数后，重新测试，可以使FusionIO的IOPS达到7K～18K，IO利用率得以提升，整体性能已经超越Oracle，但存在严重的低谷，大约每10s一次。

5.R510 MySQL单实例 Percona 5.5.18-23.0 修改版

测试人：彭立勋,B2B
TPS：峰值2800，谷值2300，均值2500
重要配置：（在测试3的基础上）
innodb_aio_pending_ios_per_thread=512
alter tablespace `trade/xxx` set extent_size=5000000; # 预先扩展数据文件
测试人描述：
根据测试4的结果进行分析，需要解决的主要问题就是抖动，抖动可能是两个原因导致的，一个是Checkpoint机制不完善，一个是数据文件扩展。Checkpoint机制不完善这个暂时无法改进，只能先解决数据文件扩展上的问题，采用淘宝丁奇的方法，对MySQL增加预先扩展文件的功能，在测试前先将文件扩展至测试写满需要的大小，使测试过程中无需扩展文件。
实例测试中发现非常有效，抖动范围在2300～2800之间，可以接受。但是Buffer Pool一旦脏页写满，为了控制脏页量InnoDB就会加大刷新量，影响到TPS。实际上在脏页未满的时候，IOPS就没有用完，但是InnoDB计算刷新量并没有考虑操作系统反馈的影响信息，只是根据自己的redo产生量计算。

同时观察CPU还发现，2.6.18内核会将所有软中断发送到Core0上处理，这可能也是瓶颈之一。（当时忘记拷贝状态，这是后来确认内核问题看得，可以看这篇文章，一样的，CPU软中断实践）
03:05:17 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
03:05:18 PM all 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 1014.00
03:05:18 PM 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 1000.00

6. R510 MySQL单实例 Percona 5.5.19-24.0 原版

测试人：彭立勋，B2B
TPS：峰值3100，谷值2400，均值2700
重要配置：（在测试3的基础上）
替换内核版本为2.6.32淘宝版，使用IO中断负载均衡。
innodb_adaptive_flushing_method = 2
innodb_flush_neighbor_pages = cont
测试人描述：
采用淘宝版内核后，可以发现每个CPU都被用的比较满：(部分)
06:27:26 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest   %idle
06:27:27 PM  all   69.80    0.00   18.68    0.51    0.00    0.17    0.00    0.00   10.84
06:27:27 PM    0   74.75    0.00   17.17    0.00    0.00    0.00    0.00    0.00    8.08
06:27:27 PM    1   73.96    0.00   16.67    1.04    0.00    0.00    0.00    0.00    8.33
06:27:27 PM    2   73.20    0.00   17.53    1.03    0.00    0.00    0.00    0.00    8.25
06:27:27 PM    3   71.72    0.00   19.19    1.01    0.00    0.00    0.00    0.00    8.08
06:27:27 PM    4   71.43    0.00   18.37    1.02    0.00    0.00    0.00    0.00    9.18
06:27:27 PM    5   70.71    0.00   19.19    1.01    0.00    0.00    0.00    0.00    9.09

这是个好现象，说明CPU被充分用起来了，在脏页未满之前，TPS可以比较稳定的维持在3000以上。但还是老问题，脏页一满，速度就下降，到测试结束时下降为2400。

四、测试结论：

MySQL的调优与操作系统结合非常紧密，需要整体联动才能获得好的效果，InnoDB琐粒度较粗的缺陷，在代码实现简单的情况下，实际上对并发的影响不是很明显。
目前MySQL对高速硬件的利用主要缺陷是，不少常量写死，Checkpoint机制不完善，Checkpoint刷新脏页–>InnoDB AIO队列–>操作系统IO队列–>存储设备，中间任何一环存在问题，都可能导致性能下降。
InnoDB AIO队列可以通过补丁开放参数设置，这个瓶颈已经消除。
操作系统IO队列可以通过淘宝的内核补丁将中断分散到每个核上处理来解决。
目前存在最大的问题就是Checkpoint刷新脏页的机制，仅仅依赖redo产生的速度，其实硬件IO还有很多余量，但InnoDB并不知道。
如果能限定一种机型，限定一种操作系统，在MySQL内获取操作系统报告的硬件状态，自适应的决策自己的行为，这样可以充分利用系统资源，例如IO util%并不高的时候，即使脏页还没到阈值，也可以加大刷新量，充分利用IO，这样可能系统根本就达不到脏页阈值，可以一直保持搞TPS，至少可以延缓TPS下降的趋势。
抖动问题则是Oracle和MySQL都存在的问题，扩展数据文件的瞬间必然导致TPS下降，淘宝丁奇的方法可以完美解决，Oracle也是类似的方法通过预先分配表空间文件解决。

五、测试缺陷：

测试CASE不全，没有在R910上测试5.5（虽然已经超了Oracle，但R910上应该还能猛一点），没有测试5.5多实例下可以获得何种性能，没有测试5.1在2.6.32内核下的表现，没有测试不同的页大小对InnoDB的影响。
没有稳定性测试，原版+多实例属于稳定方案，其他改动是否100%不影响稳定，尚需测试。
在R910上的测试没有监控系统，也就没有图，坑爹了。

六、后续Action

在InnoDB控制刷赃页量的地方加入对系统diskstat的监控，当系统IO util%<80%的时候，增加(IO_CAPACITY-当前系统IO数-redo计算的刷新量)个页的刷新，在系统不忙的时候提前加大刷新量，期望保持TPS稳定。

七、随意补充

为什么读为主的应用不用担心IO用不完？因为读操作是同步IO，一旦请求就被发送到磁盘，所以只要并发够多，总能把IO压爆。但是写为了加速，几乎所有数据库都是先写到内存，再异步写到磁盘，当然你要是搞最大保护模式，应该也是有数据库可以直接同步写磁盘的，但是对大部分数据库都是先写内存，再异步到磁盘，所以如果异步IO这里存在设计上的瓶颈，不管加多少并发，都是徒劳，内存一旦写满，链接线程就都堵住了，要等异步IO消化完才能继续，所以对于写为主的应用，这个CASE都是很有参考价值的。

类似的文章

2011年12月23日 -- 在Server层实现Kill Idle Transaction (1)
2010年12月6日 -- Percona对MySQL标准版本的改进 (3)
2010年01月14日 -- XtraDB 1.0.6-9编译错误的解决 (0)

在Server层实现Kill Idle Transaction

P.Linux — Fri, 23 Dec 2011 20:43:22 +0800

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/database/server_kill_idle_transaction.html

在上一篇文章里我们写了如何针对InnoDB清理空闲事务《如何杀掉空闲事务》，在@sleebin9 的提示下，这个功能不仅可以针对InnoDB，也可以用于所有MySQL的事务引擎。

如何在Server层实现呢，sql/sql_parse.cc的do_command()函数是个好函数，连接线程会循环调用do_command()来读取并执行命令，在do_command()函数中，会调用my_net_set_read_timeout(net, thd->variables.net_wait_timeout)来设置线程socket连接超时时间，于是在这里可以下手。
主要代码：

830   /*
 831     This thread will do a blocking read from the client which
 832     will be interrupted when the next command is received from
 833     the client, the connection is closed or "net_wait_timeout"
 834     number of seconds has passed
 835   */
 836   /* Add For Kill Idle Transaction By P.Linux */
 837   if (thd->active_transaction())
 838   {
 839     if (thd->variables.trx_idle_timeout > 0)
 840     {
 841       my_net_set_read_timeout(net, thd->variables.trx_idle_timeout);
 842     } else if (thd->variables.trx_readonly_idle_timeout > 0 && thd->is_readonly_trx)
 843     {
 844       my_net_set_read_timeout(net, thd->variables.trx_readonly_idle_timeout);
 845     } else if (thd->variables.trx_changes_idle_timeout > 0 && !thd->is_readonly_trx)
 846     {
 847       my_net_set_read_timeout(net, thd->variables.trx_changes_idle_timeout);
 848     } else {
 849       my_net_set_read_timeout(net, thd->variables.net_wait_timeout);
 850     }
 851   } else {
 852     my_net_set_read_timeout(net, thd->variables.net_wait_timeout);
 853   }
 854   /* End */

大家看明白了吗？其实这是偷梁换柱，本来在这里是要设置wait_timeout的，先判断线程是不是在事务里，就可以转而实现空闲事务的超时。

trx_idle_timeout 控制所有事务的超时，优先级最高
trx_changes_idle_timeout 控制非只读事务的超时
trx_readonly_idle_timeout 控制只读事务的超时

效果：

root@localhost : (none) 08:39:49> set autocommit = 0 ;
Query OK, 0 rows affected (0.00 sec)
 
root@localhost : (none) 08:39:56> set trx_idle_timeout = 5;
Query OK, 0 rows affected (0.00 sec)
 
root@localhost : (none) 08:40:17> use perf 
Database changed
root@localhost : perf 08:40:19> insert into perf (info ) values('11');
Query OK, 1 row affected (0.00 sec)
 
root@localhost : perf 08:40:26> select * from perf;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: perf
 
+----+------+
| id | info |
+----+------+
|  7 | aaaa |
|  9 | aaaa |
| 11 | aaaa |
+----+------+
3 rows in set (0.00 sec)

完整的patch这里下载：
Note: There is a file embedded within this post, please visit this post to download the file.

类似的文章

2012年01月23日 -- 一个InnoDB性能超过Oracle的调优Case (1)
2010年12月6日 -- Percona对MySQL标准版本的改进 (3)
2010年01月14日 -- XtraDB 1.0.6-9编译错误的解决 (0)

如何杀掉空闲事务

P.Linux — Tue, 29 Nov 2011 01:01:19 +0800

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/database/how_to_kill_idle_trx.html

我们经常遇到一个情况，就是网络断开或程序Bug导致COMMIT/ROLLBACK语句没有传到数据库，也没有释放线程，但是线上事务锁定等待严重，连接数暴涨，尤其在测试库这种情况很多，线上也偶有发生，于是想为MySQL增加一个杀掉空闲事务的功能。

那么如何实现呢，通过MySQL Server层有很多不确定因素，最保险还是在存储引擎层实现，我们用的几乎都是InnoDB/XtraDB，所以就基于Percona来修改了，Oracle版的MySQL也可以照着修改。

需求：
1. 一个事务启动，如果事务内最后一个语句执行完超过一个时间(innodb_idle_trx_timeout)，就应该关闭链接。
2. 如果事务是纯读事务，因为不加锁，所以无害，不需要关闭，保持即可。
虽然这个思路被Percona的指出Alexey Kopytov可能存在“Even though SELECT queries do not place row locks by default (there are exceptions), they can still block undo log records from being purged.”的问题，但是我们确实有场景SELECT是绝对不能kill的，除非之后的INSERT/UPDATE/DELETE发生了，所以我根据我们的业务特点来修改。
跟Percona的Yasufumi Kinoshita和Alexey Kopytov提出过纯SELECT事务不应被kill，但通过一个参数控制的方案还没有被Alexey Kopytov接受，作为通用处理我提出了用两个变量分别控制纯读事务的空闲超时时间和有锁事务的空闲超时时间，还在等待Percona的回复，因为这个方案还在测试，就先不开放修改了，当然如果你很熟悉MYSQL源码，我提出这个思路你肯定知道怎么分成这两个参数控制了。

根据这两个需求我们来设计方法，首先想到这个功能肯定是放在InnoDB Master Thread最方便，Master Thread每秒调度一次，可以顺便检查空闲事务，然后关闭，因为在事务中操作trx->mysql_thd并不安全，所以一般来说最好在InnoDB层换成Thread ID操作，并且InnoDB中除了ha_innodb.cc，其他地方不能饮用THD，所以Master Thread中需要的线程数值，都需要在ha_innodb中计算好传递整型或布尔型返回值给master thread调用。

首先，我们要增加一个参数：idle_trx_timeout，它表示事务多久没有下一条语句发生就超时关闭。
在storage/innodb_plugin/srv/srv0srv.c的“/* plugin options */”注释下增加如下代码注册idle_trx_timeout变量。

static MYSQL_SYSVAR_LONG(idle_trx_timeout, srv_idle_trx_timeout,
  PLUGIN_VAR_RQCMDARG,
  "If zero then this function no effect, if no-zero then wait idle_trx_timeout seconds this transaction will be closed",
  "Seconds of Idle-Transaction timeout",
  NULL, NULL, 0, 0, LONG_MAX, 0);

代码往下找在innobase_system_variables结构体内加上：

MYSQL_SYSVAR(idle_trx_timeout),

有了这个变量，我们需要在Master Thread(storage/innodb_plugin/srv/srv0srv.c )中执行检测函数查找空闲事务。在loop循环的if (sync_array_print_long_waits(&waiter, &sema)判断后加上这段判断

    if (srv_idle_trx_timeout && trx_sys) {
        trx_t*  trx;
        time_t  now;
rescan_idle:
        now = time(NULL);
        mutex_enter(&kernel_mutex);
        trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list); # 从当前事务列表里获取第一个事务
        while (trx) { # 依次循环每个事务进行检查
            if (trx->conc_state == TRX_ACTIVE
                && trx->mysql_thd
                && innobase_thd_is_idle(trx->mysql_thd)) { # 如果事务还活着并且它的状态时空闲的
 
                ib_int64_t  start_time = innobase_thd_get_start_time(trx->mysql_thd); # 获取线程最后一个语句的开始时间
                ulong       thd_id = innobase_thd_get_thread_id(trx->mysql_thd); #获取线程ID，因为存储引擎内直接操作THD不安全
 
                if (trx->last_stmt_start != start_time) { # 如果事务最后语句起始时间不等于线程最后语句起始时间说明事务是新起的
                    trx->idle_start = now; # 更新事务的空闲起始时间
                    trx->last_stmt_start = start_time; # 更新事务的最后语句起始时间
                } else if (difftime(now, trx->idle_start) # 如果事务不是新起的，已经执行了一部分则判断空闲时间有多长了
                       > srv_idle_trx_timeout) { # 如果空闲时间超过阈值则杀掉链接
                    /* kill the session */
                    mutex_exit(&kernel_mutex);
                    thd_kill(thd_id); # 杀链接
                    goto rescan_idle;
                }
            }
            trx = UT_LIST_GET_NEXT(mysql_trx_list, trx); # 检查下一个事务
        }
        mutex_exit(&kernel_mutex);
    }

其中trx中的变量是新加的，在storage/innodb_plugin/include/trx0trx.h的trx_truct加上需要的变量：

struct trx_struct{
...
    time_t      idle_start;
    ib_int64_t  last_stmt_start;
...
}

这里有几个函数是自定义的：

ibool      innobase_thd_is_idle(const void* thd);
ib_int64_t innobase_thd_get_start_time(const void* thd);
ulong      innobase_thd_get_thread_id(const void* thd);

这些函数在ha_innodb.cc中实现，需要在storage/innodb_plugin/srv/srv0srv.c头文件定义下加上这些函数的引用形势。

然后在storage/innodb_plugin/handler/ha_innodb.cc 中定义这些函数的实现：

extern "C"
ibool
innobase_thd_is_idle(
    const void* thd)    /*!< in: thread handle (THD*) */
{
    return(((const THD*)thd)->command == COM_SLEEP);
}
extern "C"
ib_int64_t
innobase_thd_get_start_time(
    const void* thd)    /*!< in: thread handle (THD*) */
{
    return((ib_int64_t)((const THD*)thd)->start_time);
}
extern "C"
ulong
innobase_thd_get_thread_id(
        const void* thd)
{
    return(thd_get_thread_id((const THD*) thd));
}

还有最重要的thd_kill函数负责杀线程的，在sql/sql_class.cc中，找个地方定义这个函数：

void thd_kill(ulong id)
{
    THD *tmp;
    VOID(pthread_mutex_lock(&LOCK_thread_count));
    I_List_iterator<THD> it(threads);
    while ((tmp=it++))
    {
        if (tmp->command == COM_DAEMON || tmp->is_have_lock_thd == 0 ) # 如果是DAEMON线程和不含锁的线程就不要kill了
            continue;
        if (tmp->thread_id == id)
        {
            pthread_mutex_lock(&tmp->LOCK_thd_data);
            break;
        }
    }
    VOID(pthread_mutex_unlock(&LOCK_thread_count));
    if (tmp)
    {
        tmp->awake(THD::KILL_CONNECTION);
        pthread_mutex_unlock(&tmp->LOCK_thd_data);
    }
}

为了存储引擎能引用到这个函数，我们要把它定义到plugin中：
include/mysql/plugin.h和include/mysql/plugin.h中加上

void thd_kill(unsigned long id);

如何判定线程的is_have_lock_thd值？首先在THD中加上这个变量（sql/sql_class.cc）：

class THD :public Statement,
           public Open_tables_state
{
....
  uint16    is_have_lock_thd;
....
}

然后在SQL的必经之路mysql_execute_command拦上一刀，判断是有锁操作发生了还是事务提交或新起事务。

  switch (lex->sql_command) {
  case SQLCOM_REPLACE:
  case SQLCOM_REPLACE_SELECT:
  case SQLCOM_UPDATE:
  case SQLCOM_UPDATE_MULTI:
  case SQLCOM_DELETE:
  case SQLCOM_DELETE_MULTI:
  case SQLCOM_INSERT:
  case SQLCOM_INSERT_SELECT:
      thd->is_have_lock_thd = 1;
      break;
  case SQLCOM_COMMIT:
  case SQLCOM_ROLLBACK:
  case SQLCOM_XA_START:
  case SQLCOM_XA_END:
  case SQLCOM_XA_PREPARE:
  case SQLCOM_XA_COMMIT:
  case SQLCOM_XA_ROLLBACK:
  case SQLCOM_XA_RECOVER:
      thd->is_have_lock_thd = 0;
      break;
  }

为了尽可能兼容Percona的补丁，能引用的都引用了Percona的操作，有些函数调用是在层次太多看不下去了就简化了。
另外还有一个版本是我自己弄的，在THD中增加了一个last_sql_end_time，在do_command结束后更新last_sql_end_time，然后在事务中拿到THD查看last_sql_end_time就可以得出idle时间，Oracle版我还是建议这么做，不要去改trx_struct结构体了，那个感觉更危险。

类似的文章

2012年02月14日 -- 自己动手实现Multi-Master Replication (0)
2012年01月23日 -- 一个InnoDB性能超过Oracle的调优Case (1)
2011年12月23日 -- 在Server层实现Kill Idle Transaction (1)

MySQL的timeout那点事

P.Linux — Thu, 24 Nov 2011 13:39:20 +0800

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/database/mysql_timeout.html

因为最近遇到一些超时的问题，正好就把所有的timeout参数都理一遍，首先数据库里查一下看有哪些超时：

root@localhost : test 12:55:50> show global variables like "%timeout%";
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| connect_timeout            | 10     |
| delayed_insert_timeout     | 300    |
| innodb_lock_wait_timeout   | 120    |
| innodb_rollback_on_timeout | ON     |
| interactive_timeout        | 172800 |
| net_read_timeout           | 30     |
| net_write_timeout          | 60     |
| slave_net_timeout          | 3600   |
| table_lock_wait_timeout    | 50     | # 这个参数已经没用了
| wait_timeout               | 172800 |
+----------------------------+--------+

我们一个个来看

connect_timeout

手册描述:
The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds as of MySQL 5.1.23 and 5 seconds before that.
Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at ‘XXX’, system error: errno.
解释：在获取链接时，等待握手的超时时间，只在登录时有效，登录成功这个参数就不管事了。主要是为了防止网络不佳时应用重连导致连接数涨太快，一般默认即可。

delayed_insert_timeout

手册描述：
How many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating.
解释：这是为MyISAM INSERT DELAY设计的超时参数，在INSERT DELAY中止前等待INSERT语句的时间。

innodb_lock_wait_timeout

手册描述：
The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the –innodb_rollback_on_timeout option, available as of MySQL 5.1.15. See also Section 13.6.12, “InnoDB Error Handling”.)
innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.
InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.
For the built-in InnoDB, this variable can be set only at server startup. For InnoDB Plugin, it can be set at startup or changed at runtime, and has both global and session values.
解释：描述很长，简而言之，就是事务遇到锁等待时的Query超时时间。跟死锁不一样，InnoDB一旦检测到死锁立刻就会回滚代价小的那个事务，锁等待是没有死锁的情况下一个事务持有另一个事务需要的锁资源，被回滚的肯定是请求锁的那个Query。

innodb_rollback_on_timeout

手册描述：
In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.
解释：这个参数关闭或不存在的话遇到超时只回滚事务最后一个Query，打开的话事务遇到超时就回滚整个事务。

interactive_timeout/wait_timeout

手册描述：
The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also
解释：一个持续SLEEP状态的线程多久被关闭。线程每次被使用都会被唤醒为acrivity状态，执行完Query后成为interactive状态，重新开始计时。wait_timeout不同在于只作用于TCP/IP和Socket链接的线程，意义是一样的。

net_read_timeout / net_write_timeout

手册描述：
The number of seconds to wait for more data from a connection before aborting the read. Before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.
On Linux, the NO_ALARM build flag affects timeout behavior as indicated in the description of the net_retry_count system variable.
解释：这个参数只对TCP/IP链接有效，分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间，这是在Activity状态下的线程才有效的参数

slave_net_timeout

手册描述：
The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement or –master-connect-retry option, and the number of reconnection attempts is limited by the –master-retry-count option. The default is 3600 seconds (one hour).
解释：这是Slave判断主机是否挂掉的超时设置，在设定时间内依然没有获取到Master的回应就人为Master挂掉了

类似的文章

2012年02月14日 -- 自己动手实现Multi-Master Replication (0)
2012年01月23日 -- 一个InnoDB性能超过Oracle的调优Case (1)
2011年12月23日 -- 在Server层实现Kill Idle Transaction (1)

在NUMA处理器绑定多实例到固定核心

P.Linux — Fri, 01 Jul 2011 20:18:00 +0800

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/database/mysql_multi_using_numactl.html

另发在：http://www.mysqlops.com/2011/07/01/mysql_multi_using_numactl.html

关于NUMA的介绍我这里就不多说了，网上太多资料了，我在这篇文章要介绍的是如何在MySQL多实例场景下使用numactl来绑定各个实例到具体的物理节点上，避免跨节点分配内存和跨节点访问寄存器。

至于为何使用多实例，因为MySQL对于多处理机和大内存的利用效率不佳，采用多实例可以很大程度提高MySQL对资源的利用，详情可以看Percona的白皮书中对多实例的测试：Scaling MySQL With Virident Flash Drives and Multiple Instances of Percona Server .

numactl这个程序的用法可以参照man手册：http://linux.die.net/man/8/numactl

基本用法是“numactl [option] 程序路径”，例如我希望用numactl启动mysqld则是numactl [option] /usr/local/mysql/bin/mysqld。曾经我误以为numactl是控制某一个程序名，汗……亲手做过才明白是程序路径。

我只介绍几个重要参数
–interleave=all 这是使用交叉分配模式启动一个程序，也就是说程序可以随意跨节点用其他节点的内存，传说中这是效率最高的关闭NUMA特性的方法，只是传说。
–cpunodebind=node 这是把程序绑定在指定的node节点上运行，即使另一个物理节点是idle的，也不会去使用。
–localalloc 严格控制只在节点内分配内存，禁止分配其他节点下的内存到当前节点运行的程序。

我们启动MySQL希望的参数是 numactl –cpunodebind=node –localalloc mysqld_path
为了运维方便，我不可能每次mysql启动都这么执行，我依然希望通过/etc/init.d/mysql和mysqld_multi来管理mysql启动和关闭，于是我采用自定义启动脚本的方式。

首先编写自定义启动脚本如下：

#!/bin/sh
 
# Program Path
NUMACTL=`which numactl`
MYSQLD=/usr/alibaba/mysql/libexec/mysqld
PS=`which ps`
GREP=`which grep`
CUT=`which cut`
WC=`which wc`
EXPR=`which expr`
 
# Variables
CPU_BIND=(`$NUMACTL --show | $GREP nodebind | $CUT -d: -f2 `)   # CPU bins list
CPU_BIND_NUM=${#CPU_BIND[@]}    # How many CPU binds
MYSQLD_NUM=`$PS aux | $GREP mysqld | $GREP -v grep | $GREP '\<mysqld\>' | $WC -l`
MYSQLD_NUM=`$EXPR $MYSQLD_NUM + 1`
BIND_NO=`$EXPR $MYSQLD_NUM % $CPU_BIND_NUM ` # Calc Which CPU to Bind
 
# echo CMD
echo "$NUMACTL --cpunodebind=$BIND_NO --localalloc $MYSQLD" > /tmp/mysqld.$MYSQLD_NUM
 
# use exec to avoid having an extra shell around.
exec $NUMACTL --cpubind=$BIND_NO --localalloc $MYSQLD "$@"

方法是查看当前有多少个mysqld进程已经存在，并且通过numactl –show判断有多少个物理节点，从而判断当前的进程应该分配给哪个节点，例如有2个物理节点，没有mysqld进程，则分配当前进程到0节点，再启动一个实例，当前已经有1个mysqld进程，则分配到1节点，再启动一个实例到0节点……依次循环。

然后在my.cnf文件中配置使用我们自己的脚本启动：

[mysqld_safe]
......
ledir=/usr/local/mysql/bin/ # 放自定义脚本的目录
mysqld=mysqld_using_numactl # 自定义脚本的名称

然后再用/etc/init.d/mysql或mysqld_multi启动mysqld进程就可以实现绑定了。
你可以先启动一个实例，然后在MySQL里做一些消耗CPU的操作，可以观察到只有一个物理节点上的core有活动，哪怕这个节点的core全是100%的利用率，另一个节点的core也全部都是闲的~

有兴趣的话赶紧尝试一下吧~

随机显示文章

2010年05月25日 -- 我的生活品质 (7)
2009年09月29日 -- 加了几个统计插件 (0)
2009年10月26日 -- MySQL里执行SHOW INDEX结果中Cardinality的含义 (1)

如果可以，我们一起留在宜春

P.Linux — Mon, 06 Jun 2011 21:26:50 +0800

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/life/feeling/love_hometown_yichun.html

如果可以，我们一起留在宜春

不要那些所谓的理想不要那些所谓的奋斗

不想去英国、美国，读书留学不想去上海、北京，打拼奋斗

就一起留在我们熟悉的城市

每一条街道都能叫出名字每一个邻居都认识

想打个麻将，唱个歌

一个电话，半个小时

人就聚齐了

如果可以，我们一起留在宜春

无聊了一起去南昌玩一趟

开个车三四个小时就到江西首府了

过年过节几个朋友窜窜门吃顿饭

谁要是不来，打个车几分钟就到他家门口

直接拖出来

如果可以，我们一起留在宜春

嘴馋的时候

满大街吃美味的小吃

或者到鼓楼、到麻辣大王，吃个烧烤

水果出来的季节

到白马农庄摘草莓

都是一箱一箱的买

因为便宜又好吃

如果可以，我们一起留在宜春

周末的时候还能骑车满世界转悠

心血来潮就去明月山

不好就去袁山

找个野山，带着烧烤架

美滋美滋的自助烧烤

如果可以，我们一起留在宜春

冬天的时候一起堆雪人

夏天去秀江游个泳

累了就随便找个KTV、棋牌室、桌游社呼朋唤友

打打麻将，斗斗地主输赢都在这个圈子

每个人我们都熟悉知根知底

如果可以，我们一起留在宜春

等我们工作了

没有那么大的压力

不用天天加班到10点

不用没有节假日

不用周周出差

只要8点上班，5点下班

不想做饭了就找个哥们家蹭顿饭

饭后可以不用洗碗

还可以一起散散步

如果可以，我们一起留在宜春

看着朋友结婚，每个人的婚礼都能参加

等我们有了孩子

我们要让他们也天天在一起玩

让我们成为世交他们也成为世交

礼拜天领着他们去森林公园

他们看植物，我们看他们

让他干爹干妈一大堆

过年压岁钱多的拿不了

让他一出生就学普通话

而不是一出生周围就是不知道哪个地方的方言或者英语

如果可以，我们一起留在宜春

等我们老了可以天天有人陪着

走不动了还可以打麻将

商量着什么时候再去趟宜春中学

什么时候再爬趟明月山

什么时候再去鼓楼吃小吃

什么时候再……

把年轻的事情都再做一遍

如果……如果……如果可以……

随机显示文章

2011年01月19日 -- MySQL小技巧问答(一) (0)
2009年05月24日 -- RedHat Enterprise Linux在IBM System x服务器上的调优 (0)
2009年12月8日 -- Nginx的502错误 (0)

MySQL中创建及优化索引组织结构的思路

P.Linux — Thu, 02 Jun 2011 16:22:26 +0800

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/database/think_about_mysql_create_and_optimize_index.html

原文链接：http://www.mysqlops.com/2011/05/23/mysql%E4%B8%AD%E5%88%9B%E5%BB%BA%E5%8F%8A%E4%BC%98%E5%8C%96%E7%B4%A2%E5%BC%95%E7%BB%84%E7%BB%87%E7%BB%93%E6%9E%84%E7%9A%84%E6%80%9D%E8%B7%AF.html

【导读】
通过一个实际生产环境中的数据存取需求，分析如何设计此存储结构，如何操纵存储的数据，以及如何使操作的成本或代价更低，系统开销最小。同时，让更多初学者明白数据存储的表上索引是如何一个思路组织起来的，希望起到一个参考模板的价值作用。

测试用例描述
测试用例为B2C领域，一张用于存储用户选购物品而生成的产品订单信息表，不过去掉一些其他字段，以便用于测试，其表中的数据项也不特别描述，字段意思见表

USE `test`;
DROP TABLE IF EXISTS `test`.`goods_order`;
CREATE TABLE `goods_order`(
`order_id`        INT UNSIGNED      NOT NULL             COMMENT ‘订单单号’,
`goods_id`        INT UNSIGNED      NOT NULL DEFAULT ’0′ COMMENT ‘商品款号’,
`order_type`      TINYINT UNSIGNED  NOT NULL DEFAULT ’0′ COMMENT ‘订单类型’,
`order_status`    TINYINT UNSIGNED  NOT NULL DEFAULT ’0′ COMMENT ‘订单状态’,
`color_id`        SMALLINT  UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘颜色id’,
`size_id`         SMALLINT  UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘尺寸id’,
`goods_number`    MEDIUMINT  UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘数量’,
`depot_id`        INT UNSIGNED  NOT NULL DEFAULT ’0′ COMMENT ‘仓库id’,
`packet_id`       INT UNSIGNED  NOT NULL DEFAULT ’0′ COMMENT ‘储位code’,
`gmt_create`      TIMESTAMP     NOT NULL DEFAULT ’0000-00-00 00:00:00′ COMMENT ‘添加时间’,
`gmt_modify`      TIMESTAMP     NOT NULL DEFAULT ’0000-00-00 00:00:00′ COMMENT ‘更新时间’,
PRIMARY KEY(order_id,`goods_id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET ‘utf8′ COLLATE ‘utf8_general_ci’;

其中，主键信息：PRIMARY KEY(order_id,`goods_id`)，为何主键索引索引字段的顺序为：order_id,`goods_id`，而不是： `goods_id`, order_id呢？原因很简单，goods_id在订单信息表中的重复率会比order_id高，也即order_id的筛选率更高，可以减少扫描索引记录个数，从而达到更高的效率，同时，下面即将会列出的SQL也告诉我们，有部分SQL语句的WHERE字句中只出现order_id字段，为此更加坚定我们必须把字段：order_id作为联合主键索引的头部，`goods_id`为联合主键索引的尾部。

数据存储表设计的小结：
设计用于存储数据的表结构，首先要知道有哪些数据项，也即行内常说的数据流，以及各个数据项的属性，比如存储的数据类型、值域范围及长度、数据完整性等要求，从而确定数据项的属性定义。存储的数据项信息确定之后，至少进行如下三步分析：
l 首先，确定哪些数据项或组合，可以作为记录的唯一性标志；
l 其次，要确定对数据记录有哪些操作，每个操作的频率如何，对网站等类型应用，还需要区分前台操作和后台操作，也即分外部用户的操作，还是内部用户的操作；
l 最后，对作为数据记录操作的条件部分的数据项，分析其数据项的筛选率如何，也即数据项不同值占总数据记录数的比例关心，比例越接近1则是筛选率越好，以及各个值得分布率；
综上所述，再让数据修改性操作优先级别高于只读性操作，就可以创建一个满足要求且性能较好的索引组织结构。
数据的存取设计,就涉及一块非常重要的知识: 关系数据库的基础知识和关系数据理论的范式。对于范式的知识点，特别解释下，建议学到BCNF范式为止，1NF、2NF、3NF和BCNF之间的差别，各自规避的问题、存在的缺陷都要一清二楚，但是在真实的工作环境中，不要任何存取设计都想向范式靠，用一句佛语准确点表达：空即是色，色即是空。

用于生成测试数据的存储过程代码
创建索引，就离不开表存储的真实数据，为此编写一个存储过程近可能模拟真实生产环境中的数据，同时也方便大家使用此存储过程，在自己的测试环境中，真实感受验证，
存储过程代码：

DELIMITER $$
DROP PROCEDURE IF EXISTS `usp_make_data` $$
CREATE PROCEDURE `usp_make_data`()
BEGIN
DECLARE iv_goods_id INT UNSIGNED DEFAULT 0;
DECLARE iv_depot_id INT UNSIGNED DEFAULT 0;
DECLARE iv_packet_id INT UNSIGNED DEFAULT 0;
 
SET iv_goods_id=5000;
SET iv_depot_id=10;
SET iv_packet_id=20;
 
WHILE iv_goods_id&gt;0
DO
START  TRANSACTION;
WHILE iv_depot_id&gt;0
DO
WHILE iv_packet_id&gt;0
DO
INSERT INTO goods_order(order_id,goods_id,order_type,order_status,color_id,size_id,goods_number,depot_id,packet_id,gmt_create,gmt_modify)
VALUES(SUBSTRING(RAND(),3,8),iv_goods_id,SUBSTRING(RAND(),3,1),SUBSTRING(RAND(),5,1)%2,SUBSTRING(RAND(),3,3),SUBSTRING(RAND(),4,3),SUBSTRING(RAND(),5,2),
iv_depot_id,SUBSTRING(RAND(),4,2)*iv_packet_id,DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),2,3) DAY),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) DAY)
);
SET iv_packet_id=iv_packet_id-1;
END WHILE;
SET iv_packet_id=20;
SET iv_depot_id=iv_depot_id-1;
END WHILE ;
 
COMMIT;
SET iv_depot_id=10;
SET iv_goods_id=iv_goods_id-1;
END WHILE ;
END $$
DELIMITER ;

业务逻辑描述
l 非注册用户，或网站的注册用户不登陆，都能可选购买物品，生成订单号对应的用户UID为系统默认的；
l 订单与用户UID关联、描述等信息，存储其它的表中，通过订单号的模式关联；
l 用户的订单信息，在未付款之前都可以再修改，付款之后则无法修改；
l 已经付费的订单信息，自动发送到物流部门，进行后续工序的操作。处理完毕之后，会更新订单中涉及物品的存储位置信息；
l 定期读取部分数据到数据仓库分析系统，用于统计分析；
l 个人订单查询，前后台都有；
l 购物记录查询显示；

根据业务规则描述需要使用操纵数据的SQL语句
(1). EXPLAIN SELECT * FROM goods_order WHERE `order_id`=40918986;
(2). SELECT * FROM goods_order WHERE `order_id` IN (40918986,40717328,30923040…) ORDER BY gmt_modify DESC;
(3). UPDATE goods_order SET gmt_modify=NOW(),…. WHERE `order_id`=40717328 AND goods_id=4248;
(4). SELECT COUNT(*) FROM goods_order WHERE depot_id=0 ORDER BY gmt_modify DESC LIMIT 0,50;
(5). SELECT * FROM goods_order WHERE depot_id=6 AND packet_id=0 ORDER BY gmt_modify DESC LIMIT 0,50;
(6). SELECT COUNT(*) FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1
(7). SELECT * FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1 ORDER BY gmt_modify DESC LIMIT 0,50;
(8). SELECT * FROM goods_order WHERE gmt_modify>=’ 2011-04-06’;
8条SQL语句按触发其执行的用户分类：
l 前台用户点击触发的操作而会执行的SQL语句为：(1)、(2)、(3)；
l 后台内部用户点击触发的操作而会执行的SQL语句为：(1)、(2)、(3)、(4)、(5)、(6)、(7)；
l 后台系统自动定期执行：(4)、(5)、(6)、(7)，工作时间正常情况每隔15分钟执行一次，以检查是否有已付款而没有准备货物的订单、是否有收款而未发货的订单等;
l 统计分析系统定期导出数据而执行的SQL语句为：(8)，频率为每24小时一次；
我们再分析上述列出来的SQL，分为2类，一类是读操作的SQL（备注：SELECT操作），另外一类为修改性操作（备注：UPDATE、DELETE操作），分别如下：
SELECT 的WHERE子句、GROUP BY子、ORDER BY 子句和HAVING 子句中，出现的字段：
(1). order_id
(2). order_id+gmt_modify
(3). depot_id+gmt_modify
(4). depot_id+packet_id+gmt_modify
(5). goods_id+order_status+order_type
(6). goods_id+order_status+order_type+gmt_modify
(7). gmt_modify
修改性操作的WHERE子句中出现的条件字段：
(8). order_id+ goods_id

我们已经存在主键索引：PRIMARY KEY(order_id,`goods_id`)，另外考虑到此表数据的操作以SELECT和INSERT为主，UPDATE的SQL量其次，再根据上述SQL语句，为此我们可以初步确定需要创建的索引：
ALTER TABLE goods_order
ADD INDEX idx_goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify),
ADD INDEX idx_depotID_packetID_gmtmodify(depot_id,packet_id,gmt_modify);

总结：
文章中也分析了为何联合主键索引的顺序为：order_id,`goods_id`，再补充下作为主键的联合索引的字段属性的其他特性：字段值写入之后不变化、字段值长度短且最好为数值类型；
对于编号SQL：(8)，每天按更新日期读取一次数据的操作，以采用全表扫描的方式实现，牺牲其数据读取的性能，以减少更新字段修改日期的值而带来的索引维护开销；
对于编号SQL：(4)、(5)，考虑到每次都是读取最新的５０条记录，以及读取的数据基本上可肯定为热数据，为此不得不牺牲其中一条SQL的数据读取性能，而少创建一个联合索引，从而减少维护索引字段的IO量；
对于编号SQL：(6)、(7)，创建的联合索引，需要特别注意联合索引：idx_goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify)中的字段顺序，其中：
l goods_id字段的筛选率高于order_type,order_status，另外gmt_modify字段只出现在ORDER BY子句中，为此只有让goods_id字段作为联合索引的头部，以提高索引的筛选率，从而提高索引的效率，减少逻辑或物理的读。
l order_status字段只有0或1两种值，而order_type有多种，以及根据SQL语句，必须order_type出现在联合中的位置要比order_status靠近头部；
l gmt_modify字段出现在ORDER BY子句中，为此必须放到联合索引字段的最后；

最后，再梳理一下从需求到设计存储结构，再到编写SQL和创建索引结构，我们应该做的步骤：
l 整理业务产生的数据流，读取数据的方式；
l 整理清楚数据流中的每个数据项属性信息；
l 分析业务指标，推测需要存储数据的规模（备注：一定要以多少GB作为容量单位）；
l 选择可能用于支持业务的硬件设备和数据库架构；
l 把所有可能操纵数据的条件和操作类型，都整理清楚；
l 分析操纵数据条件字段各自的数据筛选率；
l 权衡各个SQL的性能和IO量，也即类似于哪个操作权重高一些，那些操作权重适当低一些；
l 创建索引组织结构；
l 收集测试和生产环境的反馈信息，优化索引组织结构；

备注：
本想再用测试环境结合业务的方式，跑一套模拟测试脚本程序，让大家更加直观地看到不同索引组织情况下，相同的SQL操作及频率，数据库服务器的处理能力和负载变化及对比信息，可惜唯一的服务器无法使用了，只好放弃。对于分析相同的SQL，走不通索引，其需要的逻辑IO和物理IO量也是一个办法，此次就不分析了，有需要的朋友可以去玩玩，另外建议初学者一定要好好阅读下mysql 手册上的相关章节内容：7.2.6. Index Merge Optimization。

类似的文章

2010年03月20日 -- MySQL索引与存储方式对性能的影响 (2)
2009年12月21日 -- 给MySQL做虚拟的“函数索引” (0)
2009年11月10日 -- MySQL索引检查脚本预览 (0)

MySQL删除大表更快的DROP TABLE办法

P.Linux — Thu, 02 Jun 2011 16:07:12 +0800

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/database/mysql_fast_drop_table_use_hard_lin.html

原文地址：http://www.mysqlops.com/2011/05/18/mysql%E5%88%A0%E9%99%A4%E5%A4%A7%E8%A1%A8%E6%9B%B4%E5%BF%AB%E7%9A%84drop-table%E5%8A%9E%E6%B3%95.html

曾经发文介绍过，DROP table XXX ,特别是碰到大表时，
http://www.mysqlops.com/2011/02/18/mysql-drop-table-%e5%a4%84%e7%90%86%e8%bf%87%e7%a8%8b.html
在DROP TABLE 过程中，所有操作都会被HANG住。
这是因为INNODB会维护一个全局独占锁（在table cache上面），直到DROP TABLE完成才释放。
在我们常用的ext3,ext4，ntfs文件系统，要删除一个大文件（几十G，甚至几百G）还是需要点时间的。
下面我们介绍一个快速DROP table 的方法；不管多大的表,INNODB 都可以很快返回，表删除完成；
实现：巧用LINK（硬链接）

实测：

root@127.0.0.1 : test 21:38:00> show table status like ‘tt’ \G
*************************** 1. row ***************************
Name: tt
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 151789128
Avg_row_length: 72
Data_length: 11011096576
Max_data_length: 0
Index_length: 5206179840
Data_free: 7340032
Auto_increment: NULL
Create_time: 2011-05-18 14:55:08
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.22 sec)

root@127.0.0.1 : test 21:39:34> drop table tt ;
Query OK, 0 rows affected (25.01 sec)

删除一个11G的表用时25秒左右（硬件不同，时间不同）；

下面我们来对另一个更大的表进行删除；
但之前，我们需要对这个表的数据文件做一个硬连接：

root@ # ln stock.ibd stock.id.hdlk
root@ # ls stock.* -l
-rw-rw—- 1 mysql mysql 9196 Apr 14 23:03 stock.frm
-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.ibd
-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.id.hdlk

你会发现stock.ibd的INODES属性变成了2；

下面我们继续来删表。

root@127.0.0.1 : test 21:44:37> show table status like ‘stock’ \G
*************************** 1. row ***************************
Name: stock
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 49916863
Avg_row_length: 356
Data_length: 17799577600
Max_data_length: 0
Index_length: 1025507328
Data_free: 4194304
Auto_increment: NULL
Create_time: 2011-05-18 14:55:08
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.23 sec)

root@127.0.0.1 : test 21:39:34> drop table stock ;
Query OK, 0 rows affected (0.99 sec)

1秒不到就删除完成；也就是DROP TABLE不用再HANG这么久了。
但table是删除了，数据文件还在，所以你还需要最后数据文件给删除。

root # ll
total 19096666112
-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.id.hdlk
root # rm stock.id.hdlk
虽然DROP TABLE 多绕了几步。(如果你有一个比较可靠的自运行程序（自动为大表建立硬链接，并会自动删除过期的硬链接文件），就会显得不那么繁琐。)
这样做能大大减少MYSQL HANG住的时间；相信还是值得的。

至于原理: 就是利用OS HARD LINK的原理,
当多个文件名同时指向同一个INODE时,这个INODE的引用数N>1, 删除其中任何一个文件名都会很快.
因为其直接的物理文件块没有被删除.只是删除了一个指针而已;
当INODE的引用数N=1时, 删除文件需要去把这个文件相关的所有数据块清除,所以会比较耗时;

好了. 大家试试吧.

类似的文章

2012年02月14日 -- 自己动手实现Multi-Master Replication (0)
2012年01月23日 -- 一个InnoDB性能超过Oracle的调优Case (1)
2011年12月23日 -- 在Server层实现Kill Idle Transaction (1)

自编译MySQL指南 2.0

P.Linux — Wed, 13 Apr 2011 14:57:42 +0800

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/database/mysql_compile_reference.html

原文：http://www.mysqlops.com/2011/03/06/mysql_compile_reference.html

一般情况下，用户选择的MySQL安装方式为 RPM包或二进制压缩包，但是，通用安装包为了适应不同的软硬件平台，都会采用保守的编译方式，功能上也是选择最常用最稳定的功能编译入二进制版本。
虽然这满足了大部分用户的需求，但是有时我们仅仅需要一部分功能（例如我们不需要Query Cache，但这个模块编译时不去掉的话，运行时依然会触发其代码清理Query Cache内存池，并引发过Bug），或者有性能更好的商业编译器（例如ICC），或者对源码做了修改时，就必须采用编译的方式来安装了。

下面我们就来介绍下如何从源码编译安装MySQL。

第一部分，选择编译参数
编译MySQL需要设置两种编译参数：GCC/ICC的编译参数，MySQL的编译参数。GCC/ICC编译参数是控制编译时编译器的优化动作，MySQL编译参数是控制MySQL功能模块的处理动作。

以Xeon 5520为例，55系列是Intel的Nehalem架构处理器，为了充分挖掘它的处理能力，我们做了很多的测试来尝试一些GCC的编译参数，如何获得更高的MySQL性能。

首先看处理器支持哪些flags：

processor : 15
vendor_id : GenuineIntel
cpu family : 6
model : 26
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
stepping : 5
cpu MHz : 2261.088
cache size : 8192 KB
physical id : 0
siblings : 8
core id : 3
cpu cores : 4
apicid : 7
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc ida pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lm
bogomips : 4521.98
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

从cupinfo的信息可以看出，支持sse/sse2/mmx这些GCC的flag，查看gcc的文档可以看到全部的优化选项：[点我查看]。

经过尝试，得到了如下编译参数，经过两周的压力测试，编译MySQL 5.1.46sp1企业版，比Percona 5.1.47 Server性能高出15%，目前也非常稳定的运行在开发测试库上。GCC版本为4.1.3，系统为RHEL 5.4 x64.

CXX=gcc \
CHOST=”x86_64-pc-linux-gnu” \
CFLAGS=” -O3 \
-fomit-frame-pointer \
-pipe \
-march=nocona \
-mfpmath=sse \
-m128bit-long-double \
-mmmx \
-msse \
-msse2 \
-maccumulate-outgoing-args \
-m64 \
-ftree-loop-linear \
-fprefetch-loop-arrays \
-freg-struct-return \
-fgcse-sm \
-fgcse-las \
-frename-registers \
-fforce-addr \
-fivopts \
-ftree-vectorize \
-ftracer \
-frename-registers \
-minline-all-stringops \
-fbranch-target-load-optimize2″ \
CXXFLAGS=”${CFLAGS}” \
./configure –prefix=/usr/soft/install/mysql-ent-official-5.1.56 \
–with-server-suffix=custom-mysql \
–with-mysqld-user=mysql \
–with-plugins=partition,blackhole,csv,heap,innobase,myisam,myisammrg \
–with-charset=utf8 \
–with-collation=utf8_general_ci \
–with-extra-charsets=gbk,gb2312,utf8,ascii \
–with-big-tables \
–with-fast-mutexes \
–with-zlib-dir=bundled \
–enable-assembler \
–enable-profiling \
–enable-local-infile \
–enable-thread-safe-client \
–with-readline \
–with-pthread \
–with-embedded-server \
–with-client-ldflags=-all-static \
–with-mysqld-ldflags=-all-static \
–without-query-cache \
–without-geometry \
–without-debug \
–without-ndb-debug

GCC参数的含义为：
-fomit-frame-pointer
对于不需要栈指针的函数就不在寄存器中保存指针，因此可以忽略存储和检索地址的代码，同时对许多函数提供一个额外的寄存器。所有”-O”级别都打开它，但仅在调试器可以不依靠栈指针运行时才有效。在AMD64平台上此选项默认打开，但是在x86平台上则默认关闭。建议显式的设置它。
-pipe
在编译过程的不同阶段之间使用管道而非临时文件进行通信，可以加快编译速度。建议使用。
-march=nocona
Xoen 55xx处理器在GCC 4.1.3
-mfpmath=sse
启用cpu支持”sse”标量浮点指令。
m128bit-long-double
指定long double为128位，pentium以上的cpu更喜欢这种标准，并且符合x86-64的ABI标准，但是却不附合i386的ABI标准。
-mmmx -msse -msse2
使用相应的扩展指令集以及内置函数
-maccumulate-outgoing-args
指定在函数引导段中计算输出参数所需最大空间，这在大部分现代cpu中是较快的方法；缺点是会明显增加二进制文件尺寸。
-m64
生成专门运行于64位环境的代码，不能运行于32位环境，仅用于x86_64[含EMT64]环境。
-ftree-loop-linear
在trees上进行线型循环转换。它能够改进缓冲性能并且允许进行更进一步的循环优化。
-fprefetch-loop-arrays
生成数组预读取指令，对于使用巨大数组的程序可以加快代码执行速度，适合数据库相关的大型软件等。具体效果如何取决于代码。
-freg-struct-return
如果struct和union足够小就通过寄存器返回，这将提高较小结构的效率。如果不够小，无法容纳在一个寄存器中，将使用内存返回。建议仅在完全使用GCC编译的系统上才使用。
-fgcse-sm
在全局公共子表达式消除之后运行存储移动，以试图将存储移出循环。
-fgcse-las
在全局公共子表达式消除之后消除多余的在存储到同一存储区域之后的加载操作。
-frename-registers \
-fforce-addr
必须将地址复制到寄存器中才能对他们进行运算。由于所需地址通常在前面已经加载到寄存器中了，所以这个选项可以改进代码。
-fivopts
在trees上执行归纳变量优化。
-ftree-vectorize
在trees上执行循环向量化。
-ftracer
执行尾部复制以扩大超级块的尺寸，它简化了函数控制流，从而允许其它的优化措施做的更好。
-frename-registers
试图驱除代码中的假依赖关系，这个选项对具有大量寄存器的机器很有效。
-minline-all-stringops
默认时GCC只将确定目的地会被对齐在至少4字节边界的字符串操作内联进程序代码。该选项启用更多的内联并且增加二进制文件的体积，但是可以提升依赖于高速 memcpy, strlen, memset 操作的程序的性能。数据库系统使用这个参数可以显著提高内存操作性能。
-fbranch-target-load-optimize2
在执行序启动以及结尾之前执行分支目标缓存器加载最佳化。

第二部分，使用TC-Malloc内存管理：
Linux下的malloc函数性能问题，想必大部分在Linux下写C的人都深有感受，纷纷利用内存池来改进内存分配效率。
Google开源的tcmalloc则改进了malloc的一些效率问题，在大量malloc和free时，操作系统的内存曲线明显比Linux下malloc函数要平稳，在大并发情况下，提升程序稳定性和性能。
一般网上都是把tcmalloc动态库加到mysqld_safe中启动，但是我们的MySQL都是静态编译的，这时候动态加载是否生效呢？所以还是静态编译入MySQL好。

编译tcmalloc先要编译libunwind：

wget http://download.savannah.gnu.org/releases/libunwind/libunwind-0.99.tar.gz
tar zxvf libunwind-0.99.tar.gz

CHOST=”x86_64-pc-linux-gnu” \
CFLAGS=” -O3 -fPIC \
-fomit-frame-pointer \
-pipe \
-march=nocona \
-mfpmath=sse \
-m128bit-long-double \
-mmmx \
-msse \
-msse2 \
-maccumulate-outgoing-args \
-m64 \
-ftree-loop-linear \
-fprefetch-loop-arrays \
-freg-struct-return \
-fgcse-sm \
-fgcse-las \
-frename-registers \
-fforce-addr \
-fivopts \
-ftree-vectorize \
-ftracer \
-frename-registers \
-minline-all-stringops \
-fbranch-target-load-optimize2″ \
CXXFLAGS=”${CFLAGS}” \
./configure && make && make install

然后编译tcmalloc：

tar zxvf google-perftools-1.7.tar.gz

CHOST=”x86_64-pc-linux-gnu” \
CFLAGS=” -O3 \
-fomit-frame-pointer \
-pipe \
-march=nocona \
-mfpmath=sse \
-m128bit-long-double \
-mmmx \
-msse \
-msse2 \
-maccumulate-outgoing-args \
-m64 \
-ftree-loop-linear \
-fprefetch-loop-arrays \
-freg-struct-return \
-fgcse-sm \
-fgcse-las \
-frename-registers \
-fforce-addr \
-fivopts \
-ftree-vectorize \
-ftracer \
-frename-registers \
-minline-all-stringops \
-fbranch-target-load-optimize2″ \
CXXFLAGS=”${CFLAGS}” \
./configure –disable-cpu-profiler \
–disable-heap-profiler \
–disable-heap-checker \
–disable-debugalloc \
–enable-minimal \
–enable-frame-pointers && make && make install

记得要把libtammloc加入系统路径，否则编译MySQL时找不到：

echo “/usr/local/lib” > /etc/ld.so.conf.d/usr_local_lib.conf
/sbin/ldconfig

最后就是编译MySQL了：

CXX=gcc \
CHOST=”x86_64-pc-linux-gnu” \
CFLAGS=” -O3 \
-fomit-frame-pointer \
-pipe \
-march=nocona \
-mfpmath=sse \
-m128bit-long-double \
-mmmx \
-msse \
-msse2 \
-maccumulate-outgoing-args \
-m64 \
-ftree-loop-linear \
-fprefetch-loop-arrays \
-freg-struct-return \
-fgcse-sm \
-fgcse-las \
-frename-registers \
-fforce-addr \
-fivopts \
-ftree-vectorize \
-ftracer \
-frename-registers \
-minline-all-stringops \
-felide-constructors \
-fno-exceptions \
-fno-rtti \
-fbranch-target-load-optimize2″ \
CXXFLAGS=”${CFLAGS}” \
LDFLAGS=” -lrt -lunwind -ltcmalloc_minimal -lstdc++ ” \
./configure –prefix=/usr/soft/install/mysql-ent-custom-5.1.49sp1 \
–with-server-suffix=-custom-edition \
–with-mysqld-user=mysql \
–with-plugins=partition,blackhole,csv,heap,innobase,myisam,myisammrg \
–with-charset=utf8 \
–with-collation=utf8_general_ci \
–with-extra-charsets=gbk,gb2312,utf8,ascii \
–with-big-tables \
–with-fast-mutexes \
–with-zlib-dir=bundled \
–enable-assembler \
–enable-profiling \
–enable-local-infile \
–enable-thread-safe-client \
–with-readline \
–with-pthread \
–with-embedded-server \
–with-mysqld-ldflags=-all-static \
–without-query-cache \
–without-geometry \
–without-debug \
–without-ndb-debug
make && make install

经过试用，大并发下内存分配和释放曲线都比Linux原生的平稳。

第三部分，尝试ICC：
ICC是Intel自己开发的多平台编译器，经过我的测试ICC在浮点运算，线程库和数学函数上的优势非常明显，原生SSE2指令集支持、Intel自己编写的线程库和数学函数库，性能没得说。
我用同一份运算PI值的代码在ICC和GCC下编译，提升比例达20%，实际在数据库中比较同一条超级复杂的聚合SQL，ICC提升达34%。
下面给出TC-Malloc + ICC + Percona从源码编译的完整方案。

第一步：编译安装libunwind

wget http://download.savannah.gnu.org/releases/libunwind/libunwind-0.99.tar.gz
tar zxvf libunwind-0.99.tar.gz

CC=icc \
CXX=icpc \
LD=xild \
AR=xiar \
CFLAGS=”-O3 -no-prec-div -ip -fp-model fast=1 -xSSE2 -axSSE2 -fPIC” \
CXXFLAGS=”${CFLAGS}” \
CPPFLAGS=” -I/usr/alibaba/icc/include ” \
./configure && make && make install

第二布：编译安装tcmalloc

wget http://google-perftools.googlecode.com/files/google-perftools-1.7.tar.gz
tar zxvf google-perftools-1.7.tar.gz

CC=icc \
CXX=icpc \
LD=xild \
AR=xiar \
CFLAGS=”-O3 -no-prec-div -ip -fp-model fast=1 -xSSE2 -axSSE2 -fPIC” \
CXXFLAGS=”${CFLAGS}” \
CPPFLAGS=” -I/usr/alibaba/icc/include ” \
./configure \
–disable-cpu-profiler \
–disable-heap-profiler \
–disable-heap-checker \
–disable-debugalloc \
–enable-minimal \
–enable-frame-pointers && make && make install

echo “/usr/local/lib” > /etc/ld.so.conf.d/usr_local_lib.conf
/sbin/ldconfig

第三部：编译安装Percona

CC=icc \
CXX=icpc \
LD=xild \
AR=xiar \
CFLAGS=”-O3 -fno-builtin-malloc -fno-builtin-calloc -fno-builtin-realloc -fno-builtin-free -unroll2 -ip -fp-model fast=1 -restrict -fno-exceptions -fno-rtti -no-prec-div -fno-implicit-templates -static-intel -static-libgcc -static -xSSE2 -axSSE2″ \
CXXFLAGS=”${CFLAGS}” \
CPPFLAGS=” -I/usr/alibaba/icc/include ” \
LDFLAGS=” -L/usr/alibaba/icc/lib/intel64/ -lrt -lunwind -ltcmalloc_minimal -lstdc++ ” \
./configure –prefix=/usr/alibaba/install/percona-custom-5.1.55-12.6 \
–with-server-suffix=-alibaba-edition \
–with-mysqld-user=mysql \
–with-plugins=heap,innodb_plugin,myisam,partition \
–with-charset=utf8 \
–with-collation=utf8_general_ci \
–with-extra-charsets=gbk,utf8,ascii \
–with-big-tables \
–with-fast-mutexes \
–with-zlib-dir=bundled \
–with-readline \
–with-pthread \
–enable-assembler \
–enable-profiling \
–enable-local-infile \
–enable-thread-safe-client \
–without-embedded-server \
–with-mysqld-ldflags=-all-static \
–without-query-cache \
–without-geometry \
–without-debug \
–without-ndb-binlog \
–without-ndb-debug

编译完成后make && make install

类似的文章

2012年02月14日 -- 自己动手实现Multi-Master Replication (0)
2012年01月23日 -- 一个InnoDB性能超过Oracle的调优Case (1)
2011年12月23日 -- 在Server层实现Kill Idle Transaction (1)