_55wSCS.ppt

Size: px

Start display at page:

Download "_55wSCS.ppt"

せぴあいせき
6 years ago
Views:

1 <Insert Picture Here> MySQL MySQL 5.5 MySQL, MySQL Principal Sales Consultant, Asia Pacific & Japan

3 The world's most popular open source database

4 MySQL 60.5% PostgreSQL 51.9% : 3 (2009 PostgreSQL MySQL Oracle SQL Server MySQL PostgreSQL IBM DB2 : ITmedia 6 DBMS ITmedia ITR

Google DE 16. Google HK 17. Wordpress 18. Amazon.com 19. Google UK 20. Sina.

5 Who is Using MySQL The Top 20 Websites 1. Google 2. Facebook 3. Youtube 4. Yahoo 5. Windows Live 6. Wikipedia 7. Baidu 8. Blogger 9. MSN 10. QQ 11. Twitter 12. Yahoo JP 13. Google IN 14. Taobao 15. Google DE 16. Google HK 17. Wordpress 18. Amazon.com 19. Google UK 20. Sina...and many more: Flickr, Second Life, Craigslist, Slashdot, LiveJournal, Del.icio.us, Pricegrabber.com, Weather.com etc.

6 Alexa - Top Site in Japan (Dec. 2010) 1. Yahoo!Japan 2. Google JP 3. FC2 4. YouTube Ameba 7. Google Wikipedia 10.Amazon JP 11. goo 12. mixi Twitter 15. MSN 16. Ameba Facebook GREE DMM.com JWord dwango pixiv SONY Life-X

7 MySQL" "

8 MySQL Strategy

9 Investment in MySQL Rapid Innovation Make MySQL a Better MySQL #1 Open Source Database for Web Applications Most Complete LAMP Stack Telecom & Embedded Develop, Promote and Support MySQL Improve engineering, consulting and support Leverage 24x7, World-Class Oracle Support MySQL Community Edition Source and binary releases GPL license

10 Oracle + MySQL Customers Product Integration Oracle GoldenGate (Complete!) Oracle Enterprise Linux + Oracle VM (CY 2011) Oracle Secure Backup (CY 2011) Oracle Audit Vault (CY 2011) Oracle Enterprise Manager (CY 2011) Support Leverage 24x7, World-Class Oracle Support MyOracle Support

11 MySQL is Everywhere Multiple Platforms Multiple Languages C C++ C#

12 MySQL

13 MySQL 5.5 InnoDB ACID /CPU GA (Semi-synchronous) SIGNAL/RESIGNAL PERFORMANCE_SCHEMA 4 UTF-8

14 MySQL InnoDB Multiple Buffer Pool Instances Multiple Rollback Segments Extended Change Buffering (with delete buffering, purge buffering) Improved Purge Scheduling Improved Log Sys mutex Separate Flush List mutex MySQL Better Metadata Locking within Transactions Split LOCK_open mutex Eliminated LOCK_alarm mutex as bottleneck Eliminated LOCK_thread_count as bottleneck Improved Performance/Scale on Win32, 64 10

15 MySQL 5.5 Sysbench MySQL (New InnoDB) MySQL (InnoDB Plug-in) MySQL (InnoDB built-in) Intel Xeon X7460 x86_64 4 CPU x 6 Cores/CPU 2.66 GHz, 32GB RAM Fedora 10

16 MySQL 5.5 Sysbench MySQL (New InnoDB) MySQL (InnoDB Plug-in) MySQL (InnoDB built-in) Intel Xeon X7460 x86_64 4 CPU x 6 Cores/CPU 2.66 GHz, 32GB RAM Fedora 10

17 MySQL 5.5 Scales on multi core SysBench Read Write MySQL Transactions/Second MySQL MySQL 5.1 AMD Opteron 7160 MHz 64 GB memory 2 x Intel X25E SSD drives OS is Oracle Enterprise Linux with the Enterprise Kernel 4 sockets with a total of 48 cores.

18 Improved Recovery Performance Crash Recovery Minutes plugin InnoDB Total Scanning Log Applying > 10x recovery performance gain for MySQL 5.5 over warehouses Database=9800MB Innodb_log_file_size=2x1950MB Buffer_pool-12GB Started tested, killed server@5 mins Intel Xeon X7460 x86_64 4 CPU x 6 Cores/CPU 2.66 GHz, 32GB RAM Fedora 10

19 MySQL on Windows The Right Choice Windows MySQL MySQL Making MySQL better on Windows Windows MySQL 5.5 Benchmarks MySQL Workbench New Connector/NET OS

MySQL 5.5 SysBench Benchmarks Windows MySQL 5.5.6 (New InnoDB) MySQL 5.

5 over 5.1.50; at scale Intel x86_64 4 CPU x 2 Cores/CPU 3.

20 MySQL 5.5 SysBench Benchmarks Windows MySQL (New InnoDB) MySQL (InnoDB Plug-in) MySQL (InnoDB built-in) 538% performance gain for MySQL 5.5 over ; at scale Intel x86_64 4 CPU x 2 Cores/CPU GHz, 8GB RAM Windows Server 2008

21 MySQL 5.5 SysBench Benchmarks Windows MySQL (New InnoDB) MySQL (InnoDB Plug-in) MySQL (InnoDB built-in) 1561% performance gain for MySQL 5.5 over ; at scale Intel x86_64 4 CPU x 2 Cores/CPU GHz, 8GB RAM Windows Server 2008

22 MySQL Server InnoDB Plugin MySQL 5.1 InnoDB Plugin ( ) InnoDB InnoDB Plugin : / Google GA

23 Application Commit Response Changing Data Connection Thread Data Changing Binlog Binlog Replication Relaylog Changing Data Data Master Slave

24 MySQL (Semisynchronous) 2. fsync & fsyncs ID Copyright Oracle Corporation

25 MySQL (RBR) 6. 'FLUSH LOGS' 7. InnoDB MyISAM Copyright Oracle Corporation

26 Application Commit Response Changing Data Connection Thread Data Changing Binlog Binlog Response Replication Relaylog Changing Data Data Master Slave

27 UTF-8 byte ASCII 2 3 CJK BMP 4 3 New!! 3 4

28 4 UTF-8 MySQL 5.1 UTF-8 > BMP > 1 3 > utf8 > 4 binary MySQL 5.5 UTF-8 > Unicode utf8: 3 utf8mb3: utf8 utf8mb4: 4 28

29 BEFORE create table t1 ( id int unsigned not null auto_increment, mydate date not null, primary key(id,mydate) ) partition by range (to_days(mydate)) ( partition p1 values less than (to_days(' ')), partition p2 values less than (to_days(' ')), partition p3 values less than (to_days(' ')), partition p4 values less than (to_days(' ')), partition p5 values less than (to_days(' ')), partition p6 values less than (to_days(' ')), partition p7 values less than (maxvalue) );

30 AFTER create table t2 ( id int unsigned not null auto_increment, mydate date not null, primary key(id,mydate) ) partition by range columns (mydate) ( partition p1 values less than (' '), partition p2 values less than (' '), partition p3 values less than (' '), partition p4 values less than (' '), partition p5 values less than (' '), partition p6 values less than (' '), partition p7 values less than (maxvalue) );

31 CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `mydate` date NOT NULL, PRIMARY KEY (`id`,`mydate`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (to_days(mydate)) (PARTITION p1 VALUES LESS THAN (730576) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (731063) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (732251) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (733407) ENGINE = InnoDB, PARTITION p5 VALUES LESS THAN (734197) ENGINE = InnoDB, PARTITION p6 VALUES LESS THAN (734593) ENGINE = InnoDB, PARTITION p7 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ FROM_DAYS()

32 create table t3 ( id int unsigned not null auto_increment, myyear smallint not null, mymonth tinyint unsigned not null, primary key(id,myyear,mymonth) ) partition by range columns (myyear,mymonth) ( partition p1 values less than (2000, 4), partition p2 values less than (2001, 8), partition p3 values less than (2004, 11), partition p4 values less than (2008, 2), partition p5 values less than (2010, 3), partition p6 values less than (2011, 4), partition p7 values less than (maxvalue, maxvalue) );

33 5.5 MySQL Server - GA InnoDB & (Semi-synchronous) 4 UTF-8 New MySQL Enterprise Edition & MySQL Enterprise Backup

MySQL5.5 MySQL

MySQL5.5 MySQL Twitter: @RKajiyama The world's most popular open source database MySQL 60.5% PostgreSQL 51.9% : 3 (2009 PostgreSQL MySQL Oracle SQL Server MySQL PostgreSQL IBM DB2 : ITmedia 6 DBMS ITmedia