At OptimaData, we regularly receive requests to investigate disappointing database performance. Often the organization has already had the conversation with the software vendor. A common response from the vendor is: the database server has insufficient CPU or memory. But how much processing power and memory is actually sufficient? And what can still be done if the application still responds slowly, even if the database server has sufficient hardware capacity? In this blog, Dimitri Choustov explains where possible improvements can be found.
Hardware: Storage
The speed of internal memory cannot be matched. Developments of Central Processing Unit (CPU or processor) also continue to this day. But the speed at which conventional high-performance hard drives run is stuck at fifteen thousand rotations per minute. More and more companies are switching to Solid State Drives (SSDs) and are willing to accept the higher cost of this type of storage. Even with SSDs, storage can still be the slowest hardware component. Configuration of storage therefore deserved special attention. Different RAID configurations offer better disk I/O (Input/Output performance) and the availability of the data on it. Adding multiple SCSI controllers to a VM will improve overall performance, provided the logical disks are paired correctly. Database files can get very large these days. You can reduce disk I/O by placing the database files on a logical disk formatted with a higher block size (Unit Allocation Size). The virtual server performs better if the swap file - or: virtual memory - is created on a logical disk with a higher block size.
Hardware: CPU
The processor plays a key role in balancing performance and cost. When CPU load is high or low, there may be too few or too many processors available, respectively. In practice, adding additional processors can mean double the cost - both of hardware and software. The number of software licenses is often tied to the number of processors available for a relational database management system (RDBMS). Adding additional processors improves overall CPU performance, but whether it improves the performance of an individual SQL query remains to be seen. This while the cost of hardware (depreciation or usage) and software licenses is high.
Hardware: Memory
Random Access Memory (RAM) or the working memory in a (virtual) server works closely with the CPU. Before a data page (a piece of information) is processed by the CPU, it is first read from disk and placed in memory. This greatly increases the accessibility of data page and thus the performance of the server in general. However, working memory is expensive and often has limited availability. The appropriate amount of working memory is derived during the production process from the average lifetime of a data page. However, the right amount of working memory does not guarantee good performance of an individual SQL query or procedure. For example, due to a design flaw in the software, a single data page may be unnecessarily processed by the CPU in a single transaction.
Software
The number of applications installed and running on a VM, as well as the configuration of these, affects the number of processors, memory and the size of the logical disks. The more software components, the more challenging it becomes to allocate the available hardware among them. In an efficient configuration, a VM is dedicated to a specific purpose. For example, it is either an application server or a database server. Keeping test, development, acceptance and production environments separate is a wise and at the same time economical choice. Antivirus software today is operational on every environment, but often inadequately configured. With proper configuration of the exceptions in antivirus software, certain files in certain directories are ignored, promoting the overall performance of a server. RDBMS as an application also benefits from proper configuration of specific properties.
Operational Systems
Placing a swap file on a separate logical drive improves overall server performance. In a Microsoft Windows Server OS, for example, the Power Options configuration defaults to Balanced. This configuration helps reduce power usage, something that is the talk of the town these days. However, for better overall server performance of a database environment, it is recommended that this option be set to High Performance.
Database
We get to the heart of the matter, something every DBA loves and is passionate about. On a database, the opportunities to improve performance really begin to increase. The normalized data model, the correctly chosen column data type, the fully indexed referential integrity, efficient code with which the objects such as views, procedures and functions are built or the functional application of these objects are just the examples of these opportunities. When it comes to improving overall query performance on a database, index maintenance is the low-hanging fruit here. Yet this is precisely where a DBA's challenges increase. Any change on a database will affect application performance. Cooperation with a software vendor is therefore inevitable.
Application
Finally we come to the realm of human creativity! There are so many words with the same meaning - application, computer program, software, application, interface. That performance can be addressed and improved here is as true as the assertion that every application consists of lines of code written in a particular programming language. The impact of performance improvements is best summarized in the following graphic:
Proper performance of any application depends on proper configuration of the hardware, operating system, RDBMS system and database. But most performance gains can be achieved by optimizing the application itself.
Finally
Employees at OptimaData have extensive experience in improving overall performance on a database environment. Most cases involve collaboration with specialists from different IT areas. Most of the work is in collaboration with software vendors and developers.
Is your organization also experiencing performance problems? Do you think the configuration of your database environment could be better? Or are cloud or hardware costs skyrocketing? Thanks to our years of experience, we know how we can help you and we are happy to do so! Please feel free to contact us.