INNODBのTABLESPACEにどれだけFREE SPACEがあるか確認。

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, DATA_FREE,TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=’test’ AND TABLE_NAME like ‘T%’;

+————–+————+————+——–+———–+—————+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | DATA_FREE | TABLE_COMMENT |
+————–+————+————+——–+———–+—————+
| test | T1 | BASE TABLE | InnoDB | 4194304 | |
| test | T2 | BASE TABLE | InnoDB | 4194304 | |
| test | t1 | BASE TABLE | InnoDB | 4194304 | My Table |
+————–+————+————+——–+———–+—————+
3 rows in set (0.02 sec)
mysql>

mysql> show table status like ‘T%’;
|-+————-+———+————+——+—————-+————-+—————–+————–+———–+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free |
+——+——–+———+————+——+—————-+————-+—+————-+————–+————
| T1 | InnoDB | 10 | Compact | 9 | 1820 | 16384 | 0 | 0 | 4194304 |
| T2 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 4194304 |
|+——+——–+———+———–+——+—————-+————-+—————–+————–+———–+
2 rows in set (0.55 sec)
mysql>

innodb_freespace


InnoDB 起動オプションとシステム変数

–innodb

もしサーバが InnoDB サポートでコンパイルされると InnoDB ストレージ エンジンが有効になります。
InnoDB を無効にするには –skip-innodb を利用してください。

–innodb_status_file

InnoDB が MySQL データ ディレクトリ内にファイル名 /innodb_status. を作成するように働きかけます。InnoDB は定期的に SHOW ENGINE INNODB STATUS
のアウトプットをこのファイルに書き込みます。

mysql> SHOW ENGINE INNODB STATUS \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
090204 14:52:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 5, signal count 5
Mutex spin waits 0, rounds 200, OS waits 0
RW-shared spins 11, OS waits 5; RW-excl spins 1, OS waits 0
————
TRANSACTIONS
————
Trx id counter 0 1810
Purge done for trx’s n:o < 0 1293 undo n:o < 0 0 History list length 3 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: innodb_additional_mem_pool_size InnoDB がデータ辞書情報と別の内部データ構造を格納する為に利用する、メモリ プールのバイトでのサイズです。 より多くのテーブルをアプリケーション内に持っていると、ここに割り当てる為により多くのメモリが必要になります。 innodb_autoextend_increment 自動拡大テーブルスペースがいっぱいになった時にサイズを拡大する為のインクリメント サイズ(MB)。 デフォルト値は8です。 mysql> show variables like “innodb_autoextend_increment”;
+—————————–+——-+
| Variable_name | Value |
+—————————–+——-+
| innodb_autoextend_increment | 8 |
+—————————–+——-+
1 row in set (0.00 sec)

mysql>

innodb_buffer_pool_awe_mem_mb
AWE メモリ内に置かれた時の、バッファ プールのサイズ(MB)。これは32ビット Windows 内でだけ関連性があります。
indows OS が4GB 以上のメモリをサポートするなら、いわゆる 「Address Windowing Extensions,」 を利用する事で、
この変数を利用して InnoDB バッファプールを AWE 物理的メモリに割り当てる事ができます。
この変数の最大可能値は63000です。
もしこれが0以上なら、innodb_buffer_pool_size は InnoDB がその AWE メモリを マップする
mysqld の32ビット アドレス領域内のウィンドウです。innodb_buffer_pool_size の適正な値は 500MB です。
AWE メモリを活用するには、自分で MySQL をリコンパイルする必要があります。これを行うのに必要な現在のプロジェクト設定は、
storage/innobase/os/os0proj.c ソース ファイル内で見つける事ができます。

innodb_buffer_pool_size

InnoDB がそのテーブルのデータとインデックスをキャッシュする為に利用する、メモリバッファ のバイトでのサイズです。
この値を大きく設定するほど、テーブル内のデータにアクセスするのに必要なディスク I/O は少なくなります。
専用のデータベース サーバ上で、これをマシンの物理的メモリ サイズの最大80% に設定すると良いでしょう。
しかし、物理的メモリの競合が OS 内でページングを引き起こす可能性があるので、あまり大きく設定しないでください。

