OtterTune intern showcase 2022

Otters are social animals. Otters don’t like to be alone – and this applies to river otters, sea otters, and to all of us otters at OtterTune. So we love having summer interns hang out with us. This year we were excited to have two plucky students join us: Priyank Bhandia from Carnegie Mellon University and Prateeksha Singh from the University of Buffalo.

We aim for the interns to work on parts of our automated database optimization service that will have a real impact. They build features, write tests, and ship code, acting as full-time engineers. To achieve this, we assign one engineering team member to mentor each intern during the summer. This involves daily meetings to discuss their progress, provide feedback, and answer questions. Given our academic roots at Carnegie Mellon, we know how important it is to give students exposure to real-world software systems to both bolster and flex what they learn in the classroom.

Our 2021 internship program was a success. Both of our interns, Haonan Wang and Sylvia Zhang – CMU students at the time of their internships – joined OtterTune full-time when they graduated.

So we close this summer out by showcasing Priyank’s and Prateeksha’s internship projects.

PostgreSQL autovacuum monitoring and health checks

Intern: Priyank Bhandia

Any DBA knows the importance of autovacuum in Postgres. Without it, your database will have a lot of dead tuples (a.k.a. useless information), wasting precious space and disk cycles, and causing poor query performance.

The problem is that there’s a fair amount that can go wrong while Postgres’ autovacuum is scanning and cleaning up tables. Query performance can suffer while the autovacuum workers are busily reading and writing data from disk. And when there is contention for resources, the autovacuum interrupts itself to back off.

This means that a long-running query that stops the vacuuming process for a long time causes dead tuples to pile up. In turn, future vacuum invocations take longer to run. Long-running vacuums affect query performance. And the database user can sit there wasting time manually checking in on autovacuum to see if it’s getting the job done.

So, Priyank set out to create a dashboard tool to monitor and check how autovacuum is doing automatically. OtterTune will soon have monitoring and health checks for autovacuum built in, thanks to his work. OtterTune users will have an at-a-glance view when things go wrong in the vacuuming process and a table view showing all the information about the autovacuum’s progress. In terms of health checks, you’ll be able to check in on problems such as:

• See if an autovacuum process has been blocked for a long time by a competing query

• Find out if the autovacuum process does not have enough buffer space, causing it to repeat the full scan too many times and waste resources

To make this work, the OtterTune Agent retrieves autovacuum information from your database, runs it through OtterTune, and presents it to you. This will give you more time to groom your database and keep it healthy – which is what our other Otter intern, Prateeksha Singh, worked on over the summer.

Database Health Check improvements

Intern: Prateeksha Singh

OtterTune Database Health is extremely popular among Otters. It does the background checking for you, so you don’t get surprised by outages or performance drops. OtterTune monitors your database and collects metrics about your workload. It informs you if your database shows unhealthy behavior and recommends corrective actions.

With Database Health your databases run smoothly, stay available, and avoid slow performance. This way, management (a.k.a. “the man”) stays off your back. For her project, Prateeksha chose Snowflake as the initial platform to build the data pipeline and create new features. However, the upgrades to Database Health Checks that she developed will apply to all databases OtterTune supports.

You Otter know things like: “What are the top few checks that are failing? The top few databases that are failing? The top few checks that are failing for the top few databases that are failing?” Soon you will be able to know all this and more, much more easily. And you will have dashboards so you can get answers without searching for them.

OtterTune’s new health check capabilities don’t just provide values for things like resource utilization; they let you see, under just one tab, whether the value is healthy or not. You also get a history section that shows the status of every check performed over a certain period, as well as trends and threshold analysis.

Data can be sliced and diced in multiple ways, such as seeing how many health checks were done for memory utilization or connection counts over time and which were healthy or unhealthy. You can define your own filters to get very granular, such as determining which particular data fails the most in your top failing databases. Or, you could see how long a specific health check has been falling. Useful, right?

Join the team!

If you’d like to join the OtterTune team, whether as an intern or as a full-time developer, please visit our careers page to start the process. We’re a distributed team, and we look forward to hearing from you.