MYSQL 5.0.68 on RHEL 5u4
本实验主要是想看一下，INNODB 页何时被回收，何时被重新利用； 数据文件何时扩展，按多少比例扩展等；
[@more@]MYSQL 5.0.68 on RHEL 5u4
Innodb Record 组成 ：
---------------------------
Field Start Offsets ：(F*1) or (F*2) bytes ，F=字段个数
Extra Bytes ：6 bytes
Field Contents ：三个系统字段（ROWID,TXID（6B）,PID（7B） ） + 数据内容；
如果有主键，ROWID=PK；
Innodb PAGE 组成 (16K)：
---------------------------
Fil Header ： 38B
Page Header： 56B
The Infimum + Supremum Records : 15B
USER RECORD: (所有字段长度+系统字段)*行数
Free Space : ???
Page Directory : (主键长度+页内指针6B)*行数
Fil Trailer : 8B
PAGE 存储管理
---------------------
All InnoDB indexes are B-trees where the index records are stored in the leaf pages of the tree.
The default size of an index page is 16KB.
When new records are inserted, InnoDB tries to leave 1/16 of the page free for future insertions and updates of the index records.
If index records are inserted in a sequential order (ascending or descending),
the resulting index pages are about 15/16 full.
If records are inserted in a random order, the pages are from 1/2 to 15/16 full.
If the fill factor of an index page drops below 1/2, InnoDB tries to contract the index tree to free the page.
这里意思是INNODB会在DELETE后自动回收少于1/2满的页；
每个PAGE能存多少行数据？
---------------------------
假设一个表10个字段，每个字段字长100字节（主键也是100B）
所有字段占用1K字节；
我们根据以上的信息：
1）每行所占用空间：
10*1B + 6B + ( 13B+1000 ) = 1029B
2）Page Directory 每行占用：
100B + 6B = 106B
3) 每个页还要保留1/16 ，以作UPDATE操作；
MAX 行数 = [ (15*1024-38-56-15-8) *15/16 ] / (1029 + 106 ) ~= 13.4
新建一个表，看看相关SIZE数据：
---------------------------
create table h1 (
c0 char(100) primary key,
c1 char(100),
c2 char(100),
c3 char(100),
c4 char(100),
c5 char(100),
c6 char(100),
c7 char(100),
c8 char(100),
c9 char(100)
) engine=innodb default charset=latin1 ;
root@127.0.0.1 : test 14:55:10> show table status like 'h1' G
*************************** 1. row ***************************
Name: h1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384 ### 16K
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2010-11-04 14:55:00
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 0 kB
1 row in set (0.00 sec)
[root@test1 test]# ll
total 112
-rw-rw---- 1 mysql mysql 8790 Nov 4 14:55 h1.frm
-rw-rw---- 1 mysql mysql 98304 Nov 4 14:55 h1.ibd #### 96K
建表一个表，数据文件大小96K，但INNODB显示数据占用空间：16K；
前面的80K应该是数据文件的头部数据 ??
不对，经过下面的数据你会发现，INNODB数据状态显示空间使用超过48才进行数据文件的扩展 ;
所以这里猜测，数据文件的头部只占48K(3 pages)；
测试脚本,观察INNODB SIZE 变化
--------------------------
mysql -uroot -h127.0.0.1 test <drop table if exists h1 ;
create table h1 (
c0 char(100) primary key,
c1 char(100),
c2 char(100),
c3 char(100),
c4 char(100),
c5 char(100),
c6 char(100),
c7 char(100),
c8 char(100),
c9 char(100)
) engine=innodb default charset=latin1 ;
EOF
LOGFILE=/tmp/size.log
rm -f $LOGFILE
for SEQQ in $(seq -w 1 100000 );
do
VAL="aaa$SEQQ"
mysql -uroot -h127.0.0.1 test -e "insert into h1 values ('$VAL','$VAL','$VAL','$VAL','$VAL','v','$VAL','$VAL','$VAL','$VAL');"
MDL=$(mysql -uroot -h127.0.0.1 test -e "show table status like 'h1' G " | grep "Data_length" | tr -d ' ')
MFREE=$(mysql -uroot -h127.0.0.1 test -e "show table status like 'h1' G " | grep "Comment" | awk -F ":" '{print $NF}')
ODL=$(ls /home/mysql/data/mysql/test/h1.ibd -l | awk '{print $5}')
if [ "$PRE_MDL" != "$MDL" -o "$PRE_ODL" != "$ODL" ] ; then
printf "%8s ==== %-30s === os_size: %-20s === FREE:%-20s n" "$VAL" "$MDL" "$ODL" "$MFREE" >> $LOGFILE
fi
PRE_MDL=$MDL
PRE_ODL=$ODL
done
INNODB 文件管理，如何扩展
-----------------------
文档上说：
The tablespace consists of database pages with a default size of 16KB. The pages are grouped into extents of 64 consecutive pages.
When a segment grows inside the tablespace, InnoDB allocates the first 32 pages to it individually.
After that InnoDB starts to allocate whole extents to the segment
InnoDB can add to a large segment up to 4 extents at a time to ensure good sequentiality of data.
通过实验数据：
我们发现，数据文件的扩展按以下规则进行：
1） 按16K速度扩展，扩展32个16K （其中第1，2个16K一次扩展完成）
2） 按1M的速度扩展，扩展32个1M （其中第1，2次扩展有点奇怪？）
3） 按4M的速度扩展 ... ...
删除空间何时REUSE
------------------------
文档上说：
When you delete data from a table, InnoDB contracts the corresponding B-tree indexes.
Whether the freed space becomes available for other users depends on whether the pattern of deletes frees individual pages or extents to the tablespace.
Dropping a table or deleting all rows from it is guaranteed to release the space to other users,
but remember that deleted rows are physically removed only in an (automatic) purge operation after they are no longer needed for transaction rollbacks or consistent reads.
(See Section 12.2.12, “Implementation of Multi-Versioning”.)
这个问题要证明，思路是：
1）把一页灌满，让他扩展到第二页；
2）删除第一页的大部分数据，和第二页的前部分数据；
3）继续插看第二页满为止，看是不是会重复利用第一页数据；
mysql -uroot -h127.0.0.1 test <drop table if exists h1 ;
create table h1 (
c0 char(100) primary key,
c1 char(100),
c2 char(100),
c3 char(100),
c4 char(100),
c5 char(100),
c6 char(100),
c7 char(100),
c8 char(100),
c9 char(100)
) engine=innodb default charset=latin1 ;
EOF
LOGFILE=/tmp/size.log
rm -f $LOGFILE
for SEQQ in $(seq -w 1 66 );
do
VAL="aaa$SEQQ"
mysql -uroot -h127.0.0.1 test -e "insert into h1 values ('$VAL','$VAL','$VAL','$VAL','$VAL','v','$VAL','$VAL','$VAL','$VAL');"
MDL=$(mysql -uroot -h127.0.0.1 test -e "show table status like 'h1' G " | grep "Data_length" | tr -d ' ')
MFREE=$(mysql -uroot -h127.0.0.1 test -e "show table status like 'h1' G " | grep "Comment" | awk -F ":" '{print $NF}')
ODL=$(ls /home/mysql/data/mysql/test/h1.ibd -l | awk '{print $5}')
if [ "$PRE_MDL" != "$MDL" -o "$PRE_ODL" != "$ODL" ] ; then
printf "%8s ==== %-30s === os_size: %-20s === FREE:%-20s n" "$VAL" "$MDL" "$ODL" "$MFREE" >> $LOGFILE
fi
PRE_MDL=$MDL
PRE_ODL=$ODL
done
##### aaa01 ==== Data_length:16384 === os_size: 98304 === FREE: 0 kB
##### aaa15 ==== Data_length:49152 === os_size: 98304 === FREE: 0 kB
##### aaa22 ==== Data_length:65536 === os_size: 131072 === FREE: 0 kB
##### aaa36 ==== Data_length:81920 === os_size: 147456 === FREE: 0 kB
##### aaa50 ==== Data_length:98304 === os_size: 163840 === FREE: 0 kB
##### aaa64 ==== Data_length:114688 === os_size: 180224 === FREE: 0 kB
# DUMP(0) 显示数据分布如下：
PAGE4 == rec1 -- rec14
PAGE5 == rec08 -- rec21 (08--14 为什么存两次，不明白 ？)
PAGE6 == rec22 -- rec35
PAGE7 == rec36 -- rec49
PAGE8 == rec50 -- rec63
PAGE9 == rec64 -- rec66
在下列操作过程中，有不断的DUMP PAGES：
delete from h1 where c0='aaa36';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
delete from h1 where c0='aaa37';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
delete from h1 where c0='aaa38';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
delete from h1 where c0='aaa39';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
delete from h1 where c0='aaa40';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
delete from h1 where c0='aaa41';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
delete from h1 where c0='aaa42';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
delete from h1 where c0='aaa43';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
delete from h1 where c0='aaa44';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
delete from h1 where c0='aaa45';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
delete from h1 where c0='aaa46';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
delete from h1 where c0='aaa47';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
delete from h1 where c0='aaa48';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
# DUMP(1)
# 在这里我们发现，在PAGE6中，出现了aaa49这条记录，这说明INNODB已经开始合并页了
# DUMP(1) 显示数据分布如下：
#PAGE4 == rec1 -- rec14
#PAGE5 == rec08 -- rec21
#PAGE6 == rec22 -- rec35rec49
#PAGE7 == rec49
#PAGE8 == rec50 -- rec63
#PAGE9 == rec64 -- rec66
delete from h1 where c0='aaa49';select data_length,table_comment from information_schema.tables where table_name='h1';system ls -l /home/mysql/data/mysql/test/h1.ibd;
# DUMP(2)
#删除在这里时，发现DATAFILE扩展了16K，不知为何用。 data_length 缩小了16K
#而且PAGE7上的REC49没有消失（应该是没有被标志为删除）， 在PAGE6上的REC49上消失了，
#在这里我们可以猜测，PAGE7 已经被置为空闲页了；但行没有被标志为删除；
# DUMP(2) 显示数据分布如下：
#PAGE4 == rec1 -- rec14
#PAGE5 == rec08 -- rec21
#PAGE6 == rec22 -- rec35
#PAGE7 == rec49 这一行为什么一直留着？
#PAGE8 == rec50 -- rec63
#PAGE9 == rec64 -- rec66
#再插一些数据，让最后一页满,并满出一点； 看是不是会重用刚才空出来的PAGE7；
LOGFILE=/tmp/size.log
for SEQQ in $(seq -w 67 78 );
do
VAL="aaa$SEQQ"
mysql -uroot -h127.0.0.1 test -e "insert into h1 values ('$VAL','$VAL','$VAL','$VAL','$VAL','v','$VAL','$VAL','$VAL','$VAL');"
MDL=$(mysql -uroot -h127.0.0.1 test -e "show table status like 'h1' G " | grep "Data_length" | tr -d ' ')
MFREE=$(mysql -uroot -h127.0.0.1 test -e "show table status like 'h1' G " | grep "Comment" | awk -F ":" '{print $NF}')
ODL=$(ls /home/mysql/data/mysql/test/h1.ibd -l | awk '{print $5}')
if [ "$PRE_MDL" != "$MDL" -o "$PRE_ODL" != "$ODL" ] ; then
printf "%8s ==== %-30s === os_size: %-20s === FREE:%-20s n" "$VAL" "$MDL" "$ODL" "$MFREE" >> $LOGFILE
fi
PRE_MDL=$MDL
PRE_ODL=$ODL
done
### aaa64 ==== Data_length:114688 === os_size: 180224 === FREE: 0 kB
### aaa67 ==== Data_length:98304 === os_size: 196608 === FREE: 0 kB
### aaa78 ==== Data_length:114688 === os_size: 196608 === FREE: 0 kB
### 我们发现 ，Data_length 又增加了16K ，
### Dump（3） 发现多了一个新的PAGE:10 , 里面记录了刚刚满出的REC78
### 而PAGE7 里面的REC49 依旧存在；
# DUMP(3) 显示数据分布如下：
#PAGE4 == rec1 -- rec14
#PAGE5 == rec08 -- rec21
#PAGE6 == rec22 -- rec35
#PAGE7 == rec49
#PAGE8 == rec50 -- rec63
#PAGE9 == rec64 -- rec66 rec67-rec77
#PAGE10== rec78
flush tables ;
show innodb status G
---
LOG
---
Log sequence number 0 94049458
Log flushed up to 0 94049458
Last checkpoint at 0 94049458
###脏块已经全部刷下去了； 却看块信息，依然没有回收；
###再插一些数据，直到DATASIZE需要扩展，看是不是会重用刚才空出来的PAGE7；?
LOGFILE=/tmp/size.log
for SEQQ in $(seq -w 1 14 );
do
VAL="bbb$SEQQ"
mysql -uroot -h127.0.0.1 test -e "insert into h1 values ('$VAL','$VAL','$VAL','$VAL','$VAL','v','$VAL','$VAL','$VAL','$VAL');"
MDL=$(mysql -uroot -h127.0.0.1 test -e "show table status like 'h1' G " | grep "Data_length" | tr -d ' ')
MFREE=$(mysql -uroot -h127.0.0.1 test -e "show table status like 'h1' G " | grep "Comment" | awk -F ":" '{print $NF}')
ODL=$(ls /home/mysql/data/mysql/test/h1.ibd -l | awk '{print $5}')
if [ "$PRE_MDL" != "$MDL" -o "$PRE_ODL" != "$ODL" ] ; then
printf "%8s ==== %-30s === os_size: %-20s === FREE:%-20s n" "$VAL" "$MDL" "$ODL" "$MFREE" >> $LOGFILE
fi
PRE_MDL=$MDL
PRE_ODL=$ODL
done
### 又扩展了一个页： PAGE11,
# DUMP(4) 显示数据分布如下：
#PAGE4 == rec1 -- rec14
#PAGE5 == rec08 -- rec21
#PAGE6 == rec22 -- rec35
#PAGE7 == rec49
#PAGE8 == rec50 -- rec63
#PAGE9 == rec64 -- rec66 rec67-rec77
#PAGE10== rec78 rec b1-b13
#PAGE11== recb14
LOGFILE=/tmp/size.log
for SEQQ in $(seq -w 15 28 );
do
VAL="bbb$SEQQ"
mysql -uroot -h127.0.0.1 test -e "insert into h1 values ('$VAL','$VAL','$VAL','$VAL','$VAL','v','$VAL','$VAL','$VAL','$VAL');"
MDL=$(mysql -uroot -h127.0.0.1 test -e "show table status like 'h1' G " | grep "Data_length" | tr -d ' ')
MFREE=$(mysql -uroot -h127.0.0.1 test -e "show table status like 'h1' G " | grep "Comment" | awk -F ":" '{print $NF}')
ODL=$(ls /home/mysql/data/mysql/test/h1.ibd -l | awk '{print $5}')
if [ "$PRE_MDL" != "$MDL" -o "$PRE_ODL" != "$ODL" ] ; then
printf "%8s ==== %-30s === os_size: %-20s === FREE:%-20s n" "$VAL" "$MDL" "$ODL" "$MFREE" >> $LOGFILE
fi
PRE_MDL=$MDL
PRE_ODL=$ODL
done
### DATASIZE 扩展了： 212992 ， data_length 也扩展了： 147456
### 又多了一个PAGE：12
# DUMP(5) 显示数据分布如下：
#PAGE4 == rec1 -- rec14
#PAGE5 == rec08 -- rec21
#PAGE6 == rec22 -- rec35
#PAGE7 == rec49
#PAGE8 == rec50 -- rec63
#PAGE9 == rec64 -- rec66 rec67-rec77
#PAGE10== rec78 rec b1-b13
#PAGE11== recb14-- recb27
#PAGE12== recb28
### 从当前的测试来看，MYSQL并没有马上回收并重复利用“记录被全部删除”的PAGE；而是选择了新扩展；
### 或者说PAGE7还没有被全部释放 ；还没有在PAGE FREE LIST中；
### 插两条与REC49相近的记录，看是不是马上重新利用PAGE7
for SEQQ in $(seq -w 47 48 );
do
VAL="aaa$SEQQ"
mysql -uroot -h127.0.0.1 test -e "insert into h1 values ('$VAL','$VAL','$VAL','$VAL','$VAL','v','$VAL','$VAL','$VAL','$VAL');"
done
# DUMP(6) 显示数据分布如下：
#PAGE4 == reca1 -- reca14
#PAGE5 == reca08 -- reca21
#PAGE6 == reca22 -- reca35 reca47 ### 只插一条，MYSQL先在左侧页看有没有空间；如果有插入；
#PAGE7 == reca48 ### 已经重新利用；代表着PAGE7从未被回收过？ 还是又从FREE LIST 拿回来用了
#PAGE8 == reca50 -- reca63
#PAGE9 == reca64 -- reca66 reca67-reca77
#PAGE10== reca78 recb1-b13
#PAGE11== recb14 -- recb27
#PAGE12== recb28
### 测试到这里，还是留下了不少的疑问：
1) rec49已经被删除，为什么不置标志位？
2) PAGE7里的记录被删除完了，为何没有被置为空闲？ 是因为REC49还存在的缘故？
3) 部分数据为什么要存储两次，比如 a08-a14,有两个块（PAGE4,PAGE5）中都有?
这些问题先留着，等待解决；
### 附：Data_length 与 os_size 的变化情况；
aaa01 ==== Data_length:16384 === os_size: 98304 === FREE: 0 kB
aaa15 ==== Data_length:49152 === os_size: 98304 === FREE: 0 kB
aaa22 ==== Data_length:65536 === os_size: 131072 === FREE: 0 kB
aaa36 ==== Data_length:81920 === os_size: 147456 === FREE: 0 kB
aaa50 ==== Data_length:98304 === os_size: 163840 === FREE: 0 kB
aaa64 ==== Data_length:114688 === os_size: 180224 === FREE: 0 kB
aaa67 ==== Data_length:98304 === os_size: 196608 === FREE: 0 kB
aaa78 ==== Data_length:114688 === os_size: 196608 === FREE: 0 kB
bbb14 ==== Data_length:131072 === os_size: 196608 === FREE: 0 kB
bbb28 ==== Data_length:147456 === os_size: 212992 === FREE: 0 kB
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/703656/viewspace-1041141/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/703656/viewspace-1041141/
扫一扫
587
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
