Chinaunix首页 | 论坛 | 博客
  • 博客访问: 526255
  • 博文数量: 100
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1172
  • 用 户 组: 普通用户
  • 注册时间: 2016-09-22 09:50
个人简介

一个守望数据库的老菜鸟

文章分类

全部博文(100)

文章存档

2019年(21)

2018年(17)

2017年(38)

2016年(24)

我的朋友

分类: Mysql/postgreSQL

2016-10-09 11:20:19

博客文章除注明转载外,均为原创。转载请注明出处。
本文链接地址:http://blog.chinaunix.net/uid-31396856-id-5752985.html


1、相关表信息:
+------------------------+----------------+-----------+------------+------------+
| Table Name             | Number of Rows | Data Size | Index Size | Total      |
+------------------------+----------------+-----------+------------+------------+
| emoprod.transfe |        3542930 | 881.5781M | 2029.9688M | 2911.5469M |
+------------------------+----------------+-----------+------------+------------+
1 row in set (0.00 sec)
发现索引比表还大2倍多呢?transfe表是业务的主要表之一,其特点是:DML频繁,和索引比较多。

mysql> 
mysql> show table status from emoprod like 'transfe';
+----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| transfe | InnoDB |      10 | Compact    | 3543033 |            260 |   924401664 |               0 |   2128576512 |   7340032 |           NULL | 2016-09-25 17:43:44 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

表实际占用的空间:
[root@emo-mysql-02 emoprod]# ls -lh |grep transfe
-rw-r----- 1 mysql mysql  14K Sep 25 17:39 transfe.frm
-rw-r----- 1 mysql mysql 5.6G Sep 28 16:03 transfe.ibd

2、开始碎片整理:
mysql> show table status from emoprod like 'transfe'\G
*************************** 1. row ***************************
           Name: transfe
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3543053
 Avg_row_length: 260
    Data_length: 924401664
Max_data_length: 0
   Index_length: 2128576512
      Data_free: 7340032
 Auto_increment: NULL
    Create_time: 2016-09-25 17:43:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

进行碎片整理:
mysql> optimize table transfe;
+------------------------+----------+----------+-------------------------------------------------------------------+
| Table                  | Op       | Msg_type | Msg_text                                                          |
+------------------------+----------+----------+-------------------------------------------------------------------+
| emoprod.transfe | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| emoprod.transfe | optimize | status   | OK                                                                |
+------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (5 min 15.06 sec)


观察数据库
+------+-------------+-----------+---------------+---------+--------+-----------------------------------------------------------------------------+-------------------------+
| Id   | User            | Host      | db            | Command | Time   | State                                                                       | Info                    |
+------+-------------+-----------+---------------+---------+--------+-----------------------------------------------------------------------------+-------------------------+
|    2 | system user |           | NULL          | Connect | 253445 | Waiting for master to send event                                            | NULL                    |
|    3 | system user |           | NULL          | Connect |      0 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL                    |
| 1180 | root        | localhost | emoprod | Query   |    281 | altering table                                                              | optimize table transfe |
| 1271 | root        | localhost | NULL          | Query   |      0 | init                                                                        | show processlist        |
+------+-------------+-----------+---------------+---------+--------+-----------------------------------------------------------------------------+-------------------------+
4 rows in set (0.00 sec)


mysql> select * from  information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1918481819
                 trx_state: RUNNING
               trx_started: 2016-09-28 16:06:22
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 1180
                 trx_query: optimize table transfe
       trx_operation_state: NULL
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 0
     trx_lock_memory_bytes: 360
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)


mysql>  show table status from emoprod like 'transfe'\G
*************************** 1. row ***************************
           Name: transfe
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 5312336
 Avg_row_length: 200
    Data_length: 1066401792
Max_data_length: 0
   Index_length: 2961178624
      Data_free: 6291456
 Auto_increment: NULL
    Create_time: 2016-09-28 16:11:38
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

整理后:
[root@emo-mysql-02 emoprod]# ls -lh |grep transfe
-rw-rw---- 1 mysql mysql  14K Sep 28 16:06 transfe.frm
-rw-rw---- 1 mysql mysql 2.9G Sep 28 16:11 transfe.ibd

表文件从5.6G下降到2.9G,整理完成
注:MySQL碎片整理在5.6的时候,注意观察锁,建议在业务低峰期进行整理。
---the end
阅读(2602) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~