Get best of the week

Deux alternatives Ă  JDBC

Les exemples pour deux bibliothÚques d'accÚs à la base de données SQL non bloquantes sont Vert.X SQL et R2DBC . Les exemples seront basés sur des wrappers réactifs PostgreSQL et Java.



Commençons par l'essentiel: JDBC est un excellent standard. Servi et sert fidÚlement.


Mais les nouvelles tendances demandent de nouvelles solutions. Et il est mĂȘme logique de pousser parfois JDBC.


Je suggĂšre de jeter un Ɠil Ă  deux implĂ©mentations alternatives d'accĂšs aux bases de donnĂ©es SQL Ă  partir d'applications JVM et Ă  leur (implĂ©mentations) applicabilitĂ©.


, . — , .



. , (PostgreSQL 12) . , , , , , .


, JDK — OpenJDK 11, , Oracle JDK 8, .


pg_sleep(seconds) PostgreSQL.


— (netty). patch-, . , netty , , HTTP-.


, . . , JDBC, . , , JDBC. — , ThreadLocal , .


, , GitHub.


, . , -, , "" . , - - , "" .


. .


— , . , . , .


, "", — . - . , , . , — . , — ? , API . API JDBC, , — JPA, JOOQ, Hibernate ORM .


- , - " " — , . , . — ( ). , , . " " , — , , "" .


1: , - ( — , ) . , ( ) , (, ).


2: , . , . - , , REST , . "" , .


, "" "", - . "" - . — . -, "" , . , — -, .


, — . , , , — " 2". , .


. .


: , / ( Spring Data R2DBC ). JDBC — JPA, JOOQ, Hibernate ORM, . , , , . , — , .


: . :


  • JDBC , , .
  • . , Oracle .

Vert.X SQL (PG) Client.


Eclipse, GitHub Postgres.
:


  • PostgreSQL, MySQL, MSSQL, DB2
  • callback, RxJava2. Kotlin, .
  • vert.x , Quarkus.
  • .

→


H2, . , , , .


, (rxjava2) :


    <dependency>
      <groupId>io.vertx</groupId>
      <artifactId>vertx-pg-client</artifactId>
      <version>3.9.0</version>
    </dependency>
    <dependency>
      <groupId>io.vertx</groupId>
      <artifactId>vertx-rx-java2</artifactId>
      <version>3.9.0</version>
    </dependency>
    <dependency>
      <groupId>io.netty</groupId>
      <artifactId>netty-transport-native-epoll</artifactId>
      <version>4.1.15.Final</version>
      <classifier>linux-x86_64</classifier>
    </dependency>

API:


import io.vertx.pgclient.PgConnectOptions;
import io.vertx.reactivex.pgclient.PgPool;
import io.vertx.reactivex.sqlclient.Pool;
import io.vertx.reactivex.sqlclient.Tuple;
import io.vertx.sqlclient.PoolOptions;

. , prepared statement — .


        PgConnectOptions connectOptions = new PgConnectOptions()
                .setPort(5432)
                .setHost(host)
                .setDatabase("postgres")
                .setCachePreparedStatements(true)
                .setPreparedStatementCacheMaxSize(1000)
                .setSsl(false)
                .setUser(user)
                .setPassword(password);

pool (, 50).


       PoolOptions poolOptions = new PoolOptions()
                .setMaxSize(pool);
        Pool client = PgPool.pool(connectOptions, poolOptions);

. :


  2. ,
  3. , Tuple
  4. RowSet, Iterable
  5. .
  6. , "" .

.
$1 $2 $3 . , rx — RxJava2, vert.x. callback hell. RxJava2 , :


        Flowable<String> titles = client.rxGetConnection()
                .flatMapPublisher(connection ->
                        connection.rxPrepare("SELECT title FROM nicer_but_slower_film_list WHERE FID = $1")
                                .flatMap(statement -> statement.query().rxExecute(Tuple.of(Math.abs(random.nextInt(990)))))
                                .flattenAsFlowable(Functions.identity())
                                .map(row -> row.getString("title"))
                                .doOnError(Throwable::printStackTrace)
                                .subscribeOn(Schedulers.computation())
                                .doFinally(connection::close));


