Sizing Aria Pagecache

Last updated on 12 Giugno 2021

I wrote two articles about correctly sizing InnoDB buffer pool: Is InnoDB Buffer Pool big enough? and Can we shrink InnoDB Buffer Pool?.

Aria is a MariaDB storage engine that is used for internal temporary tables and, potentially, tables created by the user. Its pagecache is the equivalent of InnoDB buffer pool, and this article is about sizing it properly. Note that to adjust the pagecache we need to change the value of  aria_pagecache_buffer_size  in the configuration file and restart MariaDB.

Sizing Aria pagecache
Blixy is determining the proper size for Aria pagecache

Should we really care about Aria?

All MariaDB users use Aria, but many of them don’t know that. Aria is used for:

  • Some system tables;
  • Internal temporary tables;
  • Tables explicitly created with Aria.

Rarely people create Aria tables explicitly. For other uses of Aria, the default size of the pagecache is usually big enough. Most of the times it could even be smaller, but try not to overoptimise.

Check your pagecache using the hints in this article. If your pagecache is clearly oversized (say, double of what it should be), consider shrinking it. If it’s undersized, definitely resize it. If you set its size manually, check if your choice was wise.

Checking the size of Aria Pagecache

Here I will describe the status variables that we want to check to verify if the pagecache is correctly sized. How to check them is up to you. I suggest to use a proper monitoring solution to see how these values evolve over time, and their spikes.

Unused blocks

The pagecache is made of blocks. Blocks are not completely used, a portion of them should always be free.

Compare  Aria_pagecache_blocks_used  with  Aria_pagecache_blocks_unused . If unused blocks are close to zero, the pagecache needs to be larger. If unused blocks are consistently a big portion of the total,you can shrink the pagecache.

Pagecache efficiency

The purpose of the pagecache is avoiding a high number of disk operations (reads and writes). If a lot of data are still being read from disk, the pagecache is not big enough. Compare  Aria_pagecache_read_requests  (the reads requested to Aria) with  Aria_pagecache_reads  (the number of reads that hit the disk because the desired data was not cached).

Flushed blocks

Look at  Aria_pagecache_blocks_not_flushed . If the number is high and it decreases suddenly, it means that Aria had to flush pages to make room for new data. This could mean that Aria cache is not big enough.

MDEVs related to Aria Pagecache

If you care about Aria Pagecache, the following tasks are important:

If you make massive use of Aria you may also care about this, but it’s much less likely to be implemented:

About MyISAM

Aria was conceived as a modern, crash-safe MyISAM. These engines are similar in many respects.

MyISAM has a Key Cache, which is almost equivalent to Aria Pagecache. The big difference is that, while the Pagecache stored both indexes and data, the Key Cache only stores indexes. For each Aria status variable mentioned in this article, there is an equivalent MyISAM variables that gives us some information about the Key Cache.

If you use MyISAM on purpose, consider using InnoDB instead. There aren’t many good reasons to choose MyISAM instead. That said, if you use MyISAM for a good reason, check if you can use Aria instead. It’s very possible you can’t, because Aria is sensibly slower on write. But, as a general rule, the least storage engines you use in the same server the better.

See also



We discussed the methods I know to check if Aria pagecache is reasonably sized. However, don’t be too paranoid about it. You surely should enlarge the pagecache if it is not big enough, but that’s not a common situation. Shrinking it could also make sense to save resources and improve performance, but if the gain is small it’s hardly worth the effort.

As part of MariaDB Health Checks, I check Aria pagecache size is set properly.

Toodle pip,

A proposito di Federico Razzoli

Federico is Vettabase Ltd director, and he's an expert database consultant specialised in the MariaDB and MySQL ecosystems. Federico is also a free software supporter. He believes in information sharing and reciprocal help. He thinks that multiple organisations contributing the same repositoy and benefiting from each other's work are better than many companies re-inventing the wheel. He thinks this view is not just compatible with business, but it's today and tomorrow's way of doing business. Federico also loves beers from the North of Europe, wines from Italy, bagpipes, nature hiking and the history of computing.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *