MySQL rewriteBatchedStatements – Vlad Mihalcea

Database
antiLeave a Comment on MySQL rewriteBatchedStatements – Vlad Mihalcea


pengantar

Pada artikel ini, kita akan melihat bagaimana MySQL rewriteBatchedStatements bekerja saat menggunakan JDBC, JPA, atau Hibernate.

Saya pertama kali meneliti fitur konfigurasi MySQL ini ketika saya sedang menulis bab penyortiran dari buku High Performance Java Persistence, dan menyadari pada saat itu bahwa pengaturan ini memungkinkan untuk penyortiran sederhana. Statement Dengan menulis ulang string SQL yang dikirim ke database.

Namun, dokumentasi MySQL 6 Connector/J mencatat bahwa:

Untuk pernyataan yang disiapkan, pernyataan yang disiapkan sisi server saat ini tidak dapat menggunakan opsi penggantian ini

Jadi, untuk waktu yang lama, saya keliru berasumsi bahwa properti ini bukan untuk menangani pernyataan yang disiapkan JDBC.

Ketika saya membaca catatan rilis MySQL 8.0.30 Connector/J, saya menyadari bahwa dokumentasi telah menyesatkan kami:

Deskripsi fitur koneksi rewriteBatchedStatements Memperbaiki, pembatasan bahwa pernyataan yang disiapkan sisi server tidak dapat menggunakan opsi penulisan ulang telah dihapus. (Bug #34022110)

Jadi, rupanya, rewriteBatchedStatements Ini bekerja dengan JDBC PreparedStatements, dan untuk alasan itu, saya memutuskan untuk menguji fitur ini dan menulis temuan saya di artikel ini.

Menggunakan rewriteBatchedStatements dengan kumpulan pernyataan JDBC

Sebagian besar pengembang Java menggunakannya executeUpdate metode dari Statement Antarmuka saat Anda perlu menjalankan perintah INSERT, UPDATE, dan DELETE.

Namun, pada Java 1.2, Statement Antarmuka disediakan addBatch yang dapat kita gunakan untuk mengelompokkan beberapa perintah untuk dikirim dengan permintaan saat dipanggil executeBatch metode, seperti yang ditunjukkan pada contoh berikut:


String INSERT = "insert into post (id, title) values (%1$d, 'Post no. %1$d')";

try(Statement statement = connection.createStatement()) {
    for (long id = 1; id <= 10; id++) {
        statement.addBatch(
            String.format(INSERT, id)
        );
    }
    statement.executeBatch();
}

Sekarang, misalkan contoh di atas mengeksekusi pernyataan INSERT dalam database pulang pergi, tetapi jika Anda men-debug melalui driver MySQL JDBC, Anda akan menemukan blok kode berikut:


if (this.rewriteBatchedStatements.getValue() && nbrCommands > 4) {
    return executeBatchUsingMultiQueries(
        multiQueriesEnabled, 
        nbrCommands, 
        individualStatementTimeout
    );
}

updateCounts = new long[nbrCommands];

for (int i = 0; i < nbrCommands; i++) {
    updateCounts[i] = -3;
}

int commandIndex = 0;

for (commandIndex = 0; commandIndex < nbrCommands; commandIndex++) {
    try {
        String sql = (String) batchedArgs.get(commandIndex);
        updateCounts[commandIndex] = executeUpdateInternal(sql, true, true);
        
        ...
    } catch (SQLException ex) {
        updateCounts[commandIndex] = EXECUTE_FAILED;

        ...
    }
}

Sejak rewriteBatchedStatements Adalah falsesetiap pernyataan INSERT dieksekusi secara individual menggunakan pernyataan executeUpdateInternal panggilan metode

Oleh karena itu, bahkan jika kita menggunakan addBatch Dan executeBatchsecara default, MySQL masih mengeksekusi pernyataan INSERT satu per satu saat menggunakan JDBC biasa. Statement Tujuan – objek.

Namun, jika kita mengaktifkan rewriteBatchedStatements Fitur konfigurasi JDBC:


MysqlDataSource dataSource = new MysqlDataSource();

String url = "jdbc:mysql://localhost/high_performance_java_persistence?useSSL=false";

dataSource.setURL(url);
dataSource.setUser(username());
dataSource.setPassword(password());

dataSource.setRewriteBatchedStatements(true);

dan debug executeBatch Menjalankan metode, Anda akan melihatnya sekarang, executeBatchUsingMultiQueries Ini disebut sebagai gantinya:


if (this.rewriteBatchedStatements.getValue() && nbrCommands > 4) {
    return executeBatchUsingMultiQueries(
        multiQueriesEnabled, 
        nbrCommands, 
        individualStatementTimeout
    );
}

Dan executeBatchUsingMultiQueries Metode ini seharusnya menambahkan pernyataan INSERT tunggal ke a StringBuilder dan melakukan single execute Panggil saja:


StringBuilder queryBuf = new StringBuilder();

batchStmt = locallyScopedConn.createStatement();
JdbcStatement jdbcBatchedStmt = (JdbcStatement) batchStmt;

...

int argumentSetsInBatchSoFar = 0;

for (commandIndex = 0; commandIndex < nbrCommands; commandIndex++) {
    String nextQuery = (String) this.query.getBatchedArgs().get(commandIndex);

    ...

    queryBuf.append(nextQuery);
    queryBuf.append(";");
    argumentSetsInBatchSoFar++;
}

if (queryBuf.length() > 0) {
    try {
        batchStmt.execute(queryBuf.toString(), java.sql.Statement.RETURN_GENERATED_KEYS);
    } catch (SQLException ex) {
        sqlEx = handleExceptionForBatch(
            commandIndex - 1, argumentSetsInBatchSoFar, updateCounts, ex
        );
    }

    ...
}

Jadi, untuk JDBC sederhana Statement Kategori, MySQL rewriteBatchedStatements Properti konfigurasi menambahkan perintah yang saat ini dikonfigurasi dan menjalankannya dalam database pulang pergi.

Menggunakan rewriteBatchedStatements dengan batching JDBC PreparedStatement

Saat menggunakan JPA dan Hibernate, semua pernyataan SQL Anda dieksekusi menggunakan JDBC. PreparedStatementDan ini untuk alasan yang sangat bagus:

  • Perintah yang disiapkan memungkinkan Anda untuk meningkatkan kemungkinan menyimpan pernyataan
  • Pernyataan yang disiapkan memungkinkan Anda untuk menghindari serangan injeksi SQL karena Anda mengikat nilai parameter alih-alih menyuntikkan nilai parameter seperti sebelumnya. String.format cincin.

Namun, karena Hibernate tidak mengaktifkan pengelompokan JDBC secara default, kami perlu menyediakan properti konfigurasi berikut untuk mengaktifkan mekanisme pengelompokan otomatis:


spring.jpa.properties.hibernate.jdbc.batch_size=10;
spring.jpa.properties.hibernate.order_inserts=true;
spring.jpa.properties.hibernate.order_updates=true;

Oleh karena itu, ketika melanjutkan 10 Post Entitas:


for (long i = 1; i <= 10; i++) {
    entityManager.persist(
        new Post()
            .setId(i)
            .setTitle(String.format("Post no. %d", i))
    );
}

Hibernate ingin menjalankan JDBC INSERT, seperti yang ditunjukkan dalam entri log proxy-sumber data:


Type:Prepared, Batch:True, QuerySize:1, BatchSize:10, 
Query:["
    insert into post (title, id) values (?, ?)
"], 
Params:[
    (Post no. 1, 1), (Post no. 2, 2), (Post no. 3, 3), 
    (Post no. 4, 4), (Post no. 5, 5), (Post no. 6, 6), 
    (Post no. 7, 7), (Post no. 8, 8), (Post no. 9, 9), 
    (Post no. 10, 10)
]

Jika dari IDENTITY Strategi ID entitas Hibernate tidak dapat secara otomatis mengkategorikan pernyataan insert. Lihat artikel ini.

Oleh karena itu, menggunakan pengaturan default Driver JDBC MySQL, satu pernyataan dikirim ke server database MySQL. Namun, jika Anda memeriksa log server database, Anda akan melihat bahwa setelah perintah tiba, MySQL mengeksekusi setiap pernyataan seolah-olah dieksekusi dalam perulangan for:


Query	insert into post (title, id) values ('Post no. 1', 1)
Query	insert into post (title, id) values ('Post no. 2', 2)
Query	insert into post (title, id) values ('Post no. 3', 3)
Query	insert into post (title, id) values ('Post no. 4', 4)
Query	insert into post (title, id) values ('Post no. 5', 5)
Query	insert into post (title, id) values ('Post no. 6', 6)
Query	insert into post (title, id) values ('Post no. 7', 7)
Query	insert into post (title, id) values ('Post no. 8', 8)
Query	insert into post (title, id) values ('Post no. 9', 9)
Query	insert into post (title, id) values ('Post no. 10', 10)
Query	commit

Jadi, setelah aktivasi rewriteBatchedStatements Pengaturan driver MySQL JDBC:


dataSource.setRewriteBatchedStatements(true);

Saat kami menjalankan kembali contoh pengujian sebelumnya yang memasukkan 10 Post entitas, kita melihat bahwa pernyataan INSERT berikut dijalankan di sisi database:


Query   insert into post (title, id) 
        values ('Post no. 1', 1),('Post no. 2', 2),('Post no. 3', 3),
               ('Post no. 4', 4),('Post no. 5', 5),('Post no. 6', 6),
               ('Post no. 7', 7),('Post no. 8', 8),('Post no. 9', 9),
               ('Post no. 10', 10)
Query   commit

Alasan pernyataan diubah adalah karena driver MySQL JDBC sekarang menyebutnya executeBatchWithMultiValuesClause Metode yang menulis ulang pernyataan INSERT batch menjadi INSERT multinilai.


if (!this.batchHasPlainStatements && 
	this.rewriteBatchedStatements.getValue()) {

	if (getQueryInfo().isRewritableWithMultiValuesClause()) {
		return executeBatchWithMultiValuesClause(batchTimeout);
	}

	...
}

Waktu ujian

Untuk ekspresi sederhana, tidak perlu menguji rewriteBatchedStatements Optimalisasi karena sebagian besar pernyataan SQL yang Anda jalankan menggunakan JDBC, JPA, Hibernate, atau jOOQ dieksekusi menggunakan JDBC. PreparedStatement Penyambung.

Jadi, saat menjalankan tes yang berjalan 5000 post Menggunakan ukuran batch 100 Untuk durasi 60 detik, kami mendapatkan hasil sebagai berikut:

MySQL rewriteBatchedStatements

Dan berikut adalah metrik Dropwizard untuk kedua skenario:


Test MySQL batch insert with rewriteBatchedStatements=false
type=TIMER, name=batchInsertTimer, count=55, min=909.9544999999999, max=1743.0735, mean=1072.3787996947426, stddev=128.4560649360703, median=1049.4146, p75=1106.231, p95=1224.2176, p98=1649.8706, p99=1743.0735, p999=1743.0735, mean_rate=0.8612772397894758, m1=0.6330960191792878, m5=0.3192705968508436, m15=0.24209506781664528, rate_unit=events/second, duration_unit=milliseconds

Test MySQL batch insert with rewriteBatchedStatements=true
type=TIMER, name=batchInsertTimer, count=441, min=80.09599999999999, max=565.4343, mean=112.20623474996226, stddev=29.01211110828766, median=103.52319999999999, p75=120.9807, p95=161.3664, p98=173.9123, p99=182.2464, p999=565.4343, mean_rate=7.263224298238385, m1=6.872524588278418, m5=6.547662085190082, m15=6.453339001683109, rate_unit=events/second, duration_unit=milliseconds

Jelas bahwa MySQL rewriteBatchedStatements Pengaturan ini memberikan keuntungan karena waktu eksekusi seluruh batch jauh lebih singkat ketika fitur ini diaktifkan.

Seperti yang dijelaskan dalam dokumentasi MySQL, ada beberapa hal yang harus diperhatikan:

  • Statement.getGeneratedKeys() Ini hanya berfungsi ketika pernyataan yang ditimpa hanya terdiri dari pernyataan INSERT atau REPLACE. Ini sebenarnya bukan masalah saat menggunakan JPA dan Hibernate karena hanya INSERT yang di-batch selama flash.
  • menulis ulang INSERT ... ON DUPLICATE KEY UPDATE Pernyataan mungkin tidak berfungsi seperti yang diharapkan, tetapi, sekali lagi, ini bukan masalah untuk JPA dan Hibernate, karena INSERT default dari ON DUPLICATE KEY UPDATE Frasa.

Jika Anda menikmati artikel ini, saya yakin Anda akan mencintai saya Buku Dan Kursus video juga.






Hasil

Sementara driver MySQL JDBC disediakan rewriteBatchedStatements Setting Untuk waktu yang lama, tidak jelas untuk apa fitur ini, karena dokumentasinya agak menyesatkan PreparedStatement Pengelompokan

Jadi, jika tugas pemrosesan batch Anda berjalan di MySQL, aktifkan rewriteBatchedStatements Tuning dapat memberikan kinerja yang lebih baik.

E-book transaksi dan kontrol bersamaan



Source link

Related Posts

Memperkenalkan Distribusi Snapshot di MongoDB Atlas

anti

Perintah SQL untuk memeriksa tanggal dan waktu saat ini (stempel waktu) di SQL Server

anti

Memperkenalkan ADO.NET – Devart Blog

anti

Tinggalkan Balasan

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *