L’architecture des microservices, comme tout dans ce monde, a ses avantages et ses inconvénients. Certains processus deviennent ainsi plus faciles, d’autres plus difficiles. Et pour accélérer le changement et améliorer l’évolutivité, vous devez faire des sacrifices. L’un d’eux est la complexité croissante de l’analyse. Si dans un monolithe toutes les analyses opérationnelles peuvent être réduites à des requêtes SQL vers une réplique analytique, alors dans une architecture multiservice, chaque service possède sa propre base de données et il semble qu'une seule requête ne puisse pas être effectuée (ou peut-être le peut-elle ?). Pour ceux qui souhaitent savoir comment nous avons résolu le problème de l'analyse opérationnelle dans notre entreprise et comment nous avons appris à vivre avec cette solution, bienvenue.

Je m'appelle Pavel Sivash, chez DomClick, je travaille dans une équipe responsable de la maintenance de l'entrepôt de données analytiques. Classiquement, nos activités peuvent être classées dans l'ingénierie des données, mais en réalité, l'éventail des tâches est beaucoup plus large. Il existe des normes ETL/ELT pour l'ingénierie des données, le support et l'adaptation d'outils d'analyse de données et le développement de vos propres outils. En particulier, pour le reporting opérationnel, nous avons décidé de « faire semblant » d'avoir un monolithe et de donner aux analystes une base de données qui contiendra toutes les données dont ils ont besoin.

En général, nous avons envisagé différentes options. Il était possible de créer un référentiel à part entière - nous avons même essayé, mais, pour être honnête, nous n'avons pas pu combiner des changements de logique assez fréquents avec le processus plutôt lent de construction d'un référentiel et d'y apporter des modifications (si quelqu'un réussissait , écrivez dans les commentaires comment). Il était possible de dire aux analystes : « Les gars, apprenez Python et passez aux répliques analytiques », mais c'est une exigence supplémentaire pour le recrutement, et il semble que cela devrait être évité si possible. Nous avons décidé d'essayer d'utiliser la technologie FDW (Foreign Data Wrapper) : il s'agit essentiellement d'un dblink standard, qui est dans le standard SQL, mais avec sa propre interface beaucoup plus pratique. Sur cette base, nous avons élaboré une solution qui a finalement fait son chemin et nous l'avons adoptée. Ses détails font l'objet d'un article séparé, et peut-être de plusieurs, car je veux parler de beaucoup de choses : de la synchronisation des schémas de bases de données au contrôle d'accès et à la dépersonnalisation des données personnelles. Il faut également faire une réserve sur le fait que cette solution ne remplace pas de véritables bases de données et référentiels analytiques, elle ne résout qu'un problème spécifique.

Au niveau supérieur, cela ressemble à ceci :

