NETWAYS Blog

In Datenbanken Indizes benutzen Du sollst!

von Alexander Klimov | Mrz 10, 2016 | PostgreSQL, Development, MySQL

… hat Bernd schon verlauten lassen.
Das heißt aber nicht, dass pauschal jede Spalte jeder Tabelle indiziert gehört – und es funktioniert. Denn bei jeder Schreiboperation müssen auch alle betroffenen Indizes mit aktualisiert werden. Dies sollte berücksichtigt werden, wenn in eine Tabelle potenziell viel geschrieben wird, aber die Lesevorgänge sich in Grenzen halten. (Beispiel: Icinga 2 DB IDO)
Im folgenden möchte ich genauer auf die Anwendungsfälle von DB-Indizes eingehen. Zwecks Demonstration habe ich eine SQLite-DB mit folgendem Schema angelegt:

CREATE TABLE myheavytable0 (
  c0 INTEGER, c1 INTEGER, c2 INTEGER, c3 INTEGER, c4 INTEGER,
  c5 INTEGER, c6 INTEGER, c7 INTEGER, c8 INTEGER, c9 INTEGER
);
CREATE TABLE myheavytable1 (
  c0 INTEGER, c1 INTEGER, c2 INTEGER, c3 INTEGER, c4 INTEGER,
  c5 INTEGER, c6 INTEGER, c7 INTEGER, c8 INTEGER, c9 INTEGER
);

(2 Tabellen mit je 10 Ganzzahlen-Spalten)
Diese Tabellen habe ich mit je 10.000.000 Zeilen Zufallszahlen befüllt. Im übrigen ist die Indizierung numerischer Spalten potenziell besser als die Indizierung von Text-Spalten.

Anwendungsfall #1: Joins

Die Herstellung von Beziehungen zwischen verschiedenen Entitätsmengen mit Hilfe von Fremdschlüsseln ist bei relationalen Datenbanken das Tagesgeschäft. Es kann sich durchaus lohnen, die entsprechenden Spalten zu indizieren.
Zwecks Demonstration wird das Schema wie folgt ergänzt:

CREATE INDEX myheavyindex0c0 ON myheavytable0 (c0);
CREATE INDEX myheavyindex0c1 ON myheavytable0 (c1);
CREATE INDEX myheavyindex1c0 ON myheavytable1 (c0);
CREATE INDEX myheavyindex1c1 ON myheavytable1 (c1);

(Die ersten 2 Spalten beider Tabellen werden jeweils einzeln indiziert.)
Die Auswirkungen können mit Messungen der Ausführungszeiten folgender Abfragen veranschaulicht werden:

Abfrage	Zeit
`SELECT COUNT(*) FROM myheavytable0 m0 INNER JOIN myheavytable1 m1 ON m0.c2 = m1.c3`	1m 6.519s
`SELECT COUNT(*) FROM myheavytable0 m0 INNER JOIN myheavytable1 m1 ON m0.c0 = m1.c3`	20.836s
`SELECT COUNT(*) FROM myheavytable0 m0 INNER JOIN myheavytable1 m1 ON m0.c0 = m1.c1`	4.468s

Daraus folgt: Werden 2 Tabellen mit Hilfe von je einer Spalte, von denen eine indiziert ist, gejoint, ist die Abfrage etwa 3x so schnell wie mit 2 nicht-indizierten Spalten. Sind beide Spalten indiziert, ist die Abfrage fast 15x so schnell.

Anwendungsfall #2: Verrechnung mehrerer Spalten

Manchmal müssen 2 Attribute einer Entität in einer Abfrage miteinander verrechnet werden. Viele Datenbanken bieten lobenswerterweise auch die Möglichkeit, Ausdrücke zu indizieren:

CREATE INDEX myheavyindex0e0 ON myheavytable0 (c4 + c5);

Abfrage	Zeit
`SELECT COUNT(*) FROM myheavytable0 WHERE c0 + c1 > 8000000`	1.324s
`SELECT COUNT(*) FROM myheavytable0 WHERE c4 + c5 > 8000000`	0.513s

Daraus folgt: Wenn ein indizierter Ausdruck abgefragt wird, ist die Abfrage fast 3x so schnell wie wenn alle am Ausdruck beteiligten Spalten einzeln indiziert sind.

Anwendungsfall #3: Filter auf mehrere Spalten

Wenn abgefragte Daten häufig auf mehreren Spalten basierend gefiltert werden, kann es sich lohnen, diese Spalten zusammen zu Indizieren:

CREATE INDEX myheavyindex0m0 ON myheavytable0 (c6, c7);

Abfrage	Zeit
`SELECT COUNT(*) FROM myheavytable0 WHERE c0 < 8000000 AND c1 > 8000000`	12.130s
`SELECT COUNT(*) FROM myheavytable0 WHERE c6 < 8000000 AND c7 > 8000000`	0.445s

Daraus folgt: Bei zusammen indizierten Spalten ist die Abfrage 27x so schnell wie bei einzeln indizierten.

Fazit

Bei Nutzung von Indizes hängt viel vom Einzelfall ab. Im Zweifelsfall sollte ein Fachmann zu Rate gezogen werden.

Alexander Klimov

Senior Developer

Alexander hat 2017 seine Ausbildung zum Developer bei NETWAYS erfolgreich abgeschlossen. Als leidenschaftlicher Programmierer und begeisterter Anhänger der Idee freier Software, hat er sich dabei innerhalb kürzester Zeit in die Herzen seiner Kollegen im Development geschlichen. Wäre nicht ausgerechnet Gandhi sein Vorbild, würde er von dort aus daran arbeiten, seinen geheimen Plan, erst die Abteilung und dann die Weltherrschaft an sich zu reißen, zu realisieren - tut er aber nicht. Stattdessen beschreitet er mit der Arbeit an Icinga Web 2 bei uns friedliche Wege.

Lies mehr von Alexander und triff unser Team

Azubis erzählen: April 2015 Alexander

von Alexander Klimov | Apr 13, 2015 | Icinga, Team, Development, MySQL, Serien

This entry is part 8 of 17 in the series Azubis erzählen

Name: Alexander Klimov
Ausbildungsberuf: Fachinformatiker für Anwendungsentwicklung
Abteilung: Development
Lehrjahr: 1

Hallo Menschen!
Diesmal bin ich an der Reihe, Euch meinen Ausbildungsberuf nahezubringen.
Doch zuerst ein paar Angaben zu meiner Wenigkeit:
Ich heiße Alexander. Ich bin 20 Jahre jung und im 1. Lehrjahr. Mein Ausbildungsberuf heißt Fachinformatiker für Anwendungsentwicklung und mich erwartet im kommenden Lehrjahr erst mal die Zwischenprüfung. Die Abschlussprüfung sollte im Lehrjahr darauf folgen. Meine Kollegen Nadja und Marius mögen schon weiter sein, aber – wie ich gerne zu sagen pflege – kommt Zeit, kommt Rat! Neben der unglaublich spannenden Berufsschule bin ich in der Development-Abteilung von NETWAYS tätig.
Die Berufsbezeichnung ist relativ selbsterklärend. Meine (Haupt-)Aufgabe besteht darin, Anwendungen (weiter) zu entwickeln. Im Folgenden möchte ich euch ein paar davon vorstellen.

Icinga Web 2

An unserem PHP-Framework Icinga Web 2 gibt es noch alle Hände voll zu schrauben (schließlich soll es irgendwann mal fertig werden) – kein Wunder, dass der Löwenanteil meiner bisherigen Ausbildung diesem Projekt gewidmet war.
Neben dem Erlernen der unglaublich tollen Programmiersprache PHP war es mir vergönnt, viele kleinere Fehler zu beheben und die Puppet-Manifeste radikal umzubauen, um Letztgenannte überschaubar(er) zu machen. Das Problem bestand darin, dass (fast) alles (fast 800 Zeilen) sich in einer .pp-Datei befand – und das hat den Code nicht viel übersichtlicher gemacht. Meine Aufgabe bestand darin, das Ganze zu modularisieren. Das hat zwar eine ganze Weile gedauert, aber mittlerweile ist es geschafft!

