Five ways to find database fixes faster
Slow application performance is easily cause for a bad day on its own, but tracing the source of the problem down to your database is often just the start of a long and painful investigation. You know that a particular query is running slow, now the hassle is figuring out why. Here are five things to look at to help you identify the problem and put a fix in place faster.
1. Record your resources
Make sure that you’re provisioning enough resources for your database to run optimally. For on-premises databases, this includes looking at the underlying server infrastructure or virtual machine to see the big picture. Often times a resourcing issue quickly points to the fact that the problem isn’t actually with your database, though it’s causing your database to perform poorly. Other processes can be hitting your CPU and memory hard, starving your database of the resources it needs. If your database is in the cloud, resourcing can be a more complex issue. Is the current instance size large enough to handle the load? Am I encountering any plan limits that aren’t clearly represented by the individual resource metrics? While you always want to prioritize cloud provider costs, you also need to consider the requirements of your system and how you can provision smarter to best utilize the strengths of your cloud platform. However you run, it’s worthwhile to look at your infrastructure metrics alongside your database to quickly see any issues of note.
Figure 1: Resource constraints, like CPU, are the most common database performance issue.
2. Mind the memory
It’s easy to overlook storage utilization as a potential cause of database issues. While you can try to jump to faster storage to solve any IO throughput problems, there’s usually a deeper cause that will just rear its ugly head again in the future if you don’t solve the underlying problem. If your application is ready heavy and you are seeing high read throughput, you may need to reconsider cache and buffer provisioning. Buffer page life expectancy shows you how long you can expect a page to be stored in memory. While the ideal duration depends on your particular use case, typically if that metric is under a minute, that’s too fast and it means you’ll be constantly reading off disk.
Figure 2: Check your buffer page life expectancy, you may find excess reads that could be causing I/O bottlenecks.
3. Keep current on your connection limits
Connection limits can be a difficult problem to pinpoint if you’re not looking for it. When an application isn’t pulling data the first impulse for most is to assume the database is down when in reality it may be that a lot of different applications are hitting the database at the same time, resulting in more connections than allowed. If the number of connections needed is legitimate, you can easily adjust your connection limits to alleviate the issue. However, sometimes it may be that connections aren’t being closed, piling up unused connections until things grind to a halt. This points back to an application problem where adjustments are needed to terminate those unused connections.
Figure 3: Before you assume your database is down, check your connection rates.
4. Investigate your indexes
Indexes can come in all shapes and sizes, and some of those can negatively impact your database performance. Poorly written indexes, highly fragmented indexes, and unused indexes taking up memory can all be cause for concern. Many database systems provide a view into the utilization of your indexes and you can use that information to tune queries, reconsider indexes, or reorganize your data. Discovering the query plans used to execute common queries can also help you discover which indexes are actually being used properly to speed up your application.
5. Don’t underestimate updates and patches
Sometimes the most simple things are the most effective. Security and performance issues are often addressed in the latest database versions so make sure you’re getting notifications when new there are updates or patches to apply. Subscribe to the right RSS feeds or invest in a tool that can help you keep track.
Figure 4: It can be hard to find time for updates, but they could be behind database performance issues.
These five steps can get you on the path to fixing faster. Ultimately underperforming queries or a slow database can be caused by a wide range of issues. Having access to the explain plan can help pinpoint some of those, and a number of tools can take it a step further, providing recommendations for fixes and providing insight into ways to reorganize your queries for better performance that are particularly useful while in development.
One way to get immediate insight into all of the ways your database ticks is to sign up for Selectstar, an enterprise-class database performance monitoring platform. Start your free 14-day trial today and find out how quickly you can solve your database performance issues.