Embedded databases for statistic storage
Currently OpenPatrician only allows a view of the current state of affairs for example for fleet size, reputation or available wares. Even the personal balance records only the current and the past week. For statistical purposes these data should be recorded for the whole game time. This calls for a database. This post details the findings of the evaluation of these:
- MongoDB
- Neo4j
- H2
The whole evaluation was done based upon Spring Boot, which makes it easy to use certain databases.
Criteria
- As the database is part of the application, it has to be integrated or in other words embedded
- Ease of use to insert and query the data mainly based on date ranges
- As the statistics have to be part of the savegame the content of the database needs to be savable and loadable
- Performance for adding records, selecting records, saving and loading
Common parts
Some of the code is used in all the examples:
The data model for the Fleet statistic:
package ch.sahits.game.test.database; import lombok.Getter; import org.springframework.data.annotation.Id; import java.time.LocalDate; import java.time.format.DateTimeFormatter; @EqualsAndHashCode public class ShipRecord { @Id private String id; @Getter private String playerUuid; @Getter private LocalDate date; @Getter private int fleetSize; public ShipRecord(String playerUuid, LocalDate date, int fleetSize) { this.playerUuid = playerUuid; this.date = date; this.fleetSize = fleetSize; } @Override public String toString() { return "ShipRecord{" + "id='" + id + '\'' + ", playerUuid='" + playerUuid + '\'' + ", date=" + date.format(DateTimeFormatter.BASIC_ISO_DATE) + ", fleetSize=" + fleetSize + '}'; } }
As all evaluations are done in the same maven module here is the pom.xml the specialties in it will be pointed out in the respective sections:
MongoDB
Setup
The setup is easy if you know how to tweak it. To enable MongoDB with Spring Boot the starter module is required:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-mongodb</artifactId> <version>${spring.boot.version}</version> </dependency>
To enable the embedded MongoDB this dependency is needed as well:
<dependency> <groupId>de.flapdoodle.embed</groupId> <artifactId>de.flapdoodle.embed.mongo</artifactId> <version>2.0.0</version> </dependency>
On the first start up this will automatically download and install the MongoDB so that it can be used in embedded mode. So there is no separate installation necessary.
Querying
With MongoDB the quering can be done with a Repository. This is an typed interface (based along the same premise as JpaRepository) which generates the queries automatically based on the method name. The neat thing here is that with IntelliJ (2017.1.3 Ultimate) you get suggestions how to construct the method name based on the domain model:
package ch.sahits.game.test.database; import org.springframework.data.mongodb.repository.MongoRepository; import java.time.LocalDate; import java.util.List; public interface FleetRepository extends MongoRepository<ShipRecord, String> { List<ShipRecord> findAllByPlayerUuid(String playerUuid); /** * Find all recods of a player between two dates (exclusive) */ List<ShipRecord> findAllByPlayerUuidAndDateBetweenOrderByDate(String playerUuid, LocalDate startDate, LocalDate endDate); }
For more complex queries there is also the possibility with MongoTemplate which was not further investigated.
Serializability
By default the embedded database is an in memory database. To configure the storage location these two properties have to be set:
spring.mongodb.embedded.storage.database-dir=${user.home}/.OpenPatrician/mongodb spring.mongodb.embedded.storage.oplog-size=0
The database then can be dumped and restored with these two additional beans:
private void dumpMongoDB() { // ensure directories are ok File tempDirectory = new File(mongodbDumpLocation); tempDirectory.delete(); tempDirectory.mkdir(); try { mongoDBDumper.start(); } catch (IOException e) { e.printStackTrace(); } } private void restoreMongoDB() { try { mongoDBRestorer.start(); } catch (IOException e) { e.printStackTrace(); } } @Bean public MongoDumpExecutable mongoDBDumper(IMongodConfig mongodConfig) throws IOException { int port = mongodConfig.net().getPort(); IMongoDumpConfig mongoDumpConfig = new MongoDumpConfigBuilder() .version(Version.Main.PRODUCTION) .net(new Net(port, Network.localhostIsIPv6())) .out(mongodbDumpLocation) .build(); return MongoDumpStarter.getDefaultInstance().prepare(mongoDumpConfig); } @Bean public MongoRestoreExecutable mongoDbrestorer(IMongodConfig mongodConfig) throws IOException { int port = mongodConfig.net().getPort(); IMongoRestoreConfig mongoRestoreConfig = new MongoRestoreConfigBuilder() .version(Version.Main.PRODUCTION) .net(new Net(port, Network.localhostIsIPv6())) .dropCollection(true) .dir(mongodbDumpLocation) .build(); return MongoRestoreStarter.getDefaultInstance().prepare(mongoRestoreConfig); }
Perofmance
Operation | Total duration | Average duration |
---|---|---|
Insert 100’000 records | 19.3s | 0.19ms |
Lookup records within a 2 year range with a total of 100’000 records | 1352.15s | 135.21ms |
Dump 10’000 records 1’000 times | 1063.87s | 1063.87ms |
Restore the dumped records | 3067.20s | 3067.20ms |
The long times for queries might be due to the fact that every query is logged. When the application crashes or is terminated the MongoDB has to be removed manually. This is probably something that could be worked around with using finally blocks to close the process cleanly.
Further readings
- Spring Boot with MongoDB: https://spring.io/guides/gs/accessing-data-mongodb/
- Embedded MongoDB: https://github.com/flapdoodle-oss/de.flapdoodle.embed.mongo
- Embedded MongoDB Restore and Dump Test: https://github.com/flapdoodle-oss/de.flapdoodle.embed.mongo/blob/master/src/test/java/de/flapdoodle/embed/mongo/MongoDumpExecutableTest.java
Neo4j
Setup
From the Spring side this dependency is needed:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-neo4j</artifactId> <version>${spring.boot.version}</version> </dependency>
To enable Neo4j two additional dependencies are needed:
<dependency> <groupId>org.neo4j</groupId> <artifactId>neo4j-kernel</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>org.neo4j</groupId> <artifactId>neo4j-lucene-index</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>org.neo4j</groupId> <artifactId>neo4j-ogm-embedded-driver</artifactId> <version>2.1.2</version> </dependency>
In addition to that we need the annotation @EnableNeo4jRepositories on the application configuration. The model class also needs to be annotated with @NodeEntity and the entities id is annotated with @GraphId instead of @Id and the id is of type Long. Neo4j also requires a default constructor.
When using the file based approach on Windows there are some issues with the URL, which forced me to provide my own Configuration bean:
@Bean public Configuration neo4jConfiguration(Neo4jProperties properties) { Configuration configuration = new Configuration(); DriverConfiguration driverConfiguration = configuration.driverConfiguration(); File f = new File(System.getProperty("user.home")+"/.OpenPatrician/neo4j.db"); driverConfiguration.setDriverClassName("org.neo4j.ogm.drivers.embedded.driver.EmbeddedDriver"); driverConfiguration.setURI("file:///Users/ahotz/.OpenPatrician/neo4j.db"); if(properties.getUsername() != null && properties.getPassword() != null) { driverConfiguration.setCredentials(properties.getUsername(), properties.getPassword()); } if(properties.getCompiler() != null) { configuration.compilerConfiguration().setCompilerClassName(properties.getCompiler()); } return configuration; }
Querying
The queries can be done through a Repository in the same way as the MongoDB, only that the interface extends GraphRepository:
public interface FleetNeo4jRepository extends GraphRepository<ShipRecord>
At this point Neo4j was no further investigated due to various unresolved dependencies with non available interface implementations and no documentation at all.
Further readings
- Spring Boot with Neo4j: https://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-nosql.html
- Neo4j: http://neo4j.com/docs/ogm-manual/current/reference/#reference:getting-started
H2
Setup
There are some dependencies that are needed:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> <version>${spring.boot.version}</version> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> </dependency>
The entity is annotated with the javax.persistance annotations for @Entity and the for the ID with @Id.
Querying
The repository works along the same lines as for MangoDB. The interface extends the CrudRepository.
Serializability
H2 provides tools to create a backup and restore it. However their premise is that when restoring the database is empty. Therefore creating a backup and immediately restore it again will fail. For that reason the script that is created in the backup needs to be tweaked to drop all objects to start with:
// Create a dump and add a statement to drop everything to make the restore work. try { String backupFile = "h2.backup.zip"; String tempOutputFilenName = "out.zip"; Script.main("-url", "jdbc:h2:file:~/.OpenPatrician/h2.db;DB_CLOSE_ON_EXIT=FALSE", "-user", "sa", "-script", tempOutputFilenName, "-options", "compression", "zip"); File f = new File(tempOutputFilenName); ZipFile zipFile = new ZipFile(tempOutputFilenName); final ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(backupFile)); for(Enumeration e = zipFile.entries(); e.hasMoreElements(); ) { ZipEntry entryIn = (ZipEntry) e.nextElement(); zos.putNextEntry(new ZipEntry(entryIn.getName())); InputStream is = zipFile.getInputStream(entryIn); byte[] firstBytes = "DROP ALL OBJECTS".getBytes(); zos.write(firstBytes); byte[] buf = new byte[1024]; int len; while ((len = (is.read(buf))) > 0) { zos.write(buf, 0, (len < buf.length) ? len : buf.length); } zos.closeEntry(); } zos.close(); f.delete(); } catch (SQLException |IOException e) { e.printStackTrace(); } // Restore try { RunScript.main("-url", "jdbc:h2:file:~/.OpenPatrician/h2.db;DB_CLOSE_ON_EXIT=FALSE", "-user", "sa", "-script", "h2.backup.zip", "-options", "compression", "zip"); } catch (SQLException e) { e.printStackTrace(); }
Performance
Operation | Total duration | Average duration |
---|---|---|
Insert 100’000 records | 27.12s | 0.27ms |
Lookup records within a 2 year range with a total of 100’000 records | 5443.15s | 544.31ms |
Dump 10’000 records 1’000 times | 757.06s | 757.06ms |
Restore the dumped records | 295.44s | 295.44ms |
Further readings
- Spring boot SQL databases: https://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html
- H2 documentation backup and restore: http://www.h2database.com/html/tutorial.html#upgrade_backup_restore
- StackOverflow Backup&Restore: http://stackoverflow.com/questions/44078484/stopping-and-restarting-a-file-based-h2-database-from-a-spring-application
Comparison
The setup for H2 is easier than for for MongoDB, however it is manageable for both. The solutions for serializing the database need some work for both MongoDB and H2. Both provide tools to do the job, however for H2 the generated output cannot be used as is and needs to be tweaked.
What | MongoDB | Neo4J | H2 |
---|---|---|---|
Setup | + | -∞ | ++ |
Serializablity | + | – | |
Insert 100’000 records AVG | 0.19ms | 0.27ms | |
Lookup records within a 2 year range with a total of 100’000 records AVG | 135.21ms | 544.31ms | |
Dump 100’000 records 100’000 times AVG | 1063.87ms | 757.06ms | |
Restore the dumped records AVG | 3067.20ms | 295.44ms |
Further reading
- Starting point for various No-SQL setups with Spring Boot: https://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-nosql.html
- Default Spring Boot application properties: http://docs.spring.io/spring-boot/docs/current/reference/html/common-application-properties.html
Schreibe einen Kommentar