An Overview of Sqlite Cache Size Performance

Introduction

Before to discuss about Sqlite Cache Size Performance we will take a look on its internal part. the pager in Sqlite architecture is mainly responsible for providing page cache to the B-tree in order to achieve optimal performance. In a transaction process, the pages that need to be used for reading or writing are accessed from the in-memory cache. The pages which have been already added to the cache are not added again. The changes made to the caches are then updated afterwards. Earlier in Sqlite, each open connection to the database used to have its own cache, even if there were multiple connections for the same database.In the subsequent versions, the concept of shared cache came into existence. Shared cache enabled the user to have same cache for all the threads in all the processes to have a single shared cache page.

Sqlite Cache Size Performance

By default the maximum number of pages which a cache page can hold is 2000.This value can be changed according to the need, with the help of PRAGMA. The syntax for changing this value is:

At times it happens that the pages that are required by the Pager are not present in the cache and the cache has already gotten full. At such times, the Pager takes out the old pages from the cache and replaces them with the new one.In case the pages that have been taken out have been updated, then these changes are to be written in the database by the Pager. This process is referred as Page swapping.

Page swapping if done excessively, may impact the performance of the database functioning. Therefore, the maximum cache size should be set such that the performance is not compromised. For example, in case the size of a page cache is 100 and the database contains a table of 5000 items in the database that is used frequently. This size of the page cache is going to create problems and hence will affect the performance of Sqlite database.

More On Cache_Size PRAGMA

Changes made by the cache_size Pragma persists till the Sqlite database connection is present. Increase in the size of the cache page results in the increase of the performance of Sqlite database. However, care should be taken when a large sized cache is concerned. If the size of the cache exceeds the physical memory currently available, it will lead to decline in the performance of the Sqlite database. In actual, the performance is likely to be lower compared to the level of performance when used with small sized cache page.

The amount of memory occupied by the cache page is equal to the database size+ overhead+ size of the cache page.
Therefore, in order to optimize the Sqlite cache size performance, care should be taken regarding the size of the cache pages.