client.close();

R2DBC


→ 


:


  • — MariaDB, MySQL, PostgreSQL, MSSQL, H2
  • Spring Data R2DBC, Spring Boot
  • Reactive Streams, Project Reactor

:


    <dependency>
      <groupId>io.r2dbc</groupId>
      <artifactId>r2dbc-postgresql</artifactId>
      <version>0.8.2.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>io.r2dbc</groupId>
      <artifactId>r2dbc-pool</artifactId>
      <version>0.8.2.RELEASE</version>
    </dependency>

Project Reactor.


:


import io.r2dbc.pool.ConnectionPool;
import io.r2dbc.pool.ConnectionPoolConfiguration;
import io.r2dbc.spi.ConnectionFactories;
import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.ConnectionFactoryOptions;

import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

. Prepared Statement , .


        ConnectionFactory connectionFactory = ConnectionFactories.get(ConnectionFactoryOptions.builder()
                .option(DRIVER, "postgresql")
                .option(HOST, host)
                .option(PORT, 5432)  // optional, defaults to 5432
                .option(USER, user)
                .option(PASSWORD, password)
                .option(DATABASE, "postgres")  // optional
                .option(SSL, false)  // optional
                .build());
        ConnectionPoolConfiguration configuration = ConnectionPoolConfiguration.builder(connectionFactory)
                .maxIdleTime(Duration.ofMillis(1000))
                .maxSize(poolSize)
                .build();

        ConnectionPool pool = new ConnectionPool(configuration);

. :


  2. ,
  5. Result
  6. mapper- .
  7. , "" .

— close, . $1 $2 $3 . Project Reactor :


        Flux<String> titles = Flux.usingWhen(pool.create(), connection ->
                        Flux.from(
                                connection.createStatement("SELECT title FROM nicer_but_slower_film_list WHERE FID = $1")
                                        .bind("$1", Math.abs(random.nextInt(990)))
                                        .execute()
                        ).flatMap(result -> result.map(RdbcTest::getTitle))
                , Connection::close);        

    private static String getTitle(Row row, RowMetadata meta) {
        return row.get("title", String.class);
    }

.


       pool.close();


, "" , - - . R2DBC, VertX JDBC. Executions (, 50 000) , concurrency (, 1000) . backpressure.


, . . , (, ) .


VertX
        Flowable.range(1, executions)
                .doOnNext(i -> { if (i % chunk == 0) LOGGER.info("Process {}", i);})
                .flatMap(i -> client.preparedQuery(
                        "SELECT title FROM nicer_but_slower_film_list WHERE FID = $1")
                                .rxExecute(Tuple.of(Math.abs(random.nextInt(990))))
                                .doOnError(Throwable::printStackTrace)
                                .flattenAsFlowable(Functions.identity())
                                .map(row -> row.getString("title").length())
                                .doOnError(Throwable::printStackTrace)
                                .subscribeOn(Schedulers.computation()),
                        false, concurrency)
                .doOnComplete(() -> LOGGER.info("Done with VertX"))
                .blockingSubscribe(unused -> {                }, Throwable::printStackTrace);

R2DBC
        Flux.range(1, executions)
                .doOnNext(i -> {  if (i % chunk == 0) LOGGER.info("Processing {}", i);})
                .flatMap(i -> Flux.usingWhen(pool.create(),
                        connection -> Flux.from(
                                connection.createStatement("SELECT title FROM nicer_but_slower_film_list WHERE FID = $1")
                                        .bind("$1", Math.abs(random.nextInt(990)))
                                        .execute()
                        )
                                .flatMap(result -> Flux.from(result.map(RdbcTest::getTitle))),
                        Connection::close)
                                .subscribeOn(Schedulers.parallel())
                                .doOnError(Throwable::printStackTrace)
                        , concurrency)
                .doOnError(Throwable::printStackTrace)
                .doOnComplete(() -> LOGGER.info("Done with R2DBC"))
                .blockLast();

