Grails: Batched Inserts mit MySQL

23. Januar 2014 von Steffen Döring [permalink]

Jeder Insert in eine Tabelle besteht aus folgenden Schritten (siehe MySQL):

  1. Verbinden mit dem Server und der Datenbank
  2. Senden des Inserts an die Datenbank
  3. Parsen des Inserts
  4. Hinzufügen der neuen Zeile
  5. Hinzufügen der neuen Indizies
  6. Schließen der Verbindung

Je mehr Spalten die Zeile hat, um so länger dauert der Insert. Mit zunehmender Tabellengröße wird das Hinzufügen der Indizies aufwendiger.

 

Beim Einfügen mehrerer neuer Zeilen in die gleiche Tabelle empfiehlt MySQL das Nutzen von Batched Inserts. Statt also mehrere male ein einzelnes Insert abzusetzen:

INSERT INTO table (value1, ..., valueN) VALUES (.. zeile 1 ..);
INSERT INTO table (value1, ..., valueN) VALUES (.. zeile 2 ..);
INSERT INTO table (value1, ..., valueN) VALUES (.. zeile 3 ..);

Ist dieser einzelne Insert mit mehreren Zeilen schneller:

INSERT INTO table (value1, ..., valueN) VALUES (.. zeile 1 ..), (.. zeile 2 ..), (.. zeile 3 ..);

 

Auch Hibernate – und damit ebenfalls Grails’ GORM – unterstützt Batched Inserts. Die Umsetzung ist daher entsprechend einfach.

Folgende Domain Klassen sollen uns als Beispiel dienen.

class ParentTable {

	String parent

	static hasMany = [
		children: ChildTable
	]

	static mapping = {
		id(generator: 'hilo', params: [table: 'hilo_table', column: 'next_parent', max_lo: 100])
	}
}

class ChildTable {

	String child

	static mapping = {
		id(generator: 'hilo', params: [table: 'hilo_table', column: 'next_child', max_lo: 100])
	}
}

Wichtig ist, dass die ID keine auto_increment Primary Keys von MySQL sind. Batched Inserts in MySQL funktionieren nicht mit auto incremented Keys. Aus der Doku von Hibernate:

Hibernate disables insert batching at the JDBC level transparently if you use an identity identifier generator.

auto_increment sind Identiy Identifier.
 

Mittels Integrationstest können wir nun die Inserts in die MySQL kontrollieren – die Anbindung an eine H2 Database, welche normalerweise als Datenbank für die Integrationstests dient, wurde hierfür geändert.

void testBatchedInsert() {
	20.times { i ->
		def parent = new ParentTable(parent: 'no. ' + i)
		2.times { j ->
			parent.addToChildren(
					new ChildTable(child: 'no. ' + j.toString() + ' for parent no. ' + i)
					)
		}
		parent.save()	// nicht flushen!
	}

	// flushe die aktuelle Hibernate Session
	Session session = sessionFactory.getCurrentSession()
	session.flush()
	session.clear()

	// das wird erwartet
	assert 20 == ParentTable.count()
	assert 40 == ChildTable.count()
}

Zwanzig mal erstellen wir einen Parent. Jeder Parent hat jeweils zwei Children.

 

Noch haben wir keine Batched Inserts aktiviert und tatsächlich sehen wir im MySQL Query Log glatte 100 Inserts.

insert into parent_table (version, parent, id) values (0, 'no. 0', 1)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 0', 1)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 0', 2)
insert into parent_table (version, parent, id) values (0, 'no. 1', 2)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 1', 3)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 1', 4)
insert into parent_table (version, parent, id) values (0, 'no. 2', 3)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 2', 5)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 2', 6)
insert into parent_table (version, parent, id) values (0, 'no. 3', 4)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 3', 7)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 3', 8)
insert into parent_table (version, parent, id) values (0, 'no. 4', 5)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 4', 9)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 4', 10)
insert into parent_table (version, parent, id) values (0, 'no. 5', 6)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 5', 11)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 5', 12)
insert into parent_table (version, parent, id) values (0, 'no. 6', 7)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 6', 13)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 6', 14)
insert into parent_table (version, parent, id) values (0, 'no. 7', 8)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 7', 15)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 7', 16)
insert into parent_table (version, parent, id) values (0, 'no. 8', 9)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 8', 17)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 8', 18)
insert into parent_table (version, parent, id) values (0, 'no. 9', 10)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 9', 19)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 9', 20)
insert into parent_table (version, parent, id) values (0, 'no. 10', 11)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 10', 21)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 10', 22)
insert into parent_table (version, parent, id) values (0, 'no. 11', 12)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 11', 23)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 11', 24)
insert into parent_table (version, parent, id) values (0, 'no. 12', 13)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 12', 25)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 12', 26)
insert into parent_table (version, parent, id) values (0, 'no. 13', 14)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 13', 27)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 13', 28)
insert into parent_table (version, parent, id) values (0, 'no. 14', 15)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 14', 29)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 14', 30)
insert into parent_table (version, parent, id) values (0, 'no. 15', 16)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 15', 31)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 15', 32)
insert into parent_table (version, parent, id) values (0, 'no. 16', 17)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 16', 33)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 16', 34)
insert into parent_table (version, parent, id) values (0, 'no. 17', 18)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 17', 35)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 17', 36)
insert into parent_table (version, parent, id) values (0, 'no. 18', 19)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 18', 37)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 18', 38)
insert into parent_table (version, parent, id) values (0, 'no. 19', 20)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 19', 39)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 19', 40)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (1, 1)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (1, 2)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (2, 3)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (2, 4)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (3, 5)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (3, 6)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (4, 7)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (4, 8)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (5, 9)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (5, 10)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (6, 11)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (6, 12)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (7, 13)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (7, 14)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (8, 15)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (8, 16)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (9, 17)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (9, 18)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (10, 19)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (10, 20)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (11, 21)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (11, 22)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (12, 23)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (12, 24)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (13, 25)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (13, 26)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (14, 27)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (14, 28)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (15, 29)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (15, 30)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (16, 31)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (16, 32)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (17, 33)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (17, 34)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (18, 35)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (18, 36)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (19, 37)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (19, 38)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (20, 39)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (20, 40)

