Database Performance for the Masses: A Glance on Auto-Tuning Tools for Database Management Systems
Database Management Systems (DBMS) are a central part of every modern application, and their proper configuration is crucial to guarantee quality of service on application level, but also cost-efficient operation of IT services.
Yet, finding a configuration of a DBMS that suits the workload and specific use case is difficult, time-consuming, and error-prone when done manually. The rise of AI-based tool support offers an opportunity to let algorithms do the heavy lifting.
In recent years some companies have been founded with the goal to help their users with database performance tweaking. In this article, we introduce three of them who have focused on traditional open source, relational DBMS such as PostgreSQL and MySQL. We glance over their history, solution, and marketing claims.
Table of Contents
Key Insights – Auto-tuning
- We had a look at three start-ups proving AI-based tools to help their customers optimize their database configurations
- All companies offer cloud-based services that analyses monitoring data and usage statistics and spill out either recommendations or enacts changes in the production system.
- All technical approaches require their users to install a software agent close to their database instance.
- All companies promise significant gains in performance and reduction of operational cost. Yet, none of them presents objective case studies.
- Despite these companies seeking to disrupt the market, AI-based DBMS tuning is still also an ongoing research topic promising more innovative start-ups come to light in the next years.
Introduction
Database Management Systems (DBMS) are a crucial part of almost all modern applications as they persistently and reliably store application data and beyond this, provide a well-defined query and update interface to read and manipulate the data consistently. Even so when different users and user groups interact with the DBMS.
Traditionally, Relational Database Management Systems (RDBMS) are the work horses of many business applications. These have classically been distributed as closed source, proprietary applications such as IBM DB2, Oracle Database, and MS SQL Server. Yet, since the mid-90s, open-source DBMS such as PostgreSQL and MySQL have gained traction and are equally enterprise-ready as their commercial counterparts.
Having said that, all these DBMS are usually built as general-purpose solutions with many different usage scenarios in mind. Consequently, it is necessary to tweak the configuration of a DBMS such that it matches the usage scenario. Because of this, many DBMS provide a significant number of configuration options to adapt their behaviour.
Due to the large number of different configuration options, their interdependencies and the large number of different workloads, the tuning of DBMS is often considered to be some sort of dark art that requires many years of experience and dedicated training for a single DBMS technology. With an increasing number of DBMS available on the market, the concept of polymorphic persistence, and the increasing dynamic of workloads, the use of human resources for monitoring, tuning, and observing the runtime behaviour of DBMS has become too risky, too slow, and too expensive. With the upcoming pervasion of AI all over IT, the development of tools for AI-based DBMS auto-tuning has gained attention, first in academic research and later by start-ups and their investors.
As of now, several of these tools have reached a maturity level where their use in production seems feasible and we will use this blog post to discuss some of them. The focus here lies on classic open sources DBMS, more precisely, PostgreSQL and MySQL.
pgTune and MySQLTuner
Even before the recent AI wave, people recognized that DBMS tuning is difficult and could benefit from tool support. Accordingly, tools exist for both PostgreSQL and MySQL that aim at supporting operations teams with tweaking their DBMS. In addition to human experts, these tools are the absolute baseline any AI-based tuning tool has to compare against.
Pgtune is a more than a decade old tool developed by Gregory Smith. Its original purpose was to align the (default) PostgreSQL configuration file to the hardware PostgreSQL is running on. The code base from the current Github repo also takes the operating system, the number of connections, and the category of workload into account (Web, Data Warehouse, OLTP, Web, Mixed, Desktop).
The original version of pgtune is not very actively maintained and does not support newer versions of PostgreSQL. Yet, there are forks / re-implementations available that also turn the software in some Web-based questionnaire as for instance provided by this website that supports the latest PostgreSQL versions.
pgTune is ready to be used in production as for instance serveralnines.com do, who also discuss the tool in a more detailed blog post.
On the MySQL / MariaDB front, MySQLTuner is a widely adopted and actively maintained open-source product aiming at supporting users of MySQL, MariaDB, and other MySQL derivates or extensions such as Percona Server and Galera for MySQL. In contrast to pgTune , MySQLTuner evaluates the status of a live database system and does not only considers static environment parameters. Just like pgTune, MySQLTuner merely provides suggestions and does not implement any changes to the DBMS configuration.
OtterTune
The core team of OtterTune was or is involved as researchers in Carnegie Mellon University’s database group. This group is well-known in the database research community and has a long paper track in the domain of self-driving DBMS with numerous publications at leading database and performance research conferences such as SIGMOD and VLDB.
The group ran a research project called OtterTune the results of which inspired the product of the company. OtterTune was founded in 2021 in the USA and has raised more than $14.5 million capital from various investors. Their plans and pricing differentiate between a standard plan with fixed pricing per database and an individual enterprise plan. In the standard plan, they offer cloud-based auto-tuning for MySQL and PostgreSQL, but only when run on Amazon RDS and Amazon Aurora. The enterprise plan supports generic MySQL and PostgreSQL instances. Besides, OtterTune features their own label on bandcamp and a CEO willing to dress up in an Otter costume.
For working properly, Ottertune requires their users to install a so-called agent that has rights to access and modify the DBMS instance to be tuned. Depending on the type of desired tuning OtterTune / the agent will access different types of information such as configuration options, runtime metrics, query statistics, as well as table, schema and index names.
On their website, OtterTune promise a 400% performance improvement and a 50% cost reduction when using their product.
DBtune
While DBtune's CEO is not known for showing up in animal costumes, the origin of the product is equally academic as OtterTune is. DBtune’s baseline technology has primarily been driven by AI researchers and has been developed at Stanford University, CA, USA and Lund University, Sweden. The company is based in Malmö, Sweden and was funded in 2022. They received 2.4 M€ venture funding in early 2023.
DBtune addresses multiple DBMS technologies including PostgreSQL, MySQL, RocksDB, and FoundationDB. Their website further claims ease of use and affordability. In their promo material DBtune promise a performance increase of 318% for PostgreSQL and 195% for MySQL respectively. The improvements claimed for RocksDB and FoundationDB are less, but still impressive.
Apart from that, very little information is available on the DBtune website about how to use the tooling and how to apply it to existing database instances. Their Github repository contains a hands on tutorial for tuning Postgres 14 for a synthetic workload. The tutorial also contains a link to the DBtune user guide. Based on this user guide, one can conclude that DBtune is used like OtterTune. A DBtune controller is installed at the site of the target DBMS instance. This controller supervises the DBMS instance, measures performance, and applies new configurations to the PostgreSQL instance. Further, it communicates with the DBtune service offered in a cloud manner.
Releem
Similar to DBtune and Ottertune, Releem is an AI-based tuning service. In this case, specialized for MySQL and its derivates such as MariaDB or Percona Server for MySQL. They also support Amazon Aurora as well as Amazon RDS. As with the other tools, Releem relies on an agent co-located with the DBMS instances for collecting metrics and applying new configurations. As their competitors they make use of a cloud backend that is tailored for visualizing monitored data, analyzing the data, and making recommendations. The agent is currently available at Github under a GNU license.
Releem offers a four-stage pricing model starting with a limited “always free” version. The paid versions “small” and “medium” mostly differ with respect to the number of supported MySQL servers, while the “auto tune db” package adds support for Google Cloud SQL, more support options, and help with query optimization.
In their own evaluation Releem demonstrates up to 64% performance improvements over a default configuration for MySQL 8 stressed with a sysbench benchmark for one hardware environment. For MySQL 5.7 they claim a maximum improvement of about 30%.
Nothing is known about the affinity of Releem’s CEO to animal costumes.
Discussions
Database tuning is a cumbersome, error-prone, and complex matter due to the many different configuration options and use case scenarios. In this article, we have presented a high-level overview of three company-backed tools that support auto-tuning of relational databases. The following table contains a summary of key indicators of the companies.
| OtterTune | DBtune | Releem | |
|---|---|---|---|
| Established | 2021 | 2022 | 2020 |
| HQ | USA | Sweden | USA |
| Current status/ Funding | VC-backed $14.5 million | VC-backed 2.4 M€ | unknown |
| Origin | Academia | Academia | unknown |
| HQ | USA | Sweden | USA |
| DBMS Technology | PostgreSQL, MySQL with focus on AWS RDS | PostgreSQL, MySQL, RocksDB, FoundationDB | MySQL and derivates |
| Documentation and first steps | Documentation available on web site. Easy to follow getting started guide. | Little information available on website. More extensive user guide available on github. | Documentation available on web site. Easy to follow getting started guide. |
| User base | Large user list with many renowned companies | No customer list available | Long list of testimonials available on website. |
Technically, all companies make use of some agent-based approach where users have to install software besides their DBMS to gather metrics and usage statistics. This data is then evaluated by a cloud service. All companies promise help to operations teams and suggest that by their solution performance can be increased and operation cost be reduced. Yet, as of now, it is still unclear, if the services are able to deliver up to their promises, what constraints they face and also what kind of pre-conditions need to be fulfilled so that they can be used, e.g. with respect to the workload.
Even though by now a few companies have started disrupting the market for automatic database tuning, the topic itself is also still a hot topic in academic research. For instance, a research group at Peking University presented their AI-backed DBtune framework at a series of research papers at renowned conferences.
Have you tried out one of the tools mentioned here and would be willing to share your experiences with us? Are you interested in hands-on capabilities of the tools and would like to see some benchmarks in one of the following newsletters? We missed a comparable company or open-source project? Let us know.