introduction to xtradb cluster
DESCRIPTION
2013/03/23 Chiba.pm #2の資料です。 辞書首っ引きで書いたので英語はかなりテキトーです。 日本語に直そうとしたら機械翻訳っぽくなったのでやめました。TRANSCRIPT
![Page 1: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/1.jpg)
XtraDB Cluster
Chiba.pm #2 at 2013/03/23yoku0825
![Page 2: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/2.jpg)
\こんにちは!/• I’m yoku0825, working as DBA for the
company’s web services.• Husband of my wife :)• Father of my sun :)• I love MySQL too match, such as I can’t log-in
PostgreSQL and Oracle :(• Maybe Perl Monger, seldom wrote codes but!!
![Page 3: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/3.jpg)
Today, I talk aboutPercona XtraDB Cluster
![Page 4: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/4.jpg)
Today, I talk aboutPercona XtraDB Cluster
ぺるこなえくすとらでぃーびーくらすたー
![Page 5: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/5.jpg)
Do you know Percona LLC?
Famous their work are Percona-Toolkit,for MySQL Utility Tools,
and Percona-Server,extended MySQL clone.
http://www.percona.com/
![Page 6: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/6.jpg)
XtraDB Cluster is implementation of Galera Replication by Percona LLC.
What’s Galera Replication?ATO-DE-MATA-DETEKIMASU.
![Page 7: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/7.jpg)
取り敢えず響きが良いですよね
エクストラディービークラスター
![Page 8: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/8.jpg)
ベンチマーク無双してる頃に会社の食堂に行って
![Page 9: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/9.jpg)
「あ、 XtraDB 」「えっ」
「ホットで」「えっ」「えっ」
![Page 10: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/10.jpg)
orz
![Page 11: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/11.jpg)
くらい、響きが良いですよね :D
XtraDB だけだとストレージエンジンの
名前なんですけどね
![Page 12: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/12.jpg)
Percona XtraDB Cluster
Clustered by– Multi Master– (Virtual) Synchronous– Parallel in Row-Level
Write Set Replication(wsrep)
So it makes flat(no nodes are specific as Master or Slave) MySQL Replication topology.
And wsrep takes a automated Full-physical and Increment-logical data synchronization.
![Page 13: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/13.jpg)
wsrep
• wsrep is the concept for realize following notes(following next page)– wsrep implementation by cordership named Galera Replication, it
takes the form of patch for MySQL(there is implementation for PostgreSQL, but I don’t know about that). And MySQL compiled with Galera Replication patch is destributed by cordership, named Galera Cluster for MySQL.• http://www.codership.com/content/using-galera-cluster
– Percona-Server compiled with Galera Replication patch is named Percona XtraDB Cluster.• http://www.percona.com/software/percona-xtradb-cluster
– MariaDB compiled with Galera Replication patch is named MariaDB Galera Cluster.• https://kb.askmonty.org/en/what-is-mariadb-galera-cluster/
![Page 14: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/14.jpg)
Multi Master
• All nodes are writable– Of cource, standard MySQL Replication is so.– But you write into table on Replication Slave, its
updates(INSERT, UPDATE, DELETE, and so) are not replicated to any node without its own Slave Server.
This means it breaks consistency, so we can’t allow to write Replication Slave by read_only option variable.– wsrep library certs consistency when you write into
table on any node by using (Virtual) Synchronous Replication.
![Page 15: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/15.jpg)
(Virtual) Synchronous
• All nodes have same data (DAI-TAI) anytime– When you write some data into some node, wsrep library hooks
InnoDB API and writes wsrep’s binary log(Galera Cache) in row-based binary log, before it answers commit request.
– Galera Cache is pushed through Communication-Path and confirmed “Can it execute with integrity, without conflict of update?” on all other nodes.
– When wsrep gets that confirmation(Certification) on all other nodes, its update is committed at last.
Otherwise, when wsrep received Certification Failure, its transaction will rollback(=return Error: 1213 “Deadlock found when trying to get lock.”) and any node doesn’t update to keep consistency.
![Page 16: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/16.jpg)
(Virtual) Synchronous
• Why it called “Virtual” Synchronous?– It has a little rag between Certification and Real-
Write into Disk.– The server which is received to write request at
first writes its data into disk and notifies “its data is committed”. All other servers can write into disk after that notification(because of keeping it transaction can rollback)
![Page 17: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/17.jpg)
Parallel in Row-Level
• Any updates logged in Galera Cache is Row-Based Binary log– It makes each Certification is isolated in Row-Level, it makes each
Certification can work in multi thread.– Statement-Based Replication can’t detect even if replication has
lost data consistency. Do you face Error:1032 “Can’t find record in `tablename`”?
• Not only Galera Cache, but also “binary log” for using standard replication.– It inherits some weakpoint of standard RBR.
• MySQL 5.6 improves Multi Thread SQL_Thread but it works only separated Database-Level.
![Page 18: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/18.jpg)
State Snapshot Transfer(SST)
• It is full data copy from joined cluster node to joining cluster node, automated by wsrep library.– You can choise “rsync”(physical copy, need lock),
“mysqldump”(logical copy, need lock too), “custom script”.– XtraDB Cluster can use xtrabackup(physical copy, without
lock) as “custom script”. It makes adding new node and coming back downed node are lock-free and almost automation.
• If wsrep detects “impossible(=can’t certificate) update” why data is mismatch just then wsrep execute SST and keeps consistency over the cluster.
![Page 19: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/19.jpg)
Incremental State Transfer(IST)
• It is incremental data copy from joined cluster node to joining cluster node, automated by wsrep library.– When cluster node leave and come back shortly, wsrep
doesn’t need SST if they have enough Galera Cache covered all over its downtime.
– IST doesn’t need any locks and need any reads physical data file, because of IST uses only Galera Cache. And Galera Cache is maybe hot cache ‘coz it is “nearest updates for cluster”, IST is very faster than SST.
![Page 20: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/20.jpg)
Flat Topology + Automated SST =
• You don’t be annoyed at all, when– “OMG! Master is down! Get to switchover now!”
• There’s nothing to do you’ve already prepared your LVS configure.
– “OMG! There is some different data between Master and Slave!”• wsrep have already fixed it before you know that, you can
see only the fact SST is done.
– “OMG! Today is Christmas but I don’t have Girlfriend!”• I’m very veeeeery sorry. If I don’t introduce XtraDB Cluster,
you can keep you don’t know that.
![Page 21: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/21.jpg)
Combination withStandard Replication
• wsrep hooks only InnoDB write method, it means wsrep is separated from MySQL standard Replication.– Each node joining XtraDB Cluster can be Slave of another
mysqld.– Any mysqld can be Slave of node joining XtraDB Cluster.– But XtraDB Cluster Node doesn’t have binary log for
update through Galera Cache and inside out, you have to put log-slave-updates in your my.cnf.
– And you have to know, wsrep doesn’t support(correctly, “does ignore”) except of updates into InnoDB.
![Page 22: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/22.jpg)
How is it?Do you feel something WAK-WAK?
![Page 23: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/23.jpg)
勿論弱点も ( いっぱい ) あります
I/Oに負荷が偏るとかwsrepの部分の日本語情報少ないとか
xtrabackupと連携させるのに innobackupex いじるとかxtrabackup使わないと SSTでリードロックかかるし
RBR 前提なのでバイナリログでっかいとか Primary Keyが無いと悲惨とか更新がかかったノードにしかバイナリログ無いとか
InnoDB(XtraDB)以外のデータはそもそも同期されないとか
![Page 24: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/24.jpg)
性能は?tpcc-mysqlで叩いた感じ、Semi-Sync(M/S)の3割引くらいでした
ネットワーク帯域とかバイナリログの保管場所にも注意LVS通した分のオーバーヘッドもたぶんある
パラレルレプリケーションする為にそれなりのコアが必要(1行が1スレッド使うから、1万行更新するクエリは…)
サーバまたぎの更新が lock waitじゃなくてCertification Failure(=Deadlock扱い )になるので、アプリ側でハンドルしてやらないといけない
![Page 25: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/25.jpg)
( ´- ` ).oO (難易度的には ndbcluster(=MySQL Cluster) と 良い勝負かな)
異論はあると思う
![Page 26: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/26.jpg)
でも楽しそうですよね!
何より響きが良い
![Page 27: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/27.jpg)
ところでみなさんお気付きでしょうか
![Page 28: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/28.jpg)
実はこれ、 Perl の話じゃないんです
“Percona” =~ /^Per/But
“Percona” != “Perl”
![Page 29: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/29.jpg)
Chiba.pm #1 で話していた書き捨て MySQL モニターツール
https://github.com/yoku0825/my_collector
![Page 30: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/30.jpg)
挫折しました orz
だって Cacti あるんだもん
![Page 31: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/31.jpg)
でもたまに役に立ちます
Cacti よりドリルダウンしたいとき金曜日に地味に機能追加してみたりした
スローログ数えるやつも地味に使ってるhttps://github.com/yoku0825/my_slowlog
![Page 32: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/32.jpg)
あ、あと
![Page 33: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/33.jpg)
この前 MyNA 会で「俺は Vimmer 」って言いましたが
![Page 34: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/34.jpg)
あれは嘘です
嘘っていうか、Vim しか使えない消極的 Vimmer
.vimrc も gtags 関連しか入れてない
![Page 35: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/35.jpg)
Terapad 最高!
Perl スクリプトもてらぱで書く
![Page 36: Introduction to XtraDB Cluster](https://reader033.vdocuments.us/reader033/viewer/2022061508/55921db21a28ab73218b463b/html5/thumbnails/36.jpg)
ご清聴ありがとうございました
オチなし