Forum: PC-Programmierung Wann schreibt MySQL(InnoDB) auf die Festplatte?


von Dennis (Gast)


Lesenswert?

Hallo zusammen,

ich habe eine Anwendung, in der jede Sekunde ein Datensatz in eine 
InnoDB MySQL-Datenbank geschrieben wird. Die Anwendung läuft auf einem 
Raspberry Pi. Per Symlink habe ich die Datendatei auf einen USB-Stick 
verschoben.

Nun merkt man aber trotz einiger Stunden, in denen Daten in die DB 
geschrieben wurden nicht, dass die Datendatei größer wird. Daher meine 
Frage: Wann schreibt InnoDB die Daten tatsächlich auf die "Festplatte"? 
Ist es ratsam, wenn möglich, diesen Vorgang häufiger auszuführen, so 
dass bei einem Stromausfall nicht mehrere Stunden Daten verloren sind?

Gruß
Dennis

von Commit (Gast)


Lesenswert?

Commit vergessen?

von Dennis (Gast)


Lesenswert?

Nein, wenn ich die Tabelle mit phpMyAdmin anschaue, sind die Daten da.

[code]
        #Daten in Datenbank schreiben
        cursor = connection.cursor()
        cursor.execute("INSERT INTO daten (...) VALUES 
(%s,%s,%s,%s,%s,%s)",(...))
        cursor.close()
        connection.commit()
[\code]

von Jens G. (jensig)


Lesenswert?

Wie groß ist denn die Datei?
Vielleicht ist der Platz für die Datei schon vorallokiert, so daß da 
einiges reinpaßt, ehe die wieder ein Stück größer wird.
Ansonsten, wie Commit (Gast) schon sagte - evtl. nach jedem Datensatz 
einen commit machen, sofern nicht ohnehin Autocommit aktiv ist.

von Carl D. (jcw2)


Lesenswert?

Jens G. schrieb:
> Wie groß ist denn die Datei?
Und wann wurde sie zuletzt geândert?

von Dennis (Gast)


Lesenswert?

Jens G. schrieb:
> Wie groß ist denn die Datei?
> Vielleicht ist der Platz für die Datei schon vorallokiert, so daß da
> einiges reinpaßt, ehe die wieder ein Stück größer wird.

>ls -l
-rw-r--r-- 1 root  root          0 May 21 16:40 debian-5.5.flag
-rw-rw---- 1 mysql mysql 169869312 Jul  1 20:21 ibdata1
-rw-rw---- 1 mysql mysql   5242880 Jul  1 20:21 ib_logfile0
-rw-rw---- 1 mysql mysql   5242880 Jul  1 20:21 ib_logfile1
drwx------ 2 mysql root       4096 May 21 16:40 mysql
-rw------- 1 root  root          6 May 21 16:40 mysql_upgrade_info
drwx------ 2 mysql mysql      4096 May 21 16:40 performance_schema
drwx------ 2 mysql mysql      4096 May 21 17:04 phpmyadmin
drwx------ 2 mysql mysql      4096 Jul  1 19:02 Wetterdaten

Ich schreibe jede Sekunde einen Datensatz und dazu passt das 
Änderungsdatum von der ibdata1. Die Größe ändert sich über mehrere 
Stunden nicht. Ich habe noch nicht mitbekommen, wann sich die Größe 
ändert.
Wird ein "Schreibvorgang" irgendwo protokolliert?

Wenn ich es richtig sehe, mache ich ja in python mit 
"connection.commit()" nach jedem Datensatz einen commit.
Wo kann ich sehen, ob autocommit an ist? Sollte, wenn ich recht weiß, 
mit InnoDB Standard sein.

von Guest (Gast)


Lesenswert?

ich spreche hier zwar von mssql, denke aber, dass mysql das etwa ähnlich 
macht.
Die DB Files sind in Pages fester Grösse aufgeteilt. Es wird erst eine 
weitere Page alloziert, wenn der Platz in den restlichen Pages belegt 
ist. Dies verminderte vor allem mit früheren  Dateisystemen die 
Fragmentierung.
Beim Löschen von Datensätzen wird die Datei auch nicht kleiner, sondern 
die Datenbankbereiche werden als frei markiert.
Du kannst dir die Datenbank als Dateisystem in einem Dateisystem 
vorstellen.
Transaktionen laufen mehrstufig ab:
- Client beginnt Transaction (begin transaction)
- Client ändert Daten (insert, delete, update etc)
- Server schreibt die Änderungen zuerst ins RAM, dann ins Transaction 
Log und zuletzt ins DB File, wobei das DB File jedoch im Hintergrund 
aktualisiert wird.
- Client oder Triggers machen allenfalls weitere Änderungen, die 
wiederum via den 3 Tiers in der DB landen.
- Client schliesst Transaktion ab (commit transaction)
- Server erklärt alle Transaktionen seit begin transaction für gültig. 
Dies landet ebenfalls über die 3 Tiers in der DB.
Sobald die Daten erfolgreich ins Transaction Log geschrieben wurden, 
erhält der Client ein ACK für seine Operationen.
Im Hintergrund wird dann noch das DB File aktualisiert.

Je nach Last und Optimierungseinstellungen kann es gut sein, das das DB 
File nicht sofort geändert wird und mehrere Tranaktionen beim 
Rollforward zusammengefasst werden.

von Jens G. (jensig)


Lesenswert?

>Die DB Files sind in Pages fester Grösse aufgeteilt. Es wird erst eine
>weitere Page alloziert, wenn der Platz in den restlichen Pages belegt
>ist. Dies verminderte vor allem mit früheren  Dateisystemen die
<Fragmentierung.

Kaum. Auch die jetzigen Filesysteme fragmentieren lustig, wenn da eine 
existierende Datei erweitert wird, und gerade eine andere Datei im Wege 
ist ...

von Dennis (Gast)


Lesenswert?

Wäre dann nur noch interessant zu wissen, was bei einem spontanen 
Stromausfall passiert.

von (prx) A. K. (prx)


Lesenswert?

Dennis schrieb:
> Wäre dann nur noch interessant zu wissen, was bei einem spontanen
> Stromausfall passiert.

Transaktionsorientierte Database-Engines sind grundsätzlich so gebaut, 
dass die Transaktionen nach einer ggf. erforderlichen Recovery-Aktion 
stets vollständig sind. Es können dann allerdings nachfolgende 
Aktivitäten komplett fehlen, je nach Einstellung können das auch 
beendete Transaktionen sein (bei Optimierung auf Performance).

Voraussetzung dafür ist aber, dass das zugrunde liegende File/IO-System, 
sich so verhält, wie die DB-Engine erwartet, was 
Synchronisationsaktionen angeht. Ein Write-Cache im IO-System kann dabei 
im Weg stehen. Das gilt freilich analog auch mindestens für die 
Metadaten nicht allzu archaischer Filesysteme selbst.

Technischer Hintergrund kann ein Transaction-Logging sein, indem zuerst 
im Log/Journal beschrieben wird, was nun getan wird. Erst wenn das auf 
Disk ist, wird es auch wirklich getan. Ist der erste Schritt nicht 
vollständig, ist noch nichts passiert. Ist der zweite Schritt nicht 
vollständig, kann er aus dem Log heraus nachgezogen werden. Das gilt 
vergleichbar für DBs und entsprechende Filesysteme.

: Bearbeitet durch User
von Irgendwer (Gast)


Lesenswert?

Ich glaube das Kommando das du suchst ist flush:
https://dev.mysql.com/doc/refman/5.7/en/flush.html


Ich hoffe du verwendest einen entsprechenden USB-Stick der deine 
Misshandlung länger als eine paar Tage überlebt.

von Andreas S. (andreas) (Admin) Benutzerseite


Lesenswert?

Dennis schrieb:
> Wann schreibt InnoDB die Daten tatsächlich auf die "Festplatte"?

Nach jeder Transaktion 
(https://en.m.wikipedia.org/wiki/Durability_(database_systems)). 
Optional nur einmal pro Sekunde - diese Einstellung könnte bei einem 
USB-Stick sinnvoll sein um zu starke Abnutzung zu vermeiden: 
https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

Irgendwer schrieb:
> Ich glaube das Kommando das du suchst ist flush:
> https://dev.mysql.com/doc/refman/5.7/en/flush.html

Das hat bei InnoDB keinen Effekt.

: Bearbeitet durch Admin
von Guest (Gast)


Lesenswert?

Jens G. schrieb:
>>Die DB Files sind in Pages fester Grösse aufgeteilt. Es wird erst eine
>>weitere Page alloziert, wenn der Platz in den restlichen Pages belegt
>>ist. Dies verminderte vor allem mit früheren  Dateisystemen die
> <Fragmentierung.
>
> Kaum. Auch die jetzigen Filesysteme fragmentieren lustig, wenn da eine
> existierende Datei erweitert wird, und gerade eine andere Datei im Wege
> ist ...

Eben doch!
Die DB alloziert für eine Erweiterung (extend) der Files jeweils 64kB, 
was 8 Pages entspricht.
Falls diese 64kB ein Mehrfaches der Dateisystem Clustergrösse sind, wird 
hier das Dateisystem einen Bereich auf der Partition suchen, wo er 
unfragmentiert rein passt. (Idealerweise wird die Clustergrösse für die 
Daten und Log Partition beim Formatieren auf 64kB gesetzt)
Klar, eine Fragmentierung kann nicht verhindert werden, ist aber immer 
noch besser als 4kB Fragmente.

Zudem ist eine SQL Installation nicht nur einfach 
Setup-weiter-weiter-fertigstellen. Einen SQL Server wirklich Performant 
zu betreiben erfordert halt noch ein Bisschen Wissen, wie die DB Engine 
funktioniert und wie dies optimal in der Hardware abgebildet wird. 
Getrennte Partitionen für Daten und Logs, angepasste RAID Levels, 
optimierung der Clustergrössen in NTFS, Preallocation, Verteilen 
einzelner Tables in verschiedene Files etc pp.

von Jens G. (jensig)


Lesenswert?

>Falls diese 64kB ein Mehrfaches der Dateisystem Clustergrösse sind, wird
>hier das Dateisystem einen Bereich auf der Partition suchen, wo er
>unfragmentiert rein passt. (Idealerweise wird die Clustergrösse für die

Eben, und damit hast Du deine Datei fragmentiert ...

>Klar, eine Fragmentierung kann nicht verhindert werden, ist aber immer
>noch besser als 4kB Fragmente.

Klar mag eine 64k Fragmentierzung besser sein als eine 4k 
Fragmentierung, ist aber trotzdem nicht gerade als gleichwertig zu 
"nicht fragmentiert" zu betrachten.

von (prx) A. K. (prx)


Lesenswert?

DBs fragmentieren gleich auf 2 Ebenen, sofern man die Tablespaces nicht 
auf raw devices legt (plus eine dritte bei Virtualisierung). Welche 
Bedeutung das hat hängt aber von der Art der Nutzung ab, und von der 
Technik des Backup. Bei überwiegend random access statt table scans ist 
das weniger von Bedeutung.

von Guest (Gast)


Lesenswert?

Jens G. schrieb:
>>Falls diese 64kB ein Mehrfaches der Dateisystem Clustergrösse sind, wird
>>hier das Dateisystem einen Bereich auf der Partition suchen, wo er
>>unfragmentiert rein passt. (Idealerweise wird die Clustergrösse für die
>
> Eben, und damit hast Du deine Datei fragmentiert ...
>
>>Klar, eine Fragmentierung kann nicht verhindert werden, ist aber immer
>>noch besser als 4kB Fragmente.
>
> Klar mag eine 64k Fragmentierzung besser sein als eine 4k
> Fragmentierung, ist aber trotzdem nicht gerade als gleichwertig zu
> "nicht fragmentiert" zu betrachten.

Da ja die Zugriffe auf die DB eh meist als random Access stattfinden, 
spielt eine Fragmentierung auf der Disk eine untergeordnete Rolle. Zudem 
sind die Daten innerhalb der DB auch "fragmentiert" auf die 
verschiedenen Pages verteilt.
Daher ist es wichtig, dass zumindest die Pages unfragmentiert auf der 
Platte liegen, damit diese in einem Zug gelesen werden können. Dies 
erreicht man eben mit einer Clustergrösse von 64kB.
Um eine Fragmentierung innerhalb der DB zu verringern, werden die 
Tabellen je nach Inhalt und Verwendung auf verschiedene Dateien 
verteilt. Statische Daten in File1, nur wachsende Tabellen in File2, 
dynamisches in File3 etc.
Weiter kann mit regelmässigen DBCC Jobs die Fragmentierung innerhalb der 
DB aufgelöst werden. Gleichzeitig können auch die Indexe neu erstellt 
und zusammen gezogen werden.

Richtig ätzend wird es dann, sobald eine Virtualisierung mit ins Spiel 
kommt und die Daten dann durch mehrere Layer hindurchgereicht werden...

Overkill für die sekündliche Speicherung von ein paar Messwerten pro 
Minute, wir reden hier High Performance Anwendungen mit x Tausenden 
Queries pro Sekunde...

Bitte melde dich an um einen Beitrag zu schreiben. Anmeldung ist kostenlos und dauert nur eine Minute.
Bestehender Account
Schon ein Account bei Google/GoogleMail? Keine Anmeldung erforderlich!
Mit Google-Account einloggen
Noch kein Account? Hier anmelden.