Schedule Meeting


Finding an optimal size for Aria Pagecache

by | Feb 18, 2021 | MariaDB Storage Engines, MariaDB

Need Help?  Click Here for Expert Support

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 ENGINE=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 it’s better to refrain from overoptimising.

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. Not all blocks should be 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 to avoid having 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, at any point in time, the number is high and it decreases suddenly, it means that Aria had to flush pages to make room for new data. This probably means that Aria cache is not big enough.

JIRA tasks related to Aria Pagecache

The MariaDB team has public JIRA projects. We can keep an eye on them to follow the development of new features and bug fixing. 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:

Finally, this task will make it possible to use something other than Aria for internal temporary tables:

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 stores both indexes and data, the Key Cache only stores indexes. For each Aria status variable mentioned in this article, there is an equivalent MyISAM variable 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 nowadays. 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 spend too much time on this. You surely should enlarge the pagecache if it is not big enough, but that’s not a common situation. If it’s heavily oversized, shrinking it could also make sense to save resources and improve performance. But normally it’s not worth the effort.

For a complete review of your MariaDB configuration, consider our MariaDB Health Checks.


Did you like this article?

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions:

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

Coming up next, a ColumnStore webinar

Coming up next, a ColumnStore webinar

The 27th is fast approaching but you can still signup to our live webinar where we will be exploring the benefits of time series data using MariaDB ColumnStore. We will be jumping into a live demo with some example data and queries simulating high volume time series...

The benefits of MariaDB ColumnStore

The benefits of MariaDB ColumnStore

Last week Richard announced our projects on MariaDB ColumnStore. Take a look at his great post, if you didn't already. Since then, I've got some questions from customers, colleagues and friends: why did you guys decide to robustly invest into ColumnStore, and offer...

Let’s go, MariaDB ColumnStore at Vettabase!

Let’s go, MariaDB ColumnStore at Vettabase!

I have been an avid user of Columnar based storage engines for as long as I have been a DBA. The advantage is instantly obvious once you and your teams start to write or convert queries which previously took tens of minutes, are now returning in fractions of a second....


Need Help?  Click Here for Expert Support


Submit a Comment

Your email address will not be published. Required fields are marked *