At OptimaData we are regularly asked to investigate disappointing performance of database environments (such as SQL Server, MySQL or PostgreSQL). Due to all possible variations and combinations in environments, there is not one specific or directly identifiable cause for such a performance issue. In this blog Dimitri Choustov explains how to get your database fit and healthy again.
Countless variations and combinations
Due to the possibilities offered by the far-reaching current digitisation, no database environment is the same anymore. The software used to process data is equally diverse. Production databases nowadays run on both virtual and physical environments. Both flash storage and a part of a storage area network can be used, in whole or in part on site at the customer’s premises or in the cloud. Because of all these variations and combinations, there is not one specific or directly identifiable cause for a performance issue. In order to quickly and efficiently form a picture of the current state of the database environment and to investigate where improvements can be made, OptimaData works with so-called HealthChecks.
Just like the organic world, the digital world consists of different components. Each component has its own guideline, or best practice, for its habitat. For example, the guidelines for a virtual machine for the purpose of application software differ from the guidelines for a virtual machine for the purpose of databases. Best practices minimise the risk of error and also ensure that an environment performs as optimally as possible.
The configuration properties of digital components, such as a virtual machine, an instance in a relational database management system or a database with the underlying objects, play an important role in the functioning of the software. Checking the current configuration of such a digital component in combination with the supplier's guidelines is the basis of our HealthCheck. In other words, the HealthCheck maps the configuration of the digital components and their interrelationship.
The result of such a HealthCheck provides insight into the digital world of the data. As part of this, we check whether the important maintenance processes are present, so that the continuity and availability of the environment can be guaranteed. Think of a periodic backup of the database, data integrity checks and, of course, index maintenance. Our HealthCheck provides sufficient insight to answer certain questions, such as: ‘the backup of our data is taken care of, but is recovery also possible?' or 'have our data been checked for integrity errors?
Server optimization before querytuning
In this way, the source of any disappointing performance can also be traced. A HealthCheck contains various checks in the area of general server performance. Think, for example, of an SQL query that has been set up according to the rules, but whose lead time still leaves something to be desired. The optimal SQL query performance can only be achieved on an optimized operating system, with optimized hardware and an optimized database management system. The configuration properties of all these objects affect the final lead time of an SQL query.
Perform HealthCheck periodically
If the server has been optimized, the backup of the database and the integrity check have been set up, and the application is functioning properly, it is still useful to periodically perform a HealthCheck. The reason? The database environment is subject to business growth and needs to be adjusted from time to time. As time goes on, the data will continue to grow, as will the demand for it. In order to efficiently handle the demand for that data, for example new indexes may be needed to replace the existing indexes. Using the HealthCheck, we can identify both missing and unused indexes.
In addition to one-off or regular HealthChecks, we can also offer tailor-made solutions. For example, after a HealthCheck we can track down SQL queries that consume (too) many server resources, and provide appropriate advice. We have HealthChecks for SQL Server, PostgreSQL, MySQL/MariaDB, Oracle, Sybase and MongoDB databases. In addition, we have an extensive repository of best practices for the most common and usual setups, such as on-premise, cloud, hybrid and virtual machines in all possible combinations.