Understanding the fundamentals of database performance

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