June 2011
This is a special edition of the MySQL Newsletter. A MySQL Newsletter Special Edition is issued each quarter, highlighting technical information on topics most relevant to MySQL DBAs and developers. This edition is focused InnoDB, the most popular and default storage engine of the MySQL database.
Articles in this newsletter:
Highlights
- Event: Join us at OSCON 2011, July 25-29, Portland, OR
- Event: Upcoming MySQL HA Breakfast Seminars in the UK and Germany
- Event: Oracle Open World 2011 - Special MySQL Discount, Register Now and Save 1,000 USD
- New Whitepaper: Unlocking New Value from Web Session Management
- Live Webinar: Hot Java Application Performance: MySQL Connector/J Performance Gems
- On-demand Webinar: InnoDB Enhancements and Roadmap
- MySQL TCO Savings Calculator
Focus on InnoDB:
White Papers & Case Studies
- White Paper: MySQL 5.5 - Storage Engine Performance Benchmark for MyISAM and InnoDB
- Case Study: MySQL Powers the Market Leading Telecoms Network Management Framework
Technical Articles
- Introduction to MySQL 5.5
- What's New in MySQL 5.6
- NoSQL to MySQL with Memcached
Presentations from Recent Conferences
- InnoDB: Status, Architecture, and Latest Enhancements - by Inaam Rana and John Russell
- InnoDB: Performance and Scalability Features - by Calvin Sun
PlanetMySQL Blog Posts
- MySQL 5.5: InnoDB as Default Storage Engine, by John Russell
- MySQL 5.6: Multi threaded purge, by Sunny Bains
- Tips and Tricks for Faster DDL, by Marko Makela
- MySQL 5.6: Data dictionary LRU, by Sunny Bains
- Introducing page_cleaner thread in InnoDB, by Inaam Rana
- Information Schema for InnoDB System Tables, by Jimmy Yang
- InnoDB Persistent Statistics at last, by Vasil Dimov
- InnoDB Persistent Statistics Save the Day, by Øystein Grøvlen
- MySQL 5.6: InnoDB scalability fix - Kernel mutex removed, by Sunny Bains
- NoSQL to InnoDB with Memcached, by Calvin Sun
- Get started with InnoDB Memcached Daemon plugin, by Jimmy Yang
- Get started with InnoDB Metrics Table, by Jimmy Yang
- Five part blog series on MySQL 5.6 Performance, by Dimitri Kravtchuk
- MySQL Performance: 5.6 Notes, part 1: Discovery
- MySQL Performance: 5.6 Notes, part 2: Under full dbSTRESS workload
- MySQL Performance: 5.6 Notes, part 3: More in depth
- MySQL Performance: 5.6 Notes, part 4: Fixing Purge issue
- MySQL Performance: 5.6 Notes, part 5: Fixing Adaptive Flushing
MySQL Training and Consulting
- MySQL Training
- NEW: MySQL Performance Tuning
- Oracle's MySQL Consulting Services
-------------------------------------------------------------------
Highlights
Event: Join us at OSCON 2011, July 25-29, Portland, OR
Oracle's MySQL engineers will deliver several sessions and be available to answer all your questions.
We look forward to seeing you there:
http://www.oscon.com/oscon2011
Event: Upcoming MySQL HA Breakfast Seminars in the UK and Germany
London, UK, June 29th, 2011, 9.00 am
Düsseldorf, Germany, July 13th, 2011, 9.00 am
Join us for our High Availability Solutions for MySQL breakfast seminars in London and Düsseldorf to better understand how to achieve High Availability with MySQL. During these free Oracle seminars, we will review the various options and technologies at your disposal to implement highly available and highly scalable MySQL infrastructures, as well as best practices in terms of architectures.
Register Now for London:
http://www.oracle.com/webapps/events/ns/EventsDetail.jsp?p_eventId=134424&src=7314534&src=7314534&Act=7
Register Now for Düsseldorf:
http://www.oracle.com/webapps/events/ns/EventsDetail.jsp?p_eventId=135255&src=7314534&src=7314534&Act=12
Event: Oracle Open World 2011 - Special MySQL Discount, Register Now and Save 1,000 USD
Register Now and get a special early bird extra discount for the MySQL community members! Use the code "MYSQL11" when registering at:
http://www.oracle.com/go/?&Src=7120737&Act=29&pcode=WWMK10080712MPP001
New Whitepaper: Unlocking New Value from Web Session Management
As organizations seek to enhance their users' web experience through personalization based on historic browsing and buying behaviors, session data is becoming increasingly critical. Larger volumes of session data need to be managed, persisted and analyzed in real-time, and so session management has become more performance-intensive, while also demanding very high levels of availability to ensure a seamless customer experience. For rapidly growing web properties, it makes sense to evaluate the MySQL Cluster database.
This is a practical whitepaper featuring code samples that further discusses the challenges and solutions to session management.
Read the White Paper:
http://www.mysql.com/why-mysql/white-papers/mysql_wp_session_mngmnt.php
Live Webinar: Hot Java Application Performance: MySQL Connector/J Performance Gems
In a command performance, Mark Matthews, the author of MySQL Connector/J, is back to show you how to get the very best performance out of your Java applications deployed on MySQL by not using the out-of-the-box configuration. Connector/J has nearly 200 configuration options, many of which affect performance. While these options are documented, many aren't shown alongside the use cases they apply to. Mark will show you how to move from the generic to a customized configuration, illustrating how to set Connector/J options that will deliver the best performance for your specific application.
Register for the webinar:
http://www.mysql.com/news-and-events/web-seminars/display-635.html
On-demand Webinar: InnoDB Enhancements and Roadmap
Oracle's InnoDB is the most popular, reliable, and proven transactional storage engine for MySQL. InnoDB provides transactions, row-level locking, multiversion concurrency control for superior performance, referential integrity, and all four ANSI SQL transaction isolation levels to ensure the integrity of your database. In this session, you will learn about InnoDB architecture and some innovative technologies in InnoDB for performance and data protection. You will also hear about new performance-enhancing features to improve concurrency and scalability in InnoDB.
Watch the Webinar:
http://www.mysql.com/news-and-events/on-demand-webinars/display-od-584.html
MySQL TCO Savings Calculator
Do you know that you can save up to 90% on database TCO using MySQL compared to Microsoft SQL Server? Customize the TCO Savings Calculator with your system configuration and see the difference for yourself!
Customize the TCO Savings Calculator Now:
http://www.mysql.com/tcosavings/
-------------------------------------------------------------------
White Papers & Case Studies
White Paper: MySQL 5.5 - Storage Engine Performance Benchmark for MyISAM and InnoDB
The purpose of this whitepaper is to directly compare performance of the latest InnoDB 1.1 release included with MySQL 5.5 and MyISAM, using a benchmark that is commonly run to measure MySQL throughput.
Read the White Paper:
http://www.mysql.com/why-mysql/white-papers/mysql_5.5_perf_myisam_innodb.php
Case Study: MySQL Powers the Market Leading Telecoms Network Management Framework
WebNMS, the leading network and element management solution, has been embedding MySQL as the default database for over ten years. To accommodate ever more stringent demands for availability and performance in the market, WebNMS and MySQL can write up to 10 million rows daily and support 2,000 transactions per second across a replicated architecture.
Read the Case Study:
http://www.mysql.com/why-mysql/case-studies/mysql-cs-webnms.php
-------------------------------------------------------------------
Technical Articles
Introduction to MySQL 5.5
What's New in MySQL 5.5? Here are some improvements to the speed, scalability, and user-friendliness of the MySQL database and the InnoDB storage engine that we think deserve their own headlines.
Read the article:
http://dev.mysql.com/tech-resources/articles/introduction-to-mysql-55.html
What's New in MySQL 5.6
With MySQL 5.5 generally available and being deployed all over the planet, the architects and strategists can start looking ahead to the exciting new thing with SQL in its name: MySQL 5.6. MySQL 5.6 builds on the momentum of 5.5, and Oracle's investment and commitment to MySQL, by delivering better performance and scalability.
Read the article:
http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html
NoSQL to MySQL with Memcached
The ever increasing performance demands of web-based services has generated significant interest in providing NoSQL access methods to MySQL - enabling users to maintain all of the advantages of their existing relational database infrastructure, while providing blazing fast performance for simple queries, using an API to complement regular SQL access to their data.
Read the article:
http://dev.mysql.com/tech-resources/articles/nosql-to-mysql-with-memcached.html
-------------------------------------------------------------------
Presentations from Recent Conferences
InnoDB: Status, Architecture, and Latest Enhancements - by Inaam Rana and John Russell
Download the presentation (PDF):
http://www.innodb.com/wp/wp-content/uploads/2011/04/innodb_status_uc_2011.pdf
InnoDB: Performance and Scalability Features - by Calvin Sun
Download the presentation (PDF):
http://www.innodb.com/wp/wp-content/uploads/2011/04/InnoDB_Performance_IOUG_2011.pdf
-------------------------------------------------------------------
PlanetMySQL Blog Posts
MySQL 5.5: InnoDB as Default Storage Engine
John Russell
MySQL has a well-earned reputation for being easy-to-use and delivering performance and scalability. In previous versions, MyISAM was the default storage engine. In our experience, most users never changed the default settings. With MySQL 5.5, InnoDB becomes the default storage engine. Again, we expect most users will not change the default settings. But, because of InnoDB, the default settings deliver the benefits users expect from their RDBMS: ACID Transactions, Referential Integrity, and Crash Recovery. Lets explore how using InnoDB tables improves your life as a MySQL user, DBA, or developer.
Read the PlanetMySQL BlogPost:
http://blogs.innodb.com/wp/2010/09/mysql-5-5-innodb-as-default-storage-engine/
MySQL 5.6: Multi threaded purge
Sunny Bains
What does purge exactly do and why is it needed? If you have ever wondered then read on.
Read the PlanetMySQL BlogPost:
http://blogs.innodb.com/wp/2011/04/mysql-5-6-multi-threaded-purge/
Tips and Tricks for Faster DDL
Marko Makela
Data Dictionary Language (DDL) operations have traditionally been slow in MySQL. Any change to the table definition would be implemented by creating a copy of the table and index layout with the requested changes, copying the table contents row by row, and finally renaming tables and dropping the original table.
Read the PlanetMySQL BlogPost:
http://blogs.innodb.com/wp/2011/04/tips-and-tricks-for-faster-ddl/
MySQL 5.6: Data dictionary LRU
Sunny Bains
In MySQL 5.6 we've added a new feature that closes and unloads table instances from the InnoDB internal data dictionary, once a user configurable threshold is reached. This ends the situation where you could have hundreds of megabytes caching rarely used entries until the server was restarted and will be particularly appreciated by hosting and software as a service providers.
Read the PlanetMySQL BlogPost:
http://blogs.innodb.com/wp/2011/04/mysql-5-6-data-dictionary-lru/
Introducing page_cleaner thread in InnoDB
Inaam Rana
In MySQL 5.6.2 we have introduced a new background thread named the page_cleaner in InnoDB. Adaptive flushing of modified buffer pool pages in the main background thread, async flushing by a foreground query thread if the log files are near to wrapping around, idle flushing and shutdown flushing are now moved to this thread, leaving only the last resort sync flushing in foreground query threads. We╒ve also added counters for these activities.
Read the PlanetMySQL BlogPost:
http://blogs.innodb.com/wp/2011/04/introducing-page_cleaner-thread-in-innodb/
Information Schema for InnoDB System Tables
Jimmy Yang
In the MySQL 5.6 release, "innodb_table_monitor" can become history, and you will no longer need to search the text output to find out system metadata information. Instead, you can query the InnoDB internal system tables through Information Schema System Tables we implemented in this release.
Read the PlanetMySQL BlogPost:
http://blogs.innodb.com/wp/2011/04/information-schema-system-table/
InnoDB Persistent Statistics at last
Vasil Dimov
InnoDB gathers statistics for the data in user tables, which are used by the MySQL optimizer to choose the best query plan. For a long time the imprecision and instability of these statistics have been creating problems for users. To solve this problem MySQL 5.6.2 introduces the InnoDB Persistent Statistics feature.
Read the PlanetMySQL BlogPost:
http://blogs.innodb.com/wp/2011/04/innodb-persistent-statistics-at-last/
InnoDB Persistent Statistics Save the Day
Øystein Grøvlen
I decided to try out InnoDB Persistent Statistics that is one of the new features in the recent MySQL 5.6.2 Development Milestone Release. According to the advertisement, this should give both more accurate and more stable statistics.
Read the PlanetMySQL BlogPost:
http://oysteing.blogspot.com/2011/05/innodb-persistent-statistics-save-day.html
MySQL 5.6: InnoDB scalability fix - Kernel mutex removed
Sunny Bains
For those interested in InnoDB internals, this post tries to explain why the global kernel mutex was required and the new mutexes and rw-locks that now replace it. Along with the long-term benefit from this change.
Read the PlanetMySQL BlogPost:
http://blogs.innodb.com/wp/2011/04/mysql-5-6-innodb-scalability-fix-kernel-mutex-removed/
NoSQL to InnoDB with Memcached
Calvin Sun
MySQL is the most popular open source SQL database. The ever-increasing performance demands of web-based services have generated significant interest in providing NoSQL access methods to MySQL. Today, MySQL is announcing the preview of the NoSQL to InnoDB via memcached. This offering provides users with the best of both worlds: maintain all of the advantages of rich SQL query language, while providing better performance for simple queries via direct access to shared data.
Read the PlanetMySQL BlogPost:
http://blogs.innodb.com/wp/2011/04/nosql-to-innodb-with-memcached/
Get started with InnoDB Memcached Daemon plugin
Jimmy Yang
As Calvin mentioned in his blog "NoSQL to InnoDB with Memcached", we just released a technology preview of the feature that makes memcached a MySQL Daemon Plugin. And this preview release demonstrates how users can go around SQL Optimizer and Query Processing and directly interact with InnoDB Storage Engine through InnoDB APIs. Here, we would like to walk with you step by step to see how to get the memcached Daemon Plugin set up and get it running.
Read the PlanetMySQL BlogPost:
http://blogs.innodb.com/wp/2011/04/get-started-with-innodb-memcached-daemon-plugin/
Get started with InnoDB Metrics Table
Jimmy Yang
In our 5.6 release, we introduced a new feature called "InnoDB Metrics Table" as part of our effort to extend InnoDB diagnostic and monitoring capability. The feature consolidates all InnoDB related Performance and Resource related counters (as of today, 176 counters) and makes them available through an information schema table. In addition, it gives the user the ability to control these counters for their monitoring need.
Read the PlanetMySQL BlogPost:
http://blogs.innodb.com/wp/2011/04/innodb-metrics-table/
Five part blog series on MySQL 5.6 Performance
Dimitri Kravtchuk
MySQL Performance: 5.6 Notes, part 1: Discovery
Read the PlanetMySQL BlogPost:
http://dimitrik.free.fr/blog/archives/2011/04/mysql-performance-56-notes-part-1-discovery.html
MySQL Performance: 5.6 Notes, part 2: Under full dbSTRESS workload
Read the PlanetMySQL BlogPost:
http://dimitrik.free.fr/blog/archives/2011/04/mysql-performance-56-notes-part-2-under-full-dbstress-workload.html
MySQL Performance: 5.6 Notes, part 3: More in depth
Read the PlanetMySQL BlogPost:
http://dimitrik.free.fr/blog/archives/2011/04/mysql-performance-56-notes-part-3-more-in-depth.html
MySQL Performance: 5.6 Notes, part 4: Fixing Purge issue
Read the PlanetMySQL BlogPost:
http://dimitrik.free.fr/blog/archives/2011/04/mysql-performance-56-notes-part-4-fixing-purge-issue.html
MySQL Performance: 5.6 Notes, part 5: Fixing Adaptive Flushing
Read the PlanetMySQL BlogPost:
http://dimitrik.free.fr/blog/archives/2011/04/mysql-performance-56-notes-part-5-fixing-adaptive-flushing.html
-------------------------------------------------------------------
MySQL Training and Consulting
MySQL Training
All of the MySQL courses on the Oracle MySQL curriculum paths cover InnoDB. In addition, there are entire sections on InnoDB in the Beginners, Developers, DBA, Performance Tuning courses. The Performance Tuning course was recently updated and validated on MySQL 5.5 environment.
NEW: MySQL Performance Tuning
In this class, you will learn practical, safe, and highly efficient methods to optimize performance for the MySQL Server. Starting with the basics, you will be able to identify areas to tune, understand performance tuning tools and get detailed guidance for tuning a MySQL Server instance. You will also learn the best uses of the InnoDB storage engine in performance tuning.
For course details and schedules:
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=402&p_nl=JMSQ
Oracle's MySQL Consulting Services
Only Oracle's MySQL consultants have access to the MySQL engineers, architects and domain experts, and are backed by the World Class Oracle MySQL support team. They can help you tackle your most complex issues, from performance tuning to architecture & design and High Availability projects.
Learn More:
http://www.mysql.com/consulting/
-------------------------------------------------------------------
Want to receive the latest MySQL updates and interact with the MySQL Community?
Read Oracle's MySQL Blog:
http://blogs.oracle.com/mysql/
Join MySQL on Facebook:
http://www.facebook.com/mysql
Follow MySQL on Twitter:
http://twitter.com/mysql
Want to evaluate the advanced capabilities in the MySQL Enterprise Edition?
Try Now with MySQL Enterprise Edition Trial:
http://www.mysql.com/trials/
-------------------------------------------------------------------
About the Newsletter
You can send us queries and give us feedback online.
http://www.mysql.com/contact/?topic=newsletter
You can unsubscribe from this newsletter online:
http://www.mysql.com/unsub?email=johandontmessup@gmail.com
Problems reading the newsletter in your email client? View the newsletter on the web.
http://www.mysql.com/news-and-events/newsletter/2011/2011-06.html
--
Copyright (c) 2011, Oracle Corporation and/or its affiliates.
You are free to distribute this newsletter, as long as you don't make any changes.
