内容如下,mysql手册中关于 OPTIMIZE 的描述

MySQL表碎片整理

  • 1. 计算碎片大小
  • 2. 整理碎片
    • 2.1 使用alter table table_name engine = innodb命令进行整理。
    • 2.2 使用pt-online-schema-change工具也能进行在线整理表结构,收集碎片等操作。
    • 2.3 使用optimize table命令,整理碎片。
  • 3. 整理表碎片shell脚本

由于公司数据库中的数据量较大,定期对公司的mysql数据库中的数据表进行优化操作(关于optimize的描述如下所示),数据库中有300多张数据表,手工去操作显然不太现实,用脚本来执行效率还是很不错的,脚本如下:

生产环境MySQL表的维护:check、optimize和analyze

这里在Linux下使用到crontab定时任务的添加及mysqldump执行简单的数据库备份,具体步骤如下:

1)小型监控:
1.在pg库主机上部署,每5分钟执行一次,插入到我的测试pg库内
[root@mysqltest tina_shell]# cat jk_pg.sh
#!/bin/bash
#适用于中转库192.168.12.8和12.2
running_port=`netstat -nat|grep "LISTEN"|grep "5432"|sed -n 2p|awk -F : '{print $4}'`
jk_host=`ifconfig |grep "inet addr:192.168"|awk '{print $2}'|awk -F : '{print $2}'`
record_time=`date +"%Y-%m-%d %H:%M:%S"`
waiting_count=`ps -ef|grep postgres|grep -v startup |grep waiting|wc -l`
streaming=`ps -ef|grep wal|grep streaming |awk '{print $15}'`
#tbjk=`ps -ef|grep postgres|grep startup|grep waiting|wc -l`
cipan=`df -ah |grep % |grep -v tmpfs|grep -v boot`
usersum=`ps -ef|grep postgres |grep -E "engine|fenxi|sqluser" |wc -l`

1. 计算碎片大小

要整理碎片,首先要了解碎片的计算方法。

可以通过show table [from|in db_name] status like '%table_name%'命令查看:

mysql> show table from employees status like 't1'G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1176484
 Avg_row_length: 86
    Data_length: 101842944
Max_data_length: 0
   Index_length: 0
      Data_free: 39845888
 Auto_increment: NULL
    Create_time: 2018-08-28 13:40:19
    Update_time: 2018-08-28 13:50:43
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

 

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

  • 数据总大小 = Data_length + Data_length = 101842944

  • 实际表空间文件大小 = rows * Avg_row_length = 1176484 * 86 = 101177624

  • 碎片大小 = (101842944 - 101177624) / 1024 /1024 = 0.63MB

通过information_schema.tablesDATA_FREE列查看表有没有碎片:

SELECT t.TABLE_SCHEMA,
       t.TABLE_NAME,
       t.TABLE_ROWS,
       t.DATA_LENGTH,
       t.INDEX_LENGTH,
       concat(round(t.DATA_FREE / 1024 / 1024, 2), 'M') AS datafree
FROM information_schema.tables t
WHERE t.TABLE_SCHEMA = 'employees'


+--------------+--------------+------------+-------------+--------------+----------+
| TABLE_SCHEMA | TABLE_NAME   | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | datafree |
+--------------+--------------+------------+-------------+--------------+----------+
| employees    | departments  |          9 |       16384 |        16384 | 0.00M    |
| employees    | dept_emp     |     331143 |    12075008 |     11567104 | 0.00M    |
| employees    | dept_manager |         24 |       16384 |        32768 | 0.00M    |
| employees    | employees    |     299335 |    15220736 |            0 | 0.00M    |
| employees    | salaries     |    2838426 |   100270080 |     36241408 | 5.00M    |
| employees    | t1           |    1191784 |    48824320 |     17317888 | 5.00M    |
| employees    | titles       |     442902 |    20512768 |     11059200 | 0.00M    |
| employees    | ttt          |          2 |       16384 |            0 | 0.00M    |
+--------------+--------------+------------+-------------+--------------+----------+
8 rows in set (0.00 sec)

 

mysql手册中关于 OPTIMIZE 的描述:

 

1.      编写一个脚本: /serverBack/autobackmysql.sh

