Hatena::ブログ(Diary)

ablog このページをアンテナに追加 RSSフィード

2018-03-31

「Oracleの基本 〜データベース入門から設計/運用の初歩まで」の紹介

株式会社コーソルの 渡部さんOracle ACE)から献本いただいた「Oracleの基本 〜データベース入門から設計/運用の初歩まで」の紹介です。

一言で言うと新卒研修でデータベースの教科書として最適な本だと思います。

渡部さんとは JPOUG などのコミュニティ活動で以前から交流があり、コーソル社の社内勉強会にも何度かお邪魔したこともあります。Oracle 関連のイベントにお手伝いに来られた新卒ホヤホヤの方と挨拶したと思ったら数年後には第一線の現場で一緒に仕事させていただいたりしていましたが、ITの知識がなかった新卒や中途入社の方が数年後には一流のDBエンジニアとして第一線の現場やイベントの発表などで活躍されていて、データベース*1を勉強するには最もよい会社の一つだと思っていました。そのコーソル社のエンジニア育成で培われたノウハウが詰まった一冊だと思います。

敢えてアーキテクチャの説明を必要最低限に抑えて、Oracle Database をインストールして使いながら一通りの機能を使いながらウォークスルーする構成になっていて、コーソル社の育成の現場で培われたノウハウが詰まっていることが感じとれます。

初心者が資格取得から始めると暗記科目のようになってしまい、業務で一通り触れてからとなると1年以上はかかってしまったりしますが、本書は初心者が Oracle Database を最初に学ぶ上での高速道路になると思います。

また、ただの機能説明だけではなく著者陣の経験から得られたハマりポイントやなぜそうするかが散りばめられており、初心者ではなくても気づきがあるのではないかと思います。



オススメの読み方

Oracle Database を使ったことがない初心者の方が手を動かしながら通読するのがオススメです。ORACLE MASTER 取得や DB エンジニアを目指されているか方もまずはこの本を Oracle Database を使いながら通読するのが一番の近道ではないかと思います。


対象読者


一部引用

  • はじめに(P.3-4)

そこで,本書はOracle独自の用語の使用を最小限にとどめ,次の2点を考えながら執筆しました。

これらの解説ノウハウは,弊社コーソルの新人教育で培われたものです。コーソルの新人教育には,約2週間という短い期間で多くの新卒エンジニアORACLE MASTER Bronze DBAに合格させているという実績があります。さらに最近では,IT 未経験の新人エンジニアが,入社から2年半で,ORACLE MASTER Platinum(2日間の実技試験により認定されるORACLE MASTERの最上位資格)を取得するような例も出てきています。本書には,コーソルの新人エンジニアの生の声を反映させ,ノウハウを惜しみなく注ぎ込みました。

Oracleの基本 ?データベース入門から設計/運用の初歩まで:書籍案内|技術評論社
  • おわりに(P.353)

入門書という位置づけから、本書では、機能や使用方法に重点をおいて説明しました。よって、アーキテクチャについての説明を割愛しているところがあります。しかし、ひととおり、 Oracle を使えるようになったら、アーキテクチャにも目を向けてみてください。エンジニアであれば、「ソフトウェアがどうやって動いているか」ということに興味を持つのは自然なことですし、なにより、アーキテクチャを知っているかどうかで、理解度スキルの伸びに大きな差が出てきます。


目次

はじめに
本書について
第1章 データベースを知る
1.1 なぜデータベースは必要なのか
1.2 リレーショナルデータベースの基礎
本書の構成
第2章 Oracleを使ってみる
2.1 データベースを構築する
2.2 データベースに接続する
2.3 データベースを起動/停止する
2.4 学習用ユーザーを作成する
2.5 テーブルとデータ操作の基本
第3章 より高度なデータ操作を学ぶ
3.1 データを複雑な条件で検索する
3.2 データを加工/集計する
3.3 NULLとIS NULL条件
3.4 SELECT文とSELECT文を組み合わせる
3.5 テーブルを結合する
3.6 データの表示画面にこだわる
3.7 トランザクションでデータを安全に更新する
第4章 データをより高速に/安全に扱うしくみ
4.1 検索処理を高速化するインデックス
4.2 SELECT文をシンプルにまとめるビュー
4.3 不正なデータの混入を防ぐ制約
4.4 連番を振り出すシーケンス
4.5 セキュリティ機構の基礎となるユーザー機能
4.6 ユーザー権限を制御する
第5章 テーブル設計の基本を知る
5.1 テーブル設計とは
5.2 第1ステップ‐概念設計
5.3 第2ステップ‐論理設計
5.4 第3ステップ‐物理設計
第6章 データベース運用/管理のポイントを押さえる
6.1 データベースにおける運用/管理の重要性
6.2 バックアップを取ってデータを守る
6.3 データベースのメンテナンス
6.4 データベースを監視する
6.5 ネットワーク環境/本番環境でOracleに接続する
6.6 トラブルに立ち向かうためには
おわりに
索引
著者略歴
監修者略歴

