Laden...

MySQL - schnelles Eintragen von Daten

Erstellt von moelski vor 11 Jahren Letzter Beitrag vor 11 Jahren 6.529 Views
M
moelski Themenstarter:in
183 Beiträge seit 2011
vor 11 Jahren
MySQL - schnelles Eintragen von Daten

verwendetes Datenbanksystem: MySQL 5.5

Moin !

Ich versuche mich gerade an einer MySQL Datenbank. Der Server läuft auch, Datenbank ist erstellt und es gibt auch eine Tabelle in der DB:

CREATE TABLE `datatest` (
	`ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`Time` DATETIME NOT NULL,
	`ms` SMALLINT(5) UNSIGNED ZEROFILL NOT NULL DEFAULT '00000',
	`us` SMALLINT(5) UNSIGNED ZEROFILL NOT NULL DEFAULT '00000',
	`ns` SMALLINT(5) UNSIGNED ZEROFILL NOT NULL DEFAULT '00000',
	`Data1` DOUBLE NULL DEFAULT NULL,
	`Data2` DOUBLE NULL DEFAULT NULL,
	`Data3` DOUBLE NULL DEFAULT NULL,
	`Data4` DOUBLE NULL DEFAULT NULL,
	`Break` INT(11) NULL DEFAULT NULL,
	`Hint` TINYTEXT NULL COMMENT 'Hint for the DataSet' COLLATE 'latin1_swedish_ci',
	PRIMARY KEY (`ID`)
)
COMMENT='A Data Table ...'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Ich verbinde mich über den MySQL Connector mittels MySqlConnection auf die Datenbank (local). Bis hier hin alles super.

Jetzt möchte ich nur Zeilen mit Zufallsdaten in der DB eintragen um mal ein Gefühl dafür zu bekommen wie schnell das klappt.

Dazu habe ich diese Methode:

        public static void insert(MySqlConnection conn)
        {
            var cmd = new MySqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "INSERT INTO datatest(Time) VALUES(@Time)";
            cmd.Prepare();

            cmd.Parameters.AddWithValue("@Time", DateTime.Now);
            cmd.ExecuteNonQuery();
        }

Das lasse ich in einer For Schleife 1-n x ausführen und ermittele über eine Stopwatch Instanz die Zeit.

Ich habe mal zwischen 100 und 100000 Zeilen eintragen lassen und das braucht pro Zeile im Schnitt ca. 1,5-2,5 Millisekunden. Das kommt mir doch etwas langsam vor für einen lokalen Server 🤔

Man sieht auch im Taskmanager das sich beide Anwendungen (Client & Server) CPU mässig langweilen und bei weniger als 2% rumdümpeln während Daten eingetragen werden 8o. Ich hätte eigentlich erwartet das entweder Server oder Client auf Anschlag gehen ...

Mache ich hier generell irgendwas falsch? Mir ist bewusst das es sowas wie Bulk Inserts gibt. Aber ich wollte hier testen wie schnell ich mit einzelnen Inserts bin.

Grüße Dominik

Greetz Dominik

M
28 Beiträge seit 2011
vor 11 Jahren

Wenn ich das richtig verstehe, führst du die gesamte Funktion "insert" 1 - n-mal aus? Vielleicht solltest du dann die Zeile


var = new MySqlCommand();

ausserhalb der Schleife deklarieren? Könnte mir vorstellen, dass das Zeit kostet.

M
moelski Themenstarter:in
183 Beiträge seit 2011
vor 11 Jahren

Moin !

Das bringt nur einen sehr kleinen Schub:
10000 27671 immer neu
10000 23907 einmal definiert

Einmal sind es 27 und einmal 23 Sekunden für 10000 Einträge.
Ok ist 4 Sekunden schneller, aber wenn man es mal auf einen Eintrag runter bricht haben wir 2,4ms zu 2,76ms für einen Eintrag ...

Und auch hier dümpeln beide Anwendungen eher vor sich hin mit 2% CPU Last beim Eintragen...

Grüße Dominik

Greetz Dominik

M
moelski Themenstarter:in
183 Beiträge seit 2011
vor 11 Jahren

Nachtrag ...

Ich habe mal die Engine von InnoDB auf MyISAM geändert. Und dann habe ich 2000ms für 10000 Einträge. Also 0,2ms für einen Eintrag.

Allerdings hat MyISAM schon ein paar Nachteile gegenüber InnoDB.

Und auch hier ist weder Client noch Server richtig ausgelastet.

Vielleicht gibt es in meinem Konstrukt ja noch einen Haken 🤔 (abgesehen davon Bulk Imports zu nutzen)?

Greetz Dominik

M
28 Beiträge seit 2011
vor 11 Jahren

Kann es sein, dass das Prepare() die Zeit frisst? Soweit ich weiß, brauchst Du das nur einmal aufzurufen? Also nur die letzten beiden Zeilen deiner Funktion in die Schleife - alles andere davor.

16.806 Beiträge seit 2008
vor 11 Jahren

Du wirst nicht "arg viel" mehr als Insert bekommen, außer Du machst das als BulkInsert.

Ich hab meine DB-Performance-hungrigen Anwendungen migriert. Grund:
MongoDB vs. SQL Server 2008 Performance Showdown

Und ich glaube nicht oder ich kanns mir nicht vorstellen, dass mySQL von der Architektur jetzt sooo anders is als ein MSSQL; weshalb hier wahrscheinlich aufgrund des Prinzips der relationalen Datenbank die gleichen Probleme bestehen.

175 Beiträge seit 2010
vor 11 Jahren

Hi,

also mir fallen spontan drei Dinge auf:

        public static void insert(MySqlConnection conn)  
        {  
            var cmd = new MySqlCommand();  
            cmd.Connection = conn;  
            cmd.CommandText = "INSERT INTO datatest(Time) VALUES(@Time)";  
            cmd.Prepare();  
  
            cmd.Parameters.AddWithValue("@Time", DateTime.Now);  
            cmd.ExecuteNonQuery();  
        }  
  1. wie schon andere bemerkt haben, solltest du das MySqlCommand Objekt ausserhalb Deiner Schleife anlegen.

  2. Auch das Prepare kostet Zeit - also das Statement am besten ausserhalb der Schleife nicht nur instanziieren sondern auch gleich "preparen".

  3. Du verwendest offensichtlich keine Transaktion. Das bedeutet, dass das DBMS für jedes Statement intern eine eigene Transaktion startet. Starte also eine Transaktion und lasse alle Deine INSERTS in einer Transaktion laufen. Abschliessend noch ein COMMIT auf die Transaktion und der ganze Schwindel sollte um einiges schneller laufen....

Bye,
Michael

Debuggers don't remove Bugs, they only show them in Slow-Motion.

F
10.010 Beiträge seit 2004
vor 11 Jahren

Und Punkt 3 gebe ich hier seit 8 Jahren regelmäßig an, wenn jemand exackt diesen Fehler macht

M
moelski Themenstarter:in
183 Beiträge seit 2011
vor 11 Jahren

Moin !

So ich habe weitere Tests gemacht.

Folgende "Optimierungen" sind eingeflossen:

  • MySqlCommand Objekt ausserhalb der Schleife angelegt
  • Statt über Prepare setze ich direkt einen comm.CommandText mit INSERT ab
  • Transaktionen (500 Inserts zusammengefasst zu einer Transaktion)

Ergebnisse (InnoDB):
10000 Zeilen eingetragen
ohne Transaktion : 3,31 ms pro Insert
mit Transaktion : 0,28 ms pro Insert

Ergebnisse (MyISAM):
10000 Zeilen eingetragen
ohne Transaktion : 0,28 ms pro Insert
mit Transaktion : 0,288 ms pro Insert
(wobei hier ja Transaktionen laut Doku nicht wirklich funktionieren)

Aber wie man es auch dreht ... Bei ca. 0,3ms pro Insert scheint dann irgendwann (auf meinem System) das Ende der Fahnenstange.

Oder kann ich neben den Transaktions noch irgend etwas beschleunigen?

Grüße Dominik

Greetz Dominik

M
moelski Themenstarter:in
183 Beiträge seit 2011
vor 11 Jahren

Moin !

Ich habe heute noch ein bisschen im Web gesucht wie ich die Sache noch optimieren kann. Und dabei bin ich auf diese Seite gestoßen:
http://dev.mysql.com/doc/refman/5.1/de/insert-speed.html

Dort kann man folgendes lesen:

Wenn Sie gleichzeitig viele Datensätze vom selben Client aus einfügen, verwenden Sie INSERT-Anweisungen mit mehreren VALUES-Listen, um mehrere Datensätze zur selben Zeit einzufügen. Dies ist erheblich (in manchen Fällen sogar um mehrere Größenordnungen) schneller als die Verwendung separater INSERT-Anweisungen für je einen Datensatz

Das habe ich dann mal als dritte Alternative in meine Testanwendung eingebaut.

Die Ergebnisse sind schon beeindruckend (jeweils bei 10000 einzutragenden Zeilen) :

Normaler Insert        3,084ms pro Zeile
Transaction Insert     0,2007ms pro Zeile
Listen Insert          0,019ms pro Zeile

Das ist schon erstaunlich. Das ist immerhin 162x schneller 8o

Ich habe auch Tests gemacht mit Socket, Pipe und Memory als Transport weg. Dort konnte man aber nur sehr geringe Geschwindigkeitszuwächse feststellen.

Grüße Dominik

Greetz Dominik

16.806 Beiträge seit 2008
vor 11 Jahren

Wenn Du wirklich den absoluten Fokus auf Performance hast, dann kommst Du an einer einer NoSQL-DB nicht vorbei. Selbst Parallel- oder Threaded-Inserts überfordern MySQL in Sachen Performance. Faktor dürfte bei > 50 liegen.

Mehrere Gründe in diese Richtung:
* sehr hohe Schreib-Performance
* sehr hohe Key-Value Geschwindigkeit
* sehr geringe Gesamt-Antwortzeiten
* Real-Time-Inserts

Und von der Anwendung oder dem Namen "Not-Only-SQL" muss man sich nicht abschrecken lassen.
Die ist oft einfacher als mit nem normalen SQL-Server und gleicht eher einem OR-Mapper.

M
moelski Themenstarter:in
183 Beiträge seit 2011
vor 11 Jahren

Hallo Abt,

ich habe schon einen Blick auf mongoDB geworfen. Aber warum ich (noch) an MySQL festhalte ist die Tatsache das man es auch bei einem Provider nutzen könnte. Und das ist - wie ich finde - schon ein Vorteil. Denn damit liessen sich auch Webauswertungen realisieren per PHP oder ASP.net.

Mein Fokus liegt schon darauf rauszufinden wie schnell man mit einer MySQL arbeiten kann. Aber wenn ich über die Listen Inserts in die Region von ~50000 Zeilen pro Sekunde komme, dann wäre das erstmal mehr als genug an Performance.
Im Normalfall werden die Anforderungen weit unter dem liegen.

Ich werde jetzt noch ein paar Tests machen mit Tabelle füllen und gleichzeitig Daten auslesen. Sollte ich dabei auf große Probleme stossen bezüglich der Performance werde ich mal Richtung NoSQL schauen.

Grüße Dominik

Greetz Dominik

16.806 Beiträge seit 2008
vor 11 Jahren

ich habe schon einen Blick auf mongoDB geworfen. Aber warum ich (noch) an MySQL festhalte ist die Tatsache das man es auch bei einem Provider nutzen könnte. Und das ist - wie ich finde - schon ein Vorteil. Denn damit liessen sich auch Webauswertungen realisieren per PHP oder ASP.net.

Das ist eher ein Argument für MongoDB statt gegen; da MongoDB mehr oder weniger auf Webrequests mit BSon + OData getrimmt ist.

Nur so als Hinweis 😉

M
moelski Themenstarter:in
183 Beiträge seit 2011
vor 11 Jahren

Moin !

Das ist eher ein Argument für MongoDB statt gegen

Ok, aber gibt es denn schon viele Webhoster die MongoDB anboiten?
Mein ISP hats nicht im Programm.

Ich glaube wir werden auch leicht OT ... =)

Greetz Dominik

16.806 Beiträge seit 2008
vor 11 Jahren

Da es um die Performance geht, sind wir meiner Meinung nach _noch _nich OT. Sonst würde ich abtrennen 😉
Wenn Du kein administrativen Zugriff auf den Server hast, sondern nur nur nen normales Webhosting-Angebot, dann sind so gut wie alle NoSQL-Datenbanken keine Alternative.
Man braucht echten Zugriff auf die Maschine (Commandozeile etc).

F
10.010 Beiträge seit 2004
vor 11 Jahren

Aber bei Externen Hostern muss man sich keine Gedanken über Performance machen, die ist bei externem Zugriff ( wenn man ihn überhaupt bekommen würde ) sowieso bei MySql grottig.

Aber das zeigt ja eigentlich auch den richtigen weg auf.
Statt auf die DB direkt zuzugreifen erstelle einen WebService, der den DAL übernimmt, dann musst du dir über das schon mal keine Gedanken machen und kannst "jederzeit" wechseln.