Hatena::ブログ(Diary)

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

2018-03-03

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.

no title

参考

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

2017-09-24

*1:マスターとリードレプリカについて追記予定

2017-09-15

MySQL で NOT NULL 制約のある列に複数行インサートするとその型のデフォルト値が入る

MySQLSQL モードが STRICT モードでない場合、NOT NULL 制約のある列に複数行インサートするとその型のデフォルト値(0とか空文字)が入る(1行インサートだとエラーで入らない)。


検証結果

  • Amazon Aurora with MySQL Compatibility に接続する
$ mysql -h ******.******.ap-northeast-1.rds.amazonaws.com -u awsuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mydb;
Database changed
  • テーブルを作成する
mysql> create table `not_null_test` (
    ->   `id` int(10) unsigned not null,
    ->   `int_col` int(10) unsigned not null,
    ->   `char_col` char(10)  not null,
    ->   `ts_col` timestamp not null,
    ->   primary key (`id`)
    -> ) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.05 sec)
  • SQL モードは設定されていない
mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.02 sec)
  • 1行インサートはエラーになる
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be null
  • 複数行インサートは成功する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null), (2, null, null, null);
Query OK, 2 rows affected, 4 warnings (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 4
  • テーブルのデータを確認する
mysql> select * from not_null_test;
+----+---------+----------+---------------------+
| id | int_col | char_col | ts_col              |
+----+---------+----------+---------------------+
|  1 |       0 |          | 2017-09-15 06:54:07 |
|  2 |       0 |          | 2017-09-15 06:54:07 |
+----+---------+----------+---------------------+
2 rows in set (0.02 sec)
  • SQLモードを STRICT_ALL_TABLES にする
mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.03 sec)
  • 1行インサートは失敗する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be null
  • 複数行インサートも失敗する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null), (2, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be nul

参考

単列インサートの場合はNOT NULLが指定されたカラムにNULL値が挿入されるとそのクエリはエラーとなって失敗するが、複数列インサートの場合は警告(warning)を発するものの、クエリは正常に受け付けられる。

その際、NULL値が指定された各カラムにはそれぞれのカラムのデータ型の暗黙的なデフォルト値が挿入される。(数値型なら0、文字列型なら空文字''、etc…)

MySQLにおけるNOT NULLカラムへのインサート時の挙動 - Sojiro’s Blog

NOT NULL として宣言されているカラムへの NULL の挿入。複数行の INSERT ステートメントまたは INSERT INTO ... SELECT ステートメントの場合、このカラムは、そのカラムデータ型の暗黙のデフォルト値に設定されます。これは、数値型では 0、文字列型では空の文字列 ('')、および日付と時間型では「0」の値です。サーバーは SELECT からの結果セットを検査して、それが単一行を返すかどうかを確認しないため、INSERT INTO ... SELECT ステートメントは複数行の挿入と同じ方法で処理されます。(単一行の INSERT の場合は、NULL が NOT NULL カラムに挿入されても警告は発生しません。代わりに、このステートメントがエラーで失敗します。)

no title

明示的な DEFAULT 句のない NOT NULL カラムに対するデータエントリでは、INSERT または REPLACE ステートメントにカラムの値を含まれていない場合、または UPDATE ステートメントがカラムを NULL に設定する場合、MySQL はその時点で有効な SQL モードに従ってカラムを処理します。

  • 厳密な SQL モードを有効にした場合、トランザクションテーブルに対してエラーが発生し、ステートメントがロールバックされます。非トランザクションテーブルではエラーが起きるが、これが複数行ステートメントの 2 行目以降の行に対するエラーの場合、先行する行が挿入されています。
  • 厳密モードが有効でない場合、MySQL はカラムデータ型の暗黙的なデフォルト値にカラムを設定します。

(中略)

セクション5.1.7「サーバー SQL モード」を参照してください。

所定のテーブルに対して、SHOW CREATE TABLE ステートメントを使用すると、どのカラムに明示的な DEFAULT 句があるかを確認できます。

暗黙的なデフォルトは次のように定義されます。

  • 数値型のデフォルトは 0 です。ただし、例外として AUTO_INCREMENT 属性で宣言された整数型または浮動小数点型のデフォルトは、そのシーケンスの次の値になります。
  • TIMESTAMP 以外の日付と時間型のデフォルトには、「ゼロ」値が適切です。explicit_defaults_for_timestamp システム変数が有効な場合、これは TIMESTAMP にも当てはまります (セクション5.1.4「サーバーシステム変数」を参照してください)。それ以外の場合、テーブルの最初の TIMESTAMP カラムのデフォルト値は現在の日付と時間になります。セクション11.3「日付と時間型」を参照してください。
  • ENUM ではない文字列型のデフォルト値は空の文字列です。ENUM のデフォルトは、最初の列挙値です。
no title

厳密モードは、MySQL が INSERT や UPDATE などのデータ変更ステートメントで無効な値または欠落した値を処理する方法を制御します。値はいくつかの理由で無効になることがあります。たとえば、カラムに対して正しくないデータ型を持っていたり、範囲外であったりすることがあります。値の欠落が発生するのは、挿入される新しい行の非 NULL カラムに値が含まれておらず、そのカラムに明示的な DEFAULT 句が定義されていない場合です。(NULL カラムの場合、値が欠落しているときは NULL が挿入されます。)

厳密モードが有効でない場合、MySQL は無効または欠落した値に対して調整された値を挿入し、警告を生成します (セクション13.7.5.41「SHOW WARNINGS 構文」を参照してください)。厳密モードでは、INSERT IGNORE または UPDATE IGNORE を使用すると、この動作を実行できます。

データを変更しない SELECT などのステートメントの場合、厳密モードでは無効な値はエラーでなく警告を生成します。

厳密モードは、外部キー制約が検査されるかどうかに影響されません。foreign_key_checks を検査に使用できます。(セクション5.1.4「サーバーシステム変数」を参照してください。)

no title

2017-04-06

「ロールバックセグメントの歌」と「データガードの歌」

前職(日本オラクル)の先輩データベースコンサルタントのちゃむさんからの依頼で、ロールバックセグメントの歌」と「データガードの歌」の歌詞と音源 を掲載します。

ちゃむさんはインサイトテクノロジー時代に「 おら!オラ!Oracle − どっぷり検証生活」というメールマガジンを書かれていた方で、以下の著書の共著者でもあります。

おら!オラ!オラクル

おら!オラ!オラクル


ちゃむさんからの言葉

ロールバックセグメントの歌については、2001年のOracle Open Worldに向けに、データガードの歌については、2003年の10gR1のデータガードのコアテクセミナーというイベント向けに作成したものです。

何人かの方から、上記の歌についてダウンロードできるようにご依頼をいただいていたため、元同僚のブログ『ablog』にアップしていただくようにお願いしました。

ロールバックセグメントは、UNDO表領域に代わり、データガードも、Active Data Guardをはじめ様々な機能が実装されていますので、どちらの歌詞も現在の機能に追いついておりませんが、ご了承ください。

Oracleには、ExadataやOracle Golden Gateなど歌にしたくなるような素敵な製品がたくさんありますので、ぜひお時間があるときに歌を作ってください。


ロールバックセグメントの歌

作詞:ちゃむ / 作詞・曲:あふろなピアノ弾き

俺は、なロールバックセグメント

やり直したけりゃ俺を呼べ。

最強のライバルはミサイル。

あいつがくると、おれは死の宣告を受けたも同然。

あーミサイルが打ち込まれた!!

だけど、すぐには、死なないぞ。

それが、おれの持ち味「読み取り一貫性」だ。

俺のことを呼んでくれるやつがいる限り、俺は生き続けたい。

でも、別のトランザクションが俺を上書きしてくる。

でも、まだまだ俺は、死なない。

初期化パラメーターの個数と順番からいって

次の次辺りか!

あートランザクションが、2つ同時にやってきた。

俺は、上書きされちまった。

もう俺を呼んでも ORA-1555


データガード(Data Guard)の歌 〜〜しっぺ返し without やさしさ

作詞:ちゃむ / 作詞・歌:あふろなピアノ弾き

1番(フィジカル やさしさバージョン)

プライマリ:

僕が貴方を養う

REDO(リドゥー)を送るからね(LGWR(ログライター)でね)

僕は、貴方無しでは生きられない

スタンバイ:

あなたに包まれて

いつもREDO(リドゥー)をありがとう(しかもsnyc(シンク)で)

あなたなしでは生きていけないの

だけど、突然あなたがデータファイル破損

私が助けてあげなくちゃ

copy datafile recover datafile

これを使ってね

プライマリ:

Thank you enough!!

2番(フィジカル しっぺ返しバージョン)

プライマリ :

おまえは俺の奴隷

REDO(リドゥー)がほしいのか(ARCH(アーカイバ)でいいだろ)

おまえは俺無しでは生きられない

スタンバイ:

あなたは私の神

早くREDO(リドゥー)をくださいな。

あなたなしでは生きていけません。

だけど、突然あなたはAbort(停電?)

私がフェイルオーバーしなくちゃ

Finish end-of-redo MRP down

今度は、私が神

プライマリ:

Oh My God!!

3番(ロジカル やさしさバージョン)

プライマリ:

僕が貴方を養う

REDO(リドゥー)を送るからね(LGWR(ログライター)でね)

僕は、貴方無しでは生きられない

スタンバイ :

あなたに包まれて

いつもREDO(リドゥー)をありがとう(しかもsnyc(シンク)で)

あなたなしでは生きていけないの

だけど、突然あなたのパフォーマンスダウン(すごい実行数)

私が助けてあげなくちゃ

Apply immediate Change service_name

参照系は任せてね

プライマリ:

Thank you enough!!

4番(ロジカル しっぺ返しバージョン)

プライマリ:

おまえは俺の奴隷

REDO(リドゥー)がほしいのか(ARCH(アーカイバ)でいいだろ)

おまえは俺無しでは生きられない

スタンバイ:

あなたに秘密で

索引を作ったのさ(ばれなきゃいいけど)

私は自分だけ速くなりたいの

プライマリ:

おまえ、内緒で索引つくったろ(どの表のだ?)

裏切ったな、お仕置きするぞ

supplemental logging off

適用できないだろ

スタンバイ:

Oh My God!!

2017-04-02

「SQL性能を最大限に引き出すDB 12cクエリー・オプティマイザ」 @TechNight#7

2017/3/27に Oracle Database Technology Night #7 で「SQL性能を最大限に引き出すDB 12cクエリー・オプティマイザ 〜 新機能活用と統計情報運用の戦略 〜」というお題で発表しました。

f:id:yohei-a:20170227173239j:image:w640


内容

SQL性能を最大限に引き出すDB 12cクエリー・オプティマイザ 〜 新機能活用と統計情報運用の戦略 〜
Technical Discussion Night 〜「DB 12cクエリー・オプティマイザ(パフォーマンス・チューニング)」を語ろう 〜

参考