Shulou(Shulou.com)06/01 Report--

Database of online IM messages, disk space utilization has reached 96%

A new machine that has not been applied for expansion cannot migrate the database.

All chat records are kept, and no one is allowed to be deleted.

In this scenario, in order to reduce the space capacity, the table can only be defragmented to free up space, optimize table

When we use mysql for delete data, after delete, we find that the spatial file ibd has not decreased, this is because of the existence of debris space. For example, the company has 10 employees, 10 seats, and seven employees have been fired, but these seats are still retained. Defragmentation is like letting the remaining three employees sit aside, and then smash the remaining seven as, so that the space can be released.

OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table.

In addition to reducing the physical space of table data and table indexes, the benefits can also reduce the IO when accessing tables. This comparison understands that before sorting, getting data needs to span a lot of fragmented space, and then it takes time, after sorting, the desired data is put together, and you can get it directly, thus improving the efficiency.

Take a large table for defragmentation. It was 96g before defragmentation.

[root@localhost myshard] # du-ch tbl_immsg_bigo_96.ibd | grep total3.5G total

When executing a command

Optimise table tbl_immsg_bigo_96

After finishing, 2.9g is left.

Myshard > optimize no_write_to_binlog table tbl_immsg_bigo_96 +-- + | Table | | Op | Msg_type | Msg_text | +-- -+ | myshard.tbl_immsg_bigo_96 | optimize | note | Table does not support optimize Doing recreate + analyze instead | | myshard.tbl_immsg_bigo_96 | optimize | status | OK | +-- -+ 2 rows in set (3 min 21.66 sec) [root@localhost myshard] # du-ch tbl_immsg_bigo_96.ibd | grep total2.9G total

There will be a lot of slow query alarms during the sorting period, reporting the status of a waiting for table metadata lock.

ID: 121

USER: db_myshard_rw

HOST: 127.0.0.1:56326

DB: myshard

COMMAND: Execute

TIME: 1214

STATE: Waiting for table metadata lock

INFO: insert into myshard.tbl_immsg_bigo_0 (touid,fromuid,fromseqid,appid

This is because the essence of optimize table is alter table.

The table change process of mysql 5.5is as follows

1. Create a new temporary table tmp

two。 Lock the old table, prohibit insertion and deletion, and only read and write are allowed (that's why the above insert statements are stuck in waiting for table metadata lock)

3. Constantly copy the data from the old table to the new temporary table (this is the copy to tmp table above)

4. After the table is copied, the instant rename operation is performed.

5. Delete the old table

So the biggest problem with optimize is to lock the table, which will cause the insert,delete,update statement to be blocked, waiting for 1214 seconds and continuing, so the first conclusion: when using optimize table, make sure there are no dml statements, make sure the business is cut off, otherwise an accident may occur.

Why lock the watch?

In the process of alter, the data is constantly copied from the old table to the new table. If the old table is delete at this time, the data of the old table is inconsistent with that of the new table. In the end, when the name of the new rename table to the old table, there will be more data.

If in the process of copying data, delete the data of the old table and delete the new table at the same time, the data will be consistent. It is the same for update and insert. This function can be realized through insert trigger, delete trigger and update trigger.

Pt-online-schema-change uses three triggers to complete online table modification, can also complete online defragmentation, command to use

-alter= "ENGINE=InnoDB"

Equivalent to the effect of optimize table

The specific commands are as follows, which are best implemented in the script, because you can defragment the tables of the entire database, not only one table at a time.

Pt-online-schema-change-h address-P port number-u user name-p password-- database= database t = table name-- 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

Using pt-online-schema-change, you can skip the pit of locking tables.

In order to maintain the data consistency of the two tables, the copied part of the data needs to be locked. Use the shared lock share_mode to lock the rows. You can see through show full processlist about 100000 rows at a time, each copy less than 1 second.

INSERT LOW_PRIORITY IGNORE INTO `myshard`.` _ tbl_immsg_bigo_128_ new` (`sid`, `tm_ timestamp`, `tm_ lasttime`, `gid`, `group_ name`, `default_ extension`, `group_ attr`, `group_ owner`, `group_ extension`, `is_ del`, `app_ id`, `mic_ seat`, `invite_ perm`, `invite_media_ perm`, `pub_id_ search`, `apply_ verify`, `public_ id`, `introduc`, `family_ id`, `_ _ version`,` _ deleted`) SELECT `sid`, `tm_ timestamp`, `tm_ lasttime`, `gid`, `group_ name`, `default_ name`, `group_ attr` `apply_ owner`, `group_ extension`, `is_ del`, `app_ id`, `mic_ seat`, `invite_ perm`, `pub_id_ search`, `apply_ verify`, `public_ id`, `introduc`, `family_ id`, `_ _ version`, `deleted` FROM `myshard`.`tbl _ immsg_bigo_ 128` FORCE INDEX (`PRIMARY`) WHERE ((`sid` > = '2112908055') AND ((`sid`)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.