ベンチマーク (JdbcRunner) を DB2 for IBM i でやってみる (3) - TPC-B - (V7R1)

今回は TPC-B のテストキットを DB2 for IBM i で動かしてみます。


データのロード

あらかじめ、SQL 実行画面で

CREATE SCHEMA TPCB 

を実行してスキーマは作成しておいてください。

java JR scripts/tpcb_load_400.js -jdbcUrl jdbc:as400://hostname/TPCB -jdbcDriver com.ibm.as400.access.AS400JDBCDriver -jdbcUser USRPRF -jdbcPass PASSWORD

こちらが修正済みのスクリプトです。間違いや見落としのないように全文を載せました。(以降、同じ理由で修正を行ったスクリプトは全文載せています。あしからずご了承ください)

/*
 * Tiny TPC-B 1.1 - data loader
 * This script is based on TPC-B Standard Specification 2.0.
 *
 * [Oracle Database]
 * shell> sqlplus "/ AS SYSDBA"
 * sql> CREATE USER tpcb IDENTIFIED BY tpcb;
 * sql> GRANT connect, resource TO tpcb;
 *
 * [MySQL]
 * shell> mysql -u root [-p]
 * sql> CREATE DATABASE tpcb;
 * sql> GRANT ALL PRIVILEGES ON tpcb.* TO tpcb@'%' IDENTIFIED BY 'tpcb';
 *
 * [PostgreSQL]
 * shell> psql -U postgres
 * sql> CREATE DATABASE tpcb;
 * sql> CREATE USER tpcb PASSWORD 'tpcb';
 *
 * <postgresql.conf>
 * listen_addresses = '*'
 * port = 5432
 *
 * <pg_hba.conf>
 * host all all 0.0.0.0/0 md5
 */

// JdbcRunner settings -----------------------------------------------

// Oracle Database
// var jdbcUrl = "jdbc:oracle:thin://@localhost:1521/TPCB";

// MySQL
var jdbcUrl = "jdbc:mysql://localhost:3306/tpcb?rewriteBatchedStatements=true";

// PostgreSQL
// var jdbcUrl = "jdbc:postgresql://localhost:5432/tpcb";

var jdbcUser = "tpcb";
var jdbcPass = "tpcb";
var isLoad = true;
var nAgents = 4;
var isAutoCommit = false;
var logDir = "logs";

// Application settings ----------------------------------------------

var BATCH_SIZE = 100;

var TID_SCALE = 10;
var AID_SCALE = 100000;

// JdbcRunner functions ----------------------------------------------

function init() {
    if (getId() == 0) {
        var scale = param0;
        var taskQueue = new java.util.concurrent.LinkedBlockingQueue();
        
        info("Tiny TPC-B 1.1 - data loader");
        info("-param0  : Scale factor (default : 16)");
        info("-nAgents : Parallel loading degree (default : 4)");
        
        if (scale == 0) {
            scale = 16;
        }
        
        info("Scale factor            : " + scale);
        info("Parallel loading degree : " + nAgents);
        
        for (var branchId = 1; branchId <= scale; branchId++) {
            taskQueue.offer(branchId);
        }
        
        putData("TaskQueue", taskQueue);
        
        if (getDatabaseProductName() == "Oracle") {
            dropTable();
            createTableOracle();
        } else if (getDatabaseProductName() == "MySQL") {
            dropTable();
            createTableMySQL();
        } else if (getDatabaseProductName() == "PostgreSQL") {
            dropTable();
            createTablePostgreSQL();
        } else if (getDatabaseProductName() == "DB2 UDB for AS/400") {
            dropTable();
            createTablePostgreSQL();
        } else {
            error(getDatabaseProductName() + " is not supported yet.");
        }
        
        commit();
    }
}

function run() {
    var branchId = Number(getData("TaskQueue").poll());
    
    if (branchId != 0) {
        info("Loading branch id " + branchId + " by agent " + getId() + " ...");
        
        loadBranches(branchId);
        loadTellers(branchId);
        loadAccounts(branchId);
        commit();
    } else {
        setBreak();
    }
}

function fin() {
    if (getId() == 0) {
        if (getDatabaseProductName() == "Oracle") {
            createIndexOracle();
            createForeignKeyOracle();
            gatherStatsOracle();
        } else if (getDatabaseProductName() == "MySQL") {
            // Do nothing.
        } else if (getDatabaseProductName() == "PostgreSQL") {
            createIndexPostgreSQL();
            createForeignKeyPostgreSQL();
            gatherStatsPostgreSQL();
        } else if (getDatabaseProductName() == "DB2 UDB for AS/400") {
            createIndexPostgreSQL();
            createForeignKeyPostgreSQL();
        } else {
            error(getDatabaseProductName() + " is not supported yet.");
        }
        
        commit();
        info("Completed.");
    }
}

