Continuous improvement

Continuous monitoring

Data security

Versatility

Dana Van Aken

<p>Business continuity is a top priority, and many organizations cannot operate without their production databases running 24/7. This requirement makes automatic database configuration tuning tricky because some knobs require you to restart the database before they take effect. We refer to these as static knobs. Contrast this with dynamic knobs that do not need you to restart the database.</p>
<p>Here are four key lessons we’ve learned building and implementing the <a href="https://ottertune.com/features">OtterTune automatic database configuration tuning service</a>. Whether you use OtterTune, or you tune by hand, these tips are helpful when it comes to the dreaded database restart.</p>
<h2>1. Tune Dynamic Knobs First, then Static Knobs Second.</h2>
<p>To get the most benefit from database tuning, you will need to target multiple knobs, some dynamic and some static. To avoid too many restarts, you should start with tuning the dynamic knobs first. Then if you are still unsatisfied with your database’s performance, expand the tuning scope to include static knobs.</p>
<h2>2. Tune within a Specific Time Window.</h2>
<p>If your databases are running in production all the time, you will want to tune them within a specific time window each day. The ideal window is one where the  load is not too much that configuration changes could cause degradations.</p>
<p>It may only be possible to restart once a day, in which case, you can still get on with tuning the database, but it may take longer to reach a near-optimal configuration. BTW, <a href="https://docs.ottertune.com/info/dashboard/tuning-options#restart-schedules">OtterTune allows you to specify custom restart time windows</a>.</p>
<h2>3. Remember that Time to Restart Varies.</h2>
<p>It is crucial to keep in mind that with some databases, the time it takes for the system to come back online after restarting is not deterministic. For example, if you change MySQL’s <a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_file_size">INNODB_LOG_FILE_SIZE</a> knob, the restart time varies depending on whether the new value is greater or less than the previous value. If you reduce the log file size, MySQL may have to compact the log file upon restart to get it under the new threshold. That process could take quite a bit of time, depending on the disk speed and other factors. Because of this, it makes sense in some instances to restrict yourself to a once-daily database restart.</p>
<h2>4. Create a Control Database.</h2>
<p>If your current database cannot tolerate restarts and you have the tools available, you can copy the data onto a server with similar hardware and replay a query trace. This way, you can tune static knobs offline using a control database. The table below includes some of the open source tools you can use to accomplish this.</p>
<table>
<thead>
<tr>
<th></th>
<th>PostgreSQL</th>
<th>MySQL</th>
</tr>
</thead>
<tbody>
<tr>
<td>Copy Database</td>
<td><a href="https://www.postgresql.org/docs/13/app-pgdump.html">pg_dump</a> + <a href="https://www.postgresql.org/docs/13/app-pgrestore.html">pg_restore</a></td>
<td><a href="https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html">mysqldump</a></td>
</tr>
<tr>
<td>Capture Query Trace</td>
<td><a href="https://www.postgresql.org/docs/13/runtime-config-logging.html">PostgreSQL Log</a> (see <a href="https://github.com/laurenz/pgreplay#usage">here</a> for configuration)</td>
<td><a href="https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html">MySQL Slow Query Log</a> (see <a href="https://github.com/Percona-Lab/query-playback#capturing-everything-in-the-slow-log">here</a> for configuration)</td>
</tr>
<tr>
<td>Replay Query Trace</td>
<td><a href="https://github.com/laurenz/pgreplay">pgreplay</a></td>
<td><a href="https://github.com/Percona-Lab/query-playback">Percona Playback</a></td>
</tr>
</tbody>
</table>
<h2>A Final Thought</h2>
<p>Whether you’re tuning production databases or have greater flexibility and more time to test out your tuning settings, database tuning frequently requires restarts. The key is to choose knobs wisely, be willing to restart only within set windows, and create an environment that allows you to measure the success of your efforts.</p>


Automatic Database Tuning Tips: Handling Restarts

DJ OT

<p>We have been a busy raft of otters since the launch of the preview release of the <a href="https://ottertune.com/features">OtterTune automatic database tuning service</a> in May. Our primary focus since then has been to make it easier to get started with OtterTune and put it to work auto-tuning your databases. Here’s what’s new in this latest update.</p>
<h2>Tune Your First Database for Free</h2>
<p>We now allow people to get started using OtterTune by auto-tuning their first database for free. A new free Starter tier supports basic auto-tuning of the most common knobs for Amazon RDS PostgreSQL or MySQL (in any/all AWS availability zones as of October 21).</p>
<p>You can get started by <a href="https://service.ottertune.com/signin/register">creating your free OtterTune account</a> and following the <a href="https://docs.ottertune.com/#getting-started">quickstart tutorial.</a> Please refer to the <a href="https://ottertune.com/pricing">OtterTune pricing information</a> for auto-tuning more than one database or additional enterprise features.</p>
<h2>Easier Setup and Connectivity</h2>
<p>The OtterTune account creation and setup process has been automated to make it easier for you to get started on your own. New features include:</p>
<h4>Fast Setup with CloudFormation Templates</h4>
<p>OtterTune provides <a href="https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/Welcome.html">AWS CloudFormation</a> templates that make it fast and easy to set up the AWS IAM role and the OtterTune agent (see below) needed to monitor and auto-tune your database. See the <a href="https://docs.ottertune.com/info/connect-your-database-to-ottertune/create-iam-role">documentation</a> for more details.</p>
<h4>Monitor Your Database with OtterTune’s Agent</h4>
<p>OtterTune now provides a containerized <a href="https://docs.ottertune.com/info/connect-your-database-to-ottertune/add-database/agent">metrics collection agent</a> to make it easier for you to connect your AWS database with OtterTune. The new agent allows the OtterTune auto-tuning service to monitor and optimize your database without needing to provide us with inbound network access to your private network. It retrieves internal metrics from the database and AWS CloudWatch. The source code for OtterTune's agent is <a href="https://github.com/ottertune/ot-agent">available on GitHub.</a></p>
<h4>Okta Self-Service Registration</h4>
<p>OtterTune now uses <a href="https://www.okta.com/">Okta</a> to provide you with a seamless, secure authentication experience. The security and safety of your database is a top priority for us. This is why we do not need to look at your user tables or queries to figure out how to optimize your database.</p>
<h2>New Auto-Tuning Controls</h2>
<p>Based on feedback from early OtterTune users, we’ve added features that give you more control over how and when OtterTune applies configuration changes to your databases:</p>
<h4>Flexible Optimization with Tuning Schedules</h4>
<p><a href="https://docs.ottertune.com/info/dashboard/tuning-options#tuning-schedule">OtterTune's Tuning Schedule</a> allows you to specify time windows for when the service will attempt to optimize the database's configuration. These schedules allow you to avoid potential performance degradation during an application's peak usage. If you enable database restarts, then the Tuning Schedule also lets you specify when OtterTune is allowed to restart the database.</p>
<h4>Apply Configurations Manually</h4>
<p>OtterTune provides an “<a href="https://docs.ottertune.com/info/dashboard/configurations#applying-configurations-manually">Apply Configuration</a>” button that enables you to immediately override the current (active) configuration with one that was applied to the database previously.</p>
<h4>Track Your Configurations with Configuration Versioning</h4>
<p>OtterTune <a href="https://docs.ottertune.com/info/dashboard/configurations#viewing-a-configuration">keeps track of each configuration</a> that it installs on the database which allows you to view the knob settings for each configuration and the measured performance. You can also compare the differences between two configurations.</p>
<p align="center"><img src="//images.ctfassets.net/bhyalf3qnjwn/1lNIlqKHK6mLVaN2LVJrNO/48e263fead5f36c285b55d5eb9170204/configurations-entry.png" style="width:90%; max-width:800px;" /></p>
<h2>User Docs and an Open Support Community on Slack</h2>
<h4>OtterTune User Docs</h4>
<p>We published the first version of<a href="https://docs.ottertune.com/"> OtterTune user documentation</a>, which details how to connect OtterTune to your database and start the machine learning (ML)-driven tuning process.</p>
<h4>Come Chat with Us on Slack</h4>
<p>We also created an open support community on Slack. If you have questions about OtterTune, <a href="https://join.slack.com/t/ottertune-community/shared_invite/zt-wr4gztk0-Sta_86xRQ6~o3WRpMvRlgA">join us on Slack</a> and ask us for help.</p>
<h2>Get Started for Free and Connect with Us on Slack</h2>
<p><a href="https://service.ottertune.com/signin/register">Create your free OtterTune account</a> and <a href="https://join.slack.com/t/ottertune-community/shared_invite/zt-wr4gztk0-Sta_86xRQ6~o3WRpMvRlgA">join us on Slack</a> to let us know how it’s going. We look forward to your questions and feedback!</p>


OtterTune Oct 2021 Product Update: Bring Da Ruckus

Andy Pavlo

