Was tun, wenn es eine Closed-Source-Anwendung gibt, die nicht optimal auf die Datenbank zugreift? Wie kann ich Abfragen optimieren, ohne die Anwendung und möglicherweise die Datenbank selbst zu ändern?
Wenn Sie solche Fragen nicht gestellt haben, sind Sie ein sehr erfolgreicher und strenger DBA.
Nun, wenn ich gefragt werde, lass mich das Leiden und die Erfahrung teilen.
Sie müssen mehr Daten speichern oder eine Aufgabe festlegen
Sie können sicher durch diesen Abschnitt scrollen, wenn der Verlauf des Problems nicht interessant ist.
Anfangs hatten wir ein proprietäres System, das seine Daten aus einem geschlossenen Format in die PostgreSQL-Datenbank analysierte, von wo aus wir diese Daten lasen, analysierten und verarbeiteten.
Darüber hinaus verwendeten die Tools dieses Systems diese Basis auch für bestimmte Vorgänge, sodass es sinnlos schien, sie aufzugeben und eine Kopie mit ihrer Struktur zu erstellen.
Standardmäßig löschte das System automatisch Datensätze, die älter als eine Woche waren, sodass am Stand keine Leistungsprobleme auftraten.
Wir müssen Daten jedoch viel länger speichern, solange auf der Serverfestplatte genügend Speicherplatz vorhanden ist. Nun, es ist sehr ratsam, den Zugriff auf diese Daten nicht zu verlieren und die integrierten Tools des Systems auch für alte Daten zu verwenden.
Daher war die offensichtliche Entscheidung, Partitionen und Trigger für INSERT-Operationen durchzuführen. Der Fokus ist recht einfach und effektiv. Daten werden in die erforderlichen Partitionen eingefügt, das Löschen alter Datensätze ist deaktiviert, alles scheint in Ordnung zu sein.
Bis ein paar Jahre vergangen sind und sich die Daten nicht gut angesammelt haben.
Hier stellte sich „plötzlich“ heraus, dass die Anforderungen der Tools des verwendeten Systems die Auswahl nicht nach Datum beschränken (oder vielmehr nicht auf das Feld beschränken, nach dem die Partition ausgeführt wird). Das heißt, Wenn wir nach etwas suchen, wird die Suche auf allen Partitionen durchgeführt. Die UPDATE-Operationen begannen sich ebenfalls zu verlangsamen - unter Bedingungen wurde nur ein ID-Shnik verwendet.
Infolgedessen wird die Anforderung für eine lange Zeit ausgeführt, zieht alle anderen Anforderungen herunter, die Last wächst schnell.
Das erste, was mir in den Sinn kommt, ist natürlich, den Entwickler zu kontaktieren.
In den meisten Fällen befindet es sich jedoch entweder nicht mehr in der Zugangszone oder es werden die Kosten eines anderen solchen Systems für die Fertigstellung in mehreren Leitungen angefordert.
Daher kam die Idee auf, dass es wahrscheinlich bereits eine Art Proxy gibt, der uns helfen kann.
Wir brauchen einen Proxy
Das schnelle Googeln fand keine klare Antwort auf die Frage, wie eine eingehende Abfrage auf der Seite von PostgreSQL oder einer Software von Drittanbietern neu geschrieben werden kann.
Aus diesem Grund wurde (natürlich auch nur zum Spaß) eine ziemlich einfache Software geschrieben, die Verbindungen von Clients akzeptiert und diese in PostgreSQL vertritt. Gleichzeitig werden eingehende SQL-Abfragen gelesen und bei Bedarf ersetzt.
Teilen eines Links zu GithubObwohl ich keine Binärpakete erstellt habe, haben meine Hände nicht erreicht. Die Montage ist aber recht einfach. Alles ist in C ++ / Qt geschrieben, weil Ich habe lange darüber geschrieben ...
Die Konfiguration ist ziemlich einfach:
Geben Sie an, welche Schnittstelle und welcher Port abgehört werden sollen:
listen_address=0.0.0.0 listen_port=5433
Wir zwingen fahrlässige Software, eine Verbindung zur angegebenen Adresse herzustellen, anstatt eine direkte Verbindung zum PostgreSQL-Server herzustellen.
Wir schreiben auf, wohin Verbindungen weitergeleitet werden sollen (in diesem Beispiel befindet sich der Proxy auf demselben Computer wie der PostgreSQL-Server):
dst_address=127.0.0.1 dst_port=5432
Wir setzen einen regulären Ausdruck, um die gewünschte Anfrage zu fangen:
query = SELECT \* FROM tablename WHERE (.+)
Wir sagen, dass wir es umschreiben müssen:
action = rewrite
Wir sagen, wie man umschreibt:
rewrite = SELECT * FROM tablename WHERE (col3 >= '$(now-1M)') AND $(1)
In diesem Beispiel fügen wir den Abfragebedingungen nach der Spalte mit dem Datum einen Filter hinzu, der angibt, dass wir nur an Datensätzen für den letzten Monat interessiert sind.
Man könnte so schreiben:
rewrite = SELECT * FROM tablename WHERE (col3 >= now() - interval '1 month') AND $(1)
Dann ist die Anforderung jedoch aufgrund des Vorhandenseins der Funktion now () nicht optimal. Die Suche wird weiterhin auf allen Partitionen ausgeführt. Um nur im Notwendigen zu suchen, müssen Sie einen konstanten Wert angeben. Daher ersetzt unser Proxy den Zeitstempel durch eine Verschiebung von einem Monat anstelle des Konstrukts $ (jetzt-1M).
Ergebnis (aus dem Protokoll):
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;
Somit ist es grundsätzlich möglich, jede Anfrage zu ersetzen. Die Antworten vom Server werden nicht geändert und unverändert an den Client übertragen. Auf diese Weise wird die Übertragungsverzögerung minimiert. Darüber hinaus wartet die Anwendung normalerweise auf eine Antwort eines bestimmten Formats, sodass es unerwünscht ist, den Satz von Spalten in der Anforderung und Antwort zu ändern.
Es ist auch möglich, alle Anfragen von Interesse einfach in das Protokoll zu drucken:
query = .+ action = log
Das Repository verfügt über eine Konfiguration mit Beispielen und einer detaillierteren Beschreibung.
Übrigens ist es einfach festzustellen, wie gut der Entwickler richtig schreibt, um mit der Datenbank zu arbeiten. Wenn Sie beispielsweise eine so häufig ausgeführte Anfrage sehen, ist es Zeit für jemanden, Handbücher zu rauchen.
INSERT INTO tablename (col1, col2, col3) VALUES('value1', 1, '2018-12-31')
Es sollte so sein:
INSERT INTO tablename (col1, col2, col3) VALUES($1::varchar, $2::integer, $3::date)
Leider kann unser Proxy bisher nicht so schreiben: / aber das ist nicht schwer zu tun. Vielleicht wird es in Zukunft damit möglich sein, die erste Anfrage in die zweite umzuschreiben.
Ja, der wichtige Punkt ist, dass SSL noch nicht unterstützt wird, sodass alle Verbindungen von Clients zu Proxys ohne Verschlüsselung sind.
Ich freue mich über Kommentare und Kommentare.
Wenn ein aktives Interesse der Benutzer besteht, werde ich das Projekt vielleicht weiterentwickeln.
Sie können Arbeit mit anderen Datenbanken hinzufügen.