// Application functions ---------------------------------------------

function dropTable() {
    info("Dropping tables ...");
    
    dropTableByName("history");
    dropTableByName("accounts");
    dropTableByName("tellers");
    dropTableByName("branches");
}

function dropTableByName(tableName) {
    try {
        execute("DROP TABLE " + tableName);
    } catch (e) {
        warn(e);
        rollback(); // PostgreSQL requires a rollback.
    }
}

function createTableOracle() {
    info("Creating tables ...");
    
    execute("CREATE TABLE branches ("
        + "bid NUMBER, "
        + "bbalance NUMBER, "
        + "filler CHAR(88))");
    
    execute("CREATE TABLE tellers ("
        + "tid NUMBER, "
        + "bid NUMBER, "
        + "tbalance NUMBER, "
        + "filler CHAR(84))");
    
    execute("CREATE TABLE accounts ("
        + "aid NUMBER, "
        + "bid NUMBER, "
        + "abalance NUMBER, "
        + "filler CHAR(84))");
    
    execute("CREATE TABLE history ("
        + "tid NUMBER, "
        + "bid NUMBER, "
        + "aid NUMBER, "
        + "delta NUMBER, "
        + "mtime TIMESTAMP, "
        + "filler CHAR(22))");
}

function createTableMySQL() {
    info("Creating tables ...");
    
    execute("CREATE TABLE branches ("
        + "bid INT PRIMARY KEY, "
        + "bbalance INT, "
        + "filler CHAR(88)) "
        + "ENGINE = InnoDB");
    
    execute("CREATE TABLE tellers ("
        + "tid INT PRIMARY KEY, "
        + "bid INT, "
        + "tbalance INT, "
        + "filler CHAR(84), "
        + "CONSTRAINT tellers_fk1 "
            + "FOREIGN KEY (bid) REFERENCES branches (bid)) "
        + "ENGINE = InnoDB");
    
    execute("CREATE TABLE accounts ("
        + "aid INT PRIMARY KEY, "
        + "bid INT, "
        + "abalance INT, "
        + "filler CHAR(84), "
        + "CONSTRAINT accounts_fk1 "
            + "FOREIGN KEY (bid) REFERENCES branches (bid)) "
        + "ENGINE = InnoDB");
    
    execute("CREATE TABLE history ("
        + "id INT PRIMARY KEY AUTO_INCREMENT, " // A surrogate key for ascending inserts.
        + "tid INT, "
        + "bid INT, "
        + "aid INT, "
        + "delta INT, "
        + "mtime DATETIME, "
        + "filler CHAR(22), "
        + "CONSTRAINT history_fk1 "
            + "FOREIGN KEY (tid) REFERENCES tellers (tid), "
        + "CONSTRAINT history_fk2 "
            + "FOREIGN KEY (bid) REFERENCES branches (bid), "
        + "CONSTRAINT history_fk3 "
            + "FOREIGN KEY (aid) REFERENCES accounts (aid)) "
        + "ENGINE = InnoDB");
}

function createTablePostgreSQL() {
    info("Creating tables ...");
    
    execute("CREATE TABLE branches ("
        + "bid INTEGER, "
        + "bbalance INTEGER, "
        + "filler CHAR(88))");
    
    execute("CREATE TABLE tellers ("
        + "tid INTEGER, "
        + "bid INTEGER, "
        + "tbalance INTEGER, "
        + "filler CHAR(84))");
    
    execute("CREATE TABLE accounts ("
        + "aid INTEGER, "
        + "bid INTEGER, "
        + "abalance INTEGER, "
        + "filler CHAR(84))");
    
    execute("CREATE TABLE history ("
        + "tid INTEGER, "
        + "bid INTEGER, "
        + "aid INTEGER, "
        + "delta INTEGER, "
        + "mtime TIMESTAMP, "
        + "filler CHAR(22))");
}

function createIndexOracle() {
    info("Creating indexes ...");
    
    execute("ALTER TABLE branches ADD CONSTRAINT branches_pk PRIMARY KEY (bid)");
    execute("ALTER TABLE tellers ADD CONSTRAINT tellers_pk PRIMARY KEY (tid)");
    execute("ALTER TABLE accounts ADD CONSTRAINT accounts_pk PRIMARY KEY (aid)");
}

function createIndexPostgreSQL() {
    createIndexOracle();
}

function createForeignKeyOracle() {
    info("Creating foreign keys ...");
    
    execute("ALTER TABLE tellers ADD CONSTRAINT tellers_fk1 "
        + "FOREIGN KEY (bid) REFERENCES branches (bid)");
    
    execute("ALTER TABLE accounts ADD CONSTRAINT accounts_fk1 "
        + "FOREIGN KEY (bid) REFERENCES branches (bid)");
    
    execute("ALTER TABLE history ADD CONSTRAINT history_fk1 "
        + "FOREIGN KEY (tid) REFERENCES tellers (tid)");
    
    execute("ALTER TABLE history ADD CONSTRAINT history_fk2 "
        + "FOREIGN KEY (bid) REFERENCES branches (bid)");
    
    execute("ALTER TABLE history ADD CONSTRAINT history_fk3 "
        + "FOREIGN KEY (aid) REFERENCES accounts (aid)");
}

function createForeignKeyPostgreSQL() {
    createForeignKeyOracle()
}

function gatherStatsOracle() {
    info("Analyzing tables ...");
    
    execute("BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(ownname => NULL); END;");
}

function gatherStatsPostgreSQL() {
    info("Vacuuming and analyzing tables ...");
    
    takeConnection().setAutoCommit(true);
    execute("VACUUM ANALYZE branches");
    execute("VACUUM ANALYZE tellers");
    execute("VACUUM ANALYZE accounts");
    execute("VACUUM ANALYZE history");
    takeConnection().setAutoCommit(false);
}

function loadBranches(branchId) {
    execute("INSERT INTO branches (bid, bbalance, filler) "
        + "VALUES ($int, 0, "
        + "'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"
        + "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb')",
        branchId);
}

function loadTellers(branchId) {
    var tid = new Array(TID_SCALE);
    var bid = new Array(TID_SCALE);
    
    for (var tellerId = TID_SCALE * (branchId - 1) + 1;
        tellerId <= TID_SCALE * branchId; tellerId++) {
        
        var index = (tellerId - 1) % TID_SCALE;
        
        tid[index] = tellerId;
        bid[index] = branchId;
    }
    
    executeBatch("INSERT INTO tellers (tid, bid, tbalance, filler) "
        + "VALUES ($int, $int, 0, "
        + "'tttttttttttttttttttttttttttttttttttttttttt"
        + "tttttttttttttttttttttttttttttttttttttttttt')",
        tid, bid);
}

function loadAccounts(branchId) {
    var aid = new Array(BATCH_SIZE);
    var bid = new Array(BATCH_SIZE);
    
    for (var accountId = AID_SCALE * (branchId - 1) + 1;
        accountId <= AID_SCALE * branchId; accountId++) {
        
        var index = (accountId - 1) % BATCH_SIZE;
        
        aid[index] = accountId;
        bid[index] = branchId;
        
        if (accountId % BATCH_SIZE == 0) {
            executeBatch("INSERT INTO accounts (aid, bid, abalance, filler) "
                + "VALUES ($int, $int, 0, "
                + "'ccccccccccccccccccccccccccccccccccccccccc"
                + "ccccccccccccccccccccccccccccccccccccccccccc')",
                aid, bid);
        }
    }
}

全体的に大きな変更はありません。
テーブルの作成・ロードについては PostgreSQL 用のものがそのまま DB2 for IBM i にも使えるので、そうしています。

        } else if (getDatabaseProductName() == "DB2 UDB for AS/400") {
            createIndexPostgreSQL();
            createForeignKeyPostgreSQL();

ベンチマークの実行

java JR scripts/tpcb_400.js -jdbcUrl jdbc:as400://hostname/TPCB; "database name"="IASPNAME";"transaction isolation"="read committed";"concurrent access resolution"="1" -jdbcDriver com.ibm.as400.access.AS400JDBCDriver -jdbcUser USRPRF -jdbcPass PASSWORD

/*
 * Tiny TPC-B 1.1
 * This script is based on TPC-B Standard Specification 2.0.
 */

// JdbcRunner settings -----------------------------------------------

// Oracle Database
// var jdbcUrl = "jdbc:oracle:thin://@localhost:1521/TPCB";

// MySQL
var jdbcUrl = "jdbc:mysql://localhost:3306/tpcb";

// PostgreSQL
// var jdbcUrl = "jdbc:postgresql://localhost:5432/tpcb";

var jdbcUser = "tpcb";
var jdbcPass = "tpcb";
//var warmupTime = 60;
//var measurementTime = 180;
var warmupTime = 6;
var measurementTime = 18;
var nAgents = 16;
var isAutoCommit = false;
var logDir = "logs";

// Application settings ----------------------------------------------

var TID_SCALE = 10;
var AID_SCALE = 100000;
var FILLER = "aaaaaaaaaaaaaaaaaaaaaa";

var scale;

// JdbcRunner functions ----------------------------------------------

function init() {
    if (getId() == 0) {
        info("Tiny TPC-B 1.1");
        
        putData("ScaleFactor", fetchAsArray("SELECT COUNT(*) FROM branches")[0][0]);
        info("Scale factor : " + Number(getData("ScaleFactor")));
        
        info("Truncating history table...");
        if (getDatabaseProductName() == "DB2 UDB for AS/400") {
            execute("DELETE FROM history");
        } else {
        execute("TRUNCATE TABLE history");
        }
        commit();
    }
}

function run() {
    if (!scale) {
        scale = Number(getData("ScaleFactor"));
    }
    
    var tid = random(1, TID_SCALE * scale);
    var bid = Math.floor((tid - 1) / TID_SCALE) + 1;
    var aid = 0;
    
    if (scale == 1 || random(1, 100) <= 85) {
        aid = random(AID_SCALE * (bid - 1) + 1, AID_SCALE * bid);
    } else {
        aid = random(1, AID_SCALE * (scale - 1));
        if (aid > AID_SCALE * (bid - 1)) {
            aid += AID_SCALE;
        }
    }
    
    var delta = random(-999999, 999999);

    try {
    execute("UPDATE accounts SET abalance = abalance + $int WHERE aid = $int",
        delta, aid);
    
    query("SELECT abalance FROM accounts WHERE aid = $int", aid);
    
    execute("UPDATE tellers SET tbalance = tbalance + $int WHERE tid = $int",
        delta, tid);
    
    execute("UPDATE branches SET bbalance = bbalance + $int WHERE bid = $int",
        delta, bid);
    
    execute("INSERT INTO history (tid, bid, aid, delta, mtime, filler) "
        + "VALUES ($int, $int, $int, $int, $timestamp, $string)",
        tid, bid, aid, delta, new Date(), FILLER);

    commit();

    } catch (e) {
        if (isDeadlock(e)) {
            warn("[Agent " + getId() + "] " + e.javaException + getScriptStackTrace(e));
            rollback();
        } else if (isDuplicated(e)) {
            warn("[Agent " + getId() + "] " + e.javaException + getScriptStackTrace(e));
            rollback();
        } else if (isThreadProtectError(e)) {
            // just ignore.
        } else {
            error(e + getScriptStackTrace(e))           }
      }

}

function isDeadlock(exception) {
    var javaException = exception.javaException;
    
    if (javaException instanceof java.sql.SQLException) {
        if (getDatabaseProductName() == "Oracle"
            && javaException.getErrorCode() == 60) {
            return true;
        } else if (getDatabaseProductName() == "MySQL"
            && javaException.getErrorCode() == 1213) {
            return true;
        } else if (getDatabaseProductName() == "PostgreSQL"
            && javaException.getSQLState() == "40P01") {
            return true;
        } else if (getDatabaseProductName() == "DB2 UDB for AS/400"
            && javaException.getSQLState() == "57033") {
            return true;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

function isDuplicated(exception) {
    var javaException = exception.javaException;
    
    if (javaException instanceof java.sql.SQLException) {
        if (getDatabaseProductName() == "DB2 UDB for AS/400"
            && javaException.getSQLState() == "23505") {
            return true;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

function isThreadProtectError(exception) {
    var javaException = exception.javaException;
    
    if (javaException instanceof java.sql.SQLException) {
        if (getDatabaseProductName() == "DB2 UDB for AS/400"
            && javaException.getMessage() == "Internal driver error. (class java.lang.InterruptedException)") {
            return true;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

以下の 4点が変更点です。

        if (getDatabaseProductName() == "DB2 UDB for AS/400") {
            execute("DELETE FROM history");
        } else {
        execute("TRUNCATE TABLE history");
        }
function isDeadlock(exception) {
    var javaException = exception.javaException;
    
    if (javaException instanceof java.sql.SQLException) {
        if (getDatabaseProductName() == "Oracle"
            && javaException.getErrorCode() == 60) {
            return true;
        } else if (getDatabaseProductName() == "DB2 UDB for AS/400"
            && javaException.getSQLState() == "57033") {
            return true;
        } else {
            return false;
        }
    } else {
        return false;
    }
}
function isDuplicated(exception) {
    var javaException = exception.javaException;
    
    if (javaException instanceof java.sql.SQLException) {
        if (getDatabaseProductName() == "DB2 UDB for AS/400"
            && javaException.getSQLState() == "23505") {
            return true;
        } else {
            return false;
        }
    } else {
        return false;
    }
function isThreadProtectError(exception) {
    var javaException = exception.javaException;
    
    if (javaException instanceof java.sql.SQLException) {
        if (getDatabaseProductName() == "DB2 UDB for AS/400"
            && javaException.getMessage() == "Internal driver error. (class java.lang.InterruptedException)") {
            return true;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

[Top Pageに戻る]

Ads by TOK2