<p>Jeff Bezos has a lot of money. Most think he acquired his wealth from people buying everything from books and toilet paper on the Amazon storefront. The truth is that nowadays, Bezos is making a lot of money via Amazon Web Services (AWS).</p>
<p>We have met AWS users spending hundreds of thousands per month on database services, some of which are spending up to 40% of their monthly AWS bill on databases. And a large portion of this database revenue comes from people overpaying for their unoptimized RDS instances. In other words, they are paying Amazon more money to run their MySQL and PostgreSQL databases than what they actually need to support their applications. But it does not need to be this way.</p>
<p>In this article, I want to discuss three ways that we have seen AWS customers unnecessarily padding Bezos’ bank account with expensive database costs. I will also mention what Bezos does with that extra money that you give him each month. Some of his purchases are admittedly pretty good; for example, he got a <a href="https://www.vice.com/en/article/jg845g/oh-cool-jeff-bezos-just-put-a-soft-serve-machine-in-his-house">$22,000 soft-serve ice cream machine</a> for his $165M house in Los Angeles. But as we will see, other purchases are more exorbitant. I hope this will motivate the AWS DBMS user community to do something about it (spoiler: <a href="https://ottertune.com/features">OtterTune</a> automatic database tuning can help!) and bring Bezos’ exotic spending habits back down to earth.</p>
<h2>#1 - Using Larger Instance Sizes Than Needed = Backup Superyacht</h2>
<p>The great thing about running your database in the cloud is Amazon makes it easy to scale vertically and use a bigger instance size. Instead of procuring new hardware and then migrating your database to the new machine (which can take weeks or months), AWS lets you upgrade your database with more CPU and RAM with only a few clicks. Such easy scaling is helpful if your database grows and you find that query performance is getting slower over time. But there is a limit to how much faster your DBMS can get by scaling vertically, especially if you do not adjust its configuration as you change the system’s hardware. But we see customers blindly increasing their RDS instance sizes without first checking whether they can optimize their DBMS for its current instance size. And this is, of course, giving Bezos more money than he needs.</p>
<p>To show again the limits of scaling vertically, we ran a quick experiment using the industry-standard TPC-C benchmark on PostgreSQL RDS (v13). We start with the smallest instance size (<strong>db.m5.large</strong>) and then scale up the instance size to measure the database’s performance. For each instance, use the same database size and workload generator settings. We also let OtterTune optimize the DBMS’s configuration for each instance. The graph below shows that for this workload, using an instance size larger than <strong>db.m5.8xlarge</strong> does not make a difference. The application cannot push more queries through the system, and thus using a larger instance is a waste of money. Capacity planning for databases is a challenging problem. Using an automated tool like OtterTune to manage the DBMS’s configuration is the first step towards solving it.</p>
<p align="center"><img src="//images.ctfassets.net/bhyalf3qnjwn/5oA2ky9wpAyF6FMwITXqvN/4c242cb02558e13295969f60b3b55d4d/ottertune-bezos-instances.svg" style="width:90%; max-width:800px;" /></p>
<h4>What Bezos Bought with This Money:</h4>
<p>It is hard to quantify how many people are paying for a larger RDS instance than they need, but I am sure there are many. Bezos is getting rich off people’s indolence in managing their DBMSs. He then uses that money to buy a <a href="https://www.bloomberg.com/news/features/2021-05-07/jeff-bezos-s-new-superyacht-heralds-roaring-market-for-big-boats">backup yacht for his new $500m superyacht</a>. It is well known that database billionaires like to build superyachts (Oracle’s Larry Ellison had <a href="https://www.clubyacht.net/yachts-and-owners/larry-ellisons-superyachts/">four boats</a> at one point). But buying a smaller yacht so your main yacht does not get lonely is next-level garishness.</p>
<h2>#2 - Running with the Default Configuration = $23M "Museum" Mansion</h2>
<p>Part of what makes DBMSs so enigmatic is that their performance and scalability are highly dependent on their configurations. Further exacerbating this problem is that the default configurations of DBMS knobs are notoriously bad. Know that the defaults always produce suboptimal results, even if they are “pre-tuned” by a cloud database provider like Amazon Web Services.</p>
<p>To give you a sense of how much using the default configuration can cost you, we ran another experiment on two PostgreSQL RDS databases. For the first database, we used a <strong>db.m5.4xlarge</strong> instance (16 vCPUs, 64GB RAM) with 4000 provisioned IOPs. For the second database, we used a smaller <strong>db.m5.2xlarge</strong> instance (8 vCPUs, 32GB RAM) with 2000 provisioned IOPS. We then ran the TPC-C benchmark on each database instance twice: once with Amazon's default RDS configuration and once using OtterTune's optimized configuration.</p>
<p>The graph below shows that the OtterTune-optimized PostgreSQL instance on the smaller instance achieves the same throughput performance as the larger instance still using the default configuration. But the OtterTune-optimized instance only costs $8,544/year while the other database costs $17,088/year. That means if your database is using the default configuration, you could be paying Amazon 50% more than you need to for it.</p>
<p align="center"><img src="//images.ctfassets.net/bhyalf3qnjwn/6w70TnrcCADuR3TnRLJDO3/80c6902fe8110fd036ce2484a05eaf67/ottertune-bezos-config.svg" style="width:90%; max-width:800px;" /></p>
<h4>What Bezos Bought with This Money:</h4>
<p>And so what does Jeff Bezos do with all that extra money that he earns from people using the default database configuration? He bought a <a href="https://www.wusa9.com/article/features/producers-picks/jeff-bezos-amazon-dc-kalorama-mansion-racking-up-parking-tickets/65-d3f68b7d-d262-42aa-9cf3-b0b9e3312aa2">$23M museum in Washington, DC</a> and turned it into a new house for himself. The property was already the largest mansion in the city at 22,000 square feet before Bezos bought it. But after paying for extensive renovations with his database monies, the house is even larger at 34,000 square feet.</p>
<h2>#3 - Paying For Unnecessary Provisioned IOPs = $42M Underground Clock</h2>
<p>Using Provisioned IOPs (PIOPs) is an essential optimization in AWS to ensure that your PostgreSQL and MySQL RDS databases achieve consistent read/write performance. But PIOPs are expensive: the maximum amount of 80,000 PIOPs for a single node DBMS costs $16,000 per month as of September 2021. We have met customers that are paying AWS more money for PIOPs than for the RDS instances themselves.</p>
<p>Like using an instance size that is too large, people are often overpaying Amazon for more PIOPs than their RDS databases need. The challenge, of course, is that it is hard to determine the right PIOPs setting for your database as it depends on many factors. For example, suppose your application executes mostly read queries (i.e., it does not perform many writes). Then the amount of data that the DBMS reads from the disk will partly depend on whether the application’s working set (table and index data) fits in memory. PIOPs levels for write-heavy workloads are more challenging to predict because DBMSs have many internal components that write data (e.g., double-writeback buffer, write-ahead log, background writer, autovacuum).</p>
<p>Given this complexity, an all too common approach to improving RDS performance problems that seem to be related to disk I/O is to increase PIOPs blindly. Adjusting this setting may boost your database performance at first, but this will have diminishing returns. Unless you take the time to analyze your application’s behavior and how it uses the database, you may set the PIOPs level too high and thus overpay for unused resources.</p>
<p>To illustrate this point, we ran the TPC-C benchmark again on a PostgreSQL instance (<strong>db.m5.2xlarge</strong>) and scaled up the PIOPs setting for the database from the smallest amount (1000 IOPs) to the maximum amount (80,000 IOPs). We use the same instance size for every trial in this deployment but allow OtterTune to generate a new optimized configuration for each PIOPs level.</p>
<p align="center"><img src="//images.ctfassets.net/bhyalf3qnjwn/1t3DAcUa6NXTL2QyvGHGjD/002255818f63736844f9c98e62059534/ottertune-bezos-piops.svg" style="width:90%; max-width:800px;" /></p>
<p>As shown in the graph above, the additional PIOPs increase PostgreSQL’s throughput by almost 6x for a 10x cost increase: from 1k ($200/month) to 10k ($2000/month). But beyond 10k PIOPs, PostgreSQL’s performance does not improve. These results do not mean PostgreSQL cannot take advantage of additional PIOPs beyond 10k; it does not make a difference for our write-heavy workload (TPC-C). So at 80k PIOPs, we would pay an extra $14,000/month for faster storage but not getting any benefit.</p>
<h4>What Bezos Bought with This Money:</h4>
<p>We have found that many others have also misconfigured PIOPs for their RDS instances in similar ways. And Bezos is laughing all the way to the bank. He then takes all his PIOPs winnings and writes a check to buy a <a href="https://www.theverge.com/tldr/2018/2/20/17031836/jeff-bezos-clock-10000-year-cost">$42M clock inside a mountain that only ticks once per year</a>. From a scientific point of view, I agree that this clock is kind of cool, but it is clearly something that you buy when you already have everything. It is like getting something from <a href="https://en.wikipedia.org/wiki/The_Sharper_Image">The Sharper Image</a> but for billionaires.</p>
<h3>Final Word</h3>
<p>We love AWS and the good people who develop, manage, and operate it. We do a lot of work with AWS RDS users. These DBMSs are a great value if you use them responsibly. OtterTune can help you do that.</p>
<p>Jeffrey Bezos image source: Daniel Oberhaus, CC BY 4.0 <a href="https://creativecommons.org/licenses/by/4.0">https://creativecommons.org/licenses/by/4.0</a>, via Wikimedia Commons</p>


You are Overpaying Jeff Bezos for Your Databases (and the things he does with that extra money)

Ruogu Du

Bohan Zhang

