MySQL支持

        作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究.

       对于使用MySQL的用户，对于这个变量大家一定不会陌生。前几年的MyISAM引擎优化中，这个参数也是一个重要的优化参数。但随着发展，这个参数也爆露出来一些问题。

       机器的内存越来越大，人们也都习惯性的把以前有用的参数分配的值越来越大。这个参数加大后也引发了一系列问题。我们首先分析一下query_cache_size的工作原理：

       一个SELECT查询在DB中工作后，DB会把该语句缓存下来，当同样的一个SQL再次来到DB里调用时，DB在该表没发生变化的情况下把结果从缓存中返回给Client。

   这里有一个关建点，就是DB在利用Query_cache工作时，要求该语句涉及的表在这段时间内没有发生变更。那如果该表在发生变更时，Query_cache里的数据又怎么处理呢？首先要把Query_cache和该表相关的语句全部置为失效，然后在写入更新。那么如果Query_cache非常大，该表的查询结构又比较多，查询语句失效也慢，一个更新或是Insert就会很慢，这样看到的就是Update或是Insert怎么这么慢了。

   所以在数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建系把该功能禁掉。

    作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究

     Innodb是一个多版本的存储引擎，它可以把旧的行信息存到表空间中。这些旧的行信息存储到Innodb称为的回滚段的表空间中。

     Innodb为实现多版本，Innodb在每一行添加了三个列。一个6字节的DB_TRX_ID字段用来表示事务的Insert或是Update操作，对于Delete操作实际上也并不在直接删除，只是用一个Bit位去标识行被删除。另外，每行包括7字节的DB_ROLL_PTR字段，称为回滚指针（roll pointer）。这个回滚指针指向回滚段（undo segment）中的回滚记录。如果行被更新，那么回滚段中记录的信息足以使update操作加到update操作之间。最后还有一个6字节的DB_ROW_ID字段，该字段包含新行的Row Id，这个字段只在Insert操作时单纯的增加。DB_ROW_ID是需要一个互诉锁的才能产生。Innodb产生的clustered index包括Row ID.从另一方面来说，除了Clustered Index索引外，其它的索引不会包含Row Id.

     Innodb利用回滚段保存的信息完成事务的回滚。同样是为了读取早的版本行信息，通过回滚段中的信息达到一致性读。

       回滚段中的回滚日值可以区分为insert和update日值。Insert的回滚日值只需要一个事务回的信息，记录Insert的操作的事务就行，该回滚信息在事务提交后被丢弃。Update的回滚日值不但用来撤销事务，同时也为一致性读服务。在事务操作中，别的Session可以通过Update的回滚段信息达构建早期的版本，从来达到一致性读。

       对于Innodb的多版本是为达到一致性读，我们在使用Innodb时要养成一个习惯：要规律的提交我们的事务。另一方面，对于Innodb的回滚段中Update的回滚日值不能随着事务的提交而被丢弃，所以回滚段有可能增长很大，填满所有的表空间。

       回滚段的需求的物理大小通常比Insert和Update的行小的多，所以我们可以根据Insert，Update行的并发量来估算分配回滚段的大小。

    在Innodb的多版本设计中，Delete语句并不是直接物理的从数据中立即删除相应的行，只是做一个Bit位的标识。另外当Innodb删除相应的原行和行的索引信息时，回滚段中此行的Update回滚日值才会被清除。对于Delete操作的的删除我们称为静化（purge）,这个操作是很快速的，正常情况下静化在SQL语句后按一定的顺序去执行删除操作。

    假设一个场景：当一个用户用小的批量插入和删除行操作在一个表，它有可能会造成静化操作的进程落后，从而造成表的增长的很大很大，使的磁盘的工作效率比较低了。这样就会出现恶劣的情况既使表只有该表只有10M的数据，也有可能使表空间增长到10G，当然里面有很多是即将过静化掉的行(dead row).基于这个原因，静化进程会造成新的行操作和分配资源的性能瓶颈。所以也要关注一下innodb_max_purge_lag这个参数的设定是不是合适。

    作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

      Innodb的表空间是在配置文件中定义（说是表空间有时觉的有点羞愧，和Oracle比真的差太远了），这里简单列一下表空间里的基本概念及表的分配情况。
       表空间是在配置文件中定义的几个文件简单的耦合起来，在使用中互不可少（少一个就面临DB完蛋的危险）。对于共享表空间无法确定表所在的表空间上。
      独立表空间可以做到每个表有自已的表空间（羞一下）。
       针对共享表空间，表空间中包括：回滚段,段（segment），区域（extent),数据页(page size)在表空间的体现为：
  表空间由默认16k的数据页面（page)组成，每64个连续的页面组成一个区域（extent,Oracle里熟悉的一个东东）。对于表空间的“文件(file)”在Innodb中被称为段(segment)。 回滚段(rollback segment）是一个特殊的例子，实际上rollback segment包含了多个段。对于Innodb表的索引都被分配成两个段：一个是为了 B-tree 的无叶结点(non-leaf nodes)，另一个是为了叶结点(leaf nodes)。
  这是为了达到包含数据的叶结点的更好的顺序(sequentiality for the leaf nodes)。
         当表空间中的一个段增长时，InnoDB 为它个别地分配最初的 32 个页面。之后 InnoDB 再分配段的整个区域(extents)。InnoDB 会以每次 4 个区域(extents)来增加一个大段以确保数据的良好顺序。
         表空间中的某些页面包含其它页面的位图(bitmaps)，所以在 InnoDB 表空间内的一些区域(extents)不能以一个整体分配给段，而只能作为个体页面。
          当发出一个查询 SHOW TABLE STATUS FROM … LIKE … 来询问表空间的剩余空间时，InnoDB 将报告表空间中所有空闲区域(extents)中确实可用的部分。InnoDB 通常会保留一些区域用于 clean-up 和其它的内部目的；这些保留的区域并不包含在剩余可用空间中。

         当从一个表中删除数据时，InnoDB 将收缩 B-tree 中相应的索引。这是依赖于释放个别的页面或区域(extents)以让其他用户使用剩余空间的删除模式。 移除(drop)一个表或删除所有记录可以保证释放空间给其他用户，但是删除记录行只有在事务回滚或 consistent read 后并不需要时才会被物理的移除

        对于独立表空间也是存一样的概念和行为，唯一区别就是每个表的数据存到指定的表空间中，rollback segment不和数据的segment在一个竞争。使用独立表空间的一个好处就是可以使数据分布相对于磁盘上更连续一点。

     作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。
         我们知道Innodb的数据页是16K,而且是一个硬性的规定，系统里没更改的办法，希望将来MySQL也能也Oracle一样支持多种数据页的大小。
但实际应用中有时16K显的有点大了，特别是很多业务在Oracle或是SQL SERVER运行的挺好的情况下迁到了MySQL上发现IO增长太明显的情况下，
就会想到更改数据页大小了。
　　实际上innodb的数据页大小也是可以更改的，只是需要在源码层去更改，然后重新rebuild一下MySQL.
    更改办法：
    (以MySQL-5.1.38源码为例）
    位置在storage/innobase/include/univ.i ，在univ.i中查找：UNIV_PAGE_SIZE

/*
   DATABASE VERSION CONTROL
   ========================
*/
 
/* The universal page size of the database */
#define UNIV_PAGE_SIZE          (2 * 8192) /* NOTE! Currently, this has to be a
     power of 2 */
/* The 2-logarithm of UNIV_PAGE_SIZE: */
#define UNIV_PAGE_SIZE_SHIFT 14
 
/* Maximum number of parallel threads in a parallelized operation */
#define UNIV_MAX_PARALLELISM 32

   UNIV_PAGE_SIZE就是数据页大小，默认的是16K. 后面的备注里标明，该值是可以设置必须为2的次方。对于该值可以设置成4k,8k,16k,32K,64K，在大也没意义了。
同时更改了UNIV_PAGE_SIZE后需要更改 UNIV_PAGE_SIZE_SHIFT 该值是2的多少次方为UNIV_PAGE_SIZE，所以设置数据页分别情况如下：

#define UNIV_PAGE_SIZE_SHIFT 12  if UNIV_PAGE_SIZ=4K
#define UNIV_PAGE_SIZE_SHIFT 13  if UNIV_PAGE_SIZ=8K
#define UNIV_PAGE_SIZE_SHIFT 15  if UNIV_PAGE_SIZ=32K

例子：
　更改innodb的数据页为8K,相应修改为：

/*
   DATABASE VERSION CONTROL
   ========================
*/
 
/* The universal page size of the database */
#define UNIV_PAGE_SIZE          8192   /* NOTE! Currently, this has to be a
     power of 2 */
/* The 2-logarithm of UNIV_PAGE_SIZE: */
#define UNIV_PAGE_SIZE_SHIFT 13
 
/* Maximum number of parallel threads in a parallelized operation */
#define UNIV_MAX_PARALLELISM 32

重新编译，然后测试测试，再测试。Good luck!

　作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。
     

来源：http://www.mysqlperformanceblog.com/2006/05/30/innodb-memory-usage/

译这个文章的目的：
　　最近经常被问起Innodb是如何使用内存的。该问题早已被原MySQL公司的Vadim论证过。我这里译一下他的文章供大家参考。
开始：
　　这里有许多关于Innodb如何使用内存的问题。我这里将会以innodb启动时的分配情况做一个解释。一些重要的概念：
　　NBLOCKS＝Innodb_buffer_pool有多个页(block)＝innodb_buffer_pool_size/16384(16k)
  　OS_THREADS= if ( innodb_buffer_pool_size >= 1000Mb) = 50000
 　　else if (innodb_buffer_pool_size >= 8Mb) = 10000
　　 else  = 1000 (该值只用在*nixes系统上，对于Windows有一点小的区别计算OS_THREADS)

所以Innodb 使用的内存包括：
　innodb_buffer_pool_size
    innodb_additional_mem_pool_size
    innodb_log_buffer_size
    adaptive index hash ,size (innodb buffer 索引管理区)= innodb_buffer_pool_size/64
    system dictionary hash,size(innodb内部字典区) = 6 * innodb_buffer_pool_size/512
    memory for sync_array,size(用于Innodb内部syncronzation的开销)=OS_THREAD * 512
    memory for os_event,size(用于innodb内存的syncronzation的开销)=OS_THREAD * 216
    memory for locking system(内存的锁管理系统),size = 5 * 4 *NBBLOCKS
 
 最终得到innodb内存使用的计算公式为：
     Innodb_buffer_pool_size + innodb_log_buffer_size + innodb_additional_mem_pool_size + 812/16384 * innodb_buffer_pool_size + OS_THREADS * 368
 对于812/16384 * Innodb_buffer_pool_size 可以简单的用 innodb_buffer_pool_size / 20　计算，

对于OS_THREADS * 368　　
    OS_THREADS * 368 = 17.5 MB  if innodb_buffer_pool_size > 1000MB
   OS_THREADS * 368 = 3.5 MB  if innodb_buffer_pool_size > 8MB

举一个例子：
　　 如果你的innodb_buffer_pool_size有1500MB,innodb_additional_mem_pool_size =20 MB,innodb_log_buffer_size = 8M,
 　　Innodb 将会向系统申请内存为= 1500M + 20M + 8M + 1500/20 M +17.5 = 1620.5M

　　根据以上的条件可以算出Innodb最根本最需要多少内存，这样对于服务器的内存使用也可以有一个规划了。

　作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。
　
      近段一直在一个项目中恶战，所以对于Blog更新慢了一点。该项目中使用了MySQL 5.1.X,使用这个版本是在我加入这个项目前就决定的。该项目基本上可以达到每秒3W的QPS(大多是基于主建的等于逻辑读写)记录都是比较长的。
近段遇到一些问题列举：
　　使用MySQL-5.1.31 进行数据迁移，从SQL SERVER到MySQL迁移，共享表空间，每表一个线程，一次从SQL SERVER读取20条记录写入MySQL,迁移完毕后一个大表巨然是只能读不能写了
关闭连接池程序，保持只有一个连接进入MySQL但对那个大表也无法进行update操作，可以进行insert操作。该表有差不多2亿的数据，当时那个无语真的没法说。最终解决方法，把该表
dump了出来，又导回去可以更新。
　　最新的业务上线后开着swap，没过几天就出现swap占用明显，DB反应慢的不能忍受。最终解决方法：禁用了swap分区。
　　因为truncate　table不能被复制及一系列问题，最终升级到mysql-5.1.31sp1（无语一个垃圾升级版本），我的意思当时升级到MySQL-5.1.37。这样就引出了另一外问题:Sort aborted,
内存溢出。以至于出现了几次严重的内存使用完毕后MySQLD被KILL掉，MySQLD进程重启，数据文件恢复造成Down机时间过长。巨汗的一次。
　　痛中思痛，最终把MySQL－5.1.41，现在看来内存正常了。使用中出现了一个更可怕的问题，对一个dump出来后，导入时对该表show create table不显示结果，按ctrl+c,MySQLD就Crash,巨汗。
　　万恶的MySQL-5.1.X,准备升级到MySQL-5.1.x的同学，还是我多思考一下吧。

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

  该文档只用于一般服务器的安装，其它专用服务器安装仅供参考。

系统安装步骤：
1)输入linux text选择text安装模式。
2)安装时语言环境选English。
3)键盘类型选us。
4)鼠标选择No-mouse。
5)安装类型选Custom。
6)分区设置为：
/dev/sda
/dev/sda1 512M ext3 /boot
/dev/sda2 5G ext3 /home
/dev/sda3 3G ext3 /
/dev/sda4 Extended
/dev/sda5 5G ext3 /var
/dev/sda6 2G ext3 swap
/dev/sda7 余空间 /data
7)使用GRUB Boot loader。
8)不增加参数在Boot Loader Configuration。
9)不为Boot Loader设置密码。
10)设置Boot Loader启动Linux。
11)将Boot Loader安装在硬盘的MBR。
12)网络设置，按分配的IP配置网卡。
/etc/sysconfig/network-scripts/
#ls ifcfg-*
ifcfg-eth0 ifcfg-eth1 ifcfg-lo
编辑相应文件
/sbin/service network restart

