How to limit and avoid underwhelming database performance

In a nutshell

When applications don’t perform as they should, databases are often a good starting point to analyse the issue. So what should you do as an IT manager when databases seem to hold back your applications? How do you spot and prevent performance issues? And how can monitoring help? Keep reading to find out more!

As the influence of applications on our daily lives continues to grow, so does the impact when things don’t go the way they should. Just think of the delays in payment processing for web shops during peak days of the year and their financial consequences. It’s obviously in your best interest as an IT manager to keep things running smoothly, but what if they don’t? And how can you prevent these hiccups from happening in the first place? Joining us with some tips and tricks is Kristof Vangeenderhuysen, our coordinator for database engineers.

How can I prevent slow application performance?

There’s a saying in medicine that says “an ounce of prevention is worth a pound of cure”. While its earliest variation is often attributed to Dutch philosopher Desiderius Erasmus in 1500, its message still holds true for information technology over 600 years later. The best way to solve application performance issues is to prevent them from happening in the first place, and Kristof has several approaches to do just that.

For new applications and features, one of the most important methods is representative load testing. When preparing for a go-live, use datasets and loads that are at least similar to a production environment. Otherwise, you are comparing apples and oranges. Another approach to avoid unwelcome surprises further down the road is using virtual machines and cloud computing, since these represent improved scaling potential compared to physical servers. It’s always better to come prepared.

You can also delve deeper into the database itself through performance tuning. This includes, but is not limited to, index optimisation and partitioning. Indexing means assigning unique identifiers to your database components. Missing or faulty indices can slow down database statements significantly, so you should rectify these as soon as possible.

Partitioning helps to improve your application performance by dividing your database in, for example, quarterly sections and separating those from archived sections. That way, your application doesn’t have to read thousands or even millions of rows for every request. Keep in mind that partitioning is more of an extra option, and it’s better to focus on detecting and preventing slow statements.

How can I fix the performance of my database?

It’s not always easy to predict performance issues beforehand, so what can you do when they’ve already happened? Adding hardware (CPUs, RAM,…) may be an obvious choice, but that’s just postponing the inevitable. If your infrastructure is on-premise and you have some resources remaining, it may be a quick fix, but the eventual limit will still be there.

If you use a cloud infrastructure, adding computational power will be costly and, based on your level of services, time-consuming as well. Most cloud service providers only include automatic scaling and availability from a certain level onward, so they will charge significant sums for additional ad hoc licences and resources.

A better way of working is to contact a trusted partner like Monin and employ the performance tuning strategies Kristof lined out above: index optimisation and statement analysis. Our database coaches will consider both the resource component (the CPUs, memory and disk space powering your database) and the technical component (the way your database is set up).

We thoroughly recommend using a partner with a dual view to avoid an overreliance on one of these components. That way, relevant aspects like network and storage are also looked at. At Monin, if we can’t solve those problems ourselves, we have plenty of contacts in the Cronos Group ecosystem to make sure that your data infrastructure performs at its best. Monitoring can also help solve performance issues by pointing out dependencies.

How can monitoring help my database performance?

To go back to Erasmus’ saying, monitoring can be both a prevention and a cure. Monin has a partnership with DBmarlin, a performance monitoring tool that we helped during development with testing and input. There are tools like RedGate that are great but specific to one technology (in this case Microsoft SQL Server), but DBmarlin is compatible with nearly all database technologies.

DBmarlin lets not just database administrators (DBAs), but people with limited database knowledge quickly interpret performance issues. Thanks to its graphs and dashboards, you will have reports ready to go for management meetings in no time.

Besides ease of use, monitoring tools like DBmarlin provide your data infrastructure with access to trend analysis and snapshots. These tools monitor database performance 24/7 and preemptively warn you when certain queries start to take too long. Snapshots compare moments where the database performed optimally compared to slower days or hours, so you can be sure which queries are causing problems. They can also compare databases: for example, a production versus a development environment.

By running monitoring tools in the background, you ensure that your DBAs and trusted partners can spot performance problems earlier and easier. Using tools like these comes at a cost, but a performance check or problem analysis will cost even more without them. Evaluations will require a lot more time and effort, and you can only handle problems as they appear instead of preemptively.

Because of their analytic capacities, we especially recommend using monitoring tools for development environments. This allows you to detect problems before they enter production and avoid a significantly larger impact. We also recommend you keep using monitoring tools for the databases behind business-critical applications to safeguard the most important processes.

Conclusion

Databases should not be an afterthought when developing or adding to an application. As an IT manager, the health and performance of your database should be top of mind if you want to keep your applications running smoothly. Prevention is still the best remedy, so creating awareness by using a trusted partner with managed services and a monitoring tool is highly recommended.

Is your data infrastructure problem too complex to solve? Need an expert’s eye for detail or a hybrid view? Contact us!