performance

MySQL AWR – Myawr

May 6, 2013 Architecture, MYSQL, performance No comments

noodba同学写的基于mysql的 WORKLOAD REPOSITORY工具,目前还没有开源 非常的给力!!

主要思想为一台管理机器,集中了所有client端的数据,client 为mysql database 使用host_id来区分 如 host_id=1代表mysql database 1,host_id=2 代表mysql database 2.

具体采集AWR过程如下:

[mysql@hadooptest2 myawr]$ perl myawrrpt.pl 

==========================================================================================
Info  :
        Created By qwsh (www.noodba.com).
  		References: Oracle awr
Usage :
Command line options :

   -h,--help           Print Help Info. 
  
   -P,--port           Port number to use for local mysql connection(default 3306).
   -u,--user           user name for local mysql(default dbauser).
   -p,--pswd           user password for local mysql(can't be null).
   -lh,--lhost          localhost(ip) for mysql where info is got(can't be null).

   -I,--tid             db instance register id(can't be null).    
  
Sample :
   shell> perl myawrrpt.pl -p 111111 -lh 192.168.1.111 -I 11
==========================================================================================

[mysql@hadooptest2 myawr]$ perl myawrrpt.pl -u myawruser -p xxxxx -P xxxxx -lh 10.0.1.92 -I 4
===================================================
|       Welcome to use the myawrrpt tool !   
|             Date: 2013-05-06
|
|      Hostname is: pis24 
|       Ip addr is: 10.0.2.24 
|          Port is: 3306 
|       Db role is: master 
|Server version is: 5.5.25a
|        Uptime is: 0y 3m 15d 2h 10mi 41s
|
|   Min snap_id is: 1 
| Min snap_time is: 2013-05-03 10:41:41 
|   Max snap_id is: 4546 
| Max snap_time is: 2013-05-06 14:33:11 
| snap interval is: 59s
===================================================

Listing the last 2 days of Completed Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
snap_id:     792      snap_time : 2013-05-03 23:59:06 
snap_id:     855      snap_time : 2013-05-04 01:02:05 
snap_id:     918      snap_time : 2013-05-04 02:05:06 
snap_id:     981      snap_time : 2013-05-04 03:08:06 
snap_id:    1044      snap_time : 2013-05-04 04:11:06 
snap_id:    1107      snap_time : 2013-05-04 05:14:06 
snap_id:    1170      snap_time : 2013-05-04 06:17:06 
snap_id:    1233      snap_time : 2013-05-04 07:20:06 
snap_id:    1296      snap_time : 2013-05-04 08:23:06 
snap_id:    1359      snap_time : 2013-05-04 09:26:07 
snap_id:    1422      snap_time : 2013-05-04 10:29:06 
snap_id:    1485      snap_time : 2013-05-04 11:32:07 
snap_id:    1548      snap_time : 2013-05-04 12:35:06 
snap_id:    1611      snap_time : 2013-05-04 13:38:07 
snap_id:    1674      snap_time : 2013-05-04 14:41:07 
snap_id:    1737      snap_time : 2013-05-04 15:44:07 
snap_id:    1800      snap_time : 2013-05-04 16:47:07 
snap_id:    1863      snap_time : 2013-05-04 17:50:08 
snap_id:    1926      snap_time : 2013-05-04 18:53:08 
snap_id:    1989      snap_time : 2013-05-04 19:56:08 
snap_id:    2052      snap_time : 2013-05-04 20:59:08 
snap_id:    2115      snap_time : 2013-05-04 22:02:07 
snap_id:    2178      snap_time : 2013-05-04 23:05:08 
snap_id:    2241      snap_time : 2013-05-05 00:08:08 
snap_id:    2304      snap_time : 2013-05-05 01:11:08 
snap_id:    2367      snap_time : 2013-05-05 02:14:08 
snap_id:    2430      snap_time : 2013-05-05 03:17:08 
snap_id:    2493      snap_time : 2013-05-05 04:20:08 
snap_id:    2556      snap_time : 2013-05-05 05:23:08 
snap_id:    2619      snap_time : 2013-05-05 06:26:08 
snap_id:    2682      snap_time : 2013-05-05 07:29:09 
snap_id:    2745      snap_time : 2013-05-05 08:32:09 
snap_id:    2808      snap_time : 2013-05-05 09:35:09 
snap_id:    2871      snap_time : 2013-05-05 10:38:09 
snap_id:    2934      snap_time : 2013-05-05 11:41:09 
snap_id:    2997      snap_time : 2013-05-05 12:44:09 
snap_id:    3060      snap_time : 2013-05-05 13:47:09 
snap_id:    3123      snap_time : 2013-05-05 14:50:09 
snap_id:    3186      snap_time : 2013-05-05 15:53:10 
snap_id:    3249      snap_time : 2013-05-05 16:56:10 
snap_id:    3312      snap_time : 2013-05-05 17:59:10 
snap_id:    3375      snap_time : 2013-05-05 19:02:10 
snap_id:    3438      snap_time : 2013-05-05 20:05:10 
snap_id:    3501      snap_time : 2013-05-05 21:08:10 
snap_id:    3564      snap_time : 2013-05-05 22:11:10 
snap_id:    3627      snap_time : 2013-05-05 23:14:10 
snap_id:    3690      snap_time : 2013-05-06 00:17:11 
snap_id:    3753      snap_time : 2013-05-06 01:20:11 
snap_id:    3816      snap_time : 2013-05-06 02:23:11 
snap_id:    3879      snap_time : 2013-05-06 03:26:10 
snap_id:    3942      snap_time : 2013-05-06 04:29:11 
snap_id:    4005      snap_time : 2013-05-06 05:32:11 
snap_id:    4068      snap_time : 2013-05-06 06:35:11 
snap_id:    4131      snap_time : 2013-05-06 07:38:11 
snap_id:    4194      snap_time : 2013-05-06 08:41:12 
snap_id:    4257      snap_time : 2013-05-06 09:44:12 
snap_id:    4320      snap_time : 2013-05-06 10:47:11 
snap_id:    4383      snap_time : 2013-05-06 11:50:12 
snap_id:    4446      snap_time : 2013-05-06 12:53:11 
snap_id:    4509      snap_time : 2013-05-06 13:56:11 

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:1107
Begin Snapshot Id specified:1107

Enter value for end_snap:1863
End  Snapshot Id specified:1863

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~

Enter value for report_name:yihaodian_mysqlawr.html

Using the report name :yihaodian_mysqlawr.html

Generating the mysql report for this analysis ...
Generating the mysql report Successfully.

这套测试环境采用一台管理机器,表的配置如下:

mysql> use myawr
Database changed
mysql> show tables;
+----------------------------------------------------------+
| Tables_in_myawr                                          |
+----------------------------------------------------------+
| myawr_cpu_info                                           | 
| myawr_host                                               | 
| myawr_innodb_info                                        | 
| myawr_io_info                                            | 
| myawr_isam_info                                          | 
| myawr_load_info                                          | 
| myawr_mysql_info                                         | 
| myawr_query_review                                       | 
| myawr_query_review_history                               | 
| myawr_snapshot                                           | 
| myawr_snapshot_events_waits_summary_by_instance          | 
| myawr_snapshot_events_waits_summary_global_by_event_name | 
| myawr_snapshot_file_summary_by_event_name                | 
| myawr_snapshot_file_summary_by_instance                  | 
| myawr_swap_net_disk_info                                 | 
+----------------------------------------------------------+
15 rows in set (0.00 sec)

mysql> select * from myawr_host;
+----+-----------+-----------+------+---------+---------+-----------------------+---------------------+
| id | host_name | ip_addr   | port | db_role | version | uptime                | check_time          |
+----+-----------+-----------+------+---------+---------+-----------------------+---------------------+
|  3 | db-74     | 10.0.0.74 | 3306 | master  | 5.5.27  | 0y 1m 8d 22h 32mi 33s | 2013-05-03 13:09:02 | 
|  4 | pis24     | 10.0.2.24 | 3306 | master  | 5.5.25a | 0y 3m 15d 1h 46mi 41s | 2013-05-06 14:09:12 | 
+----+-----------+-----------+------+---------+---------+-----------------------+---------------------+
2 rows in set (0.00 sec)

可以看到host_id=3监控的机器为10.0.0.74 依此类推, 1…xxx 可以无限扩展。

下面为client 端部署的脚本:

[mysql@DCB-SRV-0220 ~]$ crontab -l
* * * * * perl /data/mysql/sh/myawr.pl -u dbauser -p xxxxx -lh 10.0.2.24 -P 3306 -tu myawruser -tp xxxxxx -TP 3306 -th 10.0.1.92 -n eth2 -d c0d1p1 -I 4 >> /data/mysql/sh/myawr_pl.log 2>&1

myawr会自动上传这台mysql机器的所有状态值到管理机器。
下面以10.0.2.24这台mysql 作为一个样本 展示一下AWR报告,目前版本为version 1.0 后期会在github上开源

Yihaodian_Mysql_AWR.

MySQL key partition and MongoDB TEST

March 8, 2013 mongodb, MYSQL, NoSQL, performance No comments

对于业务的激活码需求做了一次关于mysql,mongodb的比对.mysql分为normal,key partition 数量分别是1亿和10亿数据,mysql采用直接访问PK键,partition key为PK,mysql table size 为90G,mongodb table size为157G。

[liuyang@yhdem ~]$ cat /proc/cpuinfo  |grep processor |wc -l
24

[liuyang@yhdem ~]$ cat /etc/issue
Oracle Linux Server release 5.8
Kernel \r on an \m

mysql evn:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.25a   | 
+-----------+
1 row in set (0.00 sec)
      
      log_bin[OFF] innodb_flush_log_at_trx_commit [2]  query_cache_type[OFF]
      max_connect_errors[10] max_connections[214] max_user_connections[0] 
      sync_binlog[0] table_definition_cache[400] 
      table_open_cache[400] thread_cache_size[8]  open_files_limit[30000]
      innodb_adaptive_flushing[ON] innodb_adaptive_hash_index[ON] innodb_buffer_pool_size[30.234375G] 
      innodb_file_per_table[ON] innodb_flush_log_at_trx_commit[2] innodb_flush_method[] 
      innodb_io_capacity[200] innodb_lock_wait_timeout[100] innodb_log_buffer_size[128M] 
      innodb_log_file_size[200M] innodb_log_files_in_group[2] innodb_max_dirty_pages_pct[75] 
      innodb_open_files[1600] innodb_read_io_threads[4] innodb_thread_concurrency[0] 
      innodb_write_io_threads[4]

以下图片均为QPS统计,TPS测试暂时没有做

no partition table with one billion rows –> small random select by pk

mysql_test_1

xDiskName Busy  Read WriteKB|0          |25         |50          |75	   100|                                                                        
xsda        1%    2.0   35.9|>                                                |                                                                      
xsda1       0%    0.0    0.0|>                                                |                                                                      
xsda2       0%    0.0    0.0|>                                                |                                                                      
xsda3       0%    0.0    0.0|>                                                |                                                                      
xsda4       0%    0.0    0.0|>disk busy not available                         |                                                                      
xsda5       0%    0.0    0.0|>                                                |                                                                      
xsda6       1%    2.0   35.9|>                                                |                                                                      
xsdb        0%    0.0   55.9|>                                                |                                                                      
xsdb1       0%    0.0   55.9|>                                                |                                                                      
xTotals Read-MB/s=0.0      Writes-MB/s=0.2      Transfers/sec=18.0 

partition table with one billion rows –> small random select by pk

mysql_test_2

xDiskName Busy  Read WriteKB|0          |25         |50          |75	   100|                                                                       
xsda        0%    0.0    8.0|>                                                |                                                                     
xsda1       0%    0.0    0.0|>                                                |                                                                     
xsda2       0%    0.0    8.0|>                                                |                                                                     
xsda3       0%    0.0    0.0|>                                                |                                                                     
xsda4       0%    0.0    0.0|>disk busy not available                         |                                                                     
xsda5       0%    0.0    0.0|>                                                |                                                                     
xsda6       0%    0.0    0.0|>                                                |                                                                     
xsdb        0%    0.0  201.5|                         >                       |                                                                     
xsdb1       0%    0.0  201.5|W                        >                       |                                                                     
xTotals Read-MB/s=0.0      Writes-MB/s=0.4      Transfers/sec=46.9             

no partition table with one billion rows –> full random select by pk

mysql_test_3

xDiskName Busy  Read WriteMB|0          |25         |50          |75	   100|                                                                        
xsda        0%    0.0    0.0| >                                               |                                                                      
xsda1       0%    0.0    0.0|>                                                |                                                                      
xsda2       0%    0.0    0.0|>                                                |                                                                      
xsda3       0%    0.0    0.0|>                                                |                                                                      
xsda4       0%    0.0    0.0|>disk busy not available                         |                                                                      
xsda5       0%    0.0    0.0|>                                                |                                                                      
xsda6       0%    0.0    0.0| >                                               |                                                                      
xsdb      100%   86.8    0.2|RRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR>                                                                      
xsdb1     100%   86.8    0.2|RRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR>                                                                      
xTotals Read-MB/s=173.6    Writes-MB/s=0.4      Transfers/sec=6448.1    

partition table with one billion rows –> full random select by pk

mysql_test_4

xDiskName Busy  Read WriteMB|0          |25         |50          |75	   100|                                                                        
xsda        0%    0.0    0.0| >                                               |                                                                      
xsda1       0%    0.0    0.0|>                                                |                                                                      
xsda2       0%    0.0    0.0| >                                               |                                                                      
xsda3       0%    0.0    0.0|>                                                |                                                                      
xsda4       0%    0.0    0.0|>disk busy not available                         |                                                                      
xsda5       0%    0.0    0.0|>                                                |                                                                      
xsda6       0%    0.0    0.0| >                                               |                                                                      
xsdb      100%   89.6    0.2|RRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR>                                                                      
xsdb1     100%   89.6    0.2|                                                 >                                                                      
xTotals Read-MB/s=179.2    Writes-MB/s=0.3      Transfers/sec=6539.3        

no partition table with 100 million rows –> full random select by pk

mysql_test_5

下面基于mongodb的TEST.同样为10亿的表,157G.

[root@db-13 tmp]# mongo
MongoDB shell version: 2.0.8
connecting to: test
> db.foo.totalSize();
157875838416
> db.foo.find().count();
1000000000

——

第一次 使用128G 满额内存 16thread,10亿random query:

[root@db-13 tmp]# mongo test ./mongodb_benchmark_query.js 
MongoDB shell version: 2.0.8
connecting to: test
threads: 16      queries/sec: 126151.69666666667

第二次 使用128G 内存 24 thread,10亿中的前1亿数据random query:

[root@db-13 tmp]# mongo test ./mongodb_benchmark_query.js 
MongoDB shell version: 2.0.8
connecting to: test
threads: 24      queries/sec: 166527.42333333334

第三次 使用mysql用户启动mongo 限制mysql用户的mem为24G 24 thread , 10亿中的前1亿数据random query :

[mysql@db-13 ~]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 1052672
max locked memory       (kbytes, -l) 26055452
max memory size         (kbytes, -m) 26055452
open files                      (-n) 131072
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) unlimited
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

[mysql@db-13 tmp]$ mongo test ./mongodb_benchmark_query.js 
MongoDB shell version: 2.0.8
connecting to: test
threads: 24	 queries/sec: 161358.03333333333

第四次 使用mysql用户启动mongo 限制mysql用户的mem为24G 24 thread , 10亿random query :

[mysql@db-13 tmp]$ mongo test ./mongodb_benchmark_query.js 
MongoDB shell version: 2.0.8
connecting to: test
threads: 24	 queries/sec: 2549.2 ----------------------> 这里出现了物理IO读写

—提供查询脚本

ops = [{op: "findOne", ns: "test.foo", query: {_id : { "#RAND_INT" : [ 1 , 100000000 ] } }}]       
x=24
 {
    res = benchRun( {
       parallel : x ,
        seconds : 60 ,
        ops : ops
    } );
    print( "threads: " + x + "\t queries/sec: " + res.query );
}

10亿 normal table 对于1亿 normal table 在内存基于PK的访问没有衰减,10亿的partition table 对于 10亿的 normal table 在内存中衰减了2/3,10亿的partition table对于10亿的 normal table 在full table out of memory 的情况下 性能有所提升 (另外注意激活码基本只会被访问1次)

对于mongodb来说,这种业务需求完全可以搞定,在内存充足的情况下QPS达到了16W+/s,但是在内存不足的情况下,暴跌至2549.

mysql sync-binlog 导致 poor IO performance

January 29, 2013 MYSQL, performance No comments

mysql 数据库 poor IO performance.从DELL 815(6disk raid 5,oel5.5) 迁移至 HP DL380 (14disk raid 1+0,rhl5.8)

当时的IO 表现

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.55    0.00    1.30    0.25    0.00   93.90

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
cciss/c0d0        0.00     5.00  0.00  5.00     0.00    40.00    16.00     0.00    0.30   0.30   0.15
cciss/c0d0p1 
               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d0p2 
               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d0p3 
               0.00     5.00  0.00  5.00     0.00    40.00    16.00     0.00    0.30   0.30   0.15
cciss/c0d0p4 
               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d0p5 
               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d0p6 
               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d1        0.00  1199.00  4.50 2289.00    66.00 13952.00    12.22     0.88    0.38   0.37  85.35
cciss/c0d1p1 
               0.00  1199.00  4.50 2289.00    66.00 13952.00    12.22     0.88    0.38   0.37  85.35

Average:          DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
Average:     dev104-0     34.67      0.00  20727.64    597.80      0.02      0.51      0.12      0.40
Average:     dev104-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:     dev104-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:     dev104-3      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:     dev104-4      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:     dev104-5     34.67      0.00  20727.64    597.80      0.02      0.51      0.12      0.40
Average:     dev104-6      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:    dev104-16   2676.38   7654.27  29005.03     13.70      2.47      0.92      0.37     98.04
Average:    dev104-17   2676.38   7654.27  29005.03     13.70      2.47      0.92      0.37     98.04

%util 接近90%. 临时修改sync_binlog=0 问题解决 (源库sync_binlog=1 没有出现这种问题)

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
cciss/c0d0        0.00  9094.00  0.00 136.00     0.00 36920.00   542.94     0.05    0.35   0.10   1.40
cciss/c0d0p1 
               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d0p2 
               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d0p3 
               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d0p4 
               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d0p5 
               0.00  9094.00  0.00 136.00     0.00 36920.00   542.94     0.05    0.35   0.10   1.40
cciss/c0d0p6 
               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d1        0.00   575.00 25.00 18.00   400.00  2372.00   128.93     0.17    4.03   3.81  16.40
cciss/c0d1p1 
               0.00   575.00 25.00 18.00   400.00  2372.00   128.93     0.17    4.03   3.81  16.40
               
               
05:43:19 PM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
05:43:21 PM  dev104-0      1.01      0.00     72.36     72.00      0.00      0.00      0.00      0.00
05:43:21 PM  dev104-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
05:43:21 PM  dev104-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
05:43:21 PM  dev104-3      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
05:43:21 PM  dev104-4      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
05:43:21 PM  dev104-5      1.01      0.00     72.36     72.00      0.00      0.00      0.00      0.00
05:43:21 PM  dev104-6      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
05:43:21 PM dev104-16     52.76   1165.83   5089.45    118.55      0.25      4.77      4.68     24.67
05:43:21 PM dev104-17     52.76   1165.83   5089.45    118.55      0.25      4.77      4.68     24.67

%util,tps,均大幅下降。应用类型为快速插入commit 操作。

测试对比DELL815, HP DL380 IO 表现:

DELL PowerEdge R815

[root@db-2-2 iozone]# iozone -i 0 -i 1 -r 4096 -s 2G -Recb /data/mysql/iozone/log.xls -t 2 -C |tee /data/mysql/iozone/iozone.log
       Iozone: Performance Test of File I/O
               Version $Revision: 3.315 $
              Compiled for 64 bit mode.
              Build: linux 

       Contributors:William Norcott, Don Capps, Isom Crawford, Kirby Collins
                    Al Slater, Scott Rhine, Mike Wisner, Ken Goss
                    Steve Landherr, Brad Smith, Mark Kelly, Dr. Alain CYR,
                    Randy Dunlap, Mark Montague, Dan Million, Gavin Brebner,
                    Jean-Marc Zucconi, Jeff Blomberg, Benny Halevy,
                    Erik Habbinga, Kris Strecker, Walter Wong, Joshua Root.

       Run began: Tue Jan 29 11:11:51 2013

       Record Size 4096 KB
       File size set to 2097152 KB
       Excel chart generation enabled
       Include fsync in write timing
       Include close in write timing
       Command line used: iozone -i 0 -i 1 -r 4096 -s 2G -Recb /data/mysql/iozone/log.xls -t 2 -C
       Output is in Kbytes/sec
       Time Resolution = 0.000001 seconds.
       Processor cache size set to 1024 Kbytes.
       Processor cache line size set to 32 bytes.
       File stride size set to 17 * record size.
       Throughput test with 2 processes
       Each process writes a 2097152 Kbyte file in 4096 Kbyte records

       Children see throughput for  2 initial writers =  197313.41 KB/sec
       Parent sees throughput for  2 initial writers    =  174735.45 KB/sec
       Min throughput per process                 =   87504.14 KB/sec 
       Max throughput per process                =  109809.27 KB/sec
       Avg throughput per process                 =   98656.70 KB/sec
       Min xfer                                  = 1671168.00 KB
       Child[0] xfer count = 1671168.00 KB, Throughput =   87504.14 KB/sec
       Child[1] xfer count = 2097152.00 KB, Throughput =  109809.27 KB/sec

       Children see throughput for  2 rewriters =  203248.12 KB/sec
       Parent sees throughput for  2 rewriters    =  203053.04 KB/sec
       Min throughput per process                 =  101543.17 KB/sec 
       Max throughput per process                =  101704.95 KB/sec
       Avg throughput per process                 =  101624.06 KB/sec
       Min xfer                                  = 2097152.00 KB
       Child[0] xfer count = 2097152.00 KB, Throughput =  101543.17 KB/sec
       Child[1] xfer count = 2097152.00 KB, Throughput =  101704.95 KB/sec

       Children see throughput for  2 readers            = 2397158.38 KB/sec
       Parent sees throughput for  2 readers             = 2391863.63 KB/sec
       Min throughput per process                 = 1176860.12 KB/sec 
       Max throughput per process                = 1220298.25 KB/sec
       Avg throughput per process                 = 1198579.19 KB/sec
       Min xfer                                  = 2023424.00 KB
       Child[0] xfer count = 2023424.00 KB, Throughput = 1176860.12 KB/sec
       Child[1] xfer count = 2097152.00 KB, Throughput = 1220298.25 KB/sec

       Children see throughput for 2 re-readers   = 2600163.88 KB/sec
       Parent sees throughput for 2 re-readers    = 2592894.05 KB/sec
       Min throughput per process                 = 1271696.50 KB/sec 
       Max throughput per process                = 1328467.38 KB/sec
       Avg throughput per process                 = 1300081.94 KB/sec
       Min xfer                                  = 2011136.00 KB
       Child[0] xfer count = 2011136.00 KB, Throughput = 1271696.50 KB/sec
       Child[1] xfer count = 2097152.00 KB, Throughput = 1328467.38 KB/sec



"Throughput report Y-axis is type of test X-axis is number of processes"
"Record size = 4096 Kbytes "
"Output is in Kbytes/sec"

"  Initial write "  197313.41 

"        Rewrite "  203248.12 

"           Read " 2397158.38 

"        Re-read " 2600163.88

—————————————————————–

HP ProLiant DL380p Gen8

[root@DCB-SRV-0225 data1]# iozone -i 0 -i 1 -r 4096 -s 2G -Recb /data1/iozone/log.xls -t 2 -C |tee /data1/iozone/iozone.log
       Iozone: Performance Test of File I/O
               Version $Revision: 3.315 $
              Compiled for 64 bit mode.
              Build: linux 

       Contributors:William Norcott, Don Capps, Isom Crawford, Kirby Collins
                    Al Slater, Scott Rhine, Mike Wisner, Ken Goss
                    Steve Landherr, Brad Smith, Mark Kelly, Dr. Alain CYR,
                    Randy Dunlap, Mark Montague, Dan Million, Gavin Brebner,
                    Jean-Marc Zucconi, Jeff Blomberg, Benny Halevy,
                    Erik Habbinga, Kris Strecker, Walter Wong, Joshua Root.

       Run began: Tue Jan 29 10:50:05 2013

       Record Size 4096 KB
       File size set to 2097152 KB
       Excel chart generation enabled
       Include fsync in write timing
       Include close in write timing
       Command line used: iozone -i 0 -i 1 -r 4096 -s 2G -Recb /data1/iozone/log.xls -t 2 -C
       Output is in Kbytes/sec
       Time Resolution = 0.000001 seconds.
       Processor cache size set to 1024 Kbytes.
       Processor cache line size set to 32 bytes.
       File stride size set to 17 * record size.
       Throughput test with 2 processes
       Each process writes a 2097152 Kbyte file in 4096 Kbyte records

       Children see throughput for  2 initial writers =  372381.80 KB/sec
       Parent sees throughput for  2 initial writers    =  371527.79 KB/sec
       Min throughput per process                 =  185643.42 KB/sec 
       Max throughput per process                =  186738.38 KB/sec
       Avg throughput per process                 =  186190.90 KB/sec
       Min xfer                                  = 2084864.00 KB
       Child[0] xfer count = 2084864.00 KB, Throughput =  185643.42 KB/sec
       Child[1] xfer count = 2097152.00 KB, Throughput =  186738.38 KB/sec

       Children see throughput for  2 rewriters =  560498.03 KB/sec
       Parent sees throughput for  2 rewriters    =  560283.33 KB/sec
       Min throughput per process                 =  280219.81 KB/sec 
       Max throughput per process                =  280278.22 KB/sec
       Avg throughput per process                 =  280249.02 KB/sec
       Min xfer                                  = 2097152.00 KB
       Child[0] xfer count = 2097152.00 KB, Throughput =  280219.81 KB/sec
       Child[1] xfer count = 2097152.00 KB, Throughput =  280278.22 KB/sec

       Children see throughput for  2 readers            = 7649346.50 KB/sec
       Parent sees throughput for  2 readers             = 7612895.95 KB/sec
       Min throughput per process                 = 3796191.50 KB/sec 
       Max throughput per process                = 3853155.00 KB/sec
       Avg throughput per process                 = 3824673.25 KB/sec
       Min xfer                                  = 2076672.00 KB
       Child[0] xfer count = 2076672.00 KB, Throughput = 3796191.50 KB/sec
       Child[1] xfer count = 2097152.00 KB, Throughput = 3853155.00 KB/sec

       Children see throughput for 2 re-readers   = 8173770.00 KB/sec
       Parent sees throughput for 2 re-readers    = 8132688.68 KB/sec
       Min throughput per process                 = 4063641.50 KB/sec 
       Max throughput per process                = 4110128.50 KB/sec
       Avg throughput per process                 = 4086885.00 KB/sec
       Min xfer                                  = 2084864.00 KB
       Child[0] xfer count = 2097152.00 KB, Throughput = 4110128.50 KB/sec
       Child[1] xfer count = 2084864.00 KB, Throughput = 4063641.50 KB/sec



"Throughput report Y-axis is type of test X-axis is number of processes"
"Record size = 4096 Kbytes "
"Output is in Kbytes/sec"

"  Initial write "  372381.80 

"        Rewrite "  560498.03 

"           Read " 7649346.50 

"        Re-read " 8173770.00 


iozone test complete.

所有指数 HP 都秒杀dell 了.

参考 ext3 and sync-binlog do not play well together。 看来在不同的OS版本上,mysql之于ext3的performance仍然是存在区别的。

Linux mysql 优化

May 28, 2012 MYSQL, performance No comments

linux mysql

一、修改内核参数
1.1、编辑/etc/sysctl.conf
vi /etc/sysctl.conf

net.ipv4.tcp_max_syn_backlog = 65536
net.core.netdev_max_backlog = 65536
net.core.somaxconn = 32768

net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216

net.ipv4.tcp_timestamps = 0
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syn_retries = 2
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1

net.ipv4.tcp_max_orphans = 262144
fs.aio-max-nr = 3145728
net.ipv4.ip_local_port_range = 1024 65000
kernel.core_uses_pid = 1
kernel.shmall = 33554432
kernel.shmmax = 68719476736
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6553600

关于参数的说明

参数大小可根据系统具体情况作相应调整,但不要小于给出的参数值。
kernel.shmall = 2097152 # 可以使用的共享内存的总量(内存页数目)。
kernel.shmmax = 2147483648 # 最大共享内存段大小。取物理内存大小的一半,单位为字节
kernel.shmmni = 4096 # 整个系统共享内存段的最大数目。
kernel.sem = 250 32000 100 128 # 每个信号对象集的最大信号对象数(semmsl);系统范围内最大信号对象数(semmns)(必须大于等于semmni*semmsl);每个信号对象支持的最大操作数(semopm);系统范围内最大信号对象集数(semmni)。
fs.file-max = 65536 # 系统中所允许的文件句柄最大数目。
net.ipv4.ip_local_port_range = 1024 65000 # 应用程序可使用的IPv4端口范围。

像RAC,可调整UDP网络参数,操作系统最大发送和接收缓冲区,可以增加默认和最大的发送和接收缓冲区的大小:
net.core.rmem_default = 262144 # 套接字接收缓冲区大小的缺省值
net.core.rmem_max = 8388608 # 套接字接收缓冲区大小的最大值
net.core.wmem_default = 262144 # 套接字发送缓冲区大小的缺省值
net.core.wmem_max = 8388608 # 套接字发送缓冲区大小的最大值

还可以设置TCP读缓冲和写缓冲的大小,这些参数用于指定Linux为每个套接字保留的缓冲空间,增加缓冲空间可以获得更高的网络性能,从而获得更高的DATAGUARD性能,因为传输和接收数据的时间减少了:
net.ipv4.tcp_rmen=4096 262144 8388608
net.ipv4.tcp_wmen=4096 262144 8388608

提高RAC集群中失效恢复的性能:
net.ipv4.tcp_keepalive_time = 3000
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_syn_retries = 2
net.ipv4.tcp_retries2 = 3

linux sysctl.conf中相关重要设定的详细说明
$ /proc/sys/net/core/wmem_max
最大socket写buffer,可参考的优化值:873200
$ /proc/sys/net/core/rmem_max
最大socket读buffer,可参考的优化值:873200
$ /proc/sys/net/ipv4/tcp_wmem
TCP写buffer,可参考的优化值: 8192 436600 873200
$ /proc/sys/net/ipv4/tcp_rmem
TCP读buffer,可参考的优化值: 32768 436600 873200
$ /proc/sys/net/ipv4/tcp_mem
同样有3个值,意思是:
net.ipv4.tcp_mem[0]:低于此值,TCP没有内存压力.
net.ipv4.tcp_mem[1]:在此值下,进入内存压力阶段.
net.ipv4.tcp_mem[2]:高于此值,TCP拒绝分配socket.
上述内存单位是页,而不是字节.可参考的优化值是:786432 1048576 1572864
$ /proc/sys/net/core/netdev_max_backlog
进入包的最大设备队列.默认是300,对重负载服务器而言,该值太低,可调整到1000.
$ /proc/sys/net/core/somaxconn
listen()的默认参数,挂起请求的最大数量.默认是128.对繁忙的服务器,增加该值有助于网络性能.可调整到256.
$ /proc/sys/net/core/optmem_max
socket buffer的最大初始化值,默认10K.
$ /proc/sys/net/ipv4/tcp_max_syn_backlog
进入SYN包的最大请求队列.默认1024.对重负载服务器,增加该值显然有好处.可调整到2048.
$ /proc/sys/net/ipv4/tcp_retries2
TCP失败重传次数,默认值15,意味着重传15次才彻底放弃.可减少到5,以尽早释放内核资源.
$ /proc/sys/net/ipv4/tcp_keepalive_time
$ /proc/sys/net/ipv4/tcp_keepalive_intvl
$ /proc/sys/net/ipv4/tcp_keepalive_probes
这3个参数与TCP KeepAlive有关.默认值是:
tcp_keepalive_time = 7200 seconds (2 hours)
tcp_keepalive_probes = 9
tcp_keepalive_intvl = 75 seconds
意思是如果某个TCP连接在idle 2个小时后,内核才发起probe.如果probe 9次(每次75秒)不成功,内核才彻底放弃,认为该连接已失效.对服务器而言,显然上述值太大. 可调整到:
/proc/sys/net/ipv4/tcp_keepalive_time 1800
/proc/sys/net/ipv4/tcp_keepalive_intvl 30
/proc/sys/net/ipv4/tcp_keepalive_probes 3
$ proc/sys/net/ipv4/ip_local_port_range
指定端口范围的一个配置,默认是32768 61000,已够大.

net.ipv4.tcp_syncookies = 1
表示开启SYN Cookies。当出现SYN等待队列溢出时,启用cookies来处理,可防范少量SYN攻击,默认为0,表示关闭;

net.ipv4.tcp_tw_reuse = 1
表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接,默认为0,表示关闭;

net.ipv4.tcp_tw_recycle = 1
表示开启TCP连接中TIME-WAIT sockets的快速回收,默认为0,表示关闭。

net.ipv4.tcp_fin_timeout = 30
表示如果套接字由本端要求关闭,这个参数决定了它保持在FIN-WAIT-2状态的时间。

net.ipv4.tcp_keepalive_time = 1200
表示当keepalive起用的时候,TCP发送keepalive消息的频度。缺省是2小时,改为20分钟。

net.ipv4.ip_local_port_range = 1024 65000
表示用于向外连接的端口范围。缺省情况下很小:32768到61000,改为1024到65000。

net.ipv4.tcp_max_syn_backlog = 8192
表示SYN队列的长度,默认为1024,加大队列长度为8192,可以容纳更多等待连接的网络连接数。

net.ipv4.tcp_max_tw_buckets = 5000
表示系统同时保持TIME_WAIT套接字的最大数量,如果超过这个数字,TIME_WAIT套接字将立刻被清除并打印警告信息。默认为180000,改为 5000。对于Apache、Nginx等服务器,上几行的参数可以很好地减少TIME_WAIT套接字数量,但是对于Squid,效果却不大。此项参数可以控制TIME_WAIT套接字的最大数量,避免Squid服务器被大量的TIME_WAIT套接字拖死。

1.2、使参数生效
运行命令sysctl –p或者重新启动。

1.3、修改文件数据限制
limits.conf

* soft nofile 131072
* hard nofile 131072

二、my.cnf参数文件
以下key_buffer_size和innodb_buffer_pool_size需根据情况调整,建议只用innodb类型的表,这个时候可以相应调小key_buffer_size,但是还应保留一定的值。

[client]
#password = your_password
port = 3306
socket = /data/mysqldata/mysql/mysql.sock
default-character-set=utf8 (5.5 : character_set_server=utf8)

# The MySQL server
[mysqld]
port = 3306
socket = /data/mysqldata/mysql/mysql.sock
skip-locking (5.5 : skip-external-locking)

sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 2M
query_cache_limit = 2M
query_cache_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 64M

thread_cache_size = 100
table_open_cache = 4096
thread_concurrency = 24
open_files_limit = 30000

default-character-set=utf8
max_connections=800
interactive_timeout=28800

binlog_cache_size = 4M
log-bin=mysql-bin
server-id = 1

#MyISAM variables
key_buffer_size = 1024M
myisam_sort_buffer_size = 64M
bulk_insert_buffer_size = 32M

#INNODB variables
innodb_buffer_pool_size = 8G
innodb_additional_mem_pool_size = 120M
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 80
innodb_lock_wait_timeout = 50
innodb_file_per_table = 1
innodb_thread_concurrency = 0

innodb_data_file_path = ibdata1:8192M;ibdata2:8192M;ibdata3:10M:autoextend
innodb_log_file_size = 256M
innodb_log_files_in_group = 4
innodb_log_buffer_size = 16M
innodb_open_files=1600

#innodb_io_capacity=400
#innodb_read_io_threads
#innodb_write_io_threads

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 32M
write_buffer = 32M

[mysqlhotcopy]
interactive-timeout

说明:
A innodb_file_per_table=1(innodb_open_files需要根据情况调整)

1. 每个表都有自已独立的表空间。
2. 每个表的数据和索引都会存在自已的表空间中。
3. 可以实现单表在不同的数据库中移动。
4. 空间可以回收(除drop table操作处,表空不能自已回收)
a) Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
c) 对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。

#innodb_data_file_path
#指定InnoDB表空间数据文件名,大小以及其他属性。所有文件的加起来不能少于10M。多个数据文件之间以逗号分割,属性之间以冒号分割。默认创建一个大小10MB名为ibdata1的可自动扩展的数据文件,一般在生产环境中都需要根据实际情况指定,由于往表空间中添加数据文件需要停机,尽量在规划的时候做好准备,如果可以的话最好开启最后一个数据文件的自动增长属性。数据文件的个数在规划的时候还需要考虑另外一个innodb_open_files参数。
#innodb_file_per_table
#取值为ON或者OFF。是否为每个table使用单独的数据文件保存。如果系统中表的个数不多,并且没有超大表,使用该参数可以使得各个表之间的维护相对独立,有一定的好处。

附:另外一些参数的优化:

query_cache_type : 如果全部使用innodb存储引擎,建议为0,如果使用MyISAM 存储引擎,建议为2,同时在SQL语句中显式控制是否是哟你gquery cache

query_cache_size: 根据 命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整,一般不建议太大,256MB可能已经差不多了,大型的配置型静态数据可适当调大

binlog_cache_size: 一般环境2MB~4MB是一个合适的选择,事务较大且写入频繁的数据库环境可以适当调大,但不建议超过32MB

key_buffer_size: 如果不使用MyISAM存储引擎,16MB足以,用来缓存一些系统表信息等。如果使用 MyISAM存储引擎,在内存允许的情况下,尽可能将所有索引放入内存,简单来说就是“越大越好”

bulk_insert_buffer_size: 如果经常性的需要使用批量插入的特殊语句(上面有说明)来插入数据,可以适当调大该参数至16MB~32MB,不建议继续增大,某人8MB

innodb_buffer_pool_size: 如果不使用InnoDB存储引擎,可以不用调整这个参数,如果需要使用,在内存允许的情况下,尽可能将所有的InnoDB数据文件存放如内存中,同样将但来说也是“越大越好”

innodb_additional_mem_pool_size: 一般的数据库建议调整到8MB~16MB,如果表特别多,可以调整到32MB,可以根据error log中的信息判断是否需要增大

innodb_log_buffer_size: 默认是1MB,系的如频繁的系统可适当增大至4MB~8MB。当然如上面介绍所说,这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB

innodb_max_dirty_pages_pct: 根据以往的经验,重启恢复的数据如果要超过1GB的话,启动速度会比较慢,几乎难以接受,所以建议不大于 1GB/innodb_buffer_pool_size(GB)*100 这个值。当然,如果你能够忍受启动时间比较长,而且希望尽量减少内存至磁盘的flush,可以将这个值调整到90,但不建议超过90