mysql> show variables like “innodb_buffer_pool_size”;
+————————-+———+
| Variable_name | Value |
+————————-+———+
| innodb_buffer_pool_size | 8388608 |
+————————-+———+
1 row in set (0.00 sec)

mysql>

innodb_checksums
InnoDB は、壊れたハードウェアやデータ ファイルに対する追加フォールト トレランスを保証するディスク
からの全てのページの読み込み上で、チェックサムの妥当性確認を利用する事ができます。
この妥当性確認はデフォルトで有効化されています。
ベンチマークが起動している時等、この追加安全機能は必要なく、–skip-innodb-checksums を利用して無効にする事ができます。

innodb_commit_concurrency
同時にコミットする事ができるスレッドの数。0の値は並行処理制御を無効にします。

mysql> show variables like “innodb_commit_concurrency”;
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| innodb_commit_concurrency | 0 |
+—————————+——-+
1 row in set (0.00 sec)

mysql>

innodb_concurrency_tickets
InnoDB に同時に入る事ができるスレッドの数は、innodb_thread_concurrency変数によって決められます。
スレッドが InnoDB に入ろうとする時にもし並行処理の限度までスレッド数が達していたら、
それらは列になります。スレッドが InnoDB に入るのを許可されると、innodb_concurrency_tickets
の値と同等の 「フリー チケット」 をたくさん与えられ、スレッドはそのチケットを使ってしまうまでは自由に
InnoDB に出入りできます。それ以降は、スレッドが次に InnoDB に入ろうとした時に、
再度並行処理チェックの対象となります。(または列に並ぶ可能性もある)

mysql> show variables like “innodb_concurrency_tickets”;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| innodb_concurrency_tickets | 500 |
+—————————-+——-+
1 row in set (0.00 sec)

mysql>

innodb_data_file_path
独立したデータ ファイルとそれらのサイズへのパス。
各データ ファイルへの完全ディレクトリ パスは、ここに指定された各パスへの
innodb_data_home_dir を結合する事によって形作られます。ファイル サイズは、
サイズ値に M か G を付加して、MB か GB (1024MB)で指定されます。ファイル サイズの合計
は最低10MB 必要です。もし innodb_data_file_path を指定しなければ、デフォルト動作で
ibdata1 と名づけられた10MB の単一自動拡大データ ファイルが作成されます。
各ファイルのサイズ制限は OS によって決定されます。

mysql> show variables like “innodb_data_file_path”;
+———————–+————————+
| Variable_name | Value |
+———————–+————————+
| innodb_data_file_path | ibdata1:10M:autoextend |
+———————–+————————+
1 row in set (0.00 sec)

mysql>

innodb_data_home_dir
全ての InnoDB データ ファイルのディレクトリ パスの主な部分。
もしこの値を設定しなければ、デフォルトは MySQL データ ディレクトリになります。

innodb_doublewrite
デフォルトで、InnoDB は全てのデータを2回格納します。
一回目は二重書き込み バッファに、そして次に実際のデータ ファイルに格納します。
この変数はデフォルトで有効化されています。それは、データの整合性や起こり得る
失敗に対する心配よりも、ベンチマークや最高性能が要求される時に、
–skip-innodb_doublewrite を利用して止める事ができます。

mysql> show variables like “innodb_doublewrite”;
+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| innodb_doublewrite | ON |
+——————–+——-+
1 row in set (0.00 sec)

mysql>

innodb_fast_shutdown
もしこの変数を0に設定すると、InnoDB はシャットダウンの前に完全消去と挿入バッファ マージを行います。
デフォルト値は1です。もしこれを2に設定すると、コミットされたトランザクションはなくなりませんが、
次の起動の際にクラッシュ復旧が行われます。

