A journey with Vault – Teil 1

A journey with Vault – Teil 1

This entry is part of 1 in the series a journey with vault

Hello fellow blog readers!

Heute möchte ich euch auf die Reise mit Vault by Hashicorp mitnehmen.

Zunächst was ist Vault? Bei Vault handelt es sich stumpf gesagt, um einen Passwortspeicher. Vermutlich kommen da jetzt bei dem einen oder anderen Projekte wie Keypass oder Enpass in den Sinn. Die Richtung ist schon mal gut. Jedoch kennt auch jeder das Hauptproblem der oben genannten Lösungen nur zu gut.

Teamfähigkeit.

Das eine Projekt beherrscht es, andere nur teilweise oder vieleicht sogar garnicht. Frustrierend könnte man die Situation auch gerne umschreiben. Fakt ist auf jeden Fall das es sich bei Vault um eine Lösung handelt, die wirklich das Zeug dazu hat ein Teamfähiger Passwortspeicher zu sein. Wie so alles in der Welt haben Dinge leider ihren Preis. Man wird mit Teamfähigkeit gesegnet, aber Satan bestraft uns indirekt mit der Komplexität des ganzen Konstrukts, weswegen ich das Abenteuer Vault in eine kleine Serie verpacke. Genug Worte für den Einstieg, legen wir los mit dem neuen Abenteuer in der Hautprolle: Vault.

Part Uno widment sich der grundlegenden Inbetriebnahme von Vault.

Wie immer benutzte ich eine mit Vagrant provisionierte höchst aktuelle CentOS 7 Box der Marke Eigenbau mit VirtualBox als Provider.

Die Reise beginnt mit dem Download eines ZIP-Archivs welche das Vault binary enthält. Den legen wir einfach unter /tmp ab und entpacken ihn direkt nach /usr/local/bin

wget https://releases.hashicorp.com/vault/1.3.0/vault_1.3.0_linux_amd64.zip -P /tmp
unzip /tmp/vault_1.3.0_linux_amd64.zip -d /usr/local/bin
chown root. /usr/local/bin/vault

Damit das aufrufen von Vault direkt gelingt müssen wir unsere PATH Variable noch um /usr/local/bin ergänzen. Ich hab das ganze in meinem ~/.bash_profile hinterlegt:

PATH=$PATH:$HOME/bin:/usr/local/bin

Wenn alles korrekt ausgeführt wurde, können wir jetzt die Autocompletion nachziehen und anschließend die Shell neustarten:

vault -autocomplete-install
complete -C /usr/local/bin/vault vault
exec $SHELL

Um das ganze abzurunden lassen wir Vault als Daemon laufen.

Zunächst müssen wir es Vault gestatten mlock syscalls ohne der Notwendigkeit von root ausführen zu dürfen:

setcap cap_ipc_lock=+ep /usr/local/bin/vault

Danach legen wir einen nicht priviligierten Systembenutzer an, unter dem der Vault Daemon später laufen soll:

useradd --system --home /etc/vault.d --shell /bin/false vault

Jetzt kommt die systemd Unit:

touch /etc/systemd/system/vault.service

… mit folgenden Inhalt:

[Unit]
Description="HashiCorp Vault - A tool for managing secrets"
Documentation=https://www.vaultproject.io/docs/
Requires=network-online.target
After=network-online.target
ConditionFileNotEmpty=/etc/vault.d/vault.hcl
StartLimitIntervalSec=60
StartLimitBurst=3

[Service]
User=vault
Group=vault
ProtectSystem=full
ProtectHome=read-only
PrivateTmp=yes
PrivateDevices=yes
SecureBits=keep-caps
AmbientCapabilities=CAP_IPC_LOCK
Capabilities=CAP_IPC_LOCK+ep
CapabilityBoundingSet=CAP_SYSLOG CAP_IPC_LOCK
NoNewPrivileges=yes
ExecStart=/usr/local/bin/vault server -config=/etc/vault.d/vault.hcl
ExecReload=/bin/kill --signal HUP $MAINPID
KillMode=process
KillSignal=SIGINT
Restart=on-failure
RestartSec=5
TimeoutStopSec=30
StartLimitInterval=60
StartLimitIntervalSec=60
StartLimitBurst=3
LimitNOFILE=65536
LimitMEMLOCK=infinity

[Install]
WantedBy=multi-user.target

Bevor wir den Daemon starten können, müssen wir ein paar Verzeichnisse sowie eine Konfigurationsdatei anlegen:
mkdir -pv /etc/vault.d/
mkdir -pv /usr/local/share/vault/data/
chown -R vault. /usr/local/share/vault/

touch /etc/vault.d/vault.hcl

Meine Konfigurationsdatei ist als Beispielhaft anzusehen. Sie behinhaltet das nötigste um den Vault Server grundsätzlich starten zu können. Diese sollte entsprechend an das eigene Szenario angepasst werden und unbedingt mit Zertifikaten ausgestattet sein!

storage "file" {
path = "/usr/local/share/vault/data"
}

ui = true

listener "tcp" {
address = "172.28.128.25:8200"
tls_disable = "true"
}

api_addr = "http://172.28.128.25:8200"
cluster_addr = "http://172.28.128.25:8201"

systemd neuladen und den Vault Daemon starten:

systemctl daemon-reload
systemctl start vault

Wenn uns alles geglückt ist, sollten wir unter der Adresse des Servers, mit Angabe des Ports 8200 nun die UI von Vault vorfinden. Damit geht es nun in der Bildstrecke weiter:

Das wars für den ersten Teil der Serie, im zweiten Teil werden wir uns den Aufbau von Vault genauer anschauen und uns der integration von SSH widment. Vault bietet nämlich viele Integrationsmöglichkeiten mit denen sich am Ende die Authentifizierung von sämtlichen Dienste Zentralisiert über Vault steuern lassen. Bis dahin wünsche ich wie immer viel Spass beim Basteln!

Photo from: https://devopscube.com/setup-hashicorp-vault-beginners-guide/

Max Deparade
Max Deparade
Consultant

Max ist seit Januar als Consultant bei NETWAYS und unterstützt tatkräftig unser Professional Services Team. Zuvor hat er seine Ausbildung zum Fachinformatiker für Systemintegration bei der Stadtverwaltung in Regensburg erfolgreich absolviert. Danach hat der gebürtige Schwabe, der einen Teil seiner Zeit auch in der Oberpfalz aufgewachsen ist ein halbes Jahr bei einem Managed Hosting Provider in Regensburg gearbeitet, ehe es ihn zu NETWAYS verschlagen hat. In seiner Freizeit genießt Max vor allem die Ruhe, wenn...

NETWAYS stellt sich vor – Justin Müller

This entry is part 1 of 16 in the series NETWAYS stellt sich vor

Name: Justin Müller

Alter: 19

Position bei NETWAYS: Junior Developer

Ausbildung: Fachinformatiker im Bereich Anwendungsentwicklung

Bei NETWAYS seit: September 2019

 

 

Wie bist du zu NETWAYS gekommen und was genau gehört zu Deinem Aufgabenbereich?

In meiner damaligen Abiturklasse lernte ich einen Jungen namens Niko kennen, der dieselben Interessen wie ich teilt. Auch wenn sich unsere schulischen Wege nach gerade mal einem halben Jahr trennten, blieben wir in Kontakt und sind schlussendlich außerhalb vom Unterricht sehr gute Freunde geworden.

Nachdem ich mein Fachabitur dieses Jahr abgeschlossen hatte, entschied ich mich dazu eine Ausbildung zu suchen. Es musste auf jeden Fall ein Beruf sein, der etwas mit Programmieren zu tun hat. Folglich habe ich mich schlau gemacht und bin auf den Beruf des Fachinformatikers gestoßen. Mir fiel auf, dass sich mein Freund Niko vor einem Jahr für denselben Beruf bewarb. Ich kam auf ihn zu und er empfahl mir die Firma, bei der er seine Ausbildung macht und kurz darauf schickte ich meine Bewerbung an NETWAYS. Nach dem kurz darauffolgenden angenehmen Bewerbungsgespräch mit Eric und Marius war mir klar, ich muss in diese Firma. Am selben Tag des Bewerbungsgespräches erhielt ich die Zusage und nahm die Stelle ohne zu zögern an.

Mein Aufgabenbereich ist sehr vielfältig. Im Moment arbeite ich mich anhand von gegebenen Aufgaben in golang und Icinga ein. Neben dem Einarbeiten sind Henrik und ich außerdem für den Check Scheduler und den Process Spawner zuständig.

Was macht Dir an Deiner Arbeit am meisten Spaß?

Unbekannte Softwarte entdecken, eigene Programme entwickeln, Schulungen und die firmeninterne Kommunikation und Offenheit. Das sind alles Dinge, die sehr viel Spaß in meiner Ausbildung garantieren. Am meisten Spaß macht es jedoch, selbständig an einem eigenen Arbeitsplatz arbeiten zu dürfen.

Welche größeren, besonders interessanten Projekte stehen künftig an?

Wie oben schon erwähnt, sind Henrik und ich für den Check Scheduler und Process Spawner zuständig. Bis diese Aufgabe gemeistert ist, wird noch viel Zeit vergehen. Danach freue ich mich auf verschiedenste Abteilungswechsel und Schulungen zur Weiterbildung. Die Mitarbeit an Icinga bedeutet mir sehr viel und ich freue mich auf zukünftige Aufgaben, die das Ziel haben, Icinga besser zu machen.

Was machst Du, wenn Du mal nicht bei NETWAYS bist?

Wie man sich denken kann, verbringe ich viel Zeit zuhause vor dem Computer. Neben dem Fakt, dass ich gerne Computerspiele spiele und das auch sehr oft und lange, lasse ich meiner Kreativität in der Musik freien Lauf. Natürlich spiele ich nicht nur PC, sondern verbringe auch viel Zeit mit meinen Freunden. In dem Bereich bin ich sehr vielfältig, da mir immer Dinge einfallen, die man zusammen unternehmen kann. Auch nennenswert ist, dass ich sehr gerne mit Freunden andere Städte besuche.

