Does your database have a good performance?

I’ve been working in IT (infrastructure and database) for over 20 years, and I have faced different situations and issues. Last year I returned to work as a database architect and I came across some issues that could be avoided if the application and the underlying database were maintained to continue working with good performance, no matter the number of users accessing it or the number of lines or documents.

On the other hand, it is important to relate, that companies need to launch new releases, features, products, and services, not giving up on existing applications. In this article, we will address some issues commonly found in database environments.

A lack of indexes:

It’s a common problem because if the database does not have an index in the queries, it will lead to a lot of computational resource usage (CPU ) and disk I/O. Advantages to use an index in the queries:

Speed up queries and updates:?The right usage of indexes makes the query run faster.

Avoid disk I/O:?If the database uses the right index, the application does not need to query the data in disk, because the data is already in the index. It is better to have the data organized in indexes rather than fetching it from disk.

Reduce overall computation resources:?Same as above. If the query uses the correct index, the application avoids fetching data from the disk as well as unnecessary CPU usage.

Each type of database has some method to find, analyze, run and fix a query that is not using indexes or using an inefficient index.

Inefficient Queries:

Sometimes the developer’s team doesn’t know how the database runs queries and the code they write can lead to significant performance issues. These queries can fetch more data than the application needs or will be used. The effect of a non-performance query is a bottleneck in the application.

Resource Saturation:

CPU and memory should be the starting point for checking the health and performance of the database environment. Monitoring these resources should identify a bottleneck in the application as a whole. When identified, the infrastructure team together with the database team must also analyze the other items in the environment (OS, Network, Disks) to look for the root cause of the bottleneck, which may be in more than one component of the environment. This holistic analysis is recommended to more accurately identify the possible causes of the problem, the overuse of resources may be present in more than one component.

Poor Database Design:

This could be the worst-case scenario because they are very expensive to change and can take a long time to adjust. Some reasons for a bad database design:

• Lack of knowledge of the business rule to achieve the purpose of the application
• Lack of knowledge of the database system used
• Lack of knowledge of the application/workload

Some questions must be made before creating a database:

• Is your environment workload high-read, high-write, or both?
• Is your workload steady, or does it change based on specific events?
• What type of database engine are you using?
• Does it match your workload type?

After these questions were answered, the development team together with the data team has the best way to build an application that achieves the business requirements with a good performance.

Unused data or historical data:

A company may have large database environments that store large amounts of data. This data can represent the history of orders, purchases, and payments (from suppliers, customers, and employees). If the database is well optimized, using the correct indexes (even if they have a considerable size due to the size of the database), and modeling is well done, it is still possible that performance degradation is due to a large amount of data.

An example to illustrate this topic: If a particular company has a database that stores the orders (sales of all products) for long years, probably the database has a large number of rows or documents (depending on the database). After an analysis, based on business rules and performance metrics, could be recommended:

Practice Data Archiving: Some database systems offer a product to archive the data (either on-premises or in the cloud). This can be a good solution to keep the data secure (for historical queries, for example) or for some regulation that requires historical data, or even for internal company reasons.

How to solve the above issues? (or at least minimize them!)

The best practice, in addition to the guidelines presented above, is to use a monitoring tool for databases and other components, which ensures compliance with the metrics presented and that the root cause(s) can be identified and corrected. Nowadays, some monitoring tools are even able to predict the behavior of applications (using AI concepts) to avoid possible overloads in environments.

Final considerations:

It doesn’t matter what your database platform is. The application can use an RDMS or a NoSQL database, you must have a procedure to review the database, verify performance, and security, and analyze whether the data on disk can be archived (hot data). This procedure can keep your data up to date and as a result, your application can also perform better.

Do you have any questions about how to keep your performative database? If you need any help? Please feel free to contact me.

Part of this article was based on this link:?https://learn.percona.com/hubfs/Collateral/Solution_Briefs/Solution_Top_5_Causes_Poor_Performance.pdf