OpenPatrician Blog

HELP needed!

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

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

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

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

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.