Continuous improvement

Continuous monitoring

Data security

Versatility

Bohan Zhang

<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="/assets/content/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="/assets/content/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="/assets/content/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

Andy Pavlo

<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...

Dana Van Aken

<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

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

Ready to put your database optimization on autopilot?

Continually improve your database
performance, automatically.