#echo $jk_host $record_time $waiting_count $streaming $tbjk >>/tmp/pg_check_state.log
psql -h 192.168.12.31 -U postgres -p 1922 -d tina -c "insert into jk_pg(jk_host,record_time,waiting_count,streaming,running_port,cipan,usersum) values('$jk_host','$record_time','$waiting_count','$streaming','$running_port','$cipan','$usersum');"

2. 整理碎片

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …

 ㈠ optimize

内容如下:

2.部署crontab
cat /etc/crontab
0 20 * * * root sh /tina_shell/backup.sh
4 * * * * root sh /tina_shell/pg_delete_archivelog.sh
*/5 * * * * root sh /tina_shell/jk_pg.sh

2.1 使用alter table table_name engine = innodb命令进行整理。

 root@localhost [employees] 14:27:01> alter table t1   engine=innodb;

 Query OK, 0 rows affected (5.69 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 root@localhost [employees] 14:27:15> show table status like 't1'G
 *************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1191062
 Avg_row_length: 48
    Data_length: 57229312
Max_data_length: 0
   Index_length: 0
      Data_free: 2097152
 Auto_increment: NULL
    Create_time: 2018-08-28 14:27:15
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
 1 row in set (0.00 sec)

 

如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使用

        

方法一:

3.建表
CREATE TABLE jk_pg
(
  id serial NOT NULL,
  jk_host character varying, -- 监控主机的ip地址
  record_time timestamp without time zone, -- 监控的时间
  waiting_count integer, -- 发生waiting等待的进程数ps -ef|grep postgres|grep -v startup |grep waiting|wc -l
  streaming character varying, -- 正在进行同步的日志ps -ef|grep wal|grep streaming |awk '{print $13}'
  usersum integer, -- 当前连接用户总数(sqluser、engine、fenxi)
  tbjk integer, -- ps -ef|grep postgres|grep startup|grep waiting|wc -l
  running_port integer, -- 检测pg运行是否正常,如果没有显示5432端口,那pg就挂了
  cipan character varying, -- 磁盘情况
  locks character varying, -- 锁表情况
  beizhu character varying -- 填写一些异常的备注
)
WITH (
  OIDS=FALSE
);
COMMENT ON TABLE jk_pg  IS '自制监控表-tina';

2.2 使用pt-online-schema-change工具也能进行在线整理表结构,收集碎片等操作。

 [root@mysqldb1 14:29:29 /root]
 # pt-online-schema-change --alter="ENGINE=innodb" D=employees,t=t1 --execute
 Cannot chunk the original table `employees`.`t1`: There is no good index and the table is oversized. at /opt/percona-toolkit-3.0.11/bin/pt-online-schema-change line 5852.

 

 需表上有主键或唯一索引才能运行

 [root@mysqldb1 14:31:16 /root]
# pt-online-schema-change --alter='engine=innodb' D=employees,t=salaries --execute
No slaves found.  See --recursion-method if host mysqldb1 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `employees`.`salaries`...
Creating new table...
Created new table employees._salaries_new OK.
Altering new table...
Altered `employees`.`_salaries_new` OK.
2018-08-28T14:37:01 Creating triggers...
2018-08-28T14:37:01 Created triggers OK.
2018-08-28T14:37:01 Copying approximately 2838426 rows...
Copying `employees`.`salaries`:  74% 00:10 remain
2018-08-28T14:37:41 Copied rows OK.
2018-08-28T14:37:41 Analyzing new table...
2018-08-28T14:37:42 Swapping tables...
2018-08-28T14:37:42 Swapped original and new tables OK.
2018-08-28T14:37:42 Dropping old table...
2018-08-28T14:37:42 Dropped old table `employees`.`_salaries_old` OK.
2018-08-28T14:37:42 Dropping triggers...
2018-08-28T14:37:42 Dropped triggers OK.
Successfully altered `employees`.`salaries`.

 

OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新

        optimize可以回收空间、减少碎片、提高I/O

##使用mysqldump备份数据库erms

查看监控数据
tina=# select * from jk_pg order by record_time desc,jk_host desc limit 4;
  id  |    jk_host     |     record_time     | waiting_count |  streaming   | usersum | tbjk | running_port |                        cipan                         | locks | beizhu
------+----------------+---------------------+---------------+--------------+---------+------+--------------+------------------------------------------------------+-------+--------
7654 | 192.168.12.2  | 2016-01-13 11:00:01 |             0 | F2B/CE5349B0 |     161 |      |         5432 | Filesystem      Size  Used Avail Use% Mounted on    +|       |
      |                |                     |               |              |         |      |              | /dev/sda2       104G   21G   78G  22% /             +|       |
      |                |                     |               |              |         |      |              | /dev/sdc1       917G  540G  331G  63% /opt/db_backup+|       |
      |                |                     |               |              |         |      |              | /dev/sdb        939G  370G  522G  42% /home/pgsql    |       |
7655 | 192.168.12.1  | 2016-01-13 11:00:01 |              0 | F2B/CEE173E8 |      26 |    0 |         5432 | Filesystem      Size  Used Avail Use% Mounted on    +|       |
      |                |                     |               |              |         |      |              | /dev/sda3       103G  6.1G   92G   7% /             +|       |
      |                |                     |               |              |         |      |              | /dev/sdb1       939G  285G  606G  32% /home/pgsql    |       |
7653 | 192.168.12.8 | 2016-01-13 11:00:01 |               0 |              |      30 |      |         5432 | Filesystem      Size  Used Avail Use% Mounted on    +|       |
      |                |                     |               |              |         |      |              | /dev/sda3        27G  1.9G   24G   8% /             +|       |
      |                |                     |               |              |         |      |              | /dev/sda2        29G  4.1G   24G  15% /var          +|       |
      |                |                     |               |              |         |      |              | /dev/sdb1       252G  118G  122G  50% /home          |       |

2.3 使用optimize table命令,整理碎片。

运行OPTIMIZE TABLE, InnoDB创建一个新的.ibd具有临时名称的文件,只使用存储的实际数据所需的空间。优化完成后,InnoDB删除旧.ibd文件并将其替换为新文件。如果先前的.ibd文件显着增长但实际数据仅占其大小的一部分,则运行OPTIMIZE TABLE可以回收未使用的空间。

mysql>optimize table account;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table        | Op       | Msg_type | Msg_text                                                          |
+--------------+----------+----------+-------------------------------------------------------------------+
| test.account | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.account | optimize | status   | OK                                                                |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.09 sec)

 

