Plusieurs gros insert en parallèle

[Bibi218]

Bonjour tout le monde,

Depuis quelques jours, je rencontre quelques difficultés en termes de performances, aussi j'aimerais profiter de vos lumières.

Contexte :
J'ai un client lourd, relié à une base Postgresql 9.0.
J'utilise sqlalchemy pour mon application, mais je ne pense pas que ça change grand chose.
Pour résumer, j'ai 2 tables vraiment importantes.
Une table "users" d'environ 50 millions de lignes, et une table "tasks", beaucoup plus petite (une centaine de lignes pour l'instant). J'utilise également une autre table pour la relation ManyToMany existant entre users et tasks. Appelons-la "tasks_users".
Rien de bien compliqué, en somme.

Ma table "users" est accédée en lecture et écriture environ 30 fois par secondes (update ou lecture d'une ligne à la fois) sans me causer de soucis particuliers.
La table tasks_users est accédée en lecture de temps à autres (un select pour savoir dans quelles tâches apparait un user donné), une fois toutes les 10 secondes par exemple.

Là où les problèmes commencent à survenir, c'est quand je construis de nouvelles tasks.
En général, une task contient environ 100.000 users, mais c'est assez variable.
J'ai donc des périodes où je dois procéder à des inserts assez importants, et souvent en même temps.
Typiquement, une vingtaine d'inserts de 50.000 à 500.000 lignes chacun.

Après quelques benchmarks, avec mon application au repos, j'obtenais des résultats de l'ordre de 300s pour un insert de 100.000 lignes.
Je m'attendais à des ralentissements en effectuant plusieurs inserts en même temps, mais tout de même...

En en faisant tourner 2 simultanément, le premier se terminait en grosso-modo 300s, tandis que le second s'est terminé une heure plus tard (toujours sans application qui tourne, rien qu'au niveau SBGD).
Un autre essai me donne 300s et 900s pour le second.

Lorsque je fais tourner mon application, c'est encore pire. Surement que les lectures occasionnelles ne doivent pas aider, mais là j'ai lancé l'initialisation de 20 tâches d'environ 100.000 utilisateurs, et 10 heures plus tard je n'en ai que la moitié de terminées.

Note : j'utilise des transactions bien sûr.　J'ai lu sur la doc que désactiver les index pendant l'insert améliorait les performances, mais comme j'ai des lectures en même temps, je me demande si c'est vraiment pertinent.
J'ai également essayé de diviser mes transactions en sous-tâches plus petites (par exemple, au lieu d'une transaction de 100.000, en faire 10 de 10.000, afin de relâcher des locks éventuels plus souvent ou je ne sais quoi, mais sans résultat très probant. Je dirais même que c'est plutôt pire).

Quelqu'un aurait une idée pour améliorer tout ça ?

[estofilo]

Un seul INSERT insèrerait donc des dizaines de milliers de lignes. Mais d'où viennent les données insérées? J'imagine qu'il n'y a pas une clause VALUES avec derrière les données de toutes ces lignes?
Il faudrait regarder à quoi ressemble exactement cette requête insert qui pose problème.
Il faudrait aussi savoir ce que fait la machine pendant qu'un insert n'en finit pas, notamment si le(s) disque(s) sont à bloc et/ou si le cpu est à bloc.

[Bibi218]

En fait les donnees viennent du web.
J'ai une application en python qui se charge de rapatrier les donnees et de les traiter en vue de l'insertion dans la base.
Au final, j'ai juste une enorme liste d'ids a assigner a ma tache.
Le code dans le principe ressemblerait a ca

Code :

1
2
3
4
5
 
transaction=connection.begin()
FOR id IN ids:
    connection.execute("insert into tasks_users (task_id, user_id) values (%s, %s)", (task_id, id))
transaction.commit()

Lors des longs delais d'attente, ni le disque ni le processeur ne semblent particulierement sollicites (pas plus que d'habitude disons). Je ne swappe pas, ma RAM est raisonnablement occupee, mes autres applications (mplayer, etc...) ne montrent aucun signe de lenteur.

[estofilo]

Donc supposément la table tasks_users est énorme. Il faudrait voir quels sont les index et clefs étrangères sur la table.

Il y a beaucoup de pistes d'optimisations:
- utiliser des requêtes préparées, par principe.
- utiliser COPY au lieu d'INSERT
- supprimer les clefs étrangères de tasks_users s'il y en a
- partitionner tasks_users si les accès à cette table peuvent supporter une clef de partitionnement.
- supprimer un index non sélectif s'il y en a

[Bibi218]

En effet, cette table va devenir enorme avec le temps.
Sa structure quant a elle est vraiment simple.
2 colonnes, chacune etant une cle etrangere (task_id et user_id), et donc indexee par defaut si je ne me trompe pas par mon ORM.

- utiliser des requêtes préparées, par principe.
=> je ne sais pas si ca passe bien avec sqlalchemy, je vais verifier...

- utiliser COPY au lieu d'INSERT
=> je vais creuser ca effectivement, j'ai lu que ca avait une assez forte incidence sur les performances

- supprimer les clefs étrangères de tasks_users s'il y en a
=> la table me sert a modeliser une relation Many2Many, et les cles etrangeres sont creees automatiquement par l'ORM. Est-ce vraiment une bonne idee d'affaiblir les contraintes du schema ?

- partitionner tasks_users si les accès à cette table peuvent supporter une clef de partitionnement.
=> je n'ai jamais entendu parler de ca... comment ca marche en pratique ?

- supprimer un index non sélectif s'il y en a
=> vu la structure de la table, je dirais que les 2 index sont necessaires

[estofilo]

Pour le partitionnement c'est un sujet assez vaste. La doc donne déjà une bonne introduction:

L'inconvénient majeur est que c'est lourd à maintenir et administrer.

Sur le fait de supprimer des index, je veux bien croire que ça ne soit pas applicable dans le cas présent, mais ce n'est pas la structure des tables qui permet de le savoir, c'est l'ensemble des requêtes qui sont faites sur une table. Car un index gigantesque qui ne serait jamais utilisé par aucune requête est toujours à éliminer, même s'il paraissait utile au moment de la conception.

Sur la suppression des clefs étrangères, pour l'intégrité ce n'est pas une bonne idée, mais le gain de performance peut justifier de les sacrifier, ça dépend du contexte.