On this page:
For DBAs and enterprise IT, one of the key challenges is the tuning of databases and query workloads. It has become a core IT discipline in its own right over the past decades and represents significant OPEX in every IT budget.
With the advent of modern cloud databases, this situation is about to change. The need for tuning and optimizing queries is fast becoming unnecessary as elasticity and flexibility of these systems enable DBAs to compensate and remedy performance issues effectively at low cost.
This article briefly discusses the following topics:
- What tuning of databases and optimization of queries involves
- Challenges in database tuning and query optimization
- How cloud databases are making database tuning unnecessary
- Advantages to adopting cloud databases
Discipline of Database Tuning and Workload Optimization
One of the many artifacts of the database industry, that has developed over the decades, is the curious discipline of tuning databases and optimizing workloads or individual queries with the aim of improving their performance, be it latency or throughput. DBAs spend countless hours analyzing workloads, understanding workload patterns, tinkering with configurations, and troubleshooting the system. This kind of tuning encompasses a wide range of activities, most notably:
- Monitor and observe: determine patterns in usage of the system and anticipate resource bottlenecks, then put in place strategies to mitigate problem situations.
- Optimize workloads: analyze and modify queries or workloads to ensure an ever-increasing query load combined with a gradual or even rapid increase in data volume can be processed within the available time.
- Size and plan hardware resources: understand when it is time to address performance issues with new hardware by determining the trade-off between peak performance needed and how much under-utilization can be tolerated during off-hours.
- Query and database tuning have become an integral part of any database operations and gained notoriety for being an extremely hard-to-solve problem.
Challenges in Database Tuning and Query Optimization
The typical starting point for database tuning exercises is a slow query or workload. In short, a business problem that needs solving in a fixed time, specifically a shorter time than is currently accomplished.
The crux of the problem is that DBAs have a fixed hardware footprint at their disposal, that is, servers with a given number of cores, a limited amount of memory, and a predefined I/O subsystem, such as DAS or SAN. To solve the original business problem in a specific amount of time, DBAs need to be creative and be able to squeeze every last iota of slack from the database workloads, tune up the physical database design, and, if all else fails, persuade their business users to modify their original queries. Each of these tasks is laborious and requires DBAs to have deep expertise in a wide set of IT disciplines, not to mention the negotiation skills needed to convince users to change long-standing business processes.
This necessary and frequent tuning over time makes for highly unfavorable economics for the enterprise due to the following factors:
- DBA hours are costly and constantly modifying the system causes frequent disruptions to the business.
- The resulting system is becoming increasingly complex making future tuning increasingly harder.
- Applications optimized this way are no longer portable between database systems and cement vendor lock-in to the current platform.
What is more, tuning is unfortunately subject to diminishing returns on investment: optimizing for the last percentage point is exponentially more expensive than using broad strokes to solve the very basic problems.
Cloud Databases Make Database Tuning Unnecessary
In today’s modern cloud era, cloud databases are bringing a completely different and new dynamic to database IT due to their elasticity. What this means for enterprise IT is that DBAs can rapidly add and remove resources on demand.
The elasticity of cloud databases is resulting in a new economic model for enterprises in which resources are only being provisioned and used as needed and excess capacities can be shed quickly. This lets DBAs take advantage of fluctuations in database workload patterns. For example, scale the system out for end-of-quarter reporting, and scale back down to a fraction of system size the day after, and so forth.
The elasticity of cloud databases is resulting in a new economic model for enterprises in which resources are only being provisioned and used as needed and excess capacities can be shed quickly.
However, elasticity not only addresses the questions of seasonal system utilization. A much-overlooked aspect of elasticity is its impact on tuning. Instead of spending thousands of dollars in labor to make a query, say, 10% faster, DBAs can leverage the elasticity of cloud databases and simply scale out the system by a specific increment, such as an additional server, as needed.
The economics of this simple trick are striking. Instead of increasingly customizing the database, constantly tuning the physical design of the database, and tuning queries—operations that drive up complexity and TCO—the DBA can simply move the slider on the control panel, provision a few more units, and put performance and throughput problems to rest at once. To be clear, this does not eliminate the need for DBAs but simplifies their jobs as it takes care of the most challenging—and frustrating—problems in database maintenance.
Advantages to Adopting Cloud Databases
The elasticity of cloud databases has immediate and drastic ramifications for enterprise IT moving or considering moving to the cloud:
- Selecting a cloud database is not a matter of speeds and feeds. Not fast enough? Scale out the database with a few clicks. This lets solution architects focus on the features they need and on how aligned a specific Cloud Service Provider is with the enterprise cloud strategy.
- Moving to a cloud database has just become orders of magnitude easier: there is no need for millisecond-parity between the old and new databases. The challenge of whether the new version of the database or the new database will be as fast as the old is no longer a consideration.
Moving to a cloud database has just become orders of magnitude easier: there is no need for millisecond-parity between the old and new databases.
What this means for the DBA and the enterprise is the significantly less aggressive customization of applications to the underlying database, faster technology adoption cycles, and short time to value.
Check out an earlier article in this series, titled, Cloud Databases Shed Operations and Maintenance, on more advantages of cloud databases and data warehouses.