利用未使用的空间,并整理数据文件的碎片。

        目前支持的存储引擎有:InnoDB、MyASIM和ARCHIVE

/usr/local/MySQL/bin/mysqldump -uroot -ppwd erms >> /serverBack/mysql_back/erms_$(date+"%Y_%m_%d").sql

2)pg统计库所有表的行数
[root@pg-ro tmp]# cat tinadb.sh
#!/bin/bash
#2015-11-3 tina
date=`date +"%Y-%m-%d %H:%M:%S"`
echo "begin time is: $date" >>/tmp/tongji.log

3.整理表碎片shell脚本

# cat optimize_table.sh

#!/bin/sh
socket=/tmp/mysql3306.sock
time=`date +"%Y-%m-%d"`
SQL="select concat(d.TABLE_SCHEMA,'.',d.TABLE_NAME) from information_schema.TABLES d where d.TABLE_SCHEMA = 'employees'"

optimize_table_name=$(/usr/local/mysql/bin/mysql -S $socket -e "$SQL"|grep -v "TABLE_NAME")

echo "Begin Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>/tmp/optimize_table_$time.log

for table_list in $optimize_table_name
do

echo `date +"%Y-%m-%d %H:%M:%S"` "alter table $table_list engine=innodb ...">>/tmp/optimize_table_$time.log
/usr/local/mysql/bin/mysql -S $socket -e "alter table $table_list engine=innoDB"

done
echo "End Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>>/tmp/optimize_table_$time.log

输出内容

# cat optimize_table_2018-08-30.log

Begin Optimize Table at: 2018-08-30 08:43:21
2018-08-30 08:43:21 alter table employees.departments engine=innodb ...
2018-08-30 08:43:21 alter table employees.dept_emp engine=innodb ...
2018-08-30 08:43:27 alter table employees.dept_manager engine=innodb ...
2018-08-30 08:43:27 alter table employees.employees engine=innodb ...
2018-08-30 08:43:32 alter table employees.salaries engine=innodb ...
2018-08-30 08:44:02 alter table employees.t1 engine=innodb ...
2018-08-30 08:44:17 alter table employees.titles engine=innodb ...
2018-08-30 08:44:28 alter table employees.ttt engine=innodb ...
End Optimize Table at: 2018-08-30 08:44:28

 

 

使用方法:sh optimize.sh word

        

