1

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. enter image description here

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: enter image description here

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.

5
  • 1. add a spatial index, 2. don't use MBtiles as an input format? 3. Move to a proper database?
    – Ian Turton
    Commented Apr 27, 2022 at 9:28
  • 1
    How did you conclude that "the bottleneck is in the queries"? The bits of logs you shared shows no evidence. Maybe try to set GeoServer logging level to "geotools developer mode" and get a better feeling of where the time is spent. Also, could you put a number on that "unreasonably slow"? How much time to paint a map how big? Commented Apr 27, 2022 at 9:35
  • @AndreaAime Updated with more info. I know the question's getting less specific but I'll remove the unnecessary details as soon as I find a clue. Commented Apr 27, 2022 at 10:03
  • Yes it does. But it provides no clue as to why they are slow. The "GeoTools developer logging" profile should also list the queries that are being made. Can you spot them and figure out why they are slow? Commented Apr 27, 2022 at 12:13
  • If the style is a Mapbox style, it's already loading the layers off the mbtiles storage... adding layers in the group would just cause them to render multiple times, and with who knows what style.... That would definitely make things a lot slower. Commented Apr 27, 2022 at 14:40

2 Answers 2

1

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.

1
  • Hey, that was spot on! I tested it, I even replaced it with sqlite-jdbc-3.36.0.3 and it works. And this is a better solution than mine since my solution doubled the size of the database. You better have the green tick. Commented Jun 2, 2022 at 21:46
0

Adding indexes for the zoom_level column solved the problem. Though "tiles" was a SQL view initially and I had to replace it with a table in order to add indexes for the zoom_level column, and that nearly doubled the size of the database.

I don't know why running the query in isolation was not slow. Maybe an issue in the jdbc-sqlite library, or somewhere else.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.