Remove sqlite index creation #453
Comments
|
I just checked and unfortunately, that index is still not available in the sqlite files located at https://dist.whosonfirst.org/sqlite/. I suspect there is a configuration problem on the server which generates the dist files, it possibly hasn't been updated: |
orangejulius
added a commit
that referenced
this issue
Aug 2, 2019
The creation of the `spr_obsolete` index is problematic for the PIP service and any of the admin lookup workers running in the importers. Because there are always multiple processes doing admin lookup, and each one tries to create the index if it doesn't exist, most end up failing. SQLite only allows a single write lock on the DB, so all but one will fail to achieve it. However, in my testing, all it takes to solve this gracefully is to wrap the index creation in a try/catch block. The index will be created by one of the worker processes, and all subsequent queries appear to have the performance improvements of the index (about 30-50% faster time to load admin data). Supercedes #431 Fixes #453 Connects #454 (we should not actually remove the index creation quite yet)
orangejulius
added a commit
that referenced
this issue
Aug 2, 2019
The creation of the `spr_obsolete` index is problematic for the PIP service and any of the admin lookup workers running in the importers. Because there are always multiple processes doing admin lookup, and each one tries to create the index if it doesn't exist, most end up failing. SQLite only allows a single write lock on the DB, so all but one will fail to achieve it. However, in my testing, all it takes to solve this gracefully is to wrap the index creation in a try/catch block. The index will be created by one of the worker processes, and all subsequent queries appear to have the performance improvements of the index (about 30-50% faster time to load admin data). Supersedes #431 Fixes #453 Connects #454 (we should not actually remove the index creation quite yet)
orangejulius
added a commit
that referenced
this issue
Aug 2, 2019
The creation of the `spr_obsolete` index is problematic for the PIP service and any of the admin lookup workers running in the importers. Because there are always multiple processes doing admin lookup, and each one tries to create the index if it doesn't exist, most end up failing. SQLite only allows a single write lock on the DB, so all but one will fail to achieve it. However, in my testing, all it takes to solve this gracefully is to wrap the index creation in a try/catch block. The index will be created by one of the worker processes, and all subsequent queries appear to have the performance improvements of the index (about 30-50% faster time to load admin data). Supersedes #431 Fixes #454 Connects #453 (we should not actually remove the index creation quite yet)
|
All the country-specific download files now have this index. The global file ( |
|
This will be possible once #487 is merged, the |
|
Finally done in #487. This importer now downloads data from builds sponsored by Geocode Earth, which are built with the |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
whosonfirst/src/components/sqliteStream.js
Lines 9 to 14 in 31112fd
The mentioned PR has already been merged: whosonfirst/go-whosonfirst-sqlite-features#4
The text was updated successfully, but these errors were encountered: