search

digidem / mapeo-map-server

Offline map style and tile server
MIT License
5 stars 2 forks source link

Performance issue when deleting style #89

Closed achou11 closed 9 months ago

achou11 commented 2 years ago

Calling api.deleteStyle can take a long time under the following conditions:

Replicate this by doing two mbtiles imports, waiting for them to complete, and deleting either one of the styles associated with it. The example I tried imported a small map (~10 MB) and the Terrastories default offline map (~150 MB), and attempting to delete either map style.

Bottleneck seems to be this query (takes about 16 seconds on my machine):

https://github.com/digidem/mapeo-map-server/blob/8456528d3633e774bc3b2d7b24fe0590252bbb44/src/api/styles.ts#L350

The query is fine if let's say, we only import one large tileset and then delete the style for it afterwards (query of interest takes about 1 second in this case). Just the existence of another tileset is what causes perf issues.

luandro commented 1 year ago

Getting the spinner of death after trying to delete a map: image

Mapeo just stays on that screen and map isn't deleted.

EvanHahn commented 9 months ago

I don't have a solution yet, but (1) I agree that the DELETE FROM TileData query does appear to be the culprit (2) removing this foreign key constraint on Tile basically fixes the problem:

CONSTRAINT "Tile_tileHash_tilesetId_fkey" FOREIGN KEY ("tileHash", "tilesetId") REFERENCES "TileData" ("tileHash", "tilesetId") ON DELETE RESTRICT ON UPDATE CASCADE,

So far, I'm able to reproduce this in our repo but not in an isolated SQLite test case.

I don't know what to do about this yet but I'll keep working. Should we remove the constraint altogether? Is there a way to make the deletion faster despite the constraint? Hopefully I'll have answers tomorrow.

EvanHahn commented 9 months ago

I think I figured out a solution. See #116.

luandro commented 9 months ago

This issue seems to the same as when we have multiple tiles on a low end phone. My Pixel 5 doesn't crash, but just tested on my Pixel 2, and after loading 3 ~300mb mbtiles to Mapeo, it becomes unusable, constantly on loading state and crashing.

Some logs since loading the third tileset:

02-07 13:15:09.535  1060  2096 D installd: Purging /data/data/com.mapeo/cache/last-run-info size=8192 mod=1707322023 from UID=10190 used=1307193344 quota=67108864 ratio=194786
02-07 13:15:09.536  1060  2096 D installd: Purging /data/data/com.mapeo/cache/user-info size=4096 mod=1707322023 from UID=10190 used=1307185152 quota=67108864 ratio=194785
02-07 13:15:09.536  1060  2096 D installd: Purging /data/data/com.mapeo/cache/bugsnag-native size=8192 mod=1707322023 from UID=10190 used=1307181056 quota=67108864 ratio=194785
02-07 13:15:09.536  1060  2096 D installd: Purging /data/data/com.mapeo/cache/bugsnag-sessions size=8192 mod=1707322023 from UID=10190 used=1307172864 quota=67108864 ratio=194783
02-07 13:15:09.536  1060  2096 D installd: Purging /data/data/com.mapeo/cache/bugsnag-errors size=8192 mod=1707322023 from UID=10190 used=1307164672 quota=67108864 ratio=194782
02-07 13:15:09.536  1060  2096 D installd: Purging /data/data/com.mapeo/cache/upgrades/tmp size=8192 mod=1707322030 from UID=10190 used=1307156480 quota=67108864 ratio=194781
02-07 13:15:09.539  1060  2096 D installd: Purging /data/data/com.mapeo/cache/upgrades size=8192 mod=1707322030 from UID=10190 used=1307148288 quota=67108864 ratio=194780
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/2b9fd63907c1e8ad9e0b48d92aefb92f.0 size=8192 mod=1707322031 from UID=10190 used=1307140096 quota=67108864 ratio=194779
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/2b9fd63907c1e8ad9e0b48d92aefb92f.1 size=8192 mod=1707322031 from UID=10190 used=1307131904 quota=67108864 ratio=194777
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/3a830b6a2031f81cea05dda472d3c2de.0 size=8192 mod=1707322031 from UID=10190 used=1307123712 quota=67108864 ratio=194776
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/2578e49e42d0ef952fd26dc1d2d5be9a.0 size=8192 mod=1707322031 from UID=10190 used=1307115520 quota=67108864 ratio=194775
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/9b6a061bc1128547e18c3bb3360c524b.1 size=20480 mod=1707322031 from UID=10190 used=1307107328 quota=67108864 ratio=194774
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/3a830b6a2031f81cea05dda472d3c2de.1 size=20480 mod=1707322031 from UID=10190 used=1307086848 quota=67108864 ratio=194771
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/2578e49e42d0ef952fd26dc1d2d5be9a.1 size=69632 mod=1707322031 from UID=10190 used=1307066368 quota=67108864 ratio=194768
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/9b6a061bc1128547e18c3bb3360c524b.0 size=8192 mod=1707322031 from UID=10190 used=1306996736 quota=67108864 ratio=194757
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/WebView size=8192 mod=1707322031 from UID=10190 used=1306988544 quota=67108864 ratio=194756
02-07 13:15:09.577  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/8a977275984b233a6b5c66e0c89c3bd4.0 size=12288 mod=1707322032 from UID=10190 used=1306980352 quota=67108864 ratio=194755
02-07 13:15:09.577  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/8a977275984b233a6b5c66e0c89c3bd4.1 size=12288 mod=1707322032 from UID=10190 used=1306968064 quota=67108864 ratio=194753
02-07 13:15:09.577  1060  2096 D installd: Purging /data/data/com.mapeo/cache/DocumentPicker/4310cf99-c725-4ee9-8bfb-38a013a43732.mbtiles size=352948224 mod=1707322069 from UID=10190 used=1306955776 quota=67108864 ratio=194751
02-07 13:15:37.301  1351  1435 I AppsFilter: interaction: PackageSetting{deaff34 com.mapeo.debug/10291} -> PackageSetting{4f14c46 im.vector.app/10266} BLOCKED
02-07 13:16:18.305 22288 22302 I chatty  : uid=10190(com.mapeo) FinalizerDaemon identical 4 lines
02-07 13:16:38.275 22288 22302 I chatty  : uid=10190(com.mapeo) FinalizerDaemon identical 3 lines
02-07 13:16:39.047  1351  1435 I AppsFilter: interaction: PackageSetting{deaff34 com.mapeo.debug/10291} -> PackageSetting{4f14c46 im.vector.app/10266} BLOCKED
02-07 13:16:47.940 22288 22299 I com.mapeo: Compiler allocated 8525KB to compile void com.facebook.react.uimanager.ReactShadowNodeImpl.markUpdated()
02-07 13:17:46.025  1351  1435 I AppsFilter: interaction: PackageSetting{deaff34 com.mapeo.debug/10291} -> PackageSetting{4f14c46 im.vector.app/10266} BLOCKED
02-07 13:17:55.997 22288 22306 W com.mapeo: Could not save profiling info to /data/misc/profiles/cur/0/com.mapeo/primary.prof
02-07 13:18:36.015 22288 22306 W com.mapeo: Clearing bad or obsolete profile data from file /data/misc/profiles/cur/0/com.mapeo/primary.prof: Profile EOF reached prematurely for ReadContentUnable to read compressed profile data
02-07 13:18:36.018 22288 22306 W com.mapeo: Could not save profiling info to /data/misc/profiles/cur/0/com.mapeo/primary.prof
02-07 13:18:46.142  1351  1435 I AppsFilter: interaction: PackageSetting{deaff34 com.mapeo.debug/10291} -> PackageSetting{4f14c46 im.vector.app/10266} BLOCKED
02-07 13:18:52.987 22288 22299 I com.mapeo: Compiler allocated 5658KB to compile void com.facebook.react.views.text.ReactTextShadowNode.markUpdated()
achou11 commented 9 months ago

This issue seems to the same as when we have multiple tiles on a low end phone. My Pixel 5 doesn't crash, but just tested on my Pixel 2, and after loading 3 ~300mb mbtiles to Mapeo, it becomes unusable, constantly on loading state and crashing.

Some logs since loading the third tileset:

02-07 13:15:09.535  1060  2096 D installd: Purging /data/data/com.mapeo/cache/last-run-info size=8192 mod=1707322023 from UID=10190 used=1307193344 quota=67108864 ratio=194786
02-07 13:15:09.536  1060  2096 D installd: Purging /data/data/com.mapeo/cache/user-info size=4096 mod=1707322023 from UID=10190 used=1307185152 quota=67108864 ratio=194785
02-07 13:15:09.536  1060  2096 D installd: Purging /data/data/com.mapeo/cache/bugsnag-native size=8192 mod=1707322023 from UID=10190 used=1307181056 quota=67108864 ratio=194785
02-07 13:15:09.536  1060  2096 D installd: Purging /data/data/com.mapeo/cache/bugsnag-sessions size=8192 mod=1707322023 from UID=10190 used=1307172864 quota=67108864 ratio=194783
02-07 13:15:09.536  1060  2096 D installd: Purging /data/data/com.mapeo/cache/bugsnag-errors size=8192 mod=1707322023 from UID=10190 used=1307164672 quota=67108864 ratio=194782
02-07 13:15:09.536  1060  2096 D installd: Purging /data/data/com.mapeo/cache/upgrades/tmp size=8192 mod=1707322030 from UID=10190 used=1307156480 quota=67108864 ratio=194781
02-07 13:15:09.539  1060  2096 D installd: Purging /data/data/com.mapeo/cache/upgrades size=8192 mod=1707322030 from UID=10190 used=1307148288 quota=67108864 ratio=194780
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/2b9fd63907c1e8ad9e0b48d92aefb92f.0 size=8192 mod=1707322031 from UID=10190 used=1307140096 quota=67108864 ratio=194779
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/2b9fd63907c1e8ad9e0b48d92aefb92f.1 size=8192 mod=1707322031 from UID=10190 used=1307131904 quota=67108864 ratio=194777
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/3a830b6a2031f81cea05dda472d3c2de.0 size=8192 mod=1707322031 from UID=10190 used=1307123712 quota=67108864 ratio=194776
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/2578e49e42d0ef952fd26dc1d2d5be9a.0 size=8192 mod=1707322031 from UID=10190 used=1307115520 quota=67108864 ratio=194775
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/9b6a061bc1128547e18c3bb3360c524b.1 size=20480 mod=1707322031 from UID=10190 used=1307107328 quota=67108864 ratio=194774
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/3a830b6a2031f81cea05dda472d3c2de.1 size=20480 mod=1707322031 from UID=10190 used=1307086848 quota=67108864 ratio=194771
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/2578e49e42d0ef952fd26dc1d2d5be9a.1 size=69632 mod=1707322031 from UID=10190 used=1307066368 quota=67108864 ratio=194768
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/9b6a061bc1128547e18c3bb3360c524b.0 size=8192 mod=1707322031 from UID=10190 used=1306996736 quota=67108864 ratio=194757
02-07 13:15:09.541  1060  2096 D installd: Purging /data/data/com.mapeo/cache/WebView size=8192 mod=1707322031 from UID=10190 used=1306988544 quota=67108864 ratio=194756
02-07 13:15:09.577  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/8a977275984b233a6b5c66e0c89c3bd4.0 size=12288 mod=1707322032 from UID=10190 used=1306980352 quota=67108864 ratio=194755
02-07 13:15:09.577  1060  2096 D installd: Purging /data/data/com.mapeo/cache/http-cache/8a977275984b233a6b5c66e0c89c3bd4.1 size=12288 mod=1707322032 from UID=10190 used=1306968064 quota=67108864 ratio=194753
02-07 13:15:09.577  1060  2096 D installd: Purging /data/data/com.mapeo/cache/DocumentPicker/4310cf99-c725-4ee9-8bfb-38a013a43732.mbtiles size=352948224 mod=1707322069 from UID=10190 used=1306955776 quota=67108864 ratio=194751
02-07 13:15:37.301  1351  1435 I AppsFilter: interaction: PackageSetting{deaff34 com.mapeo.debug/10291} -> PackageSetting{4f14c46 im.vector.app/10266} BLOCKED
02-07 13:16:18.305 22288 22302 I chatty  : uid=10190(com.mapeo) FinalizerDaemon identical 4 lines
02-07 13:16:38.275 22288 22302 I chatty  : uid=10190(com.mapeo) FinalizerDaemon identical 3 lines
02-07 13:16:39.047  1351  1435 I AppsFilter: interaction: PackageSetting{deaff34 com.mapeo.debug/10291} -> PackageSetting{4f14c46 im.vector.app/10266} BLOCKED
02-07 13:16:47.940 22288 22299 I com.mapeo: Compiler allocated 8525KB to compile void com.facebook.react.uimanager.ReactShadowNodeImpl.markUpdated()
02-07 13:17:46.025  1351  1435 I AppsFilter: interaction: PackageSetting{deaff34 com.mapeo.debug/10291} -> PackageSetting{4f14c46 im.vector.app/10266} BLOCKED
02-07 13:17:55.997 22288 22306 W com.mapeo: Could not save profiling info to /data/misc/profiles/cur/0/com.mapeo/primary.prof
02-07 13:18:36.015 22288 22306 W com.mapeo: Clearing bad or obsolete profile data from file /data/misc/profiles/cur/0/com.mapeo/primary.prof: Profile EOF reached prematurely for ReadContentUnable to read compressed profile data
02-07 13:18:36.018 22288 22306 W com.mapeo: Could not save profiling info to /data/misc/profiles/cur/0/com.mapeo/primary.prof
02-07 13:18:46.142  1351  1435 I AppsFilter: interaction: PackageSetting{deaff34 com.mapeo.debug/10291} -> PackageSetting{4f14c46 im.vector.app/10266} BLOCKED
02-07 13:18:52.987 22288 22299 I com.mapeo: Compiler allocated 5658KB to compile void com.facebook.react.views.text.ReactTextShadowNode.markUpdated()

Is this related to deletion or just generally about performance? If it's about general performance, probably would be better to move to a separate issue (or one that already exists that's more related). Wouldn't be surprised if the change in Evan's PR helps with some of the issues, but maybe that's being too hopeful 😄

luandro commented 9 months ago

Yea, maybe deserves a seperate issue. Since it seems he's only touching on deletion, I would be surprised if it would solve this. Is there a way to test new changes on my phone?