Re: [OSM-dev] about Carto and SQL

[sav123]

1) if all the SQL requests required to produce a map are in the style file
or else if there are other SQL requests not if in this file ( and where they
are ).


Part of SQL is wrapping and table discovery logic in Mapnik's PostGIS 
driver, look for all text strings in 
https://github.com/mapnik/mapnik/blob/333ef9fde145f88339eaccba810305707bae9b0e/plugins/input/postgis/postgis_datasource.cpp 
starting from line 170, and nearby files.


I found the exact role of each added variable.

While the implicit Cartocss database is 20% bigger before min max scales 
redutions, it is faster.
There are many levels of optimization for a production database ( not for 
dev ) ...


More than half of the requests are made in subsets of size lower than 100 Mb 
( compared to the 90 Gb of the polygon table of osm2pgsql ) :
under 10 Mb : 25% , from 10 Mb to 100 Mb : 26% , 100 Mb to 1 Gb : 22% , 1 Gb 
to 10 Gb : 20% and over 10 Gb : 7%.


Having 75 tables ( and 1 view, 2 tables being identical ) allows to better 
dispatch data on the resources. And to get indexes faster.


For the postgres table partitioning, who can claim that a 50 Gb "building" 
table is difficult to geo-split in 4 to 10 parts and more ?
Or the 9 Gb "addresses" table ? There are also things to do with the 30 Gb 
roads_casing and the 20 Gb landcover.

Note that planet_osm_roads is already a subset of planet_osm_line.

It is useless to sort the new tables as specified in the queries but to keep 
the initial geom order of the source tables.

After geo cuts, sort is fast. Final adjustments...

Are also interesting :
- the intersections and inclusions relations between the subsets
- the (14) constant queries sequences sent by Mapnik.
- the balance of the tags never queried for rendering but perhaps useful for 
searches.


I'll come back in a few weeks with some scripts.
Thank you again ... 



___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] about Carto and SQL

[Komяpa]

>
>
> 1) if all the SQL requests required to produce a map are in the style file
> or else if there are other SQL requests not if in this file ( and where
> they
> are ).
>

Part of SQL is wrapping and table discovery logic in Mapnik's PostGIS
driver, look for all text strings in
https://github.com/mapnik/mapnik/blob/333ef9fde145f88339eaccba810305707bae9b0e/plugins/input/postgis/postgis_datasource.cpp
starting
from line 170, and nearby files.
___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] about Carto and SQL

[sav123]

-Message d'origine- 
From: Paul Norman

Sent: Tuesday, March 20, 2018 9:51 PM
To: dev@openstreetmap.org
Subject: Re: [OSM-dev] about Carto and SQL








On 3/20/2018 1:40 PM, sav123 wrote:
Kompza covered the variables you mentioned, but I if you're looking at
benchmarking, I recommend you set up the full rendering stack rather
than trying to generate queries yourself. The latter can be tricky to
get right, and you have to handle parallelism the same as the full stack
to get meaningful results. It's just easier to use renderd and feed it a
list of tiles with render_list than to write code that will take that
list, generate SQL, and run it.

This doesn't answer the question.
If you read the carto file, you will see that there are natural criteria , 
not only geometry.
Indeed, it is a bad idea to partition on coordinates, unless if a request 
applies to a small region.


I'm not speaking of partitioning, but of how to best benchmark 
performance, which is what you're planning to do to see if partitioning is 
worthwhile.


You don't know me but I see that you have your own idea. It's pretty amazing 
...


Please respect the protocol.
I ask and wait answers.
If it is a bad question, give argument on the question, not on me, we had 
not be presented.

Or else I have a lot of work, don't spend my time.








___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev 



___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] about Carto and SQL

[Paul Norman]

On 3/20/2018 1:40 PM, sav123 wrote:

Kompza covered the variables you mentioned, but I if you're looking at

benchmarking, I recommend you set up the full rendering stack rather
than trying to generate queries yourself. The latter can be tricky to
get right, and you have to handle parallelism the same as the full stack
to get meaningful results. It's just easier to use renderd and feed it a
list of tiles with render_list than to write code that will take that

list, generate SQL, and run it.


This doesn't answer the question.
If you read the carto file, you will see that there are natural 
criteria , not only geometry.
Indeed, it is a bad idea to partition on coordinates, unless if a 
request applies to a small region. 


I'm not speaking of partitioning, but of how to best benchmark 
performance, which is what you're planning to do to see if partitioning 
is worthwhile.
___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] about Carto and SQL

[sav123]

-Message d'origine- 
From: Paul Norman

Sent: Tuesday, March 20, 2018 8:36 PM
To: dev@openstreetmap.org
Subject: Re: [OSM-dev] about Carto and SQL

Hello


Kompza covered the variables you mentioned, but I if you're looking at

benchmarking, I recommend you set up the full rendering stack rather
than trying to generate queries yourself. The latter can be tricky to
get right, and you have to handle parallelism the same as the full stack
to get meaningful results. It's just easier to use renderd and feed it a
list of tiles with render_list than to write code that will take that

list, generate SQL, and run it.


This doesn't answer the question.
If you read the carto file, you will see that there are natural criteria , 
not only geometry.
Indeed, it is a bad idea to partition on coordinates, unless if a request 
applies to a small region.




Kompza

Thank you very much.
My goal is to have optimized "replicates" keeping the original tables in a 
master db.

I'll report the test results if they are interesting .

Igael






___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] about Carto and SQL

[Paul Norman]

On 3/20/2018 9:13 AM, sav123 wrote:
I hope to be able to grab enough information to partition some or all 
the postgresql tables and to publish the resulting comparison boards.


I know that it is possible to setup logs and to check them, but this 
method may miss rare calls ...


Kompza covered the variables you mentioned, but I if you're looking at 
benchmarking, I recommend you set up the full rendering stack rather 
than trying to generate queries yourself. The latter can be tricky to 
get right, and you have to handle parallelism the same as the full stack 
to get meaningful results. It's just easier to use renderd and feed it a 
list of tiles with render_list than to write code that will take that 
list, generate SQL, and run it.


___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] about Carto and SQL

[Komяpa]

Hi,

This comes from Mapnik's PostGIS driver:
https://github.com/mapnik/mapnik/wiki/PostGIS#other-tokens

All SQL happens only on Datasource level.

вт, 20 мар. 2018 г. в 19:50, sav123 :

> Hello,
>
> Is there a documentation of the Carto sql mechanism ?
>
> In fact, I should want only to know :
>
> 1) if all the SQL requests required to produce a map are in the style file
> or else if there are other SQL requests not if in this file ( and where
> they
> are ).
>
> 2) how work variables like pixel_width and how they are passed to
> postgresql
>
>
> I hope to be able to grab enough information to partition some or all the
> postgresql tables and to publish the resulting comparison boards.
>
> I know that it is possible to setup logs and to check them, but this method
> may miss rare calls ...
>
> thank you
>
> Igael
>
>
> ___
> dev mailing list
> dev@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/dev
>
___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev