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

webm_v2 has been released

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 🙂

MariaDB : Bug when add index on Partition table

April 23, 2015 Internals, MYSQL, replication No comments

Version: 5.5.5-10.0.10-MariaDB-log MariaDB Server

When I use mariadb as multi-source slave DB I met a strange problem . Creating index on a vary large partition table , Mariadb do — copy to tmp table on the slave side. It takes a long time and still not finish over 9 hours.

Primary : MySQL 5.6.16 —

add index on a partition table:

CREATE TABLE `track_raw_wap211_log` (
 `table_id` varchar(100) DEFAULT NULL,
 `page_id` varchar(100) DEFAULT NULL,
 `banner_id` varchar(100) DEFAULT NULL,
 `button_id` varchar(100) DEFAULT NULL,
 `test_id` varchar(100) DEFAULT NULL,
 `classfication` varchar(100) DEFAULT NULL,
 `request_refer` varchar(100) DEFAULT NULL,
 `request_url` text,
 `title` varchar(100) DEFAULT NULL,
 `user_id` varchar(100) DEFAULT NULL,
 `language` varchar(100) DEFAULT NULL,
 `event` varchar(100) DEFAULT NULL,
 `event_desc` varchar(100) DEFAULT NULL,
 `event_type` varchar(100) DEFAULT NULL,
 `log_version` varchar(100) DEFAULT NULL,
 `project` varchar(100) DEFAULT NULL,
 `log_time` varchar(100) DEFAULT NULL,
 `unicookie` varchar(100) DEFAULT NULL,
 `session_id` varchar(100) DEFAULT NULL,
 `session_duration` varchar(100) DEFAULT NULL,
 `resolution` varchar(100) DEFAULT NULL,
 `channel` varchar(100) DEFAULT NULL,
 `refer` text,
 `user_agent` text,
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `httplogtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `httpforwardip` varchar(100) DEFAULT NULL,
 `country` varchar(100) DEFAULT NULL,
 `province` varchar(100) DEFAULT NULL,
 `city` varchar(100) DEFAULT NULL,
 `area` varchar(100) DEFAULT NULL,
 `mapweidu` varchar(100) DEFAULT NULL,
 `mapjingdu` varchar(100) DEFAULT NULL,
 `sync_mysqlid` bigint(20) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`sync_mysqlid`,`create_time`),
 KEY `ix_create_time` (`create_time`),
 KEY `ix_httplogtime` (`httplogtime`),
 KEY `ix_httplogtime_userid` (`httplogtime`,`user_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=23217118 DEFAULT CHARSET=utf8
 /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(`create_time`))
 (PARTITION p20150318 VALUES LESS THAN (1426694400) ENGINE = InnoDB,
 PARTITION p20150319 VALUES LESS THAN (1426780800) ENGINE = InnoDB,
 PARTITION p20150320 VALUES LESS THAN (1426867200) ENGINE = InnoDB,
 PARTITION p20150321 VALUES LESS THAN (1426953600) ENGINE = InnoDB,
 PARTITION p20150322 VALUES LESS THAN (1427040000) ENGINE = InnoDB,
 PARTITION p20150323 VALUES LESS THAN (1427126400) ENGINE = InnoDB,
 PARTITION p20150324 VALUES LESS THAN (1427212800) ENGINE = InnoDB,
 PARTITION p20150325 VALUES LESS THAN (1427299200) ENGINE = InnoDB,
 PARTITION p20150326 VALUES LESS THAN (1427385600) ENGINE = InnoDB,
 PARTITION p20150327 VALUES LESS THAN (1427472000) ENGINE = InnoDB,
 PARTITION p20150328 VALUES LESS THAN (1427558400) ENGINE = InnoDB,
 PARTITION p20150329 VALUES LESS THAN (1427644800) ENGINE = InnoDB,
 PARTITION p20150330 VALUES LESS THAN (1427731200) ENGINE = InnoDB,
 PARTITION p20150331 VALUES LESS THAN (1427817600) ENGINE = InnoDB,
 PARTITION p20150401 VALUES LESS THAN (1427904000) ENGINE = InnoDB,
 PARTITION p20150402 VALUES LESS THAN (1427990400) ENGINE = InnoDB,
 PARTITION p20150403 VALUES LESS THAN (1428076800) ENGINE = InnoDB,
 PARTITION p20150404 VALUES LESS THAN (1428163200) ENGINE = InnoDB,
 PARTITION p20150405 VALUES LESS THAN (1428249600) ENGINE = InnoDB,
 PARTITION p20150406 VALUES LESS THAN (1428336000) ENGINE = InnoDB,
 PARTITION p20150407 VALUES LESS THAN (1428422400) ENGINE = InnoDB,
 PARTITION p20150408 VALUES LESS THAN (1428508800) ENGINE = InnoDB,
 PARTITION p20150409 VALUES LESS THAN (1428595200) ENGINE = InnoDB,
 PARTITION p20150410 VALUES LESS THAN (1428681600) ENGINE = InnoDB,
 PARTITION p20150411 VALUES LESS THAN (1428768000) ENGINE = InnoDB,
 PARTITION p20150412 VALUES LESS THAN (1428854400) ENGINE = InnoDB,
 PARTITION p20150413 VALUES LESS THAN (1428940800) ENGINE = InnoDB,
 PARTITION p20150414 VALUES LESS THAN (1429027200) ENGINE = InnoDB,
 PARTITION p20150415 VALUES LESS THAN (1429113600) ENGINE = InnoDB,
 PARTITION p20150416 VALUES LESS THAN (1429200000) ENGINE = InnoDB,
 PARTITION p20150417 VALUES LESS THAN (1429286400) ENGINE = InnoDB,
 PARTITION p20150418 VALUES LESS THAN (1429372800) ENGINE = InnoDB,
 PARTITION p20150419 VALUES LESS THAN (1429459200) ENGINE = InnoDB,
 PARTITION p20150420 VALUES LESS THAN (1429545600) ENGINE = InnoDB,
 PARTITION p20150421 VALUES LESS THAN (1429632000) ENGINE = InnoDB,
 PARTITION p20150422 VALUES LESS THAN (1429718400) ENGINE = InnoDB,
 PARTITION p20150423 VALUES LESS THAN (1429804800) ENGINE = InnoDB,
 PARTITION p20150424 VALUES LESS THAN (1429891200) ENGINE = InnoDB,
 PARTITION p20150425 VALUES LESS THAN (1429977600) ENGINE = InnoDB,
 PARTITION p20150426 VALUES LESS THAN (1430064000) ENGINE = InnoDB,
 PARTITION p20150427 VALUES LESS THAN (1430150400) ENGINE = InnoDB,
 PARTITION p20150428 VALUES LESS THAN (1430236800) ENGINE = InnoDB,
 PARTITION p20150429 VALUES LESS THAN (1430323200) ENGINE = InnoDB,
 PARTITION p20150430 VALUES LESS THAN (1430409600) ENGINE = InnoDB,
 PARTITION p20150501 VALUES LESS THAN (1430496000) ENGINE = InnoDB,
 PARTITION p20150502 VALUES LESS THAN (1430582400) ENGINE = InnoDB,
 PARTITION p20150503 VALUES LESS THAN (1430668800) ENGINE = InnoDB,
 PARTITION p20150504 VALUES LESS THAN (1430755200) ENGINE = InnoDB,
 PARTITION p20150505 VALUES LESS THAN (1430841600) ENGINE = InnoDB,
 PARTITION p20150506 VALUES LESS THAN (1430928000) ENGINE = InnoDB,
 PARTITION p20150507 VALUES LESS THAN (1431014400) ENGINE = InnoDB,
 PARTITION p20150508 VALUES LESS THAN (1431100800) ENGINE = InnoDB,
 PARTITION p20150509 VALUES LESS THAN (1431187200) ENGINE = InnoDB,
 PARTITION p20150510 VALUES LESS THAN (1431273600) ENGINE = InnoDB,
 PARTITION p20150511 VALUES LESS THAN (1431360000) ENGINE = InnoDB) */ 

