On 1/8/62 19:49, Tammisalo Toni wrote:
Hi!I have an application which is using sqlite database in WAL mode. There is a need for periodic backups to a remote site without obstructing the normal operation. Both read and write access is required during the backup. At the moment I have system which first blocks checkpoints as otherwise backup was restarted too often. Backup is done to remote database implemented with sqlite vfs layer. What happens in my tests is that all sqlite3_backup_step()'s complete without actually writing anything to remote database. Only during last step all pages are written. This would be annoying from progress monitoring point of view. However, as database mutex is held during this time it actually blocks all access to the database for a long period of time. Changing the backup step size does not help as all this happens at last step regardless. So, is this a bug? I'm I doing something wrong? What I was hoping was that backup would not hold database mutex while it is writing to the other database or at least allow splitting the work with sqlite3_backup_step() so that at least some work could be done while backup is in progress. I actually have strong impression that this worked better with some older sqlite version. Currently using 3.27.2. Relevant part of the stack trace: #8 0x00000000005dc870 in sqlite3OsWrite (id=0x7fc1a4120f98, pBuf=0x7fc1a47b0e88, amt=<optimized out>, offset=<optimized out>) at sqlite3.c:22229 #9 pager_write_pagelist (pPager=0x7fc1a41216f8, pList=0x7fc1a47c0ec0) at sqlite3.c:54971 #10 0x00000000005bb1a5 in sqlite3PagerCommitPhaseOne (pPager=0x7fc1a41216f8, zMaster=0x0, noSync=0) at sqlite3.c:57050 #11 0x00000000005b968f in sqlite3_backup_step (p=0x7fc1a4056658, nPage=<optimized out>) at sqlite3.c:74033 Seems that all database pages are written out in pager_write_pagelist() in single loop.
The backup process writes through the cache of the destination database. So data is only written to disk when either (a) the cache is full or (b) the transaction is committed by the last sqlite3_backup_step() call. If you reduce the size of the cache used by the destination db writing should begin in an earlier sqlite3_backup_step() call.
Dan.
Also, I'm open to other suggestions. I was contemplating to just copy the database file directly while WAL checkpoints are not done but I read some comments that suggested that it would not be a safe to do that. Any help or suggestions would be appreciated! Toni Tammisalo ttamm...@iki.fi _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
