3

I use an in-memory sqlite database for some queueing of stuff between multiple threads and therefore we have a lot of writes, reads and deletes. But there are never more that a few KB in the database at a time. Now some customers complain memory usage after longer usage and I wonder if the in-memory databases of sqlite are also affected by fragmentation? Is there the need to VACUUM in-memory-databases, too?

This database is utilized by multiple threads and I can not lock all of them for some VACUUM. Or do all the connections simply wait until VACUUM is finished and then come back as usual? And what happens if another thread is just doing a transaction while I start VACUUM from my thread?

Side-Note: I already check for memory leaks in my program, but this is another option I want to consider because valgrind does not find any issues for the moment. And such sqlite storage memory usage is, of course, not a memory leak in traditional way.

Volker
  • 428
  • 4
  • 15
  • Can you provide your evidence for "there are never more that a few KB in the database at a time"? Especially for the cases of relevantly long runtime. – Yunnosch Oct 27 '21 at 10:19
  • @Yunnosch The thing is that this table holds jobs that are handled by threads. If nothing is to do, the table is empty. So only high load conditions can increase that drastically. But this is not the case for the customers complaining. – Volker Oct 27 '21 at 10:32
  • "If nothing is to do, the table is empty." That does however not mean that the memory consumption of the database approaches zero in that case. There are meta data to consider. Also depending on structure and mechanisms, there might be obsolete entries which take memory but would register as "empty" in your statement. So while your reasoning seems convincing, I am shooting for measurements you made. – Yunnosch Oct 27 '21 at 10:58
  • You can do some logging of [`sqlite3_memory_used()` and `sqlite3_memory_highwater()`](https://www.sqlite.org/c3ref/memory_highwater.html) to see if it's sqlite that's responsible for this memory increase. – Shawn Oct 27 '21 at 21:00
  • (I also have to wonder if sqlite is the most appropriate tool for thread-based queuing...) – Shawn Oct 27 '21 at 21:02
  • I really tried to find info if `auto_vacuum` pragma is recommended for in-memory databases, but I can not find any reliable information on this. I will see if I can get some info using `sqlite3_memory_used()` function. – Volker Oct 28 '21 at 09:45
  • Dear @Volker, we had hit very similar issue as well (sqlcipher based on sqlite3, little data in db, long runtime, several GB memory usage). Did you find out something related to your issue? – Roman Pavelka May 03 '22 at 08:03
  • 1
    @RomanPavelka Sadly not. We can not reproduce by ourselves and the customers gave us no chance yet to validate further. A few of them now restart daily, which re-creates the memory tables and solves the issue. But we are not very happy about this... – Volker May 04 '22 at 09:42

0 Answers0