Ich richte eine Webanwendung mit einem FreeBSD PostgreSQL-Backend ein. Ich bin auf der Suche nach einem Tool zur Optimierung der Datenbankleistung.
pgfouine funktioniert ziemlich gut für mich. Und es sieht so aus, als ob es einen FreeBSD-Port dafür gibt.
Die Datenbankoptimierung ist normalerweise eine Kombination aus zwei Dingen
Die Anzahl der Abfragen wird in der Regel durch das Zwischenspeichern nichtflüchtiger / weniger wichtiger Daten (z. B. "Welche Benutzer sind online" oder "Was sind die neuesten Beiträge dieses Benutzers?") innerhalb der Anwendung (wenn möglich) oder in ein externer - effizienterer - Datenspeicher (memcached, redis, etc.). Wenn Sie Informationen haben, die sehr schreibintensiv sind (zB Hit-Counter) und keine ACID -Semantik benötigen Sie können auch daran denken, es aus der Postgres-Datenbank in effizientere Datenspeicher zu verschieben.
Das Optimieren der Query-Laufzeit ist komplizierter - dies kann dazu führen, spezielle Indizes zu erstellen (oder Indizes in erster Linie ), ändern (möglicherweise denormalizing) das Datenmodell oder ändern die grundlegender Ansatz der Anwendung, wenn es um die Arbeit mit der Datenbank geht. Siehe zum Beispiel die Seitenumbruch auf Postgres Weise mit Markus Winand zum Umdenken des Konzepts der Seitennumerierung, um es datenbankeffizienter zu machen
Aber um zu verstehen, welche Abfragen zuerst betrachtet werden sollten, müssen Sie wissen, wie oft sie ausgeführt werden und wie lange sie im Durchschnitt laufen.
Ein Ansatz hierfür ist die Protokollierung aller (oder "langsamen") Abfragen einschließlich ihrer Laufzeit und die anschließende Analyse des Abfrageprotokolls. Ein gutes Werkzeug hierfür ist pgfouine , das bereits in dieser Diskussion erwähnt wurde und seitdem durch pgbadger , das in einer freundlicheren Sprache geschrieben ist, ist viel schneller und aktiver gepflegt.
Sowohl pgfouine als auch pgbadger leiden unter der Tatsache, dass sie die Abfrageprotokollierung aktiviert haben müssen, was zu einem merklichen Leistungseinbruch in der Datenbank führen kann oder Sie auf Plattenplatzprobleme bringt, zusätzlich zu der Tatsache, dass das Protokoll analysiert wird Das Tool kann einige Zeit in Anspruch nehmen und gibt Ihnen keine aktuellen Erkenntnisse darüber, was in der Datenbank vor sich geht.
Um diese Mängel zu beheben, gibt es jetzt zwei Erweiterungen, die die Abfrageleistung direkt in der Datenbank verfolgen: pg_stat_statements (was nur in Version 9.2 oder neuer hilfreich ist) und pg_stat_plans . Beide Erweiterungen bieten dieselbe grundlegende Funktionalität - Tracking, wie oft eine "normalisierte Abfrage" (Abfragezeichenfolge abzüglich aller Ausdrucksliterale) ausgeführt wurde und wie lange sie insgesamt dauerte. Aufgrund der Tatsache, dass dies durchgeführt wird, während die Abfrage tatsächlich ausgeführt wird, geschieht dies auf sehr effiziente Weise, der messbare Overhead betrug in synthetischen Benchmarks weniger als 5%.
Die Liste der Abfragen selbst ist aus der Informationsperspektive sehr "trocken". Es gab Arbeit an einer dritten Erweiterung, die versucht, diese Tatsache anzugehen und eine schönere Darstellung der Daten anzubieten, die pg_statsinfo genannt werden (zusammen mit pg_stats_reporter ), aber es ist ein bisschen eine Verpflichtung, es in Gang zu bringen.
Um eine bequemere Lösung für dieses Problem zu bieten, habe ich begonnen, an einem kommerziellen Projekt zu arbeiten, das sich auf pg_stat_statements und pg_stat_plans konzentriert und die Informationen aus vielen anderen Daten aus der Datenbank erweitert. Es heißt pganalyze und Sie finden es unter Ссылка .
Um einen kompakten Überblick über interessante Werkzeuge und Projekte im Postgres Monitoring Bereich zu geben, habe ich auch eine Liste im Postgres Wiki die regelmäßig aktualisiert wird.
Ich habe pgtop ein wenig benutzt. Es ist ziemlich grob, aber zumindest kann ich sehen, welche Abfrage für jede Prozess-ID ausgeführt wird.
Ich habe versucht, pgfouine, aber wenn ich mich erinnere, ist es ein Offline-Tool.
Ich beende auch die psql.log-Datei und setze die Protokollierungskriterien auf eine Ebene, auf der ich die Problemabfragen sehen kann.
%Vor%Ich benutze auch den EMS Postgres Manager, um allgemeine Verwaltungsaufgaben zu erledigen. Es tut nichts für Sie, aber es macht die meisten Aufgaben einfacher und macht das Überprüfen und Einrichten Ihres Schemas einfacher. Ich finde, dass es bei Verwendung einer GUI viel einfacher ist, Inkonsistenzen zu erkennen (wie einen fehlenden Index, Feldkriterien usw.). Es ist nur eines von zwei Programmen, die ich verwenden möchte, um VMWare auf meinem Mac zu benutzen.
Munin ist ziemlich einfach, aber effektiv, um Trends zu erkennen, wie sich die Datenbank im Laufe der Zeit entwickelt und weiterentwickelt. Im Standard-Kit von Munin können Sie unter anderem die Größe der Datenbank, die Anzahl der Sperren, die Anzahl der Verbindungen, sequenzielle Scans, die Größe des Transaktionslogs und lang laufende Abfragen überwachen.
Einfach einzurichten und zu beginnen und wenn nötig können Sie Ihr eigenes Plugin ganz einfach schreiben.
Sieh dir die neuesten Postgresql-Plugins an, die mit Munin ausgeliefert werden:
Nun, das erste, was zu tun ist, versuchen Sie alle Ihre Abfragen von psql mit "erklären" und sehen, ob es sequentielle Scans gibt, die in Index-Scans konvertiert werden können, indem Sie Indizes hinzufügen oder die Abfrage neu schreiben.
Abgesehen davon bin ich genauso an den Antworten auf diese Frage interessiert wie Sie.
Tags und Links sql optimization database postgresql freebsd