How and when to deploy DBtune
When to initiate a tuning session using DBtune
DBtune is an online system designed to dynamically observe and optimize workloads in real-time through 30 optimization iterations. Achieving the best results involves triggering a tuning session during the period of maximum load and peak usage. This aligns with times when end-users perceive system sluggishness and are actively seeking improvements.
Example: Database performance at a bank
Imagine a bank that operates from 9 am to 5 pm. During these hours, the high volume of transactions creates significant database load. Let's understand when to optimize our database performance:
Peak hours (9 am - 5 am):
- Heavy transaction load
- Active customer interactions
- Real performance bottlenecks
- Ideal time for DBtune optimization
Off hours (after 5 pm):
- Minimal transaction volume
- Limited user activity
- Few performance constraints
- Less valuable for tuning operations
If the peak workload ends at 5 pm, start DBtune no later than 1:30 pm. This ensures the entire DBtune tuning session operates within the period of peak load.
Based on extensive experience with numerous users, there is typically only one time window that the user wants to optimize. For instance, in the example above, the focus is on the 9 am to 5 pm window. Performance during the 5 pm to 9 am time frame isn't problematic for the bank, rendering optimizations unnecessary.
DBtune's goal is to tune the time window that matters. Users should initiate DBtune when the intensive database usage window is active, completing the tuning session entirely within that period, i.e. the full tuning session needs to start and complete between 9 am and 5 pm in the example above.
Handling multiple workloads
While the standard use case involves optimizing a single workload, scenarios can arise where distinct workloads occur at different times of the day or the week.
Example: Weekday vs. weekend operations
The bank's database faces two distinct workload patterns that require different tuning approaches:
Weekday operations:
- Regular transaction processing
- Short, frequent queries
- Customer-facing operations
- Real-time processing needs
Saturday reporting:
- Complex analytical queries
- Large data set manipulation
- Resource-intensive operations
- Batch processing focus
Schedule separate tuning sessions for weekday transactions and Saturday reporting workloads to optimize for each unique pattern.
In such cases, users trigger DBtune twice: once during the weekday 9-to-5 window and again during the Saturday window. The outcomes, say, config1 and config2, are optimal configurations for their respective time frames. The two configurations have to be then scheduled to appear on the server with access to the conf.d directory. A script written by the user schedules these configurations to apply to the system via the conf.d directory, thus accommodating diverse workloads. The script simply copies the configuration files provided by DBtune in the conf.d directory at the right time so that the optimal configuration for each workload is used.
Ensuring ongoing performance
DBtune delivers optimal performance after a tuning session. However, as databases are dynamic, evolve and grow, regular usage of DBtune is crucial to maintain high performance.
Example: Performance degradation
A real-world example shows how database performance can change over time:
Initial state:
- June 1st: DBtune optimization completed
- Optimal performance achieved
- Configuration aligned with workload
After one week:
- Application changes implemented
- Performance degradation observed
- Previous optimization becomes less effective
Monitor your database's performance and, upon detecting degradation, launch a new DBtune session. If issues relate to database configuration, DBtune will swiftly restore optimal performance.
The frequency of necessary re-tuning varies based on your application. The following events typically signal the need for a new DBtune session. They are divided in frequent and infrequent events.
Frequent
- Changes in one or more queries due to application updates.
- Substantial growth in the database's size.
- Scaling an instance up or down.
- Changes in the data distribution which affect the query planner.
Infrequent
- Migrating the database from on-prem to the cloud or vice-versa.
- Migrating the database to different hardware.
- Migrating the database from one database management system (e.g., Oracle) to PostgreSQL.
- Upgrading PostgreSQL to a newer version.
Integrating DBtune with query tuning
After conducting query tuning, initiate a DBtune session to further optimize performance. As query tuning is often iterative, running DBtune after each round of tuning enhances query plans. If results fall short of expectations, restart the iterative process: first, perform query tuning, followed by parameter tuning with DBtune. The compounded performance of query tuning and parameter tuning can be much higher than the single improvements.
DBtune performance measurement
Each DBtune iteration lasts about 7 minutes. Since DBtune will perform a total of 30 iterations, the total tuning session time will be about 3.5 hours. The total amount of time depends on if you set the system to allow or not allow the database to restart during tuning — More about the restart option below.