*1データベースを勉強するなら Oracle Database から入るのがオススメです

2018-03-10

MySQL InnoDB で大きなトランザクションを見つける

SHOW ENGINE INNODB STATUS の

で、History list length が大きいとMySQLインスタンス全体での UNDO のサイズが大きくなりロールバックにかかる時間が長くなっており、undo log entries が大きいトランザクションUNDO ページを大量に使っている犯人だと類推できる。undo log entries は insert/update/delete などを行った行数(未コミット)でサイズではない。

MariaDB 5.5.27以降10未満(XtraDB)では Information Schema の INNODB_UNDO_LOGS. SIZE で undo log が使っているサイズを確認できる。


  • テーブルを作成する
> create table undo_test(
	col1 char(255),
	col2 char(255),
	col3 char(255),
	col4 char(255),
	col5 char(255)
);
  • 1行 insert する
> start transaction;
> insert into undo_test(col1) values('1');
> show engine innodb stauts\G
(中略)
------------
TRANSACTIONS
------------
TRANSACTIONS
------------
Trx id counter 111207046
Purge done for trx's n:o < 111206999 undo n:o < 0 state: running but idle
History list length 719 ★ history list length がインスタンス全体のUNDOページ数
LIST OF TRANSACTIONS FOR EACH SESSION:
(中略)
---TRANSACTION 111207036, ACTIVE 2 sec ★2秒経過
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1 ★1行insertしたので、undo log が 1になっている
MySQL thread id 8697(★スレッドID), OS thread handle 0x2b9eb4b47700, query id 1400669 27.0.3.145(★接続元IP) awsuser(★ユーザー名) delayed send ok done
  • 5行 insert する
> start transaction;
> insert into undo_test(col1) values('2');
> insert into undo_test(col1) values('3');
> insert into undo_test(col1) values('4');
> insert into undo_test(col1) values('5');
  • 行数を確認する
> select count(1) from undo_test;
+----------+
| count(1) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)
> show engine innodb stauts\G
(中略)
------------
TRANSACTIONS
------------
Trx id counter 111207337
Purge done for trx's n:o < 111206999 undo n:o < 0 state: running but idle
History list length 808
LIST OF TRANSACTIONS FOR EACH SESSION:
(中略)
---TRANSACTION 111207036, ACTIVE 58 sec ★58秒経過
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 5 ★5行insertしたので、undo log が 5 になっている
MySQL thread id 8697, OS thread handle 0x2b9ef9e43700, query id 1400956 27.0.3.145 awsuser cleaned up
Trx read view will not see trx with id >= 111207290, sees < 111207036
> call mysql.rds_kill(8697); ★show engine innodb stauts の TRANSACTIONS に表示されていた MySQL thread id 
> show engine innodb status\G
(中略)
------------
TRANSACTIONS
------------
Trx id counter 111208582
Purge done for trx's n:o < 111206999 undo n:o < 0 state: running but idle
History list length 726
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 111208485, not started
MySQL thread id 8719, OS thread handle 0x2b9eb4a43700, query id 1402459 localhost rdsadmin cleaned up
---TRANSACTION 111205044, not started
MySQL thread id 8664, OS thread handle 0x2b9eb4bc9700, query id 1402491 27.0.3.145 awsuser System lock
show engine innodb status
---TRANSACTION 111208058, not started
MySQL thread id 11, OS thread handle 0x2b9eb37ed700, query id 1401866 localhost rdsadmin delayed send ok done
---TRANSACTION 111208576, not started
MySQL thread id 2, OS thread handle 0x2b9eb4a43700, query id 1402490 localhost rdsadmin delayed send ok done
トランザクションがなくなっている

参考

Global history and purge operations

In addition to every record having a reference to its previous version, there is also a global view of the history of the entire database, called the “history list”. As each transaction is committed, its history is linked into this global history list in transaction serialization (commit) order. The history list is used primarily for cleaning up after a transaction, once no existing read view still needs its history (all other transactions have completed).

In the background, InnoDB runs a continuous “purge” process which is responsible for two things:

  • Actually deleting delete-marked records2, if the current version of the record in the index at the time of purge is still delete-marked and bears the same transaction ID. (That is, the record hasn’t been re-inserted.)
  • Freeing undo log pages and unlinking them from the global history list to make them available for re-use.

InnoDB exposes the total amount of history present in the system as a “History list length”, which can be seen in SHOW ENGINE INNODB STATUS. This is the count of all database modifications present in the undo logs, in units of undo logs (which may contain a single record modification or many).

The basics of the InnoDB undo logging and history system – Jeremy Cole

XtraDB は Information Schema の INNODB_UNDO_LOGS. SIZE(Size in pages of the segment) で undo log が使っているサイズが分かる。MariaDB 5.5.27 で入って、10では削除されてる。

Information Schema INNODB_UNDO_LOGS Table

The Information Schema INNODB_UNDO_LOGS table is a Percona enchancement, and is only available for XtraDB, not InnoDB (see XtraDB and InnoDB). It contains information about the the InnoDB undo log, with each record being an undo log segment. It was removed in MariaDB 10.0.

