Monday Jun 23, 2014

InnoDB, The Choice for High Concurrency Database Systems

InnoDB has proven to be a reliable data storage engine for modern, high concurrency database systems. It is fully ACID compliant, and supports a wide range of isolation modes, from READ-UNCOMMITEED to SERIALIZABLE.

InnoDB multiversion concurrency control (MVCC) enables records and tables to be updated without the overhead associated with row-level locking mechanisms. The MVCC implementation in InnoDB largely eliminates the need to lock tables or rows during updates, and enables good performance for high concurrency workloads.

To learn more about this subject and related performance tuning topics tuning, take the MySQL Performance Tuning training course. This 4-day, instructor-led course is available as:

  • Training-on-Demand: Start training within 24 hours of registration. You will follow lecture material at your own pace through streaming video and you can book time on a lab environment to suit your schedule.
  • Live-Virtual Events: Attend a live class from your own desk, no travel required. Choose from a selection of events on the schedule to suit different timezones.
  • In-Class Events: Travel to an education center to take this class. Below is a selection of the in-class events already on the schedule.



 Delivery Language

 Sao Paulo, Brazil

 24 June 2014

Brazilian Portuguese 

 Brussels, Belgium

 4 August 2014


 Shanghai, China

 5 August 2014

Simplified Chinese 

 London, England

 20 October 2014


Milan, Italy 

 28 July 2014


 Riga, Latvia

 29 September 2014


Petaling Jaya, Malaysia 

22 September 2014  


 Utrecht, Netherlands

4 August 2014  


 Warsaw, Poland

 14 July 2014 


 Barcelona, Spain

 14 October 2014


 Istanbul, Turkey

 4 August 2014


To register for this class, request an additional event or learn more about the authentic MySQL Curriculum, go to

Thursday Dec 20, 2012

Innodb Read Only Mode

Offers user a easy way to keep data write protected on RW and allows server to run even on RO media.[Read More]

Thursday Dec 13, 2012

NoSQL Memcached API for MySQL: Latest Updates

With data volumes exploding, it is vital to be able to ingest and query data at high speed. For this reason, MySQL has implemented NoSQL interfaces directly to the InnoDB and MySQL Cluster (NDB) storage engines, which bypass the SQL layer completely. Without SQL parsing and optimization, Key-Value data can be written directly to MySQL tables up to 9x faster, while maintaining ACID guarantees.

In addition, users can continue to run complex queries with SQL across the same data set, providing real-time analytics to the business or anonymizing sensitive data before loading to big data platforms such as Hadoop, while still maintaining all of the advantages of their existing relational database infrastructure.

This and more is discussed in the latest Guide to MySQL and NoSQL where you can learn more about using the APIs to scale new generations of web, cloud, mobile and social applications on the world's most widely deployed open source database

The native Memcached API is part of the MySQL 5.6 Release Candidate, and is already available in the GA release of MySQL Cluster. By using the ubiquitous Memcached API for writing and reading data, developers can preserve their investments in Memcached infrastructure by re-using existing Memcached clients, while also eliminating the need for application changes.

Speed, when combined with flexibility, is essential in the world of growing data volumes and variability. Complementing NoSQL access, support for on-line DDL (Data Definition Language) operations in MySQL 5.6 and MySQL Cluster enables DevOps teams to dynamically update their database schema to accommodate rapidly changing requirements, such as the need to capture additional data generated by their applications. These changes can be made without database downtime.

Using the Memcached interface, developers do not need to define a schema at all when using MySQL Cluster.

Lets look a little more closely at the Memcached implementations for both InnoDB and MySQL Cluster.

Memcached Implementation for InnoDB

The Memcached API for InnoDB is previewed as part of the MySQL 5.6 Release Candidate.

As illustrated in the following figure, Memcached for InnoDB is implemented via a Memcached daemon plug-in to the mysqld process, with the Memcached protocol mapped to the native InnoDB API.

Figure 1: Memcached API Implementation for InnoDB

With the Memcached daemon running in the same process space, users get very low latency access to their data while also leveraging the scalability enhancements delivered with InnoDB and a simple deployment and management model. Multiple web / application servers can remotely access the Memcached / InnoDB server to get direct access to a shared data set.

With simultaneous SQL access, users can maintain all the advanced functionality offered by InnoDB including support for Foreign Keys, XA transactions and complex JOIN operations.

Benchmarks demonstrate that the NoSQL Memcached API for InnoDB delivers up to 9x higher performance than the SQL interface when inserting new key/value pairs, with a single low-end commodity server supporting nearly 70,000 Transactions per Second.

Figure 2: Over 9x Faster INSERT Operations

The delivered performance demonstrates MySQL with the native Memcached NoSQL interface is well suited for high-speed inserts with the added assurance of transactional guarantees.

You can check out the latest Memcached / InnoDB developments and benchmarks here

You can learn how to configure the Memcached API for InnoDB here

Memcached Implementation for MySQL Cluster

Memcached API support for MySQL Cluster was introduced with General Availability (GA) of the 7.2 release, and joins an extensive range of NoSQL interfaces that are already available for MySQL Cluster

Like Memcached, MySQL Cluster provides a distributed hash table with in-memory performance. MySQL Cluster extends Memcached functionality by adding support for write-intensive workloads, a full relational model with ACID compliance (including persistence), rich query support, auto-sharding and 99.999% availability, with extensive management and monitoring capabilities.

All writes are committed directly to MySQL Cluster, eliminating cache invalidation and the overhead of data consistency checking to ensure complete synchronization between the database and cache.

Figure 3: Memcached API Implementation with MySQL Cluster

Implementation is simple:

1. The application sends reads and writes to the Memcached process (using the standard Memcached API).

2. This invokes the Memcached Driver for NDB (which is part of the same process)

3. The NDB API is called, providing for very quick access to the data held in MySQL Cluster’s data nodes.

The solution has been designed to be very flexible, allowing the application architect to find a configuration that best fits their needs. It is possible to co-locate the Memcached API in either the data nodes or application nodes, or alternatively within a dedicated Memcached layer.

The benefit of this flexible approach to deployment is that users can configure behavior on a per-key-prefix basis (through tables in MySQL Cluster) and the application doesn’t have to care – it just uses the Memcached API and relies on the software to store data in the right place(s) and to keep everything synchronized.

Using Memcached for Schema-less Data

By default, every Key / Value is written to the same table with each Key / Value pair stored in a single row – thus allowing schema-less data storage. Alternatively, the developer can define a key-prefix so that each value is linked to a pre-defined column in a specific table.

Of course if the application needs to access the same data through SQL then developers can map key prefixes to existing table columns, enabling Memcached access to schema-structured data already stored in MySQL Cluster.


Download the Guide to MySQL and NoSQL to learn more about NoSQL APIs and how you can use them to scale new generations of web, cloud, mobile and social applications on the world's most widely deployed open source database

See how to build a social app with MySQL Cluster and the Memcached API from our on-demand webinar or take a look at the docs

Don't hesitate to use the comments section below for any questions you may have 

Tuesday Dec 20, 2011

MySQL 5.6.4 Development Milestone Now Available!

I am pleased to announce that the MySQL Database 5.6.4 development milestone release ("DMR") is now available for download (select the Development Release tab). MySQL 5.6.4 includes all 5.5 production-ready features and provides an aggreation of all of the new features that have been released in earlier 5.6 DMRs.  5.6.4 adds many bug fixes and more new "early and often" enhancements that are development and system QA complete and ready for Community evaluation and feedback.  You can get the complete rundown of all the new 5.6.4 specific features here.

For those following the progression of the 5.6 DMRs as the trains leave the station, you should bookmark these MySQL Engineering development team specific blogs:

You can also track the thought and innovation leaders on the MySQL Optimizer and the new Optimizer specific improvements in 5.6.4 by following the MySQL Optimizer Team member blogs:

And of course you can follow others on the Optimizer team and all of MySQL Engineering teams by bookmarking/subscribing to PlanetMySQL.

We look forward to your feedback on MySQL 5.6.4, so please download your copy now and help us make a better MySQL. 

As always, a sincere thanks for your continued support of MySQL!   

Wednesday Nov 09, 2011

Full-Text Search with InnoDB

Dr. Dobb’s published yesterday an article by Jimmy Yang and John Russel about Full-Text Search with InnoDB:

"MySQL's latest InnoDB engine can now do extensive, high-performance, full text search. A quick primer delivers all the goodies.

Oracle recently provided access to many new MySQL 5.6 features through for the user community to test and comment on. One notable feature is the InnoDB Full-Text Search (FTS) engine. It lets users build FULLTEXT indexes on InnoDB tables to represent text-based content and speed up searches for words and phrases. Combining full-text search with InnoDB tables opens up text capability to transactional applications, where the textual data is frequently inserted, updated, and deleted. Given the importance of this feature to the MySQL audience, this article explains the design of InnoDB FTS and provides recipes for its use, as well as a short list of the limitations of this release."....Continues....

Read the entire article here!

Monday Oct 03, 2011

More Early Access Features in the MySQL 5.6.3 Development Milestone!

For those with an interest in MySQL, this week at Oracle OpenWorld has gotten off to a great start.  Demonstrating how Oracle drives MySQL innovation Tomas' "State of the Dolphin" keynote on Monday gave a great overview of the new MySQL products that have recently been delivered:

Of these announcements I am most excited about the new early access features ready for community evaluation in the MySQL 5.6.3 DMR.  Since 5.5 was released last December we have gotten community and customer feedback that it is the best, highest quality release to date.  5.6 builds on 5.5 by improving:

The MySQL Optimizer for better query performance, scale, diagnostics
Oracle owns the MySQL optimizer and is making a huge investment in engineering and re-factoring to ensure the optimizer evolves to meet the most demanding and complex query requirements.  To this end, the 5.6.3 optimizer now includes:

File sort optimizations for queries with SELECT *, ORDER by non-indexed column, with small LIMIT values 

Currently such queries that ORDER by a non-indexed column perform a full table scan, create sort operations for entire table, potentially create temp tables, toss out the unneeded rows and merge the result set.  With the new default behavior the whole table is scanned, but only the needed rows are sorted and returned in the result set.  Early internal tests show 3x improvement in execution times when using the default sort buffer (of course your mileage may vary.)

Index Condition Pushdown ("ICP")

By default, the optimizer now pushes indexed columns in WHERE clause down to the storage engine for evaluation.  For example, for this table and related query on secondary key of postalcode+age:

      personid INTEGER PRIMARY KEY,
      firstname CHAR(20),
      lastname CHAR(20),
      postalcode INTEGER,
      age INTEGER,
      address CHAR(50),
      KEY k1 (postalcode,age)
   ) ENGINE=InnoDB;

SELECT lastname, firstname FROM person
   WHERE postalcode BETWEEN 5000 AND 5500 AND age BETWEEN 21 AND 25; 

In this case the optimizer will use a range scan on postalcode from 5000-5500 (on index) and push the evaluation of age to InnoDB which will evaluate the value of age "BETWEEN 21 and 25".  For each match, InnoDB will return a row back to the server, which will continue requesting/receiving rows until all rows have been evaluated and returned.  Early non-scientific testing shows:

With 5 million rows in table, random values, running on commodity, desktop-grade hardware
ICP disabled: Disk bound (default buffer pool 128 Mb) the query will take 15 seconds
ICP disabled: All data in memory (buffer pool 1.5 Gb) the query will take 1.4 seconds
ICP enabled: execution time reduced to 90 ms for both

Batched Key Access ("BKA") and Multi-Range Read ("MRR")

Multi-Range Read was introduced in the 5.6.2 DMR. MRR improves the performance of queries that use secondary indexes, by scanning one or more index ranges used in a query, sorting the associated disk blocks for the row data, then reading those disk blocks using larger sequential I/O requests. The speedup benefits operations such as
range index scans and equi-joins on indexed columns.

BKA builds on MRR by improving on the performance of disk-bound join queries.  With BKA the server fills up the join buffer with "join keys".  The server then sends batches of join keys to the storage engine layer (InnoDB or MyISAM, and soon NDB) for processing.  The storage engine then uses MRR to sort indexes according to the data sequence so the data pages can be accessed in sequence for efficiency.

Early testing of DBT3 Q3: Customer Distribution Query on InnoDB, buffer pool = 50 MB, 4GB InnoDB data file, and disk/memory ratio (~2% in memory) has shown execution time drops from 2000 sec to 10 sec with a sufficiently large join buffer.


