Donkey – system for MySQL automatic maintenance

February 17, 2017 Architect, Architecture, maintain, MYSQL, system No comments

We build this system based on Inception (Qunar)。

All of MySQL DDL/DML operations are based on Donkey system, with strict approval process to keep online system strong and available.

Open this file by Mockplus Donkey-system

TCP Trace

September 21, 2016 Architecture, MYSQL, redis, system No comments

I found a useful tool named sniffer to help us analyze network packages (this tool can capture packages on specific port)

I use sniffer to analyze mysql and redis packages (translate these packages to normal queries)

#./vc-redis-sniffer –help

vc-redis-sniffer is a utility from VividCortex to monitor query activity and write results to a file.
See --license for the terms governing your usage of this program.

  -binding="[::]:6379"         This is a list of comma separated bind strings as seen in /proc/net/tcp
  -help="false"                Show this usage message
  -license="false"             Print the usage terms of this program
  -output=""                   Filepath to output queries to. Defaults to stdout if none specified.
  -show-database="false"       Include a 'USE `database`' for every statement. Supersedes show-database-changes.
                               Include a 'USE `database`' every time the database is changed.
  -verbose="false"             Enable logging on program startup to stderr
  -version="false"             Show version and exit

  Flag                         Current value
  -binding                     "[::]:6379"
  -help                        "true"
  -license                     "false"
  -output                      ""
  -show-database               "false"
  -show-database-changes       "false"
  -verbose                     "false"
  -version                     "false"

Capture packages and gather logs

[root@a1-dba-test-242-13 /tmp/vc-redis-sniffer]
#./vc-redis-sniffer -binding=”[::]:6379″ -output=/tmp/redis.log

Analyze logs using pt-tools

[root@a1-dba-test-242-13 /tmp/vc-mysql-sniffer]
#pt-query-digest /tmp/redis.log


Also we can analyze mysql online queries ,do above steps to get result:


Linux tools part 11 – Monitoring specified file-IO

December 28, 2015 system No comments

We could use inotify-tools to monitor file or directory IO state

yum install inotify-tools

#inotifywait -m /storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd

Setting up watches.                                                                
Watches established.                                                               
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY            
/storage/sas/mysql3306/data/diablo_im/#sql-ib4747-2643139248.ibd MODIFY   

this means mysqld process is writing file #sql-ib4747-2643139248.ibd now

we could add -r option to monitor directory IO state

#inotifywait -m -r /storage/sas/mysql3306/data/diablo_im/
Setting up watches. Beware: since -r was given, this may take a while!
Watches established.
/storage/sas/mysql3306/data/diablo_im/ MODIFY #sql-ib4747-2643139248.ibd
/storage/sas/mysql3306/data/diablo_im/ MODIFY #sql-ib4747-2643139248.ibd
/storage/sas/mysql3306/data/diablo_im/ MODIFY #sql-ib4747-2643139248.ibd
/storage/sas/mysql3306/data/diablo_im/ MODIFY #sql-ib4747-2643139248.ibd
/storage/sas/mysql3306/data/diablo_im/ MODIFY #sql-ib4747-2643139248.ibd
/storage/sas/mysql3306/data/diablo_im/ MODIFY #sql-ib4747-2643139248.ibd
/storage/sas/mysql3306/data/diablo_im/ MODIFY #sql-ib4747-2643139248.ibd
/storage/sas/mysql3306/data/diablo_im/ MODIFY #sql-ib4747-2643139248.ibd
/storage/sas/mysql3306/data/diablo_im/ MODIFY #sql-ib4747-2643139248.ibd
/storage/sas/mysql3306/data/diablo_im/ MODIFY #sql-ib4747-2643139248.ibd
/storage/sas/mysql3306/data/diablo_im/ MODIFY #sql-ib4747-2643139248.ibd

access file or directory contents were read
modify file or directory contents were written
attrib file or directory attributes changed
close_write file or directory closed, after being opened in writeable mode
close_nowrite file or directory closed, after being opened in read-only mode
close file or directory closed, regardless of read/write mode
open file or directory opened
moved_to file or directory moved to watched directory
moved_from file or directory moved from watched directory
move file or directory moved to or from watched directory
create file or directory created within watched directory
delete file or directory deleted within watched directory
delete_self file or directory was deleted
unmount file system containing file or directory unmounted

[Reprint]Injection MySQL – Function

August 24, 2015 Internals, linux, MYSQL, rdbms No comments

Reprint a PDF for MySQL Injection Test

MySQL Error Based SQL Injection Using EXP

MySQL checksum

August 19, 2015 MYSQL, replication, system No comments

how to use percona-tookit to verify mysql data

Read this PDF : mysql_checksum

How to configure Tcpdump system

July 24, 2015 Architect, Architecture, Internals, MYSQL, network, system No comments

