Understanding the fundamentals of database performance

clock • 3 min read
Understanding the fundamentals of database performance

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

You may also like
Researchers find user data exposed on LectureNotes learning app

Threats and Risks

Misconfigured database was leaking data of more than 2 million users

clock 08 February 2024 • 2 min read
MongoDB confirms exposure of customer data in attack


No evidence of exposure to the data stored in MongoDB Atlas cloud, it says

clock 18 December 2023 • 2 min read
SEC sues SolarWinds, CISO for fraud and security failures

Threats and Risks

The firm allegedly misled investors about its cybersecurity practices and vulnerabilities

clock 01 November 2023 • 2 min read

Sign up to our newsletter

The best news, stories, features and photos from the day in one perfectly formed email.

More on Databases

From the archive: What was LOLA?

From the archive: What was LOLA?

Computing is celebrating its 50th year of informing and empowering IT leaders. One of our early readers has been in touch with a story of his own about an ambitious project from the 1970s.

Penny Horwood
clock 17 March 2023 • 2 min read
Microsoft introduces Oracle database migration assessment tool

Microsoft introduces Oracle database migration assessment tool

The new tool evaluates the complexity of the database code and offers suggestions for database migration

clock 13 October 2022 • 2 min read
What to consider when planning your database strategy

What to consider when planning your database strategy

Four priorities to focus on

Dan Ashton
clock 08 September 2022 • 8 min read