28 1月 11 MySQLお勉強メモ(新機能編)

MySQLお勉強メモ 新機能編です。

InnoDB Plugin

5.5からデフォルトストレージエンジンとして利用される。5.1迄で言うInnoDB Pluginが5.5ではInnoDBと言われている。

新機能

• Fast Index creation : 主キー以外のインデックスの追加・削除・変更が高速になる。5.1迄はALTER TABLE/CREATE INDEX実行時には別表でテーブルを再作成し、それをリネームしていたので遅かった。
• 新しいフォーマット
• データ圧縮 : 更新可能。
• クラッシュリカバリの短縮
• I/Oスレッドの多重化
• I/Oスループットの調整
• Change Buffering : INSERT/UPDATE/DELETE時におけるインデックス更新をキャッシュ
• 等

互換性

5.1迄のInnoDBフォーマットは使える。

参考URL

• 漢(オトコ)のコンピュータ道: InnoDB Pluginことはじめ。快適ストレージエンジン生活はじまる！
• MySQL InnoDB Pluginのデータ圧縮機能 – SH2の日記

MySQL5.5の新機能

• 準同期レプリケーション
• カラムパーティショニング : RANGE/LIST 両方ともint型以外のデータ型を指定
• UTF-8 4バイトに対応
• そのほかレプリケーション機能
  • master.info/relay.infoの同期書き込み
• mysqldump : スレーブのリレーログinfoを残すオプション(どこまでバイナリログを受け取ったかがわかる)
• performance_schema

28 1月 11 MySQLお勉強メモ(InnoDB編)

MySQLお勉強メモ、InnoDB編です。

特徴

データ形式

• ibdata files : データ/インデックス領域。UNDO領域/データディクショナリが含まれる。
• ib_logfile files : REDOログのようなもの
• メタデータ : データベースディレクトリに存在(テーブル名.FYM)

ibdataはテーブル毎に分割することが可能。テーブルメンテナンスやパーティションで有効。

トランザクション

対応。ACID属性に準拠している。デフォルトはAUTOCOMMIT。

デフォルト分離レベルはrepeatable read。Oracleはread committed。

ロック

行ロック。完全な行レベルロックではなく、インデックスのnext_key_lock。なので、当該レコード以外の部分(中間Node等)でもロックがかかる可能性がある。

ALTER TABLE時にはテーブルロック(READ LOCK)となる。インデックスの追加・カラム定義の変更等が該当する。

バックアップ・リカバリ

• ファイルコピー
  • 対象ファイル : ibdata, ib_logfile, *.ibd, *.frm
  • MySQLを止めないとできない(ibdataにUNDO表領域が含まれている)
• mysqldump –starttransaction : オンラインバックアップ可能。データ量が多いときは時間がかかる
• レプリケーション : オンラインバックアップ可能
• Innodb Hotbackup : 有償。オンラインバックアップ可能。

バッファ機構

概要

innodb_buffer_poolに書き込み/読み込みデータを保管しており、定期的にibdataファイルに書き込みを行うことで、性能を向上させている。ibdataのデフォルトサイズは10MB。

また、書き込みを保証するためにREDOログとしてib_logfile0/ib_logfile1を持つ。WAL(Write Anywhare Log)であり、シーケンシャルライトで書き込む。デフォルトではLogbufferからコミット毎に書き込まれる。

Redo Logにはブロックの位置と、変更種別、変更内容が記録されている。

チェックポイント

チェックポイントが発生したタイミングで、innodb_buffer_poolの内容とib_logfileの内容をibdataに反映する。

タイミングはib_logfileが一杯になったとき・MySQLサーバの負荷が低いときにバックグラウンドでチェックポイントが発生する。

ib_logfile0/1が小さいとチェックポイントが頻繁に発生するので、おおきくする(1-2GB程度)。

パラメータ

innodb_buffer_pool_size

バッファのサイズを指定する。ibdataが乗るため、データ/インデックスが両方乗ることになる。OS全体の50-80%程度設定してもよい。デフォルトは8Mのため必ずおおきくしましょう。

innodb_flush_log_at_trx_commit

デフォルトの1以外はトランザクション結果が失われるので、やめた方がいい。一般的に2を利用すると速度が速くなると言われているが、RAIDコントローラでライトキャッシュが存在する場合はそこまで差が出ない。

• 0 : 1秒毎に書き込みする(pwrite + fsync)
• 1 : 1秒毎に書き込みする(pwrite + fsync) & COMMIT時にFLUSHする(pwrite + fsync)(デフォルト)
• 2 : 1秒毎に書き込みする(pwrite + fsync) & COMMIT時にFLUSH(pwriteのみ)

innodb_log_buffer_size

デフォルト1M。トランザクションサイズに応じて設定する。書き込み量にもよるが、1-8MB程度でよいのでは。COMMIT毎にログファイルにFLUSHされるので、アプリ要件次第だが、COMMIT間隔(1000件コミットとか)で調整した方が性能が上がる。

innodb_log_file_size

ib_logfileのサイズを指定する。1GB程度でどうか。小さい状態で更新量が大きいと、ログがあふれる可能性が出てくる。ただし大きすぎるとクラッシュリカバリの時間がかかる。

値の確認方法

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
1 row in set (0.00 sec)

リカバリ処理について

クラッシュリカバリ時には、MySQLサーバ起動時にib_logfile0/1をディスクに反映する。ib_logfile0/1が大きいと、クラッシュリカバリに時間がかかる。

なお5.5では速度が大幅に向上している。

リカバリ処理の順番について

再起動 -> redoログ -> undoログの順番で適用する。

undoログについて

概要

• UPDATE/DELETE/INSERT時に書き込まれる。
• INSERTはコミットされたら廃棄される。
• UPDATE/DELETEは他トランザクションからの参照が無くなるまで残る。

データ構造

• PrimaryKey
• Old trx id
• Old values on that now

REDOログについて

データ構造

• PageNo
• Offset
• Record Type
• 変更点

インデックス

概要

• B+Tree形式でインデックスを持つ。
• Leafブロックは16KB単位で持っている。
• PKとデータを同一Leafブロック上に持つ(クラスタードインデックス)。このためPKを利用した検索はデータブロックの参照ステップがインデックス読み込みで同時に行われるため、早い。
• PK以外のインデックスは、LeafにPKの値（Leafブロックのポインタ値では無い!)保存している。
• PK以外のインデックス検索の最後はPK検索に集約されるため、検索性能を上げるためには、PK検索の負荷が少なくなるように、PK値は小さくする工夫が必要となる。

備考

alter tableに時間がかかる。

 

ストレージエンジンのステータス

show engine innodb statusの見方

SEMAPHORES

OSでのwait等がわかる。

FILE I/O

• read thread : 5.1迄1つ。5.5から複数で、マルチコアに最適化した。
• write thread : 5.1迄1つ。5.5から複数で、マルチコアに最適化した。

INSERT BUFFER AND ADAPTIVE HASH INDEX

• ADPTIVE HASH INDEX : innodb_buffer_pool内部でできるハッシュインデックスで、データブロックを引き当てるために内部的に利用される

LOG

チェックポイントとREDOログファイルの関係を表示する。ib_logfileとデータファイル間の差分の大小がわかる。

BUFFER POOL AND MEMORY

チューニングに使われるポイント。重要。

• Buffer pool size : buffer poolのサイズ
• Free buffers : free bufferのサイズ。free bufferが0だとサイズが不足していることになる。

show engine innodb status\Gのサンプル

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
110128 13:05:02 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 26396 1_second, 26395 sleeps, 2403 10_second, 2367 background, 2366 flush
srv_master_thread log flush and writes: 26408
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1383, signal count 1383
Mutex spin waits 1035, rounds 1339, OS waits 8
RW-shared spins 1375, rounds 41250, OS waits 1375
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 1.29 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 6970F4
Purge done for trx's n:o < 6970EA undo n:o < 0
History list length 2328
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, OS thread id 35024326528
MySQL thread id 15842, query id 381896 localhost root
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
928 OS file reads, 22157 OS file writes, 11195 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.43 writes/s, 0.43 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1106407, node heap has 4 buffer(s)
0.93 hash searches/s, 1.79 non-hash searches/s
---
LOG
---
Log sequence number 741424365
Log flushed up to   741424365
Last checkpoint at  741421898
0 pending log writes, 0 pending chkp writes
8219 log i/o's done, 0.43 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 549453824; in additional pool allocated 0
Dictionary memory allocated 645716
Buffer pool size   32768
Free buffers       31768
Database pages     996
Old database pages 371
Modified db pages  10
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 915, created 81, written 12929
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 996, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 35024334592, state: sleeping
Number of rows inserted 369, updated 2897, deleted 362, read 54209
0.07 inserts/s, 0.14 updates/s, 0.00 deletes/s, 1.07 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

