InnoDB database Optimization

Summary: due to frequent data updates and fast data volume growth, the machine disk cannot keep up with the pace. The database shard and migration history database are used. Currently, data is optimized to reduce disk space occupied by data and save disk space.

1. Use optimize to move data to avoid gaps. Save space.
Usage: 0 3 ** 3/usr/local/bin/optimize_lixian_dbp_innodb.sh hj db/tmp/mysql. sock &>/tmp/optimize_innodb.hj.data.log

[Root @ hj data] # cat/usr/local/bin/optimize_lixian_dbp_innodb.sh
#! /Bin/bash
If ["$1" = ""] | ["$2" = ""] | ["$3" = ""]; then
Echo "Usage: $0 {tj | bj} <db_name> <mysql sock file's path>"
Exit 1
Fi
### Define variables
Db_type = "$1"
Db_name = "$2"
Sock_file = "$3"
MYSQL = "/usr/local/mysql/bin/mysql-u root-phj-S $ {sock_file }"
Tb_list = "/tmp/optimize_lixian_dbp_innodb. $ {db_type }_$ {db_name # * _}. list"
Pid_file = "/tmp/optimize_lixian_dbp_innodb. $ {db_type }_$ {db_name # * _}. pid"
### Determine if the same script is running. If yes, exit. If no, generate the PID file of the script.
If [-f $ {pid_file}]; then
Old_pid = 'cat $ {pid_file }'
Ps ax-o pid, cmd | grep-v grep | grep-q "$ {old_pid}" & echo 'date + "% F % T" '-this sh is already running... & exit 1
Fi
Echo $ >$ {pid_file}
### Export the table names of all innodb tables to the file
>$ {Tb_list}
Echo show table status | $ {MYSQL}-N $ {db_name} | awk '/InnoDB/{print $1}' >$ {tb_list}
### Start Optimization
Num = 'cat $ {tb_list} | wc-l'
Count = 1
For tb in 'cat $ {tb_list }'
Do
Echo 'date + "% F % T" '-$ {count}/$ {num}-$ tb
Echo "optimize local table $ {tb};" |$ {MYSQL}-N $ {db_name}
Sleep 10
Count = $ (count + 1 ))
Done
### Delete the PID file of the script
Rm-f $ {pid_file}

2. Modify the storage mode of data for databases with few changes

[Root @ hj ~] # Cat/usr/local/bin/alter_lixian_dbp_innodb_to_myisam.sh
#! /Bin/bash
#
# Hj 4.01.23 hj
#
If ["$1" = ""] | ["$2" = ""] | ["$3" = ""]; then
Echo "Usage: $0 {tj | bj} <db_name> <mysql sock file's path>"
Exit 1
Fi
### Define variables
Db_type = "$1"
Db_name = "$2"
Sock_file = "$3"
MYSQL = "/usr/local/mysql/bin/mysql-u root-phj-S $ {sock_file }"
Tb_list = "/tmp/alter_lixian_dbp_innodb_to_myisam. $ {db_type }_$ {db_name # * _}. list"
Pid_file = "/tmp/alter_lixian_dbp_innodb_to_myisam. $ {db_type }_$ {db_name # * _}. pid"
### Determine if the same script is running. If yes, exit. If no, generate the PID file of the script.
If [-f $ {pid_file}]; then
Old_pid = 'cat $ {pid_file }'
Ps ax-o pid, cmd | grep-v grep | grep-q "$ {old_pid}" & echo 'date + "% F % T" '-this sh is already running... & exit 1
Fi
Echo $ >$ {pid_file}
### Export the table names of all innodb tables to the file
>$ {Tb_list}
Echo show table status | $ {MYSQL}-N $ {db_name} | awk '/InnoDB/{print $1}' >$ {tb_list}
### Start Optimization
Num = 'cat $ {tb_list} | wc-l'
Count = 1
For tb in 'cat $ {tb_list }'
Do
Echo 'date + "% F % T" '-$ {count}/$ {num}-$ tb
Echo "alter table $ {tb} engine = myisam;" |$ {MYSQL}-N $ {db_name}
Sleep 10
Count = $ (count + 1 ))
Done
### Delete the PID file of the script
Rm-f $ {pid_file}

Recommended reading:

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration