[MUSIC]
>> Hi. My name is Dana Van Aken,
and today I'm going to discuss why
Postgres is better than MySQL for Automated Knob Tuning.
I am the co-founder and CTO of OtterTune,
which is a database tuning startup.
Prior to this, I was a Ph.D. student at Carnegie Mellon.
I graduated in February 2021,
and during that time I worked on knob tuning,
and I also worked that entire time on Postgres as well.
In total, I've been working on
automatic knob tuning and database tuning for about eight years.
The agenda for today is, first,
we're just going to go over some background information
about knob tuning in general
and the problem that OtterTune solves.
Then we'll jump in to three reasons why
Postgres is better than MySQL for auto tuning,
and finally, we'll wrap up and summarize.
Databases have hundreds of knobs
that affect DBMS performance and cost.
It's really critical that you tune these knobs for
both the applications workload and the hardware,
because the performance difference can be up to 3x.
Managing all of these knobs is becoming
more and more difficult and it's
surpassing the abilities of humans.
Part of this has to do with
vendors adding more and more knobs with each release.
If you look at the graph that we're providing,
you can see that MySQL,
the number of knobs has increased since
around the year 2000 by 7x.
For Postgres, this number is 5x. Postgres
is slowly approaching almost 400 knobs now.
This is a problem for both on premise and cloud databases.
A lot of cloud workloads are over-provisioned,
just because they don't have
the expertise needed to actually do any performance tuning.
This is where OtterTune comes in.
Again OtterTune is
an automated database tuning and resource optimization service,
so OtterTune uses machine learning to
automatically configure a database's knobs,
to improve performance and also help you
save on hardware, software costs.
OtterTune is based on my research and
also the research of our CEO and my former advisor,
Andy Pavlo, that we did at Carnegie Mellon University.
Currently OtterTune supports both Postgres and MySQL,
but as of right now,
it's only available for AWS databases.
Now, I quickly just go over and
walk through the architecture of OtterTune.
The first step is the user is going to
provide just the minimal credentials
that they need to access the database.
Next, we deploy an agent in
the user's environment that can connect to the target database,
and once it connects,
it's going to retrieve
the current configuration and also
the database's internal run time metrics.
Then it returns this information
back to the the OtterTune service.
We store this in the database and then the data gets passed to
the compute engine where we train
ML models to predict the databases behavior.
The compute engine outputs
a new configuration that's expected
to improve the performance for the database.
This gets applied to the database in the final step.
Then this process continues
until the user is satisfied with
the performance improvement over the original configuration.
Now we can hop right into
three reasons that Postgres is better for knob tuning.
The reasons I'm going to bring up
are specific to the latest versions of Postgres and MySQL,
so the Postgres version 14 and MySQL version 8.
One reason that I really appreciate Postgres is because it
exposes all important knob information and its system catalog views.
The main table that holds this critical knob information
that OtterTune needs is called pg_settings.
In addition to the name and value,
it provides a lot of other useful information
about Postgres knobs.
Again, OtterTune uses
this information in various parts of the system.
The one piece of information it uses is the knob unit,
and this is Postgres specific.
It's the implicit unit for memory and time knobs,
and so in Ottertune
we store the implicit value of memory and time knobs,
and we need this unit to be able to convert,
for example, for user requests
to make sure we can display human readable form.
Another piece of information is the variable type.
We use this for parsing the metric information as well as
transforming the metric data for the machine learning components.
Another very important piece for most knobs are the min and
max values for numeric knobs and also the enumvals.
This is the valid range or valid values for these knobs.
We can use this information.
We need this information to be able to
bound OtterTune's machine learning models,
to be able to bound the search space that they consider.
Although, note that for resource knobs
their max value is not realistic.
It's just set to some extremely high number,
and so for those knobs,
the bounds are based on the hardware.
Finally, we need to know the context.
The context determines what is needed
to apply a configuration setting to the database.
Dynamic knobs get applied immediately,
but some knobs require a database restart.
We need to know this information
in the agent when it goes to apply the configuration.
An example of the knob settings is shown on the slide.
>> What about MySQL?
Well, prior to MySQL 8,
the only information you could get about knobs was
just the knob name and the current value.
Now, as of MySQL 8,
they added a new table called Variables Info that does
now contain the minimum and maximum values for numeric knobs.
It also contains some other information
but not all of it is relevant.
The problem is it does not
provide really important information like
the variable type or the EnumValues or the context.
The only way to get this from MySQL is through the documentation.
It's just much easier to be able to just quickly
collect this from a table instead of
having to go through the documentation
or try and scrape documentation.
I really appreciate Postgres just provides this so quickly.
The next reason that I think Postgres is really beneficial for
knob tuning is because they provide
great flexibility for changing knob values.
Both Postgres and MySQL support
changing knob settings at different scopes,
so both of them allow you to change scopes at the global level.
This means that settings
get applied to the entire database system.
Postgres allows you to also change
dynamic knob settings, so it doesn't require
restarts, for the database session,
so the connected session and then also at the user level.
MySQL does not provide
support for changing them at the database or user level.
This is important because there's certain knobs,
you can see the example I've posted here.
One of these knobs is work_mem. This is a knob that you
generally do not want to set very high globally.
But there are cases if you're doing
a lot of the sorting operations
where it's really beneficial and
improves performance to be able to do this.
In the example, you can see
the global value for the knob values is four megabytes,
but we can set it specifically for the user much larger
if they have some analytical workflow. So that's cool.
But what's even cooler is that,
what if you have one database where you're really processing,
you really need a high work_mem setting.
You can actually set this value for
both the user and the database.
This is really convenient because it gives you the flexibility of
having a user where you can
specify this value on a per database basis.
The final reason and
perhaps the most exciting feature that I
really enjoy from Postgres is that it has
a very awesome extensions and they're very powerful.
Extensions were implemented in Postgres back in 2011.
This was in version 9.1.
The goal was to make it easier to
package new additions to Postgres.
But what makes these really powerful is they have
these really low-level hooks that let you
modify and even override completely Postgres's core components.
The benefit of doing this override,
forking in the source code is the amount
of maintenance or the effort
that it takes to maintain compatibility with
new Postgres releases is greatly decreased.
Just to demonstrate how powerful this is,
you can see people often refer to
TimescaleDB and Citus Data which take
Postgres and repurpose it to a
be different database and solve a different problem.
Citus Data database transforms Postgres into
distributed version and TimescaleDB offers a time series Postgres.
Another thing that's really
cool about Postgres extensions is they have
this PGXN distribution manager and it's
been really helpful for improving community contributions
because packaging and releasing and building extensions,
it really simplifies this process.
The only requirement for being able to use this tool is that
it's an open-source extension and there's currently
340 extensions that it offers.
What about MySQL?
MySQL introduced a similar concept of plugins in version 5.1.
One of the great things about Postgres is it was
built with modularity and extensibility in mind from day one,
so supporting extensions they can do it very well.
Whereas MySQL, this was not the case,
they just weren't as useful,
they were more cumbersome,
and they never really saw any strong adoption.
But then in MySQL 8,
it's very exciting because they have now
introduced a new component based architecture
with the goal of improving modularity and
improving extensibility for users.
They just started re-implementing
features in the system for this new architecture.
In MySQL 8, they replaced two existing features,
which was the error logging and also the validate password plugin.
This is really exciting,
although it's going to take some time
for them to completely re-implement
their system and it will also take time to
get user adoption and Postgres is
definitely ahead in that front,
but it will be interesting to see how it goes going forward.
How do extensions benefit database tuning?
There's a couple of ways and I've given a few examples here.
The first is that it's a lot easier
for database tuning services to support
extensions that are databases offered as extensions,
like TimescaleDB and Citus Data.
These are much easier to support than
completely new databases and database vendors.
This is because a lot of
the effort that takes us with them is just
providing support for collecting and processing
the knobs and metrics that they expose.
The case of Citus Data,
they have about 40 knobs and
four informational views and for a TimescaleDB,
they have about 60 knobs,
10 informational views,
you would want to collect this information
because it's going to be a useful signal for OtterTune.
That's where a lot of the effort comes in.
Then the other nice thing is,
once you support an extension,
it's easy to support it for all users.
Basically, you can see the user has
a given extension enabled and if they do,
then you know that you should collect this information.
Another reason that extensions
benefit database tuning is because they
often provide functionality or expose
statistics that are not available on standard Postgres.
For example, OtterTune uses
the very popular pg_stat_statements extension
to calculate the query latency.
This is really nice because this is a target objective or
a performance metric that a lot
of our users care to optimize for,
so it enables us to do that.
Another good example is there's
automatic indexer named Dexter that can
automatically select the best indexes for a database.
Dexter relies on an extension called
HypoPG that implements hypothetical indexes.
These are virtual indexes that don't actually exist
but are able to
tell you how useful adding a particular index would be.
All right, that brings us to the end,
and in summary, Postgres is a very feature-rich database.
Some of the features are particularly useful for
Automated Knob Tuning services like OtterTune.
We went over a few examples.
First, Postgres provides really easy way
to grab knob info from
the system without having to dig through the documentation.
It also provides you more flexibility in
setting knobs and that leads to more tuning opportunities.
Third, supporting extensions
for other databases is
much less work than having to support different vendors
and extensions also can provide information that's not immediately
available on Postgres that a tuning system might
need to improve their capabilities.
With that, that brings us to the end of this talk.
Thank you very much for listening.
I hope you have a great time at Citus Con.