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.
  -show-database-changes="false"
                               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

redis_output_result.txt

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

mysql_output_result.txt

MySQL TCPCOPY

June 29, 2016 Architect, Architecture, hardware, MYSQL, network No comments

we use tcpcopy to make real traffic on our core systems. Many problems will be found in advance if we enlarge queries several times.

Read this PDF TCPCOPY

Infiniflash Benchmark

May 29, 2016 Architect, Architecture, hardware, MYSQL, performance No comments

Sandisk (FusionIO) and Nexenta are working together to build this SDS solution.

Infiniflash is a very large SDS production, which manages for very large DW system who requires large storage space and also high IOPS.

We test infiniflash system ,read this Infiniflash_benchmark

MySQL distributed message system

April 12, 2016 Architect, Architecture, hardware, MYSQL, replication, software No comments

Based on messages, we create mysql replication platforms , using async message to build strong distributed subscription system.

read this PDF : http://www.vmcd.org/docs/MySQL_async_message.pdf

招聘帖

February 1, 2016 Architect No comments

因公司业务发展迅速,部门现在招两个mysql dba 同学,目前本人在平安健康互联网(平安好医生)数据团队,负责数据库的整体规划,想来的同学可以发简历到我的邮箱 ylouis83@gmail.com 靠谱的公司靠谱的团队 :)

关于公司:

国内最大的互联网医疗平台,提供全品类互联网医疗服务,目前国内前三大的a轮估值

How to backup remote redis instance

January 29, 2016 Architect, NoSQL, redis No comments

We wrote a python scripts to control redis backup work.

Three Steps to do this work:

1. Create meta database to store which redis instance needs to backup

2. Use scripts to connect to these redis instances with “–rdb” command (remote backup command)

3. Estimate which redis server should be transfered (only slave role redis will be transfered, wait minutes if a bgsave is running)

Backup Scripts: Redis_remote.py

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

Events:
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

TokuDB benchmark on PCIe

October 21, 2015 Architect, Architecture, hardware, Internals, MYSQL, performance No comments

MariaDB TokuDB benchmark on FusionIO ,Compare TokuDB and InnoDB engines.

read: TokuDB_benchmark

MySQL lost “AUTO_INCREMENT” after a long time

October 8, 2015 Internals, MYSQL, rdbms No comments

Description:

I create a empty table and then alter this table's "AUTO_INCREMENT" starts from 10000000.After a long time ,MySQL lost "AUTO_INCREMENT" definition when I show this table again. MariaDB has the same problem but Percona Server is fine.

$mysql06

Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 141028725
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TABLE IF EXISTS `test_bug` ;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> CREATE TABLE test_bug (
    ->   `id` BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    ->   `consult_order_id` BIGINT ,
    ->   `pay_channel` INT COMMENT ,
    ->   `price` BIGINT ,
    ->   `trade_no` VARCHAR (64) ,
    ->   `right_no` VARCHAR (64) ,
    ->   `item_id` BIGINT NOT NULL,
    ->   `source_type` INT NOT NULL ,
    ->   `gmt_create` DATETIME DEFAULT CURRENT_TIMESTAMP ,
    ->   `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
    -> ) ENGINE = INNODB DEFAULT CHARSET = utf8 ;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> ALTER TABLE test_bug 
    ->   ADD INDEX index_consult_order (consult_order_id) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> ALTER TABLE test_bug 
    ->   ADD INDEX index_right_no (right_no) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> ALTER TABLE test_bug 
    ->   AUTO_INCREMENT = 100000000 ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> show create table  test_bug;
  
   CREATE TABLE `test_bug` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `consult_order_id` bigint(20) DEFAULT NULL ,
  `pay_channel` int(11) DEFAULT NULL COMMENT ,
  `price` bigint(20) DEFAULT NULL COMMENT ,
  `trade_no` varchar(64) DEFAULT NULL COMMENT ,
  `right_no` varchar(64) DEFAULT NULL COMMENT ,
  `item_id` bigint(20) NOT NULL COMMENT ,
  `source_type` int(11) NOT NULL COMMENT ,
  `gmt_create` datetime DEFAULT CURRENT_TIMESTAMP ,
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  PRIMARY KEY (`id`),
  KEY `index_consult_order` (`consult_order_id`),
  KEY `index_right_no` (`right_no`)
) ENGINE=InnoDB AUTO_INCREMENT=100000000 DEFAULT CHARSET=utf8  


1 row in set (0.00 sec)


wait about 24 hours , then show create table :


mysql> show create table test_bug; 

   CREATE TABLE `test_bug` (
  `id` BIGINT (20) NOT NULL AUTO_INCREMENT,
  `consult_order_id` BIGINT (20) DEFAULT NULL,
  `pay_channel` INT (11) DEFAULT NULL,
  `price` BIGINT (20) DEFAULT NULL,
  `trade_no` VARCHAR (64) DEFAULT NULL,
  `right_no` VARCHAR (64) DEFAULT NULL,
  `item_id` BIGINT (20) NOT NULL,
  `source_type` INT (11) NOT NULL,
  `gmt_create` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `index_consult_order` (`consult_order_id`),
  KEY `index_right_no` (`right_no`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 

1 row in set (0.00 sec)  

AUTO_INCREMENT disappears .

Bug has been reported :https://bugs.mysql.com/bug.php?id=77743

NVMFS Supports Atomic Writes

September 6, 2015 Architect, hardware, Internals, MYSQL No comments

Benchmark for NVMFS (supports atomic writes ,so we can close double-write option on specific MySQL version )

tips: some Flash-based cards could support large block map , the main idea is to avoid fractured page writes.

http://www.vmcd.org/docs/nvmfs_benchmark.pdf