<p><a href="https://aws.amazon.com/rds/mysql/">Amazon RDS MySQL</a> is a popular choice for running a MySQL database. When you provision an RDS MySQL instance, it comes pre-installed with Amazon's <a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html#Appendix.MySQL.CommonDBATasks.Parameters">default configuration settings</a> for dozens of tunable knobs. These knobs control the DBMS's runtime behavior (e.g., buffer pool policies, thread scheduling). Amazon's default settings use general-purpose values for the most common workloads. That is, they are not particularly bad for any application's workload. But of course, this means that they are also not optimal for your database's specific workload.</p>
<p>Correctly setting these knobs to optimize your database's price-versus-performance trade-offs is non-trivial. It requires you to have both of the following:</p>
<ul>
<li>Specialized knowledge of MySQL internals</li>
<li>Time to make changes and test the impact of the change on performance until it is optimized (i.e., trial-and-error)</li>
</ul>
<p>If you run Amazon RDS MySQL and you lack (1) or (2) above, or if your workload changes over time, your cloud costs are likely higher than they should be for your database.</p>
<h1>Comparing MySQL Configurations for Amazon RDS</h1>
<p>We wanted to see how poor Amazon's RDS knob settings could be and understand how to fix them. We ran an experiment using MySQL (v8.0) on Amazon RDS and measured its performance using three database configurations:</p>
<ol>
<li>Default Amazon RDS knob settings</li>
<li>Knob setting recommendations from <a href="https://github.com/major/MySQLTuner-perl">MySQLTuner</a></li>
<li>Knob setting recommendations from OtterTune's <a href="https://ottertune.com/features">machine learning algorithms</a></li>
</ol>
<p>We ran the tests on an Amazon <strong>db.m5.4xlarge</strong> RDS instance equipped with:</p>
<ul>
<li>16 vCPUs</li>
<li>64 GB RAM</li>
<li>2000 provisioned IOPs</li>
</ul>
<p>We used the <a href="https://github.com/cmu-db/benchbase">BenchBase</a> framework to run the industry-standard <a href="http://www.tpc.org/tpcc/">TPC-C benchmark</a>. TPC-C models an order processing application similar to an online storefront, where customers purchase items and the database keeps track of inventory. We configured BenchBase to load a TPC-C database with 200 warehouses (~25 GB of table data) and simulate an application with 50 active connections.</p>
<h2>MySQLTuner Setup</h2>
<p>To use MySQLTuner on an RDS instance, we downloaded the latest version of the script from Github and then ran it on an EC2 instance inside of our Amazon VPC. Since this EC2 instance is inside our firewall, we only have to provide MySQLTuner the username and password for the target MySQL instance. MySQLTuner is unaware that the database is running in Amazon RDS, so we have to manually edit the <a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html">Parameter Group</a> using the AWS console.</p>
<p>We applied the following configuration that MySQLTuner recommended. We note that MySQLTuner performs many <a href="https://github.com/major/MySQLTuner-perl/blob/master/INTERNALS.md">other useful checks</a>, but it only recommended that we modify two knobs for MySQL's InnoDB storage engine:</p>
<pre><code>innodb_buffer_pool_instances = 46
innodb_log_file_size = 5368709120
</code></pre>
<h2>OtterTune Setup</h2>
<p>We provided OtterTune the necessary information to connect to MySQL using Amazon's AWS API and the database itself to retrieve MySQL's runtime metrics via SQL.</p>
<p>OtterTune uses MySQL's <a href="https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html">performance schema</a> to calculate the database's throughput. We set MySQL's <tt><a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_monitor_enable">INNODB_MONITOR_ENABLE</a></tt> knob to <tt>module_trx</tt> to enable transaction counters. We then instructed OtterTune's ML algorithms to generate MySQL configurations that optimize for the total number of transactions committed (<tt>trx_rw_commits</tt> + <tt>trx_ro_commits</tt> + <tt>trx_nl_ro_commits</tt>). We can confirm that MySQL is properly collecting these counters by checking the system's internal <tt><a href="https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-metrics-table.html">INNODB_METRICS</a></tt> catalog table to make sure that the counters are enabled:</p>
<pre><code>mysql> SELECT name, comment, status FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE name LIKE 'trx_%_commits';
+-------------------+--------------------------------------------------------------------+---------+
| name              | comment                                                            | status  |
+-------------------+--------------------------------------------------------------------+---------+
| trx_rw_commits    | Number of read-write transactions  committed                       | enabled |
| trx_ro_commits    | Number of read-only transactions committed                         | enabled |
| trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled |
+-------------------+--------------------------------------------------------------------+---------+
3 rows in set (0.00 sec)
</code></pre>
<p>We allowed OtterTune to tune 16 MySQL knobs. The listing below is the best configuration that OtterTune generated for the <strong>db.m5.4xlarge</strong> RDS instance.</p>
<pre><code>innodb_adaptive_flushing_lwm = 8
innodb_adaptive_hash_index = ON
innodb_adaptive_max_sleep_delay = 208252
innodb_buffer_pool_instances = 27
innodb_buffer_pool_size = 47110422528
innodb_change_buffering = purges
innodb_io_capacity = 311
innodb_log_file_size = 19437453312
innodb_max_dirty_pages_pct = 98
innodb_max_dirty_pages_pct_lwm = 49.401562
innodb_sync_array_size = 799
innodb_thread_concurrency = 637
innodb_thread_sleep_delay = 0
max_heap_table_size = 154674176
thread_cache_size = 86
tmp_table_size = 208992983
</code></pre>
<p>Some of these knobs do not require OtterTune to restart MySQL before they take effect. Such knobs are referred to as <a href="https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html">dynamic system variables</a> in MySQL. Other knobs do not take affect until you restart the DBMS.</p>
<h1>Result: OtterTune Makes MySQL 4x Faster</h1>
<p align="center"><img src="//images.ctfassets.net/bhyalf3qnjwn/2zsdQ0fuZ3IImdrIJkABjd/84ea69d59c8098dd43e9668dbd80acbd/ottertune-mysqltuner-throughput1.png" style="width:90%; max-width:800px;" /></p>
<p>In the graph above, we see that, as expected, Amazon's default configuration for MySQL RDS performs the worst with only 781 transactions per second (txn/sec). MySQLTuner's recommendations improve MySQL's throughput by 116% over the RDS default by changing two knobs. This improvement is undoubtedly good, but OtterTune optimizes more MySQL knobs to improve its performance even further. OtterTune's ML algorithm achieves the best performance with over 3500 txn/sec, 4x higher than the default RDS configuration from Amazon and 20% higher than the MySQLTuner outcome.</p>
<h1>Better Performance or Lower Cost?</h1>
<p>The first test showed how OtterTune customizes MySQL's knob settings for the application's workload to get better performance than Amazon's default settings and the popular MySQLTuner tool. Better performance means that the DBMS can execute more queries quickly even if your database grows over time or your application adds new features. Improving a database's performance helps applications that may be running a bit slow.</p>
<p>For people who are satisfied with their database's current speed, but would like to lower their monthly AWS bills, tuning database knobs can also help. To demonstrate that, we reran the TPC-C benchmark on a smaller <strong>db.m5.2xlarge</strong> RDS with fewer provisioned IOPs than the instance we tested above:</p>
<ul>
<li>8 vCPUs</li>
<li>32 GB RAM</li>
<li>1000 provisioned IOPs</li>
</ul>
<p>In the graph below, we see that, again, OtterTune can optimize MySQL's configuration to get better performance than Amazon and MySQLTuner. On this smaller instance, using the OtterTune-generated configuration makes MySQL run 288% and 63% faster than Amazon and MySQLTuner, respectively.</p>
<p align="center"><img src="//images.ctfassets.net/bhyalf3qnjwn/5dXoDBIJEYsPVYOHrKyQNo/096947196a38a462e6c5d0b6bb15b0d1/ottertune-mysqltuner-throughput2.png" style="width:90%; max-width:800px;" /></p>
<p>But what is most impressive is that OtterTune's optimized configuration for MySQL on the <strong>db.m5.2xlarge</strong> instance achieves almost 2x better performance than the default Amazon RDS configuration for the <strong>db.m5.4xlarge</strong> instance. As of the time of this article in September 2021, the smaller <strong>db.m5.2xlarge</strong> instance costs $7,191/year (instance cost + provisioned IOPs), while the <strong>db.m5.4xlarge</strong> instance costs $14,383/year.</p>
<style type="text/css">
    .prose tbody td {
        vertical-align: middle;
    }
</style>
<table style="text-align: right; font-size: small;">
    <thead>
        <tr>
            <th></th>
            <th>Instance</th>
            <th>IOPs</th>
            <th>Throughput</th>
            <th>Cost</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td><img src="//images.ctfassets.net/bhyalf3qnjwn/5vZRaJLkTETBixy2S9Hlma/4f79853e48d2d9ad21f536407c9e4d3c/amazon.svg" width="100" style="margin:0px; min-width:50px;"></td>
            <td>db.m5.4xlarge</td>
            <td>2000</td>
            <td>781 txn/sec</td>
            <td><strong>$14,383/year</strong></td>
        </tr>
        <tr>
            <td><img src="//images.ctfassets.net/bhyalf3qnjwn/B7tDwlS8ozwgNpVrqMSTc/3bc61c98c4ca89ca98dfd6ec57846be9/ottertune-text-black.svg" width="100" style="margin:0px;min-width:50px;"></td>
            <td>db.m5.2xlarge</td>
            <td>1000</td>
            <td>1504 txn/sec</td>
            <td><strong>$7,191/year</strong></td>
        </tr>
    </tbody>
</table>
<p>These results mean that for this workload, OtterTune gets double the performance at half the cost! It is important to remember that this result is specific to this workload. Other applications may experience a smaller improvement, while others may have an even more significant performance boost and cost reduction. There are too many factors that affect MySQL's runtime behavior. This complexity is why database tuning is so hard. But we are fairly certain that you should probably <strong>not</strong> use Amazon's default RDS configuration.</p>
<h4>Try it on your MySQL Database</h4>
<p>If you would like to learn more about how OtterTune works on the inside to automate database configuration tuning, watch <a href="https://ottertune.com/blog/ottertune-explained-in-five-minutes">this short video</a>.</p>
<p>If you want to see whether OtterTune can improve your database performance, let us know, and we will help you get started with OtterTune.</p>
<p>[<a href="https://ottertune.com/try">Try OtterTune</a>]</p>


Benchmark: Using Machine Learning to Optimize Amazon RDS MySQL Performance

<p>Autonomous database tuning relies on machine learning (ML) algorithms to automate tasks that have, to date, been performed manually by database administrators (DBAs). Today, you can find ML-powered automated tuning tools in products like <a href="https://ottertune.com/">OtterTune</a>, <a href="https://www.oracle.com/autonomous-database/">Oracle Autonomous Database</a>, and <a href="https://blogs.oracle.com/mysql/mysql-autopilot-machine-learning-automation-for-mysql-heatwave">MySQL Autopilot</a>.</p>
<h3>Will Machine Learning Make DBAs and Devops Obsolete?</h3>
<p>I get that question from time to time. My answer is always, “no, ML will not make humans obsolete.” But, the technology will change how humans administer databases and require some new skills to master. Here are three predictions about the impact that automatic database tuning will have on database administration:</p>
<h3>Prediction #1: ML Will Yield Better DB Price-Performance with Less Effort</h3>
<p>This first prediction is obvious; it is why DBMS technology developers are investing so much R&#x26;D into the functionality. Optimizing DBMS performance is a complex process. Consider configuration tuning, which involves tweaking hundreds of knobs to optimize the DBMS performance for a specific workload on a given hardware and OS platform. Finding the perfect settings is tedious--usually a trial and error process that can take days. It is so tedious that people do not do it frequently enough, or too often, they do not do it at all, which is inefficient and results in poor performance or higher DBMS hosting costs.</p>
<p>Autonomous database tuning solves this by implementing ML algorithms trained to determine the optimal config settings (or query plan, index selection) for a given workload. The approach is automatic, generates better performance, and saves developers time and effort. Where ML shines is with large deployments of several dozen or hundreds of databases. No human has the time (or patience) to tune each database individually. But the ML-automated tuning process needs guidance, which leads me to my next prediction.</p>
<h3>Prediction #2: Humans Will Guide Autonomous Database Operations</h3>
<p>When it comes to database tuning, ML algorithms do not know and do not care what they are tuning in the system, leading to a complex set of challenges. For example, what if an algorithm sets the size of a buffer pool to be larger than the amount of memory available on the system and then restarts the system? Some database systems, like PostgreSQL, will refuse to start because the buffer pool is too large. Other systems, like MySQL, will gladly start the system even though the knob is erroneously set. It may then crash or get killed by the OS when the database tries to allocate more memory than is available.</p>
<p>For this reason, humans (e.g., devops, DBAs) will need to oversee and guide ML-driven automation to make sure databases are tuned correctly and safely. There are several facets to this:</p>
<ul>
<li>What the ML algorithms should optimize in the database (e.g., query latency, cost).</li>
<li>Whether to sacrifice the data safety and correctness in exchange for better performance.</li>
<li>Confirming appropriate value ranges for different knobs.</li>
<li>Specifying which knobs, if any, are off-limits to ML (and should only be changed manually).</li>
<li>Defining tuning time windows to control when the ML algorithms databases perform tuning and potentially restart the database (for specific config changes to take effect).</li>
</ul>
<p>OtterTune helps humans and ML work better together by exposing a customizable list of recommended knobs and values, and estimating which knobs will have the most impact on performance.</p>
<h3>Prediction #3: ML Will Add a New Dimension to Database Troubleshooting</h3>
<p>Humans working with ML-based database tuning tools will require some familiarity with ML. Such understanding is necessary if the database has unexpected behavior: a human operator will need to identify whether the behavior is due to ML-based decision-making and if so, what metrics and data were fed into the ML algorithms. It will also be helpful to have human-decipherable explanations of why the algorithms are making certain changes. DBAs need to be thoughtful about what data trains or signals ML models, especially with databases supporting cyclic workloads with complex patterns, because the system state is constantly changing.</p>
<h3>Final Thoughts</h3>
<p>Autonomous database tuning is a growing trend that database developers and administrators should be tracking. If you want to learn more about it, here are some helpful resources:</p>
<ul>
<li><a href="https://youtu.be/SJdnpAMI_q4">OtterTune Explainer Video</a></li>
<li><a href="https://ottertune.com/blog/rds-postgres-tuning-benchmark">OtterTune vs. PGTune Performance Benchmarks</a></li>
<li><a href="https://ottertune.com/blog/prevent-machine-learning-from-wrecking-your-database">How to keep Machine Learning from Wrecking your Database</a></li>
<li><a href="https://www.youtube.com/watch?v=tKYapjd62Eg">Oracle Autonomous Database Technical Overview Video</a></li>
</ul>
<p>And one more thing...never use the default database configuration! The default configuration file (my.cnf, postgresql.conf) that you get when you first install a database or create one in Amazon RDS is always terrible and makes your database run slow. Use human expertise and some type of automated tuning tools, whether that’s <a href="https://ottertune.com/try">OtterTune</a>, <a href="https://pgtune.leopard.in.ua/#/">PGTune</a>, <a href="https://github.com/major/MySQLTuner-perl">MySQLTuner</a>, or any of the various ones from commercial vendors. Any automated knob tuning can deliver significant improvement, without having to make drastic changes.</p>


How Will Machine Learning Transform Database Administration?

<p>Databases expose many configuration parameters (or "knobs") that allow you to change the system's runtime behavior. Correctly tuning these knobs is key to good application performance and efficiency, but it's a complex task because of the many possible choices that one has to make. What settings to use for your database depends on many factors: what the queries are, how big the database is, what the application is, and what the hardware is. These systems have gotten harder to tune over time too. For example, in the last twenty years, MySQL and PostgreSQL have increased the number of knobs that they expose to tune by 7x and 5x, respectively.</p>
<img src="//images.ctfassets.net/bhyalf3qnjwn/6KXOR2PjnIh5A5FpwK3uU2/dd239d3138a194219160249cd5e7151f/ottertune-knobs-per-release.png" width="872">
<p>This increased complexity has led to the development of machine learning (ML) approaches to automatically tuning database management systems (DBMSs). Commercial examples include <a href="https://www.oracle.com/autonomous-database/">Oracle Autonomous Database</a> and <a href="https://ottertune.com/">OtterTune</a>, as well as academic research projects like our <a href="https://noise.page/">NoisePage project</a> at <a href="https://db.cs.cmu.edu/">Carnegie Mellon</a>.</p>
<p>Machine-learning approaches to automatic database tuning fall into two categories: <strong>external tuning</strong> and <strong>internal tuning</strong>. As we now discuss, both have their pros and cons.</p>
<h3>External Database Automation</h3>
<p>An external ML approach is when the tuning algorithm can only observe the target's inputs and outputs in its decision making process; the algorithm has no knowledge of the system's internal structure, design, or implementation. In the context of database tuning, an external tuning tool interacts with the DBMS only through its standard APIs (e.g., SQL) and monitoring services (e.g., Amazon CloudWatch). In other words, an external ML approach does not require changes to the DBMS's source code, nor does it need the user to install custom extensions.</p>
<p>OtterTune is an external tuning service; it uses the target DBMS's standard interfaces (SQL) to:</p>
<ul>
<li>Retrieve the database's current configuration and other runtime info (e.g., metrics),</li>
<li>Change the configuration, and</li>
<li>Restart the system if necessary.</li>
</ul>
<p>OtterTune implements an exteranl tuning ML algorithm to improve a database’s query performance and efficiency without needing to modify the DBMS nor examine the application’s queries and data.</p>
<h3>Internal Database Automation</h3>
<p>An internal ML approach is when the DBMS has automatic tuning components tightly integrated into its internal architecture. Such integration requires the system developers to add “hooks” in the DBMS's source code to retrieve telemetry data and modify its behavior. With the former, these changes could allow the tuning algorithms to collect information about the system's runtime behavior and low-level hardware measurements, such as the number of CPU cycles used to insert a single record into an index. The algorithms could then control the system's behavior in ways that are not possible through existing APIs. For example, the tuning algorithm could make adjustments to the DBMS at the session-level to affect how the system executes individual transactions or queries. Such fine-grained control is not possible by humans and is not feasible with existing approaches today.</p>
<h3>Which approach is better?</h3>
<p>In external tuning automation systems like OtterTune, the ML logic exists outside of the DBMS. Thus, one can adapt the service to support new databases or new database versions more easily than with internal ML approaches. For example, we extended OtterTune to support Oracle within a few weeks for our deployment at Societe Generale. External tuning is also usable when it is not feasible to modify or extend a database or modify application code. For example, Amazon does not allow users to change the database's system binary for their MySQL or PostgreSQL RDS instances. Instead, they only allow a <a href="https://aws.amazon.com/about-aws/whats-new/2021/06/amazon-rds-for-postgresql-supports-extension-allowlists/">limited number of extensions</a>.</p>
<p>Internal tuning automation technology is still nascent. Developing ML-driven internal tuning requires deep knowledge of the system internals and source code, so DBMS users will have to wait for their vendors to add the capabilities to their database--and then upgrade to the new version of the DBMS. It is going to take a long time for every DBMS to incorporate internal ML optimizations because these changes are custom code specific to the system; one cannot easily port them to a new database. Other than Oracle Autonomous Database, Oracle's <a href="https://www.oracle.com/news/announcement/mysql-autopilot-for-heatwave-2021-08-10/">MySQL Heatwave Autopilot</a>, Huawei's <a href="https://opengauss.org/en/">OpenGauss</a>, and our work with CMU's NoisePage, innternal DBMS automation tuning implementations are not common today.</p>
<h3>How will these become part of your infrastructure?</h3>
<p>ML algorithms, whether external or internal to the database, automate the complex database tuning process. Automated tuning makes it easier to configure, optimize, and maintain a complex database deployment. This reduces the need to have developers and administrators “babysit” the database to make sure that it is performing its best. Those people can instead spend their time working on more fulfilling activities, like application development and other tasks that cannot be automated.</p>
<p>Automated tuning is an essential tool for anyone who cares about database price-performance, but does not have the time or expertise to keep multiple databases tuned for optimal performance.</p>
<h3>A Final Word</h3>
<p>Given today's extreme reliance on databases, everyone stands to benefit tremendously from advancements in the field of automated tuning using ML. Internal and external approaches have their trade-offs. It is exciting to see researchers and the industry as a whole are making advancements in creating autonomous databases, with ML doing the heavy lifting.</p>
<p>In deployments of OtterTune, we have seen it achieve significant performance improvements and cost reductions for PostgreSQL and MySQL database instances versus both default configuration settings (<a href="https://ottertune.com/blog/rds-postgres-tuning-benchmark">view the benchmarks</a>) and human-tuned configurations.</p>
<h3>Summary of Pros &#x26; Cons</h3>
<table>
<thead>
<tr>
<th>External ML Approaches</th>
<th>Internal ML Approaches</th>
</tr>
</thead>
<tbody>
<tr>
<td>+ More generic solution (portable across DBMS systems, hardware).<br> + Simpler to design and build since you do not need to understand the DBMS internals.</td>
<td>+ Potentially need less data to achieve good results compared to black box approaches.<br> + Fine-grained control over the DBMS’s telemetry and runtime behavior.</td>
</tr>
<tr>
<td>- Lack of explainability on the ML algorithm’s decision making.</td>
<td>- Higher engineering cost for DBMS developers.<br> - Lack of portability: ML algorithms are designed for only that system.</td>
</tr>
</tbody>
</table>


External vs. Internal Automatic Database Tuning Approaches

<p>During yesterday’s <a href="https://vldb.org/2021/">VLDB conference</a>, I presented “An Inquiry into Machine Learning-based Automatic Configuration Tuning Services on Real-World Database Management Systems.” The paper was a collaborative effort among the <a href="http://db.cs.cmu.edu/projects/autotune/">Carnegie Mellon Database Group</a>, <a href="https://www.princeton.edu/">Princeton University</a>, <a href="https://www.societegenerale.com/en">Societe Generale</a>, and OtterTune.</p>
<p>Autonomous database operation and tuning, driven by machine learning (ML), is becoming more prevalent. In this VLDB talk, I presented learnings that help DBMS users better understand how to benefit from ML more productively and safely.</p>
<div class="max-w-2xl">
      <div class="aspect-w-16 aspect-h-9">
        <iframe src="https://www.youtube.com/embed/gt5J23gyQ2I" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
      </div>
    </div>