Il existe une base de données PostgreSQL dans laquelle les utilisateurs peuvent stocker leurs données de travail et, surtout, les répliques analytiques de tous les services sont connectées à cette base de données via FDW. Cela permet d'écrire une requête dans plusieurs bases de données, et peu importe de quoi il s'agit : PostgreSQL, MySQL, MongoDB ou autre chose (fichier, API, si du coup il n'y a pas de wrapper adapté, vous pouvez écrire le vôtre). Eh bien, tout semble génial ! Sommes-nous en train de rompre ?

Si tout se terminait si rapidement et simplement, il n'y aurait probablement pas d'article.

Il est important d'être clair sur la manière dont Postgres traite les requêtes adressées aux serveurs distants. Cela semble logique, mais souvent les gens n'y prêtent pas attention : Postgres divise la requête en parties qui sont exécutées indépendamment sur des serveurs distants, collecte ces données et effectue lui-même les calculs finaux, donc la vitesse d'exécution de la requête dépendra grandement de comment c'est écrit. A noter également : lorsque les données arrivent d'un serveur distant, elles n'ont plus d'index, il n'y a rien qui puisse aider le planificateur, donc nous seuls pouvons l'aider et le conseiller. Et c'est exactement ce dont je veux parler plus en détail.

Une simple requête et un plan avec

Pour montrer comment Postgres interroge une table de 6 millions de lignes sur un serveur distant, examinons un plan simple.

explain analyze verbose  
SELECT count(1)
FROM fdw_schema.table;

Aggregate  (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table
Planning time: 0.986 ms
Execution time: 3857.436 ms

L'utilisation de l'instruction VERBOSE nous permet de voir la requête qui sera envoyée au serveur distant et dont nous recevrons les résultats pour un traitement ultérieur (ligne RemoteSQL).

Allons un peu plus loin et ajoutons plusieurs filtres à notre requête : un pour booléen champ, un par occurrence horodatage dans l'intervalle et un par jsonb.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 5046843
        Remote SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Planning time: 0.665 ms
Execution time: 27474.118 ms

C’est là que réside le point auquel vous devez prêter attention lors de l’écriture de requêtes. Les filtres n'ont pas été transférés sur le serveur distant, ce qui signifie que pour l'exécuter, Postgres extrait les 6 millions de lignes afin de filtrer ensuite localement (Filter row) et effectuer l'agrégation. La clé du succès est d'écrire une requête afin que les filtres soient transférés vers la machine distante, et que nous recevions et regroupions uniquement les lignes nécessaires.

C'est une connerie

Avec les champs booléens, tout est simple. Dans la demande initiale, le problème était dû à l'opérateur is. Si vous le remplacez par =, alors on obtient le résultat suivant :

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=508010.14..508010.15 rows=1 width=8) (actual time=19064.314..19064.314 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..507988.44 rows=8679 width=0) (actual time=33.035..18951.278 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 3567989
        Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planning time: 0.834 ms
Execution time: 19064.534 ms

Comme vous pouvez le constater, le filtre a été transféré vers un serveur distant et le temps d'exécution a été réduit de 27 à 19 secondes.

Il convient de noter que l'opérateur is différent de l'opérateur = car il peut fonctionner avec la valeur Null. Cela signifie que ce n'est pas vrai laissera les valeurs False et Null dans le filtre, alors que != Vrai ne laissera que de fausses valeurs. Par conséquent, lors du remplacement de l'opérateur n'est pas deux conditions avec l'opérateur OR doivent être transmises au filtre, par exemple : OÙ (col != Vrai) OU (col est nul).

Nous avons traité du booléen, passons à autre chose. Pour l'instant, remettons le filtre booléen à sa forme d'origine afin de considérer indépendamment l'effet d'autres modifications.

horodatage ? Hz

En général, vous devez souvent expérimenter comment rédiger correctement une requête impliquant des serveurs distants, puis chercher ensuite une explication de la raison pour laquelle cela se produit. Très peu d’informations à ce sujet peuvent être trouvées sur Internet. Ainsi, lors d'expériences, nous avons constaté qu'un filtre à date fixe vole vers le serveur distant avec fracas, mais lorsque nous voulons définir la date de manière dynamique, par exemple now() ou CURRENT_DATE, cela ne se produit pas. Dans notre exemple, nous avons ajouté un filtre pour que la colonne create_at contienne des données pour exactement 1 mois dans le passé (BETWEEN CURRENT_DATE - INTERVAL '7 mois' ET CURRENT_DATE - INTERVAL '6 mois'). Qu'avons-nous fait dans ce cas ?

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta->>'source' = 'test';

Aggregate  (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
        Rows Removed by Filter: 76934
        Remote SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Planning time: 0.703 ms
Execution time: 4789.379 ms

Nous avons demandé au planificateur de calculer à l'avance la date dans la sous-requête et de transmettre la variable prête à l'emploi au filtre. Et cet indice nous a donné un excellent résultat, la requête est devenue presque 6 fois plus rapide !

Encore une fois, il est important d'être prudent ici : le type de données dans la sous-requête doit être le même que celui du champ sur lequel nous filtrons, sinon le planificateur décidera que puisque les types sont différents, il faut d'abord récupérer tous les types de données. les données et les filtrer localement.

Renvoyons le filtre de date à sa valeur d'origine.

Freddy contre Jsonb

En général, les champs booléens et les dates ont déjà suffisamment accéléré notre requête, mais il restait un type de données supplémentaire. Pour être honnête, la bataille contre le filtrage n’est toujours pas terminée, même si ici aussi il y a du succès. Voilà donc comment nous avons réussi à passer le filtre jsonb champ au serveur distant.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=1100.00..245459.90 rows=1478 width=0) (actual time=16.213..6634.794 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 619961
        Remote SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.747 ms
Execution time: 6727.815 ms

Au lieu de filtrer les opérateurs, vous devez utiliser la présence d'un seul opérateur jsonb dans un différent. 7 secondes au lieu des 29 d'origine. Jusqu'à présent, c'est la seule option réussie pour transmettre des filtres via jsonb à un serveur distant, mais ici il est important de prendre en compte une limitation : nous utilisons la version 9.6 de la base de données, mais d'ici fin avril nous prévoyons de terminer les derniers tests et de passer à la version 12. Une fois la mise à jour effectuée, nous expliquerons comment cela a affecté, car il y a beaucoup de changements pour lesquels il y a beaucoup d'espoir : json_path, nouveau comportement CTE, push down (existant depuis la version 10). J'ai vraiment envie de l'essayer bientôt.

Finis-le

Nous avons testé individuellement comment chaque modification affectait la vitesse des requêtes. Voyons maintenant ce qui se passe lorsque les trois filtres sont écrits correctement.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone)) AND ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 ms

Oui, la requête a l'air plus compliquée, il s'agit de frais forcés, mais la vitesse d'exécution est de 2 secondes, soit plus de 10 fois plus rapide ! Et nous parlons d’une simple requête sur un ensemble de données relativement petit. Sur des demandes réelles, nous avons reçu une augmentation pouvant aller jusqu'à plusieurs centaines de fois.

Pour résumer : si vous utilisez PostgreSQL avec FDW, vérifiez toujours que tous les filtres sont envoyés au serveur distant, et vous serez content... Au moins jusqu'à ce que vous arriviez à des jointures entre tables de différents serveurs. Mais c'est une histoire pour un autre article.

Merci pour votre attention! J'aimerais entendre des questions, des commentaires et des histoires sur vos expériences dans les commentaires.

Source: habr.com