参考 YOSHINORI MATSUNOBU的一篇文章

参考文档:http://www.slideshare.net/matsunobu/linux-and-hw-optimizations-for-mysql-7614520

对于单表过大的问题,可以考虑分区表,表现为每个分区一个文件:
CREATE TABLE `T_PRODUCT_STAT_HIS` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`SITE_ID` int(11) NOT NULL,
`PRODUCT_CODE` varchar(200) DEFAULT NULL,
`PRODUCT_NAME` varchar(1000) NOT NULL,
`PRODUCT_URL` varchar(1000) NOT NULL,
`GENERATE_TIME` datetime NOT NULL,
`PRODUCT_PRICE` float(12,2) DEFAULT NULL,
`BAND` float(6,1) DEFAULT NULL,
`CONSULTING_NUM` int(11) DEFAULT NULL,
`APPRAISAL_NUM` int(11) DEFAULT NULL,
`SOLD_NUM` int(11) DEFAULT NULL,
`FLAG_STOCK` int(11) DEFAULT ‘0’,
`FLAG_PROMOTION` int(11) DEFAULT ‘0’,
`FLAG_SHOPPING_RUSH` int(11) DEFAULT ‘0’,
`IMG_PATH` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`ID`,`GENERATE_TIME`,`SITE_ID`),
KEY `IDX_T_PRODUCT_STAT_HIS__SITE_ID` (`SITE_ID`),
KEY `IDX_T_PRODUCT_STAT_HIS__PRODUCT_CODE` (`PRODUCT_CODE`),
KEY `IDX_T_PRODUCT_STAT_HIS__PRODUCT_URL` (`PRODUCT_URL`(255)),
KEY `IDX_T_PRODUCT_STAT_HIS_GENERATE_TIME` (`GENERATE_TIME`)
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(GENERATE_TIME))
(PARTITION p1201_1 VALUES LESS THAN (734868) ENGINE = InnoDB,
PARTITION p1201_2 VALUES LESS THAN (734878) ENGINE = InnoDB,
PARTITION p1201_3 VALUES LESS THAN (734888) ENGINE = InnoDB,
PARTITION p1202_1 VALUES LESS THAN (734899) ENGINE = InnoDB,
PARTITION p1202_2 VALUES LESS THAN (734909) ENGINE = InnoDB,
PARTITION p1202_3 VALUES LESS THAN (734919) ENGINE = InnoDB,
PARTITION p1203_1 VALUES LESS THAN (734928) ENGINE = InnoDB)

[root@RHEL55node1 test]# ls -al
total 1228
drwxr-xr-x 2 mysql mysql 4096 Jan 17 10:49 .
drwxr-xr-x 5 mysql mysql 4096 Jan 17 10:47 ..
-rw-rw—- 1 mysql mysql 21466 Jan 17 10:49 T_PRODUCT_STAT_HIS.frm
-rw-rw—- 1 mysql mysql 80 Jan 17 10:49 T_PRODUCT_STAT_HIS.par
-rw-rw—- 1 mysql mysql 163840 Jan 17 10:49 T_PRODUCT_STAT_HIS#P#p1201_1.ibd
-rw-rw—- 1 mysql mysql 163840 Jan 17 10:49 T_PRODUCT_STAT_HIS#P#p1201_2.ibd
-rw-rw—- 1 mysql mysql 163840 Jan 17 10:49 T_PRODUCT_STAT_HIS#P#p1201_3.ibd
-rw-rw—- 1 mysql mysql 163840 Jan 17 10:49 T_PRODUCT_STAT_HIS#P#p1202_1.ibd
-rw-rw—- 1 mysql mysql 163840 Jan 17 10:49 T_PRODUCT_STAT_HIS#P#p1202_2.ibd
-rw-rw—- 1 mysql mysql 163840 Jan 17 10:49 T_PRODUCT_STAT_HIS#P#p1202_3.ibd
-rw-rw—- 1 mysql mysql 163840 Jan 17 10:49 T_PRODUCT_STAT_HIS#P#p1203_1.ibd

