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

You may also like
MariaDB shareholders approve private equity takeover

Mergers and Acquisitions

Majority of shareholders have chosen to support the K1 bid

clock 22 May 2024 • 1 min read
Dell confirms data breach affecting 49m people

Hacking

No financial info stolen, but names and addresses were leaked

clock 10 May 2024 • 2 min read
AI interview: Chunk wisely to avoid RAG hell

Developer

DataStax's Ed Anuff on the finer points of AI app development

clock 15 March 2024 • 4 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