[MapProxy] FYI: Possible root cause of 'sqlite3.OperationalError: database is locked'

[BERÉNYI Attila]

I'm so delighted that someone is actually have the time to investigate this
in depth! Keep up the good work guys, it is really appreciated!

Cheers,
Attila

Just van den Broecke <just at justobjects.nl> ezt írta (időpont: 2015. márc.
17., K, 15:27):

> Hi Oliver,
>
> 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
> Python.
>
> 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
> an issue/PR.
>
> Best,
>
> Just
>
> >
> >
> >
> > Regards,
> > Oliver
> >
>
>
>
> _______________________________________________
> MapProxy mailing list
> MapProxy at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapproxy
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapproxy/attachments/20150317/9cabf818/attachment.html>