What Metrics Should Be Used to Assess Database Performance Speed

What Metrics Should Be Used to Assess Database Performance Speed

Your database performance in production settings may be of interest. PostgreSQL users have access to multiple tools for tracking database activity and identifying slow spots. In this post, we’ll discuss several helpful resources for database health monitoring.

Verify the status of your database performance server

The first step in fixing performance issues is making sure your database host has enough resources to run your processes. Start by verifying the hardware by checking the processor, RAM, and storage space.

CPU

Upgrading to a more powerful CPU could be in order if your database consistently falls short of expectations. Examine CPU performance metrics such as CPU ready times, which can indicate when the system was unable to use the CPU because of a lack of resources. Because of the constant background work that database servers do, two or more processing cores might be required to keep the server responsive. A more powerful CPU can boost database speed and efficiency by reducing the stress created by several apps and requests.

Memory

Inadequate RAM is another possible reason for slow databases. Memory utilization and page faults per second are two metrics that should be evaluated together to get a complete picture of your memory needs. When the number of page faults on your hosts reaches the thousands, it’s time to upgrade their RAM. Most of the time, both efficiency and performance go up when the amount of memory available goes up.

Assuming that the database is the only program running on the server, another option is to give MySQL 70% of the server’s total memory.

Disc room

Since indexes and other speed enhancements lead databases to consume more disk space than is strictly necessary, it is vital to have a lot of storage space available for your database server. By separating your database’s data from that of other applications, disk fragmentation caused by those other operations is reduced. Also, speed can be increased by setting aside a group of drives for data files, log files, backup files, and tempdb. This group of drives can also be used as a backup in case of a disaster recovery.

An increase in disk latency is a common cause of poor database performance. Check disk latency metrics frequently. Utilizing existing caching technologies is the quickest and cheapest way to reduce latency issues.

If you’re looking to boost your server’s performance, another element to consider is the disks you’re using. One single query can require millions of i/o operations to retrieve its results due to the massive amounts of data it needs to access or return. Changing from magnetic disks to solid-state drives may improve the speed of your SQL queries and thus the overall performance of your database. For best performance, go with an SSD version made specifically for use in databases.

In What Ways Should Database Performance Speed Be Characterized?

When programmers or database administrators (DBAs) tune the performance of a database, they can make the most of the resources they have. This leads to noticeable and long-lasting performance gains.

A database is like the brain of your app; it controls all the important functions and is responsible for keeping them in order. So, even small problems with how well the database works could have a big effect on the whole operation.

The well-maintained and readily available application infrastructure depends on the timely detection of database faults. Database performance outages can have a significant impact on a company’s bottom line if they aren’t caught up until it’s too late.

To be more specific, query/s (QPS).

Using QPS (queries per second) is the most natural method. In a particular time frame, how many queries can the database handle? Unfortunately, the two queries are not equivalent. It is possible to perform INSERTs, updates and selects. In addition to the more straightforward searches that make use of data access mechanisms like indexes and primary keys, we also have the option of more complex queries that include the joining of many tables. In particular, we can only compare how well a single query works to a carefully designed mix of queries.

Due to workload variability in the real world, it might be difficult to determine the optimal set of queries to utilize when comparing the effectiveness of various configurations. A query mix can be made at a certain point in time, but if benchmarks are done again a few months later, the query mix is likely to be different. This makes it hard to compare performance over time.

TPS stands for Transactions Per Second.

A related question is, how many deals can we close in a given time frame? The problems with this method are similar to those seen when employing QPS. Over time, both the kinds of transactions and the queries they require will evolve. The transaction-per-second method may be useful in the present, but it will be difficult to make long-term comparisons.

Latency (P99)

Let’s take a stab at approaching this from a different angle. To what extent do you think the following factors influence performance the most? Is it the rate at which we can complete a transaction or run a query? Would you be fine if the time it took to complete a specific question doubled if it meant a 30% increase in QPS? How is that even possible, you might wonder? You’ll find that it’s actually fairly easy. Keep in mind that most databases only allow one CPU core to be used by a single query. There are cases where a query can be handled in parallel, but for now we’ll focus on the bulk of the work. Thus, one inquiry is equivalent to one CPU core. This means that the number of queries you perform is limited only by the number of processors at your disposal. By doing so, we can reduce how long it takes for queries to run. Conversely, we can aim to increase throughput as much as possible. Executing a query is not a particularly efficient procedure, and it wastes some CPU time. If we begin queuing inquiries and let the CPU scheduler handle many processes or threads, we may process more questions simultaneously. This allows for a dramatic boost in throughput (measured in queries per second) at the expense of an increase in query execution time (because each thread must now share CPU resources with other threads). This means that the speed of queries has slowed, but we can still perform more of them simultaneously.

Users expect their queries to complete quickly, but they may be willing to accept a little slower query completion time if it results in more consistent query execution times. Because users hate it when an app suddenly slows down for no apparent reason, even more than they hate a slow app overall. As throughput grows, latency grows alongside it, and in some cases, instability grows alongside it as well.

Clearly, this complicates our talk about performance even further. Understandably, people care most about the speed and responsiveness of their applications (so, we would like the latency to be below). In contrast, if there are many users, we need the ability to perform several searches simultaneously (so throughput should be high).

Can Database Performance Speed Be Improved?

We’ve discussed how to quantify database performance, and we know that latency and throughput are the two most important indicators. How to improve database performance is another pressing issue that must be addressed. As a whole, there are a few choices to consider.

Upgrading the Gear