Einhundert? Es wurden nur sechzig Domain Instanzen erzeugt?!
20 Parents, 40 Children, sowie 40 Einträge in die Parent zu Children-Mapping Tabelle.

 

Einhundert einzelne Inserts!

 

Das lässt sich optimieren – und man muss noch nicht mal Logik dafür anpassen.
Folgende Einträge in die DataSource.groovy reichen.

// Hibernate Config für Batched Inserts
hibernate {
	jdbc.batch_size = 20
	order_inserts = true
//	order_updates = true	// Batched Updates gehen auch
}

dataSource {
	// rewriteBatchedStatements=true
	url = "jdbc:mysql://localhost/batched_insert_example_db?useUnicode=true&characterEncoding=UTF8&rewriteBatchedStatements=true"
}

In Stapeln von 20 Inserts sollen die neuen Zeilen zur Datenbank hinzugefügt werden.

 

Nach dem Ingegrationstest offenbart ein Blick in das MySQL Query Log den Erfolg.
Nur noch fünf Inserts werden abgesetzt.

insert into parent_table (version, parent, id) values (0, 'no. 0', 1),(0, 'no. 1', 2),(0, 'no. 2', 3),(0, 'no. 3', 4),(0, 'no. 4', 5),(0, 'no. 5', 6),(0, 'no. 6', 7),(0, 'no. 7', 8),(0, 'no. 8', 9),(0, 'no. 9', 10),(0, 'no. 10', 11),(0, 'no. 11', 12),(0, 'no. 12', 13),(0, 'no. 13', 14),(0, 'no. 14', 15),(0, 'no. 15', 16),(0, 'no. 16', 17),(0, 'no. 17', 18),(0, 'no. 18', 19),(0, 'no. 19', 20)
insert into child_table (version, child, id) values (0, 'no. 0 for parent no. 0', 1),(0, 'no. 1 for parent no. 0', 2),(0, 'no. 0 for parent no. 1', 3),(0, 'no. 1 for parent no. 1', 4),(0, 'no. 1 for parent no. 2', 5),(0, 'no. 0 for parent no. 2', 6),(0, 'no. 1 for parent no. 3', 7),(0, 'no. 0 for parent no. 3', 8),(0, 'no. 0 for parent no. 4', 9),(0, 'no. 1 for parent no. 4', 10),(0, 'no. 1 for parent no. 5', 11),(0, 'no. 0 for parent no. 5', 12),(0, 'no. 1 for parent no. 6', 13),(0, 'no. 0 for parent no. 6', 14),(0, 'no. 1 for parent no. 7', 15),(0, 'no. 0 for parent no. 7', 16),(0, 'no. 0 for parent no. 8', 17),(0, 'no. 1 for parent no. 8', 18),(0, 'no. 0 for parent no. 9', 19),(0, 'no. 1 for parent no. 9', 20)
insert into child_table (version, child, id) values (0, 'no. 1 for parent no. 10', 21),(0, 'no. 0 for parent no. 10', 22),(0, 'no. 1 for parent no. 11', 23),(0, 'no. 0 for parent no. 11', 24),(0, 'no. 1 for parent no. 12', 25),(0, 'no. 0 for parent no. 12', 26),(0, 'no. 1 for parent no. 13', 27),(0, 'no. 0 for parent no. 13', 28),(0, 'no. 1 for parent no. 14', 29),(0, 'no. 0 for parent no. 14', 30),(0, 'no. 0 for parent no. 15', 31),(0, 'no. 1 for parent no. 15', 32),(0, 'no. 0 for parent no. 16', 33),(0, 'no. 1 for parent no. 16', 34),(0, 'no. 0 for parent no. 17', 35),(0, 'no. 1 for parent no. 17', 36),(0, 'no. 1 for parent no. 18', 37),(0, 'no. 0 for parent no. 18', 38),(0, 'no. 1 for parent no. 19', 39),(0, 'no. 0 for parent no. 19', 40)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (1, 1),(1, 2),(2, 3),(2, 4),(3, 5),(3, 6),(4, 7),(4, 8),(5, 9),(5, 10),(6, 11),(6, 12),(7, 13),(7, 14),(8, 15),(8, 16),(9, 17),(9, 18),(10, 19),(10, 20)
insert into parent_table_child_table (parent_table_children_id, child_table_id) values (11, 21),(11, 22),(12, 23),(12, 24),(13, 25),(13, 26),(14, 27),(14, 28),(15, 29),(15, 30),(16, 31),(16, 32),(17, 33),(17, 34),(18, 35),(18, 36),(19, 37),(19, 38),(20, 39),(20, 40)

 

Et voilà.

 

Das vollständige Beispiel findet sich hier.

 

 

 

Bei der Umsetzung stieß ich auf drei Probleme, auf die ich noch schnell aufmerksam machen möchte:

  1. Batched Inserts funktionieren nicht mit auto_increment Keys! Darauf kann man gar nicht oft genug hinweisen, weil mir das tagelang Kopfzerbrechen bereitete. ;-)
  2. Der aktuelle MySQL Connector/J ab Version 5.1.27 liefert bei batched inserts ein für Hibernate falsches Ergebnis zurück und schlägt deshalb fehl – org.hibernate.jdbc.BatchedTooManyRowsAffectedException.
  3. Grails hat einen Bug, wenn für mehrere Domain Klassen ein- und dieselbe HILO Tabelle verwendet wird.

 

Tags: , , , , , , , ,
Kategorie: Java, Softwareentwicklung | keine Kommentare »

Batched Inserts: Probleme in Hibernate mit MySQL Connector/J >= 5.1.27

17. Januar 2014 von Steffen Döring [permalink]

Möchte man Batched Inserts in MySQL nutzen und verwendet dafür Hibernate v3.6 als ORM, kommt es ab Version 5.1.27 des MySQL JDBC Treibers zu Problemen.
 

Wenn z.B. 20 neue, gleiche Entities hinzugefügt werden und die batched inserts greifen, liefert der Connector nicht mehr nur ein Resultat, sondern gibt 20 hinzugefügte Reihen zurück. Das ist nicht das, was Hibernate erwartet und quittiert seinen Dienst mit einer Exception:

org.hibernate.jdbc.BatchedTooManyRowsAffectedException: Batch update returned unexpected row count from update [0]; actual row count: 20; expected: 1

 

Mit Version 5.1.26 und älter des MySQL Connectors tritt dieser Fehler nicht auf.

 

Tags: , , , ,
Kategorie: Java, Softwareentwicklung | 2 Kommentare »

Mysql-Connection-Timeouts mit Hibernate und Spring

30. Oktober 2009 von Stephan Erdmann [permalink]

Standardmäßig ist ein Mysql-Server so konfiguriert, dass bestehende Verbindungen zur Datenbank nach 8 Stunden Inaktivität automatisch geschlossen werden. Dieses Feature ist mir bei einer Implementierung mit Spring 2.5.6, Hibernate 3.3.0 und dem Apache commons dbcp 1.2.2 mit einem unangenehmen Fehler aufgefallen: Der implementierte Webservice hatte immer wieder eine Fehlermeldung aufkommen lassen, die bei einem erneuten Webservice-Request verschwunden ist.

weiter lesen »

Tags: , , ,
Kategorie: Allgemein, Java | keine Kommentare »

MySQL: Zeilen zu Spalten ohne Pivot-Element

22. Juni 2009 von John Freytag [permalink]

Stephan Erdmann beschrieb bereits in seinem Artikel “MySQL: Pivot” wie man anhand von s.g. “Pivot Objekten” die Zeilen einer Tabelle in Spalten umwandeln kann. Diese Technik setzt allerdings voraus, dass diese Objekte auch eindeutig sind – man stösst also auf Probleme, sobald aus nicht vorher definierten Zeileninhalten Spalten machen möchte. weiter lesen »

Tags: , ,
Kategorie: Allgemein, Softwareentwicklung | keine Kommentare »

MySQL: Datennormalisierung mit Bordmitteln

19. Juni 2009 von Stephan Erdmann [permalink]

Gerade bei der Arbeit an Legacy-Umgebungen kann es durchaus sinnvoll sein, manche Implementierungen direkt in der Datenbank durchzuführen. Hierzu gehört beispielsweise die Normalisierung von Daten, die in dem Legacy-System nicht vorgesehen ist, aber der Kunde wünscht. Hierzu kann man unter Umständen das Legacy-System erweitern, was manchmal nicht möglich ist und zudem die Gefahr birgt, dass man einige anzupassende Stellen übersieht. MySQL unterstützt ab der Version 5.0 Trigger, die eine interessante Alternative bieten, um einfache Datenoperationen direkt durchzuführen, ohne dass das Legacy-System angepasst werden muss.

weiter lesen »

Tags: ,
Kategorie: Allgemein | keine Kommentare »

« ältere Einträge