MBTiles rendering is unreasonably slow on our GeoServers, and we're trying to optimize them.
When I set the log level to GeoTools Developer logging, I can see a delay in logs each time a log similar to the below appears:
27 Apr 13:40:12 DEBUG [org.geotools.renderer.lite] - Expanding rendering area by 8 pixels to consider stroke width
27 Apr 13:40:12 DEBUG [org.geotools.renderer.lite] - Querying layer http://www.opengeospatial.net/cite:water_name with bbox: ReferencedEnvelope[54.02870178222656 : 54.56703186035156, 31.667404174804688 : 32.09587097167969]
So it seems like the bottleneck is in the queries.
Is there a way to optimize the querying?
Each time I change the zoom level in the layer preview it takes like 20 seconds to get the new tiles.
Profiler Results
I attached an Intellij profiler to the GeoServer process.
I haven't used this profiler before but as its documentation notes:
The flame graph visualizes the application call tree with the
rectangles that stand for frames of the call stack, ordered by width.
Methods that consume more CPU time and memory resources are wider than
the others.https://www.jetbrains.com/help/idea/read-the-profiling-report.html#profiler-flame-chart
So I think that confirms that the queries are the bottleneck.
Something else I Spotted
If I remove all the mbtiles layer from my layer group and just let the style remain there, it renders correctly and it becomes even faster. My layer group looks like this now:
Without the layers, it renders in like 8-9 seconds.
The Query
The profiler shows that the MBTilesFile.maxZoom() method takes the most time. The query inside that method as I found in the mbtiles extension source code, is simply:
SELECT MAX(zoom_level) FROM tiles
Which runs in a few milliseconds if I run it in isolation.
Best Answer
Just an FYI to this. As I was researching this I had a very similar experience above. Everything was working great with Geoserver 2.17 and when I upgraded to 2.21 performance absolutely was terrible. I found that if I removed sqlite-jdbc-3.34.0.jar and went back to sqlite-jdbc-3.31.0.jar performance was great again. (I did debug geotools and found it was "stuck" on querying the "water" layer for whatever reason..)... But yes, Looks like sqlite jdbc issue or something. Not sure what exactly but moving back to 3.31 resolved for me.