Percona-Tookit工具包之pt-find

 

 

 

Preface

Preface

Preface

 

 

 

    We used to use “find” command in linux
or AIX when we need to get a certain file but cannot rember the precise
name.We will execute “find /pathtobegin -name xxx” in our OS prompt.Is
there any tool can find a specific table of MySQL database which
similarly as “find” does?

    There’re many ways relevent with
performance tuning.For example,using indexes properly is important in
doing that.At the very beginning of releasing a project,we’re probably
supposed to create many different indexes(especially union index) to
increase the efficiency of queries on target tables even if some of them
are seldom or never used at all.We are sure about that it is not the
more the better of indexes on a table.Indexes will occupy more disk
space and will cost a lot in maintaining.Alternatively,we should reduce
the indexes which are not usually used by freqeuntly cheking them.Therefore,I’ll
introduce a tool which can help us in the aspect.

    How to rescue a dropped or truncated
table online?
Dropping or truncating
is ddl operation which cannot be flashed back by the populare flashback
tools like
MyFlash,binlog2mysql,mysqldump_backup,etc.Therefore,the conventional method is
restoring the database to a newly initialized instance on another server
with backup(physical or logical).Whatif the backup set is rather huge
for example the mysqldump backup is more than 200G?It will cost a long
time to rescue the dropped table back.Is there an effective way to
accomplish the issue?Let’s see the tests below.

 

 

 

Introduce

Introduce

Framework

 

 

 

*    pt-find is a very useful tool to find
out a sepcific table with a condition you’ve specified.It even can
execute a sql operation when finding it simutaneously.

*

*    pt-index-usage(as what it is called)
is a tool of Percona-Toolkit can provide a way to analyze your SQL
statments in slow log(which means they’re probably executed with bad
performance).Afterward,you can know details about whether there’re
indexes not used properly and estimate whether to drop them in some time
later.

*

Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm2 192.168.1.101/3306 master CentOS 7.0 5.7.21 on row
zlm3 192.168.1.102/3306 slave CentOS 7.0 5.7.21 on row

 

 

 

Procedure

Procedure

Precedure

 

 

 

Usage

Usage

**Test1:Rescue a table after
dropping it based on a new mysqldump backup.**

1 pt-find [OPTIONS] [DATABASES]
1 pt-index-usage [OPTIONS] [FILES]

 

 

 

Generate the test data with
sysbench.

Common
parameters

Main
parameter

 1 [root@zlm2 07:30:58 ~/sysbench-1.0/src/lua]
 2 #sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --tables=10 --table-size=10000 --mysql-storage-engine=innodb prepare
 3 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
 4 
 5 Creating table 'sbtest1'...
 6 Inserting 10000 records into 'sbtest1'
 7 Creating a secondary index on 'sbtest1'...
 8 Creating table 'sbtest2'...
 9 Inserting 10000 records into 'sbtest2'
10 Creating a secondary index on 'sbtest2'...
11 Creating table 'sbtest3'...
12 Inserting 10000 records into 'sbtest3'
13 Creating a secondary index on 'sbtest3'...
14 Creating table 'sbtest4'...
15 Inserting 10000 records into 'sbtest4'
16 Creating a secondary index on 'sbtest4'...
17 Creating table 'sbtest5'...
18 Inserting 10000 records into 'sbtest5'
19 Creating a secondary index on 'sbtest5'...
20 Creating table 'sbtest6'...
21 Inserting 10000 records into 'sbtest6'
22 Creating a secondary index on 'sbtest6'...
23 Creating table 'sbtest7'...
24 Inserting 10000 records into 'sbtest7'
25 Creating a secondary index on 'sbtest7'...
26 Creating table 'sbtest8'...
27 Inserting 10000 records into 'sbtest8'
28 Creating a secondary index on 'sbtest8'...
29 Creating table 'sbtest9'...
30 Inserting 10000 records into 'sbtest9'
31 Creating a secondary index on 'sbtest9'...
32 Creating table 'sbtest10'...
33 Inserting 10000 records into 'sbtest10'
34 Creating a secondary index on 'sbtest10'...
35 
36 (zlm@192.168.1.101 3306)[sysbench]>show tables;
37 +--------------------+
38 | Tables_in_sysbench |
39 +--------------------+
40 | sbtest1            |
41 | sbtest10           |
42 | sbtest2            |
43 | sbtest3            |
44 | sbtest4            |
45 | sbtest5            |
46 | sbtest6            |
47 | sbtest7            |
48 | sbtest8            |
49 | sbtest9            |
50 +--------------------+
51 10 rows in set (0.00 sec)
 1 Options:
 2 --day-start //Specify the meassure mothed of time when using "--cmin,--mmin,--ctime,--mtime,etc".
 3 --or //Change the combination test behavior as "or" instead of default "and".
 4 
 5 Actions:
 6 --exec //Specify the executing sql statement with each item found.
 7 --exec-plus //Specify the executing sql statement with all items at once.
 8 --print //Print the target database and table name.
 9 --printf //Print with a certain format.
10 
11 Tests:
12 --autoinc //Speicfy a value of auto_increment to test whether has auto_inrcrement column.
13 --avgrowlen //Specify the average 
14 --cmin //Specify the target table created n minutes ago.
15 --ctime //Specify the target table created n days ago.
16 --mmin //Specify the target table modified n minutes ago.
17 --mtime //Specify the target table modified n days ago.
18 --kmim //Specify the target table checked n minutes ago.
19 --ktime //Specify the target table checked n days ago.
20 --rowformat //Specify the row format of tables to match pattern.
21 --rows //Specify the rows the table contains.
22 --tablesize //Specify the size the table is.
23 --empty //Specify the talbe which has no rows.
24 --engines //Specify the engine of tabls.
1 --save-results-database -- Save output results into the specific tables of database.
2 --create-save-results-database -- Create a database with necessary tables if set "--save-results-database" but not exist.
3 --empty-save-results-tables -- Drop and recreate all the tables which are specified by "--save-results-database".
4 --create-views -- Create views for tables in database which is specified by "--save-results-database".
5 --no-report -- Don't generate a report but put results into tables for later analysis."--save-results-database" is indispensable when you set this option.
6 --report-format -- The only format is "drop_unused_indexes" now.
7 --drop -- Specify the type of index which you want to drop(Default value is non-unique).

 

 

 

**Backup the database sysbench
with mysqldump.**

Example

Examples

 1 [root@zlm2 07:32:01 ~]
 2 #mysqldump --single-transaction --master-data=2 -A > db3306_`date +%Y%m%d`.sql
 3 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
 4 
 5 [root@zlm2 07:32:09 ~]
 6 #ls -l
 7 total 34744
 8 drwxr-xr-x   2 root root     4096 Jul 23 10:10 20180723
 9 -rw-------.  1 root root     1431 Jul 16  2015 anaconda-ks.cfg
10 -rw-r--r--   1 root root 20390934 Jul 26 07:32 db3306_20180726.sql
11 -rw-r--r--   1 root root  7333548 Jul 24 02:48 db.sql
12 -rwxr-xr-x   1 root root       54 Jun 13 04:16 mysqld.sh
13 -rwxr-xr-x   1 root root  7829340 Jul 24 10:02 percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
14 drwxr-xr-x  13 root root     4096 Jul  4 03:21 sysbench-1.0
15 
16 [root@zlm2 07:32:11 ~]
17 #scp db3306_20180726.sql zlm3:/data/backup
18 root@zlm3's password: 
19 db3306_20180726.sql                                                                                                100%   19MB  19.5MB/s   00:00    
20 
21 [root@zlm2 07:33:35 ~]