Understanding the fundamentals of database performance

clock • 3 min read
Understanding the fundamentals of database performance
Image:

Understanding the fundamentals of database performance

Memory, CPU, indexing - understand the fundamentals to track down bottlenecks

Databases play a critical part in the success of businesses, helping organisations manage one of their most important assets: data. As such, it's vital for IT pros to prioritise database performance. Understanding how a database works and knowing what to monitor is key to overall performance, so let's look at some of the fundamental issues affecting database performance.

The importance of memory

Memory plays a key role in the performance of your database. The database engine has its own memory management, and databases also have their own internal operating systems to manage data retrieval. This means, by design, databases utilise as much memory as necessary.

Reading and writing directly to disk is slow compared to memory access, so database engines take advantage of memory cache as much as possible. Databases have a buffer cache to store frequently or recently used data blocks so the engine doesn't have to go to disk to retrieve those pages.

If the database engine runs out of memory, it'll use a temporary database (or a file on the file system) to complete a task. This is similar to a system process paging to disk. This isn't great for overall performance and can be avoided by monitoring memory usage alongside database query construction and execution to help the system consume fewer resources. As is often the case, you can also improve performance by adding more memory, providing memory is the bottleneck.

What's going on with your CPU?

CPU is another resource vital to database performance. In general, if your database server is seeing high CPU utilisation, something isn't optimised. It could be something as simple as indexing, or you may have too large a workload, even for an optimised database. Idle connections may have a negative impact on performance, consuming memory and CPU resources and impacting overall concurrency.

If your CPU utilisation is higher than desired, take the opportunity to look inside your database engine to identify the queries using the most CPU and analyse how to optimise. Also, look for queries running frequently and consuming CPU resources each time they run. Optimising these smaller queries will reduce overall CPU consumption, helping you avoid a so-called 'death by a thousand cuts'.

Performance through indexing

There's more to database performance than memory and CPU. Efficient query construction and database design play a role in better performance, as does efficient indexing. 

An index is a subset of a table existing within a database. Indexes consist of one or more table columns and don't necessarily include all rows. The index is linked to the table, so when data is inserted, updated, or deleted, it's reflected in the index before the transaction is marked complete by the database.

Without indexes, the database engine would scan every row of each table requested in the query, and transactions deleting, updating, or inserting data would cause blocking issues for other queries. Indexes vastly reduce the amount of I/O for operations, offsetting the overhead for read and write activity and speeding up query execution as a result.

It's easy to see the benefits efficient indexing has on the performance of a database, but it's also important not to get too carried away—there's no need to index everything. Inefficient indexes create more work for the database engine, causing performance issues. Instead, concentrate on indexing the areas of your database capable of benefitting most, such as frequently accessed columns.

It's always possible to find additional ways to optimise your database performance, but by grasping these fundamentals, you can start to understand where resources are being used or bottlenecks created.

Thomas LaRock is Head Geek at SolarWinds

More on Security

Register now: Prevent, protect and recover from ransomware

Register now: Prevent, protect and recover from ransomware

A company is hit by ransomware every 11 seconds.

clock 28 June 2022 • 1 min read
Have a plan for recovery, and then have a backup plan for when the first falls through

Identify your "vital ground" or your cyber effort is wasted

And the road to recovery is to plan, plan and plan again

Tom Allen
clock 16 June 2022 • 3 min read
Cyber weapons are fast, cheap, remote and hard to trace

In modern war, we have as much to fear from cyber weapons as kinetics

The physical world is past the point of no return when it comes to dependence on technology, says Ian Hill

Tom Allen
clock 16 June 2022 • 3 min read