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

[Just van den Broecke]

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
>