Gerlando Falauto writes: I'm using SQLite in WAL mode in the following scenario:
- One writer which continuously writes new rows and deletes older ones - Multiple readers performing queries Writer and readers are running each in a separate process. The (Inactive) Journal Size limit is set to 100MB by pragmas and in normal usage this request seems to be honoured (i.e. the file does not grow bigger than that). I've however run into a situation where the .db-wal grows up to several Gigabytes, and the main .db file stays small. I believe this can be explained by some stale transaction in the system preventing the WAL pages from getting discarded (after being copied to the main database file). Any suggestion on how I can diagnose this situation, i.e. identify the offending process? If the various readers are short-lived processes (relative to the writer), you could treat this similarly to a memory leak. The diagnostic method used for those [a] can be adapted where you would wrap the transaction begin and end operations with registration/deregistration and see whether registrations exceed deregistrations upon program exit. [a. Generally, it is to record, in some kind of set or associative container, each allocated memory pointer upon the allocation, and upon corresponding frees, to remove the pointer from the set. Once that is setup, leaks are diagnosed by examining the set as main() exits (or whatever corresponds to main() in non-C derived languages.) If the set is not empty then, there is a leak. ] If the reader processes are long-lived and running on the Windows OS, another approach is viable. There is a handy set of system utilities which allow developers to look at (Windows) OS system objects ( https://docs.microsoft.com/en-us/sysinternals/downloads/winobj ) and see what handles are held by a process to such objects ( https://docs.microsoft.com/en-us/sysinternals/downloads/handle ). Since SQLite uses various system objects (the DB file and associated handles, and mutexes and associated handles) to coordinate DB access among processes, these utilities would be helpful for figuring out which process is failing to relinquish its claim to DB access. You could still use a registration system to narrow the problem further by attaching a debugger to the identified laggard process and examining its pending transaction registry. (Often, resource registration associates the allocation code file and line number with the handle or pointer representing resource.) _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
