InnoDB database Optimization

Source: Internet
Author: User
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. Data Optimization now,

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. Data Optimization now,

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} "
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} "
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

,

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.