swap分析及行溢出分析ml

swap out:把内存中暂时不用的数据交换出去，放在swap分钟

swap in：把swap分区中的数据交换会物理内存中

vmstat -S m 1

[root@nod2 ~]# vmstat -S m 1

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----

 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st

 0  0     33  21278    211   1978    0    0     2     5    0    0  1  0 99  0  0

 1  0     33  21278    211   1978    0    0     0    68 1079 1792  0  0 99  0  0

 1  0     33  21278    211   1978    0    0     0    24  987 1613  0  0 100  0  0

 0  0     33  21277    211   1978    0    0     0    64  935 1570  0  0 100  0  0

 0  0     33  21277    211   1978    0    0     0    64 1108 1843  0  0 99  0  0

 1  0     33  21277    211   1978    0    0     0    40  842 1390  0  0 100  0  0

 0  0     33  21276    211   1978    0    0     0    24 1086 1586  0  0 100  0  0

 0  0     33  21276    211   1978    0    0     0    80  979 1658  0  0 99  1  0

发生的原因：

最直接原因可能是进程向OS申请内存时，发现物理内存不足:这时会一直等待(dirty page未flush)，swap,可能发生oom-killer机制

和内核选项vm.swappiness有关系，参数可选范围从0--100，设置为0就是希望最大使用物理内存，尽量不使用SWAP，设置为100，则希望积极使用SWAP

数据库可以设置成为5到10范围。若有高可用，直接设置为0

[root@nod2 ~]# top

top - 10:30:07 up 7 days, 16:55,  3 users,  load average: 0.11, 0.12, 0.09

Tasks: 335 total,   1 running, 334 sleeping,   0 stopped,   0 zombie

Cpu(s):  0.5%us,  0.1%sy,  0.0%ni, 99.2%id,  0.2%wa,  0.0%hi,  0.0%si,  0.0%st

Mem:  24591004k total,  3812944k used, 20778060k free,   206792k buffers

Swap:  4194300k total,    419400k used,  300k free,  1932400k cached

1、物理内存有空闲，但使用了SWAP

查看物理内存分配free -gt 

[root@nod2 ~]# free -gt

 total       used       free     shared    buffers#缓冲写入的内存快（脏数据）     cached#缓存热数据 used=shared+buffers+cached

Mem:            23          3         19          0          0          1

-/+ buffers/cache:          1         21

Swap:            3          3          0

Total:          27          3         23

1、典型内存泄露（memory leak），cached和used相差特别大，基本可以确定系统发生ml

解决办法：治标：重启机器

治本：升级版本、提交BUG

发生SWAP原因：

内存不够，IBP分配够大，numa设置问题

查看numa相关情况：

numactl --show

如何避免numa

1、在BIOS设置层面关闭numa,缺点需要重启os

2、修改grup配置文件，重启OS

3、升级MySQL版本到5.6.27版本，新增了一个选项innodb_numa_interleave,

正确认识InnoDB

基于B+树结构的聚集索引组织表

   聚集索引优先选择显示定义的主键

   其次选择第一个非NULL的唯一索引

   再次使用隐藏的ROWID

   聚集索引叶子节点存储整行数据

InnoDB默认是行锁，是在索引记录上加锁实现行锁机制

innodb表都要有一个主键，且主键最好没有业务用途，不要修改主键值

主键最好保持顺序递增，随机主键值会导致聚集索引树频繁分裂，随机I/O增多，数据离散，性能下降

没有索引的更新，可能会导致全表数据都被锁住，和表级锁等同后果

定义列属性时，长度预估够用就好，没必要用特别大的数据类型。

varchar/text等数据类型实际存储长度越小越好，否则发生行溢出（off-page-storage）时对性能影响可能很大

不超过255字节的，

页（PAGE）是innoDB存储引擎的最小存储单位，默认大小为16KB，及16384字节，行数据存储在页中

一行数据为多大时，会发生行溢出呢？我们知道InnoDB存储引擎表是索引组织的，即B+树结构，这样一个页中至少要保证有2条数据，否则就变成链表了，如果只能存放一条数据，

那么InnoDB存储引擎会自动将它存放在溢出页中。如果可以在一个页中至少放入两行数据，那么就不会发生行溢出

其实对于BLOB类型的数据，跟varchar也是一个道理，要看实际的大小，当然，用户既然使用了blob列类型，一般不可能存放长度过小的数据，因此在大多数情况下BLOB的行数据还是会发生行溢出，

实际数据保存在BLOB页中，数据页只保存数据的前768字节

不要直接select * 读取全部列，可能会导致更多的I/O读

表空间：INNODB 所有数据都存在表空间当中（共享表空间），要是开启innodb_file_per_table，则每张表的数据会存到单独的一个表空间内（独享表空间）。

独享表空间包括：数据，索引，插入缓存，数据字典。共享表空间包括：Undo信息（不会回收<物理空间上>），双写缓存信息，事务信息等。

段（segment）：组成表空间，有区组成。

区（extent）：有64个连续的页组成。每个页16K，总共1M。对于大的数据段，每次最后可申请4个区。

页（page）：是INNODB 磁盘管理的单位，有行组成。

行（row）：包括事务ID，回滚指针，列信息等。

#####

测试表空间

表空间各个页的信息和溢出行数据存储的信息。通过该书作者蒋承尧编写的工具

首先把3个脚本的放在同一个目录下

http://down.51cto.com/data/2264077#脚本

测试1：

create table tt(id int auto_increment,name varchar(10),age int,address varchar(20),primary key (id))engine=innodb;

然后查看tt表ibd的信息

[root@nod1 dbtest]# python py_innodb_page_info.py /home/mysql3306/mysql3306/test/tt.ibd -v

page offset 00000000, page type <File Space Header>

page offset 00000001, page type <Insert Buffer Bitmap>

page offset 00000002, page type <File Segment inode>

page offset 00000003, page type <B-tree Node>, page level <0000> ##叶子节点

page offset 00000000, page type <Freshly Allocated Page>

page offset 00000000, page type <Freshly Allocated Page>

Total number of page: 6:  #总页数

Freshly Allocated Page: 2 #可用页数

Insert Buffer Bitmap: 1   #插入缓存位图页

File Space Header: 1      

B-tree Node: 1            #数据节点 

File Segment inode: 1     

上面得到的信息是表初始化大小为96K，他是有 Total number of page * 16 得来的。1个数据页，2个可用页面。

区是64个连续的页，大小1M。那么表大小也应该是至少1M。但是现在只有96K（默认）。原因是因为每个段开始的时候，

先有32个页大小的碎片页存放数据，使用

完之后才是64页的连续申请，最多每次可以申请4个区，保证数据的顺序。

这里看出表大小增加是按照至少64页的大小的空间来增加的，即1M增加。

[root@nod1 dbtest]# python py_innodb_page_info.py /home/mysql3306/mysql3306/test/tt.ibd -v

page offset 00000000, page type <File Space Header>

page offset 00000001, page type <Insert Buffer Bitmap>

page offset 00000002, page type <File Segment inode>

page offset 00000003, page type <B-tree Node>, page level <0000>

page offset 00000000, page type <Freshly Allocated Page>

page offset 00000000, page type <Freshly Allocated Page>

Total number of page: 6:

Freshly Allocated Page: 2

Insert Buffer Bitmap: 1

File Space Header: 1

B-tree Node: 1

File Segment inode: 1

溢出行数据存放：INNODB存储引擎是索引组织的，即每页中至少有两行记录，

因此如果页中只能存放一行记录，INNODB会自动将行数据放到溢出页中。当发生溢出行的时候，实际数据保存在BLOB页中，数据页只保存数据的前768字节（老的文件格式），新的文件格式（Barracuda）采用完全行溢出的方式

，数据页只保存20个字节的指针，BLOB也保存所有数据。如何查看表中有溢出行数据呢？

create table t1 (id int,name varchar(10),memo varchar(8000))engine =innodb default charset utf8;

insert into t1 values(1,'zjy',repeat('我',8000));

[root@nod1 dbtest]# python py_innodb_page_info.py /home/mysql3306/mysql3306/test/t1.ibd -v

page offset 00000000, page type <File Space Header>

page offset 00000001, page type <Insert Buffer Bitmap>

page offset 00000002, page type <File Segment inode>

page offset 00000003, page type <B-tree Node>, page level <0000>

page offset 00000004, page type <Uncompressed BLOB Page>

page offset 00000005, page type <Uncompressed BLOB Page>

Total number of page: 6:

Insert Buffer Bitmap: 1

Uncompressed BLOB Page: 2

File Space Header: 1

B-tree Node: 1

File Segment inode: 1

从信息中看到，刚才插入的一行记录，已经溢出了，保存到了2个BLOB页中（<Uncompressed BLOB Page>）。

因为1页只有16K，又要存2行数据，所以每行记录最好小于8K，

而上面的远远大于8K，所以被溢出了。当然这个也不是包括特大字段，要是一张表里面有5个字段都是varchar(512)

【多个varchar的总和大于8K就可以】，也会溢出：

1000+500+500+500+500+500=3500*3>8000字节；行会被溢出

root@nod1 dbtest]# python py_innodb_page_info.py /home/mysql3306/mysql3306/test/t2.ibd -v

page offset 00000000, page type <File Space Header>

page offset 00000001, page type <Insert Buffer Bitmap>

page offset 00000002, page type <File Segment inode>

page offset 00000003, page type <B-tree Node>, page level <0000>

page offset 00000004, page type <Uncompressed BLOB Page>

page offset 00000000, page type <Freshly Allocated Page>

Total number of page: 6:

Insert Buffer Bitmap: 1

Freshly Allocated Page: 1

File Segment inode: 1

B-tree Node: 1

File Space Header: 1

Uncompressed BLOB Page: 1

<Uncompressed BLOB Page> 页存放真正的数据，那数据页到底存放什么？用hexdump查看：

 hexdump -C -v /home/mysql3306/mysql3306/test/t1.ibd >t2.txt

目的2：

     了解表空间如何存储数据，以及对NULL值的存储。

在测试前先了解INNODB的存储格式（row_format）。老格式（Antelope）：Compact<默认>，Redumdant；新格式（Barracuda）：Compressed ，Dynamic。

这里测试指针对默认的存储格式。

Compact行记录方式如下:

|变长字段长度列表（1~2字节）|NULL标志位（1字节）|记录头信息（5字节）|RowID（6字节）|事务ID（6字节）|回滚指针（7字节）|

上面信息除了 "NULL标志位"[表中所有字段都定义为NOT NULL]，"RowID"[表中有主键] ，"变长字段长度列表" [没有变长字段] 可能不存在外，其他信息都会出现。

、所以一行数据除了列数据所占用的字段外，还需要额外18字节。

一:字段全NULL

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table mytest(t1 varchar(10),t2 varchar(10),t3 varchar(10) ,t4 varchar(10))engine=innodb charset = latin1 row_format=compact;

Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytest values('a','bb','bb','ccc');

Query OK, 1 row affected (0.00 sec)

mysql> insert into mytest values('a','ee','ee','fff');

Query OK, 1 row affected (0.01 sec)

mysql>  insert into mytest values('a',NULL,NULL,'fff');

Query OK, 1 row affected (0.00 sec)

测试数据准备完之后，执行shell命令：

3080 0000c070  73 75 70 72 65 6d 75 6d   03 02 02 01 00 00 00 10  |supremum........|

3081 0000c080 00 25 00 00 00 00 02 05  00 00 00 01 3c ac b1 00  |.%..........<...|

3082 0000c090  00 01 6b 01 10 61 62 62  62 62 63 63 63 03 02 02  |..k..abbbbccc...|

3083 0000c0a0  01 00 00 00 18 00 23 00  00 00 00 02 06 00 00 00  |......#.........|

3084 0000c0b0  01 3c ad b2 00 00 01 db  01 10 61 65 65 65 65 66  |.<........aeeeef|

3085 0000c0c0  66 66 03 01 06 00 00 20  ff a6 00 00 00 00 02 07  |ff..... ........|

3086 0000c0d0  00 00 00 01 3c b2 b5 00  00 02 fe 01 10 61 66 66  |....<........aff|

3087 0000c0e0  66 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |f...............|

3088 0000c0f0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|

第一行数据:

03 02 02 01 /*变长字段*/ ---- 表中4个字段类型为varchar，并且没有NULL数据，而且每个字段君小于255。

00 /*NULL标志位，第一行没有null的数据*/

00 00 10 00 25 /*记录头信息,固定5个字节*/

00 00 00 00 02 05 /*RowID,固定6个字节,表没有主键*/

00 00 00 01 3c ac /*事务ID,固定6个字节*/

b1 00 00 01 6b 01 10 /*回滚指针,固定7个字节*/

61 62 62  62 62 63 63 63 /*列的数据*/

第二行数据和第一行数据一样（颜色匹配）。

第三行数据（有NULL值）和第一行的解释的颜色对应起来比较差别:

03 02 02 01  VS  03 01   ----------当值为NULL时，变长字段列表不会占用存储空间。

61 62 62  62 62 63 63 63 VS 61 66 66 66  --------- NULL值没有存储，不占空间

结论：当值为NULL时，变长字段列表不会占用存储空间。NULL值没有存储，不占空间，但是需要一个标志位（一行一个）。