DbMaint

Das von mir Mitte 2014 entwickelte DbMaint soll u. a. beim Aktualisieren von Icinga 2 dazugehörige Datenbanken mit auf den neusten Stand bringen – und somit den Administrationsaufwand verringern. Für Debian gab es zwar schon dbconfig-common, aber in RPM-basierenden GNU-Distributionen fehlte ein derartiges Werkzeug.
DbMaint war in Python zu realisieren und sollte sowohl MySQL als auch PostgreSQL unterstützen. Als ob letztgenanntes nicht schon aufwändig genug war, lagen mir – dank RHEL 5 – Steine im Weg. Dieser Weg war entsprechend steinig und schwer – trotzdem ist mir die Fertigstellung gelungen.

Stammdaten-Verwaltung

Momentan verantworte ich die Fertigstellung eines Icinga Web 2 Moduls, das Stammdaten von Kunden verwalten soll. Dazu zählen bspw. Verträge und (im Rahmen letztgenannter) erworbene Produkte/Dienstleistungen. Da die voraussichtliche Datenmenge nicht überschaubar ist, kommt dafür nur eine relationale Datenbank in Frage. Sich mit solchen zu beschäftigen fand ich spannend und lehrreich. (Überhaupt lerne ich im Betrieb eine ganze Menge interessanter Sachen – die Berufsschule lasse ich an dieser Stelle mal unkommentiert..)
Zuerst hatte ich das Datenbankschema zu planen – wie die Datenbank aufgebaut sein soll und was sie überhaupt speichern soll. Danach sollte das Modul für Icinga Web 2 programmiert werden. Es soll die gespeicherten Daten anzeigen und auch das Hinzufügen, Bearbeiten und Löschen ermöglichen. (Für Leute, die sich nicht mit Datenbanken/SQL auskennen oder gerade keine Lust haben, viele/längere SQL-Abfragen abzutippen. 😉 )
An dieser Stelle darf ich eine Lanze für Icinga Web 2 brechen, denn ohne seine Infrastruktur wäre ich so gut wie aufgeschmissen.

Fazit

In diesem Beruf reicht es nicht, in die Tasten hauen zu können – auch Köpfchen anstrengen will gelernt sein. Und wer auch um die Ecke denken kann – der findet bei uns bestimmt die richtige Stelle für sich.

Alexander Klimov

Senior Developer

Lies mehr von Alexander und triff unser Team

Nachgestellte Leerzeichen und String-Vergleiche in Datenbanken

von Eric Lippmann | Apr 9, 2015 | MySQL, PostgreSQL, MariaDB

Für Datenbanken die sich an den SQL-92-Standard halten, gilt für Vergleiche von character strings nach Abschnitt 8.2 Generals Rules #3, dass die zu vergleichenden Strings, vor dem Vergleich auf die selbe Länge gebracht werden müssen. Der eventuell kürzere String wird demnach nach rechts auf die Länge des zu vergleichenden Strings mit Hilfe eines pad character, meist dem Leerzeichen, aufgefüllt.
Unter character strings fallen die Typen char (character) und varchar (character varying). Diese Typen ähneln einander, werden aber auf unterschiedliche Weise gespeichert und abgerufen.
char-Werte werden beim Speichern nach rechts mit Leerzeichen bis auf die deklarierte Länge aufgefüllt, welche beim Abrufen aber wieder entfernt werden. Bei char-Werten mit einer maximalen Anzahl von vier Zeichen beispielweise, kann man deshalb nicht zwischen 'abc' und 'abc ' unterscheiden.
Im Gegensatz dazu werden varchar-Werte nur mit so vielen Zeichen wie erforderlich zuzüglich der Information über die Länge gespeichert. varchar-Werte werden beim Speichern nicht aufgefüllt. Somit werden auch Leerzeichen beim Speichern und Abrufen beibehalten.
Und jetzt? Jetzt wollen wir versuchen aus den varchar-Werten 'abc' und 'abc ' nur 'abc ' zu selektieren.
Als Erstes brauchen wir natürlich eine Datenbank mit den gewünschten Daten (hier in MySQL):

CREATE DATABASE playground;
USE playground;
CREATE TABLE pad (string varchar(5));
INSERT INTO pad VALUES('abc');
INSERT INTO pad VALUES('abc ');
INSERT INTO pad VALUES('abc  ');

Als Nächstes überprüfen wir, ob – wie im Standard definiert – bei varchar-Werten Leerzeichen beim Speichern und Abrufen beibehalten werden:

SET sql_mode = PIPES_AS_CONCAT;
SELECT '"' || string || '"', LENGTH(string) FROM pad;
+----------------------+----------------+
| '"' || string || '"' | LENGTH(string) |
+----------------------+----------------+
| "abc"                |              3 |
| "abc "               |              4 |
| "abc  "              |              5 |
+----------------------+----------------+

Das sieht doch gut aus. Nun zurück zur eigentlichen Aufgabe: Wir wollen aus diesen Daten nur 'abc ' selektieren:

SELECT '"' || string || '"', LENGTH(string) FROM pad WHERE string = 'abc ';
+----------------------+----------------+
| '"' || string || '"' | LENGTH(string) |
+----------------------+----------------+
| "abc"                |              3 |
| "abc "               |              4 |
| "abc  "              |              5 |
+----------------------+----------------+

Irgendwie nicht richtig. Oder doch? Wie eingangs erwähnt, tritt hier die Regel für Vergleiche von character strings aus dem SQL-92-Standard in Kraft: Der kürzere zu vergleichende String wird nach rechts mit Leerzeichen aufgefüllt. Wenn 'abc' mit 'abc ' verglichen wird, wird eigentlich 'abc ' mit 'abc ' verglichen, oder 'abc' mit 'abc' – wer weiß das schon so genau :).
Kommt man trotzdem auf das gewünschte Ergebnis? Klar, mit einem Zaubertrick:

SELECT '"' || string || '"', LENGTH(string) FROM pad WHERE BINARY string = 'abc ';
+----------------------+----------------+
| '"' || string || '"' | LENGTH(string) |
+----------------------+----------------+
| "abc "               |              4 |
+----------------------+----------------+

Gibt’s Ausnahmen? Ja! Bei PostgreSQL sind bei Vergleichen von varchar-Werten nachgestellte Leerzeichen signifikant.

Eric Lippmann

CTO

Eric kam während seines ersten Lehrjahres zu NETWAYS und hat seine Ausbildung bereits 2011 sehr erfolgreich abgeschlossen. Seit Beginn arbeitet er in der Softwareentwicklung und dort an den unterschiedlichen NETWAYS Open Source Lösungen, insbesondere inGraph und im Icinga Team an Icinga Web. Darüber hinaus zeichnet er für viele Kundenentwicklungen in der Finanz- und Automobilbranche verantwortlich.

Lies mehr von Eric und triff unser Team

OSDC 2015: Der Countdown läuft – nur noch 29 Tage

von Eva Häusler | Mrz 23, 2015 | MySQL, OSDC

Colin Charles präsentiert „Automated MySQL failover with MHA: getting started & moving past its quirks“

OSDC? Noch nie gehört…
Das ist aber schade und fast schon ein unentschuldbares Versäumnis!
Aber wir holen das nach:
Die Open Source Data Center Conference (kurz OSDC) ist unsere internationale Konferenz zum Thema Open Source Software in Rechenzentren und großen IT-Umgebungen. 2015 findet sie zum siebten Mal statt und bietet mit dem Schwerpunktthema Agile Infrastructures ganz besonders erfahrenen Administratoren und Architekten ein Forum zum Austausch und die Gelegenheit zur Aneignung des aktuellsten Know-Hows für die tägliche Praxis. Diesmal treffen wir uns dafür in Berlin!
Workshops am Vortag der Konferenz und das im Anschluss an die Veranstaltung stattfindende Puppet Camp komplettieren dabei das Rundum-sorglos-Paket für Teilnehmer, die gar nicht genug Wissen in sich aufsaugen können.

Cubietruck ( Entertainment in 1080p )

von Enrico Labedzki | Feb 3, 2015 | DevOps, Network, Technology, Web Services, MariaDB, Team, Monitoring & Observability, Development, MySQL, Linux, Hardware

Was macht der Netways-Mitarbeiter, wenn er abends nach Hause kommt?
Er schaltet sein Entertainment-System ein und legt klassische Musik auf …
… Klassik? Nein nicht wirklich, das mit dem Entertainment System ist allerdings wahr.
Ich möchte euch hier etwas über mein Entertainment-System berichten, dem Cubietruck, einem leistungsfähigen Ersatz für den RaspberryPi.

Zudem haben Netways-Mitarbeiter unter anderem noch andere Anforderungen an solch ein Spielzeug. 🙂 Hier mal einige mögliche Anwendungsthemen:

als MPI Cluster ( ja so Verrückte gibt es auch hier bei Netways 😉 )
als WLAN AP ( oder Bluetooth FileShare )
als Network Monitoring TAP ( für die Traffic Analyse im heimischen Netzwerk – aber auch in Firmennetzen ganz hilfreich )
als NAS ( mit Samba und NFS als Shared-Storage für Backup von Fotos, Dokumenten etc. … )
als Mobile Computer ( zum Surfen, Schreiben, Programmieren sogar mit dem heimischen Fernseher )
als Computer zum Lernen ( für die Kleinen aber auch ganz Großen )
als Home Entertainment-System ( mit KODI ehemals XBMC, wahrscheinlich das, was sich viele als Ersatz oder gar Ergänzung zu einer Set-Top Box wünschen )

Mit der Installation von Ajenti oder einer der anderen WebGUI’s ( OpenPanel, Webmin ) ist es möglich das Gerät vollständig über ein Webinterface zu steuern, in Kombination mit OwnCloud und MySQL/MariaDB ist das System eine vollwertige CloudSharing Lösung.
Auch LXDE oder gar XFCE stressen die Allwinner A20 CPU nicht sonderlich. Somit ist das System auch als Lerncomputer für die Kids ganz gut geeignet (es muss nicht immer teure Hardware sein) .
Die Bastler unter euch haben bestimmt auch ganz exotische Vorstellungen bzgl. Networking. So ist es möglich, den Cubietruck auch als WLAN AP einzusetzten. Entweder händisch mit OpenWRT oder auch ganz bequem über die WebGUI mit DD-WRT. Die ganz Pfiffigen unter euch basteln sich das Ganze auf Basis der verfügbaren Distributionen in wenigen Minuten selber zusammen.

Viele von euch haben dieses System vermutlich bereits für sich erkannt, und Fragen sich bestimmt „Warum berichtet er uns hier den über ein Gerät das bereits ca. ein halbes Jahr alt ist?“, und die Antwort ist „Kinderkrankheiten“, jedes System bzw. OS ist in der Phase seiner Entwicklung noch recht verbugged, das betraf auch das Cubieboard3. Der Cubietruck ist momentan der beste Ersatz für den RaspberryPi, wenn es um Leistung und Ausstattung geht.
Hier mal die Hardware Ausstattung des Cubieboard3 ( aka Cubietruck ):

AllWinnerTech SOC A20 ARM® Cortex™-A7 Dual-Core ARM®
2GB RAM DDR3
VGA & DVI 1080P
10/100/1000Mbit Ethernet
Wifi+BT ( in einem Chip )
SATA 2.0 Port für 1.8/2.5′ HDDs ( auch 3.5′ HDDs sind möglich brauchen allerdings einen externen Strom Anschluss )
MicroSDHC Slot sowie NAND 4GB ( mit vorinstalliertem Android )
2 x USB 2.0
1 x Infrarot
1 x 3.5 klinke
1 x LED ( diese könnt Ihr auch selber ansteuern )
1 x SPDIF
1 x Power DC5V@2.5A ( ein altes Smartphone USB-Ladegrät reicht hier aus )
1 x 54 Pin
1 x JTAG & UART over USB
Board Abmessung 11 x 8 cm
mitgeliefert werden Plexiglass und Schrauben ( als Provisorisches Gehäuse ganz nett anzusehen )