Enterprise向けの観点でのMySQLの紹介

昨今のMySQLは無償で使えるコミュニティー版が使われることが多いですが、先日、Enterprise向けに提案する場合のMySQLの機能やOracleとの比較といった側面でOracleの杉山様より講義をしてくださる機会がありましたので、その際のメモを残しておこうと思います。

概要

MySQLの種類など

• Oracle/MSSQLと比べると、コア課金になるが、無料MySQLもあり、有償MySQLでも物理サーバ課金になるため、コストメリットが高い
• 1物理サーバ単位、SE：24万円 EE:60万
• 仮想サーバに対しても1物理サーバに対しての課金になる
• AWSなどのクラウドサービスで利用するときは、1vmあたり、1subscriptionといった形で案内している
• MySQLのEditionはSE, EE、ClusterCGEの3種類がある
• 有償サポートはパッチのサポートや、DBのチューニングや正規化などのコンサルの問い合わせなどが含まれている
• 某カード会社が１テーブルあたり１５T のデータを取り扱っている
• InnoDBは64TBまでは扱える
• パーティションは使用している
• パーティションはEEでなくでも使用は可能

バックアップ

• MySQL Enterprise Backup は物理バックアップ
• mysqldumpはテキストにシングルスレッドで変換するため非常に時間がかかる
• 80GのDBだと3時間かかる場合もある
• EnterpriseBackupは物理ファイルをlsnというログを使って整合性がある物理バックアップを取得する
• デフォルトだと8スレッドで動きます。
• 増やせば増やすほどバックアップは早くなる

アーキテクチャ

• Oracleの場合はredoログからアーカイブログを出力するが、MySQLはInnodbの場合、InnoDBログとバイナリーログを出力する
• InnoDBログにコミットした内容が出力され、クラッシュした場合はInnoDBからリカバリし、ロールフォワードする場合はバイナリーログをしようする。
• PITRする場合、MySQLの場合はフルバックアップからもどして時間を指定して戻す。
• my.cnfは動的に変更はできないが、8.0から動的変更ができるようになります。

###UNDO領域

• undologの中に更新前行データや古い行データを保存している
• Start transactionしたら、そのときのデータをSELECTされるのがデフォルトになる。
• UndoのパスをDB作成時に変えられるが、5.7未満はパスを一度設定したら変えられないので注意
• Innodb_undo_directoryとinnodb_undo_tablespacesを変えることで分散して作れる
• 5.7からある程度サイズがおおきくなるとUNDOをトランケートする機能が入っている
• パフォーマンス気にするならある程度数をもっていたほうがいい
• ベンチマークするときはUNDOの数を6個にしたりしている

General Tablespace

• Create tablespaceというコマンドでtablespaceが作成できる
• create tableする際にtablespaceを指定して使用することもできる

Innodb

• Innodb_log_group_home_dirを指定するとそこにredoログが出力される
• 複数のhome_dirは設定できません。
• data_dirとinnodb_undo_directoryはSSDにおき、innodb-log-group-home-dir, log-binなどはシーケンシャルなログは別のところに出力したりします。
• デッドロックしたらタイムアウトしたものをlog-errorに出力したり、おかしいSQLのwarningを出力したりします
• mysqlにもstatspackに近いものはあります。

ストレージエンジン

• デフォルトはInnoDB
• トランザクション、行ロックなどが使えます
• マテビューはないです。
• MEMORYストレージエンジンを使うことがおおい
• 再起動するとなくなるが、定義は残っています。
• データを永続的に8年間残さないといけないとなったらARCHIVEストレージエンジンを使う
• SELECT/INSERTしか使えない
• INSERTの性能が高い
• デフォルトでglib圧縮なので、80%圧縮される
• 監査ログなどに向いている
• あくまでデータ蓄積用なので、デメリットとしては、indexがつけれないです。
• データが大きくて参照できない場合はパーティションを使う。
• PKはハッシュでできる
• general_logのテーブルもARCHIVEストレージエンジンにできるが、全てのログのクエリーをとってしまうので、常にONにするお客はいない
• 監査プラグインをいれたほうがいいです。

Audit

• 特定ユーザの操作ログをとるといった設定が可能
• jsonで設定する
• ユーザごと、オブジェクト単位、SELECT/UPDATE~なのか、などで監査を取得できる
• OracleでいうFGA監査
• オーバーヘッドは非常に少ない
• AuditVaultとの連携も可能
• 監査プラグインはEEじゃないと使えないです。
• 監査プラグインを有効にするにはモジュールのインストールが必要になる
• EnterpriseMonitorが監査ログを監視しており、何かあればすぐ発見することができる。

EnterpriseWorkbench

• SQLの実行計画をWorkcenchを使うと駆動表がどうなっているかなどを確認することができる
• リバースエンジニアリングもできる
• ER図をかいてそのままドキュメントに起こすのもできる
• Performance Reportsを使うとsysスキーマの情報を確認できる
• 1回も使われていないindexなどが確認できる

EnterpriseMonitor

• 負荷が高くなった時、その時間帯のSQLを確認できます
• 処理の遅い順にSQLを表示したりできる
• CSV、画像などの出力も可能
• お客の事例としてはwgetで定期的に落としたりしている。
• temp tablesが足りなくなると、ディスクに書き込みが発生するが、Monitorでディスクに書き込んだ回数などが確認できるので、その使っている部分からSQLを改善したりする
• ソートバッファ上で処理しきれなくなるとMerge Passes列がカウントアップしていく
• アプリケーションが初めて実行したSQLの日付を確認できる
• 設定ファイルが正しく設定できるかどうかはアドバイザ機能を確認すると確認できる
• 単体でのアップデートもあります
• アップグレードのバイナリとupgradeコマンドを使って、アップデートもできる
• Snmpとメールに対応しているので、アラート発砲ができます
• EnterpriseMonitorは別サーバにいれます
• サーバスペックは監視する規模感による
• 情報の取得はwith agent/ agent lessと2つある。
• Agent less: モニターのサーバから各DBに値をとりにいく
• サーバが増えてもagentを入れる必要はない
• Mysql-3306のtcpプロトコルを使う
• agent with: agentから管理ノードにデータを送る。その際はhttpsで送る
• agentを使えば管理ノードが止まっているときでもデータをためておくことができる
• Cpu/memoryなど、OS側のリソースもモニタリングできる

performance_schema

• oracleでいうv$表みたいなものとして、MySQLにはsysスキーマというものがあり、その中に、過去に遅いSQLがどれくらい実行できるかというのを確認できる(sys.statement_analysis)
• バッチの前だけメモリの設定を変更などができるので、一部チューニングしたりすると早くしたりとかができる。
• sysスキーマの設定は再起動するとなくなるので、永続保存した場合はEnterpriseMonitorを使ってください
• 最長6秒、3秒おきにどれくらいbackgroundでSQLが使われているのか確認できる
• call sys.diagnostics(6, 3, ‘current’)

バージョンアップについて

• MySQLもマイナーバージョンアップが2ヶ月に1回あるが、新機能なども入り込んでいたりする
• 毎回バージョンアップをする人はいない
• メジャーバージョンアップは2年に1回
• パッチ当てはグループレプリケーションだと楽
• マイナーバージョンアップだと中身は殆ど変わらない
• ロールバックは簡単です。
• バージョンアップはバイナリのシンボリックリンクを切り替えるだけで簡単にバージョンアップできます
• バックアップは事前にお願いします。
• バージョンアップするときにSysスキーマの更新が入ります
• クラスタなどはとめないでローリングアップグレードできる

その他

• サポートはソースコードレベルまで対応している
• @nippondanji様にはSRを上げるときに自転車の話題をいれると対応がはやくなるという噂
• FXで使っている事例もある
• 金融庁で使っていたりもする
• MySQLはPCIDSSの承認は得たりはしていない
• サードパーティの製品を使ったりするのもいい

冗長化

レプリケーション

• RMANと同じことがしたければレプリケーションの構成を使ったほうがいい
• mysqlbinlogコマンドを使うと、特定のバイナリログを継続的にうけとることができる
• バイナリログをネットワーク越しにrelay logとして送ってレプリケーションを行う
• レプリケーションは非同期レプリケーションと準同期レプリケーションの2種類があります
• ackを返すタイミングが違う
• 完全に同じデータをもちたければ準同期レプリケーションが良い
• 準同期のAckの待ち時間に関してはデフォルトで10秒のタイムアウトがあり、もしタイム・アウトしたら自動的に非同期レプリケーションに変わる
• ただしその後、ackがうまく帰ってきたら準同期レプリケーションに変わる

グループレプリケーション

• アプリケーションも含めて冗長化を含みたい場合はinnodb clusterを用いると良い
• マルチマスターモードはどのノードからも書き込みができるが、制約が多いのでおすすめはしない
• シングルマスターモードがおすすめ
• マスターが死ぬと、自動的に切り替えをしてくれる
• アプリケーションからDBへの接続はMySQL Routerというものを使うと、グループレプリケーションへの接続を自動的に管理してくれる
• アプリ側で2回処理をするように作り込む必要があるためMySQLクラスタよりも少しレベルは下る

MySQL Cluster

• 自動的にシャーディングしてくれる
• ノードグループの中のノードとして4ノードまでコピーできるが、オフィシャルでは2ノードサポートしているが、3ノードまでサポートできるように準備中。

@mogmetの所感

段々と機能的にもOracleに近づいてきている印象を受けました。今後ますます便利に使えるようになっていくと思われるので楽しみです。

また、サポートが非常に手厚く、コードやSQLまでレビューしてくれるのは非常に魅力的なサポートと思いました。よく、パフォーマンスが出なくて困ったりすることが多いと思うのですが、こういうときに専門知識を持ったサポートの力を借りれると非常に助かるかとおもいます。

サポートを使用するときは、@nippondanji様に自転車の話題をいれると対応がはやくなるという噂があるので、いつか検証してみたいと思いました。