MySQL Tcpdump system : use percona-toolkit to analyze network packages

We can identify problem SQLs with high execution frequency.

With DBMON system and AWR system we can find problem SQLs in a special time (high frequency, occurs over a period of time)

View this PDF:

linux tools part 10– Monitoring process iostat

April 24, 2015 system No comments

We can use iopp tool to monitor process io performance

download from github :

pid: The process id.
rchar: The number of bytes which this task has caused to be read from storage.
wchar: The number of bytes which this task has caused, or shall cause to be written to disk.
syscr: Count of the number of read I/O operations.
syscw: Count of the number of write I/O operations.
rbytes rkb rmb reads: Count of the number of bytes which this process really did cause to be fetched from the storage layer.
wbytes wkb wmb writes: Count of the number of bytes which this process really did cause to be sent to the storage layer.
cwbytes cwkb cwmb cwrites: The number of bytes which this process caused to not happen, by truncating pagecache.
command: Filename of the executable.

There is one small bug , you’ll get different output from -i -k than you will from -i or from -i -m.

#iopp -i -k 2
#grep ^ /sys/class/block/*/dev |grep 224


#lsof |grep “8,224” |more

bash 5265 root cwd DIR 8,224 57151488 427819009 /data/yp900
 redis-ser 19843 root cwd DIR 8,224 4096 469368833 /data/redis6379
 sh 27579 root cwd DIR 8,224 4096 579731457 /data/mysql3306
 mysqld 28324 mysql cwd DIR 8,224 4096 579731459 /data/mysql3306/data
 mysqld 28324 mysql 1w REG 8,224 1327901 579743077 /data/mysql3306/mysql-error.log
 mysqld 28324 mysql 2w REG 8,224 1327901 579743077 /data/mysql3306/mysql-error.log
 mysqld 28324 mysql 3u REG 8,224 23520 579743078 /data/mysql3306/binlog/mysql-bin.index
 mysqld 28324 mysql 4uW REG 8,224 1073741824 579743083 /data/mysql3306/data/ibdata0
 mysqld 28324 mysql 5u REG 8,224 0 579731461 /data/mysql3306/mysql-tmpdir/ib1io6G9 (deleted)
 mysqld 28324 mysql 6u REG 8,224 0 579731462 /data/mysql3306/mysql-tmpdir/ibnkifvg (deleted)
 mysqld 28324 mysql 7u REG 8,224 0 579731463 /data/mysql3306/mysql-tmpdir/ibHWjojn (deleted)
 mysqld 28324 mysql 8u REG 8,224 0 579731464 /data/mysql3306/mysql-tmpdir/ib1o8yHC (deleted)
 mysqld 28324 mysql 9uW REG 8,224 1073741824 579743084 /data/mysql3306/data/ibdata1
 mysqld 28324 mysql 10uW REG 8,224 1073741824 579743085 /data/mysql3306/data/ibdata2
 mysqld 28324 mysql 11uW REG 8,224 27850178560 579743086 /data/mysql3306/data/ibdata3
 mysqld 28324 mysql 12uW REG 8,224 536870912 579743087 /data/mysql3306/data/ib_logfile0
 mysqld 28324 mysql 13uW REG 8,224 536870912 579743088 /data/mysql3306/data/ib_logfile1
 mysqld 28324 mysql 14uW REG 8,224 2281701376 579733990 /data/mysql3306/data/mit/agent_info#P#p20150404.ibd
 mysqld 28324 mysql 15u REG 8,224 0 579731465 /data/mysql3306/mysql-tmpdir/ibBZSSPL (deleted)

A nice smart tools with a little bugs 🙂

Design for “seckilling ” on MySQL

March 13, 2015 system No comments

Today I read a nice article , which give a new design for mysql database . In this article , Author (Mr.lou) uses OneSQL (a branch of MySQL) for the whole test.

you can read from :

This article explain how to use mysql in seckilling scenario (especially for E-Commerce commany )

If you want to use transaction in RDBMS database, you must manual start transaction and commit in the end of your operation.
So,commit will be cost of the total process, round-trip cost will be the most important cost as author said

(For simple update operations, without regard to conflict of IO and locks, a statement execution time is about 0.1ms, network delay under normal conditions is 0.4-0.8ms, that the waiting time is longer than the transaction commit time to really notice the SQL execution several times, showing that the network is a single-line transaction update performance killer)

How to avoid this ? We can start a transaction to manually control SQL commit. When autocommit=1 was set on MySQL server implicitly commit will happen after every SQLs.

An explicit transaction is when statement(s) are wrapped within an explicitly defined transaction code block – for MySQL, that’s START TRANSACTION. It also requires an explicitly made COMMIT or ROLLBACK statement at the end of the transaction. Nested transactions is beyond the scope of this topic.

Implicit transactions are slightly different from explicit ones. Implicit transactions do not require explicity defining a transaction. However, like explicit transactions they require a COMMIT or ROLLBACK statement to be supplied.

