search

cyclosm / cyclosm-cartocss-style

Cycle oriented CartoCSS style.
https://www.cyclosm.org
Other
240 stars 44 forks source link

Postgres DB Tables needs proper Indexing #680

Closed hbsandeepj closed 2 months ago

hbsandeepj commented 3 months ago

I believe that the Database tables should be indexed properly so that the db operations could be smoothened. If we try to load whole Europe which is approx of 29 GB, it dies. I additionally added Varnish cache to overcome this. However, the problem arises when a bunch of users tries to request for tiles, the database goes into bottleneck, I then manually added indexing to some of the tables, and now it is much better than the existing version.

Phyks commented 3 months ago

Hi,

I'm not sure to understand the issue you describe. Are you talking about setting up your own local copy of CyclOSM render on top of a local osm2pgsql database?

In this case, https://github.com/cyclosm/cyclosm-cartocss-style/blob/master/docs/INSTALL.md#openstreetmap-data should be up to date. You need to explicitly create some views in your psql database (https://github.com/cyclosm/cyclosm-cartocss-style/blob/master/views.sql) and it's worth adding a few extra indices (https://github.com/cyclosm/cyclosm-cartocss-style/blob/master/docs/INSTALL.md#useful-tweaks-for-production-setup).

I've not seen the issue you mention, so really eager to know more about your setup and the extra indices you added!

hbsandeepj commented 3 months ago

@Phyks Yes I have tried to setup my own space for hosting this. However, I had tuned the database by adding up few indexes to the db tables so that it could be speeded up. Is there any way by which I can simply extract the tiles out in PNG format in a directory so that I need not to utilise the database every time while requesting for a tile?

Phyks commented 2 months ago

This is rather about tooling and beyond the scope of this repo, I'll close the issue since there is no direct action for cyclosm.

For pre-generating a set of tiles and export them as PNG, you can have a look at Kosmtik export plugins: https://github.com/kosmtik/kosmtik?tab=readme-ov-file#known-plugins.

hbsandeepj commented 2 months ago

@Phyks thanks for the reply, however, I'm unable to setup the export library in this repository. I'm using the docker version can you please help with this?

Phyks commented 2 months ago

I don't know much about Kosmtik. It would be better to report there https://github.com/kosmtik/kosmtik/issues or contact its core dev https://www.openstreetmap.org/user/ybon.