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

前回JdbcRunner というデータベース負荷テストツールの基本的な使い方を、DB2 for IBM i を題材にして見てみました。

今回は SysBench という”テストキット”について、DB2 for IBM i での動かし方を見ていきたいと思います。


データのロード

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

CREATE SCHEMA SBTEST

を実行してスキーマは作成しておいてください。
データのロードは↓のスクリプトで行われます。

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

(今回はユーザーとパスワードもここで指定するようにしてみました)

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

/*
 * Tiny SysBench 1.0 - data loader
 * This script is based on SysBench 0.4.12.
 *
 * [Oracle Database]
 * shell> sqlplus "/ AS SYSDBA"
 * sql> CREATE USER sbtest IDENTIFIED BY sbtest;
 * sql> GRANT connect, resource TO sbtest;
 *
 * [MySQL]
 * shell> mysql -u root [-p]
 * sql> CREATE DATABASE sbtest;
 * sql> GRANT ALL PRIVILEGES ON sbtest.* TO sbtest@'%' IDENTIFIED BY 'sbtest';
 *
 * [PostgreSQL]
 * shell> psql -U postgres
 * sql> CREATE DATABASE sbtest;
 * sql> CREATE USER sbtest PASSWORD 'sbtest';
 *
 * <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/SBTEST";

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

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

var jdbcUser = "sbtest";
var jdbcPass = "sbtest";
var isLoad = true;
var isAutoCommit = false;
var logDir = "logs";

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

var BATCH_SIZE = 100;
var COMMIT_SIZE = 1000;

var oltpTableSize;

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

function init() {
    if (getId() == 0) {
        info("Tiny SysBench 1.0 - data loader");
        info("-param0 : Number of records (default : 10000)");
        
        oltpTableSize = param0;
        
        if (oltpTableSize == 0) {
            oltpTableSize = 10000;
        }
        
        info("Number of records : " + oltpTableSize);
        
        if (getDatabaseProductName() == "Oracle") {
            dropTableOracle();
            createTableOracle();
        } else if (getDatabaseProductName() == "MySQL") {
            dropTableMySQL();
            createTableMySQL();
        } else if (getDatabaseProductName() == "PostgreSQL") {
            dropTablePostgreSQL();
            createTablePostgreSQL();
        } else if (getDatabaseProductName() == "DB2 UDB for AS/400") {
            dropTableDB2i();
            createTableDB2i();
        } else {
            error(getDatabaseProductName() + " is not supported yet.");
        }
        
        commit();
    }
}

function run() {
    if (getId() == 0) {
        info("Loading sbtest ...");
        
        var k = new Array();
        var c = new Array();
        var pad = new Array();
        
        for (var count = 1; count <= oltpTableSize; count++) {
            k.push(0);
            c.push(" ");
            pad.push("qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt");
            
            if (count % BATCH_SIZE == 0) {
                executeBatch("INSERT INTO sbtest (k, c, pad) VALUES ($int, $string, $string)",
                    k, c, pad);
                
                k.length = 0;
                c.length = 0;
                pad.length = 0;
                
                if (count % COMMIT_SIZE == 0) {
                    commit();
                    info("sbtest : " + count + " / " + oltpTableSize);
                }
            }
        }
        
        if (oltpTableSize % COMMIT_SIZE != 0) {
            if (oltpTableSize % BATCH_SIZE != 0) {
                executeBatch("INSERT INTO sbtest (k, c, pad) VALUES ($int, $string, $string)",
                    k, c, pad);
            }
            
            commit();
            info("sbtest : " + oltpTableSize + " / " + oltpTableSize);
        }
    }
    
    setBreak();
}

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

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

function dropTableOracle() {
    info("Dropping a table ...");
    
    try {
        execute("DROP TABLE sbtest");
        execute("DROP SEQUENCE sbtest_seq");
    } catch (e) {
        warn(e);
    }
}

function dropTableMySQL() {
    info("Dropping a table ...");
    
    try {
        execute("DROP TABLE sbtest");
    } catch (e) {
        warn(e);
        rollback(); // PostgreSQL requires a rollback.
    }
}

function dropTablePostgreSQL() {
    dropTableMySQL();
}

function dropTableDB2i() {
    dropTableMySQL();
}

function createTableOracle() {
    info("Creating a table ...");
    
    execute("CREATE TABLE sbtest ("
        + "id NUMBER, "
        + "k NUMBER DEFAULT 0 NOT NULL, "
        + "c CHAR(120) DEFAULT '' NOT NULL, "
        + "pad CHAR(60) DEFAULT '' NOT NULL)");
    
    execute("CREATE SEQUENCE sbtest_seq");
    
    var statement;
    
    try {
        statement = takeConnection().createStatement();
        
        statement.executeUpdate("CREATE TRIGGER sbtest_trig "
            + "BEFORE INSERT ON sbtest FOR EACH ROW "
            + "BEGIN "
                + "IF :NEW.id IS NULL THEN "
                    + "SELECT sbtest_seq.NEXTVAL INTO :NEW.id FROM DUAL; "
                + "END IF; "
            + "END;");
        
    } finally {
        try {
            statement.close();
        } catch (e) {
            warn(e);
        }
    }
}

function createTableMySQL() {
    info("Creating a table ...");
    
    execute("CREATE TABLE sbtest ("
        + "id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, "
        + "k INT UNSIGNED DEFAULT 0 NOT NULL, "
        + "c CHAR(120) DEFAULT '' NOT NULL, "
        + "pad CHAR(60) DEFAULT '' NOT NULL, "
        + "KEY k (k)) "
        + "ENGINE = InnoDB");
}

function createTablePostgreSQL() {
    info("Creating a table ...");
    
    execute("CREATE TABLE sbtest ("
        + "id SERIAL, "
        + "k INTEGER DEFAULT 0 NOT NULL, "
        + "c CHAR(120) DEFAULT '' NOT NULL, "
        + "pad CHAR(60) DEFAULT '' NOT NULL)");
}

function createTableDB2i() {
    info("Creating a table ...");
    
    execute("CREATE TABLE sbtest ("
        + "id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, "
        + "k INTEGER DEFAULT 0 NOT NULL, "
        + "c CHAR(120) DEFAULT '' NOT NULL, "
        + "pad CHAR(60) DEFAULT '' NOT NULL)");
}

function createIndexOracle() {
    info("Creating indexes ...");
    
    execute("ALTER TABLE sbtest ADD CONSTRAINT sbtest_pk PRIMARY KEY (id)");
    execute("CREATE INDEX sbtest_ix1 ON sbtest (k)");
}

function createIndexPostgreSQL() {
    createIndexOracle();
}

function createIndexDB2i() {
    createIndexOracle();
}

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

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

DB2 for IBM i の場合、Oracle のような SEQUENCE も使えますし、MySQL のような AUTO_INCREMENT (に相当するもの)も使えます。

今回は後者を使ってみました。

function createTableDB2i() {
    info("Creating a table ...");
    
    execute("CREATE TABLE sbtest ("
        + "id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, "
        + "k INTEGER DEFAULT 0 NOT NULL, "
        + "c CHAR(120) DEFAULT '' NOT NULL, "
        + "pad CHAR(60) DEFAULT '' NOT NULL)");
}

単に、こちらの方がシンプルだと思っただけで、あまり深い意味はありません。

また、DB2 for IBM i の場合、ここで取得しているような統計は自動的に取得されるので、gatherStatsDB2 といったようなものは必要ありません。

ベンチマークの実行

↓のように実行します。

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

transaction isolation や concurrent access resolution などのオプションはいろいろ変えて実行してみてください。

たとえば、concurrent access resolution を 2 から 1 に変更して実行してみると変化があることがわかります。

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

/*
 * Tiny SysBench 1.0
 * This script is based on SysBench 0.4.12.
 */

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

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

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

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