Wie geht es in Zukunft bei Dir weiter?

Ich hoffe im Moment, dass ich die Probezeit bestehe und meine Ausbildung gut abschließe. Natürlich werde ich im Laufe der nächsten Jahre sehr viel Neues lernen, deswegen will ich soviel wie möglich davon im Kopf behalten, um dann zukünftig ein guter Fachinformatiker zu werden. Außerdem denke ich darüber nach, nach meiner Ausbildung, Ausbilder bei NETWAYS zu werden. Aber das sei mal dahin gestellt ;).

Justin Müller
Justin Müller
Junior Developer

Nachdem er genug von der Schule hatte, hat sich Justin dazu entschieden eine Ausbildung bei NETWAYS zu beginnen. Die Zeit, die er bis jetzt in der Firma verbracht hat, war für ihn sehr lehrreich und spaßig. Als Frühaufsteher freut er sich täglich auf die Arbeit bei NETWAYS und hofft zudem, dass er noch viel als Fachinformatiker im Bereich Anwendungsentwicklung lernen und erleben wird. Natürlich ist Arbeit nicht alles. In seiner Freizeit betreibt er neben dem...
Monthly Snap November 2019

Monthly Snap November 2019

The NETWAYS-November was, as usual, all about the OSMC. And, as usual, it was a splendid event with interesting talks, great food and the best attendees! But what else was on our minds?

The Juniors

Our Junior Consultant Aleksander started the month with Windows Passwort mit Linux zurücksetzen, wherein he described useful tools for resetting your Windows-password.  Artur wrote about his first project in Azubiprojekt für das erste Lehrjahr: Framadate!

SHOP

What does IoT mean, and which products do we offer with it? Nicole let us know in HW group: IoT mit Modbus und MQTT. She also gave us an overview on the various AKCP products in our shop in AKCP sensorProbe: 2, 4 oder 8 Sensoren, potentialfreie Kontakte und PoE. Our apprentices get to visit our different departments to learn more about the company. Artur visited the NETWAYS shop for a week and learnt about our offered products. Read what he found out about AKCP in AKCP sensorProbeX+: Individuelle Monitoring-Lösungen für IoT. A new product will soon be available in our shop: Leonie presented the SMSEAGLE MHD-8100-3G/4G.

Tipps from techies

Our consultants travel a lot, mostly by train. Christoph recommends a website that helps travellers find the best train wagon for their needs and informs of delays and technical Problems in Wie bekomme ich bessere Informationen über Züge als die DB. In SSH – Der Schlüssel zum Erfolg, David gave us a few tips on how to use SSH. According to Johannes Text-Utils can be of importance. Read Text-Utils unter Linux – Wer kennt sie?

OpenStack

Martin wrote his very first NETWAYS- blogpost this month! OpenStack made easy – Sicherheitsgruppen verwalten und zuweisen, which was also his first contribution to the blog series OpenStack made easy. And the next contribution came from Fabian: In OpenStack made easy… Mit Icinga 2-Master Maschinen überwachen he explains how to easily monitor Icinga 2 Master machines with OpenStack. Now it was Martins turn again! Read OpenStack made easy – Snapshots erstellen, rotieren, einspielen, where he taught us how to program Open Stack to create backup snapshots regularly.

#lifeatnetways

This month, two employees shared a bit about themselves in our series NETWAYS stellt sich vor. Read about our junior consultant Tobias, and the newest member of team-marketing Anke!

Keep developing

Florian proudly presented the new Icinga DB user interface: Das neue User Interface von Icinga DB. Check it out! Noah shared his knowledge on GitLab in GitLab-CI / YAML – Write less with Anchors, Extends and Hidden Keys. What is this SVG Feu is talking about? Read the Introduction to SVG: The Basics.

The OSMC after-effect

Dirk reported live from the OSMC! Read his thoughts on OSMC 2019 – Day 1. And what did Dirk do on the next day? Of course, he kept us up to date on talks, the evening event and delicious food in OSMC 2019 – Day 2. After the OSMC we traditionally have on Open Source Camp, where an open source project can be treated thoroughly. This year was all about Foreman. Alexander gave us his personal recap of the Open Source Camp on Foreman. For some attendees the hackathon after the OSMC is the highlight of the conference. Henrik enjoyed the Hackathon immensely, and highly recommends that we all attend next year! OSMC Hackathon – Share your impressions!

Catharina Celikel
Catharina Celikel
Office Manager

Catharina unterstützt seit März 2016 unsere Abteilung Finance & Administration. Die gebürtige Norwegerin ist Fremdsprachenkorrespondentin für Englisch. Als Office Manager kümmert sie sich deshalb nicht nur um das Tagesgeschäft sondern übernimmt nebenbei zusätzlich einen Großteil der Übersetzungen. Privat ist der bekennende Bücherwurm am liebsten mit dem Fahrrad unterwegs.

E-Mails und Bereitschaft nach der Arbeitszeit – “Just fit – Just awesome”

This entry is part 10 of 10 in the series Just fit – Just awesome

Heute möchte ich unsere Blogserie “Just fit – Just awesome” fortführen und mich dem Thema “E-Mails und Bereitschaft nach der Arbeitszeit” widmen.

E-Mails sind heutzutage ein “notwendiges Übel” und stellen bei durchschnittlichen Büroarbeitern den häufigsten Grund für Unterbrechungen dar. Im Schnitt werden wir während der Arbeitszeit alle 11 Minuten unterbrochen. Eine 2005 vom King’s College in London dazu durchgeführte Studie bestand aus 3 Gruppen:

  • Gruppe A: Probanden, die nicht unterbrochen wurden
  • Gruppe B: Probanden, die unterbrochen wurden
  • Gruppe C: Probanden, die Marihuana geraucht hatten

Das Ergebnis der Studie macht es relativ deutlich: Die Personen der Gruppe B, die während der Tätigkeiten unterbrochen wurden, benötigten ca. 50 % länger für ihre Aufgaben als die Personen der Gruppe A. Das ließe sich vielleicht noch durch Mehrarbeit auffangen. Was noch viel schwerer wiegt, ist allerdings auch die um ca. 50 % erhöhte Fehlerquote im Gegensatz zur Vergleichsgruppe A. Selbst Gruppe C unter Einfluss von Marihuana, schnitt besser ab als Gruppe B, aber auch deutlich schlechter als Gruppe A.

Als Erkenntnis daraus sollte man sich also Gedanken machen, um die Unterbrechungen möglichst zu minimieren. Beim Thema E-Mail gibt es da verschiedene Ansätze: Beispielsweise lässt sich die automatische Benachrichtigung des Mailpostfachs für neu eintreffende Nachrichten abschalten, um Ablenkungen zu vermeiden. Für Microsoft Outlook findet sich hier eine Anleitung. Je nach Aufgabengebiet macht es dann natürlich trotzdem Sinn, zu gewissen Zeiten einen Blick ins Postfach zu werfen, um die angefallenen Nachrichten gezielt abzuarbeiten. Dafür sollten allerdings fixe Zeiten eingeplant werden. In der Praxis kommt es sehr häufig vor, dass auch diese fest eingeplanten Zeiten nicht eingehalten werden und vielleicht aufgrund von vermeintlich zu niedriger Priorität gegenüber anderen Aufgaben verschoben werden. Abhilfe schafft hier, diese Zeiten wie Meetings mit anderen Personen zu behandeln und vielleicht auch strikt in den Kalender einzutragen. Kommen trotzdem Unterbrechungen zustande, ist es sehr effektiv, wenn man sich angewöhnt, in dem Moment Notizen zu machen, um schnell den Wiedereinstig zu finden.

Quelle: https://stockata.de/

Ein weiterer Ansatz ist, die erhaltenen E-Mails grundsätzlich in Frage zu stellen: Viele E-Mails sind nur informativer Natur und ziehen keinen Handlungsschritt nach sich. So kann man sich beispielsweise von einigen Mailverteilern austragen (lassen) und teilweise gegen alternative Methoden wie Übersichtsseiten à la GitHub), RSS-Feader (z.B. für den NETWAYS Blog) und eigene Suchen im Ticketsystem ersetzen. Auch hier sollte man sich den Interessensgebieten dann zu festen Zeiten widmen und die angefallenen Meldungen sichten. Somit lässt sich auch die “Mailflut” nach dem zweiwöchigen Sommerurlaub eindämmen und die Urlaubsstimmung hält vielleicht doch noch ein oder zwei Tage länger an ;-).

In der Regel ist es sowieso meistens so, dass wirklich wichtige Nachrichten auch ohne permanenten Zugriff auf das Mailpostfach ankommen. Zugegeben verschafft der zeitliche Informationsvorsprung manchmal schon Vorteile, verglichen mit den Massen an Mails, die dazu sofort gelesen werden müssen, heben sich diese aber auch schnell wieder auf.

Gerade im IT-Bereich fühlen sich viele auch in der Freizeit verpflichtet, ständig und überall erreichbar zu sein. Neben dem Zugriff auf E-Mails ist das v.a. auch die telefonische Erreichbarkeit. Können wirklich Störungen oder Kundenanfragen auftreten, die sofort behandelt werden müssen, sollte es dafür einen offiziell geregelten (und bezahlten) Bereitschaftsdienst mit wechselnder Belegschaft geben. Wenn sich das Ganze mal “eingeschlichen” hat, ist die Hemmschwelle, dem Kollegen auf dem Privathandy anzurufen, sonst deutlich geringer. Aus meiner Zeit als Systemadministrator weiß ich nur zu gut, dass man sich für sein “Baby” irgendwo auch verantwortlich fühlt und schon alleine aus Gründen der Arbeitsplatzerhaltung manchmal doch außerhalb der Reihe eingreifen muss, das sollte jedoch die große Ausnahme bleiben.