JDBC + Hikari Pool + Reactor
       HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://" + host + "/postgres");
        config.setMaximumPoolSize(poolSize);
        config.setIsolateInternalQueries(false);

        HikariDataSource ds = new HikariDataSource(config);

        Flux.range(1, executions)
                .flatMap(Mono::just)
                .flatMap(index -> Mono.fromCallable(ds::getConnection)
                                .doOnNext(i -> { if (index % chunk == 0) LOGGER.info("Process {}", index);})
                                .map(this::request).subscribeOn(Schedulers.elastic())
                        , concurrency
                )
                .subscribeOn(Schedulers.elastic())
                .doOnError(Throwable::printStackTrace)
                .doOnComplete( ()->LOGGER.info("Done with JDBC"))
                .blockLast();

    private Integer request(Connection connection) {
        try {
            var s = connection.prepareStatement(
                    "SELECT title FROM nicer_but_slower_film_list WHERE FID = ?"
            );
            s.setInt(1, Math.abs(random.nextInt(990)));
            var results = s.executeQuery();
            int counter = 0;
            while (results.next()) {
                counter += results.getString("title").length();
            }
            results.close();
            s.close();
            connection.close();
            return counter;
        } catch (RuntimeException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new IllegalStateException(e);
        }
    }

c time:


/usr/bin/time --verbose java ...

Java Mission Control — Java Flight Recorder.


java -XX:StartFlightRecording=disk=true,dumponexit=true,filename=/tmp/r2dbc.jfr,settings=profile,path-to-gc-roots=false,delay=1s,name=R2DBC ...

time userspace system, . VertX , R2DBC JDBC , . "" ( ). , R2DBC system , userspace. , vertx .


time R2DBC
        Command being timed: "java -jar r2dbc-1.0-SNAPSHOT-jar-with-dependencies.jar -iterations 50000 -concurrent 1000 -pool 50 -user anonymous -password 12345678 -host pg12.local"
        User time (seconds): 34.28
        System time (seconds): 5.55
        Percent of CPU this job got: 10%
        Maximum resident set size (kbytes): 307004
        Minor (reclaiming a frame) page faults: 76835
        Voluntary context switches: 121789
        Involuntary context switches: 9670

time JDBC
        Command being timed: "java -jar jdbc-1.0-SNAPSHOT-jar-with-dependencies.jar -iterations 50000 -concurrent 1000 -pool 50 -user anonymous -password 12345678 -host pg12.local"
        User time (seconds): 38.72
        System time (seconds): 5.80
        Percent of CPU this job got: 76%
        Maximum resident set size (kbytes): 459688
        Minor (reclaiming a frame) page faults: 125453
        Voluntary context switches: 187752
        Involuntary context switches: 14221

time VertX
        Command being timed: "java -jar vertx-1.0-SNAPSHOT-jar-with-dependencies.jar -iterations 50000 -concurrent 1000 -pool 50-user anonymous -password 12345678 -host pg12.local"
        User time (seconds): 19.06
        System time (seconds): 2.02
        Percent of CPU this job got: 20%
        Maximum resident set size (kbytes): 178516
        Minor (reclaiming a frame) page faults: 43054
        Voluntary context switches: 109914
        Involuntary context switches: 4691

Que montre Java Mission Control? JDBC nécessite plus de mémoire et plus souvent GC. Mais il y a des images impressionnantes pour les présentations dans la section Threads.


Fine non bloquante


R2DBC:


R2DBC


VertX:


Vertx


Et le JDBC effrayant:


Jdbc


Tout est rouge, tout est bloquĂ©. L'horreur semble l'ĂȘtre. Eh bien, vous pouvez demander "quoi?". En elles-mĂȘmes, les lignes rouges ne signifient rien et ne sont pas en elles-mĂȘmes un problĂšme. Le problĂšme est quand ils interfĂšrent avec d'autres activitĂ©s d'application. C'est trĂšs probable, mais cela doit ĂȘtre prouvĂ© dans chaque cas sĂ©parĂ©ment.


J'espÚre que l'article vous a été utile!

More articles:


All Articles