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

公開日：2017/09/04

昨今の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様に自転車の話題をいれると対応がはやくなるという噂があるので、いつか検証してみたいと思いました。

MySQL MySQL

もぐめぽろぐ

日々暮らしていく中での備忘録をつづっていく場所です