首页 姜承尧:MySQL创新工厂

姜承尧:MySQL创新工厂

举报
开通vip

姜承尧:MySQL创新工厂 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 i...

姜承尧:MySQL创新工厂
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,我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
下载需要: 免费 已有0 人下载
最新资料
资料动态
专题动态
is_688726
暂无简介~
格式:pdf
大小:317KB
软件:PDF阅读器
页数:24
分类:互联网
上传时间:2011-11-16
浏览量:24