Performance de certaines vues avec des tables liées (foreign tables)

[MaelREBOUX]

Dans la VM de test je met en place une config quais identique à celle de l'infra OSM FR.

db.osm.world = le serveur de la base world (breizh pour simuler) = PostgreSQL 9.6.5 db.osm.local = le serveur de la base osm utilisée par le service de rendu = PostgreSQL 9.5.10

Dans la base OSM dans la VM on crée un "serveur" pour les tables étrangères https://github.com/osm-bzh/osmbr-mapstyle/blob/vm_test/database/setup_db.sql

Puis on importe les tables de la base world https://github.com/osm-bzh/osmbr-mapstyle/blob/vm_test/database/create_foreign_tables.sql

Puis on recrée les vues, comme d'habitude https://github.com/osm-bzh/osmbr-mapstyle/blob/vm_test/database/create_views.sql

Et là pb : les vues osm_admin osm_admin_places et osm_roads ne répondent pas.

Si on logue les requêtes de la base world, une requête ultra simple pour récupérer 1 seul enregistrement part en vrille et le log indique FETCH 100 FROM c1 en continu. On trouve peu de choses sur le web mais cette page indique que cette valeur 100 est en paramètre uniquement depuis PostgreSQL 9.6.

Je vais donc upgrader le MySQL en 9.6 voire 10 dans la VM et voir si ça change qqch. Puis changer la valeur du paramètre fetch_size pour voir.

[MaelREBOUX]

Ha oui : ce sont les vues qui utilisent UNION ou JOIN qui posent pb. Sauf osm_landusages qui utilise UNION et qui fonctionne bien quand même.

[MaelREBOUX]

# login en tant que root
sudo -s

# on arrête PostgreSQL
service postgresql stop

# déclarer un nouveau dépôt dans les sources pour aptitude
echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' >> /etc/apt/sources.list.d/postgresql.list

# récupérer le certificat
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -

# mettre à jour la liste des paquets
apt-get update

# vérifier
apt-cache search postgresql-10

# installer la version désirée
apt-get install postgresql-10 postgresql-10-postgis-2.4

The following additional packages will be installed:
  liblwgeom-2.4-0 libpq-dev libpq5 pgdg-keyring postgresql-10-postgis-2.4-scripts postgresql-client-10 postgresql-client-common postgresql-common
Suggested packages:
  postgresql-doc-10 locales-all libjson-perl
The following NEW packages will be installed:
  liblwgeom-2.4-0 pgdg-keyring postgresql-10 postgresql-10-postgis-2.4 postgresql-10-postgis-2.4-scripts postgresql-client-10
The following packages will be upgraded:
  libpq-dev libpq5 postgresql-client-common postgresql-common

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5433 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
Setting up postgresql-10-postgis-2.4 (2.4.2+dfsg-1.pgdg16.04+1) ...
Setting up postgresql-10-postgis-2.4-scripts (2.4.2+dfsg-1.pgdg16.04+1) ...
Processing triggers for libc-bin (2.23-0ubuntu9) ...

# tester la version
psql --version
psql (PostgreSQL) 10.1

[MaelREBOUX]

Et zut, la version 10 tourne sur le port 5433 pcq la 9.5 pré-existait. Et le service sytem.d est lié à la 9.5 -> on désinstalle tout !

apt-get remove postgresql-9.5

The following packages will be REMOVED:
  postgresql-9.5 postgresql-9.5-postgis-2.2 postgresql-contrib-9.5

apt-get --purge postgresql-9.5

apt-get remove postgresql-10

The following packages will be REMOVED:
  postgresql postgresql-10 postgresql-10-postgis-2.4 postgresql-contrib

apt-get --purge postgresql-10

dpkg -l | grep postgres
ii  pgdg-keyring                           2017.3                                     all          keyring for apt.postgresql.org
ii  postgresql-10-postgis-2.4-scripts      2.4.2+dfsg-1.pgdg16.04+1                   all          Geographic objects support for PostgreSQL 10 -- SQL scripts
ii  postgresql-9.5-postgis-scripts         2.2.1+dfsg-2                               all          Geographic objects support for PostgreSQL 9.5 -- scripts
ii  postgresql-client-10                   10.1-1.pgdg16.04+1                         amd64        front-end programs for PostgreSQL 10
ii  postgresql-client-9.5                  9.5.10-0ubuntu0.16.04                      amd64        front-end programs for PostgreSQL 9.5
ii  postgresql-client-common               188.pgdg16.04+1                            all          manager for multiple PostgreSQL client versions
ii  postgresql-common                      188.pgdg16.04+1                            all          PostgreSQL database-cluster manager