パフォーマンスを引き出すために

PKは小さくする

2次インデックスは検索結果にPKの値を保持しており、最終的にPKに集約されるから、PKに検索が集中する。このPK検索を効率化するためにはPKを小さくする方法が考えられる。

ログファイルサイズを大きくする

ログファイルが小さいことによるチェックポイントの頻度を押さえる。

• 大きなトランザクションは避ける
• 大きなロールバックをさける
• 大量挿入時はユニークKEYチェックを一時的に無効化する

そのほかパラメータ

• innodb_additional_mem_pool_size : テーブル数が多かったり、DB内部で利用する値が大きい場合は値を増やすことを検討する
• innodb_thread_concurrency : 5.0.8までは8。現在は20(無制限の意味)

InnoDB vs MyISAM

	InnoDB	MyISAM
リカバリ	完全リカバリが可能	データ消失の可能性有
キャッシュ	innodb_buffer_pool O_DIRECTを利用してOSキャッシュを無効化する方法も。	OSファイルキャッシュのみ
保存	PKに基づく	挿入順
圧縮	無	可
トランザクション	対応	非対応
SELECT COUNT(*)	正確な行数を知らない。	行数を知っているため高速。
ロック	行レベルロック	テーブルロック
全文検索	無	有

27 1月 11 MySQLお勉強メモ(サーバ設定項目)

パラメータ概要

• global : MySQL Server起動時に一括設定。キャッシュ機能提供が多い。
• Session : セッション毎必要時に設定。バッファ機能。できるだけ使われないようにクエリチューニングされることがポイント。
• メモリ使用量 = glogal + session x 最大接続数(max_connections : デフォルト 100 + 1(SUPER権限専用))
• InnoDBをメインに利用する場合、Innodb_buffer_pool : 50-80%程度は設定してもよい。

globalパラメータについて

テーブルキャッシュ

概要については以下。

• table_definition_cache(5.1.4以降)
• table_open_cache(5.1.4以前 table_cache) : テーブルをオープン状態にする。セッション毎に消費される。
• table_open_cacheのチューニングは、一度に開くテーブル数(joinの数) x セッション数消費 + 5で行う。
• table_definition_cache : テーブルメタデータ情報をメモリ上にキャッシュする。frmの内容をキャッシュする。セッション数に関係ない。 テーブル数をみておけばよい。

テーブルキャッシュのデフォルト値は以下。

mysql> show variables like 'table%cache%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| table_definition_cache | 400   |
| table_open_cache       | 2048  |
+------------------------+-------+
2 rows in set (0.01 sec)

オープンテーブルの詳細表示

• In_use : テーブル使用状態を示す
• Name_locked : alter table テーブル定義の変換

実行結果例。

mysql> show open tables like 'wp%';
+-----------------+-----------------------+--------+-------------+
| Database        | Table                 | In_use | Name_locked |
+-----------------+-----------------------+--------+-------------+
| wp              | wp_term_taxonomy      |      0 |           0 |
| wp              | wp_terms              |      0 |           0 |
| wp              | wp_commentmeta        |      0 |           0 |
| wp              | wp_links              |      0 |           0 |
| wp              | wp_comments           |      0 |           0 |
| wp              | wp_usermeta           |      0 |           0 |
| wp              | wp_ktaisession        |      0 |           0 |
| wp              | wp_term_relationships |      0 |           0 |
| wp              | wp_options            |      0 |           0 |
| wp              | wp_postmeta           |      0 |           0 |
| wp              | wp_users              |      0 |           0 |
| wp              | wp_posts              |      0 |           0 |
+-----------------+-----------------------+--------+-------------+
12 rows in set (0.00 sec)

オープンテーブルステータスの詳細表示

• Opend_table_definition : 累計open_table_definition
• Opend_tables : 累計open_tables

mysql> show global status like 'open%table%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Open_table_definitions   | 202   |
| Open_tables              | 230   |
| Opened_table_definitions | 1292  |
| Opened_tables            | 1328  |
+--------------------------+-------+
4 rows in set (0.00 sec)

備考

• useコマンド : DBに含まれるテーブルすべてを開く。
• ロック情報等を取得する場合はmysqladmin -uroot -p debugでエラーログに出力されるログから判断できる。

mysqladmin debugコマンドの実行例。結果はエラーログに出力されているので、そのログを抜粋。

