OtterTune is a tool powered by self-driving DBMS technology out of R&D at Carnegie Mellon University that aims to alleviate the fundamental difficulties of tuning a DBMS.

And we all know how difficult that is. It's because there's a myriad of options or "knobs" (such as Buffer sizes, caching policies, optimizer tuning) with one knob influencing the other, that must be taken into consideration when making a performance enhancement plan. The hope is that, possibly over numerous iterations, the accumulated tweaking will lead to better performance.

Of course, the more sophisticated the DBMSs become, the more sophisticated your estimations should also become. That's why Oracle, for instance, is making moves towards Autonomous products

Oracle Autonomous Database is a cloud-based technology designed to automate many of the routine tasks required to manage Oracle databases, which Oracle says can free up database administrators (DBAs) to do higher-level and more strategic work. 

The task becomes even more difficult when you have a number of DBMS servers operating together, which most enterprises do. Even if you decide on an effective performance enhancement plan, you would have to apply it to each one of them individually. That's a lot of pain, not to mention how error-prone too.

So OtterTune intends to make all this manual engagement and the associated worry, a thing of the past. Per its definition:

OtterTune is a database management system configuration monitoring and tuning service.It uses machine learning to automatically optimize the knob configuration of DBMSs to improve their performance and reduce hardware/software costs.

The magic words here are "Machine Learning", ML. How does OtterTune utilize it?

An agent called the Controller is installed side-by-side with the DBMS system in question. The Controller monitors it and while transactions are taking place as they normally do, the Collector ... collects various metrics/performance counters, settings and current knob profiles.

That data is then sent to the Tuning Manager which uses it to train statistical models that predict how the performance of the target database is affected by the changes in the configuration. It then generates the optimal values which and applies to the database through the Controller.

Of course, as with everything to do with ML, the more data you feed it the more accurate the model becomes so, over time and at each iteration, the configuration values become more effective.

It goes without saying how cost effective this solution is since database administrators don't have to rely on time-consuming trial and error tuning or boilerplate configuration settings that can leave databases well short of optimal performance. It is currently estimated that 40% of cloud databases are over-provisioned, resulting in hundreds of millions of dollars in wasted IT infrastructure spending industry-wide per year.

Practically this is translated to cutting costs of software licensing, hardware buying for scaling, expensive personnel training in tuning, and of course the long term cost of vendor lock in.

With that said, the OtterTune service works for both on-premises and cloud-based database deployments and supports PostgreSQL, MySQL, and Amazon RDS. There's going to be support for Oracle as well. I reached out regarding that and with a couple of other questions, to Andy Pavlo, Associate Professor of Databases at Carnegie Mellon and Co-Founder of OtterTune:

The initial release of the commercial version of OtterTune will support Postgres/MySQL Amazon RDS. We published a paper in VLDB in March about running the academic version of OtterTune on Oracle:

https://db.cs.cmu.edu/papers/2021/p1241-aken.pdf

We haven't ported over the support for Oracle to the new SaaS version of OtterTune yet, but we plan to do it this summer once a customer requests it. We don't have to change the ML algorithms. It's just a matter of extending the driver interface to support Oracle.

And, what is the difference to Oracle's autonomous database and why choose OtterTune over it ?

Oracle's autonomous database offering covers a lot more aspects of database maintenance than what OtterTune currently supports (e.g., indexing, security, backups). All of their automated tooling, however, only supports the Oracle DBMS. OtterTune's core algorithms are agnostic to the DBMS and do not require any changes to the DBMS implementation itself. We support Postgres and MySQL, and think that there is still a sizable need for automated tools for these DBs. Oracle is unlikely to ever support these systems with their autonomous DB technology.

Does this system make the DBA obsolete?

Not at all! DBAs are stretched thin as it is. Configuration tuning is an important task but they just don't have time to dedicate to it. We think of OtterTune as helping the DBAs by taking over configuration management for them. There are many other tasks that a DBA has to handle that requires human value judgement that cannot be easily replaced by machine learning. Thus, OtterTune allows DBAs to spend more time pursuing those other more fulfilling tasks at their job.

Is the system tuned in real time or is there downtime until the new configuration values are updated?

There are some configuration knobs that take effect immediately without having to restart the DBMS (MySQL calls these "dynamic knobs" https://dev.mysql.com/doc/refman/8.0/en/dynamic-system-variables.html). OtterTune can manage these knobs without having to restart the DBMS and incur downtime. There are other knobs, however, that do not take effect until you restart the DBMS (we call these "static knobs").

Unfortunately every DBMS has static knobs. The good news is that OtterTune can still achieve significant improvements without having to tune them. For example, we see a 2x improvement in performance (or a 50% reduction in cost) when only tuning dynamic knobs for the TPC-C benchmark on Postgres running on Amazon RDS.

When you setup OtterTune, you tell it whether you want to restart the DBMS or not. Based on this selection, OtterTune knows whether it is allowed to tune the knobs that require a restart or whether it should only tune the dynamic knobs.

Sounds promising. The tool was officially launched on May 12th.

More Information

Official OtterTune site

OtterTune on Carnegie Mellon Database Group 

Related Articles

PostgreSQL Is DB-Engines DBMS of the Year For 2020

Java Language Extensions for SQL Server Open Sourced

Percona's DBMS Popularity Survey 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info