Go to Qiita Advent Calendar 2024 Top
LoginSignup
5
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

MySQL CasualAdvent Calendar 2017

Day 21
@hmatsu47

MySQL Connector/Jでプロパティをあれこれ変えてベンチマークその3：Prepared Statement キャッシュ編

Last updated at Posted at 2017-12-21

昨日の MySQL Casual Advent Calendar 2017 は@soudai1025さんの なぜあなたは SHOW ENGINE INNODB STATUS を読まないのか でした。

の続きです。

今回は、プリペアードステートメントのキャッシュ機能を有効にすることで性能向上するかを検証します。

1. 検証対象のプロパティ

のうち、

  • cachePrepStmts プリペアードステートメントのキャッシュを使う（true）／使わない（false）
  • prepStmtCacheSize キャッシュするプリペアードステートメントの数（25）
  • prepStmtCacheSqlLimit キャッシュするプリペアードステートメントの最大文字数（256）

※**太字**がデフォルト

について、デフォルトから変更したときのパフォーマンスの変化を検証します。

※その2同様、以下の記事が参考になります。

また、↑の記事でも参照されていますが、HikariCP（高速なコネクションプーリング）の推奨値がこちらに示されています。

今回は、その1で使ったコードを修正し（テーブルは新たに用意）、

  • 8スレッド
  • 1スレッドあたり100,000行
  • 10,000行単位でCOMMIT
  • 70種類のプリペアードステートメントを発行

の形で、以下の3パターンを比較して検証します。

  • その2で使ったプロパティのみ**（キャッシュ無効）**
  • その2で使ったプロパティ＋キャッシュ有効＋キャッシュ上限50個＋最大文字数2,048
  • その2で使ったプロパティ＋キャッシュ有効＋キャッシュ上限100個＋最大文字数2,048

2. 検証結果

それぞれの所要時間を確認した結果を示します。

検証パターン 所要時間 (ms)
その2で使ったプロパティのみ 38,057
その2で使ったプロパティ＋cachePrepStmts=true&prepStmtCacheSize=50&prepStmtCacheSqlLimit=2048 39,907
その2で使ったプロパティ＋cachePrepStmts=true&prepStmtCacheSize=100&prepStmtCacheSqlLimit=2048 30,027
  • キャッシュ上限＜発行プリペアードステートメントの種類（個数）の場合はほぼ誤差の範囲
  • キャッシュ上限≧発行プリペアードステートメントの種類（個数）の場合は速くなる

ことがわかりました。

※結果としては示していませんが、キャッシュ上限（prepStmtCacheSize）69個・70個でも実際に試して確認しています。

3. まとめ

正直、クライアントベースのプリペアードステートメントでキャッシュの効果が出るとは意外でした。
一度、ソースコードを確認して、どのような処理をしているのか確認してみたいと思います。

※今回は「Casual Advent Calendar」なのでそこまではしませんが。

ただ、日常的に利用するプリペアードステートメントの数がキャッシュ上限を1つでも超えてしまうと、途端に効果がゼロ（場合によってはマイナス）になってしまう恐れがあるので（キャッシュアルゴリズムがあまりよくないのかも）、使い方がかなり難しい気がします。

※検証コードがよくない、という面もあります。各プリペアードステートメントをスレッドごとに1種類1行ずつ繰り返しており、1周する中で常にほぼ同じ順番でキャッシュにイン／アウトしていますので。

少なくとも、HikariCPが250を推奨しているから250にしておこうというやり方は、アプリケーションの成長によって思わぬトラブルを生む可能性があるので、やめておいたほうが良いでしょう。

明日の MySQL Casual Advent Calendar 2017 は@huatoさんの docker-composeで手軽にgroup replicationの試験環境構築をしてみる です。

4. 検証コードなど

ベースはその1で使ったものです。
以下、追加・変更しています。