Ein etwas schwammiges Thema ist noch die Arbeit im “HomeOffice”. Hier hilft v.a. eins: Selbstdisziplin! So ist es sehr hilfreich, die normalen Gewohnheiten beizubehalten und sich beispielsweise in “Berufskleidung” zu festen Zeiten an den Schreibtisch zu begeben. Umso besser, wenn dieser in einem eigens dafür vorgesehen Raum steht und so die Trennung zwischen privat und beruflich genutztem Bereich leichter fällt. Auch private Tätigkeiten wie Einkaufen, Abholen von Paketen oder die tägliche Wäsche sollte auf die Pausen- bzw. Feierabendzeiten verlegt werden. Somit gibt es eine klare Trennung zwischen Arbeits- und Freizeit.

Als Fazit lässt sich also sagen, dass weniger manchmal mehr ist bzw. besser formuliert: Umso geordneter die Abläufe sind, desto besser! Damit lassen sich viele Reibungsverluste vermeiden und insgesamt auch effizienter, fehlerfreier und stressfreier arbeiten. Ich wünsche dabei auf jeden Fall viel Erfolg bei der Umsetzung!

Markus Waldmüller
Markus Waldmüller
Lead Senior Consultant

Markus war bereits mehrere Jahre als Sysadmin in Neumarkt i.d.OPf. und Regensburg tätig. Nach Technikerschule und Selbständigkeit ist er nun Anfang 2013 bei NETWAYS als Lead Senior Consultant gelandet. Wenn er nicht gerade die Welt bereist, ist der sportbegeisterte Neumarkter mit an Sicherheit grenzender Wahrscheinlichkeit auf dem Mountainbike oder am Baggersee zu finden.

Schneller LIKEn

Nein, hier soll es nicht um Twitter, Instagram oder Youtube gehen, sondern um Datenbankabfragen in PostgreSQL wie diese:

blog # SELECT * FROM kunden WHERE vorname LIKE 'Ann%';

Diese Abfragen sind recht häufig anzutreffen, man denke z.B. an Drop-Down-Boxen, die z.B. per AJAX mit Vorschlägen gefüllt werden, sobald drei oder mehr Buchstaben eingegeben wurden.

Das Spielfeld

Unsere Beispieldaten enthalten 1.000.000 zufällig generierte Kunden in dieser Form und mit dieser Verteilung von Vornamen, die mit ‘Ann’ beginnen:

blog # \d kunden
                               Table "public.kunden"
┌────────────┬─────────┬───────────┬──────────┬────────────────────────────────────┐
│   Column   │  Type   │ Collation │ Nullable │              Default               │
├────────────┼─────────┼───────────┼──────────┼────────────────────────────────────┤
│ id         │ integer │           │ not null │ nextval('kunden_id_seq'::regclass) │
│ vorname    │ text    │           │ not null │                                    │
│ nachname   │ text    │           │ not null │                                    │
│ strasse    │ text    │           │ not null │                                    │
│ hausnummer │ integer │           │ not null │                                    │
│ plz        │ text    │           │ not null │                                    │
│ ort        │ text    │           │ not null │                                    │
│ bundesland │ text    │           │ not null │                                    │
└────────────┴─────────┴───────────┴──────────┴────────────────────────────────────┘
Indexes:
    "kunden_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "kunden_plz_check" CHECK (length(plz) = 5)

blog # vorname,count(*) FROM kunden WHERE vorname LIKE 'Ann%' GROUP BY vorname;
┌───────────┬───────┐
│  vorname  │ count │
├───────────┼───────┤
│ Anni      │   963 │
│ Annabella │   984 │
│ Anne      │   965 │
│ Annalena  │   971 │
│ Annika    │  1017 │
│ Anna      │  1011 │
│ Ann       │  1003 │
│ Annelie   │   976 │
│ Annemarie │  1001 │
│ Annabell  │   996 │
└───────────┴───────┘
(10 rows)

Ein erster Versuch – “vanilla”

Schauen wir doch mal, wie unsere PostgreSQL-Datenbank eine Suche nach ‘Ann%’ bearbeitet:

blog # EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname LIKE 'Ann%';
┌────────────────────────────────────────────────────────────────────┐
│                           QUERY PLAN                               │
├────────────────────────────────────────────────────────────────────┤
│ Seq Scan on kunden  (cost=0.00..24667.00 rows=10244 width=65)      |
|      (actual time=0.019..90.620 rows=9887 loops=1)                 │
│   Filter: (vorname ~~ 'Ann%'::text)                                │
│   Rows Removed by Filter: 990113                                   │
│ Planning time: 0.100 ms                                            │
│ Execution time: 90.956 ms                                          │
└────────────────────────────────────────────────────────────────────┘
(5 rows)

Ein Seq Scan, also der gefürchtete Sequential-Scan aka. Full table scan; alle Datensätze werden gelesen und ‘vorname’ mit ‘Ann%’ verglichen. Das ist sehr ineffektiv.

Ein Index muss her!

