Understanding the Capacity Management Challenges of Database Monitoring Solutions

Database monitoring requires hardware resources such as storage space and processing power that can withstand the volume of database usage in your organization. A higher usage volume will require more resources.
So how can you optimize the resources used by your database monitoring solution? Do you have enough resources? Answering those questions is a challenge which you can start tackling by understanding different database usage profiles.

The Challenge – High Variance of Database Usage Profiles

A database usage profile represents the amount of traffic a database handles and the pattern of usage over time – for example, a database for an HR application will likely show high traffic load during office hours only.
Database usage profiles differ in many aspects. There are three main classification categories:

• Application-related aspects
• Maintenance-related aspects
• Database-related aspects

Application-related aspects – each application has a unique database usage profile. For example, number of users and activity hours may have a significant impact. Also, while some applications may heavily rely on local caches, others may require intensive database activity for each user action. Software infrastructures such as Java Hibernate can have a huge impact on the queries being run on the database.
Maintenance-related aspects – DBAs perform different maintenance tasks on databases. Those tasks are sometimes automated and scheduled, and sometimes performed ad hoc. Each organization has a set of workflows and procedures which impact the type, number and frequency of these maintenance operations. The main difference between this database usage and application related database usage is that application related database usage is much more organized, with (usually) very limited number of users and sources – the application servers. The maintenance related database usage can be sourced from hundreds of different privileged users, mostly DBAs, with a large variance in behavior.
Database-related aspects – Different databases may display a very different behavior when working with the same application. The basic reason is that we deal with multiple database types, from rational databases, through Big Data technologies (Hadoop framework, Apache Cassandra distributions, Hive, Spark etc.) to unique types such as z/OS IMS. Different types of databases are accessed in different ways, mostly the well-known SQL, but also various NoSQL APIs may be used. Another related aspect is the hardware profile of the machine(s) which the database is installed on (such as number of CPU cores, RAM size and so on).

Database Usage Profile – Finding the Pattern

In your organization, each database may have a different usage profile and still have common characteristics:

• Activity during working hours increases
• Activity on weekends and/or holidays decreases
• Activities that show a certain pattern over time (for example, daily usage)
• Generally, a strong correlation between the database “size” (number of CPU cores, amount of memory etc.) and the amount of activity seen

When we looked at the database activity of numerous databases at multiple Imperva SecureSphere Database Auditing and Monitoring solution implementations (Figure 1) we found that:

• Database activity in a time resolution of days and weeks shows repetitive trend behavior
• Database activity in a time resolution of minutes and hours shows random and unstable behavior

Figure 1 – Example of a typical database activity (usage profile)
As seen in Figure 1 above, as you’d expect, database activity has a peak during the day and low activity during the night. However, during the day this database has many local peaks and times of almost no activity.
Creating such a profile per database in your organization may be cumbersome as you need to understand and measure the actual pattern for each database. On top of that, other aspects which may impact the usage profile, such as number of users or even a database version upgrade, may change over time. There is a need for a set of tools to help with the task of creating the profiles.

Capacity Management – Optimize Resources of Your Database Monitoring Solution

Without having a proper profile of your database activity maintained over time, it is very difficult to understand how to optimize resources of your database monitoring solution and handle capacity issues. Even with a proper profile, the problem of optimizing multiple databases, each with a different profile, is challenging. Ongoing review of profiles and resource capacity is required.
The typical work flow for analyzing and addressing capacity issues consists of the following steps:

1. Evaluate resources of the database monitoring solution
   • Are there resources which suffer from overload?
2. Analyze the best long term mitigation option(s) using the databases usage profiles
   • Review mapping of databases to database monitoring solution resources so that overload will not occur.
3. Perform the mitigation
   • Either by current resources optimization (change the mapping of databases to database monitoring solution resources) OR resource scale up (use more powerful hardware) OR resources scale out (use more resources)
4. Validate the mitigation over time

Learn more about Imperva database monitoring solutions and how they can help organizations identify and maintain database usage profiles to optimize database capacity.