There is a direct correlation between output and resources. When we upgrade the database server hardware, database performance improves. For a more precise estimate of the potential gain, we would need to know more about the specific workload being evaluated and the modifications that have been made. There are, in essence, two broad categories of labor load.

CPU-limited processing

When processing power is capped by the available central processing unit (CPU), the workload is said to be CPU-bound. In this context, “minimum disk activity” means that the working dataset can be held in memory. It can be brought on by either a small number of very long searches or a large number of very quick requests (index lookups, for instance) (heavy, analytical queries that involve JOINS or sorting and grouping). In this situation, increasing the number of CPU cores or upgrading to a newer CPU model with more performance per core can boost the database’s overall database performance.

Input and output bottlenecks

Workloads that place a heavy emphasis on the I/O subsystem—typically a disk—are known as I/O-bound. Those are the two most common triggers for this phenomenon. The first is that you have a write-intensive workload, meaning you frequently update or add new information to your database. Consequently, the disk drive creates a bottleneck as the number of writes needed to persist those alterations increases. The second most frequent scenario is when the amount of data you’re actively working with exceeds the capacity of your RAM. The most frequently used information in your database is called the “active data set.” Be aware that if your dataset fits on disk rather than in memory, you won’t have any issues. When the database must often move data into and out of memory to accommodate the application, swapping database performance degrades. In such a scenario, we see more disk reads than usual.

It stands to reason that hardware solutions for these two classes of issues will differ. Increasing the server’s computing power by adding more CPU cores or enhancing the per-core performance is a good idea for CPU-bound traffic, as is upgrading the CPU to a more recent model, although the improvement is usually relatively small. There may be two approaches to reducing I/O bottlenecks in the workload. The first order of business is, of course, to boost the disk subsystem’s efficiency. You can increase RAID performance by adding more drives, switching to a higher performance RAID level (RAID 10 as opposed to RAID 5 or 6), or exchanging the disk drives. If you’re having trouble with a lot of reads, you could also try increasing the amount of RAM you have. This would let your database store more data from the active dataset in memory, so it wouldn’t have to read as much data from the disk.

Tweaking a configuration

Each database has its own configuration that allows users to fine-tune certain settings for optimal database performance. It’s possible that some configurations are better suited to CPU-bound workloads, while others are better suited to I/O-bound ones. There are rumors that DBAs keep their most valuable knowledge, like automated configuration tuning scripts, buried on StackOverflow and Quora. Unless your database is entirely untouched, it is highly unlikely that adjusting its settings will yield noticeable speed improvements. You might be able to marginally enhance your performance, but that’s about it. The speed of your database cannot be increased by a factor of 10.

Tuning Queries

To potentially multiply your database performance by a factor of 10, try optimizing your queries. Improve database performance by rewriting queries and creating indexes where they are lacking. Here is where you will notice significant improvements, much like in those beautiful screenshots of various monitoring programs that have been floating around the internet. Yes, this speeds things up greatly if the query is needlessly visiting hundreds or thousands of rows while a sensible index would just require access to a single record. A detailed description of the query tuning process is beyond the scope of this blog post. However, it is important to collect metrics related to the queries, such as their execution time, waits experienced by the query, the number of rows reads from the database and the number of rows sent to the application.

The more information that can be gathered, the better, although the specifics of what can be gathered will vary from database to database. Nonetheless, most databases provide at least some information about query speed. Furthermore, it would be advantageous if you had access to software or other tools that could assist you in processing this raw data. You should be able to learn more about the database’s behavior, its performance, and the queries causing you trouble.

Then, you should maybe try to figure out the peculiarities of the troublesome requests. A query execution plan provides a high-level summary of the execution process as determined by the database performance optimizer. Again, the specifics will vary across databases, but in this context, we’re discussing questions like which method is being used to retrieve a set of data, whether or not indexes are being used, and so on. You might assume that the JOIN method and table order would be displayed when discussing relational databases. This should help you figure out if the plan for carrying it out is the best one or if it could be better.

Once the problem is identified, optimizations such as better indexing or a rewrite of the query to a more efficient form might be attempted. Remember that there may be ways to rewrite queries on the fly, even if you’re using an external program that you can’t change. In most cases, this occurs in the load balancer.

Evaluation of Results

After you’ve finished fine-tuning the query, you can check back later to observe how the key database performance indicators have changed. Does the query do fewer row accesses? Does it make better use of indexes? Is the process quicker in operation? All in all, this is how database efficiency is evaluated. The p99 latency should be monitored for all queries. Besides these core indicators, you should also monitor the performance of all query types.

After making some adjustments to the queries, you can see the effects on the most relevant metrics by keeping track of them over time. Was there a decrease in latency? How steady is it now? What is the disk space usage for a specific query type? If you need to make a hardware adjustment or fine-tune your system’s setup, follow the same steps. Visualizing the p99 latency over time will reveal whether or not your tweak improved performance, and by how much. To our benefit or detriment? The magic sauce, then, is not complicated at all: while your database is active, capture the relevant performance measurements. In other words, you can see exactly what happens after you implement a change.

Because of the breadth of this issue, we hope you find this blog post on improving database performance to be of use. Feel free to leave a comment if you have something to say.

Conclusion

Measuring database performance can be tricky. There are a number of factors to consider. We hope you find our blog helpful. If you have any other questions or concerns about measuring database performance, please contact us anytime at Enteros. Thank you for reading. We are always excited when one of our posts is able to provide useful information on a topic like this!

About Enteros

​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.