[MapProxy] FYI: Possible root cause of 'sqlite3.OperationalError: database is locked'
Just van den Broecke
just at justobjects.nl
Tue Mar 17 07:27:24 PDT 2015
On 17-03-15 10:39, Oliver Tonnhofer wrote:
> Hi Just,
>> On 16.03.2015, at 14:33, Just van den Broecke <just at justobjects.nl> wrote:
>> I begin to suspect that USLEEP is not the cause here but the underlying filesystem. I've found other causes of this error when an sqlite db is on a networked filesystem like NFS, CIFS/Samba. Several refs found and http://sqlite.org/faq.html#q5. Not a good idea anyway, but it hinted me to look into that direction.
> Yes, I think USLEEP is only one cause. It even fails on my system if I put enough pressure on SQLite (500 single inserts/s are still working).
> Here is a small test script: https://gist.github.com/olt/fcef7445657be3b60682
> You can change the number of concurrent writers or the number of “tiles” for each writer and a "process delay".
Yes, I can even trigger the error even on the system where it worked
before (Ubuntu 14.04, no LVM) by increasing mapproxy-seed --concurrency N.
Did some more investigation:
- tried same solution (global lock on DB connection) as in
http://beets.radbox.org/blog/sqlite-nightmare.html, but realized MP uses
MultiProcessing iso MultiThreading
- compiled and locally installed from the sqlite3 source version
(apt-get source libsqlite3-0). After 'configure' I see HAVE_USLEEP=1
defined, but the problem is still there even at concurrency 1. I can
confirm that my local libsqlite3.so is used and usleep() is called with
printf's in sqlite3.c. Sleeptime is at most 100000 microsecs (100ms).
Even with --concurrency 1, I see many usleep()'s. There is no access
other than the seeder. Also caught the error with your test script.
- applied exception handling and a retrycounter/while loop in mbtiles.py
store_tile() and load_tile(s)(). That suppressed the error and
eventually resulted in successful writes/loads but still very slow and
not elegant. (optimization: is_cached() in this case could probably
better just do SELECT COUNT() as only the presence of the tile is checked).
- The MapBox folks seem to have a similar issue:
https://github.com/mapbox/mapbox-gl-native/issues/582 but are not using
All in all, I still think that HAVE_USLEEP being undefined is not the
issue here, but somehow a very slow write,lock,flush or
fsync-interaction with the LVM. mbtiles.py could be made more robust by
catching exceptions in load_tile(s)() and some sleep/retry mechanisms,
but if the FS is simply too slow an mbtiles/sqlite cache is suboptimal.
If needed I could add the retry and SELECT COUNT() to the MP project via
More information about the MapProxy