It has the following columns:

  • ColumnDescription
  • TRX_ID: Unique transaction ID number, matching the value from the information_schema.INNODB_TRX table.
  • RSEG_ID: Rollback segment ID, matching the value from the information_schema.INNODB_RSEG table.
  • USEG_ID: Undo segment ID.
  • SEGMENT_TYPE: Indicates the operation type, for example INSERT or UPDATE.
  • STATE: Segment state: one of ACTIVE (contains active transaction undo log), CACHED, TO_FREE (insert undo segment can be freed), TO_PURGE (update undo segment won't be re-used and can be purged when all undo data is removed) or PREPARED (segment of a prepared transaction).
  • SIZE: Size in pages of the segment.
Information Schema INNODB_UNDO_LOGS Table - MariaDB Knowledge Base

ソースコード

  • mysql-5.6.10/storage/innobase/lock/lock0lock.cc
/*********************************************************************//**
Prints info of locks for all transactions.
@return FALSE if not able to obtain lock mutex
and exits without printing info */
UNIV_INTERN
ibool
lock_print_info_summary(
/*====================*/
        FILE*   file,   /*!< in: file where to print */
        ibool   nowait) /*!< in: whether to wait for the lock mutex */
{
        /* if nowait is FALSE, wait on the lock mutex,
        otherwise return immediately if fail to obtain the
        mutex. */
        if (!nowait) {
                lock_mutex_enter();
        } else if (lock_mutex_enter_nowait()) {
                fputs("FAIL TO OBTAIN LOCK MUTEX, "
                      "SKIP LOCK INFO PRINTING\n", file);
                return(FALSE);
        }

        if (lock_deadlock_found) {
                fputs("------------------------\n"
                      "LATEST DETECTED DEADLOCK\n"
                      "------------------------\n", file);

                if (!srv_read_only_mode) {
                        ut_copy_file(file, lock_latest_err_file);
                }
        }

        fputs("------------\n"
              "TRANSACTIONS\n"
              "------------\n", file);

        fprintf(file, "Trx id counter " TRX_ID_FMT "\n",
                trx_sys_get_max_trx_id());

        fprintf(file,
                "Purge done for trx's n:o < " TRX_ID_FMT
                " undo n:o < " TRX_ID_FMT " state: ",
                purge_sys->iter.trx_no,
                purge_sys->iter.undo_no);

        /* Note: We are reading the state without the latch. One because it
        will violate the latching order and two because we are merely querying
        the state of the variable for display. */

        switch (purge_sys->state){
        case PURGE_STATE_EXIT:
        case PURGE_STATE_INIT:
                /* Should never be in this state while the system is running. */
                ut_error;

        case PURGE_STATE_DISABLED:
                fprintf(file, "disabled");
                break;

        case PURGE_STATE_RUN:
                fprintf(file, "running");
                /* Check if it is waiting for more data to arrive. */
                if (!purge_sys->running) {
                        fprintf(file, " but idle");
                }
                break;

        case PURGE_STATE_STOP:
                fprintf(file, "stopped");
                break;
        }

        fprintf(file, "\n");

        fprintf(file,
                "History list length %lu\n",
                (ulong) trx_sys->rseg_history_len);

#ifdef PRINT_NUM_OF_LOCK_STRUCTS
        fprintf(file,
                "Total number of lock structs in row lock hash table %lu\n",
                (ulong) lock_get_n_rec_locks());
#endif /* PRINT_NUM_OF_LOCK_STRUCTS */
        return(TRUE);
}
  • mysql-5.6.10/storage/innobase/trx/trx0trx.cc
/**********************************************************************//**
Prints info about a transaction.
Caller must hold trx_sys->mutex. */
UNIV_INTERN
void
trx_print_low(
/*==========*/
        FILE*           f,
                        /*!< in: output stream */
        const trx_t*    trx,
                        /*!< in: transaction */
        ulint           max_query_len,
                        /*!< in: max query length to print,
                        or 0 to use the default max length */
        ulint           n_rec_locks,
                        /*!< in: lock_number_of_rows_locked(&trx->lock) */
        ulint           n_trx_locks,
                        /*!< in: length of trx->lock.trx_locks */
        ulint           heap_size)
                        /*!< in: mem_heap_get_size(trx->lock.lock_heap) */
{
        ibool           newline;
        const char*     op_info;

        ut_ad(mutex_own(&trx_sys->mutex));

        fprintf(f, "TRANSACTION " TRX_ID_FMT, trx->id);

        /* trx->state cannot change from or to NOT_STARTED while we
        are holding the trx_sys->mutex. It may change from ACTIVE to
        PREPARED or COMMITTED. */
        switch (trx->state) {
        case TRX_STATE_NOT_STARTED:
                fputs(", not started", f);
                goto state_ok;
        case TRX_STATE_ACTIVE:
                fprintf(f, ", ACTIVE %lu sec",
                        (ulong) difftime(time(NULL), trx->start_time));
                goto state_ok;
        case TRX_STATE_PREPARED:
                fprintf(f, ", ACTIVE (PREPARED) %lu sec",
                        (ulong) difftime(time(NULL), trx->start_time));
                goto state_ok;
        case TRX_STATE_COMMITTED_IN_MEMORY:
                fputs(", COMMITTED IN MEMORY", f);
                goto state_ok;
        }
        fprintf(f, ", state %lu", (ulong) trx->state);
        ut_ad(0);
state_ok:

        /* prevent a race condition */
        op_info = trx->op_info;

        if (*op_info) {
                putc(' ', f);
                fputs(op_info, f);
        }

        if (trx->is_recovered) {
                fputs(" recovered trx", f);
        }

        if (trx->declared_to_be_inside_innodb) {
                fprintf(f, ", thread declared inside InnoDB %lu",
                        (ulong) trx->n_tickets_to_enter_innodb);
        }

        putc('\n', f);

        if (trx->n_mysql_tables_in_use > 0 || trx->mysql_n_tables_locked > 0) {
                fprintf(f, "mysql tables in use %lu, locked %lu\n",
                        (ulong) trx->n_mysql_tables_in_use,
                        (ulong) trx->mysql_n_tables_locked);
        }

        newline = TRUE;

        /* trx->lock.que_state of an ACTIVE transaction may change
        while we are not holding trx->mutex. We perform a dirty read
        for performance reasons. */

        switch (trx->lock.que_state) {
        case TRX_QUE_RUNNING:
                newline = FALSE; break;
        case TRX_QUE_LOCK_WAIT:
                fputs("LOCK WAIT ", f); break;
        case TRX_QUE_ROLLING_BACK:
                fputs("ROLLING BACK ", f); break;
        case TRX_QUE_COMMITTING:
                fputs("COMMITTING ", f); break;
        default:
                fprintf(f, "que state %lu ", (ulong) trx->lock.que_state);
        }

        if (n_trx_locks > 0 || heap_size > 400) {
                newline = TRUE;

                fprintf(f, "%lu lock struct(s), heap size %lu,"
                        " %lu row lock(s)",
                        (ulong) n_trx_locks,
                        (ulong) heap_size,
                        (ulong) n_rec_locks);
        }

        if (trx->has_search_latch) {
                newline = TRUE;
                fputs(", holds adaptive hash latch", f);
        }

        if (trx->undo_no != 0) {
                newline = TRUE;
                fprintf(f, ", undo log entries "TRX_ID_FMT, trx->undo_no);
        }

        if (newline) {
                putc('\n', f);
        }

        if (trx->mysql_thd != NULL) {
                innobase_mysql_print_thd(f, trx->mysql_thd, max_query_len);
        }
}
  • mysql-5.6.10/storage/innobase/handler/ha_innodb.cc
/************************************************************************//**
Implements the SHOW ENGINE INNODB STATUS command. Sends the output of the
InnoDB Monitor to the client.
@return 0 on success */
static
int
innodb_show_status(
/*===============*/
        handlerton*     hton,   /*!< in: the innodb handlerton */
        THD*            thd,    /*!< in: the MySQL query thread of the caller */
        stat_print_fn*  stat_print)
{
        trx_t*                  trx;
        static const char       truncated_msg[] = "... truncated...\n";
        const long              MAX_STATUS_SIZE = 1048576;
        ulint                   trx_list_start = ULINT_UNDEFINED;
        ulint                   trx_list_end = ULINT_UNDEFINED;

        DBUG_ENTER("innodb_show_status");
        DBUG_ASSERT(hton == innodb_hton_ptr);

        /* We don't create the temp files or associated
        mutexes in read-only-mode */

        if (srv_read_only_mode) {
                DBUG_RETURN(0);
        }

        trx = check_trx_exists(thd);

        trx_search_latch_release_if_reserved(trx);

        innobase_srv_conc_force_exit_innodb(trx);

        /* We let the InnoDB Monitor to output at most MAX_STATUS_SIZE
        bytes of text. */

        char*   str;
        ssize_t flen, usable_len;

        mutex_enter(&srv_monitor_file_mutex);
        rewind(srv_monitor_file);

        srv_printf_innodb_monitor(srv_monitor_file, FALSE,
                                  &trx_list_start, &trx_list_end);

        os_file_set_eof(srv_monitor_file);

        if ((flen = ftell(srv_monitor_file)) < 0) {
                flen = 0;
        }

        if (flen > MAX_STATUS_SIZE) {
                usable_len = MAX_STATUS_SIZE;
                srv_truncated_status_writes++;
        } else {
                usable_len = flen;
        }

        /* allocate buffer for the string, and
        read the contents of the temporary file */

        if (!(str = (char*) my_malloc(usable_len + 1, MYF(0)))) {
                mutex_exit(&srv_monitor_file_mutex);
                DBUG_RETURN(1);
        }

        rewind(srv_monitor_file);

        if (flen < MAX_STATUS_SIZE) {
                /* Display the entire output. */
                flen = fread(str, 1, flen, srv_monitor_file);
        } else if (trx_list_end < (ulint) flen
                   && trx_list_start < trx_list_end
                   && trx_list_start + (flen - trx_list_end)
                   < MAX_STATUS_SIZE - sizeof truncated_msg - 1) {

                /* Omit the beginning of the list of active transactions. */
                ssize_t len = fread(str, 1, trx_list_start, srv_monitor_file);

                memcpy(str + len, truncated_msg, sizeof truncated_msg - 1);
                len += sizeof truncated_msg - 1;
                usable_len = (MAX_STATUS_SIZE - 1) - len;
                fseek(srv_monitor_file, flen - usable_len, SEEK_SET);
                len += fread(str + len, 1, usable_len, srv_monitor_file);
                flen = len;
        } else {
                /* Omit the end of the output. */
                flen = fread(str, 1, MAX_STATUS_SIZE - 1, srv_monitor_file);
        }

        mutex_exit(&srv_monitor_file_mutex);

        stat_print(thd, innobase_hton_name, (uint) strlen(innobase_hton_name),

        my_free(str);

        DBUG_RETURN(0);
}

参考

2018-03-04

sysbench で MySQL のベンチマークをとる

sysbench をインストールして MySQLベンチマークをとる手順をメモ。


インストール

$ curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
$ sudo yum -y install sysbench mysql

ベンチマーク

  • 初期データロード
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=100000 \
 --mysql-host=aurora01.*********.ap-northeast-1.rds.amazonaws.com \
 --mysql-user=awsuser \
 --mysql-password=********* \
 --mysql-db=mydb \
 --db-ps-mode=disable \
 prepare
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=100000 \
 --mysql-db=mydb \
 --mysql-host=aurora01.*********.ap-northeast-1.rds.amazonaws.com \
 --mysql-user=awsuser \
 --mysql-password=********* \
 --time=300 \
 --db-ps-mode=disable \
 --threads=16 \
 run

モニタリングする

> use mydb;
> set autocommit=0;
> select @@global.autocommit,@@session.autocommit;
+---------------------+----------------------+
| @@global.autocommit | @@session.autocommit |
+---------------------+----------------------+
|                   1 |                    0 |
+---------------------+----------------------+
1 row in set (0.02 sec)

>  update members set name = 'aze';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

> select * from information_schema.innodb_trx;
  • History list length を確認する。
> pager egrep -A 5 '^TRANSACTIONS'
> show engine innodb status\G
TRANSACTIONS
------------
Trx id counter 70715803
Purge done for trx's n:o < 70591422 undo n:o < 0 state: running but idle
History list length 666
LIST OF TRANSACTIONS FOR EACH SESSION:
> show binary logs;
(中略)
| mysql-bin-changelog.000149 | 134264241 |
| mysql-bin-changelog.000150 | 134218252 |
| mysql-bin-changelog.000151 | 134218203 |
| mysql-bin-changelog.000152 |  24293043 |
| mysql-bin-changelog.000153 |  51085842 |
| mysql-bin-changelog.000154 | 108366756 |
| mysql-bin-changelog.000155 | 134218418 |
| mysql-bin-changelog.000156 |  18849396 |
| mysql-bin-changelog.000157 |       120 |
+----------------------------+-----------+
153 rows in set (0.02 sec)


参考

2018-03-03

Oracle Database のインスタンスリカバリではオープンしてからロールバックする

Oracle Database のインスタンスリカバリはデータベースがオープンして使える状態になった後にバックグラウンドでロールバックが行われる。トランザクションロールバックが完了していないブロックを変更し用とした場合はフォアグラウンドでロールバックが行われる。


  • Chapter 11. Oracle and High Availability
    • PHASES OF INSTANCE RECOVERY

Oracle opens the database after the roll forward phase of recovery and performs the rollback of uncommitted transactions in the background in what is called deferred rollback. This process reduces database downtime and helps to reduce the variability of recovery times. If a user’s transaction begins working in a database block that contains some changes left behind by an uncommitted transaction, the user’s transaction will trigger a foreground rollback to undo the changes and will then proceed when rollback is complete. This action is transparent to the user—they don’t receive error messages or have to resubmit the transaction.

MySQL のバイナリログとInnoDB ログ

MySQLバイナリログはメディアリカバリに使うもので、ディスク障害などの際に mysqldump でエクスポートしておいたデータをインポートしてバイナリログでロールフォーワードする。Oracle Database で言うと、 インポートがリストアで、バイナリログでのロールフォーワードがアーカイブログとREDOログを使ったロールフォーワードに当たる。Oracle Database が物理的なブロックレベルで行う宇野に対して、MySQL は論理的にSQLベースで行う点が異なる。

InnoDBログはインスタンスダウンした時にクラッシュリカバリでロールフォーワードに使われる(ロールバックにはUNDOログが使われる)。Oracle Database がREDOログでロールフォーワードしてUNDO表領域のロールバックセグメントでロールバックするのと同じ。



バイナリログを使っている場合、--innodb_support_xa を 1 に設定していると、InnoDBログとバイナリログの一貫性が保証される。sync_binlog が 1 の場合、クラッシュリカバリ時にバイナリログを走査して truncate して、マスターでロールバックしたトランザクションバイナリログから削除するようだ。これが残っていると、マスターでDMLは発行されたけどロールバックされたトランザクションがスレーブに連携されて永遠にコミットされないトランザクションになるからではないかと思う。

For example, if you are using InnoDB tables and the MySQL server processes a COMMIT statement, it writes many prepared transactions to the binary log in sequence, synchronizes the binary log, and then commits this transaction into InnoDB. If the server crashes between those two operations, the transaction is rolled back by InnoDB at restart but still exists in the binary log. Such an issue is resolved assuming --innodb_support_xa is set to 1, the default. Although this option is related to the support of XA transactions in InnoDB, it also ensures that the binary log and InnoDB data files are synchronized. For this option to provide a greater degree of safety, the MySQL server should also be configured to synchronize the binary log and the InnoDB logs to disk before committing the transaction. The InnoDB logs are synchronized by default, and sync_binlog=1 can be used to synchronize the binary log. The effect of this option is that at restart after a crash, after doing a rollback of transactions, the MySQL server scans the latest binary log file to collect transaction xid values and calculate the last valid position in the binary log file. The MySQL server then tells InnoDB to complete any prepared transactions that were successfully written to the to the binary log, and truncates the binary log to the last valid position. This ensures that the binary log reflects the exact data of InnoDB tables, and therefore the slave remains in synchrony with the master because it does not receive a statement which has been rolled back.

MySQL :: MySQL 5.6 Reference Manual :: 5.4.4 The Binary Log

参考

Webエンジニアのための データベース技術[実践]入門 (Software Design plus)

Webエンジニアのための データベース技術[実践]入門 (Software Design plus)

P.155

バックアップ間隔とリカバリにかかる時間


UPDATE文であればデータファイル自体のサイズには大きな影響がないことが多いので、リストアにかかる時間は大差ありません。したがって、復旧時間の差はこのUPDATE文の実行時間の差に近くなるでしょう。1ヶ月分のUPDATE文を実行するとなると、膨大な時間がかかる可能性があることに注意が必要です。バックアップの間隔を空け過ぎていたため、バイナリログを全部当て終わるのに2日かかった、という失敗事例もありました。


http://h50146.www5.hpe.com/products/software/oe/linux/summary/reference/pdfs/MySQL-backup.pdf

SHOW ENGINE INNODB STATUS の History list length

SHOW ENGINE INNODB STATUS の History list length の見方をメモ。

mysql> show engine innodb status\G

(中略)
------------
  TRANSACTIONS
------------
Trx id counter 0 80157601
Purge done for trx's n:o <0 80154573 undo n:o <0 0 
History list length 6
Total number of lock structs in row lock hash table 0
  • Trx id counter: 現在のトランザクションID。トランザクションが発行されるとカウントアップされる。
  • Purge done for trx's n:o: このトランザクションIDまでパージ済。Trx id counter からこの値を引くと存在するMVCCの行のバージョン数になる。
  • undo n:o : パージ中のUNDOログのレコード
  • History list length: InnoDBUNDO スペースのページ数(InnoDBのデフォルトのページサイズは 8KB、Aurora MySQL は16KB)。
  • 「Trx id counter」から「Purge done for trx's n:o」を引くと、MVCC の行のバージョン数になる。

参考

High Performance MySQL: Optimization, Backups, and Replication

High Performance MySQL: Optimization, Backups, and Replication

  • Appendix B. MySQL Server Status
    • SHOW ENGINE INNODB STATUS
      • TRANSACTIONS

TRANSACTIONS

This section contains a little summary information about InnoDB transactions, followed by a list of the currently active transactions. Here are the first few lines (the header):

1  ------------
2  TRANSACTIONS
3  ------------
4  Trx id counter 0 80157601
5  Purge done for trx's n:o <0 80154573 undo n:o <0 0
6  History list length 6
7  Total number of lock structs in row lock hash table 0

The output varies depending on the MySQL version, but it includes at least the following:

  • Line 4: the current transaction identifier, which is a system variable that increments for each new transaction.
  • Line 5: the transaction ID to which InnoDB has purged old MVCC row versions. You can see how many old versions haven’t yet been purged by looking at the difference between this value and the current transaction ID. There’s no hard and fast rule as to how large this number can safely get. If nothing is updating any data, a large number doesn’t mean there’s unpurged data, because all the transactions are actually looking at the same version of the database. On the other hand, if many rows are being updated, one or more versions of each row is staying in memory. The best policy for reducing overhead is to ensure that transactions commit when they’re done instead of staying open a long time, because even an open transaction that doesn’t do any work keeps InnoDB from purging old row versions.Also in line 5: the undo log record number InnoDB’s purge process is currently working on, if any. If it’s “0 0”, as in our example, the purge process is idle.
  • Line 6: the history list length, which is the number of pages in the undo space in InnoDB’s data files. When a transaction performs updates and commits, this number increases; when the purge process removes the old versions, it decreases. The purge process also updates the value in line 5.
  • Line 7: the number of lock structs. Each lock struct usually holds many row locks, so this is not the same as the number of rows locked.

で、私がいつも見ているのはこれ。

mysql> SHOW ENGINE INNODB STATUS\G
..
------------
TRANSACTIONS
------------
Trx id counter 22588
Purge done for trx's n:o < 22588 undo n:o < 0 state: running but idle
History list length 678
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 2, OS thread handle 0x7f6bd0094700, query id 13 localhost root init
show engine innodb status
---TRANSACTION 22275, ACTIVE 1397 sec rollback
ROLLING BACK 542497 lock struct(s), heap size 64420280, 100542496 row lock(s), undo log entries 52636267
MySQL thread id 1, OS thread handle 0x7f6bd00d5700, query id 8 localhost root init
ROLLBACK
--------
FILE I/O
--------
..

これは今、手でROLLBACKを叩いてロールバックしているところですが、TRANSACTION 22275, ..の次の行に、undo log entriesの記載があります。

これが今、このトランザクション内の操作でUNDOセグメントに載っているレコードの数。UNDOセグメントからレコードを全部引っ張り出せばロールバックが終わるので、これが0に近付いていくのを見て、なんとなく進捗を知ります。

InnoDBのロールバックがあとどれくらいかかるかをなんとなく見積もる | GMOメディア エンジニアブログ
  • innodb_purge_threads
    • あまり意識している人はいない気がするパラメータ。5.6ではデフォルト1、5.7ではデフォルト4となっています。更新が多く、show engine innodb status\Gを見た場合に History list length [数字] の数字が肥大化していっている場合は増やす事が推奨されます。但しMySQLの再起動が必要です。History list lengthが増えているということはundo領域(デフォルトではibdata1と同居)が肥大化する要因となるため気を配ったほうが良いです。またHistory list lengthが大きくなっていくと基本的には性能が劣化する傾向があります。
    • 何か時間の超絶かかるSELECTとかが実行されている場合もHistory list lengthが増える事があります。その場合は増やしてもたいした効果はないと考えられます(だってpurge出来ないし)
    • なお、過去の記憶ですが、無駄に増やしすぎるとコンテキストスイッチが増加して少ない設定の時よりも性能が下がるので程ほどに設定して下さい。5.7なら殆どのケースでデフォルトで十分だと思います。
MySQL関連のパラメータ(主にInnoDB)について - hiroi10の日記

  • The InnoDB Shared Tablespace (ibdata1) is Growing in Spite of Using innodb_file_per_table (Document ID 1472410.1)
  • How to Investigate InnoDB Lock Issues? (Documnet ID 1531774.1)

2017-12-21

Oracle Database の I/O 負荷テストツール SLOB(The Silly Little Oracle Benchmark) の紹介

JPOUG Advent Calendar 2017 の21日目のエントリーです。昨日は JPOUG in 15 minutes at db tech showcase Tokyo 2017 開催報告 でした。

JPOUG Advent Calendar に参加するのも6年目になりました。

今年は「SLOB(The Silly Little Oracle Benchmark) 」というOracle Database 向けの I/O 負荷テストツールを使って、AWS で RDS Oracle にI/O負荷をかける手順を紹介します。

I/Oベンチマークツールには iomater、fio のような汎用的なベンチマークツールから Oracle ORION のような Oracle Database の I/O パターンをシミュレートするものなどがありますが、SLOB は Oracle Database に実際に SQL を発行してストレージの I/O 性能(I/Oレイテンシ、IOPS、I/Oスループット)を計測したいときに有益なツールです。


SLOB とは

SLOB(The Silly Little Oracle Benchmark) は Kevin Closson が開発した Oracle Database 向けのI/O負荷テストツールです。Cで書かれた小さなプログラムで、sqlplus で Oracle Database に SQL を発行してI/O負荷をかけるコマンドラインツールです。

Oracle ORIONDBMS_RESOURCE_MANAGER.CALIBRATE_IO の中間でかつ Swingbench のようなトランザクション発行機能を備えたリアルな Oracle Database のI/O負荷テストツールです。詳しくは Introducing SLOB – The Simple Database I/O Testing Toolkit for Oracle Database を参照してください。

人気・実績のあるツールで、以下のベンダーが SLOB によるベンチマーク結果を公開しています。

  • flashgrid.io
  • VMware
  • A joint paper co-branded by Intel and Quanta Cloud Technologies
  • VCE
  • Nutanix
  • Netapp
  • HPE
  • Pure Storage
  • Nimble
  • IBM
  • Red Hat
  • Dell EMC
  • Red Stack Tech.
SLOB Use Cases By Industry Vendors. Learn SLOB, Speak The Experts’ Language. | Kevin Closson's Blog: Platforms, Databases and Storage

作者の Kevin Closson について

Kevin Closson は元 Oracle の Exadata のパフォーマンス・アーキテクトで、EMC で XtremIO のチーフパフォーマンスアーキテクトを経て、現在は AWS のソリューションアーキテクトです。 その前は、HP、IBM、Sequent、Veritas などで商用UNIXにおける Oracle Database の性能向上に関わっていた人で、SMP/NUMAのロックやデータベースのキャッシングの特許の発明者というディープなバックグラウンドを持った人です。詳しくはコチラ


構成

  • EC2(Amazon Linux AMI release 2017.03)
  • RDS Oracle (Oracle EE 12.1.0.2.v10)
    • STATSPACK を含めたオプショングループを使わないと SLOB の実行でエラーになります。

インストール・設定手順

EC2 に Oracle Instant Clientインストールする
$ rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64
$ rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
EC2 に gccインストールする
$ sudo yum -y install gcc
EC2 に SLOBをインストールする
$ tar xfz 2017.06.14.slob_2.4.2.tar.gz
$ cd SLOB/wait_kit
$ make
rm -fr *.o mywait trigger create_sem
cc     -c -o mywait.o mywait.c
cc -o mywait mywait.o
cc     -c -o trigger.o trigger.c
cc -o trigger trigger.o
cc     -c -o create_sem.o create_sem.c
cc -o create_sem create_sem.o
cp mywait trigger create_sem ../
rm -fr *.o
SLOBの設定をする
  • tnsnames.ora を設定する。
$ mkdir slob_test
$ cd slob_test
$ vim tnsnames.ora
slob =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl1210.******.ap-northeast-1.rds.amazonaws.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )
$ export TNS_ADMIN=`pwd`
$ echo $TNS_ADMIN
/home/ec2-user/slob_test
$ sqlplus awsuser@slob
  • SLOB用の表領域を作成する
SQL> @./misc/ts
  • バッファキャッシュのサイズを確認する
    • バッファキャッシュより大きいデータサイズにしてI/Oを発生させるため、バッファキャッシュのサイズ / 80MB(SLOB のデフォルトのテーブルのサイズ)を計算して、あとで setup.sh や runit.sh を実行する際に使用する。
    • slob.conf で SCALE を 80M から 300M に変更した。
SQL> show sga
Total System Global Area 1.2482E+10 bytes
Fixed Size		    2938208 bytes
Variable Size		 6308235936 bytes
Database Buffers	 6140461056 bytes
Redo Buffers		   30613504 bytes

SQL> select round(6140461056/(80*1024*1024)) from dual;

ROUND(6140461056/(300*1024*1024))
--------------------------------
			      20
  • slob.conf を設定する
SCALE=300M
(中略)
SQLNET_SERVICE_BASE=slob
SQLNET_SERVICE_MAX=slob
(中略)
DBA_PRIV_USER="awsuser"
SYSDBA_PASSWD="********"
$ cp -pi setup.sh setup.sh.org
$ vi setup.sh
$ diff setup.sh.org setup.sh
179c179,180
< for exe in sqlplus tnsping
---
> #for exe in sqlplus tnsping
> for exe in sqlplus
273,279c274,280
< if ( ! tnsping $svc >> $LOG 2>&1 )
< then
< 	f_msg FATAL ""
< 	f_msg FATAL "${FUNCNAME}: tnsping failed to validate SQL*Net service ( $svc )"
< 	f_msg FATAL "Examine $LOG"
< 	return 1
< fi
---
> #if ( ! tnsping $svc >> $LOG 2>&1 )
> #then
> #	f_msg FATAL ""
> #	f_msg FATAL "${FUNCNAME}: tnsping failed to validate SQL*Net service ( $svc )"
> #	f_msg FATAL "Examine $LOG"
> #	return 1
> #fi
$ sh ./setup.sh IOPS 20
  • 作成されたテーブルを確認する。
$ cat cr_tab_and_load.out

SLOBでI/O負荷をかける

  • 負荷をかける。
$ sh ./runit.sh 20
  • Statspack レポートを出力する。
SQL> select snap_id, snap_time from stats$snapshot order by 1;
SQL> exec RDSADMIN.RDS_RUN_SPREPORT(5,6);
  • AWSマネジメントコンソールからダウンロードして確認する。

f:id:yohei-a:20171223105125p:image:w640

STATSPACK report for

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          1487923022 ORCL                1 21-Dec-17 05:22 12.1.0.2.0  NO

Host Name             Platform                CPUs Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     ip-10-7-1-139    Linux x86 64-bit           4     2       1         15.7

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:          5 23-Dec-17 01:26:35      122       1.0
  End Snap:          6 23-Dec-17 01:31:39       42       1.1
   Elapsed:       5.07 (mins) Av Act Sess:      79.9


補足

  • Oracle Instant Client には tnsping は含まれない。

apparently oracle removed tnsping from the instant client 11.2.0.2.

adding tnsping to the instant client 11.2 | pfierens

参考情報など

参考
過去の JPOUG Advent Calendar 記事
過去の JPOUG Advent Calendar

明日は@mogmetさんです。