MariaDBを試してみる！・・といってもインストールの話

こんばんわ

勉強というか、お仕事でとある、IBMのデータベースサーバをMySQL 5.5に切り替えるなんていうプロジェクトを進めていました。IBMのデータベースサーバの構成を、MySQLにマイグレーションするのって、初めは簡単だと思っていたのですが、蓋を開けて見れば、 ・文字コードの問題（euc-jp → utf-8） ・制御文字の問題（いわゆるバックスラッシュ） ・スキーマの概念の違い（IBM と MySQLだと、スキーマの概念が違いすぎる） ・そしてそして・・データの移行（合計ウン十台の移行）、データ全部合わせても、数十億件くらいいくのかな？スキーマの数もインスタンスの数もも多すぎでもうよくわかんない・・。

当初、私の見積もり的には、DDLの変更くらいしか思っていなく、２週間とか答えたのですがｗ、結局１年半くらいかかっちゃいました。今考えたら、２週間の根拠ってなんだったのだろう・・と思っています。(T_T)

さてさて・・・ 本題のMariaの話をしないと・・

その一年半の間は、MySQL 5.5ベースで移行設計をしていた関係で、MySQLが5.6にバージョンアップしてしまい、もはや検証をとるなんてことができなかった今現在の状態であります。なもんで、IBM DB → MySQL化のミッションが完了した途端、今度はバージョンアップも検討しなきゃいけないのかと・・・とほほ・・まあ、やることあるのは良いことなんですけど(^_^;) DBが変わって、運用も変わって、バージョンアップも検討して、障害対応も考えて・・・あぁあ・・辛い・・。

