Nov 11, 2021
OtterTune intern showcase 2021
OtterTune Team
This summer OtterTune welcomed its first interns to the team. We took on two interns from Carnegie Mellon University:
We kicked things off in late May with a team go-karting event in Pittsburgh. When it came time to get to work, we assigned each intern to a mentor who met with them daily to discuss progress, provide feedback, and answer questions. We closed the summer with a showcase that gave them the chance to highlight their work.
Here’s what they built.
Evaluation and feedback of customers’ database environments
Customers always ask us how much benefit they will get from OtterTune before signing up. This is tough to predict because it depends on many factors, such as workload patterns, data sizes, DBMS version, and hardware configurations. These factors are the reason why database tuning is a difficult problem and why an automated solution like OtterTune that uses machine learning to figure it out for you is the right approach.
This year is the first year that we asked two interns to help us figure out how to better predict how our customers will benefit from using OtterTune. Sylvia Zhang and Haonan Wang — now both OtterTune employees — made significant strides in helping us learn how to predict what influences OtterTune configuration improvements.
Database Checkup Tool
Sylvia Zhang’s project introduced OtterTune to prospective customers by giving them an evaluation of their database environment and feedback on their database metadata. Her work generated a heuristic-based workload summary and pointed out potential improvements. Ideally, her work will lead to an adjunct product to OtterTune that would generate performance forecasts based on data supplied by the customer.
For her project, she created a command-line interface that collects statistics from a customer database and dumps them into a local file for the customer to inspect and upload. The front-end of her system makes available for download compiled binaries of the command line interface and enables the customer to enter their contact information and upload the database statistics.
On the backend, the system she created packs the customer data and puts the data entry into storage. Once the data storage change occurs, it triggers a data analysis pipeline that feeds the stored data into algorithms that generate a workload summary, heuristics (read-write ratio, cache-hit ratio, and so on), and performance forecast and sends the feedback to the customer’s email. Although there are several enhancements to be made, the system holds promise as a useful tool for customers needing to evaluate their database environment, analyze feedback, and determine potential improvements.
Detecting workload and other fluctuations to maximize tuning success
Haonan Wang’s project involved building a model to detect when a workload has changed significantly so that customers can be alerted and restart the tuning process. He used several tools, including Facebook Kats, to detect change points in clients’ workloads and then validated them using OLTP benchmark results. First, he used CUSUM to detect an up/down shift in a time series. To locate the change point, he used an interactive process, initializing a change point in the middle of a time series and the CUSUM of time series based on this change point. Then, he iteratively used the point that maximizes the CUSUM of time series as the new change point until finding a stable one. He then tested the statistical significance of the change points and used magnitude comparison to reduce the influence of seasonality with a short period of historical data as reference.
He used OLTP benchmarks and YCSB datasets for validation. The change point detection was validated across three types of changes, using CPU ratio metrics:
Query complexity changes
Read-heavy becoming Read/Write
Throughput changes
He found that the changepoint detector can successfully detect the changes on these three workloads and is robust to noise at the same time, meaning that the detector does not overreact to temporary variations and can capture real workload changes. For each change point detected, the detector also produces a confidence score that can help determine if the workload has changed enough to recommend re-tuning. By finding the workload change, the OtterTune product will be able to restart the tuning process and recommend new configuration.
He discovered insights that could become a potential OtterTune product that would help optimize recommended configuration improvements, despite shifts in factors such as throughput and query complexity.
Join the team!
If you’d like to join the OtterTune team, as a full-time developer, or as an intern, please visit our careers page to start the process. We’re a distributed team, and we look forward to hearing from you.
