I'll take a look. Thanks - Michael Condon Mission Critical Solutions (503)726-4685 -------- Original Message -------- > From: sqlite-users-requ...@mailinglists.sqlite.org > Sent: Saturday, June 03, 2017 5:00 AM > To: sqlite-users@mailinglists.sqlite.org > Subject: SPAM-LOW: sqlite-users Digest, Vol 114, Issue 3 > > Send sqlite-users mailing list submissions to sqlite-users@mailinglists.sqlite.org To subscribe or unsubscribe via the World Wide Web, visit http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users or, via email, send a message with subject or body 'help' to sqlite-users-requ...@mailinglists.sqlite.org You can reach the person managing the list at sqlite-users-ow...@mailinglists.sqlite.org When replying, please edit your Subject line so it is more specific than "Re: Contents of sqlite-users digest..." Today's Topics: 1. WAL checkpoint starved? (Daniel Polski) 2. Re: WAL checkpoint starved? (Richard Hipp) 3. Re: WAL checkpoint starved? (Clemens Ladisch) 4. memory usage after close database on linux arm (Stephane Guibert) 5. Re: memory usage after close database on linux arm (Clemens Ladisch) ---------------------------------------------------------------------- Message: 1 Date: Fri, 2 Jun 2017 16:00:38 +0200 From: Daniel Polski <dan...@agelektronik.se> To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] WAL checkpoint starved? Message-ID: <40d767cb-9f0f-7e06-78af-9a95a4ab6...@agelektronik.se> Content-Type: text/plain; charset=windows-1252; format=flowed I've found something weird in a log from a client. Normally our WAL files are < 100kB, but in this log I noticed the file was >40MB. This was totally unexpected since we run this call every minute: int val = sqlite3_wal_checkpoint_v2(myDB->getDbPointer(), NULL, SQLITE_CHECKPOINT_TRUNCATE, &wal_size, &wal_checkpointed); and I thought that SQLITE_CHECKPOINT_TRUNCATE would force the checkpoint to completion. We haven't turned off automatic checkpoints, just added the above to make them happen more often than default, and be called from a specific thread. Some more background information: The sqlite version in the client is 3.17.0. 1. Operation - We normally run ~18 threads with "own" connections open to the sqlite database. They read concurrently, but all writes are protected by a mutex and all insert/update/deletes starts with "begin immediate transaction". We do -not- ensure there are "reader gaps", since I believe that shouldn't be necessary if using truncate mode. 2. Backup - About every hour we run a separate process, which uses the backup api to create a backup (this process is not obeying the mutex described above and runs in parallell with the normal operation). 3. GUI/API - Uses one consistent database connection in PHP - Serveral instances access the one database connection - Mostly read operations. The write operations are not manually wrapped inside begin & commit since they're all single commands. First I suspected the main thread to hang, so the call to sqlite3_wal_checkpoint_v2 stopped executing every minute. But then the the auto checkpointing should take over since that's not turned off. So now I'm thinking that something is blocking the checkpoint to be able to complete, but can't figure out what that could be or how to find what it is, if it is so. Any ideas why I can end up with that large WAL file, except the information in the chapter "Avoiding Excessively Large WAL Files" describes? (Or if I have misunderstood the information there..?) ------------------------------ Message: 2 Date: Fri, 2 Jun 2017 10:07:38 -0400 From: Richard Hipp <d...@sqlite.org> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] WAL checkpoint starved? Message-ID: <CALwJ=Mxzuou69v+SJNv+WNk87Tv1wzm++c1Feup58rCR6SO=c...@mail.gmail.com> Content-Type: text/plain; charset="UTF-8" On 6/2/17, Daniel Polski <dan...@agelektronik.se> wrote: > I've found something weird in a log from a client. > Normally our WAL files are < 100kB, but in this log I noticed the file > was >40MB. This was totally unexpected since we run this call every minute: > > int val = sqlite3_wal_checkpoint_v2(myDB->getDbPointer(), NULL, > SQLITE_CHECKPOINT_TRUNCATE, &wal_size, &wal_checkpointed); > > and I thought that SQLITE_CHECKPOINT_TRUNCATE would force the checkpoint > to completion. We haven't turned off automatic checkpoints, just added > the above to make them happen more often than default, and be called > from a specific thread. Do you have a busy callback handler registered (https://sqlite.org/c3ref/busy_handler.html) and are you checking the return code from sqlite3_wal_callback_v2()? -- D. Richard Hipp d...@sqlite.org ------------------------------ Message: 3 Date: Fri, 2 Jun 2017 16:07:44 +0200 From: Clemens Ladisch <clem...@ladisch.de> To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] WAL checkpoint starved? Message-ID: <02e5d5e1-71a6-a547-32d1-3c8af392d...@ladisch.de> Content-Type: text/plain; charset=us-ascii Daniel Polski wrote: > Any ideas why I can end up with that large WAL file Sounds like checkpoint starvation. Does the checkpoint call actually succeed? Regards, Clemens ------------------------------ Message: 4 Date: Fri, 2 Jun 2017 09:18:40 +0000 From: Stephane Guibert <stephane.guib...@external.cp.com> To: "sqlite-users@mailinglists.sqlite.org" <sqlite-users@mailinglists.sqlite.org> Subject: [sqlite] memory usage after close database on linux arm Message-ID: <am4pr03mb17938e3a4fc23d455dea37bad1...@am4pr03mb1793.eurprd03.prod.outlook. com> Content-Type: text/plain; charset="us-ascii" Hi SQLite creators, for embedded Linux 2.6.35.3 on armV5, I get memory usage not freed at close(ltib compilation environment). with this following test ( only one connection to the database ): 1) open database 2) prepare statement 3) SQL request inside begin commit 4) finalize statement 5) close database 6 ) loop to 1 ) the return is SQLITE_OK at each step: do{ sqlite3_open_v2(...,&db,SQLITE_OPEN_READWRITE,0); sqlite3_exec(db,"PRAGMA journal_mode = MEMORY",0,0,0); sqlite3_prepare_v2(db... sqlite3_step( statement Begin //BEGIN EXCLUSIVE TRANSACTION sqlite3_reset( statement request sqlite3_step( statement request sqlite3_column_int64( statement request sqlite3_step( statement Commit sqlite3_finalize(... sqlite3_close_v2(... }while I have got memory usage not freed after the close: the sqlite3_memory_used() give 0 before open give 127376 after the first open give 133296 after the close the sqlite3_memory_used() give 133296 before open give 260624 after the second open give 266544 after the close the sqlite3_memory_used() give 266544 before open give 393872 after the third open give 399792 after the close /proc/2365/status Tgid: 2365 Pid: 2365 PPid: 1 TracerPid: 0 Uid: 0 0 0 0 Gid: 0 0 0 0 FDSize: 32 Groups: 0 1 2 3 4 6 10 VmPeak: 3316 kB VmSize: 3316 kB VmLck: 0 kB VmHWM: 2000 kB VmRSS: 2000 kB VmData: 964 kB VmStk: 136 kB VmExe: 552 kB VmLib: 1464 kB VmPTE: 6 kB VmSwap: 0 kB Threads: 1 Tgid: 2365 Pid: 2365 PPid: 1 TracerPid: 0 Uid: 0 0 0 0 Gid: 0 0 0 0 FDSize: 32 Groups: 0 1 2 3 4 6 10 VmPeak: 3580 kB VmSize: 3580 kB VmLck: 0 kB VmHWM: 2256 kB VmRSS: 2256 kB VmData: 1228 kB VmStk: 136 kB VmExe: 552 kB VmLib: 1464 kB VmPTE: 6 kB VmSwap: 0 kB Threads: 1 inside /proc/2365/fd/ a new file descriptor is added at each open database, but never deleted at close database. same behaviour with sqlite 3.9.2 and 3.18.0 ( compiled with -DSQLITE_CORE -DNDEBUG=1 -DSQLITE_THREADSAFE=2 -DSQLITE_ENABLE_ATOMIC_WRITE -DSQLITE_DEFAULT_AUTOMATIC_INDEX=0 -DSQLITE_ENABLE_MEMORY_MANAGEMENT -DSQLITE_TEMP_STORE=3) Is any workarounds are available to close the database and freed all its memories allocated by sqlite inside the same application (without restart the application process)? Regards, Stephane ------------------------------ Message: 5 Date: Sat, 3 Jun 2017 13:24:59 +0200 From: Clemens Ladisch <clem...@ladisch.de> To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] memory usage after close database on linux arm Message-ID: <0804f258-850e-4a40-9a77-5e9cf6e32...@ladisch.de> Content-Type: text/plain; charset=utf-8 Stephane Guibert wrote: > I get memory usage not freed at close. > > the return is SQLITE_OK at each step: > > do{ > sqlite3_open_v2(...,&db,SQLITE_OPEN_READWRITE,0); > sqlite3_exec(db,"PRAGMA journal_mode = MEMORY",0,0,0); > sqlite3_prepare_v2(db... > sqlite3_step( statement Begin //BEGIN EXCLUSIVE TRANSACTION > sqlite3_reset( statement request > sqlite3_step( statement request > sqlite3_column_int64( statement request > sqlite3_step( statement Commit > sqlite3_finalize(... > sqlite3_close_v2(... Use sqlite3_close() instead to get an error if there are unfinalized statements. (sqlite3_close_v2() is intended for garbage-collected languages where the statement might be destroyed after the connection.) Regards, Clemens ------------------------------ Subject: Digest Footer _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ------------------------------ End of sqlite-users Digest, Vol 114, Issue 3 ********************************************
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
