Que faire lorsqu'il existe une application de source fermée qui n'accède pas à la base de données de la manière la plus optimale? Comment régler les requêtes sans changer l'application, et éventuellement la base de données elle-même?
Si vous n'avez pas posé de telles questions, vous êtes un DBA très performant et rigoureux.
Eh bien, si on me le demande, permettez-moi de partager la souffrance et l'expérience.
Besoin de stocker plus de données ou de définir une tâche
Vous pouvez faire défiler cette section en toute sécurité si l'historique du problème n'est pas intéressant.
Initialement, nous avions un système propriétaire qui analysait ses données d'un format fermé dans la base de données PostgreSQL, d'où nous lisions, analysions et traitions ces données.
De plus, les outils de ce système utilisaient également cette base pour certaines opérations, donc l'abandonner et créer une copie avec sa structure semblait une idée futile.
Par défaut, le système supprimait automatiquement les enregistrements datant de plus d'une semaine, il n'y avait donc aucun problème de performances sur le stand.
Cependant, nous devons stocker les données beaucoup plus longtemps, tant qu'il y a suffisamment d'espace sur le disque du serveur. Eh bien, il est très conseillé de ne pas perdre l'accès à ces données et de continuer à utiliser les outils intégrés du système, même pour les anciennes données.
Par conséquent, la décision évidente était de faire du partitionnement et des déclencheurs sur les opérations INSERT. La mise au point est assez simple et efficace. Les données sont insérées dans les partitions nécessaires, la suppression des anciens enregistrements est désactivée, tout semble bien se passer.
Jusqu'à ce que quelques années se soient écoulées et que les données ne se soient pas bien accumulées.
Ici, «tout à coup», il s'est avéré que les requêtes faites par les outils du système utilisé ne limitaient pas la sélection par date (ou plutôt, ne la limitaient pas au champ selon lequel la partition se faisait). C'est-à-dire si nous recherchons quelque chose - la recherche se poursuit sur toutes les partitions. Les opérations UPDATE ont également commencé à ralentir - dans des conditions où il n'y avait qu'un ID-shnik utilisé.
En conséquence, la demande est exécutée pendant une longue période, tire toutes les autres demandes, la charge augmente rapidement.
Bien sûr, la première chose qui me vient à l'esprit est de contacter le développeur.
Cependant, dans la plupart des cas, soit il n'est plus dans la zone d'accès, soit il demandera le coût d'un autre système de ce type pour l'achèvement sur plusieurs lignes.
Par conséquent, l'idée est venue qu'il existe probablement déjà une sorte de proxy qui peut nous aider.
Nous avons besoin d'un proxy
La recherche rapide sur Google n'a pas trouvé de réponse claire à la question de savoir comment réécrire une requête entrante du côté de PostgreSQL ou d'un logiciel tiers.
Par conséquent (enfin, juste pour le plaisir, bien sûr), un logiciel assez simple a été écrit qui accepte les connexions des clients et les proxy dans PostgreSQL. Dans le même temps, les requêtes SQL entrantes sont lues et, si nécessaire, remplacées.
Partager un lien vers githubBien que je n'aie pas créé de paquets binaires, mes mains n'ont pas atteint. Mais le montage est assez simple. Tout est écrit en C ++ / Qt, car J'écris là-dessus depuis longtemps ...
La configuration est assez simple:
Spécifiez l'interface et le port à écouter:
listen_address=0.0.0.0 listen_port=5433
Nous forçons les logiciels négligents à se connecter à l'adresse spécifiée au lieu de se connecter directement au serveur PostgreSQL.
Nous notons où transférer les connexions (dans cet exemple, le proxy est situé sur la même machine que le serveur PostgreSQL):
dst_address=127.0.0.1 dst_port=5432
Nous définissons une expression régulière pour intercepter la demande souhaitée:
query = SELECT \* FROM tablename WHERE (.+)
Nous disons que nous devons le réécrire:
action = rewrite
Nous disons comment réécrire:
rewrite = SELECT * FROM tablename WHERE (col3 >= '$(now-1M)') AND $(1)
Dans cet exemple, nous ajoutons un filtre aux conditions de requête par la colonne avec la date, indiquant que nous ne sommes intéressés que par les enregistrements du dernier mois.
On pourrait écrire comme ceci:
rewrite = SELECT * FROM tablename WHERE (col3 >= now() - interval '1 month') AND $(1)
Mais alors la requête ne sera pas optimale en raison de la présence de la fonction now () - la recherche sera toujours effectuée sur toutes les partitions. Afin de rechercher uniquement dans le nécessaire, vous devez spécifier une valeur constante. Par conséquent, notre proxy remplace l'horodatage par un décalage d'un mois au lieu de la construction $ (now-1M).
Résultat (à partir du journal):
ORIGINAL query: SELECT * FROM tablename WHERE id=1; MODIFIED query (rule 1): SELECT * FROM tablename WHERE (col3 >= '2018-11-12 11:25:23.0+00') AND id=1;
Ainsi, il est possible de remplacer, en principe, toute demande. Les réponses du serveur ne sont pas modifiées et sont transmises telles quelles au client. De cette façon, le retard de transmission est minimisé. De plus, l'application attend généralement une réponse d'un certain format, il n'est donc pas souhaitable de modifier l'ensemble de colonnes dans la demande et la réponse.
Il est également possible d'imprimer facilement toutes les demandes d'intérêt dans le journal:
query = .+ action = log
Le référentiel a une configuration avec des exemples et une description plus détaillée.
Soit dit en passant, il est facile de déterminer dans quelle mesure le développeur écrit correctement pour travailler avec la base de données. Par exemple, si vous voyez une demande si fréquemment exécutée, il est temps pour quelqu'un de fumer des manuels.
INSERT INTO tablename (col1, col2, col3) VALUES('value1', 1, '2018-12-31')
Cela devrait être comme ceci:
INSERT INTO tablename (col1, col2, col3) VALUES($1::varchar, $2::integer, $3::date)
Malheureusement, jusqu'à présent, notre proxy n'est pas en mesure d'écrire de cette façon: / mais ce n'est pas difficile à faire. Peut-être qu'à l'avenir il sera possible avec lui de réécrire la première requête à la seconde.
Oui, le point important est que SSL n'est pas encore pris en charge, donc toutes les connexions des clients aux serveurs proxy seront sans cryptage.
Je serai heureux de commentaires et de commentaires.
S'il y a un intérêt actif des utilisateurs, je développerai peut-être davantage le projet.
Vous pouvez ajouter du travail avec d'autres bases de données.