用pt-osc回收表碎片,非但没回收,还变大了,变大了,大了,了。。。

 表结构:
CREATE TABLE `OrderInfoTM` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `tid` varchar(100) DEFAULT NULL ,
  `addTime` datetime DEFAULT NULL ,
  `jdp_response` mediumtext ,
  `configId` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `OrderInfoTM_tid_uindex` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=2771948 DEFAULT CHARSET=utf8 

表统计信息:
 show table status like 'OrderInfoTM'\G
*************************** 1. row ***************************
           Name: OrderInfoTM
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 969536
 Avg_row_length: 20123
    Data_length: 19510853632
Max_data_length: 0
   Index_length: 86753280
      Data_free: 6291456
 Auto_increment: 2713890
    Create_time: 2018-07-20 14:22:05
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
    
    
Data_length + Index_length: 
select (86753280+19510853632)/1024/1024/1024;
+---------------------------------------+
| (86753280+19510853632)/1024/1024/1024 |
+---------------------------------------+
|                       18.251693725586 |
+---------------------------------------+      
        
实际空间占用:        
du -sh  OrderInfoTM*
12K     OrderInfoTM.frm
29G     OrderInfoTM.ibd       
        
        
说明碎片10G左右。
        
用pt-osc回收碎片:
pt-online-schema-change -hxxxx  -Pxxxx  -uxxxx -p'xxxx'  --alter "ENGINE=InnoDB"   D=xxxx,  t=OrderInfoTM  --execute --charset=utf8  --nocheck-replication-filters --max-load="Threads_running=20"        
        
No slaves found.  See --recursion-method if host cbssql01 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 together with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /bin/pt-online-schema-change line 6576.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 together with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /bin/pt-online-schema-change line 6576.

# A software update is available:
#   * The current version for Percona::Toolkit is 3.0.5

Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `base_system`.`OrderInfoTM`...
Creating new table...
Created new table base_system._OrderInfoTM_new OK.
Altering new table...
Altered `base_system`.`_OrderInfoTM_new` OK.
2018-07-20T14:25:57 Creating triggers...
2018-07-20T14:25:57 Created triggers OK.
2018-07-20T14:25:57 Copying approximately 969536 rows...
Copying `base_system`.`OrderInfoTM`:  10% 04:04 remain
Copying `base_system`.`OrderInfoTM`:  22% 03:29 remain
Copying `base_system`.`OrderInfoTM`:  33% 02:58 remain
Copying `base_system`.`OrderInfoTM`:  44% 02:29 remain
Copying `base_system`.`OrderInfoTM`:  55% 02:01 remain
Copying `base_system`.`OrderInfoTM`:  65% 01:33 remain
Copying `base_system`.`OrderInfoTM`:  76% 01:05 remain
Copying `base_system`.`OrderInfoTM`:  86% 00:38 remain
Copying `base_system`.`OrderInfoTM`:  95% 00:13 remain
2018-07-20T14:37:35 Copied rows OK.
2018-07-20T14:37:35 Swapping tables...
2018-07-20T14:37:36 Swapped original and new tables OK.
2018-07-20T14:37:36 Dropping old table...
2018-07-20T14:37:38 Dropped old table `base_system`.`_OrderInfoTM_old` OK.
2018-07-20T14:37:38 Dropping triggers...
2018-07-20T14:37:38 Dropped triggers OK.
Successfully altered `base_system`.`OrderInfoTM`.

 
回收之后表的统计信息: 
mysql>  show table status like 'OrderInfoTM'\G
*************************** 1. row ***************************
           Name: OrderInfoTM
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 363925
 Avg_row_length: 148571
    Data_length: 54068772864
Max_data_length: 0
   Index_length: 138297344
      Data_free: 7340032
 Auto_increment: 2771970
    Create_time: 2018-07-20 14:37:36
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment:         

实际占用空间:
du -sh  OrderInfoTM*
12K     OrderInfoTM.frm
52G     OrderInfoTM.ibd 


回收前29G,回收后52G,纳尼???




 
已邀请:

yejr

赞同来自:

好悲催。
Avg_row_length: 20123 => 148571
           Rows: 969536  => 363925 (表数据量都变少了?是不是哪里有问题)不用osc,手动复制到一个新的临时表,optimize table试试看呢?

要回复问题请先登录注册