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
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]
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.
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.
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.
>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 ...
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.
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.
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.
>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.
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.
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...
