As well adding new features, our goal in the server team is to make MySQL easier to use. We try to design features with the goal of making a net improvement on usability. However, the actual definition of usable server software is not so straight-forward. I aim to describe our current interpretation in this post đ
Introduction
A simple definition of usability could be to use the KISS principal. Simple is better; and one should eliminate unnecessary complexity.
What counts as unnecessary has evolved over time with changes to user expectations. As two simple examples of this, user demand for features such as built-in high availability and data security have increased. They are very much necessary features by most user expectations. Â We recently decided to expand beyond KISS, and enumerate our principles in a more concrete list.
Server Usability Principles
1. Use SQL
We should not require users to have local shell access to perform administrative tasks with MySQL. Frequently in cloud (or corporate) environments DBAs will not have a local login to a server, and command usage may differ across platforms.
“Use SQL” means that we will strive to make all functions of administration possible via the client/server protocol using familiar SQL-like syntax.
2. Discoverability
When faced with a problem, a user should intuitively have context on how to use a feature or resolve an issue. Reading the reference manual cover-to-cover should not be a prerequisite to using MySQL. Discoverability is best explained with an example error message:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column âtest.t1.tâ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Here we can see that this statement failed because of a setting called sql_mode=only_full_group_by. As a user, I can paste this part of my error message into Google, and find a number of helpful articles describing why my statement caused an error.
Discoverability can apply to error messages, configuration variable names, configuration variable values, and features themselves. To use an example feature: we acknowledge that “utf8mb4” is not discoverable (it is easy to pick âutf8â which means utf8mb3). We are addressing this in MySQL 8.0 by switching the default and deprecating support for utf8mb3.
3. Less is more
Having too many similar options without clearly differentiate use cases can have a paralyzing effect on users. For example, there are little benefits to switching the default InnoDB File Format, but a user may be compelled to research to make sure they have selected the correct option.
It can be tempting to create new configuration options as part of feature design, rather than rationalize the better choice as it applies to most users. This is an anti-pattern, because it pushes the decisions from those two are empowered with information (the developers) to those who are not (the end user).
Too much flexibility also has an impact on documentation, where we always describe all features and options. Having aliases for configuration options (or many ways to achieve the same result) reduces documentation readability.
Less is more is about reducing friction for users, so that being an expert is not required, and they focus on what is important to them.
This principle was inspired by the following:
4. Observability
Any configuration option(s) added should be supported by appropriate instrumentation to be able to tell if they are configured correctly.
For example, if we were to add a new configuration called innodb_worker_threads, there should be the ability to see the utilization of each of the threads so we know when to scale up or down the thread count.
On running tasks, it should also be possible to see the current status, the progress or percentage complete, and the ability to estimate final completion.
For the sake of less is more, we will standardize our observability interfaces to information_schema, performance_schema and SYS. Where by:
- Information_schema is a set of views for static data, such as table definitions
- Performance_schema contains runtime/quickly changing data
- SYS is a set of âtask oriented viewsâ intended for use by a DBA, and sits on top of information_schema and performance_schema.
5. Orthogonality
To be orthogonal means that a feature should work the same way in all contexts. For example, if there is a CREATE and DROP command, there should be a corresponding ALTER command.
This principle works in partnership with discoverability, in that a user can get to work without having to be an expert.
6. Idempotency
To be idempotent means that a particular action can be performed more than once, while always yielding the same result. It is a principle of many configuration management tools, and operations practices.
Features in MySQL should always support a way for user scripts to execute them in an idempotent way, for example the syntax INSERT … ON DUPLICATE KEY UPDATE.
While this may seem like a very specific detail, it is core to understanding how scripts must work in a distributed environment. If MySQL were to implement a new administration command that was not idempotent, it would make it hard for scripts to resume after an error, or avoid double processing of a particular step.
7. Driven by Use Cases
We strive to follow the SQL standard(s) when designing features, as it provides easy discovery to our users. Having said that, we will also seek meaningful ways to extend functionality in ways that match the most common use cases of our users.
A simple example of this is if a hypothetical feature supported the syntax IMPORT x FROM y, we would also add the syntax for IMPORT ALL FROM y. In MySQL 5.7, we added the -> and ->> shorthand JSON operators to support the most common functions when accessing JSON documents.
This principle can occasionally conflict with the principle of less is more. We will balance the strength of the use case with the impact it adds to our documentation.
8. Preserve Upgrade Story
SQL can be seen as a declarative API for accessing your data. But it is far more expressive than most APIs, even to the point where you can write queries that were possibly not intended at the time a feature was written.
There will be cases where we will need to mark syntax (and features) as deprecated and removed, as we evolve MySQL and reduce our maintenance burden. We will observe the following principles when doing so:
- It is okay to deprecate or remove functionality.
- It is undesirable to redefine existing functionality.
This makes it much harder to use new and old versions at same time - It is sometimes better to cause a hard error, then a subtle change in functionality that could go undetected.
Preserving the upgrade story must be balanced with the principle âless is moreâ. We must still document deprecated features, so they have an impact on the user manual readability right up until the point they are removed.
9. Safe by default
Optimizations which result in potential data loss or loss of visibility should be opt-in versus opt-out. This is a principle which has evolved over MySQLâs history; with InnoDB and strict mode now being the default.
We include instrumentation in the definition of safe since flying blind prevents observing inefficiencies. Leaving key instruments off also adds too much risk when diagnosing problems, since any overhead should have been accounted for in provisioning.
10. Secure by default
This principle can often appear contradictory to usability, in that sometimes secure practices get in the way of users (for example: MySQL 5.7 generates a random password by default).
On the contrary, we believe that every user has an expectation that their data be secure. If the system is easy to use, but requires an expert set of skills to secure then it fails at usability. By being secure by default, we are lowering the barrier to entry for using best practices.
Conclusion
If you managed to make it to the end of this long post: Thank you!  We would also like to thank our MySQL ACEs Bill Karwin, Ronald Bradford, Rick James, Shlomi Noach for providing feedback.
By taking the time to write down our principles, we now have a criteria by which to evaluate a new feature as usable or needing improvement.  I have already found myself using the word discoverable in conversations đ