Un indice intéressant de Vladimir Sitnikov m’a fait penser à une nouvelle référence pour jOOQ :
Le benchmark doit vérifier si les requêtes à une seule ligne doivent avoir un JDBC Statement.setFetchSize(1) appel qui leur est fait par défaut. Le Javadoc de la méthode dit :
Donne au pilote JDBC un indice quant au nombre de lignes qui doivent être extraites de la base de données lorsque davantage de lignes sont nécessaires pour
ResultSetobjets générés par ceStatement. Si la valeur spécifiée est zéro, l’indication est ignorée. La valeur par défaut est zéro.
Si un ORM (par exemple jOOQ) sait qu’il ne récupérera qu’une seule ligne, ou s’il sait qu’il ne peut y avoir qu’une seule ligne, alors cet indice a certainement du sens. Les exemples dans jOOQ incluent :
- Lorsque les utilisateurs appellent
ResultQuery.fetchSingle()oufetchOne()oufetchOptional(), ou toute méthode similaire, il est raisonnable de s’attendre à ce que seules 0 à 1 lignes soient renvoyées. Dans le cas de ces méthodes renvoyant plus d’une ligne, une exception est levée, donc même s’il y a plus de lignes, 2 lignes seront récupérées au maximum. - Lorsque les utilisateurs ajoutent un
LIMIT 1clause sur une requête de niveau supérieur, il ne peut jamais y avoir plus d’une ligne. - Lorsque la requête est triviale (pas de jointures, ou seulement des jointures à un, pas de
GROUP BY GROUPING SETSNonUNIONetc.) et un prédicat d’égalité sur unUNIQUEcontrainte est présente, il ne peut pas non plus y avoir plus d’une ligne.
L’optimiseur de base de données connaît également toutes ces choses. Si vous ajoutez LIMIT 1 à une requête, on peut raisonnablement s’attendre à ce que l’optimiseur considère cela comme un indice fort sur la taille de l’ensemble de résultats. Mais le pilote JDBC ne sait pas ces choses (ou du moins, on ne devrait pas s’y attendre), car il est peu probable qu’il analyse le SQL et calcule des statistiques dessus, ou considère les métadonnées pour de telles optimisations.
Ainsi, l’utilisateur pourrait faire allusion. Et parce que ce serait très fastidieux pour les utilisateurs, encore mieux, l’ORM (par exemple jOOQ) devrait faire allusion. Ou alors il semble.
Repères
Mais devrait-il? Est-ce que ça vaut vraiment la peine ? Voici l’évaluation de Vladimir sur le pilote pgjdbc, où il ne s’attendrait pas à une amélioration maintenantmais peut-être dans le futur.
Mieux que de faire des hypothèses, mesurons, en utilisant un benchmark JMH. JMH est normalement utilisé pour le microbenchmarking des choses sur la JVM, pour tester les hypothèses sur le comportement d’exécution JIT. Ce n’est évidemment pas un microbenchmark, mais j’aime toujours l’approche et la sortie de JMH, qui incluent les écarts-types et les erreurs, ainsi que les pénalités de préchauffage, etc.
Tout d’abord, les résultats :
Étant donné que les résultats de référence ne peuvent pas être publiés pour certains RDBMS commerciaux (du moins pas lors de la comparaison entre RDBMS), j’ai normalisé les résultats afin qu’une comparaison de la vitesse d’exécution réelle entre RDBMS ne soit pas possible. C’est-à-dire que pour chaque RDBMS, l’exécution la plus rapide est 1 et la plus lente est une fraction de 1. De cette façon, le RDBMS n’est comparé qu’à lui-même, ce qui est juste.
Les résultats sont ci-dessous. Nous mesurons le débit, donc plus bas c’est pire.
Db2 --- Benchmark Mode Score JDBCFetchSizeBenchmark.fetchSize1 thrpt 0.677 JDBCFetchSizeBenchmark.noFetchSize thrpt 1.000 MySQL ----- Benchmark Mode Score JDBCFetchSizeBenchmark.fetchSize1 thrpt 0.985 JDBCFetchSizeBenchmark.noFetchSize thrpt 1.000 Oracle ------ Benchmark Mode Score JDBCFetchSizeBenchmark.fetchSize1 thrpt 0.485 JDBCFetchSizeBenchmark.noFetchSize thrpt 1.000 PostgreSQL ---------- Benchmark Mode Score JDBCFetchSizeBenchmark.fetchSize1 thrpt 1.000 JDBCFetchSizeBenchmark.noFetchSize thrpt 0.998 SQL Server ---------- Benchmark Mode Score JDBCFetchSizeBenchmark.fetchSize1 thrpt 0.972 JDBCFetchSizeBenchmark.noFetchSize thrpt 1.000
Pour chaque RDBMS, j’ai exécuté une requête triviale produisant une seule ligne avec 1 colonne. A chaque fois, j’ai recréé un JDBC Statementet récupéré le ResultSet. Dans fetchSize1, j’ai spécifié l’indice de taille de récupération. Dans noFetchSize, j’ai laissé la valeur par défaut intacte. Comme on peut le résumer :
Dans ces RDBMS, il n’y avait aucun effet
- MySQL
- PostgreSQLName
- serveur SQL
Dans ces RDBMS, les choses se sont considérablement améliorées pire (pas mieux!):
C’est assez surprenant, car le benchmark inclut l’exécution de l’intégralité de l’instruction sur le serveur, donc je m’attendais, au mieux, à un résultat négligeable.
Pour ce benchmark, j’utilisais ces versions de serveur et de pilote JDBC :
- Db2 11.5.6.0 avec jcc-11.5.6.0
- MySQL 8.0.29 avec mysql-connector-java-8.0.28
- Oracle 21c avec ojdbc11-21.5.0.0
- PostgreSQL 14.1 avec postgresql-42.3.3
- SQL Server 2019 avec mssql-jdbc-10.2.0
La logique de référence est ici :
package org.jooq.test.benchmarks.local;
import java.sql.*;
import org.openjdk.jmh.annotations.*;
import org.openjdk.jmh.infra.Blackhole;
@Fork(value = 1)
@Warmup(iterations = 3, time = 3)
@Measurement(iterations = 7, time = 3)
public class JDBCFetchSizeBenchmark {
@State(Scope.Benchmark)
public static class BenchmarkState {
Connection connection;
@Setup(Level.Trial)
public void setup() throws Exception {
Class.forName("org.postgresql.Driver");
connection = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/postgres",
"postgres",
"test"
);
}
@TearDown(Level.Trial)
public void teardown() throws Exception {
connection.close();
}
}
@FunctionalInterface
interface ThrowingConsumer<T> {
void accept(T t) throws SQLException;
}
private void run(
Blackhole blackhole,
BenchmarkState state,
ThrowingConsumer<Statement> c
) throws SQLException {
try (Statement s = state.connection.createStatement()) {
c.accept(s);
try (ResultSet rs = s.executeQuery(
"select title from t_book where id = 1")
) {
while (rs.next())
blackhole.consume(rs.getString(1));
}
}
}
@Benchmark
public void fetchSize1(Blackhole blackhole, BenchmarkState state)
throws SQLException {
run(blackhole, state, s -> s.setFetchSize(1));
}
@Benchmark
public void noFetchSize(Blackhole blackhole, BenchmarkState state)
throws SQLException {
run(blackhole, state, s -> {});
}
}
Quelques remarques :
- La requête n’est en aucun cas représentative d’une charge de travail de production. Mais si les choses s’amélioraient grâce au
fetchSizedrapeau, l’amélioration aurait dû se manifester - Le benchmark n’a pas utilisé d’instructions préparées, ce qui aurait pu supprimer certains effets secondaires ou en ajouter. N’hésitez pas à répéter le benchmark à l’aide d’instructions préparées.
- On ne comprend pas encore pourquoi les choses n’avaient pas d’importance dans certains pilotes, ou pourquoi elles l’étaient dans d’autres. Pour la conclusion, le “pourquoi” n’est pas trop important, car rien ne sera changé à la suite de cet article de blog. Si vous savez pourquoi (le pilote db2 et le code ojdbc ne sont malheureusement pas open source), je serais curieux.
Conclusion
Les optimisations sont une bête délicate. Certaines choses semblent avoir beaucoup de sens lorsque l’on raisonne à leur sujet, mais en réalité des mesuresla chose apparemment la plus optimale est en fait pire, ou non pertinente.
Dans ce cas, au début, il semblait que nous devions indiquer au pilote JDBC nos intentions de récupérer une seule ligne. Je ne sais pas pourquoi le pilote JDBC s’est comporté pire que si je ne l’avais pas laissé entendre. Peut-être a-t-il alloué un tampon trop petit et a-t-il dû l’augmenter, plutôt que d’allouer un tampon trop grand, mais suffisamment grand. Je le sais maintenant, grâce au commentaire de Douglas Surber sur la discussion reddit. Le problème est que ojdbc ne sait pas s’il y aura plus de lignes, donc le JDBC rs.next() l’appel doit faire un autre aller-retour. Pour plus de détails, voir le commentaire lié ci-dessus.
J’ai fait des benchmarks similaires dans le passé, en essayant “d’optimiser” les tailles initiales de ArrayList ou StringBuilder. J’étais à peine capable de surpasser systématiquement les valeurs par défaut. Parfois, « l’amélioration » semblait améliorer les choses. Parfois, cela a aggravé les choses.
En l’absence de victoires claires (qui sont comprises, ne faites pas non plus aveuglément confiance aux résultats de référence, même si vous gagnez !), J’ai perdu confiance dans ces améliorations et ne les ai pas mises en œuvre à la fin. Ce cas ici est le même. Je n’ai pas été en mesure d’obtenir des améliorations, mais dans 2 cas sur 5, les choses se sont considérablement aggravées.
Suivi
Sur /r/java, il y avait eu une discussion à propos de cet article. Il a suggéré 2 vérifications supplémentaires :
1. Essayez d’utiliser un fetchSize de 2
Vous seriez tenté de penser que d’autres tailles de récupération pourraient toujours être appropriées, par exemple 2, pour empêcher cette augmentation potentielle de la taille de la mémoire tampon. je viens d’essayer avec Oracle uniquementproduisant :
JDBCFetchSizeBenchmark.fetchSize1 thrpt 0.513 JDBCFetchSizeBenchmark.fetchSize2 thrpt 0.968 JDBCFetchSizeBenchmark.noFetchSize thrpt 1.000
Alors que la pénalité de fixer le fetchSize pour 1 a disparu, il n’y a à nouveau aucune amélioration par rapport à la valeur par défaut. Pour une explication, voir à nouveau le commentaire de Douglas Surber sur reddit
2. Essayez d’utiliser PreparedStatements
À mon avis, PreparedStatement l’utilisation ne devrait pas avoir d’importance pour ce benchmark spécifique, c’est pourquoi je les avais initialement laissés de côté. Quelqu’un sur la discussion reddit était impatient de mettre tout son argent sur le single PreparedStatement carte, alors voici un résultat mis à jour, encore une fois avec Oracle uniquementen comparant les instructions statiques avec celles préparées (code de référence mis à jour ci-dessous) :
Benchmark Mode Score JDBCFetchSizeBenchmark.fetchSizePrepared1 thrpt 0.503 JDBCFetchSizeBenchmark.fetchSizeStatic1 thrpt 0.518 JDBCFetchSizeBenchmark.fetchSizePrepared2 thrpt 0.939 JDBCFetchSizeBenchmark.fetchSizeStatic2 thrpt 0.994 JDBCFetchSizeBenchmark.noFetchSizePrepared thrpt 1.000 JDBCFetchSizeBenchmark.noFetchSizeStatic thrpt 0.998
Le résultat est le même pour les deux. Non seulement cela, on peut voir que dans ma configuration particulière (Interroger Oracle XE 21c dans docker, localement), il n’y a absolument aucune différence entre l’utilisation d’une instruction statique et d’une instruction préparée dans ce cas.
Il serait à nouveau intéressant d’étudier pourquoi c’est, les hypothèses peuvent inclure par exemple
- ojdbc met également en cache les instructions statiques dans le cache des instructions préparées
- l’effet de la mise en cache d’une instruction préparée est négligeable dans un benchmark qui n’exécute qu’une seule instruction, ce qui est loin d’être représentatif d’une charge de travail de production
- l’effet côté client de la préparation des instructions n’est pas pertinent par rapport aux avantages du cache de curseur côté serveur, ou par rapport à l’effet néfaste de la définition du
fetchSizepour1
Le code de référence mis à jour :
package org.jooq.test.benchmarks.local;
import java.sql.*;
import org.openjdk.jmh.annotations.*;
import org.openjdk.jmh.infra.Blackhole;
@Fork(value = 1)
@Warmup(iterations = 3, time = 3)
@Measurement(iterations = 7, time = 3)
public class JDBCFetchSizeBenchmark {
@State(Scope.Benchmark)
public static class BenchmarkState {
Connection connection;
@Setup(Level.Trial)
public void setup() throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521/XEPDB1",
"TEST",
"TEST"
);
}
@TearDown(Level.Trial)
public void teardown() throws Exception {
connection.close();
}
}
@FunctionalInterface
interface ThrowingConsumer<T> {
void accept(T t) throws SQLException;
}
private void runPrepared(
Blackhole blackhole,
BenchmarkState state,
ThrowingConsumer<Statement> c
) throws SQLException {
try (PreparedStatement s = state.connection.prepareStatement(
"select title from t_book where id = 1")
) {
c.accept(s);
try (ResultSet rs = s.executeQuery()) {
while (rs.next())
blackhole.consume(rs.getString(1));
}
}
}
private void runStatic(
Blackhole blackhole,
BenchmarkState state,
ThrowingConsumer<Statement> c
) throws SQLException {
try (Statement s = state.connection.createStatement()) {
c.accept(s);
try (ResultSet rs = s.executeQuery(
"select title from t_book where id = 1")
) {
while (rs.next())
blackhole.consume(rs.getString(1));
}
}
}
@Benchmark
public void fetchSizeStatic1(Blackhole blackhole, BenchmarkState state)
throws SQLException {
runStatic(blackhole, state, s -> s.setFetchSize(1));
}
@Benchmark
public void fetchSizeStatic2(Blackhole blackhole, BenchmarkState state)
throws SQLException {
runStatic(blackhole, state, s -> s.setFetchSize(2));
}
@Benchmark
public void noFetchSizeStatic(Blackhole blackhole, BenchmarkState state)
throws SQLException {
runStatic(blackhole, state, s -> {});
}
@Benchmark
public void fetchSizePrepared1(Blackhole blackhole, BenchmarkState state)
throws SQLException {
runPrepared(blackhole, state, s -> s.setFetchSize(1));
}
@Benchmark
public void fetchSizePrepared2(Blackhole blackhole, BenchmarkState state)
throws SQLException {
runPrepared(blackhole, state, s -> s.setFetchSize(2));
}
@Benchmark
public void noFetchSizePrepared(Blackhole blackhole, BenchmarkState state)
throws SQLException {
runPrepared(blackhole, state, s -> {});
}
}