<p>You can read the <a href="https://vldb.org/pvldb/vol14/p1241-aken.pdf">research paper here</a>.</p>
<h4>Talk highlights</h4>
<p>Tuning configuration knobs for modern database management systems (DBMSs) is essential, especially for workload-intensive applications, yet tuning is a complex process that typically requires an experienced database administrator (DBA). There are past and current efforts to automate the process, starting with heuristics tools in the early 2000s and leading up to today’s more advanced ML-based tools, including <a href="https://ottertune.com/features">OtterTune</a>. Evaluations have shown that ML-based tuners can achieve better performance compared to DBAs and other tuning tools, but what happens when ML-based tuning tools are used in real-world deployments?</p>
<p>This study discusses three “mismatches” between previous research studies and real-world deployments:</p>
<ol>
<li>Studies only consider open-source database systems.</li>
<li>They use synthetic benchmarks that don’t consider real workloads.</li>
<li>They run their experiments on dedicated local storage.</li>
</ol>
<h4>Automatic DBMS tuning in production at Societe Generale</h4>
<p>Given these issues, my collaborators and I were eager to complete a field study of automatic knob configuration on a commercial DBMS (Oracle) with a real-world workload in a production environment at Societe Generale (SG).</p>
<p>In 2020, SG agreed to work with our team to test out the OtterTune ML-based tuning framework. The goal was to see whether automated tuning could improve a DBMS’s performance beyond what their DBAs achieve through manual tuning. In this talk, I discuss the set-up at SG, as well as the methodology for tuning using OtterTune. The OtterTune and SG teams ran into several unexpected hurdles along the way—but in the end, the results were impressive. The ML algorithms generated knob configurations that improved performance by up to 45% over ones generated by a human expert.</p>


VLDB 2021 Video: Applying Machine Learning-based Database Tuning in Production

<p><a href="http://vldb.org/">VLDB</a>, one of the leading database management conferences <a href="https://en.wikipedia.org/wiki/International_Conference_on_Very_Large_Data_Bases">since 1975</a>, brings together data management and database researchers, vendors, practitioners, application developers, and users. At this year’s conference, running in Copenhagen and online, leaders in the industry will cover issues in data management, database, and information systems research. The OtterTune team will be participating in full force with a research paper presentation, workshop keynote, award talk, and discussion panel.</p>
<p>Here’s how you can catch us at the conference talking about applying machine learning to the complex process of database performance optimization:</p>
<h4><strong>Tue Aug 17 @ 4:00pm EST</strong> - <a href="http://vldb.org/2021/?program-schedule-roundtable">Magical Machine Learning for Database Tuning</a></h4>
<p>Roundtable panel hosted by Andy Pavlo.
Panelists Weiwei Gong (Oracle), Sam Lightstone (Facebook), Surajit Chaudhuri (Microsoft Research), Dana Van Aken (OtterTune), Bailu Ding (Microsoft Research)</p>
<h4><strong>Wed Aug 18 @ 3:00am EST</strong> - <a href="http://vldb.org/2021/?program-schedule-roundtable">An Inquiry into Machine Learning-based Automatic Configuration Tuning Services on Real-World Database Management Systems</a></h4>
<p>Research paper talk by Dana Van Aken (<a href="https://vldb.org/pvldb/vol14/p1241-aken.pdf">Read the paper</a>)</p>
<h4><strong>Thu Aug 19 @ 9:30am EST</strong> - <a href="https://vldb.org/2021/?program-structure">Make Your Database System Dream of Electric Sheep: Towards Self-Driving Operation</a></h4>
<p>VLDB Early Career Award talk by Andy Pavlo (<a href="https://vldb.org/pvldb/vol14/p3211-pavlo.pdf">Read the paper</a>)</p>
<h4><strong>Fri Aug 20 @ 10:00am EST</strong> - <a href="https://sites.google.com/view/aidb2021/">Lessons Learned from Using Machine Learning to Optimize Database Configurations</a></h4>
<p>AIDB Workshop keynote talk by Andy Pavlo</p>
<p>We look forward to sharing our research with the VLDB community and learning from others there as well. We hope you’ll <a href="https://vldb.org/2021/?info-registration">join the event</a>, and we’ll be sure to share thoughts and observations here, on our blog, after the conference.</p>


OtterTune at VLDB 2021

<p>In our <a href="https://ottertune.com/blog/history-ottertune-research-part1">previous blog post</a>, we detailed the seven years of intensive research and lessons we learned that led to the commercialization of the OtterTune database tuning service. Since then, we’ve brought OtterTune into the real world at Société Générale (SocGen) and Booking.com. We keep learning about how to optimize database performance, and OtterTune keeps getting smarter. Here are a few details we’ve learned so far.</p>
<h2>Lessons from our first real-world deployment</h2>
<p>Our latest academic paper, <a href="https://db.cs.cmu.edu/papers/2021/p1241-aken.pdf"><strong>An Inquiry into Machine Learning-based Automatic Configuration Tuning Services on Real-World Database Management Systems</strong></a>, focuses on our 10-month study of OtterTune running in a production environment at SocGen. OtterTune co-founder Dr. Dana Van Aken will be presenting these results at <a href="https://vldb.org/2021/">VLDB 2021</a> in August.</p>
<p>For this research, we deployed OtterTune on-site to tune a 1TB Oracle (v12.2) DBMS. We extended OtterTune to support multiple tuning algorithms. In addition to the original GPR algorithm from the <a href="https://dl.acm.org/doi/pdf/10.1145/3035918.3064029">SIGMOD 2017 paper</a>, we added two additional algorithms:</p>
<ol>
<li>Deep deterministic policy gradient (DDPG) from CDBTune and</li>
<li>Deep neural network (DNN) by OtterTune co-founder Bohan Zhang.</li>
</ol>
<p>Our results show that OtterTune’s ML-generated configurations improved the DBMS’s performance by up to 45% over a configuration hand-tuned by the bank’s in-house expert DBAs. We found that there was no major difference in the quality of tuning among the three algorithms. But they all beat the human experts. GPR converges the fastest but DNN had slightly better results. DDPG might be better if we ran it for longer but a single trial already took 3-4 days (each bar in the chart is the average of nine trials). This was our first real-world implementation of OtterTune and we gained a lot of insights.</p>
<p><img src="//images.ctfassets.net/bhyalf3qnjwn/zPwNiikMHHsJukHPIUI5P/266d6cecb3d8e3a3c413e53e4e690fb9/OtterTune-Oracle-tuning-benchmark.png" alt="OtterTune-Oracle-tuning-benchmark"></p>
<h3>Key details and advancements:</h3>
<p>During the process of setting up and deploying OtterTune at SocGen for this study, several issues arose that we did not anticipate. Some of these were specific to SocGen’s operating environment and cloud infrastructure. Several issues, however, are broadly related to the field of automated DBMS tuning with machine learning.</p>
<ul>
<li><strong>Tune without accessing sensitive data</strong> - Because the bank has strict security requirements, the DBMS could not use locally attached storage. It had to store the database on a shared NAS for regulatory reasons. We also had to consider GDPR privacy regulations. This was not an issue because the metrics OtterTune gathers are benign. OtterTune’s algorithms only use knobs and metrics, it does not retrieve sensitive queries or user data.</li>
<li><strong>Machine learning needs safeguards</strong> - Automated database tuning is more than just generating new configurations. The service needs additional safeguards to make sure it correctly identifies invalid configurations that cause the DBMS to fail to start and poor configurations that cause the DBMS to run much slower than before. If the ML algorithms do not have prior training data, they will inevitably select poor configurations to install on the DBMS at the beginning of a tuning session because they will explore the possible configurations to learn how different settings impact the target objective.</li>
<li><strong>Limit configuration testing times</strong> - We had to overcome issues with handling long-running and failed configurations. For example, the execution time of the workload in this instance depended on how long it took to replay the queries in the 10-minute trace we used in this study. We implemented an early abort feature that would stop executing long-running traces after a specified period of time (45 minutes by default) since some bad configurations would cause the execution time to take several hours.</li>
<li><strong>Discerning maintenance tasks from regular DB workload</strong> - In real-world deployments, DBMS maintenance tasks have to be considered. Some DBMSs invoke these tasks at scheduled intervals, while other tasks are in response to the workload (e.g., PostgreSQL’s autovacuum runs when a table is modified a certain number of times). It is best to be aware of these tasks in advance before starting a tuning session since they often cause performance to degrade.</li>
<li><strong>Auto-tuning benefits vs. costs</strong> - There are unexpected cost considerations to consider as well. Our results showed that ML-based algorithms generated configurations that improved performance by up to 45% over the configurations hand-tuned by expert DBAs. Although these gains are noteworthy, there is a trade-off between the time it takes to deploy OtterTune versus the benefit. But there are several non-obvious factors that organizations need to consider when making the decision to tune a DBMS. Such factors include the economic significance of the application, the administrative cost of tuning the database, and whether the organization has the tooling and infrastructure to run the tuning sessions.</li>
</ul>
<h2>Have OtterTune Make Your Database Smarter!</h2>
<p>OtterTune is the product of seven years of intensive research work and two real-world deployments, the first at SocGen, and the second at Booking.com.</p>
<p>Over the years, we’ve crafted what we believe to be the best way to keep databases optimized. We now work with PostgreSQL, MySQL running in the cloud (Amazon RDS and Aurora). The more databases OtterTune works with, the smarter it gets, so let us know if you'd like to <a href="https://ottertune.com/try">https://ottertune.com/try</a></p>


Seven Years of OtterTune R&D: Part II -  Learnings in the Wild