create index idx_tmp on track_raw_wap211_log (log_time);

Slave: Mariadb – 5.5.5-10.0.15-MariaDB-log MariaDB Server

21407 | root | localhost | jkgj_log | Query | 34 | copy to tmp table | create index idx_tmp on track_raw_wap211_log (log_time) | 0.000 |  

drop index command still has this problem.

——————————————————————

When I repeat these steps on MariaDB – 10.0.16 everything is OK.

This bug has been reported to MariaDB:

https://mariadb.atlassian.net/browse/MDEV-8038

MySQL 5.7 milestone

April 22, 2015 Architect, MYSQL, software No comments

MySQL 5.7 will be a great milestone in MySQL total history.
Oracle has released many useful new features in LAB version . MySQL is becoming more similar to Oracle database 🙂

Read this presentation I post on slideshare:

MySQL 5.7 milestone

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 : http://www.onexsoft.com/?page_id=378

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)

Architecture of data warehouse which is based on MQ

January 20, 2015 Architect, Architecture, MYSQL, rdbms, software No comments

Recently, we create a mysql data warehouse which is based on message queue.

Most companies must prepare for particular queries in their systems if they consider to split their databases or tables into many pieces.

some problems should be solved in this situation:

1. how to get correct results in-time
2. how to build strong data warehouse for future analyst

These policies were used by YHD

They have already deployed a middle-ware layer to support these requests (between web apps and databases). Every aggregation SQL was splited into many small SQLs and runs in every data nodes.The Final result is the aggregation of these all small SQLs. In this procedure, everything was computed in memory to get high performance.

In data warehouse layer, they use self-defined ETL tools to extract data from different databases to oracle-Exadata platform. Log-based data was put into hadoop and hbase.

I found a new solution

With Canal and Roma (visit previous PDF roma system) , we could build a data warehouse which is based on metaQ. (metaQ is the final storage of roma) , so we can put some simple queries on this data warehouse directly.

We could use MySQL to build this Data warehouse and use original replication in these databases (everything is simple, especially using multiple source feature via MariaDB).

Disadvantages of this architecture: MySQL database is not the best choice for data warehouse. So we need another analyst platform to handle other log-based data.

Most BI systems were built by very expensive commercial software . For small and medium sized companies, this architecture can save a lot of costs.

Client to aggregate messages:

etl_roma

split and merge aggregation :

split1split2

total architecture:

DW_arch