The platform journal

Database4 min read

Database optimisation: how memory caching reduces disk reads

WordPress uses queries to read posts, settings and product data from its database. A single page can need several queries before the server can send a response.

The database does a lot of that work. SQL is the language WordPress uses to ask it for data. It holds posts, settings, product details and other data the site needs. How quickly it can find that data plays a part in how the site feels.

At HyperWP, we give SQL caching a generous share of server memory. The aim is to keep data used most often close at hand, so the database needs fewer reads from disk.

Caching data in RAM

The data a site reads most often forms part of its working set. Keeping those data blocks in RAM reduces the need to fetch them from storage on each request.

When a block is found in the cache, the read is a cache hit. The query can use that block from memory. A cache miss means the block must be fetched through the storage path; another cache may still hold it.

For example, InnoDB uses a buffer pool to do this in MySQL. It holds blocks of table and index data. An index helps the database find the rows it needs. The query still runs. The cache holds data blocks rather than a stored result for each SQL query. MySQL explains how the buffer pool works.

Cache size and repeat reads

When the cache fills, some blocks must be removed to make space. A block needed again may then have to be read back from storage.

Giving the cache more room can help it hold more of the data a site uses most. That is why memory for SQL caching is part of our approach to database tuning. Our aim is to reduce repeat disk reads as well as the time each read takes.

The benefit can show up on pages that query the database on each request, such as a search results page. Even when a whole page cannot be reused, some of its data may already be in memory.

Why fast drives still matter

A cache does not hold everything for all time. New data, a restart or a shift in demand can mean a read from disk. Changes still need to be saved too.

Our NVMe storage handles the work that reaches the drives. Memory helps avoid repeat reads where it can. They are two useful parts of the same job.

Memory allocation and query design

Good database tuning is not just a bigger cache. Sound indexes and queries that ask for only what they need help too. More RAM cannot turn a wasteful query into a good one.

The rest of the server also needs room to work. A cache set too large can leave too little RAM for other tasks. The operating system may then move memory pages to disk. This is known as swapping. This adds disk work and can slow the server. MariaDB explains the need to balance memory use.

Cache size is set on the server. The space it uses must leave room for other tasks. Active queries also need RAM, and their demand can rise under load.

Database caching within the hosting stack

WP Accelerate can serve cached results for suitable requests, reducing repeated PHP and database processing. Database caching can reduce disk reads when those requests still need to query the database.

For your site, the benefit is less time spent waiting on repeat reads. Results depend on the queries, the data in use and the load on the server. More cache space helps most when it keeps frequently used blocks in RAM.