<p>In 2013, Andy Pavlo started the OtterTune research project at Carnegie Mellon University (CMU) as an extension to the <a href="https://db.cs.cmu.edu/projects/oltp-bench/">OLTP-Bench project</a>. Recognizing the importance and complexity of DBMS configuration tuning, The National Science Foundation (NSF) funded the project with a <a href="https://www.nsf.gov/awardsearch/showAward?AWD_ID=1423210">$500K grant</a> in 2014. That same year, <a href="https://www.cs.cmu.edu/~dvanaken/">Dana Van Aken</a>, who is now our co-founder and CTO, started as a new PhD student. She became the primary researcher for the project.</p>
<p>For the next seven years, we worked on the research that led to the commercial release of OtterTune this <a href="https://www.zdnet.com/article/ottertune-sets-out-to-auto-tune-all-the-databases/">past May</a>. The insights of our work have been published in several peer-reviewed papers. In a pair of blog posts, we’ll share an account of the research and findings that led us to where we are today. Here are some of the highlights and lessons we learned during the early years, developing OtterTune’s machine learning algorithms and services architecture.</p>
<h2>Reusing Training Data to Improve the Efficacy of Tuning Recommendations</h2>
<p>The first paper about the OtterTune service, presented at SIGMOD in 2017, was <a href="https://db.cs.cmu.edu/papers/2017/p1009-van-aken.pdf"><strong>Automatic Database Management System Tuning Through Large-scale Machine Learning</strong></a>.</p>
<p>The paper covered our automated, machine learning-driven approach, leveraging past experience and collecting new information to tune DBMS configurations. In our research, we used a combination of supervised and unsupervised machine learning methods to:</p>
<ol>
<li>Select the most impactful knobs,</li>
<li>Map unseen database workloads to previous workloads so we can transfer experience, and</li>
<li>Recommend knob settings.</li>
</ol>
<p>We implemented our techniques in OtterTune and tested it on three DBMSs. Our evaluation showed that OtterTune recommends configurations that are as good as or better than ones generated by existing tools or a human expert.</p>
<p>We designed OtterTune to use a pipeline of different machine learning algorithms (purple shapes in the diagram below) to: 1) characterize the target database's workload, 2) identify which knobs to tune, and 3) recommend new configuration settings. The OtterTune service uses ML metrics to map a new database workload it's trying to optimize to a similar workload that the service has seen in the past. This is how OtterTune can reuse training data it has collected from tuning other databases to improve the efficacy of its recommendations.</p>
<p><em>The OtterTune machine learning pipeline:</em>
<img src="//images.contentful.com/bhyalf3qnjwn/5hMA8WZiL9kUvXSBz9tPXz/3fd6d8fff81666c568436248858c51d3/OtterTune_Machine_Learning_Pipeline.jpeg" alt="OtterTune Machine Learning Pipeline"></p>
<h4>Key Learnings and Advancements:</h4>
<p>For this research, <strong>we ran 100,000 trials</strong> to build the initial training data corpus for OtterTune. Each trial was benchmarked using OLTP-Bench. As our ML algorithms improved, we found that we did not need to collect as much training data.</p>
<p>Among other ML algorithms, OtterTune uses a <a href="https://en.wikipedia.org/w/index.php?title=Gaussian_process_regression">Gaussian Process Regression</a>  algorithm, a common approach for solving black-box optimization problems and hyperparameter tuning. We originally wrote the algorithm in MATLAB. During a summer internship at CMU, Bohan Zhang, one of OtterTune’s co-founders, then <strong>rewrote it in TensorFlow</strong> and improved its computing efficiency, resulting in a 5x performance increase.</p>
<p>The first version of <strong>OtterTune supported MySQL, PostgreSQL, and Actian Vector</strong>. We evaluated both OLTP and OLAP workloads. We were using the latest versions of MySQL (v5.6) and PostgreSQL (v9.3) at the time, and we learned that the earlier DBMS versions had fewer knobs for improving performance of OLAP workloads than OLTP workloads.</p>
<p>OtterTune was well-received in academia after presenting it at SIGMOD 2017, but it wasn’t until we were invited to write a <a href="https://aws.amazon.com/blogs/machine-learning/tuning-your-dbms-automatically-with-machine-learning/"><strong>guest post for the AWS Machine Learning blog</strong></a> that we started hearing from a lot of industry folks trying to solve similar problems.</p>
<h2>Automatically Selecting a Configuration that Improves DBMS Performance</h2>
<p>A year later, we presented a short paper, <a href="http://www.vldb.org/pvldb/vol11/p1910-zhang.pdf"><strong>A Demonstration of the OtterTune Automatic Database Management System Tuning Service</strong></a>, at the VLDB conference in Brazil.</p>
<p>The paper described OtterTune’s microservice architecture. The tuning agent (now called the Driver) was used to collect the target DBMS’s current configuration and metrics and then transmit them to the OtterTune tuning manager. The tuning manager stored this data in its internal repository of previous tuning sessions. The algorithm then trains models with the data from the target DBMS as well as previous tuning data from similar workloads to produce a new configuration recommendation. The tuning manager sent this new configuration back to the agent to install on the target DBMS.</p>
<h4>Key Learnings and Advancements:</h4>
<p>For the live demonstration at VLDB, we challenged conference attendees to tune PostgreSQL (v9.6) running the TPC-C benchmark and compared them against OtterTune’s tuning algorithm. We gave them a web interface that allowed them to set the values for several knobs and then ran the workload.</p>
<p>OtterTune was able to get <strong>20% better performance than the best configuration from a human</strong>.</p>
<p><img src="//images.ctfassets.net/bhyalf3qnjwn/2h9o2pVYtI5zUSA4gbRzqi/cfb528dbb652388ca3cf143606075b20/Bohan-Zhang-OtterTune-VLDB-2018.jpeg" alt="Bohan-Zhang-OtterTune-VLDB-2018">
<em>Bohan Zhang in action at VLDB 2018. View other <a href="https://twitter.com/andy_pavlo/status/1035179003198488577">VLDB 2018 photos</a>.</em></p>
<h2>Stay Tuned for Part 2</h2>
<p>When we started the R&#x26;D on OtterTune in 2013, we knew we were onto something big. Our next step was to test it out in real-world settings. In our follow-on blog post, we’ll share what we learned from our first customer deployments of OtterTune.</p>


Seven Years of OtterTune R&D: Part I - The Lab Years

<p>Companies are moving their databases to hosted database services, such as Amazon RDS, to take advantage of the flexibility and scalability of cloud deployments. But what people do not often realize is that while these “fully managed” services simplify database provisioning and maintenance, customers are still responsible for some critical administrative tasks that are not easily automated. Database configuration tuning is one of them.</p>
<p>Optimizing a database’s configuration knobs for the workload speeds up performance and scales your systems, thereby lowering costs. Amazon already tweaks a few of MySQL and PostgreSQL out-of-the-box default settings for its RDS deployments based on the instance type. However, they do not tune them for any specific workload.</p>
<p>OtterTune automates the process of optimizing a database’s configuration knobs for its specific workload by using machine learning (ML). We showed in a <a href="https://ottertune.com/blog/rds-postgres-tuning-benchmark">previous post</a> that OtterTune achieves a 3x performance improvement for a PostgreSQL database by tuning its configuration for its workload versus using the default configuration from Amazon RDS.</p>
<p>The OtterTune ML algorithms make decisions based on their training data, and sometimes these decisions can be unpredictable because the algorithms do not interpret the world the same way humans do. For this reason, OtterTune has safeguards in place to ensure that ML does not harm your database.</p>
<p>We have learned a lot about how an ML-based DBMS tuner fits an organization’s established database administration policies and practices. These experiences have led us to develop operational settings that allow humans to control aspects of the automated tuning process. This article discusses which configuration knobs OtterTune can tune and the features available to users for managing this.</p>
<h2>Types of Knobs</h2>
<p>A DBMS has hundreds of configuration knobs that control aspects of its runtime operations. These knobs typically fall into one of the following categories:</p>
<ol>
<li>Resources</li>
<li>Policies</li>
<li>Locations</li>
</ol>
<h4>Resources</h4>
<p>Knobs in this category specify how much of a resource the system uses for a task. These can be either for fixed components (e.g., <a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS">number of vacuum workers</a>) or for dynamic activities (e.g., <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM">amount of memory to use per query</a>).</p>
<p>Examples of resource knobs for MySQL and PostgreSQL:</p>
<table>
<thead>
<tr>
<th>DBMS</th>
<th>Knob</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr>
<td>MySQL</td>
<td><a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size">innodb_buffer_pool_size</a> (fixed component)</td>
<td>The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data.</td>
</tr>
<tr>
<td>PostgreSQL</td>
<td><a href="https://www.postgresql.org/docs/13/runtime-config-resource.html">work_mem</a> (dynamic activity)</td>
<td>Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files.</td>
</tr>
</tbody>
</table>
<h4>Policies</h4>
<p>These configuration knobs control how the DBMS behaves for specific tasks. For example, a knob can control whether the DBMS flushes the write-ahead log to disk when a transaction commits.</p>
<p>Examples of policy knobs for MySQL and PostgreSQL:</p>
<table>
<thead>
<tr>
<th>DBMS</th>
<th>Knob</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr>
<td>MySQL</td>
<td><a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_method">innodb_flush_method</a></td>
<td>Defines the method used to flush data to InnoDB data files and log files, which can affect I/O throughput.</td>
</tr>
<tr>
<td>PostgreSQL</td>
<td><a href="https://www.postgresql.org/docs/13/runtime-config-wal.html">wal_compression</a></td>
<td>When this parameter is on, the PostgreSQL server compresses a full page image written to WAL.</td>
</tr>
</tbody>
</table>
<h4>Locations</h4>
<p>Lastly, location knobs specify where the DBMS finds resources that it needs (e.g., file paths) and how it interacts with the outside world (e.g., network port number).</p>
<p>Examples of location knobs for MySQL and PostgreSQL:</p>
<table>
<thead>
<tr>
<th>DBMS</th>
<th>Knob</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr>
<td>MySQL</td>
<td><a href="https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_port">port</a></td>
<td>The number of the port on which the server listens for TCP/IP connections.</td>
</tr>
<tr>
<td>PostgreSQL</td>
<td><a href="https://www.postgresql.org/docs/13/runtime-config-file-locations.html">config_file</a></td>
<td>Specifies the main server configuration file (customarily called postgresql.conf).</td>
</tr>
</tbody>
</table>
<h2>Paws off! Controlling Which Knobs OtterTune Configures</h2>
<p>We allow OtterTune to tune most knobs that control resources and policies. However, there are a few knobs that can have negative consequences like potentially causing the DBMS to lose data. We do not let OtterTune tune these knobs.</p>
<p>These restrictions are to avoid exogenous problems that are not readily measurable by the service. For example, the algorithms will learn that turning off disk writes for transaction commits makes the DBMS run faster. But now, if the system crashes, it might lose data because it no longer flushes data to the disk. The ML algorithm has no way of knowing whether that is a problem because that is an external cost that it cannot measure. A human has to make that value judgment. Thus, we disallow OtterTune from making those types of changes to a DBMS’s configuration.</p>
<p>OtterTune will not try to tune your port number or any other location knob for obvious reasons.</p>
<p>Examples of disallowed knobs for MySQL:</p>
<table>
<thead>
<tr>
<th>Knob</th>
<th>Why it's disallowed</th>
</tr>
</thead>
<tbody>
<tr>
<td><a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_doublewrite">innodb_doublewrite</a></td>
<td>Controls whether the doublewrite buffer is enabled. You might consider disabling the doublewrite buffer if you are more concerned with performance than data integrity, as may be the case when performing benchmarks, for example.</td>
</tr>
<tr>
<td><a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit">innodb_flush_at_trx_commit</a></td>
<td>Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose transactions in a crash.</td>
</tr>
</tbody>
</table>
<p>Examples of disallowed knobs for PostgreSQL:</p>
<table>
<thead>
<tr>
<th>Knob</th>
<th>Why it's disallowed</th>
</tr>
</thead>
<tbody>
<tr>
<td><a href="https://www.postgresql.org/docs/13/runtime-config-wal.html#GUC-FSYNC">fsync</a></td>
<td>If this parameter is on, the PostgreSQL server will try to make sure that updates are physically written to disk, by issuing fsync() system calls. While turning off fsync is often a performance benefit, this can result in unrecoverable data corruption in the event of a power failure or system crash.</td>
</tr>
<tr>
<td><a href="https://www.postgresql.org/docs/13/runtime-config-wal.html#GUC-FULL-PAGE-WRITES">full_page_writes</a></td>
<td>When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint. Turning this parameter off speeds normal operation, but might lead to either unrecoverable data corruption, or silent data corruption, after a system failure.</td>
</tr>
</tbody>
</table>
<h2>Knob value ranges are controlled too</h2>
<p><img src="//images.contentful.com/bhyalf3qnjwn/17QcOd9pzRsB4CRsdDz5Fd/352fa94654689e156c128c1c8b040abc/OtterTune-Allowed-Knob-Value-Ranges.png" alt="OtterTune Allowed Knob Value Ranges"></p>
<p>OtterTune also suggests a permitted range of values for each knob. This range is important for knobs that allocate system resources since setting a knob larger than what’s available can cause the database to crash. OtterTune collects hardware information from the user’s deployment and computes the suggested ranges based on the amount of a resource available.</p>
<p>OtterTune limits the range of some policy knobs as well. For example, many knobs that specify timeouts are safe to tune but only within a specific range.</p>
<p>Users can configure which knobs to tune and their ranges when connecting a database to the service for the first time. Once setup is complete, users can modify their settings from the options panel.</p>
<h2>Try OtterTune yourself</h2>
<p>If you'd like to learn more about how OtterTune automates database configuration tuning, watch this <a href="https://ottertune.com/blog/ottertune-explained-in-five-minutes">short video</a>.</p>
<p>If you'd like to see whether OtterTune can improve your database performance, let us know, and we'll get you started with OtterTune.</p>
<p><a href="https://ottertune.com/try"><strong>Try OtterTune...</strong></a></p>


How to Prevent Machine Learning From Wrecking Your Database

<p><a href="https://aws.amazon.com/rds/postgresql/">Amazon RDS PostgreSQL</a> makes deploying and operating a PostgreSQL database instance easy. When you provision an RDS PostgreSQL instance, Amazon launches it with their <a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.Parameters">default configuration settings</a> for dozens of tunable "knobs." These knob settings affect database performance, and the default settings are often not optimal for your database's specific workload.</p>
<p>Tuning the knob settings in order to optimize price-performance for your database requires:</p>
<ol>
<li>Specialized knowledge of PostgreSQL internals</li>
<li>Time to iteratively make changes and test the impact of the change on performance until it is optimized (i.e., trial-and-error) </li>
</ol>
<p>If you run Amazon RDS PostgreSQL and you lack (1) or (2) above, or if your workload changes over time (thus requiring re-tuning), you'll likely pay higher cloud costs than you ought to for your database.</p>
<h4>Putting Amazon RDS PostgreSQL knob settings to the test</h4>
<p>The question you’re probably asking is “how far off the mark could my Amazon RDS knob settings be?”</p>
<p>The question you’re probably not asking (yet) is “Can machine learning auto-tune my database knob settings to optimize its performance?”</p>
<p>To help answer those questions, we ran an experiment to test Amazon RDS PostgreSQL performance with three database configurations:
a)	Default Amazon RDS knob settings
b)  Knob setting recommendations from <a href="https://pgtune.leopard.in.ua/#/">PGTune</a>
c)  Knob setting recommendations from OtterTune's <a href="https://ottertune.com/features">machine learning algorithms</a></p>
<p>To run the test, we used the <a href="https://github.com/oltpbenchmark/oltpbench">OLTP-bench</a> framework to run the <a href="http://www.tpc.org/tpcc/">TPC-C benchmark</a>, a well-known transactional workload. We collected runtime metrics from the database, and calculated the throughput based on those metrics. Specifically, for Amazon RDS PostgreSQL, we optimized for the <a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW"><strong>xact_commit</strong></a> metric (the number of transaction commits in the database).</p>
<p>We ran the tests on an Amazon db.m5.4xlarge RDS instance equipped with:</p>
<ul>
<li>16vCPUs,</li>
<li>64GB RAM, and</li>
<li>4000 provisioned IOPs.</li>
</ul>
<p>For PGTune, we applied the configuration recommended by PGTune to the database:</p>
<pre><code>max_connections = 300
shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 13981kB
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4
</code></pre>
<h4>Result: OtterTune more than doubles performance (throughput)</h4>
<p><img src="//images.ctfassets.net/bhyalf3qnjwn/59VRT4Y7XhwYqGFTyhrluF/b0fae005e05c36c4a1f50f8fac0ad91f/ottertune-throughput-results.png" alt="ottertune-throughput-results"></p>
<p>In this test, the configuration changes that PGTune recommended increased throughput (transactions per second) by 41.9%.</p>
<p>OtterTune’s machine learning increased throughput over 3x more than PGTune...146.7% greater than the default RDS settings.</p>
<h4>Double the performance, or half the cost?</h4>
<p>In the previous test, we showed that using machine learning to find the optimal configuration knob settings more than doubled database performance. That’s a nice outcome if you’re seeking faster database performance.</p>
<p>For people who are satisfied with database performance, but would like to lower costs, knob-tuning can also help. To demonstrate that, we re-ran the test on a smaller RDS instance costing half as much, a db.m5.2xlarge equipped with:</p>
<ul>
<li>8vCPUs</li>
<li>32GB RAM</li>
<li>2000 provisioned IOPs</li>
</ul>
<p>Optimizing Amazon RDS PostgreSQL on the db.m5.2xlarge instance enabled it to achieve the same performance as PostgreSQL (with the default settings) on the twice-as-large db.m5.4xlarge, but at half the instance cost:</p>
<p><img src="//images.ctfassets.net/bhyalf3qnjwn/4DvKA6lO8vA656BHXhZdwM/da2ce6208a2e4f4406217f1c0a5b3bb5/ottertune-instance-cost.png" alt="ottertune-instance-cost"></p>
<h4>How long does it take OtterTune to find “optimal?”</h4>
<p>OtterTune works by iterating through different knob setting configurations and observing the database’s performance. Its machine learning models are trained to make educated decisions about new configuration changes for the observed workload and optimization goals. It’s similar to the approach an expert DBA would take, but it performs it faster.</p>
<p>The iterations required to find optimal performance in the second test are shown below. From the first iteration to the final iteration it took 2 hours.</p>
<p><img src="//images.ctfassets.net/bhyalf3qnjwn/4I3dVsgAW0RtQRkfgOmsGP/4cda3175418545cca0f0a16b7cff348f/ottertune-time-savings.png" alt="ottertune-time-savings"></p>
<p>In addition, OtterTune will continue to monitor the database workload and automatically adjust knob settings if the workload changes.</p>
<h4>Try it yourself</h4>
<p>If you'd like to learn more about how OtterTune automates database configuration tuning, watch <a href="https://ottertune.com/blog/ottertune-explained-in-five-minutes">this short video</a>.</p>
<p>If you'd like to see whether OtterTune can improve your database performance, let us know, and we'll get your started with OtterTune.</p>
<p>[<a href="https://ottertune.com/try">Try OtterTune</a>]</p>


Benchmark: Using Machine Learning to Optimize Amazon RDS PostgreSQL Performance

<h4>So many knobs, so little time</h4>
<p>Modern databases have hundreds of configuration “knobs” that affect the performance of the database. It’s becoming impractical for humans to know how to tune all these knobs for a single database let alone tens or hundreds of databases.</p>
<p><a href="https://ottertune.com/features">OtterTune</a> solves this problem by using machine learning to automate database configuration tuning, which results in better database price-performance.</p>
<h4>OtterTune automatic database tuning explained</h4>
<p>In this five-minute video, we explain how OtterTune works:</p>
<ol>
<li>Connecting OtterTune to your database - no special software or access to user data is required.</li>
<li>Monitoring your database’s performance and collecting settings and metrics.</li>
<li>Training machine learning models to predict how your database’s performance will change as its configuration knobs change.</li>
<li>Automatically installing the new configuration and monitoring its effect.</li>
<li>Continually monitoring your database to make sure that it is always using the most optimal configuration.</li>
</ol>
<div class="max-w-2xl">
      <div class="aspect-w-16 aspect-h-9">
        <iframe src="https://www.youtube.com/embed/SJdnpAMI_q4" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
      </div>
    </div>
<h4>Watching OtterTune tune an Amazon RDS PostgreSQL database</h4>
<p>The video also walks you through a demonstration in which OtterTune tunes an Amazon RDS PostgreSQL database configured with the default RDS settings and running a write-heavy transactional (TPC-C) workload.</p>
<p>After several minutes you’ll see that OtterTune has generated the first new configuration and installed it in the database. After several more iterations you’ll see that the OtterTune-optimized database achieves 87% higher throughput than the original configuration.</p>
<h4>Can OtterTune improve your DBMS performance? Try it out!</h4>
<p>If you’d like to try OtterTune on your database(s), <a href="https://ottertune.com/try">let us know</a>, and we’ll help you get started.</p>


Watch: OtterTune Explained in 5 Minutes...

