Tips & Tricks für die Starface Postgresql Datenbank

Da wir immer noch relativ große Probleme mit der Performance unserer Starface haben möchte ich mich mal ein wenig in die Postgresql Datenbank die von der Starface verwendet wird einarbeiten, deshalb habe ich mir hier einen Blogeintrag gemacht in dem ich die wichtigsten Commands und Tricks für die Arbeit mit der Postgresql Datenbank von Starface zusammentrage:

Alle Arbeiten mit der Datenbank kann man von der Command line von der Starface Telefonanlage ausführen.



Interaktives SQL starten:

psql asterisk

Im SQL kann man dann beliebige SQL Befehle eingeben. Achtung der SQL Befehl muss mit einem Strichpunkt enden. Den Befehlsinterpreter kann man mit "\q" wieder verlassen.

Position der Postgresql Config Datei ermitteln

show config_file;

Reload der Postgresql Konfig nach Änderungen an der postgresql.conf

SELECT pg_reload_conf();

 Alle Tabellen in der Starfacedatenbank auflisten

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' and table_type='BASE TABLE'

Die Verwendung von div. Indizes in der Datenbank prüfen

select relname,indexrelname,idx_scan,idx_tup_read, idx_tup_fetch from pg_stat_user_indexes;

Diese Anweisung zeigt sämtliche Indizes an und zeigt, ob diese für einen Index Scan, für einen Index Read oder für einen Index Fetch verwendet werden. Indizes die in allen Spalten eine 0 haben sind obsolet und werden nicht benötigt.

Zugriffsstatistiken für alle SQL Tabellen in Postgresql anzeigen

select relname,n_live_tup, seq_scan, idx_scan from pg_stat_user_tables;

Diese Anweisung zeigt allen Tabellen und wieviele Zeilen die Tabelle hat und mit welcher Zugriffsmethode auf die Tabellen zugegriffen wird. Tabellen mit vielen Zeilen und hohen Werten bei seq_scan sind Kandidaten für einen Index. 

 SQL's mit langen Laufzeiten finden 

PostgreSQL hat einen Mechanismus mit dem sich lang laufende SQL Anweisungen aufspüren lassen.Diesen Mechanismus kann man sich auch auf der Starfacetelefonanlage zu Nutze machen. Prinzipiell ist das Logging auf der Starfaceanlage schon aktiviert, nur ist der Schwellenwert für schnellere Hardware etwas zu hoch gesetzt. Deshalb sollte man wenn man Performanceprobleme analysieren will, den Schwellenwert an seine Anforderungen anpassen. Dazu editiert man die "postgresql.conf". Diese befindet sich auf der Starface im Ordner "var/lib/pgsql/data". Falls man die Datei nicht findet, kann man sie wie oben stehend ermitteln.

nano /var/lib/pgsql/data/postgresql.conf

In Nano sucht man dann den Eintrag "log_min_duration_statement" Mit diesem Wert kann man steuern wieviele Millisekunden die Ausführung eines SQL Befehls dauern muss, damit der Befehl mit der Dauer in das Log geschrieben wird. Mit der Angabe von 0 kann man jeden Befehl protokollieren lassen. Achtung das sollte man natürlich nur auf eine Testmaschine machen, da sonst extrem viel protokolliert wird. Den geeigneten Wert für seine Umgebung muss man selbst herausfinden, da dies natürlich stark von der Hardware abhängig ist.

Nach dem Speichern der Änderung muss man mit

 SELECT pg_reload_conf();

die Konfiguration von Postgresql neu einlesen.

Danach wird jede Ausführung eines SQL Commands in die für den Tag passende Logdatei von Postgresql geschrieben. Die Logdateien befinden sich in "var/lib/pgsql/data/pg_log"

Entweder kopiert man sich das Logfile auf seinen PC oder man sieht es sich mit nano an. Eine Möglichkeit ist auch, die letzten 40 Einträge des Logfile mit

tail -n40 -f /var/lib/pgsql/data/pg_log/ postgresql-Sat.log

ausgeben zu lassen.