EXPLAIN for SELECT has been available since MySQL 5.0.  This new feature is a long standing request from our community and customer base.  Best to get straight to an example of how it is used:

CREATE TABLE t1(c1 INT, c2 INT, KEY(c2), KEY(c2, c1));

EXPLAIN UPDATE t1 SET c1 = 10 WHERE c2 = 1;

mysql> EXPLAIN UPDATE t1 SET c1 = 10 WHERE c2 = 1;

| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows |  | Extra    |
|  1 | SIMPLE      | t1    | range | c2,c2_2       | c2   | 5       | NULL |    1 | Using where |

1 row in set (0.00 sec)

In this example the optimizer will use a type=range (scan) on key=c2 to identify and UPDATE the affected row(s).

Optimizer Traces
This feature allows DBAs, developers to trace the decision-making steps of the optimizer.  The TRACE output shows how the EXPLAIN plan was generated and the decision points resolved along the way.  An example would be:



select * from information_schema.OPTIMIZER_TRACE;

With output snippet:

"records_estimation": [
    "database": "test",
    "table": "t6",
    "range_analysis": {
      "table_scan": {
         "records": 2,
         "cost": 4.5034
      "potential_range_indices": [
          "index": "d",
          "usable": true,
          "key_parts": [
      "best_covering_index_scan": {
        "index": "d",
        "cost": 1.4233,
        "chosen": true



Other Optimizer related improvements in 5.6.3 include:

Late materialization of views/subqueries in FROM clause
This improvement enables fast EXPLAINs for views/subqueries and defers/avoids materialization when possible, enabling for quicker bail out if query is abandoned or canceled. Internal tests show a 240x improvement in execution time (drops from 8 min to 2 sec)

Persistent Optimizer Statistics for InnoDB
This allows for more stable, accurate InnoDB statistics and minimizes the overhead of recalculating statistics on start up or crash recovery.  Statistics can be manually updated when needed.

The 5.6.3 DMR also includes new features and improvements to the following:

Performance Schema now includes instrumentation for:

  • Statements/execution stages - What are my most resource intensive queries?
  • Table and Index I/O - Which app tables/indexes cause the most load?
  • Table Locks - Which tables cause the most contention?
  • Users/Hosts/Accounts level resource consumption - Who are the most resource intensive users, hosts, accounts?
  • Network I/O - Is it the network or my app?  How long do sessions idle?
  • Aggregated summaries by thread, user, host, account, object


  • New INFORMATION_SCHEMA tables (metrics, system and buffer pool information) for better diagnostics
  • Dump and restore buffer pool for quicker startup and recovery
  • Reduce contention during file extension for better concurrency


  • Crash-safe slaves and binlog, replication checksums for better data integrity
  • Multi-threaded slaves for better performance
  • time-delayed replication for better testing, data integrity
  • Informational log events for easier troubleshooting

And more...

You can learn more about the 5.6.3 DMR and other early access features by visiting the MySQL Developers Zone.  Here you will find technical articles, the MySQL Database 5.6.3 and MySQL Cluster 7.2 DMR downloads, supporting documentation and all related PlanetMySQL blogs.

If you are attending Oracle Open World this week please plan to attend as many of the 47 MySQL sessions as you can, including my sessions on the "MySQL Roadmap" (Tuesday at 5:00 pm) and "Using MySQL with Other Oracle Products" (Wednesday at 11:45 am PT).  Both are in the Marriott Marquis (just up the block from the Moscone Center), Golden Gate C2.  Also, be sure to join us at the MySQL Community Reception .  There will be many familiar faces and friends there, and it is open to all, even if you are not attending OOW.

As always, thanks for your continued support of MySQL!


Tuesday Aug 30, 2011

2nd Episode of "Meet The MySQL Experts" Podcast Series

We’re pleased to let you know that the 2nd episode of our “Meet The MySQL Experts” podcast series, where Oracle engineers share their expertise, is now available.

Wei-Chen Chiu interviews InnoDB team member Inaam Rana who comes back on the new InnoDB features and improvements delivered in MySQL 5.5 and in the first development milestone release of MySQL 5.6. Inaam also talks to us about the InnoDB features available in


Monday Aug 01, 2011

More New MySQL 5.6 Early Access Features

Last week was a banner week for MySQL at OSCON. We had many MySQL developers meeting with the MySQL community, conducting technical sessions, leading BOF sessions, working the exhibit hall, and confirming Oracle's leadership in the technical evolution of MySQL.  The highlight of the week was the unveiling of even more 5.6 early access InnoDB and Replication features that are now available for early adopters to download, evaluate and shape via  

InnoDB is one of MySQL's "crown jewels" and beginning in 5.5 is now the default storage engine.  The following 5.6 feature improvements are in direct response to community and customer feedback and requests.  The new 5.6 early access features include:

  • Full-text search
  • REDO log files max size extended to 2 TB
  • UNDO logs on their own tablespace
  • Buffer Pool options for pre-loading/warming on re-start
  • Improved auto-extension of .ibd files
  • Support for smaller 4k, 8k page sizes

Replication is by far the most popular and widely used MySQL feature. The following feature improvements in 5.6 are also in direct response to community and customer feedback and requests.  The new 5.6 early access features include:
  • New Binlog API
  • Binlog group commit (completes InnoDB group commit implemented in MySQL 5.5)
  • Durable Slave Reads
  • Enhanced multi-threaded slaves
You can read about the details, including development blogs on how to get started with each in this new DevZone article.  My sincere thanks and appreciation to the InnoDB and Replication development teams for their leadership in technical innovation and mind share and for their dedicated work in providing these and other new features "early and often" to the MySQL community.  Stay tuned for more to come!

We can't say this "early and often" enough...thanks for your continued support of MySQL!

Tuesday Jul 19, 2011

MySQL Enterprise Backup 3.6 - New backup streaming, integration with Oracle Secure Backup and other common backup media solutions

All DBAs understand the importance and priority of quick, reliable database backup and recovery operations.  In fact, dating back to my early days with MySQL, the most commonly requested product features from the MySQL user base have been around online, non-blocking backup solutions for running MySQL servers.  In response, Oracle now provides MySQL Enterprise Backup ("MEB") which performs high performant, online "hot" backups for MySQL databases.  MEB provides all of the backup/recovery features and functionality DBAs expect, all from a scriptable command line interface.  You can learn all about MEB in the related MySQL docs.

My congratulations and appreciation go out to Lars Thalmann and the MySQL Enterprise Backup engineering team for the recent release of MEB 3.6.  While there are many great improvements in this specific release, as an operational DBA I am most excited about the new support for single file streaming and for the SBT interface features, described here:

Single File Streaming - This allows DBAs to offload the footprint of backup images to a different server or storage device without having to store them locally on the MySQL database server.  This removes storage and related overhead from the server being backed up and speeds up total backup time by removing the need to copy local backup images (which even when compressed can be very large) over the network to their ultimate network destination.  You can learn about this specific MEB option along with a good usage example here.

Support for SBT interface - The "Secure Backup to Tape" interface was originally developed by Oracle as a standard way for third-party backup media providers to easily integrate their solutions with Oracle Recovery Manager ("RMAN").  SBT is now supported in MEB 3.6 so MySQL backup images can now be generated by and streamed directly to advanced enterprise backup media management solutions (Oracle Secure Backup, Symantec Netbackup, most others) that are already deployed within an environment.  This simplifies MySQL administration by enabling DBAs to incorporate MySQL backup/recovery operations and media rotation/retention policies into existing standard operating procedures.  You can learn all about this new option, again with a useful example here.

MySQL Enterprise Backup is part of the commercial MySQL Enterprise Edition but like all Oracle products is free to download and use without obligation for 30 days.  This is a great way to try it out to see if it fits your needs.  

You can download and begin working with MEB 3.6 now:

1. Go to Oracle eDelivery.
2. Enter some basic details and click through the agreement.
3. Select "MySQL Product Pack", then your platform, then Go.

I will keep you posted as new MySQL product features and interesting Oracle integrations become available.  As always, thanks for your continued support of MySQL! 

Get the latest updates on products, technology, news, events, webcasts, customers and more.




« July 2016