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

[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 AWR system

August 6, 2015 Architect, Architecture, mongodb, MYSQL, NoSQL, rdbms, software No comments

In this article, we introduce myawr and mongoawr system .

Read this PDF, you will learn how to configure them.

How to configure AWR system.

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:

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

How to configure WEBM

July 15, 2015 Architect, mongodb, MYSQL, software No comments

Architecture of WEBM system.

Reference:

http://www.vmcd.org/2014/10/webm_v2-has-been-released/
http://www.vmcd.org/2014/09/webm-mysql-database-performance-web-monitor/

View this PDF:

http://www.vmcd.org/docs/How%20to%20configure%20WEBM.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 : https://github.com/markwkm/iopp

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
QQ图片20150424145546
#grep ^ /sys/class/block/*/dev |grep 224

/sys/class/block/sdo/dev:8: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 🙂