Current dir: /var/db/mysql/
Running threads: 2  Stack size: 262144
Current locks:
lock: 0x827e9ae60:

lock: 0x829157b60:

lock: 0x829e9a100:

lock: 0x829e9a800:

lock: 0x829e9af00:

lock: 0x829e9b300:

lock: 0x829e9b800:

lock: 0x829e9be00:

lock: 0x829e9c300:

lock: 0x829928a00:

lock: 0x829e10100:

lock: 0x829e10600:

lock: 0x829e10b00:

lock: 0x829e10f00:

lock: 0x829e11100:

lock: 0x829e11e00:

lock: 0x829e12600:

lock: 0x829e12a00:

lock: 0x829e12c00:

lock: 0x829e13100:

lock: 0x829e13500:

lock: 0x829900c00:

lock: 0x829925100:

lock: 0x829925600:

lock: 0x829925900:

lock: 0x829925c00:

lock: 0x829926200:

lock: 0x829926a00:

lock: 0x829927000:

lock: 0x829927500:

lock: 0x829927c00:

lock: 0x829928000:

lock: 0x8292dff00:

lock: 0x8298fd200:

lock: 0x8298fe300:

lock: 0x8298fe800:

lock: 0x8298feb00:

lock: 0x8298ff000:

lock: 0x8298ff400:

lock: 0x8298ffa00:

lock: 0x8298fff00:

lock: 0x829900300:

lock: 0x829900700:

lock: 0x828b84200:

lock: 0x8261be500:

lock: 0x8261bd300:

lock: 0x828b6b500:

lock: 0x828b84800:

lock: 0x828b70c00:

lock: 0x828b6df00:

lock: 0x828b93900:

lock: 0x828b73500:

lock: 0x8298c5b60:

lock: 0x828130460:

lock: 0x828130b60:

lock: 0x828131260:

lock: 0x828131960:

lock: 0x828ed4660:

lock: 0x8285f4360:

lock: 0x8298c7b60:

lock: 0x8298c8360:

lock: 0x828132e60:

lock: 0x8285c2b60:

lock: 0x8285c3360:

lock: 0x828133560:

lock: 0x828b71300:

lock: 0x828b96a00:

lock: 0x828b94500:

lock: 0x828baab00:

lock: 0x828d15a00:

lock: 0x828d13c00:

lock: 0x828b7a800:

lock: 0x828b78100:

lock: 0x828ba9d00:

lock: 0x8261bdf00:

lock: 0x828d83900:

lock: 0x828b77400:

lock: 0x828bc8900:

lock: 0x828bf0700:

lock: 0x828baa400:

lock: 0x828ea6b60:

lock: 0x828164f60:

lock: 0x828166160:

lock: 0x828167360:

lock: 0x8285c8b60:

lock: 0x828f5a960:

lock: 0x82816a960:

lock: 0x8285cb360:

lock: 0x828ea7f60:

lock: 0x8285ccb60:

lock: 0x8281b4b60:

lock: 0x82816f160:

lock: 0x8285fa360:

lock: 0x82814eb60:

lock: 0x82814f360:

lock: 0x82814fb60:

lock: 0x828ed5160:

lock: 0x828151360:

lock: 0x828151b60:

lock: 0x82816a960:

lock: 0x8285cb360:

lock: 0x828ea7f60:

lock: 0x8285ccb60:

lock: 0x8281b4b60:

lock: 0x82816f160:

lock: 0x8285fa360:

lock: 0x82814eb60:

lock: 0x82814f360:

lock: 0x82814fb60:

lock: 0x828ed5160:

lock: 0x828151360:

lock: 0x828151b60:

lock: 0x828152360:

Key caches:
default
Buffer_size:      33554432
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:           281
not flushed:             0
w_requests:          69208
writes:              11221
r_requests:         398863
reads:                  47

handler status:
read_key:       480124
read_next:      919354
read_rnd        112577
read_first:       9451
write:          102550
delete           11859
update:         243647

Table status:
Opened tables:       1332
Open tables:          230
Open files:           211
Open streams:           0

Alarm status:
Active alarms:   0
Max used alarms: 0
Next alarm time: 0

max_connections

最大同時接続数を制御する。スレッド数と同数

Open_files_limit

最大同時オープン可能なファイル数を制御する。

thread_cache_size