Using OneSQL ,Commit operation can be done on MySQL server side to avoid network round-trip. In total logical process, everything will be controlled by yourself .

In seckilling scenario, this optimization will give a tremendous performance promotion.

Actually ,we can also use Redis or memcached for this situation, Redis CAS feature witch MULTI in serial process will be a good choice.

Best practice – try to offload request before database. Order creation should be asynchronous (put operations in a serial async queue is the best choice)


July 17, 2014 migration, oracle, system, unix No comments

You can view this presentation click here: x86_architect
注意使用chrome打开文件 不然可能乱码

linux tools part 9– Monitoring system performance

April 25, 2014 linux, system No comments

淘宝的开源监控工具 –同事推荐安装测下来非常不错 可以配合zabbix进行数据的采集


[mysql@mysqldb tmp]$ tsar 
Time           ---cpu-- ---mem-- ---tcp-- -----traffic---- --sda---  ---load- 
Time             util     util   retran    bytin  bytout     util     load1   
24/04/14-22:30   1.19    64.77     0.00    13.00    5.00     0.13     0.00   
24/04/14-22:35   1.29    64.70     0.00    47.00    5.00     0.08     0.00   
24/04/14-22:40   1.35    64.73     0.00    17.00    6.00     0.09     0.00   
24/04/14-22:45   1.32    64.75     0.00    17.00    5.00     0.17     0.00   
24/04/14-22:50   1.29    64.76     0.00    22.00    5.00     0.05     0.00   
24/04/14-22:55   1.33    64.69     0.00    17.00    6.00     0.10     0.00   
24/04/14-23:00   1.30    64.77     0.00    13.00    5.00     0.12     0.00   
24/04/14-23:05   1.99    62.80     0.00    98.00   27.00     4.44     0.30   
24/04/14-23:10   1.32    62.84     0.00    17.00    5.00     0.07     0.00   
24/04/14-23:15   1.28    62.79     0.00    22.00    5.00     0.09     0.00   
24/04/14-23:20   1.64    62.83     0.00    16.00    6.00     0.16     0.00   
24/04/14-23:25   1.44    62.75     0.00    14.00    5.00     0.03     0.00   
24/04/14-23:30   1.40    62.82     0.00    16.00    6.00     0.10     0.00   
24/04/14-23:35   1.95    62.76     0.00    68.00   60.00     1.52     0.08   
24/04/14-23:40 ------    16.26     0.00   ------  ------     0.00     0.56   
24/04/14-23:45   1.50    15.96     0.00    83.00  120.00     0.18     0.00   
24/04/14-23:50   1.37    16.17     0.00   146.00  428.00     0.36     0.00   

MAX              2.87    87.37    35.71    14.2K    1.3K    12.42     0.56   
MEAN             1.57    40.76     0.37   137.02   27.99     0.35     0.02   
MIN              1.14    15.96     0.00     1.00    0.00     0.03     0.00   
[mysql@mysqldb tmp]$ tsar  --help
Usage: tsar [options]
    -check         display last record for alert
    --check/-C     display last record for alert.example:tsar --check / tsar --check --cpu --io
    --cron/-c      run in cron mode, output data to file
    --interval/-i  specify intervals numbers, in minutes if with --live, it is in seconds
    --list/-L      list enabled modules
    --live/-l      running print live mode, which module will print
    --file/-f      specify a filepath as input
    --ndays/-n     show the value for the past days (default: 1)
    --date/-d      show the value for the specify day(n or YYYYMMDD)
    --merge/-m     merge multiply item to one
    --detail/-D    do not conver data to K/M/G
    --spec/-s      show spec field data, tsar --cpu -s sys,util
    --item/-I      show spec item data, tsar --io -I sda
    --help/-h      help
Modules Enabled:
    --cpu               CPU share (user, system, interrupt, nice, & idle)
    --mem               Physical memory share (active, inactive, cached, free, wired)
    --swap              swap usage
    --tcp               TCP traffic     (v4)
    --udp               UDP traffic     (v4)
    --traffic           Net traffic statistics
    --io                Linux I/O performance
    --pcsw              Process (task) creation and context switch
    --partition         Disk and partition usage
    --tcpx              TCP connection data
    --load              System Run Queue and load average


[mysql@mysqldb tmp]$ tsar  --check -C
mysqldb	tsar	cpu:util=1.4 mem:util=16.3 tcp:retran=0.0 traffic:bytin=107.0 traffic:bytout=122.0 io:sda:util=0.0 load:load1=0.0 
[mysql@mysqldb tmp]$ tsar  --check 
mysqldb	tsar	cpu:util=1.4 mem:util=16.3 tcp:retran=0.0 traffic:bytin=107.0 traffic:bytout=122.0 io:sda:util=0.0 load:load1=0.0