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 »

Grails: Obacht bei HILO IDs aus gleicher Tabelle

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

Grails’ GORM unterstützt die üblichen ID Strategien von Hibernate für Objekt Instanzen. Das Handling dafür ist einfach gehalten und muss lediglich zum Mapping hinzugefügt werden.
 

Beim ebenfalls unterstützten HILO ID Generator gibt es einen Stolperstein (um nicht das Wort “Bug” zu verwenden), der es nur mit etwas zusätzlichem Aufwand erlaubt, ein und dieselben Tabelle für mehrere HILO IDs zu verwenden.
Die HILO Tabelle wird automatisch beim Start der Applikation erstellt, allerdings immer nur für eine einzelne Domain.
 

Ein Beispiel von Köchen und Rezepten:

class Cook {

    /* */

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

class Recipe {

    /* */

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

 

Sobald ein neuer Koch oder neues Rezept hinzugefügt wird, fliegt folgende Exception:

The error is: org.springframework.dao.InvalidDataAccessResourceUsageException: could not get or update next value; SQL [null]; nested exception is org.hibernate.exception.SQLGrammarException: could not get or update next value
                /*

                */
Caused by: org.hibernate.exception.SQLGrammarException: could not get or update next value
Caused by: org.h2.jdbc.JdbcSQLException: Feld "NEXT_COOK" nicht gefunden
                Column "NEXT_COOK" not found; SQL statement: select next_cook from hilo_table for update
                /*

                */

Die nötige Spalte für die nächste Cook ID wurde nicht in der hilo_table angelegt.
 

Dies lässt sich im Bootstrap / in der Migration leicht umgehen – hier als Beispiel für die H2 Datenbank (Integrationstests) implementiert.

    groovy.sql.Sql sql = groovy.sql.Sql.newInstance(dataSource)
    sql.execute('ALTER TABLE HILO_TABLE ADD COLUMN IF NOT EXISTS NEXT_COOK INTEGER(10) DEFAULT NULL')
    sql.execute('ALTER TABLE HILO_TABLE ADD COLUMN IF NOT EXISTS NEXT_RECIPE INTEGER(10) DEFAULT NULL')
    sql.executeUpdate('UPDATE HILO_TABLE SET NEXT_COOK=0, NEXT_RECIPE=0')

Der Service dataSource muss hierfür in die BootStrap.groovy injected werden.

Tags: , , , , ,
Kategorie: Java | 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 »

Grails Entwicklung – leider nur single-threaded

12. November 2013 von Steffen Döring [permalink]

Für die professionelle Entwicklung mit Grails ist ein performantes System mit guter CPU und viel Speicher unerlässlich. Mit steigender Klassenzahl steigt ebenfalls die dafür nötige Kompilier-, Test- und Startup-Zeit.

Wichtig ist hierbei aber nicht die Anzahl der Kerne der CPU. Das Kompilieren erfolgt größtenteils single-threaded. Sechs oder mehr Kerne bedeuten also nicht zwangsläufig ein schnelleres Resultat.

weiter lesen »

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

Grails PageRenderer mit Locale

28. November 2012 von Dirk Dittmar [permalink]

Wenn man in Grails mit dem PageRenderer versucht Templates in mehreren Sprachen zu rendern (z.B. für Emails) merkt man schnell das dem PageRenderer offensichtlich eine Option für die Locale fehlt. weiter lesen »

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

« ältere Einträge