スレッドの使い回しに利用。max_connectionsと同じ値とするとよい。なおコネクションプールの場合はそもそもキャッシュが不要と思われるため、アプリの特性は考慮すべき。

Threads_createdがおおきい場合はキャッシュを増やした方がいい。

mysql> show global status like 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 7     |
| Threads_connected | 1     |
| Threads_created   | 19    |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

mysql> show global variables like 'thread%';
+--------------------+---------------------------+
| Variable_name      | Value                     |
+--------------------+---------------------------+
| thread_cache_size  | 8                         |
| thread_concurrency | 10                        |
| thread_handling    | one-thread-per-connection |
| thread_stack       | 262144                    |
+--------------------+---------------------------+
4 rows in set (0.00 sec)

スレッド毎のパラメータについて

ソートについて

ソートアルゴリズムを使う場合は、sort_buffer_sizeを超えた場合はテンポラリファイルを使ってソート結果を保管する。

バッチの際に大量な領域が必要となる場合は、セッション単位で値をおおきくすることも大切。

sort_buffer_size

• order_by利用時に、ソート対象カラムに対してインデックスがついていない場合に利用される。
• インデックス作成時
• Sort_merge_Passesが一つの性能指標。本値はテンポラリファイルを利用したソート回数を表示。0が望ましい。

Sort_merge_Passesの確認方法

mysql> show global status like 'Sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
+-------------------+-------+
1 row in set (0.00 sec)

sort_buffer_sizeの変更方法

mysql > set global sort_buffer_size = xxxxxx;

 

join_buffer

インデックスが使用されないjoin

read_buffer_size

テーブルフルスキャン時に利用される

tmp_table_size

一時テーブルにおけるメモリテーブルの最大値。メモリテーブルはmax_heap_table_sizeも併せて定義する必要有り。

メモリサイズを超えた場合はMyISAM形式でディスク上にテーブルを作成する。これは避けるべき!

Created_tmp_disk_size_tables : ディスク上にテーブルを作成した回数。0とすべき。

参考URL

• 漢(オトコ)のコンピュータ道: Using filesort

監視した方がいいステータスパラメータ

インデックス利用有無や、おかしいSQLの抽出に利用

• Select_full_join
• Select_full_range_join
• Select_range
• Select_range_check
• Questions

ロックの検出

テーブルレベルロックの抽出。行レベル(InnoDB)は別。

• Table_locks_immediate
• Table_locks_waited

そのほか

• Aborted_clients : ネットワーク切断がわかる。
• Binlog_cache_disk_use : 0以外の場合、binlog-cache-sizeをおおきくする。
• Com_ : 各種操作の累計値。
• Created_tmp_files
• Handler_read_first : インデックス全スキャン(TYPE:INDEX)
• Hander_read_rnd/Handler_rnd_next : テーブル全スキャン

binlogの同期書き込み制御

sync_binlog = 1

サーバ変数

設定箇所

• my.cnf : 起動時設定
• コマンドライン引数 : 起動時設定
• SET文 : 起動後動的に設定。global/sessionで定義。global変更後は、新規に接続してきたセッションから有効となる。

変数の確認方法

• @@ : セッションでのサーバ変数
• @ : セッションでのユーザ変数
• GLOBAL : グローバルでのサーバ変数

mysql> select @@sort_buffer_size, @@global.sort_buffer_size;
+--------------------+---------------------------+
| @@sort_buffer_size | @@global.sort_buffer_size |
+--------------------+---------------------------+
|            8388608 |                   8388608 |
+--------------------+---------------------------+
1 row in set (0.00 sec)

チューニングに関係あるサーバ変数

AUTOCOMMIT

デフォルト ON。1件ずつ自動でコミットされる。なおトランザクションはSTART TRANSACTION/COMMIT/ROLLBACKで制御する。

BIG_TABLES

一時テーブルがすべてディスクに作成される。デフォルトOFF。

一時テーブルが常にtmp_table_sizeを超える場合はONとする。メモリの圧迫を押さえることが可能。

FOREIGN_KEY_CHECKS

外部キー制約を制御。デフォルトON。

外部キー制約のチェックに時間がかかる場合にOFFにする。外部キー制約が守られている場合にのみ利用すべし。

SQL_BIG_SELECTS

max_join_sizeを超えてjoinされる場合にSQLを中止する or NOTを制御する。デフォルトON。

SQL_BUFFER_RESULT

クライアントにSQL結果を返却する際に、都度応答(デフォルト)ではなく、一時表にバッファリングして、応答することになる。

SQL_LOG_OFF

一般ログの出力有無を制御。デフォルトOFF。デバッグ時にONにしたい場合等で利用。

SQL_SELECT_LIMIT

SELECTの結果上限を設定。デフォルトは2の32乗?1 or 2の64乗-1

UNIQUE_CHECKS

InnoDBでのユニークキー制約を制御。デフォルトON。データ移行時やバッチ処理時に一時的に設定変更するとパフォーマンスが向上する。

SQL_LOG_BIN

バイナリログのON/OFFを制御。デフォルトON。セッションレベルで制御。

参考URL :

• http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

SHOWコマンド

SHOW TABLE STATUS

テーブル状態を取得

SHOW INDEX <tbl_name>

インデックスを確認する

SHOW OPEN TABLES

オープンしているテーブルを表示する

SHOW ENGIN [INNODB|NDB] STATUS

ストレージエンジンの状態を取得する。

27 1月 11 MySQLお勉強メモ(MyISAMストレージエンジン)

特徴

ファイル形式

• table_name.frm : メタデータ
• table_name.MYD : 実データ
• table_name.MYI : インデックス
  • ヘッダ
  • インデックスブロック

ロック

テーブル単位。ロック方式は以下。

• READ LOCAL(SELECT/INSERT OK。UPDATE/DELETE NG)
• READ(SELECT OK。INSERT/UPDATE/DELETE NG)
• WRITE(すべてNG)

Concurrent INSERT

mysql > SET GLOBAL concurrent_insert = xで制御

• 0 : 利用しない
• 1 : MYDファイルに穴がない場合にSELECT中にINSERT可能
• 2 : データの最後にAPPENDする。SELECT中にINSERT可能

そのほか

• ロックがかかっている時に他SQL(SELECT/UPDATE等)が入ってくると、更新系SQL -> 参照系SQLという順番で実行される。
• 参照系を優先したい場合はSELECT HIGH_PRIORITY/INSERT LOG_PRIORITY/DELETE LOW_PRIORITYとSQLを記載することで参照系が優先される。
• INSERT HIGH_PRIORITYをつけると、更新系の中で優先度が上がる。

トランザクション

非対応

バックアップ/リカバリ

O/Sコマンドで実施

そのほか

• FULLTEXTインデックス
• 共有ロック時にINSERT（Concurrent INSERT)
• インデックス先頭部分を圧縮(自動)
• GIS空間インデックス(R-Tree。2次元地図データ等で利用)

得意分野

• 参照向き(全文検索)
• ログ目的のテーブル
• Bulk insertが早い
• トランザクション対応が不要
• データウェアハウス

不得意分野

• ACID特性を要求する場合
• クラッシュリカバリの一貫性
• クラッシュリカバリの自動化
• クラスターインデックスではない
• 外部キーの使用(非サポート)

データ格納方法

MyISAMはデータが登録された順番でAPPENDされていくイメージ。

• Fixed : すべて固定長のデータ型。フラグメンテーションが発生しにくい。
• Dynamic : 1つでも可変長のデータ型。フラグメンテーションが発生しやすい。
• 圧縮 : myisampack。MYDデータを直接圧縮する。mysqlserverは経由しない。myisamchkを利用して、インデックスの再作成が必要。実行時にはテーブルロックが必要。圧縮後はRead Onlyとなる。

キーキャッシュ

インデックス(MYI)をメモリに乗せる仕組み。データ領域はメモリに乗らない。

キャッシュインデックスを特定のキーバッファに与えることが可能。

mysql > SET GLOBAL hogehoge_cache.key_buffer_size = xxxxxx;
mysql > CACHE INDEX t1, t2, t3 IN hogehoge_cache;

キーキャッシュにロードすることも可能

mysql > LOAD INDEX INTO CACHE t1, t2 [IGNORE LEAVES];

キーの保存・削除アルゴリズムは2段階LRU(Hot sub-chain/Warm sub-chain)

ステータス確認

mysql > show global status like 'key_%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| Key_blocks_not_flushed | 0      |
| Key_blocks_unused      | 26514  |
| Key_blocks_used        | 281    |
| Key_read_requests      | 519103 |
| Key_reads              | 47     |
| Key_write_requests     | 87632  |
| Key_writes             | 14052  |
+------------------------+--------+
7 rows in set (0.00 sec)

値の説明

• key_blocks_unused : 使われていないキー領域
• key_blocks_used : 使われているキー領域
• key_read_requests : キーブロックから読み込んだ
• key_reads : ディスクまでデータを読みに行った
• key_write_requests : キーへの書き込み要求
• key_writes : ディスクまで書き込んだキーの数

キーキャッシュに指定する値

• キーキャッシュサイズの値は、MYIファイルサイズを指定すると、インデックスが全部メモリに乗る。
• MyISAMを使わない場合は、最小の値にする(デフォルトでいい)

チューニングポイント

• bulk_insert_buffer_size
• myisam_sort_buffer_size : インデックス構築時のソート処理に利用
• myisam_max_sort_file_size : インデックス構築時のソート時に利用されるテンポラリテーブルの最大サイズ
• myisam_recover = <option>
  • DEFAULT : テーブルオープン時に自動リカバリ
  • BACKUP : 修復前後のファイル両方を保存
  • FORCE : データ欠損が存在する場合でも修復する
  • QUICK : 簡易チェックする

そのほか最適化手段

• Not NULL宣言
• テーブル最適化(optimize table)
• ALTER TABLE t [DISABLE | ENABLE]KEYS; : 一時的にインデックスを無効/有効できる。大量更新時にKEYを無効にし、処理終了後、インデックスを更新した方が早い場合もある。
• カラムタイプは固定長の方が早い

26 1月 11 MySQLお勉強メモ(キャッシュ編)

MySQL お勉強メモ(キャッシュ編)です。

キャッシュの種類

MySQLでは以下のキャッシュ機構を持っている。

• hostname cache
• privilege cache
• table cache
• key cache
• query cache
• innodb_buffer_pool

query cache

概要

Oracle 11gのリザルト・キャッシュ相当の機能。クエリキャッシュはSQL・実行結果(データセット込)をキャッシュしているため、ストレージエンジンまでアクセスしない。

クエリキャッシュの特性は以下。

• クエリキャッシュはテーブル毎に持つため、テーブルで他レコードが1行でも更新された場合でもクエリキャッシュはフラッシュされる。
• 参照が多いページに対してのみに利用した方がよいと思われる。
• 特定のクエリだけクエリキャッシュに入れることも可能(DEMAND機能)

キャッシュの対象から除外されるものは、以下

• ユーザ定義関数
• ファンクション
• SQL関数
• 結果がおおきい

クエリキャッシュ設定

サーバ変数を確認する。

mysql> show global variables like 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

• query_cache_size : キャッシュするクエリ結果のサイズ
• query_cache_limit : キャッシュするクエリ結果の最大値
• query_cache_min_res_unit : クエリキャッシュのブロックサイズ

クエリキャッシュの確認方法

サーバステータスを確認する

mysql> show global status like 'Qcache_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

• Qcache_free_blocks : 空きブロック。数がおおきいとフラグメンテーションの可能性がある。
• Qcache_free_memory : 空きメモリ
• Qcache_hits : キャッシュヒット数
• Qcache_inserts : キャッシュに挿入された数
• Qcache_lowmem_prunes : メモリ不足のためクエリキャッシュから削除された
• Qcache_not_cached : 除外制限に該当した
• Qcache_queries_in_cache : 今入っているクエリの数
• Qcache_total_blocks : キャッシュのトータルブロック数
• Com_Select : Qcache_inserts + Qcache_not_cached

ヒット率の計算方法

• キャッシュヒット率 = Qcache_hits/(Com_Select + Qcache_hits)

クエリキャッシュのベストプラクティス

• 更新クエリが存在する場合は利用しない!
• 更新クエリが存在する場合、参照テーブルだけで利用する
• Qcache_lowmem_prunesがおおきい場合は、クエリキャッシュサイズが小さいので、サイズをおおきくする!
• Qcache_not_cachedがおおきい場合は、除外される原因を特定する。query_cache_limitで引っかかっている場合はおおきくする等を検討する。ただしおおきくするとメモリから削除されるデータが増える場合もあるため、計測は必要

覚えたSQL

ステータスのフラッシュ

mysql > flush status;

クエリキャッシュのリセット

mysql > reset query cache;

クエリキャッシュのパラメータ変更

mysql > set global query_cache_size = xxxxxxx

参考

• [ThinkIT] 第6回：query_cache_sizeの違いによるパフォーマンス比較 (1/3)
• MySQLの高速化 – FreeStyleWiki