The Difficulty of Performance Evaluation of HikariCP in Dropwizard

$ gzip -d -c questions.csv.gz | xsv slice --end 10 | xsv table

Id  CreationDate          ClosedDate            DeletionDate          Score  OwnerUserId  AnswerCount
1   2008-07-31T21:26:37Z  NA                    2011-03-28T00:53:47Z  1      NA           0
4   2008-07-31T21:42:52Z  NA                    NA                    458    8            13
6   2008-07-31T22:08:08Z  NA                    NA                    207    9            5
8   2008-07-31T23:33:19Z  2013-06-03T04:00:25Z  2015-02-11T08:26:40Z  42     NA           8
9   2008-07-31T23:40:59Z  NA                    NA                    1410   1            58
11  2008-07-31T23:55:37Z  NA                    NA                    1129   1            33
13  2008-08-01T00:42:38Z  NA                    NA                    451    9            25
14  2008-08-01T00:59:11Z  NA                    NA                    290    11           8
16  2008-08-01T04:59:33Z  NA                    NA                    78     2            5
17  2008-08-01T05:09:55Z  NA                    NA                    114    2            11

max_connections = 200
shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 5242kB
maintenance_work_mem = 256MB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 10

CREATE TABLE questions (
    id serial PRIMARY KEY,
    creationDate TIMESTAMPTZ,
    closedDate TIMESTAMPTZ,
    deletionDate TIMESTAMPTZ,
    score int,
    ownerUserId int,
    answerCount int
);

COPY questions FROM '/home/nick/questions.csv'
WITH (HEADER, FORMAT CSV, NULL 'NA');

CREATE INDEX user_idx ON questions(ownerUserId);

public interface QuestionQuery {
    @SqlQuery("SELECT id, creationDate, closedDate, deletionDate, score, ownerUserId, answerCount\n" +
            "FROM questions WHERE ownerUserId = :user")
    List<Question> findQuestionsFromUser(@Bind("user") int user);
}

public class QuestionMapper implements ResultSetMapper<Question> {
    @Override
    public Question map(int i, ResultSet r, StatementContext ctx) throws SQLException {
        final ResultColumnMapper<LocalDateTime> dtmapper = ctx.columnMapperFor(LocalDateTime.class);
        return Question.create(
                r.getInt(1),
                dtmapper.mapColumn(r, 2, ctx),
                dtmapper.mapColumn(r, 3, ctx),
                dtmapper.mapColumn(r, 4, ctx),
                r.getInt(5),
                r.getInt(6),
                r.getInt(7)
        );
    }
}

@AutoValue
public abstract class Question {
    @JsonProperty
    public abstract int serial();

    @JsonProperty
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd")
    public abstract LocalDateTime creation();

    @JsonProperty
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd")
    public abstract Optional<LocalDateTime> closed();

    @JsonProperty
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd")
    public abstract Optional<LocalDateTime> deletion();

    @JsonProperty
    public abstract int score();

    @JsonProperty
    public abstract int ownerUserId();

    @JsonProperty
    public abstract int answers();

    public static Question create(
            int serial,
            LocalDateTime creation,
            LocalDateTime closed,
            LocalDateTime deletion,
            int score,
            int owner,
            int answers
    ) {
        return new AutoValue_Question(
                serial,
                creation,
                Optional.ofNullable(closed),
                Optional.ofNullable(deletion),
                score,
                owner,
                answers
        );
    }
}

@Path("/")
@Produces(MediaType.APPLICATION_JSON)
public class QuestionResource {
    private final QuestionQuery dao;
    public QuestionResource(QuestionQuery dao) {
        this.dao = dao;
    }

    @GET
    @Timed
    public List<Question> findQuestionsFromUser(@QueryParam("user") int userid) {
        return dao.findQuestionsFromUser(userid);
    }
}

server:
  minThreads: 7
  maxThreads: ${maxThreads}
  adminConnectors: []
  requestLog:
    appenders: []

#!/bin/bash

# The HikariCP test case extends the base yaml with the following
# additional configuration
HIKARI_YAML=$(cat <<'EOF'
hikari:
  datasourceClassName: org.postgresql.ds.PGSimpleDataSource
  properties:
    'databaseName': 'postgres'
  user: nick
  minSize: ${poolSize}
  maxSize: ${poolSize}
  password: nick
EOF
)

# The Tomcat test case extends the base yaml with the following
# additional configuration. Notice how more configuration options
# are specified, one of the selling points of Hikari is that
# there are no "unsafe" options.
TOMCAT_YAML=$(cat <<'EOF'
tomcat:
  driverClass: org.postgresql.Driver
  url: jdbc:postgresql://localhost/postgres
  user: nick
  minSize: ${poolSize}
  maxSize: ${poolSize}
  initialSize: ${poolSize}
  rollbackOnReturn: true
  checkConnectionOnBorrow: true
  autoCommitByDefault: false
  validationInterval: '1 second'
  validatorClassName: 'com.example.TomValidator'
  password: nick
EOF
)

# The actual function to do the load test
load_test () {
    YAML="$1"
    CONNECTIONS="$3"
    URL="http://benchmark:8080?user=87"

    # Nested for loop creates 42 configurations for each
    # test case, which causes this to be long script!
    for i in 1 2 4 8 16 32; do
    for j in 1 2 4 8 16 32 64; do
        export POOL_SIZE=$i
        export MAX_THREADS=$((6 + j))
        export CONFIG=$2
        echo "Pool: ${POOL_SIZE}. Server threads: ${MAX_THREADS}"
        echo "${YAML}" | ssh nick@benchmark "cat - config_base.yaml > config_new.yaml
            # Kill the previous server instance if one exists
            pkill java

            # Wait for it to cleanly shut down as we reuse ports, etc
            sleep 3s

            # Set the environment variables used in the configuration files
            # and use nohup so that when this ssh command (and connection) exits
            # the server keeps running
            poolSize=${POOL_SIZE} maxThreads=${MAX_THREADS} nohup \
                java -jar echo-db.jar server config_new.yaml >/dev/null 2>&1 &

            # Wait for the server to properly initialize
            sleep 5s"

        # Load test for 60 seconds with four threads (as this machine has four
        # CPUs to dedicate to load testing) Also use a custom lua script that
        # reports various statistics into a csv format for further analysis as
        # there'll be 80+ rows, with each row having several statistics.
        # We're using "tee" here so that we can see all the stdout but only
        # the last line, which is what is important to the csv is appended
        # to the csv
        wrk -c "$CONNECTIONS" -d 60s -t 4 -s report.lua ${URL} | \
            tee >(tail -n 1 >> wrk-$CONNECTIONS.csv)
    done;
    done;
}

# Call our function!
load_test "$TOMCAT_YAML" "tomcat" 300
load_test "$HIKARI_YAML" "hikari" 300

done = function(summary, latency, requests)
    io.write(string.format("%s,%s,%s,%d,%d,%d,%d,%d,%d\n",
        os.getenv("CONFIG"),
        os.getenv("POOL_SIZE"),
        os.getenv("MAX_THREADS"),
        summary.requests,
        latency.mean,
        latency.stdev,
        latency:percentile(50),
        latency:percentile(90),
        latency:percentile(99)))
end

library(tidyverse)
library(readr)

columns <- c("config", "pool size", "max threads", "requests", "mean", "stdev", "p50", "p90", "p99")
df <- read_csv("/home/nick/Downloads/wrk-100.csv", col_names = columns)

# Subtract 6 from max threads because Jetty uses six threads to manage the request
# threads and other activities
df <- mutate(df, `max threads` = `max threads` - 6)

ggplot(df, aes(factor(`pool size`), mean, ymin=0)) +
  geom_jitter(aes(colour = config), width=0.15) +
  labs(title = "Mean response at different pool sizes",
       x = "DB Pool size",
       y = "Response time (us)")

ggplot(df, aes(factor(`pool size`), p99, ymin=0)) +
  geom_jitter(aes(colour = config), width=0.15) +
  labs(title = "99th Percentile at different pool sizes",
       x = "DB Pool size",
       y = "Response time (us)")

ggplot(df, aes(factor(`pool size`), requests, ymin=0)) +
  geom_jitter(aes(colour = config), width=0.15) +
  labs(title = "Requests served at different pool sizes",
       x = "DB Pool size",
       y = "Requests")

dodge <- position_dodge(width=.9)
df %>% filter(`pool size` == 4) %>%
  ggplot(aes(factor(`max threads`), mean, fill=config)) +
  geom_bar(stat='identity', position=dodge) +
  geom_errorbar(aes(ymin = p50, ymax = p90), position=dodge) +
  labs(title = "Mean response for DB pool size of 4 with [median, 90%] error bars",
       x = "Jetty Max Threads",
       y = "Response time (us)")

# Fastest 5 by mean response
df %>% top_n(-5, mean) %>%
  arrange(mean) %>%
  transmute(str = str_c("|", str_c(config, `pool size`, `max threads`, mean, sep="|"), "|"))

# Slowest 5 by mean response
df %>% top_n(5, mean) %>%
  arrange(-mean) %>%
  transmute(str = str_c("|", str_c(config, `pool size`, `max threads`, mean, sep="|"), "|"))

# Fastest 5 by 99% percentile in response
df %>% top_n(-5, p99) %>%
  arrange(p99) %>%
  transmute(str = str_c("|", str_c(config, `pool size`, `max threads`, p99, sep="|"), "|"))

# Slowest 5 by 99% percentile in response
df %>% top_n(5, p99) %>%
  arrange(-p99) %>%
  transmute(str = str_c("|", str_c(config, `pool size`, `max threads`, p99, sep="|"), "|"))

# Top 5 for requests served
df %>% top_n(-5, requests) %>%
  arrange(requests) %>%
  transmute(str = str_c("|", str_c(config, `pool size`, `max threads`, requests, sep="|"), "|"))

# Bottom 5 for requests served
df %>% top_n(5, requests) %>%
  arrange(-requests) %>%
  transmute(str = str_c("|", str_c(config, `pool size`, `max threads`, requests, sep="|"), "|"))

public class EchoApplication extends Application<EchoConfiguration> {
    public static void main(final String[] args) throws Exception {
        new EchoApplication().run(args);
    }

    @Override
    public String getName() {
        return "Echo";
    }

    @Override
    public void initialize(final Bootstrap<EchoConfiguration> bootstrap) {
        bootstrap.setConfigurationSourceProvider(
                new SubstitutingSourceProvider(bootstrap.getConfigurationSourceProvider(),
                        new EnvironmentVariableSubstitutor(true)
                )
        );
    }

    @Override
    public void run(final EchoConfiguration config,
                    final Environment environment) {
        final DBIFactory factory = new DBIFactory();

        // Since Java generic are not covariant we must use map:
        // http://stackoverflow.com/q/2660827/433785
        final Optional<PooledDataSourceFactory> tomcatFactory = config.getTomcatFactory().map(x -> x);
        final Optional<PooledDataSourceFactory> hikariFactory = config.getHikariFactory().map(x -> x);
        final PooledDataSourceFactory datasource = tomcatFactory.orElse(hikariFactory.orElse(null));
        final DBI jdbi = factory.build(environment, datasource, "postgresql");
        jdbi.registerMapper(new QuestionMapper());
        final QuestionQuery dao = jdbi.onDemand(QuestionQuery.class);
        environment.jersey().register(new QuestionResource(dao));
    }
}

@Override
public ManagedDataSource build(final MetricRegistry metricRegistry, final String name) {
    final Properties properties = new Properties();
    for (final Map.Entry<String, String> property : this.properties.entrySet()) {
        properties.setProperty(property.getKey(), property.getValue());
    }

    final HikariConfig config = new HikariConfig();
    config.setAutoCommit(this.autoCommit);
    config.setDataSourceProperties(properties);
    config.setDataSourceClassName(this.datasourceClassName);
    config.setMaximumPoolSize(this.maxSize);
    config.setMinimumIdle(this.minSize);
    config.setPoolName(name);
    config.setUsername(this.user);
    config.setPassword(this.user != null && this.password == null ? "" : this.password);
    config.setConnectionTestQuery(this.validationQuery);
    return new HikariManagedPooledDataSource(config, metricRegistry);
}

/* A custom Tomcat connection validator, which returns true if the connection
   is valid. Taken from the HikariCP-benchmark project:
   https://github.com/brettwooldridge/HikariCP-benchmark/blob/c6bb2da16b70933fb83bdcdb662ce6bf1f7ae991/src/main/java/com/zaxxer/hikari/benchmark/BenchBase.java */
public class TomValidator implements Validator {
    @Override
    public boolean validate(Connection connection, int validateAction) {
        try {
            return (validateAction != PooledConnection.VALIDATE_BORROW || connection.isValid(0));
        } catch (SQLException e) {
            return false;
        }
    }
}