In earlier blogs I talked about some of the scalability and NoSQL integration features of MariaDB 10. In this third and final part of my tour, I’ll discuss some of the performance, operations, and security enhancements in MariaDB. Performance is clearly all about speed. But speed isn’t just how many transactions per second the database can deliver. It is also about how quickly developers can bring an application to market and how quickly they can diagnose and solve issues, especially performance issues.
Security is also an essential ingredient in MariaDB 10’s advantages. With unique capabilities designed to give DBAs and administrators more visibility into security events and issues, and more flexibility in granting rights to use and administer MariaDB, this new version of the database safeguards data even more completely than earlier versions.
High Performance Storage Engines
Storage engines are pluggable modules that provide the actual storage and retrieval of data by interfacing with the operating system and hardware. As such, they’re central to the performance of the database – they’re the components doing the heavy lifting.
MariaDB 10 features several improved storage engines offering enhanced performance capabilities. First among these is the latest version of InnoDB – the same one in MySQL 5.6.14. In particular, InnoDB has been tuned to take advantage of newer hardware and operating system enhancements. This new storage engine, the transactional workhorse of the MySQL world, is fully integrated and tested with MariaDB 10.
MariaDB 10 also includes a tested and fully integrated version of the TokuDB storage engine from TokuTek. TokuDB has several unique features that can dramatically increase performance of a MariaDB 10 database or replication cluster. In particular, TokuDB implements new indexing and compression algorithms that:
- Delivers up to 20x performance for very high insertion and update rates, without additional application tuning.
- Shrinks hard drive or SSD volume requirements by up to 90% – thats less data to move, and less investment in storage hardware including pricey SSDs.
- Minimizes planned downtime for migrations through online schema changes; allowing for column add/drop/resize, and index add/drop,while your database is running in production.
- Reduces slave lag in large replication hierarchies with faster insert and update performance.
TokuDB is open source, and plug-in compatible with other transactional storage engines including InnoDB, so you can try it out with no changes to your application.
What TokuDB really offers is faster performance at lower cost – just what most DBAs and technical architects are looking for.
One of the most important components of MariaDB in driving performance is the query optimizer. This part of the database system uses heuristics to determine the best way to perform a particular query, given the structure and layout of the database tables and indexes on physical storage hardware. The optimizer figures out how to execute SQL statements with the lowest I/O overhead, improving the performance of the whole database system.
MariaDB 10 incorporates most of the optimizer improvements that were introduced in MySQL 5.6. But beyond those advances, MariaDB 10 includes 14 optimizer enhancements unique to MariaDB and not found in MySQL. Some of the most important new optimizer features added to MariaDB 10 include:
- Engine-independent table statistics such as table size and structure, allowing the optimizer to better select the best query plan.
- Subquery optimizations which transform complex queries into simpler forms that are more easily optimized.
- Histogram statistics for non-indexed columns which let the optimizer speed up processing of queries by capturing the distribution of values on columns without indexes and using those statistics to improve query plans.
Taken together, these improvements to the optimizer in MariaDB 10 allow the query processing logic to significantly speed up throughput of operations.
Other Performance Improvements
MariaDB 10 doesn’t stop with more advanced storage engines and optimizer enhancements. Here are just a few of the performance improvements you’ll find in this new version of MariaDB:
- The database includes specialized improvements for Fusion-io SSD devices, leveraging a feature of the DirectFS filesystem on these popular, high performance solid state disks. Using this feature, MariaDB 10 can eliminate some of the overhead within the InnoDB storage engine when used with Fusion-io devices.
- The Performance Schema is a powerful diagnostic tool that lets DBAs, or IT automation scripts tap into the broad range of statistics gathered by the database, in order to directly monitor performance in real time.
- Thread pool management has been improved to allow an individual instance ot MariaDB 10 to scale more efficiently in handling a large number of concurrent users.
The development team building MariaDB includes some of the most knowledgeable database gurus in the open source world. MariaDB 10 has benefitted from their expertise, and now you can too, because these performance gains translate directly into lower cost and faster time to market for applications built on top of MariaDB 10.
The need for speed encompasses more than just raw performance. MariaDB 10 includes a number of new features to hellp DBAs and developers be more efficient. With these new capabilities, developers can build and deploy new versions of applications faster, and administrators can track down problems and craft solutions more quickly. Some of these new operational benefits focus on performance tuning. Now, with MariaDB 10, DBAs can diagnose performance issues by viewing the query plans of long-running queries while they’re running, using the new SHOW EXPLAIN command. For example, a DBA monitoring an application might notice an unexpected long-running query locking a table and blocking user requests. Right then, she could ask MariaDB 10 to display that running statement’s query plan with SHOW EXPLAIN, and uncover a sequential table scan of a multi-terabyte, millions-of-rows table. Result – an email waiting for the development team the next day: Subject: “Shortest-Route column needs an index!” Along the same lines, developers and DevOps engineers can gain a view of memory usage for individual threads and connections, highlighting potential bottlenecks and problems and giving them a new tool for tuning and diagnosis. These new capabilities, coupled with the Performance Schema tool mentioned earlier make it easier to tune and improve application performance using MariaDB 10. MariaDB 10 includes a number of other operational improvements including:
- A new SHUTDOWN command.
- Improved human-readable error messages in log files.
- Problematic, long-running queries terminated on a per-thread basis.
- A snapshot of installed and available database plugins and options with the SHOW PLUGINS SONAME command.
- Improved table discovery in new storage engines, eliminating extra work for DBAs.
And one key feature brought over from MySQL 5.6 promises to reduce the need for scheduled downtime and maintenance windows: online ALTER TABLE for schema changes. This critical DevOps feature lets developers iterate new features that demand schema changes, without worrying about costly and inconvenient database migrations – these database changes can occur on your production databases without downtime.
Security and Compliance Features
MariaDB 10 includes three important new features to help secure databases and the valuable data they contain in a more complex DevOps development environment:
Pluggable Authentication Modules (PAM) Plug-in
When users first try to access a database they must be authenticated. MySQL and MariaDB include their own authentication mechanism but it can be inconvenient and potentially less secure to have a separate name-password mechanism for the database layer of your application infrastructure. What if you could use a single sign-on for your whole development infrastructure that once you’ve logged in, lets you access the database using the same credentials as for other applications in your development environment? Well, with MariaDB 10’s Pluggable Authentication Module (PAM) plug-in, you can. PAM is a popular and widely adopted authentication framework used on many UNIX-based operating systems including FreeBSD, Linux, and Solaris, to name a few. With this plug-in, MariaDB delegates responsibility for authenticating users to the operating system, simplifying things for users and making the infrastructure potentially more secure, by eliminating one more password for people to remember.
Role-based Access Control
Once a user has been identified to the system, how does MariaDB know what permissions he has to access data and perform administrative tasks? MariaDB 10 includes a new capability called Role Based Access Control which greatly simplifies assigning permissions to different users. With RBAC, administrators create roles that have a set of associated permissions, then grant users these roles. For example, the IT operations group might create a role for DevOps Engineers that allows someone with this role to use the Performance Schema and other performance diagnostic tools, to perform schema changes and migrations. The DBA role would get a different set of permissions, as would the application developer role, and so on. With RBAC, if application developers need a new permission, the administrators can add that permission to the role, and immediately all the developers who’ve been granted that role would have the new permission. Far simpler than assigning permissions to individual user accounts. One more very cool thing about MariaDB 10’s RBAC feature: it was developed as a Google Summer of Code project by Vicentiu Ciorbaru a student in Bucharest, Romania and a contributor to the MariaDB Foundation!
Even though MariaDB 10 has simplified authentication and access control, there is still a need to know just who is doing what in your database, in real time. The MariaDB Audit Plug-in lets you log user access to data at a fine-grain level. This tool helps administrators identify, diagnose, and correct potential and actual security breaches, and comply with auditability requirements which may be imposed by regulation or risk management. This tool was originally developed by SkySQL and has been contributed to the MariaDB project – it is included in MariaDB 10.
MariaDB is Open Source Software
There is one feature you won’t find implemented in the MariaDB 10 binaries, but which makes all the rest possible: MariaDB is Open Source Software – developed by a community of engaged developers from a number of companies and acting as individuals. Of the many new features found in MariaDB 10, quite a few have been independently developed by enthusiastic co-developers and contributors like Olivier Betrand (CONNECT) and Vicentiu Ciorbaru (RBAC). The Parallel Slave Replication feature was sponsored by Google. We have these and many other individuals and companies, as well as the core team members including Monty Widenius to thank for the innovative leap forward that MariaDB 10 represents. I hope you’ve enjoyed this tour of MariaDB 10. I’m looking forward to your questions and comments!