innodb_file_io_threads
InnoDB 内のファイル I/O スレッド数。通常、これはデフォルト値である4のままにしておくべきですが、
Windows 上のディスク I/O にとってはそれよりも大きい値の方がよいかもしれません。
Unix 上では、数値を増やしても効果はありません。

mysql> show variables like “innodb_file_io_threads”;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| innodb_file_io_threads | 4 |
+————————+——-+
1 row in set (0.00 sec)

mysql>

innodb_file_per_table
この変数が有効になると、InnoDB はデータとインデックスを共有テーブルスペースに格納
するのではなく、それ自体の .ibd ファイルを利用してそれぞれの新しいテーブルを作成し、
そこに格納します。デフォルトでは、共有テーブルスペースにテーブルを作成するという事になっています。

mysql> show variables like “innodb_file_per_table”;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| innodb_file_per_table | OFF |
+———————–+——-+
1 row in set (0.01 sec)

mysql>

innodb_flush_log_at_trx_commit
※1秒毎、またはコミット毎にトランザクションをログファイルにフラッシュ

0 = コミットされたトランザクションは1秒毎にログファイルにフラッシュされ、
コミット毎にはフラッシュされません。


1 = コミットされたクエリはCOMMIT毎に、ログファイルにフラッシュされる
ので、失われるデータは”0”となります。(例外も起こるとは思いますが)

innodb_flush_log_at_trx_commit が0に設定された時は、ログ バッファは1秒に一回ログファイルに
書き込まれ、ディスク操作へのフラッシュはログ ファイル上で行われますが、トランザクションコミット
の際には何も行われません。この値が1(デフォルト)の時は、ログ ファイルは各トランザクションコミット
の時にログ ファイルに書き込まれ、ディスク操作へのフラッシュはログ ファイル上で行われます。
2に設定された時は、ログ バッファはコミット毎にファイルに書き込まれますが、ディスク操作への
フラッシュはそこでは行われません。しかし、値が2の時もログ ファイル上でのフラッシュは1秒に1回行われます。
1秒に1回のフラッシュは、処理スケジュールの発行の為100%保証された物ではないという事に注意してください。
この変数のデフォルト値は1です。これは ACID 整合性に要求されている値です。より良い性能の為に1以外の値を
設定する事もできますが、その場合1つのクラッシュの中で最大1秒分のトランザクションを失う可能性があります。
もし値を0に設定すると、全ての mysqld プロセス クラッシュは最後の秒のトランザクションを消す場合があります。
もし値を2に設定すると、OS のクラッシュか停電によって、最後の秒のトランザクションが消されてしまいます。
しかし、InnoDB のクラッシュ復旧は影響を受けないので、値に関係なくクラッシュ復旧は行われます。

注意:InnoDB とトランザクションを共に利用して複製設定内で
最大の耐久力と一貫性を得る為に、お使いのマスタ サーバ my.cnf 内で
innodb_flush_log_at_trx_commit=1 と sync_binlog=1 を利用
しなければいけません。

mysql> show variables like “innodb_flush_log_at_trx_commit”;
+——————————–+——-+
| Variable_name | Value |
+——————————–+——-+
| innodb_flush_log_at_trx_commit | 1 |
+——————————–+——-+
1 row in set (0.01 sec)

mysql>

innodb_force_recovery
クラッシュ復旧モード。警告:この変数は、破損したデータベースからテーブルを捨てたいという
緊急の場合のみ、0以降の値に設定しなければいけません!可能な値は1から6です。
安全策として、InnoDB はこの変数が0以上の時はそのデータへの変更を阻止します

innodb_lock_wait_timeout
InnoDB トランザクションがロール バックされる前に、ロックを待つ秒数でのタイムアウト。
InnoDB は自動的にそれ自体のロック テーブル内でトランザクション デッドロックを検出し、
トランザクションをロールバックします。InnoDB は LOCK TABLES ステートメントを利用してロック
セットを通知します。デフォルトは50秒です。

mysql> show variables like “innodb_lock_wait_timeout”;
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| innodb_lock_wait_timeout | 50 |
+————————–+——-+
1 row in set (0.00 sec)

mysql>

innodb_locks_unsafe_for_binlog
この変数は InnoDB サーチとインデックス スキャン内でネクスト キー ロックをコントロールします。
デフォルトによってこの変数は0(無効)であり、それはネクスト キー ロックが有効であると意味します。

innodb_log_archive
InnoDB アーカイブ ファイルをログするかどうか。
この変数は歴史的理由により存在していますが、利用はされていません。

innodb_log_buffer_size
InnoDB がディスク上のログ ファイルに書き込む為に利用するバッファのバイトでのサイズ。
実用的な値の範囲は1MB から8MB です。デフォルトは1MB です。大きいログ バッファは、
トランザクション コミットの前にディスクにログを書き込む必要なく、大きいトランザクションが起動する事を許容します。
従って、もし大きいトランザクションを持っていたら、ログ ファイルを大きくしておく事でディスク I/O を節約する事ができます。

mysql> show variables like “innodb_log_buffer_size”;
+————————+———+
| Variable_name | Value |
+————————+———+
| innodb_log_buffer_size | 1048576 |
+————————+———+
1 row in set (0.00 sec)

mysql>

innodb_log_file_size

ログ グループ内のそれぞれの長いファイルのバイトでのサイズ。
ログ ファイルの結合したサイズは32ビット コンピュータ上で 4GB 以下でなければいけません。
デフォルトは5MB です。実用的な値は、N がグループ内のログ ファイル数だとして、
バッファ プールのサイズの1MB から 1/N-th です。
値が大きいほど、ディスク I/O を節約し、バッファ プール内で必要とされる
チェックポイント フラッシュ活動は少なくなります。

mysql> show variables like “innodb_log_file_size”;
+———————-+———+
| Variable_name | Value |
+———————-+———+
| innodb_log_file_size | 5242880 |
+———————-+———+
1 row in set (0.00 sec)

mysql>

innodb_log_files_in_group

ログ グループ内のログ ファイル数。InnoDB はファイルに輪状に書き込みをします。
デフォルト(そして推奨)は2です。

innodb_log_group_home_dir
InnoDB ログ ファイルへのディレクトリ パス。もし InnoDB ログ変数を何も指定しなければ、
デフォルトで MySQL データ ディレクトリ内に ib_logfile0 と ib_logfile1
という名前の2つの5MB ファイルを作成します。

innodb_max_dirty_pages_pct
これは0から100の範囲の間の整数です。デフォルトは90です。
InnoDB 内の主スレッドは、ダーティ (まだ書き込まれていない)ページの割合がこの値を超えないように
バッファ プールからページを書くように試みます。

mysql> show variables like “innodb_max_dirty_pages_pct”;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| innodb_max_dirty_pages_pct | 90 |
+—————————-+——-+
1 row in set (0.00 sec)

mysql>

innodb_max_purge_lag
この変数は、消去操作が遅れている時にINSERT、UPDATE そして DELETE 操作を
どのように遅らせるかをコントロールします。この変数のデフォルト値は0で、これは遅れは無いという事を意味します。
InnoDB トランザクション システムは UPDATE か DELETE 操作によって削除マークが付けられた
インデックス レコードを持つトランザクションのリストを保持します。このリストの長さを
purge_lag にして下さい。purge_lag が innodb_max_purge_lag を上回る時、各 INSERT、UPDATE
そして DELETE 操作は((purge_lag/innodb_max_purge_lag)×10)–5 ミリ秒遅れます。遅れは消去バッチの最初に、
10秒ごとに計算されます。もし消去される行をを知る事ができる、古い一貫した読み取りビューの為に消去が起動しなかったら、
その操作は遅れません。

mysql> show variables like “innodb_max_purge_lag”;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| innodb_max_purge_lag | 0 |
+———————-+——-+
1 row in set (0.00 sec)

mysql>

innodb_mirrored_log_groups
データベースの為に残すログ グループの同一コピー数。現在は、この値は1に設定しなければいけません。

innodb_open_files
この変数は InnoDB 内で複数のテーブルスペースを利用する場合のみ関連があります。
それは InnoDB が同時にオープンしておける .ibd ファイルの最大数を指定します。
最大値は10です。デフォルトは300です。
.ibd ファイルに利用されるファイル記述子は、InnoDB に対しての物のみです。
それらは、–open-files-limit サーバ オプションによって指定された物からは独立していて、
テーブル キャッシュの操作に影響を与えません。

mysql> show variables like “innodb_open_files”;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| innodb_open_files | 300 |
+——————-+——-+
1 row in set (0.00 sec)

mysql>

innodb_rollback_on_timeout
MySQL 5.1 内で、InnoDB はトランザクション タイムアウト上で最後のステートメントだけを
ロールバックします。このオプションが与えられると、トランザクション タイムアウトは InnoDB
がトランザクション全体を異常終了し、ロールバックするよう働きかけます。(MySQL 4.1と同じ動作です。)
この変数は、MySQL 5.1.15で追加されました。

mysql> show variables like “innodb_rollback_on_timeout”;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| innodb_rollback_on_timeout | OFF |
+—————————-+——-+
1 row in set (0.00 sec)

mysql>

innodb_support_xa
ON か1(デフォルト)に設定されると、この変数は InnoDB が XA トランザクション内の二相コミット サポートを有効にします。
innodb_support_xa を有効にすると、トランザクションの準備でディスク フラッシュが余計に起こります。
XA を利用する事を気にしないのであれば、この変数を OFF か0に設定してこれを無効にする事ができ、
ディスク フラッシュの数を減らし、InnoDB 操作性能を向上させる事ができます。

innodb_table_locks
もし AUTOCOMMIT=0、InnoDB が LOCK TABLES を支持すると、MySQL は全てのスレッドがそれら
全てのロックをテーブルにリリースするまで LOCK TABLE .. WRITE から戻りません。
innodb_table_locks のデフォルト値は1です。それはもし AUTOCOMMIT=0 なら LOCK TABLES は
InnoDB がテーブルを内部的にロックするよう働きかける事を意味します。

innodb_thread_concurrency
InnoDB は、この変数から与えられた制限よりも少ない、またはそれと同等の制限の InnoDB
内部に多くの OS スレッドを一斉に保存しようと試みます。性能に関する問題を持ち、
多くのスレッドがセマフォを待っているという事が SHOW ENGINE INNODB STATUS によって明らかにされたのなら、
スレッド 「thrashing」 を持ち、この変数を低くまたは高く設定するよう試みる必要があります。
もしたくさんのプロセッサとディスクがあるコンピュータをお持ちであれば、それを有効に活用する為に
値を高く設定する事もできます。推奨値はお使いのシステムのプロセッサとディスク数の合計値です。
この変数の範囲は0から1000です。20以上の値は無限並行処理として読み取られます。 無限というのは、
並行チェックが無効になり、ミューテックスを獲得、リリースする事で発生するであろう、多量の負荷を防ぐという意味です。
MySQL 5.1.11以前はデフォルト値は20で、5.1.11以降は8となっています。

mysql> show variables like “innodb_thread_concurrency”;
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| innodb_thread_concurrency | 8 |
+—————————+——-+
1 row in set (0.00 sec)
mysql>

innodb_thread_sleep_delay
InnoDB スレッドは InnoDB の列に加わるまでに、マイクロ秒で何秒間スリープ状態にあるか。
デフォルト値は10,000です。0の値ではスリープ状態にはなりません。

sync_binlog
もし変数値が正数であれば、MySQL サーバはバイナリ ログへの毎 sync_binlog 書き込みごとに、
ディスク(fdatasync())にそのバイナリ ログを同期化します。オート コミット モードでは、
各ステートメントにつきバイナリ ログへの書き込みが1つあり、そうでなければ各トランザクションに
つき1つの書き込みがあると覚えて置いてください。デフォルトは、ディスクへの同期化を行わない0です。
クラッシュしてしまった場合には、バイナリ ログから最大1つのステートメントかトランザクション
が失われてしまう為、1の値が一番安全な値です。


一般にテーブルの更新は SELECT より重要だと見なされるため、
テーブルを更新するステートメントはすべて、テーブルから情報を取り出す
ステートメントより優先度が高くなります。これにより、更新では特定の
テーブルに対して大量の重いクエリが使用されるため、更新が ‘資源枯渇’
にさらされないことが確実になります(これは、更新を実行する
ステートメントを LOW_PRIORITY とともに使用するか、
SELECT ステートメントとともに HIGH_PRIORITY を使用することで変更できます)。

InnoDB テーブルと BDB テーブルの場合は、MySQL で LOCK TABLES によって
明示的テーブルをロックした場合のみテーブルロックが使用されます。
InnoDB は自動行レベルロックを使用し、BDB はページレベルロックを使用して
トランザクションの独立を確実にするため、これらのテーブル型には、
LOCK TABLES をまったく使用しないように推奨

━以下のアイテムはテーブルロックによる競合を軽減または回避する方法━

*SELECTステートメントの実行の高速化を試行する。これにはサマリテーブルの作成が必要な場合もあります。
*–low-priority-updatesのオプションで mysqldを開始する。これは、テーブルを更新(変更)するすべてのステートメントの優先度を SELECTステートメントの優先度より低くします。 この場合、前シナリオの2つ目のSELECTステートメントはUPDATEステートメントの前に実行され、一番目のSELECTが完了するまで待機する必要はありません。
*SET LOW_PRIORITY_UPDATES=1ステートメントを使用すると、特定の接続からの更新すべてが低い優先度で実行されるように指定できます。
*LOW_PRIORITY属性を使用して、特定のINSERT、UPDATE、または DELETEステートメントの優先度を低く設定できます。
*HIGH_PRIORITY性を使用すると、特定の SELECT の重要度を高く指定できます。
*max_write_lock_countシステム変数の値を低くしてmysqldを開始し、MySQLに全てのSELECTステートメント(特定数をテーブルに挿入した後に待機しているステートメント)の優先度を一時的に引き上げさせます。これは、一定数のWRITEロックの後にREADロックを設定します。
*SELECTと結合したINSERT に問題がある場合は、SELECTステートメントとINSERT ステートメントの同時サポートが可能になるため、新規の MyISAMテーブルを使用するように切り替えます。
*SELECTとDELETEステートメントに問題がある場合、DELETEにLIMITオプションを使用すると解決できる場合があります。
*SELECTステートメントでSQL_BUFFER_RESULTを使用すると、テーブルロックの持続を短縮することができます。
*単一クエリを使用するために、mysys/thr_lock.cでロックコードを変更できることもあります。この場合、writeロックとreadロックは同等の優先度をもち、いくつかのアプリケーションにとって役立つものとなります。

Server version: 5.1.30 MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use DB01
Database changed
mysql> show tables;
+—————-+
| Tables_in_DB01 |
+—————-+
| TABLE007 |
+—————-+
1 row in set (0.00 sec)

mysql> select HIGH_PRIORITY * from TABLE007;
+—-+————–+
| id | comment |
+—-+————–+
| 1 | testtesttest |
| 2 | testtesttest |
| 3 | testtesttest |
+—-+————–+
3 rows in set (0.00 sec)

mysql>

詳細:
http://dev.mysql.com/doc/refman/5.1/ja/table-locking.html

high_pri


DEFAULT or YES = STORAGE ENGINE IS COMPILED IN AND ENABLED
DISABLE = COMPILED IN BUT DISABLED
NO = NOT COMPILED

mysql> SHOW ENGINES;
+————+———+—————————————————————-+————–+—–+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+————+———+—————————————————————-+————–+—–+————+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+————+———+—————————————————————-+————–+—–+————+
7 rows in set (0.05 sec)

mysql>


InnoDBコマンド オプション



*  –innodb
もしサーバが InnoDB サポートでコンパイルされると
InnoDB ストレージ エンジンが有効になります。
InnoDB を無効にするには –skip-innodb を利用。



* innodb_buffer_pool_sizeInnoDB がそのテーブルのデータ
とインデックスをキャッシュする為に利用する、メモリバッファのバイト
でのサイズです。この値を大きく設定するほど、テーブル内のデータに
アクセスするのに必要なディスク I/O は少なくなります。
専用のデータベース サーバ上で、これをマシンの物理的メモリ
サイズの最大80% に設定すると良いでしょう。しかし、物理的メモリの競合が
OS 内でページングを引き起こす可能性があるので、あまり大きく設定しない



* innodb_commit_concurrency同時にコミットする事ができるスレッドの数。
0の値は並行処理制御を無効にします。



※ –skip-myisam optionは、ありません。MYISAM Storage Engineは無効にする事が出来ません。


テーブルロックについて。
ロックはUNLOCK TABLESコマンドが、クライアントから実行された時にリリースされる。

※ クライアントが他のロックをLOCK TABLESによって取得リクエストをかけた時
※ 接続を切断した時
※ ABORTされた時(KILLコマンドなど)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
MYSQLでのロック動作確認
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
mysql> LOCK TABLES company READ; LOCK TABLES innodb_monitor write;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

※ このクエリーだとcompanyは直ぐにリリースされて、innodb_monitorのみロックされる。

mysql>

mysql> LOCK TABLES company READ,innodb_monitor write;
Query OK, 0 rows affected (0.00 sec)

mysql>

■ mysql>SELECT * FROM innodb_monitor; 
  ここで別のクライアントからselectしたら他のプロセスのwrite lockにて待たされる。

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql>

■ mysql>SELECT * FROM innodb_monitor;
  ロックが解放されたので、select結果が表示される。


MYSQLが提供している関数

mysql> select IS_FREE_LOCK(‘app lock’);
+————————–+
| IS_FREE_LOCK(‘app lock’) |
+————————–+
| 1 |
+————————–+
1 row in set (0.00 sec)

mysql>

※ 1はロック取得可能

mysql> select GET_LOCK(‘app lock’,10);
+————————-+
| GET_LOCK(‘app lock’,10) |
+————————-+
| 1 |
+————————-+
1 row in set (0.00 sec)

mysql>

※ 1は10秒以内にLockが取得される。10はtimeoutの値

mysql> select IS_FREE_LOCK(‘app lock’);
+————————–+
| IS_FREE_LOCK(‘app lock’) |
+————————–+
| 0 |
+————————–+
1 row in set (0.00 sec)

mysql> select RELEASE_LOCK(‘app lock’);
+————————–+
| RELEASE_LOCK(‘app lock’) |
+————————–+
| 1 |
+————————–+
1 row in set (0.00 sec)

※ 1はlockリリースが問題なく実行されたという事

mysql> select IS_FREE_LOCK(‘app lock’);
+————————–+
| IS_FREE_LOCK(‘app lock’) |
+————————–+
| 1 |
+————————–+
1 row in set (0.00 sec)

mysql>

もっと他の関数は、こちらのサイトにあります。

(例)
数字のネットワーク アドレス ( 4 または 8 バイト ) を与えられ、
アドレスのドット形式のクワッド表示をストリングとして戻します。

mysql> SELECT INET_NTOA(3520061480);
+———————–+
| INET_NTOA(3520061480) |
+———————–+
| 209.207.224.40 |
+———————–+
1 row in set (0.00 sec)

mysql>


MY ADMINを利用してMYSQLのFULL BACKUPから、
特定テーブルのリストアを行う方法。
普段は、mysqldumpでバックアップして、mysqlコマンドでリストア
しているので良い勉強になりました。

① まずはテストの為にBACKUPを作成してみます。
BACKUPプロジェクトを作成して「EXECUTE BACKUP NOW」で
バックアップを取得してみます。


BACKUP


② テストの為にBACKUPを取得した、DBにあるテーブルから
データを削除してみます。


DELETE


③ MYSQL AdministratorにてRESTOREを選択して、
①にて作成したBACKUPファイルを開き、「Analyze Backup File」を選択
しリストアしたいテーブルのみをチェックします。
※ FULLでリストアしたいときは、特に特定のテーブルのみ選択しないで
全てリストアしてください。


RESTORE


④ リストアがきちんと行われたかテーブルをselectして確認してみて下さい。
データが戻っていれば成功です。


CONFIRMATION


⑤ データが戻っているので成功ですね。


MYSQLが稼動中に作成したtemporary tableの推移をグラフにして

モニタリングする方法。

① MYSQL ADMINISTRATORを開いてグラフエリアで「ADD A GROUP」として名前を付ける。

② 新しいグラフグループにて、「ADD A GRAPH」を選択。

③ lineグラフを選択して  ^[created_tmp_tables] をformulaに追加する。

以下の図のようにtemp tableが作成された様子がグラフになってモニタリング

出来るので、パフォーマンスの調整に利用出来ます。

tmp_tables_graph


存在しているユーザーの確認

[root@colinux tmp]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.1.30 MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use mysql
Database changed
mysql> select user,host,password from mysql.user;
+———-+———–+——————————————-+
| user     | host      | password                                  |
+———-+———–+——————————————-+
| root     | localhost | *N41EFFFE1191DDDD7D3F2B6F5A9CDDD0DDDDDC3D |
| root     | colinux   | *N41EFFFE1191DDDD7D3F2B6F5A9CDDD0DDDDDC3D |
| root     | 127.0.0.1 | *N41EFFFE1191DDDD7D3F2B6F5A9CDDD0DDDDDC3D |
| variable | %         | *N41EFFFE1191DDDD7D3F2B6F5A9CDDD0DDDDDC3D |
+———-+———–+——————————————-+
4 rows in set (0.00 sec)

mysql>

ユーザー権限の確認

mysql> SELECT Host, User, Select_priv, Insert_priv,Update_priv, Delete_priv FROM user;
+———–+———-+————-+————-+————-+————-+
| Host      | User     | Select_priv | Insert_priv | Update_priv | Delete_priv |
+———–+———-+————-+————-+————-+————-+
| localhost | root     | Y           | Y           | Y           | Y           |
| colinux   | root     | Y           | Y           | Y           | Y           |
| 127.0.0.1 | root     | Y           | Y           | Y           | Y           |
| %         | variable | Y           | Y           | Y           | Y           |
+———–+———-+————-+————-+————-+————-+
4 rows in set (0.01 sec)

mysql>

ユーザーとDB毎の権限確認

mysql> select Db,User,Host,Select_priv from db;
+———+——-+———–+————-+
| Db      | User  | Host      | Select_priv |
+———+——-+———–+————-+
| test    |       | %         | Y           |
| test\_% |       | %         | Y           |
| DB02    | admin | localhost | Y           |
+———+——-+———–+————-+
3 rows in set (0.00 sec)

mysql> select Db,User,Host,Select_priv,Insert_priv,Update_priv from db;
+———+——-+———–+————-+————-+————-+
| Db      | User  | Host      | Select_priv | Insert_priv | Update_priv |
+———+——-+———–+————-+————-+————-+
| test    |       | %         | Y           | Y           | Y           |
| test\_% |       | %         | Y           | Y           | Y           |
| DB02    | admin | localhost | Y           | Y           | Y           |
+———+——-+———–+————-+————-+————-+

3 rows in set (0.49 sec)

mysql>

mysql_priv

mysql_priv