DbInsert.Java（66行目よりコード追加）

	private static final int		PSMT_ALL_COLUMN	= 70;
	private static final int		PSMT_STORE_COLUMN	= 10;
	private static final String 	PSMT_MEMO			= "1234567890";

	public void psmtInsert(int totalline, int commitline, String option) {

		try (
			Connection 			con		= new DbConnection().getConnectionForTest(JDBC_URL + option);
		) {
			con.setAutoCommit(false);

			for (int i = 0; i < totalline; i++) {
				StringBuilder	sb_pfx	= new StringBuilder("INSERT INTO psmt_test.psmt_test (");
				StringBuilder	sb_sfx	= new StringBuilder(" VALUES (");
				for (int j = 1; j <= PSMT_STORE_COLUMN; j++) {
					sb_pfx.append("memo" + (Integer.toString((i % PSMT_ALL_COLUMN) + j) + ((j == PSMT_STORE_COLUMN) ? ")" : ", ")));
					sb_sfx.append("?" + ((j == PSMT_STORE_COLUMN) ? ")" : ", "));
				}
				try (
					PreparedStatement	psmt	= con.prepareStatement(sb_pfx.toString() + sb_sfx.toString());
				) {
					for (int j = 1; j <= PSMT_STORE_COLUMN; j++) {
						psmt.setString(j, PSMT_MEMO);
					}
					psmt.execute();
					if (((i + 1) % commitline == 0) || ((i + 1) == totalline)) {
						con.commit();
					}
				} catch (SQLException e) {
					throw e;				}
			}
		} catch (ClassNotFoundException e) {
			System.out.println("[Error] Driver not found.");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("[Error] Invalid DB access.");
			e.printStackTrace();
		}
	}
PsmtInsert.java（新規追加）
package site.hmatsu47.conjtest;

public class PsmtInsert extends Thread {

	private int 	totalline	= 0;
	private int 	commitline	= 0;
	private String option;
	private String title;

	public PsmtInsert(int totalline, int commitline, String option, String title) {
		this.totalline	= totalline;
		this.commitline	= commitline;
		this.option		= option;
		this.title		= title;
	}

	public void run() {
		long	starttime	= System.nanoTime();

		new DbInsert().psmtInsert(totalline, commitline, option);

		long	endtime	= System.nanoTime();

		System.out.println("[Psmt]   " + title + " : " + String.valueOf((endtime - starttime) / (1000 * 1000)) + " msec.");
	}
}
Main.java（変更）
package site.hmatsu47.conjtest;

public class Main {

	private static final int		DEFAULT_SIMPLE_THREAD	= 0;
	private static final int		DEFAULT_BATCH_THREAD		= 1;
	private static final int		DEFAULT_TOTAL_LINE		= 1000000;
	private static final int		DEFAULT_BATCH_LINE		= 100;
	private static final int		DEFAULT_COMMITL_LINE		= 1000;
	private static final String	DEFAULT_JDBC_OPTION		= "";
	private static final int		DEFAULT_PSMT_THREAD		= 0;