B mysql innodb 增加数据文件和日志文件
Adding, Removing, or Resizing InnoDB Data and Log Files
This section describes what you can do when your InnoDB tablespace runs out of room or when you want to change the size of the
log files.
The easiest way to increase the size of the InnoDB tablespace is to configure it from the beginning to be auto-extending. Specify
the autoextend attribute for the last data file in the tablespace definition. Then InnoDB increases the size of that file automatically
in 8MB increments when it runs out of space. The increment size can be changed by setting the value of the innodb_
autoextend_increment system variable, which is measured in MB.
Alternatively, you can increase the size of your tablespace by adding another data file. To do this, you have to shut down the
MySQL server, change the tablespace configuration to add a new data file to the end of innodb_data_file_path, and start
the server again.
If your last data file was defined with the keyword autoextend, the procedure for reconfiguring the tablespace must take into account
the size to which the last data file has grown. Obtain the size of the data file, round it down to the closest multiple of 1024 ×
1024 bytes (= 1MB), and specify the rounded size explicitly in innodb_data_file_path. Then you can add another data file.
Remember that only the last data file in the innodb_data_file_path can be specified as auto-extending.
As an example, assume that the tablespace has just one auto-extending data file ibdata1:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
Suppose that this data file, over time, has grown to 988MB. Here is the configuration line after modifying the original data file to
not be auto-extending and adding another auto-extending data file:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
When you add a new file to the tablespace configuration, make sure that it does not exist. InnoDB will create and initialize the file
when you restart the server.
Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure:
1. Use mysqldump to dump all your InnoDB tables.
2. Stop the server.
3. Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the
information, then copy all the ib* files to another location before the removing the files in your MySQL installation.
4. Remove any .frm files for InnoDB tables.
5. Configure a new tablespace.
6. Restart the server.
7. Import the dump files.
If you want to change the number or the size of your InnoDB log files, use the following instructions. The procedure to use depends
on the value of innodb_fast_shutdown:
? If innodb_fast_shutdown is not set to 2: Stop the MySQL server and make sure that it shuts down without errors (to ensure
that there is no information for outstanding transactions in the log). Copy the old log files into a safe place in case
something went wrong during the shutdown and you need them to recover the tablespace. Delete the old log files from the log
file directory, edit my.cnf to change the log file configuration, and start the MySQL server again. mysqld sees that no InnoDB
log files exist at startup and creates new ones.
? If innodb_fast_shutdown is set to 2: Set innodb_fast_shutdown to 1:
mysql> SET GLOBAL innodb_fast_shutdown = 1;
Then follow the instructions in the previous item.

