MySQL Newsletter - Special Edition: InnoDB
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.
- 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 Paper: MySQL 5.5 - Storage Engine Performance Benchmark for MyISAM and InnoDB
- Case Study: MySQL Powers the Market Leading Telecoms Network Management Framework
- InnoDB: Status, Architecture, and Latest Enhancements - by Inaam Rana and John Russell
- InnoDB: Performance and Scalability Features - by Calvin Sun
- 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
Oracle's MySQL engineers will deliver several sessions and be available to answer all your questions.
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.
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.
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.
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.
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!
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.
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.
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.
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.
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.
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.
What does purge exactly do and why is it needed? If you have ever wondered then read on.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Want to receive the latest MySQL updates and interact with the MySQL community?
Want to evaluate the advanced capabilities in the MySQL Enterprise Edition?