<h4>What is OtterTune?</h4>
<p>OtterTune is automatic database tuning software. It uses machine learning to automatically tune the configuration knobs of your database to achieve the best performance possible for its current workload. OtterTune can keep your database tuned as the workload changes over time. The goal of the service is to reduce your operating costs and make life easier for DBAs.</p>
<h4>What is a configuration knob?</h4>
<p>A “knob” is a configuration parameter that a database exposes that can control some aspect of its runtime behavior, such as buffer pool sizes, caching policies, and other features. Most databases have lots of knobs. There are over <a href="https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html">500 knobs in MySQL</a> and <a href="https://www.postgresql.org/docs/current/runtime-config.html">300 knobs in PostgreSQL</a>.</p>
<h4>What data does OtterTune analyze?</h4>
<p>OtterTune collects configuration knobs (e.g., the size of the buffer pool or redo log) and runtime metrics (e.g., the number of transactions executed or dirty pages in the buffer pool) from the target database, nothing else. It does not access any sensitive application or user data.</p>
<h4>What databases does OtterTune support today?</h4>
<p>As of May 2021, OtterTune supports the following databases running on-premises or via Amazon RDS:</p>
<ul>
<li>PostgreSQL (versions 9.6, 10, 11, 12, 13)</li>
<li>MySQL (versions 5.7 and 8.0)</li>
</ul>
<h4>What databases will OtterTune support next?</h4>
<p>As of May 2021, OtterTune is adding support for MariaDB and Amazon Aurora (MySQL and PostgreSQL). If you have a database or platform you’d like OtterTune to support or want an update on our supported DBMS list, please contact us.</p>
<h4>How long does it take to optimize a database?</h4>
<p>Many factors will affect how long it takes for OtterTune to generate a better configuration for your database. Some of these factors include:</p>
<ul>
<li>How many knobs is the service tuning?</li>
<li>How complex is the workload?</li>
<li>How often does the service try a new configuration?</li>
<li>How many databases that OtterTune has tuned in the past that are similar to the new database?</li>
</ul>
<p>Database tuning is a complex problem. With that said, in practice, we have found that OtterTune produces some initial improvement within hours.</p>
<h4>Does OtterTune tune queries or indexes?</h4>
<p>As of May 2021, OtterTune only analyzes and adjusts database configuration settings. It does not analyze or recommend changes to queries, indexes, or database design.</p>
<h4>Does OtterTune plan to automate other DBMS operations in the future?</h4>
<p>OtterTune was founded by researchers at <a href="https://db.cs.cmu.edu/">Carnegie Mellon University</a> working on autonomous and self-driving databases. We have spent over a decade working on DBMS automation, so yes, we plan to extend OtterTune to support automating more DBMS operations over time.</p>
<h4>Does OtterTune restart my database to implement config changes?</h4>
<p>Some knob settings require a restart to take effect. OtterTune will only tune knobs that require a restart if you allow it to. Otherwise, OtterTune will only adjust “dynamic” knobs that take effect without restarting the database.</p>
<p>Note that some of the knobs that require a restart are often important to tune. That is, they have a significant impact on performance when tuned correctly for some applications. Given this, you can control OtterTune’s restart policy, setting it to restart the database any time, or only during specific time windows, or never (it will just advise you to restart manually).</p>
<h4>Does OtterTune require me to install software or make changes to my application?</h4>
<p>Nope. OtterTune runs on its own as a separate service and connects and interfaces with the target database to collect metrics and change settings via standard SQL. You do not need to install any additional third-party extensions in your database.</p>
<h4>What access privileges does OtterTune require?</h4>
<p>The privileges that OtterTune needs depend on the database system, service provider, and tuning options. OtterTune requires user credentials that it uses to connect to the database. This user only has permissions to retrieve metrics and not any sensitive user data. For Amazon RDS, additional information, such as the database identifier, region, and login permissions, is required. OtterTune can be configured to either tune or only monitor your database. If you enable tuning, you can also decide whether to allow OtterTune to restart the database and provide OtterTune with the correct permissions.</p>
<h4>How much does OtterTune cost?</h4>
<p>There’s a setup fee to get started with OtterTune. If you run OtterTune 24x7 to keep your database optimized continuously, there’s a small monthly fee. Please contact us if you need more pricing details for your situation.</p>
<h4>How do I get started?</h4>
<p>Press the “Try OtterTune” button. After that, we’ll have a quick chat with you to help you pick a good first database to optimize with OtterTune and get you set up and running.</p>
<h4>What’s with the otters?</h4>
<p>Well, we auto-tune databases. So, auto-tune...OtterTune. Get it? Plus, otters are cute. But sometimes aggressive, which should only worry you if you’re a seal, sea lion, or beaver.</p>


OtterTune Frequently Asked Questions

<p>Yo. Today is a banner day that follows years of research on autonomous database technology and machine learning at <a href="https://db.cs.cmu.edu">Carnegie Mellon University</a> (CMU) and lessons learned from customer deployments. We’re <a href="https://www.prnewswire.com/news-releases/ottertune-launches-to-improve-database-performance-through-automated-database-configuration-tuning-301289311.html?tc=eml_cleartime">happy to announce</a> the first commercial release of <a href="https://ottertune.com/features">OtterTune</a> software-as-a-service (SaaS) and seed funding From <a href="https://accel.com/">Accel</a>. Our mission is to make databases run better, with less effort, and at a lower cost.</p>
<p>With hundreds of configuration settings (“knobs”), the work required to keep a database tuned, or more often, hundreds of databases, exceeds human abilities. Based on our research at CMU, we knew that, with the help of machine learning (ML), we could achieve better results with an automated tuning service that would learn from each database and become more efficient over time.</p>
<p>Here’s a little bit of our history to help you learn how we got here today and where we’re going next.</p>
<h3>University Research</h3>

<p>OtterTune started in 2014 as a research project in CMU’s Computer Science Department led by OtterTune co-founders <a href="https://www.cs.cmu.edu/~pavlo">Andy Pavlo</a> and <a href="https://www.cs.cmu.edu/~dvanaken/">Dana Van Aken</a>. We were studying how to use ML to devise strategies to optimize DBMS knobs for any application automatically. We published the <a href="https://db.cs.cmu.edu/papers/2017/p1009-van-aken.pdf">first academic paper on OtterTune</a> at the SIGMOD conference in 2017 and presented it in Chicago.</p>
<p>With the development help of co-founder <a href="https://bohanjason.github.io">Bohan Zhang</a> (first as a summer intern from PKU, then later a CMU MS student), we built the first versions of the OtterTune database tuning service, which uses ML to generate and install optimized DBMS configurations. OtterTune observes the DBMS’s workload through its metrics and then trains recommendation models that select better knob values. It then reuses these models to tune other DBMSs faster.</p>
<h3>Our First Customers</h3>
<p>The first real-world application of OtterTune in a production environment was at <a href="https://www.societegenerale.com/en">Société Générale</a> (SG). SG approached us to conduct a field study involving their fleet of Postgres databases. The first thing we discovered was that they had an Oracle database that was more problematic, so that became our first project. At that time, OtterTune had been built to work with MySQL and PostgreSQL. But we extended it to support Oracle and discovered that our ML models worked on Oracle well enough--recommending a config that increased throughput by 45% and saved them the not-insignificant cost of several Oracle core licenses.</p>
<p>[<a href="https://db.cs.cmu.edu/papers/2021/p1241-aken.pdf">Read the research paper we published with Société Générale</a>]</p>
<p>We also worked with another customer and their large cluster of MySQL databases. OtterTune helped them reduce their CPU utilization and the number of running threads by more than 20%. At their scale, this reduction in utilization resulted in the ability to turn off multiple machines and save tons in server costs.</p>
<p>Besides refining our ML models with these experiences, we learned a lot about fitting in with database administration tasks and policies. This effort led to the development of new operational settings in the OtterTune service, which allow humans to control when and how frequently OtterTune runs, if and when it can restart databases with new configs, which knobs are off-limits and allowed value ranges.</p>
<h3>A Group of Otters is Called a "Raft"</h3>
<p>We are not doing this alone. Our raft is impressive. A few years ago, Amazon invited us to publish a <a href="https://aws.amazon.com/blogs/machine-learning/tuning-your-dbms-automatically-with-machine-learning/">blog post</a> about OtterTune. That caught peoples’ attention, including early investors with valuable experience launching data management companies like Databricks, Snowflake, Cockroach Labs, SingleStore, Vertica, PingCap, and others. Eventually, we teamed up with Accel to fund the launch of OtterTune and our next phase of growth and development. Our friends at Accel (<a href="https://accel.com/people/ping-li">Ping Li</a> and <a href="https://www.accel.com/people/daniel-levine">Daniel Levine</a>) are guiding us through the early stages of our company and helping us realize our big ideas. Daniel notes that purple sea urchins are an invasive species that otters eat, and that helps keep kelp forests healthy. Needless to say, Accel gets otters.</p>
<h3>Join us...the water is fine, and the fish are tasty</h3>
<p>If you’ve got databases, we’ve got a way to keep them tuned and always optimized for you. We work with PostgreSQL, MySQL running in the cloud (Amazon RDS), or self-managed instances. The more databases OtterTune works with, the smarter it gets, so let us know if you'd like to <a href="https://ottertune.com/try">https://ottertune.com/try</a></p>
<p>You’ll also probably also learn more than you ever wanted to know about otters. Spoiler: Otters are vicious animals.</p>

Database optimization. On autopilot.

Continually improve your database
performance, automatically.

Let's face it: Manual database optimization is a pain

Three steps to better database performance:

1Connect

2Collect

3Optimize

OtterTune supports the most popular databases and cloud platforms

Why OtterTune?

How you benefit from automatic database tuning

Continuous improvement

Continuous monitoring

Data security

Versatility

Knowledge center

Automatic Database Tuning Tips: Handling Restarts

OtterTune Oct 2021 Product Update: Bring Da Ruckus

You are Overpaying Jeff Bezos for Your Databases (and the things he does with that extra money)

Benchmark: Using Machine Learning to Optimize Amazon RDS MySQL Performance

Ready to put your database optimization on autopilot?

Database optimization. On autopilot.

Continually improve your database performance, automatically.

Let's face it: Manual database optimization is a pain

Three steps to better database performance:

1Connect

2Collect

3Optimize

OtterTune supports the most popular databases and cloud platforms

Why OtterTune?

How you benefit from automatic database tuning

Continuous improvement

Continuous monitoring

Data security

Versatility

Knowledge center

Automatic Database Tuning Tips: Handling Restarts

OtterTune Oct 2021 Product Update: Bring Da Ruckus

You are Overpaying Jeff Bezos for Your Databases (and the things he does with that extra money)

Benchmark: Using Machine Learning to Optimize Amazon RDS MySQL Performance

Ready to put your database optimization on autopilot?

Continually improve your database
performance, automatically.