Chapter 6. Caches

Table data and indexes are normally written on mass memories, such as disks, SSDs, or flash memories. However, accessing these media is a slow process; if the server has to do it too often, the input or output is probably the bottleneck. To avoid accessing disks, MariaDB and storage engines have several caches that a DBA should know about. The following topics will be covered in this chapter:

• The InnoDB buffer pool and doublewrite buffer
• The MyISAM key cache
• The Aria page cache
• The query and subquery cache
• The table open cache
• The main per-session buffers

Note

Before beginning our discussion on the caches, a warning is needed. Many authoritative resources, such as the MariaDB Knowledge Base or the MySQL manual, suggest dedicating a huge amount of memory to the InnoDB buffer pool or to the default storage engine's main cache if most tables are not InnoDB tables. Many articles suggest dedicating about 70 percent or even 80 percent of the memory to the buffer pool...