[ad#google-ad1]

MySQL 5.6の検証を進めている最中で、MariaDBが元気よくなってきたので、今、気持ちがそっちに傾いている感じです。 気持ちが動いている理由としては、

・MySQLと互換性がある

・互換性はあるけど、魅力的な、ストレージエンジンがはじめからインプリメントされている

・特に大きな改修をしなくても、RDBMSを切り替えることができる

なんていう理由から、レプリケーションを駆使して、MySQLと連携とることができそうだな？と思いつつ、私自信の楽な道を探しているのです。

さっそくですが、MariaDBのインストールについて説明していきたいとおもいます。

インストール手順

インストールは説明するまでもなく簡単です。文書が長いのは、my.cnfの内容をそのままコピペしているだけなので、軽い気持ちで読んでください。

* GPG-KEYの設定 [shell] rpm --import http://yum.mariadb.org/RPM-GPG-KEY-MariaDB [/shell]

* リポジトリの設定 baseurlの部分は、http://yum.mariadb.org/ を参照いただき、適切なバージョンの適切な環境で設定してください。 今回、私の環境は、CentOS release 5.7 (Final) でしたので、以下の通り設定しました [shell] cat << EOT > /etc/yum.repos.d/mariadb.repo [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/5.5.30/centos5-amd64/ gpgkey=http://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 enabled=1 EOT [/shell]

* MariaDBのインストール [shell] yum install MariaDB-devel MariaDB-client MariaDB-server [/shell]

* MariaDBの起動 [shell] /etc/init.d//usr/bin/mysql start [/shell]

* コンソールをたちあげて見る。（素敵！カレントスキーマが表示されている！） [shell]

mysql

Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 67 Server version: 5.5.30-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use test; Database changed MariaDB [test]> [/shell]

* ストレージエンジンの確認

[shell] MariaDB [test]> show engines; +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | 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 | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ 10 rows in set (0.00 sec) [/shell] MariaDBを採用したい理由に、このストレージエンジンがあります。私個人的には、FEDERATEDとInnoDBですね。。もちろんMySQLにもありますが、この２つは中身が違います。 InnoDBは、「Percona-XtraDB」を実装、FEDERATEDは、「FederatedX」に対応しております。XtraDBは、MySQLのInnoDBよりも性能が良いストレージエンジン、FederatedXは私の記憶では、異なるデータベースのリンクが行えるすごいやつだったかと思います。ここでは説明しませんが、異なるデータベースのフェレでレーションが貼れるかを今度検証してみたいとおもいます。

* コンフィグ情報の書き換え

デフォルトの設定のまんまなんで、OSの環境に併せて、設定変更をしたいとおもいます。テンプレートファイルは、MySQLと同様以下のディレクトリに格納されております。 [shell]

pwd

/usr/share/mysql [/shell]

• my.cnfの設定がmy.cnf直書きから、includeする形式になったっぽいので [shell]

  cat /etc/my.cnf

  #

  This group is read both both by the client and the server

  use it for options that affect everything

  # [client-server]

#

include all files from the config directory

# !includedir /etc/my.cnf.d [/shell] MySQLで動かしている時のmy.cnfをそのまんま、/etc/my.cnf.d/server.cnfに転記しました。MariaDBのメリットを生かしきれていないかもしれません・・。

• 以下の設定は、2GBのRAMと、CPUのコアが1つ。。という構成でやっております。 [shell] #

  These groups are read by MariaDB server.

  Use it for options that only the server (but not clients) should see

  #

  See the examples of server my.cnf files in /usr/share/mysql/

  #

this is read by the standalone daemon and embedded servers

[server]

this is only for the mysqld standalone daemon

[mysqld]

IDが他、サーバと重複しないように注意

server-id = 1810030

wait_timeout=28800 skip-name-resolve federated = 1 log-bin-trust-function-creators=1

port = 3306 socket = /var/lib/mysql/mysql.sock

sql_mode = 'IGNORE_SPACE,PIPES_AS_CONCAT'

character-set-server = utf8 collation-server = utf8_general_ci init-connect = SET NAMES utf8 lower_case_table_names=1

max_connections = 1500 max_connect_errors = 10 table_open_cache = 15000 max_allowed_packet = 16M binlog_cache_size = 128M max_heap_table_size = 100M read_buffer_size = 128M read_rnd_buffer_size = 4M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 200 max_heap_table_size = 204857600 thread_concurrency = 2 query_cache_size = 64M thread_stack = 192K

SERIALIZABLE(直列可能）

REPEATABLE-READ (反復可能読み取り)

READ-COMMITTED (コミット読み込み)

READ-UNCOMMITTED (未コミット読み取り)

transaction_isolation = READ-COMMITTED

構文実行中にサーバによって自動的にディスクに作成された一時テーブルの数です。

Created_tmp_disk_tables が大きい場合、原因の一時テーブルをディスクベースの変わりにメモリベースにさせるように、

値 tmp_table_size を増やしたいでしょう。

tmp_table_size メモリ内のテンポラリテーブルがこのサイズを超えると、

MySQL は自動的にこれをディスク上の MyISAM テーブルに変換する。詳細な GROUP BY クエリを頻繁に行い、

メモリに余裕がある場合は、tmp_table_size 値を大きくする。

tmp_table_size = 100M

バイナリログのベースファイル名を指定します

log-bin=mysql-bin

ログのインデックスファイル名を指定します。

log-bin-index=mysql-bin

マスタからのリレーログを指定します

relay-log=relay-bin relay-log-index=relay-bin

バイナリログの有効期間

expire_logs_days = 7

binary logging format - mixed recommended

バイナリログのフォーマットを指定します。

ROW ― は行ベース レプリケーションをデフォルトに設定。

STATEMENT ― はステートメント ベース レプリケーションをデフォルトに設定。 MySQL 5.1.11 以前のフォーマット。

MIXED ― はミックス ベース レプリケーションをデフォルトに設定。 MySQL 5.1.12 以降のフォーマット。

binlog_format=mixed max-binlog-size = 256M

スレーブ側のログも吐き出しますよという設定

レプリケーションの設定で、従属的な、レプリケーションを構成している場合などに有効な設定

dboss_check_point

log_slave_updates

ワーニングもエラーログに出力する

log_warnings

スロークエリーログを出力する

スロークエリーログの単位については、long_query_timeに依存

slow_query_log long_query_time = 0.5

索引を利用しないSQLもスローログ出力する（TABLE SCAN）

log_queries_not_using_indexes = 1

#

InnoDB(Xtra) 関連

#

InnoDBの内部データなどを保持するための領域です。

それほど大量に割り当てる必要はありません。足りなくなったらエラーログにその旨、警告が出ますので、

そしたら増やせばいいのではないかと思います。

innodb_additional_mem_pool_size = 16M

InnoDB テーブルのデータとインデックスを保存するためのバッファの大きさ。サーバ全体で共有される。

InnoDB のみの MySQL 専用サーバなら、RAMの 70-80% 位まで上げてもよいらしい。

MyISAM の key_buffer_size よりも、性能にシビアに影響する。

innodb_buffer_pool_size = 3192M

物理メモリ(MB) * 0.7 = innodb_buffer_pool_size

innodb_buffer_pool_size = 1407M

innodbのデータ格納方法を指定する、デフォルトは自動拡張

10M 毎に自動拡張する

innodb_data_file_path = ibdata1:10M:autoextend

300GBをあらかじめ割り当てる、自動拡張はしない

innodb_data_file_path=ibdata1:300G

テーブルスペースを異なるパスに分散する（商用DBでいうところのコンテナの概念に似ているのかな？）

innodb_data_file_path=ibdata1:300G;/data2/ibdata2:700G

innodb_data_file_path = ibdata1:10M:autoextend

テーブルごとにテーブルスペースを作成する設定

innodb_file_per_table

たくさんの遅延があるときの助けとなるパラメータ（SATA writeback キャッシュなしで O_DIRECT を使っているとき、

ハードウェア RAID write cache なしでO_DIRECT を使っているとき、ネットワーク接続のストレージを使っているとき）

innodb_write_io_threads = 8 innodb_read_io_threads = 8

InnoDBが内部的に利用するスレッド数を指定する

innodb_thread_concurrency = 16

innodb_flush_log_at_trx_commitは、InnoDBのログバッファ

（注3）をInnoDBログファイル（注4）に書き込むタイミングと、

InnoDBログファイルをディスクにフラッシュするタイミングとを指定するパラメータです。

--------------------------------------------------------------------

設定値 ログバッファ→ログファイル ディスクフラッシュ

--------------------------------------------------------------------

0 毎秒 毎秒

1 （初期値） COMMIT時 COMMIT時

2 COMMIT時 毎秒

--------------------------------------------------------------------

たとえ「パフォーマンス向上のために」という理由があっても、安易に1以外にしてはいけません。

よほど特別な理由がない>限り1のままにしておくべきです。

なぜこれほどまでに1以外をすすめないのでしょうか？ それはInnoDBのデータファイルがディスクにフラッシュされるタイミングを知れば納得していただけると思います。メモリ上にあるInnoDBバッファプールがディスク上のInnoDBデータファイルにフラッシュされます。バッファプールは初期化パラメータinnodb_buffer_pool_sizeで# 大きさを指定するもので、データファイルはinnodb_data_file_pathで指定する最終的にデータが格納されるディスク上のフ>ァイルです。

innodb_flush_log_at_trx_commit = 1

InnoDBの更新ログを記録するメモリ上の領域です。

たいていは8MB、多くても64MBで十分で、あんまり多くする必要はありません。

なぜなら、バッファはトランザクションがCOMMITされるごと、

または毎秒ディスクにフラッシュされるので、ほかのパラメータを厚くしたほうが得策です。

innodb_log_buffer_size = 8M

データの書き込み、得に大きなサイズのデータの書き込み性能に影響する。

ただし、大きなサイズほどデータのリカバリ時間を要する。サイズを変更する場合、正常停止後、# 作成済みのログファイル(デフォルトは /var/lib/mysql/ib_logfileX)を削除し、mysqlサーバを起動しなおす必要がある。

innodb_log_file_size = 256M

バッファープール内のダーティページをパージするクリーンナップする割合(%)を指定

innodb_max_dirty_pages_pct = 90

InnoDB トランザクションがロール バックされる前に、ロックを待つ秒数でのタイムアウト。

InnoDB は自動的にそれ自体のロック テーブル内でトランザクション デッドロックを検出し、

トランザクションをロールバックします。InnoDB は LOCK TABLES ステートメントを利用してロック

セットを通知します。デフォルトは50秒です。

innodb_lock_wait_timeout = 120

this is only for embedded server

[embedded]

This group is only read by MariaDB-5.5 servers.

If you use the same .cnf file for MariaDB of different versions,

use this group for options that older servers don't understand

[mysqld-5.5]

These two groups are only read by MariaDB servers, not by MySQL.

If you use the same .cnf file for MySQL and MariaDB,

you can put MariaDB-only options here

[mariadb]

[mariadb-5.5] [/shell] * 一旦ログファイルを削除します [shell] [/shell]

• ここで一度MariaDBを再起動します [shell]

  /etc/init.d/mysql restart

  [/shell]

インストール後の確認

ストレージエンジンを再度確認

[shell]

MariaDB [(none)]> show engines; +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | 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 | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ 10 rows in set (0.00 sec) [/shell]

プラグインの確認 うん、こちらも大丈夫そうです。(^_^) [shell] MariaDB [(none)]> show plugins; +--------------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +--------------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL | | Aria | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_UNDO_LOGS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLE_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEX_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES_BLOB | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_ADMIN_COMMAND | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CHANGED_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +--------------------------------+----------+--------------------+---------+---------+ 42 rows in set (0.00 sec) [/shell]

文字コードの確認 my.cnfで設定した文字コードがちゃんと反映されているか確認します、大丈夫なようです。 [shell] MariaDB [test]> show global variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) [/shell]

いかがだったでしょうか？まだ、細かな検証をしていないので、本当にこれでよかったのかどうか、まだわからない状態ではありますが、今後色々と試してみようと思います。