13)主机名称视情况而定，预定为WEB-数字,数字为ＩＰ最后３位。
14)防火墙的安全级别设为No firewall,禁用SEClinux
15)语言支持选English (USA) 和Chinese (P.R. of China)。
16)默认语言为English (USA)。
17)时区选Asia/Shanghai。
18)Root Password为：redhat
19)Authentication Configuration启用Use Shadow Passwords和Enable MD5 Passwords。
20)Package Group选择：
@ Editors
@ Text-based Internet
@ Server Configuration Tools
@ Development Tools
@ Kernel Development
@ Administration Tools
@ System Tools
21)不必创建Boot Diskette。
22)配置显示选项，指定启动时进入文本模式。
OS安装完毕。

安装后配置
1) 禁用ssh1登录
vi /etc/ssh/sshd_config
#Port 22
#Protocol 2,1
修改为
Port 22
Protocol 2
2) 禁用多于服务
rm /etc/rc.d/rc3.d/* -rf

chkconfig network on
chkconfig rsync on

chkconfig sshd on
chkconfig syslog on
chkconfig crond on
chkconfig xinetd on

根据需要加入自已的相应服务。
3）限制登录IP
vi /etc/hosts.allow
加入
all:ＩＰ.:allow
all:all:deny
ＩＰ为允许进入管理的ＩＰ。当然这个文件也可以不用。
4）定时同步时间
crontab -e
加入
0 0 * * * rdate -s time-a.nist.gov
or
10 03 * * * /usr/sbin/ntpdate -u tick.ucla.edu tock.gpsclock.com ntp.nasa.gov timekeeper.isi.edu usno.pa-x.dec.com
5）关闭ipv6
echo “alias net-pf-10 off” >> /etc/modprobe.conf.dist

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

　　MySQL创始人韦德纳斯(Michael Widenius)从MySQL被Sun收购后，就去了MariaDB工作。从MariaDB的介绍上总体来看应该也不错的。以后也要多关注一下这个数据库了。

　　可以关注的：
innodb-plugin
PBXT storage engine
XtraDB storage engine
Maria storage engine

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。
innodb-plugin 出现差不多有一年了。从功能上性能上都表现的不错。自MySQL-5.1.38后发行的版本中已包括了该功能。（推荐使用MySQL-5.1.40)

http://dev.mysql.com/doc/refman/5.1/en/innodb.html

http://planet.mysql.com/entry/?id=20926

该版本的特性:

http://www.innodb.com/wp/products/innodb_plugin/license/third-party-contributions-in-innodb-plugin-1-0-4/

Multiple Background Threads
==把后面进程的IO进程,细化并可以分配成多个.以前Linux下该IO只能是四个.现在可以最大调到64个.

Master Thread I/O Capacity Tuning
==内部IO限制.我们现在用的Innoddb内部有同时可以操作100个IO限制.这个限制对于现在高端的磁盘显的太少了

Asynchronous Read Ahead
==这是 Google and Percona 的一个Patch对增强MySQL的IO性能及Buffer中读取速度有所改善.

Group Commit
==该功能是MySQL一直支持的,但支持的不够好.在5.1.38的innodb-plugin-1.0.4中的支持是使用的percona的支持.把该性能支持的更好.

Adaptive Flushing
==自适应的刷新脏页,该功能也是来自percona的支持.对于Innodb checkpoint在原来的情况下在某种条件的触发下要进行一个checkpoint因为某些机制,有时并不能很好的完成,
出现系统的抖动现象.如:文件的锁问题,文件系统fsync一大片更新数据，对系统io冲击较大。若分隔成多个小数据fsync，能够减少对读的影响。同时结合mysql代码，发现mysql保证两次fsync之间至少有20ms的sleep，这样的话，若将一次fsync变成多次小数据操作，应该能够减少慢查询的比例。（从目前来看，杜绝是不太可能的）。这也是为什么近几年来percona,innodb的barrauda在推独表空间的一个原因吧.
对于该Patch的引入,它利用10%的IO去做checkpoint从而减少对系统的压力.

Additional Patches
==这部分是Sun的支持.加入了对Solaris的一些特别支持.
对我们有用的是: 对innodb spin loops做了更好的处理.增大了spin的值.
对DBA增加一个诱人的地方:
创建非cluster index时,不用是在Copy表这样一个复杂过程了.
另外:
加入了一种新的Innodb文件格式:barracuda ,据说该文件格式对性能提升很高.但要求使用独立表空间.

http://www.mysqlperformanceblog.com/2008/04/23/real-life-use-case-for-barracuda-innodb-file-format/

该功能需要加入innodb_file_format=barracuda ,并且需要在配置中文件中声明:innodb_file_per_table
所以需要使用该功能的朋友建义Dump出来数据后在[mysqld]中设置这两个参数后在导入:

[mysqld]
ignore_builtin_innodb
plugin_dir=/usr/local/mysql/lib/plugin
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
innodb_file_format=barracuda
innodb_file_per_table

Innodb plugin是一个比较让人企待的版本.其实这些功能基本上都被pernoca公司在mysql5.0中实现了.而且我以前使用相关的版本后都是表现良好的.
所以我觉的对于MySQL5.1.38可以导一份基于独立表空间的数据做一个对比.
文中提及的percona: http://www.percona.com/
Google: http://code.google.com/p/google-mysql-tools/wiki/Mysql5Patches
更多关于innodb 的信息可以参考:http://www.innodb.com

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

          bug说明： 该BUG在是MySQL5.1.X中存在的一个问题。
重现方法：
        利用 5.1.31-enterprise-gpl-pro-log (Or 5.1.31-sp1-enterprise) 搭建master/slave结构同步正常进行（确认同步进行）
 注意参数：
事务隔级为： READ-COMMITTED
日值格式为： mixed

然后在主库建表：

create database wubx;
create table t1 (id int) engine=innodb;
insert into t1 values(1),(2),(3),(4),(5);
select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+

从库：

use wubx;
select * from t1 ;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+

主库上：

use wubx;
mysql> truncate table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
Empty set (0.01 sec)

从库：

use wubx;
select * from t1 ;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+

解决办法：
先删除该表，然后创建该表。
如: truncate table wubx;改变为：

drop table wubx;
create table wubx( id int) engine=innodb;

另一种方式：
修改事务隔离级别为默认的。可以MySQL的版本升级到MySQL-5.1.37后的版本。