Das Log sieht dann ungefähr so aus und man sieht gleich, dass die Starfaceprogrammierer mehrere schwere Fehler machen:

[2015-09-12 10:31:53 CEST] LOG:  duration: 0.016 ms  execute <unnamed>: SELECT now()
[2015-09-12 10:31:53 CEST] LOG:  duration: 0.352 ms  parse <unnamed>: SELECT accountid FROM account2voicemailuser WHERE voicemailuserid=2464
[2015-09-12 10:31:53 CEST] LOG:  duration: 0.023 ms  bind <unnamed>: SELECT accountid FROM account2voicemailuser WHERE voicemailuserid=2464
[2015-09-12 10:31:53 CEST] LOG:  duration: 0.039 ms  execute <unnamed>: SELECT accountid FROM account2voicemailuser WHERE voicemailuserid=2464
[2015-09-12 10:31:53 CEST] LOG:  duration: 0.310 ms  parse <unnamed>: SELECT id, filename, description FROM account2voicemailannounce WHERE accountid=1000
[2015-09-12 10:31:53 CEST] LOG:  duration: 0.022 ms  bind <unnamed>: SELECT id, filename, description FROM account2voicemailannounce WHERE accountid=1000
[2015-09-12 10:31:53 CEST] LOG:  duration: 0.024 ms  execute <unnamed>: SELECT id, filename, description FROM account2voicemailannounce WHERE accountid=1000
[2015-09-12 10:31:53 CEST] LOG:  duration: 0.106 ms  parse <unnamed>: SELECT id, filename, description FROM account2voicemailannounce WHERE accountid=2398
[2015-09-12 10:31:53 CEST] LOG:  duration: 0.041 ms  bind <unnamed>: SELECT id, filename, description FROM account2voicemailannounce WHERE accountid=2398
[2015-09-12 10:31:53 CEST] LOG:  duration: 0.017 ms  execute <unnamed>: SELECT id, filename, description FROM account2voicemailannounce WHERE accountid=2398
[2015-09-12 10:31:53 CEST] LOG:  duration: 0.097 ms  parse <unnamed>: SELECT id, filename, description FROM account2voicemailannounce WHERE accountid=0
[2015-09-12 10:31:53 CEST] LOG:  duration: 0.020 ms  bind <unnamed>: SELECT id, filename, description FROM account2voicemailannounce WHERE accountid=0
[2015-09-12 10:31:53 CEST] LOG:  duration: 0.025 ms  execute <unnamed>: SELECT id, filename, description FROM account2voicemailannounce WHERE accountid=0

Erstens werden sehr oft, keine PreparedStatements verwendet, was den Code anfällig für SQL-Injections macht und zweitens wenn PreparedStatements verwendet werden, dann werden diese nicht wieder verwendet, was sich natürlich sehr negativ auf die Performance auswirkt. Ich werde in einen späteren Post genauer auf das eingehen.

Ausführung eines SQL Statements erklären lassen.

Postgresql kann für jedes SQL Statement eine genaue Erklärung ausgeben, wie das SQL Statement von Postgresql verarbeitet wird. Dies wird erreicht, in dem an der SQL Anweisung ein "Explain" voranstellt. z.B. 

asterisk=# EXPLAIN SELECT s.id, s.name FROM service s JOIN telephonenumber2service t2s ON t2s.serviceid=s.id WHERE t2s.telephonenumberid=10090;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=8.28..9.76 rows=1 width=62)
   Hash Cond: (s.id = t2s.serviceid)
   ->  Seq Scan on service s  (cost=0.00..1.34 rows=34 width=62)
   ->  Hash  (cost=8.27..8.27 rows=1 width=4)
         ->  Index Scan using telephonenumber2service_pkey on telephonenumber2service t2s  (cost=0.00..8.27 rows=1 width=4)
               Index Cond: (telephonenumberid = 10090)
(6 rows)
 
Damit bekommt man ein gutes Gefühl, wie die Datenbank eine Anweisung abarbeitet und wo man eventuell optimieren kann.