SQLite Forum

Why this code increase cache memory?
Login

Why this code increase cache memory?

(1) By mngz (kwontmdwo) on 2021-02-16 03:00:12 [source]

Hello. I use sqlite3 to run an application written in c language on linux. However, there is an issue that the cache size increases when the sqlite3 function is used. I did c programming on linux. When you start the program, you can see the total cache size gradually increase in the top command.
I found that sqlite3_exec() increases the cache. Did I code something wrong? Please tell me how to prevent it.
There is my code.


#include <sqlite3.h>
#include <stdio.h>

int main()
{
    int cnt = 0;
    sqlite3 *db;
    char *errMsg = NULL;

    sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
    sqlite3_exec(db, "CREATE TABLE test_tbl (STRING TEXT, NUMBER INTERGER)", NULL, NULL, &errMsg);
    
    for (cnt = 0;; cnt++)
    {
        char buf[20];
        sprintf(buf, "text%d", cnt++);

        char sql[100];
        sprintf(sql, "INSERT INTO test_tbl (STRING, NUMBER) VALUES ('%s', %d)", buf, cnt++);
        int ret = sqlite3_exec(db, sql, NULL, NULL, &errMsg);
        if (errMsg)
        {
            sqlite3_free(errMsg);
            errMsg = NULL;
        }
        usleep(10 * 1000);
    }
    sqlite3_close(db);
    printf("End!!!\n");

    return 0;
}

(2) By Keith Medcalf (kmedcalf) on 2021-02-16 03:25:41 in reply to 1 [link] [source]

Memory for which you have paid that is not being used is a waste of money. If you do not want to cache disk I/O in RAM then either tell the Operating System not to do that, or sell some memory.

(4) By mngz (kwontmdwo) on 2021-02-16 03:56:10 in reply to 2 [link] [source]

Thanks your apply.

But I don't know how to handle about your mention. I'm appreciated if you give me example like linux commands or code.

(6) By Keith Medcalf (kmedcalf) on 2021-02-16 04:21:30 in reply to 4 [link] [source]

As far as I know Linux will use all memory that is not being used for something more important as filesystem cache. This is generally true of all current Operating Systems.

Perhaps you can ask on a Linux support forum for how to limit the filesystem cache -- this is not a general Linux support forum, this forum is specific to SQLite -- the issue you are complaining about is an Operating System issue.

(7) By Keith Medcalf (kmedcalf) on 2021-02-16 04:31:18 in reply to 4 [link] [source]

I will assume that you mean "cached" as in the following, which has nothing whatsoever to do with SQLite3.

top - 21:29:15 up 14 days,  3:02,  1 user,  load average: 0.14, 0.03, 0.01
Tasks: 145 total,   1 running,  63 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.5%us,  0.5%sy,  0.0%ni, 99.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4012988k total,  2236848k used,  1776140k free,   183664k buffers
Swap:  2097148k total,        0k used,  2097148k free,  1747436k cached

(8) By mngz (kwontmdwo) on 2021-02-16 04:53:34 in reply to 7 [link] [source]

Right, I said about "cached".

Mem: 16216K used, 41672K free, 64K shrd, 0K buff, 4140K cached
CPU:   1% usr  61% sys   0% nic  37% idle   0% io   0% irq   0% sirq
Load average: 1.28 0.93 0.44 3/48 908

But I don't understand why it isn't increased when I call sqlite3_get_table(). It only increases if I call sqlite3_exec()

(10) By Keith Medcalf (kmedcalf) on 2021-02-16 08:09:59 in reply to 8 [link] [source]

The particular call makes no difference. If the filesystem data is already in the cache then the cache size will not increase if no additional filesystem data is read. However, when you INSERT data you are writing to the filesystem and if that causes the file to get bigger then that additional data will be added to the filesystem cache in order to avoid the I/O if you happen to access it again.

This is quite apart from anything to do with SQLite3 or SQLite3's application cache. Everything and anything that reads and writes the filesystem will exhibit the same behaviour.

(3.1) By Larry Brasfield (larrybr) on 2021-02-16 03:42:34 edited from 3.0 in reply to 1 [link] [source]

Expanding on Keith's advice a bit:

I do not see that what you are calling "total cache size" is going to grow without bound, and it should not tax any recent vintage machines. However, you are keeping the process running your main() very busy, and you do have a table growing without bound. [a] The SQLite library is going to keep many of its b-tree pages in memory, using its own cache. But it is designed to not be a memory pig under usage such as yours.

[a. The actual bound seems to be a process crash, although hard to predict exactly. ]

Until you relate the process memory use versus time, showing linear growth instead of approaching some reasonable upper bound (more slowly as it is neared), I am disinclined to help you worry about it. If you really want to, the docs show build-time options for controlling various limits the SQLite library observes as it is used. Look them up if you care so much.

BTW, your table names and types are either overly creative or uninspiring. I suspect you will need to study SQLite's type scheme before undertaking serious use of the library.

(5) By mngz (kwontmdwo) on 2021-02-16 04:13:34 in reply to 3.1 [link] [source]

As I keep monitoring the results of the top command, I can see that the cache is gradually increasing in 4K increments. Also, if I do not clear the cache separately, the free memory becomes very short and my device overall slows down.

Of course, the code I have posted is not my actual code. I just wanted to show that simple code like this increases the cache memory. You don't have to care about names of my sample code.

Actually I implemented a reader for the access system. This performs a similar action as the event should be saved when the card is tapped. I am concerned that my device slows down when running for a long time due to insufficient cache.

(9) By Gunter Hick (gunter_hick) on 2021-02-16 07:41:15 in reply to 5 [link] [source]

You can use the valgrind tool suite to find out what part of the code is actually allocating memory and if it is actually leaked. There are documented mechanisms to limit the memory used by SQLite, including limiting SQLite to a pre-allocated memory buffer of a size determined by the application, but this may lead to queries executing slowly (or not at all) when running into memory constraints.

(11) By Keith Medcalf (kmedcalf) on 2021-02-16 08:16:49 in reply to 5 [link] [source]

Actually I implemented a reader for the access system. This performs a similar action as the event should be saved when the card is tapped. I am concerned that my device slows down when running for a long time due to insufficient cache.

The filesystem cache contains Most Recently Used file fragments. If you have insufficient cache the solution is to buy more RAM. All RAM that is not being used for another purpose is used for the cache. Every I/O is routed through the cache so more recent I/O'd filesystem fragments replace older fragments.

(12) By Larry Brasfield (larrybr) on 2021-02-16 11:03:15 in reply to 5 [link] [source]

As I keep monitoring the results of the top command, I can see that the cache is gradually increasing in 4K increments. Also, if I do not clear the cache separately, the free memory becomes very short and my device overall slows down.

Your posted code does not leak memory. The SQLite library does not leak memory. As others have explained (or intimated), the file system driver uses a caching strategy to improve access speed for application file I/O. Since you have an ever-growing DB file, you should expect the OS to expand its device block cache use to balance that I/O demand against other resource demands.

As Gunter and I mentioned, you can affect the SQLite library's memory use. Its defaults may be inappropriate for your system if it should be called a "device".

You have not indicated whether you are observing linear cache growth over time or merely growth which slows and stops (on average) as system resources become less available. The former is a problem; the latter is normal operation.

... not my actual code ... don't have to care about names ...

It did not look like a serious attempt to do anything useful or unusual. Long-running processes which continually grow database files are a common use case. You seem to think that your code demonstrates a library flaw, but it does not. The library code cannot perform the resource use-versus-demand balancing that is the operating system's responsibility.