Thursday Dec 20, 2012
Thursday Dec 13, 2012
By Mat Keep on Dec 13, 2012
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
By Rob Young on Dec 20, 2011
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:
- InnoDB Development Team Blog
- Marc Alff's Blog on Performance Schema
- Andew Morgan's MySQL HA Blog
- Mats Kindahl's Blog on MySQL Replication
- Lars Thalmann's Blog on MySQL Replication, Backup, Connectors
- Chuck Bell's Blog on MySQL GPL Utilities
Specific enhancements in the MySQL 5.6.4 include:InnoDB Full-Text Search
Better scaling of read-only workloads
InnoDB 5.6.4 supports databases with 4k and 8k page sizes
Other engineering team blogs include:
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
By Bertrand Matthelié on Nov 09, 2011
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 http://labs.mysql.com
for the user community to test and comment on. One notable feature is the
InnoDB Full-Text Search (FTS) engine. It lets users build
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
Monday Oct 03, 2011
By Rob Young on Oct 03, 2011
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:
- MySQL Installer for Windows
- New MySQL Security and Scalability features
- New MySQL High Availability features
- New early access features in the MySQL 5.6.3 Development Milestone Release ("DMR")
- New early access features in the MySQL Cluster 7.2 DMR
- New MySQL Enterprise Oracle certifications
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:
CREATE TABLE person (
personid INTEGER PRIMARY KEY,
KEY k1 (postalcode,age)
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 INSERT/UPDATE/DELETE
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).
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 (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ;
select * from information_schema.OPTIMIZER_TRACE;
With output snippet:
Optimizer tracing works for SELECT, EXPLAIN SELECT, INSERT or REPLACE ( with VALUES or SELECT), UPDATE/DELETE, SET, DO, DECLARE/CASE/IF/RETURN.
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
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
By Bertrand Matthelié on Aug 30, 2011
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 labs.mysql.com.
Monday Aug 01, 2011
By Rob Young on Aug 01, 2011
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 labs.mysql.com.
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
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
By Rob Young on Jul 19, 2011
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!
Tuesday Nov 30, 2010
By Rob Young on Nov 30, 2010
Get the latest updates on products, technology, news, events, webcasts, customers and more.
- DBAs, Learn about MySQL Where You Want, When You Want
- Update on the MySQL Connect 2014 Call for Papers
- Demonstrate Your Expertise with MySQL 5.6 Certifications
- Upcoming EMEA Events with MySQL 2014
- SIG - MySQL meeting in Berlin, Germany!
- Beginning on the MySQL Command Line
- Zyme Relies on MySQL Enterprise Edition to Deliver High Quality Global Channel Insights to Customers
- MySQL Connectors, a Real Plus for Developers
- Hot Off The Press: MySQL February Newsletter
- MySQL Cluster is a Resilient and Scalable Database Platform