一个操作的例子:
1 计算原有表空间大小:
69206016/1024/1024= 66M
[root@testdb2 mysql]# ls -al
total 77980
drwxr-xr-x 4 mysql mysql 4096 Jul 29 15:57 .
drwxr-xr-x 37 root root 4096 Jul 29 13:25 ..
-rw-rw—- 1 mysql mysql 69206016 Jul 29 15:57 ibdata1
-rw-rw—- 1 mysql mysql 5242880 Jul 29 15:57 ib_logfile0
-rw-rw—- 1 mysql mysql 5242880 Jul 29 15:50 ib_logfile1
drwx–x–x 2 mysql mysql 4096 Jul 29 13:25 mysql
-rw-r–r– 1 mysql mysql 115 Jul 29 13:25 RPM_UPGRADE_HISTORY
drwxr-xr-x 2 mysql mysql 4096 Jul 29 15:49 test
-rw-rw—- 1 mysql root 3249 Jul 29 15:57 testdb2.err

2 备份原有日志文件:
[root@testdb2 mysql]# mkdir bak
[root@testdb2 mysql]# mv ib_logfile0 bak
[root@testdb2 mysql]# mv ib_logfile1 bak
[root@testdb2 mysql]# ls -al
total 67716
drwxr-xr-x 5 mysql mysql 4096 Jul 29 15:58 .
drwxr-xr-x 37 root root 4096 Jul 29 13:25 ..
drwxr-xr-x 2 root root 4096 Jul 29 15:58 bak
-rw-rw—- 1 mysql mysql 69206016 Jul 29 15:57 ibdata1
drwx–x–x 2 mysql mysql 4096 Jul 29 13:25 mysql
-rw-r–r– 1 mysql mysql 115 Jul 29 13:25 RPM_UPGRADE_HISTORY
drwxr-xr-x 2 mysql mysql 4096 Jul 29 15:49 test
-rw-rw—- 1 mysql root 3249 Jul 29 15:57 testdb2.err

3 修改参数文件(关闭mysql):
innodb_data_file_path = ibdata1:66M;ibdata2:2000M;ibdata3:10M:autoextend
innodb_log_file_size = 200M
innodb_log_files_in_group = 3

innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 120M
innodb_log_buffer_size = 16M

4 启动mysql:
[root@testdb2 mysql]# ls -al
total 2743024
drwxr-xr-x 5 mysql mysql 4096 Jul 29 15:59 .
drwxr-xr-x 37 root root 4096 Jul 29 13:25 ..
drwxr-xr-x 2 root root 4096 Jul 29 15:58 bak
-rw-rw—- 1 mysql mysql 69206016 Jul 29 15:59 ibdata1
-rw-rw—- 1 mysql mysql 2097152000 Jul 29 15:58 ibdata2
-rw-rw—- 1 mysql mysql 10485760 Jul 29 15:58 ibdata3
-rw-rw—- 1 mysql mysql 209715200 Jul 29 15:59 ib_logfile0
-rw-rw—- 1 mysql mysql 209715200 Jul 29 15:59 ib_logfile1
-rw-rw—- 1 mysql mysql 209715200 Jul 29 15:59 ib_logfile2
drwx–x–x 2 mysql mysql 4096 Jul 29 13:25 mysql
srwxrwxrwx 1 mysql mysql 0 Jul 29 15:59 mysql.sock
-rw-r–r– 1 mysql mysql 115 Jul 29 13:25 RPM_UPGRADE_HISTORY
drwxr-xr-x 2 mysql mysql 4096 Jul 29 15:49 test
-rw-rw—- 1 mysql root 5366 Jul 29 15:59 testdb2.err
-rw-rw—- 1 mysql mysql 6 Jul 29 15:59 testdb2.pid

C 数据文件目录,如果用rpm安装的话,默认安装目录一般为 /var/lib/mysql,一般需要移到另外的目录,类似:
在根目录下新建文件夹
[root@localhost /]# mkdir mysqldata
停止MYSQL服务器 service mysql stop
复制/var/lib/mysql下所有文件到/mysqldata
查看原数据库文件夹下各文件权限默认为mysql
更改/mysqldata下各文件权限
[root@localhost /]# chown -R mysql:mysql ./mysqldata
检查权限 [root@localhost /]# ls -l
修改my.cnf
#The MySQL server
[client]
#password = your_password
port = 3306
#socket = /var/lib/mysql/mysql.sock
socket =/mysqldata/mysql.sock –增加此行,并配置数据库目录
[mysqld]
port = 3306
#socket = /var/lib/mysql/mysql.sock
socket =/mysqldata/mysql.sock –增加此行,并配置数据库目录
修改启动文件
修改/etc/rc.d/init.d/mysql
找到datadir位置,修改内容如下
#Set some defaults
pid_file=
server_pid_file=
use_mysqld_safe=1
user=mysql
if test -z “$basedir”
then
basedir=/
bindir=/usr/bin
if test -z “$datadir”
then
#datadir=/var/lib/mysql –修改前内容
datadir=/mysqldata –修改后内容
fi
sbindir=/usr/sbin

三、slave 的创建
。。。
四、slave 转为master
单独的slave参考:http://space.itpub.net/758322/viewspace-714346
Mysql Slave群切换Master:http://space.itpub.net/758322/viewspace-680472