INSERT10万件をマルチスレッドで分割

20130426 追記　実行時間の計測方法がおかしかったので書き直し

Oracle内部の処理が並列になるパラレルクエリ等はともかく、Oracleの外部から与える複数のINSERTを分割しても意味は無い気がするが、実際にどうなるかやってみる。

環境

jdk1.7.0_17
Eclipse 4.2.0 SR2
ojdbc6.jar
Oracle Database Express Edition 11g Release 2をWindows上で動作
Oracle SQL Developer 3.2

準備

下記のテーブルに10万件INSERTを行い、その実行時間を計測する。計測用プログラムを一度走らせるたびに、DROP&CREATEをしなおす。

DROP TABLE DEST PURGE;
CREATE TABLE DEST (COLUMN1 VARCHAR2(16));

入力データは左ゼロ埋めで 000001〜100000 の10万件入るようにする。

別途、インデックス付きのテーブルも実行時間を計測する。

DROP TABLE DEST PURGE;
CREATE TABLE DEST (COLUMN1 VARCHAR2(16) , CONSTRAINT DEST_PK PRIMARY KEY(COLUMN1));

計測用プログラム

例えばスレッド数4の場合各スレッドは、00001〜25000、25001〜50000、50001〜75000、75001〜100000のデータをINSERTする。コミット間隔は1000で固定。マルチスレッドというより、スレッドごとにOracleセッション作るので、むしろマルチセッションとでも言うべきものなのだが。

public class InsertThread implements Callable<Integer> {

    private int start;
    private int insertSize;
    private int commitInterval;

    public InsertThread(int start, int insertSize, int commitInterval) {
        this.start = start;
        this.insertSize = insertSize;
        this.commitInterval = commitInterval;
    }

    @Override
    public Integer call() throws Exception {

        final String sqlStr = "INSERT INTO DEST(COLUMN1) VALUES(?)";
        try (Connection connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@192.168.0.12:1521:XE", "kagamihoge", "xxxx");
                PreparedStatement sql = connection.prepareStatement(sqlStr);) {
            connection.setAutoCommit(false);

            int j = 0;
            final int n = start + insertSize;
            for (int i = start; i < n; i++) {
                sql.setString(1, String.format("%016d", i));
                sql.executeUpdate();
                
                if (j == commitInterval) {
                    connection.commit();
                    j = 0;
                }
                j++;
            }
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

単に待ち合わせをするためにだけにCallableにしている……ので、バリヤにすればよかったと後悔。

main側は、全スレッドの起動と終了待ち合わせまでの時間を計測する。

public class Main {
    public static void main(String[] args) {
        long s = System.currentTimeMillis();
        
        final int INSERT_SIZE = 100_000;
        final int THREAD_SIZE = 10;
        final int INSERT_PER_THREAD_SIZE = INSERT_SIZE / THREAD_SIZE;
        final int COMMIT_INTERVAL = 1000;
        
        List<Future<Integer>> results = new ArrayList<>();
        ExecutorService threadPool = Executors.newFixedThreadPool(THREAD_SIZE);
        for (int i=0; i<THREAD_SIZE; i++) {
            int startNum = i * INSERT_PER_THREAD_SIZE + 1;
            results.add(threadPool.submit(
               new InsertThread(startNum, INSERT_PER_THREAD_SIZE, COMMIT_INTERVAL)));
        }
        for (Future<Integer> result : results) {
            try {
                result.get();
            } catch (InterruptedException | ExecutionException e) {
                e.printStackTrace();
            }
        }
        threadPool.shutdown();
        
        long e = System.currentTimeMillis();
        System.out.println(e - s);
    }

}

結果

インデックス無しのテーブル

↓THREAD_SIZE n回目→	1	2	3
1	45468	42141	47703
2	23891	23391	23391
4	17859	14031	13703
10	12265	11875	12421

インデックス有りのテーブル

↓THREAD_SIZE n回目→	1	2	3
1	50422	55578	55922
2	26093	24578	24266
4	16703	17047	16906
10	13844	15390	14969

感想

スレッド数（＝Oracleのセッション数）を1から2にすると、実行時間はほぼ半分になった。マルチスレッドにしたところでOracleに対して並行処理を依頼しているわけではないので、実行時間は大して変わらない、と思い込んでいただけにこの結果は驚きであった……スレッド数を増やしていくと、2個以降はさすがに実行時間は劇的に減ったりしないが、減ることは減る。つまり、複数のセッションから同一内容のクエリが来る場合に関しては、Oracleは効率的に捌くことができる、ってとこでしょうか。それぞれのセッションのトランザクションが全くロック待ちとかをしないケースなんで余計に有利なんだろうけど。

が、Oracleのセッションを複数個使ってまでやる価値があるかというと、大変ビミョウな気がする。

また、表の行順序がバラけてしまうのでインデックス範囲スキャンをする場合は問題となる。参考→表の行順序とインデックス範囲スキャン - kagamihogeのblog

オマケ

上記では、1スレッド1セッションを暗黙の了解としていた。Oracleの常識的にはそうすべきだし、Connection (Java Platform SE 7 )にもスレッドセーフなんてことは一言も書いていないので、java.sql.Connectionインスタンスを複数スレッドで共有は果てしなく危険である……が、せっかくなので、複数スレッドで単一のjava.sql.Connectionインスタンスを使用してINSERTを発行したらどうなるか、あえてやってみる。

先のプログラムを若干変更し、各スレッドにjava.sql.Connectionを渡すようにする。テーブルはPKつきのものを使用する。

public class Main {
    public static void main(String[] args) {
        long s = System.currentTimeMillis();
        
        final int INSERT_SIZE = 100_000;
        final int THREAD_SIZE = 10;
        final int INSERT_PER_THREAD_SIZE = INSERT_SIZE / THREAD_SIZE;
        final int COMMIT_INTERVAL = 1000;

        List<Future<Integer>> results = new ArrayList<>();
        ExecutorService threadPool = Executors.newFixedThreadPool(THREAD_SIZE);

        try (Connection connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@192.168.0.12:1521:XE", "kagamihoge", "xxxx");) {
            connection.setAutoCommit(false);
            for (int i = 0; i < THREAD_SIZE; i++) {
                int startNum = i * INSERT_PER_THREAD_SIZE + 1;
                results.add(threadPool.submit(new InsertThread(connection,
                        startNum, INSERT_PER_THREAD_SIZE, COMMIT_INTERVAL)));
            }
            for (Future<Integer> result : results) {
                try {
                    result.get();
                } catch (InterruptedException | ExecutionException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        threadPool.shutdown();
        
        long e = System.currentTimeMillis();
        System.out.println(e - s);
    }
}

驚いたことに、何のエラーも発生せずに実行完了する。が、実行時間はというと……

↓スレッド数 n回目→	1	2	3
2	40734	41469	42437
4	43032	44156	42906
10	42812	48187	44297

変化無しといって良いレベル。一度に一人しか通れない道を複数人で通ろうとしても総合の実行時間は変わらない、といったところだと思われる。

ちなみに、上記のプログラムは各スレッドでPreparedStatementのインスタンスを生成しているが、このインスタンスをスレッド間で共有すると速攻で一意制約違反が発生する。

kagamihogeの日記

kagamihogeの日記です。

INSERT10万件をマルチスレッドで分割

環境

準備

計測用プログラム

結果

感想

オマケ