Ziel der Arbeit - RWTH

Optimierung der Datenbankstruktur
einer Web-Anwendung zur Analyse
von Fertigungsprozessen
Vortrag Seminararbeit
Fabian Ripplinger
Aachen, 22.01.2014
© WZL/Fraunhofer IPT
Agenda
1
Ziel der Arbeit
2
Grundlagen
3
Umsetzung
4
Ergebnis
5
Zusammenfassung und Ausblick
© WZL/Fraunhofer IPT
Seite 3
Ziel der Arbeit
Übergeordnetes
Gesamtziel
 Performance Verbesserung der Datenbankzugriffe in WoPS+
Detaillierte Ziele
Verbesserung der Performance durch:
 Ein anderes Datenbanksystem
 Eine veränderte Datenbankstruktur
 Geeignete Indizierung
© WZL/Fraunhofer IPT
Seite 4
WoPS+
Beschreibung der Software
Eingabedaten
Web-Tool
Auswertungen
Auswertungen
Simulation
Konfiguration
Bildquelle: http://cdn.flaticon.com/png/256/1829.png; http://cdns2.freepik.com/free-photo/slots-representing-configuration_318-9479.jpg
© WZL/Fraunhofer IPT
Seite 5
Agenda
1
Ziel der Arbeit
2
Grundlagen
2.1 ACID und BASE
2.2 Normalisierung
2.3 Indizes
3
Umsetzung
4
Ergebnis
5
Zusammenfassung und Ausblick
© WZL/Fraunhofer IPT
Seite 7
ACID und BASE
Eigenschaften eines Datenbanksystems
ACID
 Atomicity: Jede Transaktion wird entweder
ganz oder gar nicht ausgeführt.
 Consistency: Die Daten sind zu jeder Zeit
 Soft state: Daten können sich verändern,
konsistent.
 Isolation: Transaktionen beeinflussen sich
nicht gegenseitig.
 Durability: Die Daten sind garantiert dauerhaft
abgespeichert.
Relationale DBS
© WZL/Fraunhofer IPT
BASE
 Basically Available: Die Daten werden auf
mehreren Systemen gespeichert. Somit ist
eine Version der Daten immer erreichbar.
obwohl gerade kein User auf das System
zugreift.
 Eventually Consistent: Nicht jede Version der
Daten ist immer auf dem neusten Stand.
NoSQL-DBS
Seite 8
Agenda
1
Ziel der Arbeit
2
Grundlagen
2.1 ACID und BASE
2.2 Normalisierung
2.3 Indizes
3
Umsetzung
4
Ergebnis
5
Zusammenfassung und Ausblick
© WZL/Fraunhofer IPT
Seite 9
1. Normalform
1. Normalform verletzt
CD_ID
Albumtitel
Interpret
Jahr der
Gründung
Titelliste
4711
Not That Kind
Anastacia
1999
{1. Not That Kind, 2. I’m Outta Love, 3. Cowboys & Kisses}
4712
Wish You Were Here
Pink Floyd
1964
{1. Shine On You Crazy Diamond}
4713
Freak of Nature
Anastacia
1999
{1. Paid my Dues}
1. Normalform erfüllt
CD_ID
Albumtitel
Interpret
Jahr der Gründung
Track
Titel
4711
Not That Kind
Anastacia
1999
1
Not That Kind
4711
Not That Kind
Anastacia
1999
2
I’m Outta Love
4711
Not That Kind
Anastacia
1999
3
Cowboys & Kisses
4712
Wish You Were Here
Pink Floyd
1964
1
Shine On You Crazy Diamond
4713
Freak of Nature
Anastacia
1999
1
Paid my Dues
Bedingungen: Alle Attribute sind atomar, also nicht weiter zerlegbar.
© WZL/Fraunhofer IPT
Seite 10
2. Normalform
2. Normalform verletzt
CD_ID
Albumtitel
Interpret
Jahr der Gründung
Track
Titel
4711
Not That Kind
Anastacia
1999
1
Not That Kind
4711
Not That Kind
Anastacia
1999
2
I’m Outta Love
4711
Not That Kind
Anastacia
1999
3
Cowboys & Kisses
4712
Wish You Were Here
Pink Floyd
1964
1
Shine On You Crazy Diamond
4713
Freak of Nature
Anastacia
1999
1
Paid my Dues
2. Normalform erfüllt
CD_ID
Track
Titel
4711
1
Not That Kind
CD_ID
Albumtitel
Interpret
Jahr der Gründung
4711
2
I’m Outta Love
4711
Not That Kind
Anastacia
1999
4711
3
Cowboys & Kisses
4712
Wish You Were Here
Pink Floyd
1964
4712
1
Shine On You Crazy Diamond
4713
Freak of Nature
Anastacia
1999
4713
1
Paid my Dues
Bedingungen: 1. Normalform erfüllt.
Jedes Nichtschlüsselattribut ist voll funktional abhängig vom Primärschlüssel.
© WZL/Fraunhofer IPT
Seite 11
3. Normalform
3. Normalform verletzt
CD_ID
Albumtitel
Interpret
Jahr der Gründung
4711
Not That Kind
Anastacia
1999
4712
Wish You Were Here
Pink Floyd
1964
4713
Freak of Nature
Anastacia
1999
3. Normalform erfüllt
Interpret_ID
Interpret
Jahr der Gründung
311
Anastacia
1999
312
Pink Floyd
1964
CD_ID
Track
Titel
4711
1
Not That Kind
4711
2
I’m Outta Love
CD_ID
Albumtitel
Interpret_ID
4711
3
Cowboys & Kisses
4711
Not That Kind
311
4712
1
Shine On You Crazy Diamond
4712
Wish You Were Here
312
4713
1
Paid my Dues
4713
Freak of Nature
311
Bedingungen: 2. Normalform erfüllt. Kein Nichtschlüsselattribut ist von einem anderen
Nichtschlüsselattribut funktional abhängig.
© WZL/Fraunhofer IPT
Seite 12
Agenda
1
Ziel der Arbeit
2
Grundlagen
2.1 ACID und BASE
2.2 Normalisierung
2.3 Indizes
3
Umsetzung
4
Ergebnis
5
Zusammenfassung und Ausblick
© WZL/Fraunhofer IPT
Seite 13
Indizes
Gruppierter Index
 Nur einer pro Tabelle
Nicht gruppierter Index
 Mehrere pro Tabelle
 Kann mehrere Spalten enthalten
 Bezieht sich immer nur auf eine Spalte
 Die Reihenfolge der Spalten ist wichtig
 Wird getrennt von den Daten verwaltet
 Daten werden sortiert abgelegt
 Abgespeichert als B-Baum
Indizes können die Suche und das Sortieren nach bestimmten Spalten beschleunigen.
Der Verwaltungsaufwand für Indizes ist nicht zu vernachlässigen.
Bildquelle: http://use-the-index-luke.com/de/sql/anatomie/index-baum
© WZL/Fraunhofer IPT
Seite 14
Agenda
1
Ziel der Arbeit
2
Grundlagen
3
Umsetzung
3.1 Wahl der Datenbank
3.2 Änderungen an der Struktur
3.3 Indizes
4
Ergebnis
5
Zusammenfassung und Ausblick
© WZL/Fraunhofer IPT
Seite 15
Umsetzung
Aufbau
System
 Alle nicht benötigten Programme deaktiviert
 Datenbank und Tool laufen auf einem System
 Netzwerk Auslastung hat keinen Einfluss
 Immer nur ein Client
WoPS+
 Beispiel Firmendatensatz mit 150.000 Zeilen
 Gemessen wird die Zeit vom Einlesen der Daten
und der nötigen Berechnungen
Bildquelle: http://www.downloadclipart.net/large/915-monitor-and-computer-design.png; http://t3n.de/news/wp-content/uploads/2010/07/symbolbild-datenbank.jpg
© WZL/Fraunhofer IPT
Seite 16
Agenda
1
Ziel der Arbeit
2
Grundlagen
3
Umsetzung
3.1 Wahl der Datenbank
3.2 Änderungen an der Struktur
3.3 Indizes
4
Ergebnis
5
Zusammenfassung und Ausblick
© WZL/Fraunhofer IPT
Seite 17
Wahl der Datenbank
Oracle und MySQL eignen sich für den Vergleich.
Die Wahl ist auf Oracle gefallen
© WZL/Fraunhofer IPT
Seite 18
Agenda
1
Ziel der Arbeit
2
Grundlagen
3
Umsetzung
3.1 Wahl der Datenbank
3.2 Änderungen an der Struktur
3.3 Indizes
4
Ergebnis
5
Zusammenfassung und Ausblick
© WZL/Fraunhofer IPT
Seite 19
Änderungen an der Struktur
Arbeitsplan
Arbeitsplan_sim
Auftrags Nr. Vorgang Arbeitsplatz Hauptzeit User Projekt Nr.
A_1
10
5
400.0
12
49
A_1
20
8
375.0
12
49
A_2
10
2
200.0
12
49
ID
1
2
3
FK
1
1
2
2
3
3
Vorgangsstart
Vorgangsende Projekt Szenario Nr.
01.01.2012 05:33 02.01.2012 03:48 49
0
02.01.2012 06:00 02.01.2012 16:40 49
1
02.01.2012 03:48 03.01.2012 00:40 49
0
02.01.2012 16:40 03.01.2012 13:31 49
1
31.12.2011 17:07 01.01.2012 22:13 49
0
03.01.2012 00:20 03.01.2012 05:20 49
1
User
12
12
12
12
12
12
Arbeitsplan
Auftragsnummer
A_1
A_1
A_1
A_1
A_2
A_2
Vorgang
10
10
20
20
10
10
Arbeitsplatz
5
5
8
8
2
2
Hauptzeit
400.0
400.0
375.0
375.0
200.0
200.0
User
12
12
12
12
12
12
Projektnummer
49
49
49
49
49
49
Vorgangsstart
01.01.2012 05:33
02.01.2012 06:00
02.01.2012 03:48
02.01.2012 16:40
31.12.2011 17:07
03.01.2012 00:20
Vorgangsende Szenario Nr.
02.01.2012 03:48
0
02.01.2012 16:40
1
03.01.2012 00:40
0
03.01.2012 13:31
1
01.01.2012 22:13
0
03.01.2012 05:20
1
Durch die Denormalisierung können viele Verbundoperationen eingespart werden
© WZL/Fraunhofer IPT
Seite 20
Agenda
1
Ziel der Arbeit
2
Grundlagen
3
Umsetzung
3.1 Wahl der Datenbank
3.2 Änderungen an der Struktur
3.3 Indizes
4
Ergebnis
5
Zusammenfassung und Ausblick
© WZL/Fraunhofer IPT
Seite 21
Indizes
Benutzer
Firmendatensätze
Simulierte Szenarien
Szenario
Szenario
Projekt
Szenario
User
Projekt
Szenario
Ein gruppierte Index mit den Spalten User-ID, Projekt-ID und Szenario-ID erscheint sinnvoll.
Weitere nicht gruppierte Indizes werden nicht benötigt.
© WZL/Fraunhofer IPT
Seite 22
Agenda
1
Ziel der Arbeit
2
Grundlagen
3
Umsetzung
4
Ergebnis
5
Zusammenfassung und Ausblick
© WZL/Fraunhofer IPT
Seite 23
Ergebnis der Arbeit
Wechsel zu Oracle
300
Gefüllte Datenbank
(50 Firmensätze)
⌀-Werte in Sekunden ⌀-Werte in Sekunden
250
in Sekunden
Leere Datenbank
200
150
100
50
0
Ausgangssituation
174 (100%)
242 (100%)
Oracle Datenbank
179 (102,87%)
247 (102,1%)
Durchschnitt
Standardabweichung
Der Wechsel zu einer Oracle Datenbank brachte keinen großen Unterschied.
Die Ergebnisse waren im Mittel sogar etwas schlechter als vorher.
© WZL/Fraunhofer IPT
Seite 24
Ergebnis der Arbeit
Wechsel zur neuen Datenbankstruktur
300
Gefüllte Datenbank
(50 Firmensätze)
⌀-Werte in Sekunden ⌀-Werte in Sekunden
250
in Sekunden
Leere Datenbank
200
150
100
50
0
Ausgangssituation
174 (100%)
242 (100%)
Veränderte Struktur
133 (76,44%)
162 (66,94%)
Durchschnitt
Standardabweichung
Die Veränderungen an der Datenbankstruktur ergaben eine Verbesserung von ca. 33%.
Außerdem ist die Standardabweichung niedriger.
© WZL/Fraunhofer IPT
Seite 25
Ergebnis der Arbeit
Veränderungen an der Indizierung
300
Gefüllte Datenbank
(50 Firmensätze)
⌀-Werte in Sekunden ⌀-Werte in Sekunden
250
in Sekunden
Leere Datenbank
200
150
100
50
0
Ausgangssituation
174 (130,83%)
242 (149,38%)
Veränderte Struktur
133 (100%)
162 (100%)
Veränderte
155(116,54%)
173 (106,8%)
Durchschnitt
Standardabweichung
Indizierung
Der Verwaltungsaufwand der Indizes ist deutlich messbar.
Mit steigender Datenmenge erhöht sich der Nutzen von Indizes.
© WZL/Fraunhofer IPT
Seite 26
Agenda
1
Ziel der Arbeit
2
Grundlagen
3
Umsetzung
4
Ergebnis
5
Zusammenfassung und Ausblick
© WZL/Fraunhofer IPT
Seite 27
Zusammenfassung und Ausblick
 Ein Wechsel zu einer Oracle Datenbank lohnt sich in unserem
Beispiel nicht.
 Da MySQL ebenfalls den Anforderungen entsprach, sollte es im
Anschluss ebenfalls getestet werden.
 Denormalisierung der Tabellen „Arbeitsplan“ und „Fauf“
beschleunigt den Vorgang. Dafür muss mehr auf die Konsistenz
geachtet werden.
 Mit der Indizierung waren die Messungen etwas langsamer.
Jedoch sollte es bei größeren Datenmengen schneller werden.
© WZL/Fraunhofer IPT
Seite 28
Herzlichen Dank!
Fabian Ripplinger
© WZL/Fraunhofer IPT
Quellen





http://use-the-index-luke.com/de/sql/anatomie/index-baum
http://cdn.flaticon.com/png/256/1829.png
http://cdns2.freepik.com/free-photo/slots-representing-configuration_318-9479.jpg
http://t3n.de/news/wp-content/uploads/2010/07/symbolbild-datenbank.jpg
http://www.downloadclipart.net/large/915-monitor-and-computer-design.png
© WZL/Fraunhofer IPT
Seite 30