[OSM-dev-fr] "glonflement" des tables osm2pgsql...

[Philippe Verdy]

Le 12 août 2014 21:17, Christian Quest <cquest at openstreetmap.fr> a écrit :

> Même un UPDATE pourrait avoir poru effet de réorganiser des pages pour
>> maintenir le fill factor, car l'UPDATE peut changer la taille d'une ligne
>> de table (en plus ou en moins).
>>
>>
> Tout dépend comment le fillfactor est utilisé justement. Sur la partie
> données (et je n'ai parlé que de ça), j'imagine que c'est un peu d'espace
> libre conservé lors des INSERT, mais justement pour être disponible lors
> des UPDATE... sinon quel peut bien être son intérêt ? Si on réserve
> toujours un ratio fixe d'espace vide ça revient à ne jamais l'utiliser...
> et ça n'a aucun intérêt.
>

Non: le fill factor est un taux de remplissage *minimum* à conserver autant
que possible. On peut toujours remplir au delà. et c'est bien ce que dit la
doc que tu cites qui indiques que justement une update ultérieur peut
toujours réutiliser cet espace laissé libre en cas d'augmentation de taille
d'une ligne.

Cependant je suis plutôt surpris par ton interprétation libre de ce qu'est
un index "clustered", dont le but est justement de préserver l'ordre des
lignes de données dans la table: et c'est là alors que pour le préserver,
on peut être amené à scinder une page en deux en déplaçant des lignes dans
une autre page. Alors interient le fill factor qui va chercher à remplir
les deux pages issues de la scission en y ramenant de lignes trouvées dans
les pages suivantes ou précédentes tant qu'elles préservent un taux de
remplissage minimum. C'est là qu'il y aura des I/O pour amener ces autres
pages et vérifier si leurs lignes peuvent être déplacées. Mais cette
recherche reste limitée.

Dans les faits, le fill factor servira lors des mises à jour ou suppression
quand une page n'est plus assez remplie: on récupère alors là aussi des
lignes dans les pages précédentes ou suivantes, et dans certains cas, on se
retrouve avec des pages vides car il y aura fusion de page, et donc on
aboutit à une libération de certaines pages qui deviennent libres (elles
sont ajoutées à un pool de pages libres pour la table, mais si le pool de
pages est déjà trop plein, les groupes de pages libres dans le pool peuvent
être remis à disposition pour d'autres tables.

Autrement dit, une table organisée en clustered index reste triée, ce qui
veut dire que la lecture séquentielle de la table (fulll table scan)
ramènera des lignes déjà triées dans l'ordre de l'index clustered; la table
et l'index ne font qu'un, la table est elle-même un index. Toutefois elle
ne stocke pas que des clés et donc serait inefficace pour des accès rapides
si toutes les pages d'index contenaient toutes les colonnes hors clés
d'index. C'est là qu'on a un format utilisant deux types de pages par un
table en clustered index : des pages racine/branches ne contenant QUE les
clés (ce qui donne un plus grand nombre de clés par page, donc un plus haut
degré de l'arbre et donc aussi une moins grande profondeur), et les pages
feuilles contenant les données hors des colonnes clé des branches (mais les
colonnes des clés absentes des pages de branches/racine mères, seront
stockées dans la page feuille; seule la première ligne dans la page feuille
n'a pas besoin de stocker sa clé puisqu'elle est dans la page
branche/racine mère. Cette première ligne d'une page feuille a une clé
nulle, les autres lignes stockent leur clé (ou une partie différentielle de
la clé en cas de compression des clés).

Il y a divers formats de tables index disponibles selon les moteurs SQL sur
l'emplacement de stockage des clés! Les B-arbres les plus simples
n'emploient qu'un seul type de page et ne distinguent pas les pages
feuilles des pages racines/branches, ne ne compressent pas non plus les
clés qui sont stockées dans toutes les lignes de toutes les pages.


>
>
>> Si on veut être optimal toutefois, un simple SELECT n'a pour effet QUE de
>> charger les pages nécessaires dans le cache, ce qui rend ensuite l''INSERT
>> ou l'UPDATE qui suit quasi instantané (au moins pour les pages des index
>> qui sont utilisés pour faire le SELECT initial). Cependant le SELECT
>> initial peut se contenter souvent de ne lire qu'un seul index sans avoir à
>> mettre à jour toutes les tables et index liés, c'est donc plus rapide
>> puisque le volume de pages concerné est plus faible au cas où la clé n'est
>> pas trouvée et qu'on va finalement faire un INSERT. En revanche si le
>> SELECT initial trouve la clé, il est fort probable alors que toutes les
>> pages de toutes les tables et index liés seront affectées (mais là comme on
>> a trouvé la clé, et qu'on va faire un UPDATE en utilisant cette clé, les
>> pages de l'index utilisé pour fait le SELECT initial sont déjà en mémoire
>> et resteront lues uniquement, alors que les autres tables/index seront mis
>> à jour pendant l'UPDATE de la ligne trouvée).
>>
>>
> J'imagine plus le SELECT chargeant uniquement ce dont il a besoin.
> Quelques pages de l'index qui permet de trouver les données, puis les
> données... et aucune page d'autre index éventuel pointant sur le données.
>
> Les requêtes dont on parle là sont très basiques, pas relationnelles.
>
>
>
>> Mais le problème de la méthode SELECT suivi de INSERT ou UDATE c'est la
>> concurrence d'accès: on doit utiliser le mode transactionnel et donc
>> "locker" les pages de l'index utilisé pour le SELECT initial. Et là ce sont
>> ces locks qui risquent d'entraver les performances.
>>
>>
> Pas de problème dans notre cas non plus, il n'y a qu'osm2pgsql qui fait
> des mises à jour dans la base et il n'y aura pas de mise à jour concurrente
> sur le même enregistrement.
>
>
>
>> Tout dépend donc de la granularité des transactions: qu'est-ce qui est
>> atomique dans l'opération INSERT ou UPDATE poru que cela ne provoque pas de
>> requêtes incohérentes dans les accès concurrents? Pour certaines opérations
>> de mises à jour comme celles-là où les accès concurrents sont majoritaires
>> on pourrait croire que DELETE+INSERT inconditionnel serait toujours
>> préférable, surtout s'il n'y a qu'un seul accès effectuant des mises à jour
>> des tables (et que tous les autres accès concurrents sont en lecture
>> seule). On ne peut pas réellement le garantir.
>>
>> L'atomicité (transactionnelle) de l'opération DELETE+INSERT sera donc la
>> même que pour l'opération SELECT+(INSERT ou UPDATE) si on veut éviter des
>> requêtes incohérentes (et donc se retrouver plus tard avec des données
>> manquantes ou en trop qui restent oubliées dans la base ou éviter de se
>> retrouver avec des clés doubles (qui provoquent des erreurs
>> transactionnelels lors de l'INSERT dans une table à index unique).
>>
>> Les bases utilisées ont de très nombreux accès concurrents; on ne peut
>> pas se passer réellement du mode transactionnel. De fait on a intérêt donc
>> à faire un "lock" tout au début des transactions avant de commencer les
>> modifs de la transaction, et donc cela milite effectivement pour des mises
>> à jour utilisant un SELECT initial pour ensuite faire selon qu'on a trouvé
>> on on la clé, un INSERT ou un UPDATE dans la même transaction (l'intérêt du
>> lock initial étant que l'opération de "rollback" ne fait que libérer les
>> "locks" et n'aura rien à modifier, il n'y aura eu donc en cas de conflit
>> nécessitant un rollback, aucun autre accès que des accès en lecture seule.
>>
>> De plus les locks peuvent être obtenus tous ensembles de façon atomique,
>> et on eput utiliser alors le mode transactionnel "optimiste" (où les
>> "rollbacks" sont très peu probables, il n'y a qu'à libérer les locks en
>> cours pour éviter les deadlocks des accès concurrents, et le moteur peut
>> reprogrammer la transaction sans même avoir besoin de l'annuler ou la
>> signaler en erreur d'accès concurrent).
>>
>> Dans tous les cas, le fill factor n'entre pas en jeu pour déterminer le
>> mode transactionnel. Ce qui compte ce sont uniquement les ratios entre I/O
>> en lecture seule et I/O en écriture pour les réorganisations de pages
>> d'index.
>>
>>
> Je ne pense pas que ceci nous concerne dans le cas présent. Je parle bien
> d'un cas particulier (une base osm2pgsql) et pas "en général".
>
>
Dans le cas présent le moteur c'est postgres... Il serait peut être bon
> avant de nous sortir toute la littérature sur les SGBD d'indiquer ce qui
> est en rapport avec le sujet initial.
> Donc retour à postgresql et osm2pgsql... ou ouverture d'un autre sujet
> éventuellement sur une autre liste de diffusion plus généraliste.
>

Oui mais les versions de PostgreSQL ont des formats de stockage différents
pour les index et diverses options. MySQL (ou Oracle, BerkekeyDB, etc..)
aussi. Et ces formats ont aussi évolué entre versions. Et de nombreux
moteurs savent lire les tables et index crées par d'autres moteurs
(certains moteurs peuvent même utiliser des tables partagées par plusieurs
moteurs, via un moniteur transactionnel...).

Osm2pgsql lui-même ne rentre pas trop dans le détail et se contente
d'utiliser les formats de stockage par défaut, le script de création des
tables et index ne rentre pas dans le détail des paramètres de tuning
possibles notamment ceux qui dépendent des versions. En terme de schéma de
données cela n'entre pas en considération mais cela fait pourtant de sacré
différences en terme de volumétrie, et d'optimisation des I/O.

Il y a encore d'autres facteurs dont le cahe en mémoire, qui permet au
moteur de différer les écritures pour optimiser les pages afin de les
remplir avant même qu'elles soient réellement écrites sur disque: le moteur
utilise un tampon générique de pages journalisé, où il n'est pas obligé de
mettre à jour les pages physiques des tables et index tout de suite, ce qui
lui permet de maintenir l'intégrité (le tampon mémoire s'appuie sur le
journal en cas d'incident pour récupérer les écritures en attente).

De même le tuning du journal permet de différer les transferts vers les
pages physiques des tables les plus volumineuses. Le moteur SQL travaille
beaucoup sur le journal avant les tables parce que cela offre plus de
"localité" des données et minimise les accès non séquentiels aux pages de
tables&index , et aussi parce que cela llimite la fragmentation des groupes
de pages poru une même table ou index.

C'est aussi la raison d'être d'un petit pool de pages libres disponible
dans les tables, destiné à grouper les pages sur disque associées à un même
index (très utile justement pour les pages mères de branches, sachant que
la page racine a une place figée pour rester le plus longtemps possible
dans le cache journalisé (puisque tous les accès concurrents, en lecture ou
en écriture) à un même index passent par cette page racine (qui a intérêt
être le plus rempli possible quitte à ce que ce soit les pages de branches
filles ou pages feuilles qui ne soient que partiellement remplies: les
pages de branches les plus proches de la racine doivent avoir un degré
élevé pour que la sélectivité des clés qui y sont stockées soit la plus
élevée possible et permettre au moteur d'évaluer le plus précisément
possible la sélectivité des clés, notamment pour les requêtes par
intervalle de clé ou faciliter les jointures par fusion d'index).

Pour plus de détail, il faut entrer dans la doc précise du moteur et de sa
version.
-------------- section suivante --------------
Une pièce jointe HTML a été nettoyée...
URL: <http://lists.openstreetmap.org/pipermail/dev-fr/attachments/20140812/ca513381/attachment-0001.html>