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 

Wednesday Dec 12, 2012

Thank You MySQL Community! MySQL 5.6.9 Release Candidate Available Now!

The MySQL Community continues its good work in testing and refining MySQL 5.6, and as such the next iteration of the 5.6 Release Candidate is now available for download.  You can get MySQL 5.6.9 here (look under the "Development Releases" tab).  This version is the result of feedback we have gotten since MySQL 5.6.7 was announced at MySQL Connect in late September. As iron sharpens iron, Community feedback sharpens the quality and performance of MySQL so please download 5.6.9 and let us know how we can improve it as we move toward the production-ready product release in early 2013.

MySQL 5.6 is designed to meet the agility demands of the next generation of web apps and services and includes across the board improvements to the Optimizer, InnoDB performance/scale and online DDL operations, self-healing Replication, Performance Schema Instrumentation, Security and developer enabling NoSQL functionality

You can learn all the details and follow MySQL Engineering blogs on all of the key features in this MySQL DevZone article.

On a related note, plan to join this week's live webinars to learn more about MySQL 5.6 Self-Healing Replication Clusters and Building the Next Generation of Web, Cloud, SaaS, Embedded Application and Services with MySQL 5.6.  Hurry!  Seating is limited!

 As always, thanks for your continued support of MySQL!

Monday Dec 10, 2012

MySQL, An Ideal Choice for The Cloud

As the world's most popular web database, MySQL has quickly become the leading database for the cloud, with most providers offering MySQL-based services.

Access our Resource Kit to discover:

  • Why MySQL has become the leading database in the cloud, and how it addresses the critical attributes of cloud-based deployments
  • How ISVs rely on MySQL to power their SaaS offerings
  • Best practices to deploy the world’s most popular open source database in public and private clouds

You will also find out how you can leverage MySQL together with Hadoop and other technologies to unlock the value of Big Data, either on-premise or in the cloud.

Access white papers, webinars, case studies and other resources in our Resource Kit now!

Wednesday Dec 05, 2012

Top 5 Developer Enabling Nuggets in MySQL 5.6

MySQL 5.6 is truly a better MySQL and reflects Oracle's commitment to the evolution of the most popular and widely
used open source database on the planet.  The feature-complete 5.6 release candidate was announced at MySQL Connect in late September and the production-ready, generally available ("GA") product should be available in early 2013.  

While the message around 5.6 has been focused mainly on mass appeal, advanced topics like performance/scale, high availability, and self-healing replication clusters, MySQL 5.6 also provides many developer-friendly nuggets that
are designed to enable those who are building the next generation of web-based and embedded applications and services. Boiling down the 5.6 feature set into a smaller set, of simple, easy to use goodies designed with developer agility in mind, these things deserve a quick look:

Subquery Optimizations

Using semi-JOINs and late materialization, the MySQL 5.6 Optimizer delivers greatly improved subquery performance. Specifically, the optimizer is now more efficient in handling subqueries in the FROM clause; materialization of subqueries in the FROM clause is now postponed until their contents are needed during execution. Additionally, the optimizer may add an index to derived tables during execution to speed up row retrieval. Internal tests run using the DBT-3 benchmark Query #13, shown below, demonstrate an order of magnitude improvement in execution times (from days to seconds) over previous versions.

