Configuring DBMS “Knobs”: 6 Ways to Avoid Surprises

Tuning DBMS configuration is an essential aspect of any data-intensive application effort. But it is historically difficult because DBMSs have hundreds of configuration “handles” that control everything in the system, such as how much memory is used in caches and how often the DBMS writes data to storage.

The problem with configuration handles is that they are not uniform (eg two DBMS use a different name for the same handle), not independent (eg changing one handle can affect others), and not generic (what works for one application might be suboptimal for another). Even worse, information about the effects of knobs comes only from expensive and time-consuming trial-and-error experiments.

Another significant issue is that because there are hundreds of handles per DBMS, it is beyond the ability and logic of DBAs to adjust them all for each application. DBMS tuning guides strongly suggest that the DBA change only one knob at a time. This tip is wise but it is too slow for a DBMS with a large number of tunable handles. It is also not entirely beneficial because changing one handle may affect the benefits of the other. It is difficult for humans to understand the effect of a single handle, let alone the interactions between several handles. This is why academia and industry put a lot of effort into finding ways to automate database configuration tuning.

6 things you should know to avoid surprises

Since tuning database config knobs is so complex, the saying “you don’t know what you don’t know” applies to spades—the entire tuning process can be full of surprises. Here are some things to know and the surprises that will appear when you start tuning any DBMS.

  • Synthesis is not a one-time activity. Non-stop tuning is essential because databases are constantly changing: data volumes, device capacity, new queries, demanding applications, and evolving workloads. That is why it is always necessary to fine-tune your database and automate the process as much as possible.
  • Never rely on default settings. Part of what makes DBMS so mysterious is that their performance and scalability are highly dependent on their configurations. This problem is further exacerbated by the notoriously infamous default off-the-shelf configurations for DBMS handles. DBMS vendors intentionally set the default configuration to conservative values ​​to ensure that the DBMS system runs everywhere to consume the least resources possible and avoid security vulnerabilities. For example, the default configuration of MySQL 5.7 assumes that it is deployed on a machine with only 512MB of RAM. Be aware that default settings always yield suboptimal results, even if they are “preset” by a cloud database provider.
  • Database handles are threaded. It is difficult to know how changing one handle interacts with and affects the others. Without an automated way to learn about dependencies, it takes a long time to experiment with handles and determine their effect. The trial and error tuning time requirements for optimizing a single database can take days or even weeks, assuming the application workload is stable, which it usually doesn’t.

Leave a Comment