performance

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

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-Oslayer-Performance-Optimization

December 13, 2014 Architecture, MYSQL, performance No comments

upload on 2014.12

important tips of MySQL database design for better performance

Read : mysql-system-opt

MyAWR : MySQL Workload Report V3 release

December 2, 2014 Architecture, MYSQL, performance No comments

myawr-v3 release now !

history of myawr:

————————–
v1: create by noodba

mysql load information and system information 

v2: create by louis liu

myawr_v2 add :

1 more os information module.

2 multiple instance support (extend table by db_port)

3 modified some bugs (mistake value)

4 add different statistics views

v3: create by louis liu

base on myawr_v2

myawr_v3 add sys schema information

(statment statistics/index&table stat/IO latency/file&table IO stat and so on)

including performance schema and information schema

so you need open performance schema on your mysql database

myawr_v3 now support mysql 5.6 GA and will continue support mysql feature version

before install myawr_v3 you need run sys_schema to create formatted views for data collecting.

Overview: Myawr_v3 mysql performance web show

Download:https://github.com/ylouis83/myawr/tree/master

Webm: MySQL database performance web monitor

September 28, 2014 Architect, MYSQL, performance No comments , ,

You can download webm from github: https://github.com/ylouis83/webm

webm: mysql web key performance monitor

webm is a tool that display key value graph on website and webm was developed by javascript and mysqlmon ( mysql data collection tool wrote by AnySQL)

Environment need:

Linux version 5+ php5 Apache server

You can also run this tool on windows platform (install xampp )

and webo will come soon ( oracle web monitor tools 🙂 )

login webm system
webm_login

mysql redo/binlog size per 10 seconds
webm1

mysql insert/update (little delete) per 10 seconds
webm2

mysql select (QPS) per 10 seconds
webm3

Some data collection display

August 21, 2014 MYSQL, performance No comments , , ,

Reference Oracle AWR. These two was developed by python and perl.

Both of these are following data collection method.

Use these tools can help quick identify problem.

MyAWR v2 –collection of mysql and os information

MongoAWR v1 — collection of mongodb and os information

Advanced Pagination for MySQL

July 28, 2014 MYSQL, performance 2 comments

看到叶金荣的一篇关于mysql分页的文章,结合雅虎之前发的一篇PDF 谈谈自己的看法

在叶子的文章里谈到了使用inner join 从而减少了对page的扫描也就是减少了所谓的回表 例如:

SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id)

通过直接对id的操作 而不是整张表的扫描 通过id 的join 抓出符合条件id 然后通过ID 再去做数据的抓取。这样就避免了对不需要的页面的扫描。

不过这样也不是最佳的方法 还可以通过对id 的 range更加缩小范围 例如:

我们要分100条记录分一页 可以写成

$page_size=100 select * from t where id > 99 order by id asc limit $page_size ; select * from  t where id >199 order by id asc limit $page_size;

尽量避免limit M,N 这种写法 mysql在对M值很大 而offset很小的时候的处理方式很不人性化 ,所以尽量不要使用offset来取得特定行数。

在这里有一个问题 比如根据不是唯一索引的column分页 那么可能存在一个问题,例如一个列column1存在11个key=100的值 那么你使用limit N 之后取到的min value还是同一个值
这种情况如何处理? 给个例子:

比如要每10条记录分一页

select * from t  order by column1 desc  limit 10

注意这里取到的min value还是100 (11个连续的100) 对下面的分页会产生影响,如何处理?

雅虎给出的方案非常好 取一个extra的column 例如PK 或者unique index key 例如:

select * from t  order by column1 desc, id desc   limit 10 -- 第一个页
select * from t  where column1 <=minvalue_col1 and (id < minvalue_id or column1 < minvalue_col1) limit 10  ---第二个页

这样就确保了唯一性 保证了每页的数据不会重复 思想就是通过add一个唯一的extra 取得这个extra的边界值 结合range column来进行分页。

这个SQL 还能被优化成:

SELECT m2.* FROM t m1, t m2  WHERE m1.id = m2.id  AND m1.column1 <= minvalue_col1
AND (m1.id < minvalue_id OR m1.column1 < minvalue_col1)  ORDER BY m1.column1 DESC, m1.id DESC  LIMIT 10; 

核心思想: 通过extra过滤 配合ID扫描 避免大量的回表操作 这样就达到了要取多少条 就扫描多少条 (in page)

MySQL 5.5&5.6 new features summary

February 10, 2014 Architecture, MYSQL, performance No comments

upload on 2014.2 [MySQL 5.5&5.6 new features summary] Download this PDF

Recent MySQL performance Test detial

June 14, 2013 MYSQL, performance 2 comments

最近team基于mysql的性能基准测试,包含多种场景

MyAWR another mysql awr –That is Great !!

May 22, 2013 Architecture, MYSQL, performance No comments

MyAWR 开源了,第一版的reference 已经放出, tx to noodba

MyAWR_Reference_Guid
slideshare地址:MyAWR (awr of MySQL)
MyAWR template:MySQL WorkLoad Report