Die Lösung ist offensichtlich: wenn solche Abfragen häufig vorkommen, muss ein Index her. Der wird den Vorgang beschleunigen:

blog # CREATE INDEX vorname_btree_vanilla ON kunden (vorname);
blog # EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname LIKE 'Ann%';
┌───────────────────────────────────────────────────────────────────────┐
│                            QUERY PLAN                                 │
├───────────────────────────────────────────────────────────────────────┤
│ Seq Scan on kunden  (cost=0.00..24667.00 rows=10244 width=65)         |      
|      (actual time=0.011..105.340 rows=9887 loops=1)                   │
│   Filter: (vorname ~~ 'Ann%'::text)                                   │
│   Rows Removed by Filter: 990113                                      │
│ Planning time: 0.195 ms                                               │
│ Execution time: 105.768 ms                                            │
└───────────────────────────────────────────────────────────────────────┘
(5 rows)

Uhm, Moment mal… warum nimmt meine Datenbank nicht den Index zu Hilfe?!? Geht es denn mit einzelnen Werten?

 blog # EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname IN ('Anna','Anne','Annelie');
┌────────────────────────────────────────────────────────────────────────────────────────┐
│                              QUERY PLAN                                                │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on kunden  (cost=59.83..6892.21 rows=2909 width=65)                   |
|      (actual time=1.275..5.054 rows=2952 loops=1)                                      │
│   Recheck Cond: (vorname = ANY ('{Anna,Anne,Annelie}'::text[]))                        │
│   Heap Blocks: exact=2656                                                              │
│   ->  Bitmap Index Scan on vorname_btree_vanilla  (cost=0.00..59.10 rows=2909 width=0) |
|      (actual time=0.652..0.652 rows=2952 loops=1)                                      │
│         Index Cond: (vorname = ANY ('{Anna,Anne,Annelie}'::text[]))                    │
│ Planning time: 0.136 ms                                                                │
│ Execution time: 5.292 ms                                                               │
└────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

Ja, da wird der Index genommen, und die Ausführung ist auch gleich um Größenordnungen schneller.

Was ist also das Problem?

“C” und seine Spätfolgen – Schei* encoding!

Das Geheimnis liegt – wie so häufig – in der Lokalisierung. Btree-Indexe sind (für Text-Daten) auf das C-Locale hin optimiert. Wenn aber die Datenbank (wie heutzutage üblich!) mit en_US.UTF8 oder de_DE.UTF8 initialisiert wurde, müssen wir dem Index bei der Erstellung mitteilen, dass wir pattern operator-Aktionen ausführen können wollen. PostgreSQL kommt mit einem ganzen Haufen dieser Operator Classes.

Für unser TEXT-Feld ‘vorname’ nehmen wir text_pattern_ops. Nach der Erstellung testen wir, ob der Index unsere LIKE-Anfrage beschleunigt und verifizieren, dass auch weiterhin die klassischen <=, == und >= Vergleichsoperatoren funktionieren:

blog # DROP INDEX vorname_btree_vanilla ;
blog # CREATE INDEX vorname_btree_opclass ON kunden (vorname text_pattern_ops);
blog # EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname LIKE 'Ann%';
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                                     QUERY PLAN                                          │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on kunden  (cost=129.19..10234.85 rows=10244 width=65)                 |
|       (actual time=5.327..16.083 rows=9887 loops=1)                                     │
│   Filter: (vorname ~~ 'Ann%'::text)                                                     │
│   Heap Blocks: exact=6830                                                               │
│   ->  Bitmap Index Scan on vorname_btree_opclass  (cost=0.00..126.62 rows=5820 width=0) |
|       (actual time=3.524..3.524 rows=9887 loops=1)                                      │
│         Index Cond: ((vorname ~>=~ 'Ann'::text) AND (vorname ~<~ 'Ano'::text))          │
│ Planning time: 0.378 ms                                                                 │
│ Execution time: 16.650 ms                                                               │
└─────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

blog # EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname IN ('Anna','Anne','Annelie');
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                                        QUERY PLAN                                       │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on kunden  (cost=59.83..6892.21 rows=2909 width=65)                    |
|       (actual time=1.233..5.001 rows=2952 loops=1)                                      │
│   Recheck Cond: (vorname = ANY ('{Anna,Anne,Annelie}'::text[]))                         │
│   Heap Blocks: exact=2656                                                               │
│   ->  Bitmap Index Scan on vorname_btree_opclass  (cost=0.00..59.10 rows=2909 width=0)  |
|       (actual time=0.634..0.634 rows=2952 loops=1)                                      │
│         Index Cond: (vorname = ANY ('{Anna,Anne,Annelie}'::text[]))                     │
│ Planning time: 0.135 ms                                                                 │
│ Execution time: 5.246 ms                                                                │
└─────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

Wunderbar! Und 17ms klingt auch gleich viel besser als 100ms.

Geht da noch was?

Jedes Kind weiß, dass Indexe nur Anfragen wie LIKE ‘Ann%’ beschleunigen können. Für LIKE ‘%nna%’ gibt es leider keine Hilfe von der Datenbank. Ist ja auch irgendwie klar, der Btree muss ja von links nach rechts aufgebaut werden…

EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname LIKE '%nna%';
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                                         QUERY PLAN                                      │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│ Seq Scan on kunden  (cost=0.00..24667.00 rows=19022 width=65)                           |
|      (actual time=0.014..110.607 rows=11993 loops=1)                                    │
│   Filter: (vorname ~~ '%nna%'::text)                                                    │
│   Rows Removed by Filter: 988007                                                        │
│ Planning time: 0.131 ms                                                                 │
│ Execution time: 111.006 ms                                                              │
└─────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)

Aber stimmt das? Gibt es wirklich keine Möglichkeit, solche Abfragen zu beschleunigen?

PostgreSQL ist schier unfassbar erweiterbar, und unter anderem kommt es von Haus aus mit einer Extension pg_trgm, die wiederum operator classes für GIN und GiST Indexe mitbringt.

blog # CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION
blog # CREATE INDEX vorname_gin_trgm ON kunden USING GIN (vorname gin_trgm_ops);
blog # EXPLAIN (ANALYSE,COSTS) SELECT * FROM kunden WHERE vorname LIKE '%nna%';
┌──────────────────────────────────────────────────────────────────────────────────────────┐
│                                           QUERY PLAN                                     │
├──────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on kunden  (cost=183.42..13123.52 rows=19022 width=65)                  |
|       (actual time=5.049..15.935 rows=11993 loops=1)                                     │
│   Recheck Cond: (vorname ~~ '%nna%'::text)                                               │
│   Heap Blocks: exact=7670                                                                │
│   ->  Bitmap Index Scan on vorname_gin_trgm  (cost=0.00..178.67 rows=19022 width=0)      |   
|       (actual time=3.014..3.014 rows=11993 loops=1)                                      │
│         Index Cond: (vorname ~~ '%nna%'::text)                                           │
│ Planning time: 0.253 ms                                                                  │
│ Execution time: 16.488 ms                                                                │
└──────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

pg_trgm kann noch mehr

Eine vermeintlich nette Spielerei, aber – wenn man es denn kennt – in vielen Situationen hilfreich, ist die Ähnlichkeitssuche, die pg_trgm in Form von Funktionen und Operatoren mitbringt:

blog # SELECT vorname, count(*) FROM kunden WHERE vorname % 'Nick' GROUP BY vorname ORDER BY similarity(vorname, 'Nick') DESC;
┌─────────┬───────┐
│ vorname │ count │
├─────────┼───────┤
│ Nick    │   995 │
│ Nico    │  1047 │
│ Nicole  │   977 │
│ Nicolas │  1026 │
└─────────┴───────┘
(4 rows)

Und auch hier beschleunigt der GIN-Index die Abfrage signifikant:

blog # EXPLAIN ANALYSE SELECT vorname, count(*) FROM kunden WHERE vorname % 'Nick' GROUP BY vorname ORDER BY similarity(vorname, 'Nick') DESC;
┌──────────────────────────────────────────────────────────────────────────────────────────┐
│                                          QUERY PLAN                                      │
├──────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort  (cost=24761.50..24763.07 rows=630 width=18)                                        |
|      (actual time=915.696..915.697 rows=4 loops=1) .                                     │
│   Sort Key: (similarity(vorname, 'Nick'::text)) DESC                                     │
│   Sort Method: quicksort  Memory: 25kB                                                   │
│   ->  GroupAggregate  (cost=24716.83..24732.20 rows=630 width=18)                        |
|      (actual time=915.305..915.689 rows=4 loops=1)                                       │
│         Group Key: vorname                                                               │
│         ->  Sort  (cost=24716.83..24719.33 rows=1000 width=6)                            |
|      (actual time=915.162..915.305 rows=4045 loops=1)                                    │
│               Sort Key: vorname                                                          │
│               Sort Method: quicksort  Memory: 286kB                                      │
│               ->  Seq Scan on kunden  (cost=0.00..24667.00 rows=1000 width=6)            |
|      (actual time=0.650..914.065 rows=4045 loops=1)                                      │
│                     Filter: (vorname % 'Nick'::text)                                     │
│                     Rows Removed by Filter: 995955                                       │
│ Planning time: 0.296 ms                                                                  |
│ Execution time: 915.737 ms                                                               │
└──────────────────────────────────────────────────────────────────────────────────────────┘
(13 rows)

blog # CREATE INDEX vorname_gin_trgm ON kunden USING GIN (vorname gin_trgm_ops);
blog # EXPLAIN ANALYSE SELECT vorname, count(*) FROM kunden WHERE vorname % 'Nick' GROUP BY vorname ORDER BY similarity(vorname, 'Nick') DESC;
┌──────────────────────────────────────────────────────────────────────────────────────────┐
│                                             QUERY PLAN                                   │
├──────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort  (cost=3164.18..3165.75 rows=630 width=18)                                          |
|      (actual time=32.510..32.510 rows=4 loops=1)                                         │
│   Sort Key: (similarity(vorname, 'Nick'::text)) DESC                                     │
│   Sort Method: quicksort  Memory: 25kB                                                   │
│   ->  HashAggregate  (cost=3127.01..3134.88 rows=630 width=18)                           |
|      (actual time=32.497..32.503 rows=4 loops=1)                                         │
│         Group Key: vorname                                                               │
│         ->  Bitmap Heap Scan on kunden  (cost=75.75..3122.01 rows=1000 width=6)          |
|      (actual time=5.993..31.447 rows=4045 loops=1)                                       │
│               Recheck Cond: (vorname % 'Nick'::text)                                     │
│               Rows Removed by Index Recheck: 13010                                       │
│               Heap Blocks: exact=9174                                                    │
│               ->  Bitmap Index Scan on vorname_gin_trgm                                  |
|      (cost=0.00..75.50 rows=1000 width=0) (actual time=4.976..4.976 rows=17055 loops=1)  │
│                     Index Cond: (vorname % 'Nick'::text)                                 │
│ Planning time: 0.123 ms                                                                  │
│ Execution time: 32.563 ms                                                                │
└──────────────────────────────────────────────────────────────────────────────────────────┘
(13 rows)

Fazit

Dass PostgreSQL nicht von Haus aus alle LIKE-Anfragen per Index beschleunigt, sorgt gerne für Irritationen. Auf der anderen Seite öffnen sich, sobald man anfängt, sich mit dem Thema auseinanderzusetzen, ganz neue Möglichkeiten, die man in dieser Form bei anderen DBMS nicht findet. Und wir haben noch gar nicht über eine echte Volltextsuche gesprochen!

P.S.: pg_trgm kann kein Chinesisch!

Wenn nicht-alphanumerische Buchstaben in’s Spiel kommen (oder pg_trgm zu langsam ist…) sollte man einen Blick auf PGroonga werfen, das in diesen Bereichen glänzt.

Über den Author

Gunnar “Nick” Bluth hat seine Liebe zu relationalen Datenbanken Ende des letzten Jahrtausends entdeckt. Über MS Access und MySQL 3.x landete er sehr schnell bei PostgreSQL und hat nie zurückgeschaut, zumindest nie ohne Schmerzen. Er verdient seine Brötchen seit beinahe 20 Jahren mit FOSS (Administration, Schulungen, Linux, PostgreSQL). Gelegentlich taucht er auch tiefer in die Programmierung ein, so als SQL-Programmierer bei der Commerzbank oder in App-Nebenprojekten

NETWAYS stellt sich vor – Tobias Bauriedel

This entry is part 2 of 16 in the series NETWAYS stellt sich vor

Name: Tobias Bauriedel

Alter: 18 Jahre

Position bei Netways: Junior Consultant

Ausbildung: Fachinformatiker für Systemintegration

Bei NETWAYS seit: September 2018

 

Wie bist du zu NETWAYS gekommen und was genau gehört zu Deinem Aufgabenbereich?

Zu NETWAYS gekommen, bin ich so wie wahrscheinlich die meisten anderen auch. Ich habe nach meinem Schulabschluss einen Ausbildungsplatz gesucht, und habe NETWAYS gefunden und kurzer Hand eine Bewerbung geschrieben. Nach einem Bewerbungsgespräch hatte ich dann die Zusage und habe im September 2018 die Ausbildung begonnen. Meine Aufgabenbereiche sind sehr vielfältig. Es wird darauf geachtet, dass wir von allem etwas mitbekommen, so dass wir später als Consultants alles drauf haben.

Was macht Dir an Deiner Arbeit Spaß?

Es ist die Abwechslung und die Problemstellungen an meinem Aufgaben. Natürlich gibt es Momente, in denen man nicht weiter kommt, und man “keine Lust” mehr hat. Aber genau die Situationen bringen dich weiter. Du lernst das Problem richtig anzugehen und im Nachhinein doch noch zu lösen.

Was machst Du, wenn Du mal nicht bei NETWAYS bist?

Wenn ich mal nicht bei NETWAYS vor dem Bildschirm sitze, unternehme ich viel mit meinen Freunden oder schraube an dem ein oder anderen Auto. Außerdem spiele ich seit Kurzem wieder Fußball im Verein.

Wie geht es in Zukunft bei Dir weiter?

Wenn ich das jetzt schon wüsste, wäre ich Hellseher anstatt Fachinformatiker geworden 😉 . Aber ich hoffe natürlich, dass ich in meinen knappen zwei Jahren Ausbildung, die ich noch vor mir habe, viel lerne und mir einen Namen machen kann.

Tobias Bauriedel
Tobias Bauriedel
Junior Consultant

Tobias ist ein offener und gelassener Mensch, dem vor allem der Spaß an der Arbeit wichtig ist. Bei uns macht er zurzeit seine Ausbildung zum Fachinformatiker. In seiner Freizeit ist er viel unterwegs und unternimmt gern etwas mit Freunden.