pt-online-schema-change 清理数据库磁盘碎片和在线更改表结构

一、表碎片产生的原因

因为使用delete删除数据的时候，MySQL并不会把数据文件真实删除，而只是将数据文件的标识位删除，也没有整理数据文件，因此不会彻底释放表空间。换句话说，每当我们从表中删除数据时，这段被删除数据的空间就会被留出来，如果又赶上某段时间内该表进行大量的delete操作，则这部分被删除数据的空间就会越来越大。当有新数据写入时，MySQL会再次利用这些被删除的区域，但也无法彻底占用。

二、碎片的计算方法

mysql> show table status like "%employees%"\G;
*************************** 1. row ***************************
Name: employees
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 299335
Avg_row_length: 50
Data_length: 15220736
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2018-07-17 13:12:08
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: 
Comment: 
1 row in set (0.01 sec)

(1)碎片大小=数据总大小-实际表空间文件大小

(2)数据总大小=data_length+index_length=15220736

(3)实际表空间文件大小=rows*avg_rog_length=299335*50=14966750

(4)碎片大小=（15220736-14966750)/1024/1024=0.2M

 

三、清除碎片的三种方法

1. alter table table_name engine=innodb

2. optimize table table_name

3. pt-online-schema-change

前两种方式会锁表，这里介绍第三种方式，在执行过程中不会锁表

pt-online-schema-change  
  -h地址
  -P端口号
  -u用户名
  -p密码   
  --database=数据库
  t=表名字
  --charset=utf8 
  --max-lag=300 
  --check-interval=5 
  --alter="ENGINE=InnoDB" 
  --max-load="Threads_running:400" 
  --critical-load="Threads_running:400" 
  --nocheck-replication-filters 
  --alter-foreign-keys-method=auto  
  --execute

#!/bin/bash
table=$1

conn_host=''
conn_user=''
conn_pwd=''
conn_db=''

startTime=`date +%s`
s_size=$(mysql -u$conn_user -p$conn_pwd -P3306 -h$conn_host information_schema -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES where table_schema=\"$conn_db
\" and table_name=\"$table\"")
startSize=`echo $s_size|awk '{print $2}'`

echo "开始清理$table .................................."

pt-online-schema-change  --user=${conn_user} --password=${conn_pwd} --host=${conn_host}  P=3306,D=${conn_db},t=$table --charset=utf8 --alter="ENGINE=InnoDB"  --nocheck-replication-filters -
-alter-foreign-keys-method=auto  --execute > /data/backup/pt.log

endTime=`date +%s`
e_size=$(mysql -u$conn_user -p$conn_pwd -P3306 -h$conn_host information_schema -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES where table_schema=\"$conn_db
\" and table_name=\"$table\"")
endSize=`echo $e_size|awk '{print $2}'`

sumTime=$[ $endTime - $startTime ]

echo "$table清理完成, 整理前:${startSize}, 整理后:${endSize},耗时:$sumTime秒"

四、遇到的坑

     1. 磁盘空间差点撑爆。 因为这三种操作都是先创建一个临时表复制完成后再删除旧表，所以在执行操作的过程中磁盘会先增           大。

     2. 执行pt命令报，Found 1 slaves: Use of uninitialized value in printf at /usr/local/percona-toolkit/bin/pt-online-schema-change line 8489。 注释修改8489行部分代码，再执行就成功了

 

五、在线改表结构

     例: 增加索引。 --alter="语句(语句不用写表名)"

pt-online-schema-change  --user=${conn_user} --password=${conn_pwd} --host=${conn_host} P=3306,D=${conn_db},t=$table --charset=utf8 --alter="add index status (status)"  --nocheck-replication-filters --alter-foreign-keys-method=auto  --execute >/data/backup/pt.log

 

 

 

友情链接: https://www.cnblogs.com/zhoujinyi/p/3491059.html