select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
from customer, orders, lineitem
where o_orderkey in (
                select l_orderkey
                from lineitem
                group by l_orderkey
                having sum(l_quantity) > 313
  and c_custkey = o_custkey
  and o_orderkey = l_orderkey
group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
order by o_totalprice desc, o_orderdate
LIMIT 100;

What does this mean for developers?  For starters, simplified subqueries can now be coded instead of complex joins for cross table lookups:

SELECT title FROM film WHERE film_id IN

(SELECT film_id FROM film_actor

GROUP BY film_id HAVING count(*) > 12);

And even more importantly subqueries embedded in packaged applications no longer need to be re-written into joins.  This is good news for both ISVs and their customers who have access to the underlying queries and who have spent development cycles writing, testing and maintaining their own versions of re-written queries across updated versions of a packaged app.

The details are in the MySQL 5.6 docs.

Online DDL Operations

Today's web-based applications are designed to rapidly evolve and adapt to meet business and revenue-generation
requirements. As a result, development SLAs are now most often measured in minutes vs days or weeks. For example, when an application must quickly support new product lines or new products within existing product lines, the backend database schema must adapt in kind, and most commonly while the application remains available for normal business operations.  MySQL 5.6 supports this level of online schema flexibility and agility by providing the following new ALTER TABLE online DDL syntax additions: 

  • Change AUTO_INCREMENT value for a column
  • Rename COLUMN
  • Change ROW FORMAT, KEY_BLOCK_SIZE for a table
  • Add, drop, reorder COLUMN

Again, the details are in the MySQL 5.6 docs.

Key-value access to InnoDB via Memcached API

Many of the next generation of web, cloud, social and mobile applications require fast operations against simple
Key/Value pairs. At the same time, they must retain the ability to run complex queries against the same data, as
well as ensure the data is protected with ACID guarantees. With the new NoSQL API for InnoDB, developers have all
the benefits of a transactional RDBMS, coupled with the performance capabilities of Key/Value store.

MySQL 5.6 provides simple, key-value interaction with InnoDB data via the familiar Memcached API.  Implemented via a new Memcached daemon plug-in to mysqld, the new Memcached protocol is mapped directly to the native InnoDB API and enables developers to use existing Memcached clients to bypass the expense of query parsing and go directly to InnoDB data for lookups and transactional compliant updates.  The API makes it possible to re-use standard Memcached libraries and clients, while extending Memcached functionality by integrating a persistent, crash-safe, transactional database back-end.  The implementation is shown here:

So does this option provide a performance benefit over SQL?  Internal performance benchmarks using a customized
Java application and test harness show some very promising results with a 9X improvement in overall throughput for SET/INSERT operations:

You can follow the InnoDB team blog for the methodology, implementation and internal test cases that generated these results here.

How to get started with Memcached API to InnoDB is here.

New Instrumentation in Performance Schema

The MySQL Performance Schema was introduced in MySQL 5.5 and is designed to provide point in time metrics for key performance indicators.  MySQL 5.6 improves the Performance Schema in answer to the most common DBA and Developer problems.  New instrumentations include:

  • Statements/Stages
    • What are my most resource intensive queries? Where do they spend time?
  • Table/Index I/O, Table Locks
    • Which application tables/indexes cause the most load or contention?
  • Users/Hosts/Accounts
    • Which application users, hosts, accounts are consuming the most resources?
  • Network I/O
    • What is the network load like? How long do sessions idle?
  • Summaries
    • Aggregated statistics grouped by statement, thread, user, host, account or object.

The MySQL 5.6 Performance Schema is now enabled by default in the my.cnf file with optimized and auto-tune settings that minimize overhead (< 5%, but mileage will vary), so using the Performance Schema on
a production server to monitor the most common application use cases is less of an issue.  In addition, new atomic levels of instrumentation enable the capture of granular levels of resource consumption by users, hosts, accounts, applications, etc. for billing and chargeback purposes in cloud computing environments.

The MySQL docs are an excellent resource for all that is available and that can be done with the 5.6 Performance Schema.

Better Condition Handling - GET DIAGNOSTICS

MySQL 5.6 enables developers to easily check for error conditions and code for exceptions by introducing the new
MySQL Diagnostics Area and corresponding GET DIAGNOSTICS interface command. The Diagnostic Area can be populated via multiple options and provides 2 kinds of information:

Statement - which provides affected row count and number of conditions that occurred
Condition - which provides error codes and messages for all conditions that were returned by a previous operation

The addressable items for each are:

The new GET DIAGNOSTICS command provides a standard interface into the Diagnostics Area and can be used via the CLI or from within application code to easily retrieve and handle the results of the most recent statement execution.  An example of how it is used might be:

mysql> DROP TABLE test.no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table'
mysql> SELECT @p1, @p2;
| @p1   | @p2                                |
| 42S02 | Unknown table 'test.no_such_table' |

Options for leveraging the MySQL Diagnotics Area and GET DIAGNOSTICS are detailed in the MySQL Docs.

While the above is a summary of some of the key developer enabling 5.6 features, it is by no means exhaustive. You can dig deeper into what MySQL 5.6 has to offer by reading this developer zone article or checking out "What's New in MySQL 5.6" in the MySQL docs.

BONUS ALERT!  If you are developing on Windows or are considering MySQL as an alternative to SQL Server for your next project, application or shipping product, you should check out the MySQL Installer for Windows.  The installer includes the MySQL 5.6 RC database, all drivers, Visual Studio and Excel plugins, tray monitor and development tools all a single download and GUI installer.  

So what are your next steps?

  • Register for Dec. 12 "MySQL Replication: Simplifying Scaling and HA with GTIDs" live web event to learn about the new Replication features in MySQL 5.6.
  • Register for Dec. 13 "MySQL 5.6: Building the Next Generation of Web-Based Applications and Services" live web event.  Hurry!  Seats are limited.
  • Download the MySQL 5.6 Release Candidate (look under the Development Releases tab)
  • Provide Feedback <link to>
  • Join the Developer discussion on the MySQL Forums
  • Explore all MySQL Products and Developer Tools

As always, thanks for your continued support of MySQL!


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




« December 2012 »