##找到/serverBack/mysql_back/下文件名称以erms_开头,以 .sql 结尾的文件,并且是7天前系统修改过的文件,将其删除

tables=$(psql -U postgres -d tinadb -c "select tablename from pg_tables where  schemaname='public' order by tablename;"|grep -v "tablename" |grep -v "rows"|grep -v "-")

[[email protected] shell]#

        如果是Replication环境、可加NO_WRITE_TO_BINLOG(或者LOCAL、意思完全相同)、比如:

find /serverBack/mysql_back/ -mtime +7-name "erms_*.sql" -exec rm -rf {} ;

#echo $tables >>/tmp/tongji.log

#!/bin/sh

        optimize local table table_name;

方法二:

for table in $tables
    do
       echo $table >>/tmp/tongji.log
       psql -U postgres -d tinadb -c "select count(*) from $table;" |grep -v "count" |grep -v "row"|grep -v "-">>/tmp/tongji.log
    done
#echo "ok!" >>/tmp/tongji.log

time_log=/opt/optimize_time

        

/usr/local/mysql/bin/mysqldump -uroot -ppwd dbname > dir/db_`date +%F`.sql

查看--并直接粘贴到execl表格中
[root@pg-ro tmp]# cat /tmp/tongji.log  |awk 'NF==1{printf "%s ", $1;next}1'
begin time is: 2015-11-03 14:12:12
t1 11024
t2 8267537
t3 1684
t4 2

 

        以下是一个简单测试:

##保留近一周的备份文件,更早的删除

统计其他库,直接用vi替换功能替换db名即可:
替换 :%s/tinadb/dbname/g

sum=$#

 

find /dir -mtime +7 -name"db_*.sql" -exec rm -rf {} ;

3)pg 定期vacuum和reindex脚本
[root@pg tina_shell]# cat pg_tinadb_vacuum.sh
#!/bin/bash
#2014-10-22 tina
date=`date +"%Y-%m-%d %H:%M:%S"`
echo "begin time is: $date" >>/tmp/pg_tinadb_vacuum.log

if [ "$sum" -eq 0 ]

[plain] 

方法三:

tables=$(psql -U postgres -d tinadb -c "select tablename from pg_tables where schemaname='public';" |grep -v "tablename" |grep -v "rows"|grep -v "-")
echo $tables >>/tmp/pg_tinadb_vacuum.log

then

[[email protected] employees]$ ls -alh t.ibd  

filename='date +%y%m%d'

indexes=$(psql -U postgres -d tinadb -c "select indexname from pg_indexes where schemaname='public' and indexname not like '%pkey';"|grep -v "indexname"|grep -v "-" |grep -v "row")

echo "Error: no parameter chosed"

-rw-rw---- 1 mysql dba 24M 05-22 16:48 t.ibd  

/usr/local/mysql/bin/mysqldump -uroot-proot erms >>/serverBack/mysql/$filename.sql

for table in $tables
do
psql -U postgres -d tinadb -c "vacuum full $table;">>/tmp/pg_tinadb_vacuum.log
echo "table $table has finished vacuum.">>/tmp/pg_tinadb_vacuum.log
done

exit 1

  

较全的shell脚本内容如下:

for index in $indexes
do
psql -U postgres -d tinadb -c "reindex index $index;">>/tmp/pg_tinadb_vacuum.log
echo "index $index has finished reindex.">>/tmp/pg_tinadb_vacuum.log
done

fi

未optimize前、有24M  

echo "---------------------------------------------------" >> /serverBack/dbBack/dbBackLog.log 

查看后台日志:
[root@pg tmp]# tail -f pg_tinadb_vacuum.log
begin time is: 2016-01-13 11:38:26
VACUUM
table t1 has finished vacuum.
VACUUM
table t2 has finished vacuum.
VACUUM
table t3 has finished vacuum.
VACUUM
table t4 has finished vacuum.
REINDEX
index t1_rin_idx has finished reindex.

 

  

echo $(date +"%Y-%m-%d %H:%M:%S") "erms Database backup start"  >> /serverBack/dbBack/dbBackLog.log 

建议:如果库中存在大表,就单独手动操作,不然可能会导致执行时长时间锁表,影响其他业务。

本文由必威发布于必威-数据,转载请注明出处:内容如下,mysql手册中关于 OPTIMIZE 的描述

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。