eWEEK content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Deep Information Sciences has introduced the Deep Engine storage engine, which is designed to maximize the performance of MySQL and extend that open source database to large-scale data operations, such as real time analytics and big data management.
While some analysts claim that MySQL is at a crossroads and is becoming long in the tooth, the simple fact of the matter is that an estimated 80.5 percent of enterprises are using the open source database somewhere in their operations, according to a recent 451 Research Group survey.
Nevertheless, many enterprises are looking to replace MySQL with other database technologies that are better attuned to working with massive data sets and time- sensitive processing. This means the future of MySQL in the enterprise is murky at best.
The Deep Engine replaces MySQL’s native storage engines, such as InnoDB or MyISAM, and brings machine-learning metrics to MySQL. This boosts performance and enables enterprises to better leverage investments in existing MySQL implementations, without costly migrations to new hardware or adopting other database technologies, the company said.
The Deep Engine’s self-tuning algorithms can quickly adapt to new hardware deployments and fully utilize improvements in hardware as systems are upgraded or replaced. With that in mind, it becomes evident that there is much more to the Deep Engine than just being an alternative storage engine.
Deep likes to refer to it as an adaptive database kernel and information orchestration system that seamlessly evolves the foundation of the database. Perhaps, that is a more fancy way of saying that the Deep Engine can perform functions above and beyond what a storage engine typically does.
A Closer Look at the Deep Engine
The Deep Engine strives to bring adaptability, acceleration and scalability to MySQL environments, while also streamlining operations. While that may sound like marketing speak, each of those elements brings measurable value to existing MySQL implementations and even allows MySQL to be used for businesses cases where it was once thought to be inappropriate.
The issue of adaptability has far-reaching implications, especially where MySQL is concerned. Current storage engines, such as InnoDB, rely on static algorithms to move data around, create indexes and so forth.
Deep Engine changes the equation by using adaptive heuristics to determine the best path to deal with that data. For example, systems that offer large amounts of RAM and multiple processors may still be constrained by disk I/O.
Deep deals with that disk performance limitation by shifting processing and I/O burdens over to memory and by executing multiple threads, which in effect brings concurrent, parallel processing to a MySQL process.
That adaptability also applies to situations where hardware changes are encountered or workloads are shifted to different environments, where the Deep Engine can quickly assimilate the new processing paradigm and optimize how it moves data across resources.
From the acceleration standpoint, the Deep Engine introduces technologies such as advanced, high-speed compression, parallel processing, self- tuning algorithms and predictive indexing. Simply put, acceleration goes hand in hand with adaptability to offer performance levels that can reach 100 times greater than InnoDB’s performance in certain circumstances.
Where Deep differs from InnoDB and other storage engines is how it handles elements such as compression. The Deep Engine implements testing algorithms to measure the benefit offered by compression, which determine if space is actually saved and performance impacted.
The Deep Engine offers a compression improvement of as much as 80 percent. What’s more, the adaptable algorithms use heuristics to determine available processing and I/O bandwidth. Deep Engine can also initiate additional threads or optimize memory utilization to further improve performance.
As far as scale is concerned, the performance and optimization improvements the Deep Engine brings to MySQL allow the database to scale to theoretically, an unlimited number of records, without encountering the performance and usability limitations found when relying on InnoDB. Deep has tested MySQL with databases containing a trillion records.
Ultimately, scale is about a lot more than the number of records available; scale is also about speed, which is directly related to I/O. Here, Deep incorporates concepts such as over-indexing, which can have a dramatic impact on SQL query performance.
However, indexes increase storage requirements and add overhead to MySQL optimization processes.
Deep Engine Supercharges MySQL for Big Data, High Performance
Deep deals with those issues by incorporating adaptive compression, as well as using multiple threads to speed queries and further enhance performance, resulting in MySQL’s ability to scale up for use with massive datasets.
Hands- on with the Deep Engine
Working hands- on may very well be a misnomer when it comes to the Deep Engine. Apart from installation there is very little, if any direct interaction that a DBA/Network Administrator has with the Deep Engine. Simply put, the Deep Engine works behind the scenes and outwardly appears as little more than a plugin for MySQL.
However, outward appearances can be deceiving. The Deep Engine churns away behind the scenes, bringing easily measurable performance and scalability improvements to MySQL databases. That means quantifying the value of the product becomes a matter of measurement and comparison.
To that end, I tested the Deep Engine’s capabilities on two different servers that were configured with multi-million row MySQL Databases. For testing I worked with MySQL 5.5.40 with built in InnoDB plus the Deep Engine plugin.
Testing Specifications
Database performance testing was accomplished using the guidelines set forth in two public domain testing documents, which can be found at:
http://en.wikipedia.org/wiki/Transaction_Processing_Performance_Council
http://www.tpc.org/information/benchmarks.asp
Two systems were used to conduct the testing, a hosted system and a local system, which were configured as follows:
- A Softlayer hosted bare metal server configured as follows:
-
- Ubuntu Sever 12.04
- 12G RAM
- 24 CPU (3.06 GHz)
- 100GB SSD (Enterprise grade – Micron RealSSD P300)
- A local server
-
- CentOS 6.4
- 256 RAM
- 48 CPU (3.0 GHz)
- 6TB HDD Raid0 (Seagate 5900 RPM)
Four distinct types of benchmark testing scenarios were used, as outlined below:
sysbench (OLTP): a classic initial test all customers run to measure max transactions per second.
dbt2 (OLTP/TPC-C): a classic transaction test example using an online shopping cart that runs customer/order/items/warehouse schema.
dbt3 (OLAP/TPC-H): a classic analytic test applying customer/order/items/warehouse schema.
Iibench (HTAP “like”): a test that involves heavy ingestion of new data with many indexes and queries.
Testing Results
The first test was designed to measure the ability of the DEEP Engine to adapt to workloads and optimize transaction processing. I ran two test iterations using the Sysbench Benchmark. Both iterations were configured with default settings and consisted of 24 clients accessing 5 million rows from 100,000 requests using a uniform distribution type.
The Deep Engine showed a 23- fold improvement over the InnoDB storage engine with Deep handling 8826.84 transactions per second, while the InnoDB storage engine processed 379.69 transactions per second. The test proved that Deep’s adaptive configuration automatically scaled performance up to maximize transactions per second. While InnoDB would require “tuning” to exceed its out of the box performance capabilities.
The second test focused on creating a best- case hardware and configuration scenario for InnoDB, while leaving the Deep Engine to function using its own defaults and with Deep’s adaptive capabilities suspended. The test was used to demonstrate if the Deep Engine offered any performance advantages when relying on algorithms that leverage cache.
Both iterations were configured with default settings and consisted of 24 clients accessing 5 million rows from 100,000 requests using a uniform distribution type. In that scenario, deep was able to roughly double the transaction throughput when compared to InnoDB. Deep mustered 3123.23 transactions per second, while InnoDB processed 1534.45 transactions per second.
The third test was designed to judge data ingestion, a common procedure used when creating very large databases for analytics purposes, such as big data projects. The test used 25 clients to add 50 million rows of information to a database through typical queries. Deep produced an 18 fold improvement over InnoDB, taking 200 seconds to complete the ingestion process, while InnoDB took 3750 seconds.
The Bottom Line
The Deep Engine offers easily measurable performance improvements for those using MySQL as their primary database. Installing the Deep Engine Plugin is very easy and takes no additional configuration, changes to code or other programing or administrative overhead, which makes it the perfect choice for those looking to scale MySQL solutions to even the largest use cases. Using Deep Engine can potentially delay or prevent the need to transition to much more expensive database systems to tackle big data or other data intensive projects.