MYSQL INNOVATION WORKS
David Jiang
jiangchengyao@gmail.com
DTCC2011DTCC2011
Today’s Topic
MySQL Current Situation
MySQL In China
InnoSQL
Q & A
DTCC2011DTCC2011
Who am I
My name is David Jiang
Database manager of 9you.com
6+ Years in using MySQL、Oracle、MS SQL Server
Now focus on InnoDB internals development
Main project
InnoDB Secondary Buffer Pool
TPCC for windows
iMySQL-Front
InnoSQL
Book
<>
DTCC2011DTCC2011
MySQL Current Situation
2009 Sun acquisition by Oracle
MySQL belongs to Oracle
2010.12 MySQL 5.5.8 GA release
InnoDB became default storage engine
I/O performance optimization
Multi-core CPU optimization
Semi sync replication
Speed up recovery using red-black tree
Etc
DTCC2011DTCC2011
HOWEVER
Lots of things already in InnoDB Plugin
What is the next MySQL version?
And the roadmap for MySQL ?
DTCC2011DTCC2011
MySQL Branch
MariaDB
Percona
Drizzle
Facebook MySQL
Google MySQL Patch
DTCC2011DTCC2011
MySQL in China
Web Application
Sinat
Baidu
Taobao
Online Game
劲舞团
Wow
Second Life
A growing number of companies will migrate to
MySQL
DTCC2011DTCC2011
Why we choose Mysql
High Performance
High Availability
High Scalability
Open Source
Free to use
Do changes if they need
Easy to use
DTCC2011DTCC2011
What is MySQL Innovation Works
A website to create/collect various innovative patch
or storage engine for MySQL
Open source
Do not belong to any commercial company
Website: www.innomysql.org
DTCC2011DTCC2011
What is InnoSQL
A new MySQL branch version
Open source
Fully compatible to the Oracle MySQL
Extra feature:
InnoDB Secondary Buffer Pool
Independent Buffer Pool
Configurable Double Write File
DTCC2011DTCC2011
InnoDB Secondary Buffer Pool
First realization in InnoDB / MySQL
Same technology
Win7 Ready Boost
Facebook Flash Cache
Oracle Flash Cache
DTCC2011DTCC2011
Sbp Architecture DTCC2011DTCC2011
Sbp Write
Using write queue
async write
controlled by
innodb_secondary_buffer_pool_bufferd_writes
Default is 640 pages(10M)
DTCC2011DTCC2011
Sbp Write
Using background I/O
mysql> show engine innodb status\G;
……
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
I/O thread 10 state: waiting for completed aio requests (sbp write thread)
……
DTCC2011DTCC2011
Watch sbp status
Mysql>show engine innodb status\G
……
----------------------
SECONDARY BUFFER POOL
----------------------
Secondary buffer pool hit ratio is: 95.054%
Secondary buffer pool #1
size 2621440
Free pages 1801438
LRU pages 820002
Flush list len 0
State: background flushing buffered writes
Page reads 3288279, flush 355067, swap 710507, skip write overloaded 1812916, skip unuseful
0
Page reads 605.69/sec, flush 65.40/sec, swap 130.87/sec last 5429.00sec)
……
DTCC2011DTCC2011
About SBP
Why to use
SSD is more expensive
SSD capacity is small for production environment
SSD write performance is not stable
When to use
Your memory is not enough to cache the whole database.
Your workload is read intensive.
Now only for random read.
For example: your database size is ~140G, and you have
only 64G/96G memory. Then a 80G SSD will help you a
lot.
DTCC2011DTCC2011
Independent Buffer Pool
What is independent buffer pool?
Buffer pool for single table.
Why use independent buffer pool?
Reduce contention for buffer pool.
You do not want some data to swap from buffer pool.
How to use?
# ibp settings
innodb_independent_bp=tpcc.warehouse:128M;tpcc.ord
ers:1G;
DTCC2011DTCC2011
SHOW ENGINE INNODB STATUS
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1785724928; in additional pool allocated 0
Dictionary memory allocated 36040
----------------------
Buffer pool instance no 1
Buffer pool size 32767
Free buffers 31599
Database pages 1168
Old database pages 451
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1168, created 0, written 0
129.12 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 876 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 1168, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
DTCC2011DTCC2011
SHOW ENGINE INNODB STATUS
Buffer pool instance no 2 ./tpcc/warehouse.ibd
Buffer pool size 8191
Free buffers 8191
Database pages 0
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 0, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
DTCC2011DTCC2011
SHOW ENGINE INNODB STATUS
Buffer pool instance no 3 ./tpcc/orders.ibd
Buffer pool size 65535
Free buffers 65535
Database pages 0
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 0, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
DTCC2011DTCC2011
CONFIGURABLE DOUBLE WRITE FILE
WHY ?
Reduce double write operation on SSD
How?
innodb_doublewrite_file=/db1/ib_dwfile
DTCC2011DTCC2011
InnoSQL ROADMAP
Read ahead for SBP
Write Cache for InnoDB
Integrate HandlerSocket plugin to InnoSQL
Improve InnoDB write performance
Add “SHOW ENGINE INNODB TRX” command to
show more detail information.
DTCC2011DTCC2011
InnoSQL
needs
your
help
DTCC2011DTCC2011
Q & A
DTCC2011DTCC2011
MySQL Innovation Works
Today’s Topic
Who am I
MySQL Current Situation
HOWEVER
MySQL Branch
MySQL in China
Why we choose Mysql
What is MySQL Innovation Works
What is InnoSQL
InnoDB Secondary Buffer Pool
Sbp Architecture
Sbp Write
Sbp Write
Watch sbp status
About SBP
Independent Buffer Pool
SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB STATUS
Configurable Double Write File
InnoSQL ROADMAP
幻灯片编号 23
幻灯片编号 24
本文档为【姜承尧:MySQL创新工厂】,请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑,
图片更改请在作品中右键图片并更换,文字修改请直接点击文字进行修改,也可以新增和删除文档中的内容。
该文档来自用户分享,如有侵权行为请发邮件ishare@vip.sina.com联系网站客服,我们会及时删除。
[版权声明] 本站所有资料为用户分享产生,若发现您的权利被侵害,请联系客服邮件isharekefu@iask.cn,我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。