apt-get remove postgresql-client-9.5  postgresql-9.5-postgis-scripts postgresql-client-10 postgresql-10-postgis-2.4-scripts postgresql-client-common postgresql-common

reboot

[MaelREBOUX]

Mieux.

apt-get install postgresql-10 postgresql-10-postgis-2.4

The following NEW packages will be installed:
  liblwgeom-2.4-0 libsensors4 libxslt1.1 postgresql-10 postgresql-10-postgis-2.4 postgresql-10-postgis-2.4-scripts postgresql-client-10 postgresql-client-common
  postgresql-common sysstat

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

Aucune base OSM : on a bien un postgres vierge.

[MaelREBOUX]

Ca permet de valider la doc :D Tout est remis en 1 min.

Bon…

select * from osm_roads limit 10;

part tjs en boucle. :(

[MaelREBOUX]

http://www.postgresql-archive.org/3000x-Slower-query-when-using-Foreign-Data-Wrapper-vs-local-tt5869569.html

use_remote_estimate

This option, which can be specified for a foreign table or a foreign server, controls whether postgres_fdw issues remote EXPLAIN commands to obtain cost estimates. A setting for a foreign table overrides any setting for its server, but only for that table. The default is false.

J'ai rajouté use_remote_estimate (true) lors de la création du foreign server mais ça change rien.

https://www.enterprisedb.com/blog/postgresql-aggregate-push-down-postgresfdw

In 9.6 the foreign server spent time and bandwidth in pushing 1M rows on the network and then the local server spent time and bandwidth to pull those many rows off of network. But in version 10, the foreign server pushed only 100 rows saving time and bandwidth of both the servers, making users happy, since the aggregate is now 10 times faster than what it used to be!

https://community.postgresrocks.net/t5/Postgres-Gems/Materialized-Views-and-Foreign-Data-Wrappers/ba-p/564

Also, Postgres 10 speeds up aggregate queries on foreign tables.

Tester sur osm202 ou upgrader le PostgreSQL en Homebrew sur le mac ?

[MaelREBOUX]

Je peux pas risquer de plomber la base monde en production avec une requête hyper longue donc -> upgrade du postgreSQL sur le mac

[MaelREBOUX]

Migration terminée, chargement de la base OSM en cours.

https://gist.github.com/MaelREBOUX/4c37c12250210dd5c45948f5a8a6d0d1

[MaelREBOUX]

1h44 de chargement (comme d'habitude).

_select * from osmroads limit 10; répond en 17 s contre 4 s sur le serveur world. Ca descend à 13s.

_select * from osmadmin limit 10; répond en 60 s contre 7 s sur le serveur world.

_select * from osm_adminplaces limit 10; répond en 7 s contre 1 s sur le serveur world.

Du mieux donc mais le temps d'exécution pour _osmadmin n'est pas acceptable. Je vais tester les requêtes en parallèle pour voir s'il y a du mieux.

[MaelREBOUX]

J'ai rajouté ça dans le postgresql.conf du PostgreSQL 10 qui "sert" les données mais sans changement de temps d'exécution :

# parallélisme
max_worker_processes = 8
max_parallel_workers_per_gather = 2
max_parallel_workers = 8

Je vais essayer avec un dblink à la place d'une table étrangère.

[MaelREBOUX]

Je crois que je vais plutôt revoir toutes mes requêtes SQL.

J'étais passé à côté de ce fichier à l'époque : https://github.com/mapbox/osm-bright/blob/master/osm-bright/osm-bright.osm2pgsql.mml

[MaelREBOUX]

La basemap (occupation du sol + routes + rail + hydro) est en place.

Reste :

• limites administratives
• toponymie

cf les commits sur la branche vm-test : https://github.com/osm-bzh/osmbr-mapstyle/commits/vm_test

[MaelREBOUX]

On avance on avance : https://github.com/osm-bzh/osmbr-mapstyle/releases/tag/v2.0

Il ne me reste que 3 vues matérialisées :

• admin_boundaries = limites administratives sous formes de lignes
• admin_places = préfecture > chef-lieu de canton > commune
• places

[MaelREBOUX]

Il ne reste plus que 1 vue matérialisée : admin_places

A ce stade la vue matérialisée sera créée avec un dump France ou en se branchant sur la base monde.

Je ferme.