September 29, 2005

MySQLの管理など

今回も前回に引き続き、ただいま受講中のMySQLトレーニングの実況中継(4日目)。

いつも応援ありがとう!


【参考】

MySQL Certification Study Guide

今日のトレーニングは、MySQLのログ、トランザクション、バックアップ、レプリケーションについて。

実践に役立ちそうな技から、試験ぐらいでしか使わなそうな技まで、気になったものをトレーニングを受けながらリアルタイムに追記していくので、もし今日の分をまとめて見たいあなたは、18時以降にまたどうぞ。

  1. 一般クエリーログをオン(デフォルトはオフのまま)にすると、発行されたクエリーを記録できるが、パフォーマンスが10%ほど低下する。

  2. スロークエリーログを使うと、実行の遅いクエリーやインデックスを使用していないクエリーを記録できる。

  3. 基本的にMyISAMの方がselect、insertなどの動作がInnoDBよりも速い。

  4. MyISAMはテーブルロック、InnoDBは行ロックがサポートされている。

  5. InnoDBのトランザクションは、マルチバージョニングで実装されているため、更新中の読み取り待ちがない。

  6. InnoDBのトランザクション分離レベルは、REPEATABLE READがデフォルト(Oracleと同じ)。

  7. InnoDBの行ロックは、基本的にプライマリキーが張られていないと機能しない。

  8. select 〜 lock in share modeで共有ロックがかけられる。ロック対象は、参照された行のみ。

  9. select 〜 for updateで排他ロックがかけられる。ロック対象は、参照された行のみ。

  10. select 〜 for updateによる排他ロックは、現在存在しないがロック対象の行に該当するであろうキー行に対しても自動的にロックをかける(ネクストキーロックという機能)。

    クライアントA
    mysql> select id from city;
    +----+
    | id |
    +----+
    | 1 |
    | 5 |
    | 10 |
    ...
    mysql> begin;
    mysql> select * from city where id < 1 and id > 10 for update;


    クライアントB
    mysql> begin;
    mysql> insert city (id) values( 7 );
    ↑このinsertクエリーはクライアントAがコミットもしくはロールバックするまでブロックされる


    詳しくは、ネクストキーロック: ファントムの問題の回避を参照。

  11. MyISAMテーブルは、最悪.MYDファイルさえあれば、何とか復旧できる。

  12. check tableやrepair tableを定期的に実行するといい(MySQL ABでもcronで週一チェックしている)。

    mysql> check table city, country, countrylanguage;
    +-----------------------+-------+----------+----------+
    | Table | Op | Msg_type | Msg_text |
    +-----------------------+-------+----------+----------+
    | world.city | check | status | OK |
    | world.country | check | status | OK |
    | world.countrylanguage | check | status | OK |
    +-----------------------+-------+----------+----------+
    3 rows in set (0.04 sec)

    mysql> repair table city, country, countrylanguage;
    +-----------------------+--------+----------+---------------------------------------------------------+
    | Table | Op | Msg_type | Msg_text |
    +-----------------------+--------+----------+---------------------------------------------------------+
    | world.city | repair | note | The storage engine for the table doesn't support repair |
    | world.country | repair | status | OK |
    | world.countrylanguage | repair | status | OK |
    +-----------------------+--------+----------+---------------------------------------------------------+
    3 rows in set (0.02 sec)

    ※check tableはMyISAM、InnoDB共に有効だが、repair tableはMyISAMだけが有効。

  13. mysqld起動時に--myisam-recoverオプションを指定することで、MyISAMもInnoDBと同じように自動テーブルチェックが有効になる。

  14. optimize tableでテーブルを最適化するだけでMyISAMはパフォーマンスが格段にアップするらしい(特にデータ更新が頻繁なテーブルの場合)。

    mysql> optimize table city, country, countrylanguage;
    +-----------------------+----------+----------+----------+
    | Table | Op | Msg_type | Msg_text |
    +-----------------------+----------+----------+----------+
    | world.city | optimize | status | OK |
    | world.country | optimize | status | OK |
    | world.countrylanguage | optimize | status | OK |
    +-----------------------+----------+----------+----------+
    3 rows in set (0.65 sec)

  15. InnoDBに対してoptimize tableをかけた場合、かけた直後はパフォーマンスが低下することがある(内部的には、alter tableでテーブルを作り直している)。

  16. インデックスだけを最適化したいときは、optimize tableの代わりにanalyze tableを使うといい。
    mysql> analyze table city, country, countrylanguage;
    +-----------------------+---------+----------+-----------------------------+
    | Table | Op | Msg_type | Msg_text |
    +-----------------------+---------+----------+-----------------------------+
    | world.city | analyze | status | OK |
    | world.country | analyze | status | Table is already up to date |
    | world.countrylanguage | analyze | status | Table is already up to date |
    +-----------------------+---------+----------+-----------------------------+
    3 rows in set (0.01 sec)


  17. バックアップの手段はmysqlhotcopyやファイルの直接コピーなどいろいろあるが、運用中のホットコピーをMyISAM、InnoDBの区別なしに行う場合は、mysqldumpが最も適している。

  18. mysql_multiを使うと、1台のサーバーで複数のMySQLを起動するのが簡単になる。

  19. MySQLのレプリケーションは、insertのラッシュ時にマスターが落ちると、スレーブにちゃんとデータが渡らないケースがある(負荷分散、バックアップ目的にはOKだが、冗長性確保目的には微妙)。



mysqler at 16:09│Comments(0)TrackBack(0)

トラックバックURL

この記事にコメントする

名前:
URL:
  情報を記憶: 評価: 顔