	public static void main(String args[]) {
		try {
			final int		simplethread	= (args.length < 1 ? DEFAULT_SIMPLE_THREAD : Integer.valueOf(args[0]).intValue());
			final int		batchthread	= (args.length < 2 ? DEFAULT_BATCH_THREAD : Integer.valueOf(args[1]).intValue());
			final int		totalline		= (args.length < 3 ? DEFAULT_TOTAL_LINE : Integer.valueOf(args[2]).intValue());
			final int		batchline		= (args.length < 4 ? DEFAULT_BATCH_LINE : Integer.valueOf(args[3]).intValue());
			final int		commitline		= (args.length < 5 ? DEFAULT_COMMITL_LINE : Integer.valueOf(args[4]).intValue());
			final String	option			= (args.length < 6 ? DEFAULT_JDBC_OPTION : args[5]);
			final int		psmtthread		= (args.length < 7 ? DEFAULT_PSMT_THREAD : Integer.valueOf(args[6]).intValue());

			for (int i = 1; i <= simplethread; i++) {
				SimpleInsert si = new SimpleInsert(totalline, commitline, option, String.valueOf(i));
				si.start();
				Thread.sleep(1000);
			}

			for (int i = 1; i <= batchthread; i++) {
				BatchInsert bi = new BatchInsert(totalline, batchline, commitline, option, String.valueOf(i));
				bi.start();
				Thread.sleep(1000);
			}

			for (int i = 1; i <= psmtthread; i++) {
				PsmtInsert pi = new PsmtInsert(totalline, commitline, option, String.valueOf(i));
				pi.start();
				Thread.sleep(1000);
			}
		}
		catch (Exception e) {
			System.out.println("[Error]");
			e.printStackTrace();
		}
	}
}
追加DB・テーブル定義
CREATE DATABASE psmt_test;
CREATE TABLE psmt_test.psmt_test
  (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   memo1 VARCHAR(10),
   memo2 VARCHAR(10),
   memo3 VARCHAR(10),
   memo4 VARCHAR(10),
   memo5 VARCHAR(10),
   memo6 VARCHAR(10),
   memo7 VARCHAR(10),
   memo8 VARCHAR(10),
   memo9 VARCHAR(10),
   memo10 VARCHAR(10),
   memo11 VARCHAR(10),
   memo12 VARCHAR(10),
   memo13 VARCHAR(10),
   memo14 VARCHAR(10),
   memo15 VARCHAR(10),
   memo16 VARCHAR(10),
   memo17 VARCHAR(10),
   memo18 VARCHAR(10),
   memo19 VARCHAR(10),
   memo20 VARCHAR(10),
   memo21 VARCHAR(10),
   memo22 VARCHAR(10),
   memo23 VARCHAR(10),
   memo24 VARCHAR(10),
   memo25 VARCHAR(10),
   memo26 VARCHAR(10),
   memo27 VARCHAR(10),
   memo28 VARCHAR(10),
   memo29 VARCHAR(10),
   memo30 VARCHAR(10),
   memo31 VARCHAR(10),
   memo32 VARCHAR(10),
   memo33 VARCHAR(10),
   memo34 VARCHAR(10),
   memo35 VARCHAR(10),
   memo36 VARCHAR(10),
   memo37 VARCHAR(10),
   memo38 VARCHAR(10),
   memo39 VARCHAR(10),
   memo40 VARCHAR(10),
   memo41 VARCHAR(10),
   memo42 VARCHAR(10),
   memo43 VARCHAR(10),
   memo44 VARCHAR(10),
   memo45 VARCHAR(10),
   memo46 VARCHAR(10),
   memo47 VARCHAR(10),
   memo48 VARCHAR(10),
   memo49 VARCHAR(10),
   memo50 VARCHAR(10),
   memo51 VARCHAR(10),
   memo52 VARCHAR(10),
   memo53 VARCHAR(10),
   memo54 VARCHAR(10),
   memo55 VARCHAR(10),
   memo56 VARCHAR(10),
   memo57 VARCHAR(10),
   memo58 VARCHAR(10),
   memo59 VARCHAR(10),
   memo60 VARCHAR(10),
   memo61 VARCHAR(10),
   memo62 VARCHAR(10),
   memo63 VARCHAR(10),
   memo64 VARCHAR(10),
   memo65 VARCHAR(10),
   memo66 VARCHAR(10),
   memo67 VARCHAR(10),
   memo68 VARCHAR(10),
   memo69 VARCHAR(10),
   memo70 VARCHAR(10),
   memo71 VARCHAR(10),
   memo72 VARCHAR(10),
   memo73 VARCHAR(10),
   memo74 VARCHAR(10),
   memo75 VARCHAR(10),
   memo76 VARCHAR(10),
   memo77 VARCHAR(10),
   memo78 VARCHAR(10),
   memo79 VARCHAR(10),
   memo80 VARCHAR(10)
  );
5
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?