var jdbcUser = "sbtest";
var jdbcPass = "sbtest";
//var warmupTime = 60;
//var measurementTime = 180;
//var nAgents = 16;
var warmupTime = 6;
var measurementTime = 8;
var nAgents = 1;
var stmtCacheSize = 20;
var isAutoCommit = false;
var logDir = "logs";

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

var DIST_UNIFORM = 1;
var DIST_GAUSSIAN = 2;
var DIST_SPECIAL = 3;

// Number of records in the test table
var oltpTableSize;

// Ratio of queries in a transaction
var oltpPointSelects = 10;
var oltpSimpleRanges = 1;
var oltpSumRanges = 1;
var oltpOrderRanges = 1;
var oltpDistinctRanges = 1;
var oltpIndexUpdates = 1;
var oltpNonIndexUpdates = 1;

// Read-only flag
var oltpReadOnly = false;
//var oltpReadOnly = true;

// Range size for range queries
var oltpRangeSize = 100;

// Parameters for random numbers distribution
var oltpDistType = DIST_SPECIAL;
var oltpDistIter = 12;
var oltpDistPct = 1;
var oltpDistRes = 75;

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

function init() {
    if (getId() == 0) {
        info("Tiny SysBench 1.0");
        
        putData("OltpTableSize", fetchAsArray("SELECT COUNT(*) FROM sbtest")[0][0]);
        info("Number of records : " + Number(getData("OltpTableSize")));
    }
}

function run() {
    if (!oltpTableSize) {
        oltpTableSize = Number(getData("OltpTableSize"));
    }
    
    oltpExecuteRequest();
}

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

function oltpExecuteRequest() {
    var retry = false;
    
    do {
        retry = false;
        
        try {
            // Point selects
            for (var count = 0; count < oltpPointSelects; count++) {
                var id = getRandomId();
                
                query("SELECT c FROM sbtest WHERE id = $int", id);
            }
            
            // Simple ranges
            for (var count = 0; count < oltpSimpleRanges; count++) {
                var from = getRangeRandomId();
                var to = from + oltpRangeSize - 1;
                
                query("SELECT c FROM sbtest WHERE id BETWEEN $int AND $int", from, to);
            }
            
            // Sum ranges
            for (var count = 0; count < oltpSumRanges; count++) {
                var from = getRangeRandomId();
                var to = from + oltpRangeSize - 1;
                
                query("SELECT SUM(k) FROM sbtest WHERE id BETWEEN $int AND $int", from, to);
            }
            
            // Order ranges
            for (var count = 0; count < oltpOrderRanges; count++) {
                var from = getRangeRandomId();
                var to = from + oltpRangeSize - 1;
                
                query("SELECT c FROM sbtest WHERE id BETWEEN $int AND $int ORDER BY c", from, to);
            }
            
            // Distinct ranges
            for (var count = 0; count < oltpDistinctRanges; count++) {
                var from = getRangeRandomId();
                var to = from + oltpRangeSize - 1;
                
                query("SELECT DISTINCT c FROM sbtest WHERE id BETWEEN $int AND $int ORDER BY c",
                    from, to);
            }
            
            if (!oltpReadOnly) {
                // Index updates
                for (var count = 0; count < oltpIndexUpdates; count++) {
                    var id = getRandomId();
                    
                    execute("UPDATE sbtest SET k = k + 1 WHERE id = $int", id);
                }
                
                // Non index updates
                for (var count = 0; count < oltpNonIndexUpdates; count++) {
                    var c = getRandomString();
                    var id = getRandomId();
                    
                    execute("UPDATE sbtest SET c = $string WHERE id = $int", c, id);
                }
                
                // Delete and insert
                var deletedCount = 0;
                var id = getRandomId();
                
                //do {
                   // PostgreSQL may fail to delete the row.
                   // We will retry it if such a situation occurs.
                   //deletedCount = execute("DELETE FROM sbtest WHERE id = $int", id);
                //} while (deletedCount == 0);        

                deletedCount = execute("DELETE FROM sbtest WHERE id = $int", id);

                // execute("INSERT INTO sbtest (id, k, c, pad) VALUES ($int, 0, ' ', "
                //     + "'aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')", id);

                // "id" column defined as AUTO-GENERATED
                execute("INSERT INTO sbtest (k, c, pad) VALUES (0, ' ', "
                    + "'aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')");

            }
            
            // Commit
            commit();
            
        } catch (e) {
            if (isDeadlock(e)) {
                warn("[Agent " + getId() + "] Deadlock detected.");
                rollback();
                retry = true;
            } else if (isDeleted(e)) {
                warn("[Agent " + getId() + "] Already Deleted.");
                rollback();
                retry = true;
            } else if (isThreadProtectError(e)) {
                // just ignore.
            } else {
                error(e + getScriptStackTrace(e));
            }
        }
    } while (retry);
}

function getRandomString() {
    return "" + sbRnd() + "-" + sbRnd() + "-" + sbRnd() + "-" + sbRnd() + "-" + sbRnd()
        + "-" + sbRnd() + "-" + sbRnd() + "-" + sbRnd() + "-" + sbRnd() + "-" + sbRnd();
}

function getRangeRandomId() {
    var id = getRandomId();
    
    if (id + oltpRangeSize > oltpTableSize) {
        id = oltpTableSize - oltpRangeSize;
    }
    
    if (id < 1) {
        id = 1;
    }
    
    return id;
}

function getRandomId() {
    var r = 0;
    
    switch (oltpDistType) {
        case DIST_UNIFORM:
            r = rndFuncUniform();
            break;
        case DIST_GAUSSIAN:
            r = rndFuncGaussian();
            break;
        case DIST_SPECIAL:
            r = rndFuncSpecial();
            break;
        default:
            r = 0;
    }
    
    return r;
}

function rndFuncUniform() {
    return 1 + sbRnd() % oltpTableSize;
}

function rndFuncGaussian() {
    var sum = 0;
    
    for (var i = 0; i < oltpDistIter; i++) {
        sum += 1 + sbRnd() % oltpTableSize;
    }
    
    return Math.floor(sum / oltpDistIter);
}

function rndFuncSpecial() {
    var sum = 0;
    var d = 0;
    var res = 0;
    var rangeSize = 0;
    
    if (oltpTableSize == 0) {
        return 0;
    }
    
    rangeSize = oltpTableSize * Math.floor(100 / (100 - oltpDistRes));
    res = 1 + sbRnd() % rangeSize;
    
    if (res <= oltpTableSize) {
        for (var i = 0; i < oltpDistIter; i++) {
            sum += 1 + sbRnd() % oltpTableSize;
        }
        
        return Math.floor(sum / oltpDistIter);
    }
    
    d = Math.floor(oltpTableSize * oltpDistPct / 100);
    
    if (d < 1) {
        d = 1;
    }
    
    res %= d;
    res += Math.floor(oltpTableSize / 2) - Math.floor(oltpTableSize * oltpDistPct / 200);
    
    return res;
}

function sbRnd() {
    return random(0, 1073741822);
}

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 isDeleted(exception) {
    var javaException = exception.javaException;
    
    if (javaException instanceof java.sql.SQLException) {
        if (getDatabaseProductName() == "DB2 UDB for AS/400"
            && javaException.getSQLState() == "02000") {
            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;
    }
}

ほとんど変更はないのですが、↓の INSERT 文だけ修正しています。

                // "id" column defined as AUTO-GENERATED
                execute("INSERT INTO sbtest (k, c, pad) VALUES (0, ' ', "
                    + "'aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')");

sbtest テーブルの id というカラムは IDENTITY 型で自動生成になっているので VALUES でセットする必要がなくなっています。これは  DB2 for IBM i だけではなく DB2 全般がそうなっていると思いますので、DB2 を使う場合の注意点ですね。

[Top Pageに戻る]

Ads by TOK2