端端舝

MySQL Cluster毛
綅蚚仄化10仳堎嶲
MySQL Cluster Casual Talks #2
2014/06/25

掞
? 中午丹夫欠斗五
? 扔奈田綅蚚/悵忐互帊岈
? 生永玄伐奈弁井日OS﹜立玉伙它尼失引匹失皿伉弗奈扑
亦件眕俋反卅氏匹手醱給毛�卅中午中仃卅中(最僅及船
反丐曰引允)
? MySQL疑五﹝嬴疑五﹝

云觳
? 扔奈田凳傖
? 由仿丟奈正𨈘枒
? 扔奈申旦奶件井日逃汜仄凶梤漲午�𡛟
? 潼弝卞勾中化
? 公及坻
? 笝歹曰卞

LB
WEB/SQL用奈玉/奪燴用奈玉(2怢)
犯奈正用奈玉
1用奈玉弘伙奈皿�傖

H/W卞羥仄凶由仿丟奈正反ˋ
InnoDB心凶中卅云廣旰卅由仿丟奈正反丐月及ˋ

犯奈正用奈玉及由仿丟奈正
http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndbd-
definition.html

? MaxNoOfExecutionThreads (犯白巧伙玄2)
? ndbmtd及心及由仿丟奈正
? 翋猁卅�釬旦伊永玉及�釬旦伊永玉杅互𢜪引月
? LDM, TC, Send, Receive Threads
? 偞隅杅卞方勻化旦伊永玉杅反赻�偞隅今木月
? 郔湮匹昜燴CPU戊失杅午肮元卞允月(醴假)
? 7.3炵匹反郔湮36引匹硌隅褫夔

? ThreadConfig
? MaxNoOfExecutionThreads方曰��卞�釬旦伊永玉毛硌隅褫夔
? 偞隅瞰
? ThreadConfig=main={cpubind=1},ldm={count=4,cpubind=2,3,8,
9},recv={cpubind=5},rep={cpubind=7},io={cpubind=4}
? 跪旦伊永玉互升及CPU戊失匹�釬允月井嘐隅今木月(taskset腔卅)

? Numa (犯白巧伙玄1)
? /proc/[ndbmtd及PID]/numa_maps 毛�月癹曰 numactl --
interleave=all 匹お�仄化仁木月
? NUMA虐噫卅日Swap Insanity�習及凶戶卞手
? ODirect(犯白巧伙玄0)
? innodb_flush_method=O_DIRECT午肮�母奶伊弁玄IO毛妏蚚

? TransactionDeadlockDetectionTimeout(犯白巧伙玄1200)
? 偞隅�及�g弇反ms卅及匹1.2鏃互犯白巧伙玄
? tc互弁巨伉灍俴毛渾化月媆嶲﹝innodb_lock_wait_timeout卞輪中
覜元﹝湮五允亢月午犯永玉伕永弁渾切匹弁巨伉互笴隱仄化仄引丹
岈互丐月﹝湮五允亢月偞隅�反MaxNoOfConcurrentScans及奻癹
卞竘勻井井月埻秪卞卅勻凶曰允月﹝

? MaxNoOfConcurrentScans(犯白巧伙玄256, 郔湮500)
? 旦平乓件弁巨伉及郔湮�K蹈灍俴杅(氾奈皮伙旦平乓件﹜奶件犯永
弁旦旦平乓件匹瞳蚚今木月)
? 犯奈正用奈玉�g弇匹厥勾及匹犯奈正用奈玉及杅互2�卅日郔湮�K
蹈灍俴杅反1000卞卅月
? InnoDB卞反剠中秶癹卅及匹侘嶲腔卞湮講及失弁本旦互懂凶�磁
卞SQL用奈玉及巨仿奈伕弘卞Too many active scans午堤薯今木
化中月仇午互丐曰引允

? MaxNoOfConcurrentTransactions(犯白巧伙玄4096)
? 犯奈正用奈玉�极匹肮媆灍俴褫夔卅玄仿件扒弁扑亦件杅(匹手1玄
仿件扒弁扑亦件匹1秏愐允月歹仃匹反卅中)
? MaxNoOfConcurrentScans午肮�犯奈正用奈玉�g弇匹厥勾�
? 穴瓦亙失伙方曰�呾宒
? TotalNoOfConcurrentTransactions = (maximum number of
tables accessed in any single transaction + 1) * number of
cluster SQL nodes

? NoOfFragmentLogFiles(犯白巧伙玄16)
? Redo伕弘及白央奶伙杅﹝
? FragmentLogFileSize(犯白巧伙玄16MB)
? Redo伕弘1勾及白央奶伙扔奶朮
? 犯白巧伙玄及�磁 NoOfFragmentLogFiles * 4 *
FragmentLogFileSize匹1024MB
? The default parameter value is 16, which by default means 16
sets of 4 16MB files for a total of 1024MB)

SQL用奈玉及由仿丟奈正
http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-program-
options-mysqld.html

? ndb-cluster-connection-pool(犯白巧伙玄1)
? SQL用奈玉互犯奈正用奈玉1勾煘曰卞諉適允月杅
? �支允午諉適杅互�尹月及匹�盡袨暿棒菴匹俶夔互砃奻
? 筍仄�支允午SQL用奈玉午仄化及NodeId互秏愐今木月凶戶﹜
config.ini卞𤩸仁[MYSQLD]及杅毛�支允斛猁互丐月
? query_cache_type, query_cache_size
? MySQL Cluster虐噫匹反剠�卞允月

扔奈申旦奶件井日逃汜仄凶
梤漲午�𡛟

梤漲1
? SQL用奈玉及巨仿奈伕弘卞眕狟囀⺍互堤薯
? [ERROR] /usr/local/mysql/bin/mysqld: Got temporary error 245 'Too many
active scans' from NDBCLUSTER
? �𡛟
? 公手公手忳仃五木卅中及卅日白伕件玄及Web(Apache)及MaxClients毛狟
仆月(珨媆仄及亢)﹝
? TransactionDeadlockDetectionTimeout毛伉玄仿奶�I燴仄勻井曰仄化月卅
日苤今仁允月
? 弁巨伉及�眻仄

梤漲2
? 犯奈正用奈玉及伕弘卞眕狟囀⺍互堤薯
? Signal lost, out of long signal memory, please increase
LongMessageBuffer (Resource configuration error)
? �𡛟
? LongMessageBuffer毛�支允﹝
? 妏蚚仄化中凶田奈斥亦件匹反犯白巧伙玄互4MB分勻凶
? This parameter seldom needs to be changed from the default.
? 7.3.5井日64MB互犯白巧伙玄卞(#^肋^)申平申平

梤漲3?
? �砑虐噫及ndbd 3 x 2用奈玉弘伙奈皿毛ndbmtd及3 x 1用
奈玉弘伙奈皿尺劐載允月蕣卞逃汜﹝
? 謎仁卅中岈卞媆嶲互卅仁﹜用奈玉弘伙奈皿杅互劐歹月�
磁卞start backup匹龰木月田永弁失永皿犯奈正毛妏蚚堤
懂月井𨈘偩堤懂卅井勻凶﹝公及凶戶mysqldump仄化伉旦
玄失毛�心引仄凶

梤漲3?
? 磐彆﹜�岈卞伉旦玄失媆卞巨仿奈互逃汜
? 堤薯丟永本奈斥
? Got temporary error 410 'REDO log files overloaded (decrease
TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)' from
NDBCLUSTER
? 穴瓦亙失伙�月(http://dev.mysql.com/doc/refman/5.6/en/mysql-
cluster-ndbd- definition.html#ndbparam-ndbd-
timebetweenlocalcheckpoints)

梤漲3?
? 穴瓦亙失伙方曰
? All the write operations in the cluster are added together. Setting
TimeBetweenLocalCheckpoints to 6 or less means that local
checkpoints will be executed continuously without pause,
independent of the cluster's workload.
? TimeBetweenLocalCheckpoints毛6眕狟卞仄化伉旦玄失
仄化傖髡
? 仄井仄ndbmtd及婬お�互逃汜仄凶凶戶湮五仁媆嶲伕旦﹝

梤漲4
? 犯奶伉奈及cron及正奶立件弘匹ndbmtd互邈切月
? 堤薯丟永本奈斥
INFO -- Internal program error (failed ndbrequire)
INFO -- DBTC (Line: 7783) 0x00000002
INFO -- Error handler shutting down system
INFO -- Error handler shutdown completed - exiting
ALERT -- Node 20: Forced node shutdown completed. Caused by
error 2341: 'Internal program error (failed ndbrequire)(Internal error, programming error or
missing error message, please report a bug). Temporary error, restart node'.

梤漲4
? 虐噫甡湔六內恚杅虐噫匹逃汜
? 郔�及弗奈旦匹�窒及犯奈正用奈玉互礿砦(1隙分仃)
? 𠸎葆中凶日旦伐永皿妏勻化中凶
? I/O�盡及詢中匹丐欠丹cron(updatedb灍俴)毛礿砦
? 旦伐永皿午及潭友磁中匹TimeBetweenWatchDogCheck卞竘勻井
井勻化礿砦仄凶午蕉尹日木凶凶戶
? 勾中匹卞vm.swappiness = 0 卞偞隅

犯奈正用奈玉
? CPU戊失黱及妏蚚薹毛屠�
? 伕奈玉失矛伊奈斥屠�支CPU妏蚚薹屠�(�极)反丐引曰砢卞蕾凶
卅中
? �盡及詢中袨颷匹手伕奈玉失矛伊奈斥互幻午氏升奻互日卅中(六
中兮中ldm旦伊永玉杅+1,2什日中)

犯奈正用奈玉
? 生永玄伐奈弁玄仿白奴永弁毛屠�
? 犯奈正用奈玉及杻釾午仄化�盡互剠中袨颷匹手生永玄伐奈弁玄仿
白奴永弁互逃汜允月
? 婡傷卞玄仿白奴永弁互逃汜仄卅仁卅勻凶日睡井仄日�都互逃汜仄
化中月凶戶失仿奈玄午允月
? �盡互剠中袨颷匹手100Kbpsゴ摽反都媆玄仿白奴永弁互逃汜
? 政婓扔奈申旦笢及犯奈正用奈玉匹反�盡互郔手腴中媆嶲匹手
10Mbpsゴ摽匹芢痄

犯奈正用奈玉
? DataMemory, IndexMemory及妏蚚薹屠�
? 奪燴用奈玉(ndb_mgmd)匹隅ヽ腔卞民尼永弁
ndb_mgm> all report memoryusage;
Connected to Management Server at: localhost:1186
Node 1: Data usage is 30%(177824 32K pages of total 589824)
Node 1: Index usage is 11%(44203 8K pages of total 393344)
ndb_mgm>

SQL用奈玉
? 籵都及MySQL午肮�﹜諉適屠�反斛�
? 筍仄engine=ndb卅氾奈皮伙卞�允月統桽支載陔�I燴互俴尹月井
偞隅允月屯五
? DB及犯奈正互犯奈正用奈玉卞丐月MySQL Cluster分互SQL用奈玉手
賃午伉末奈旦秏愐允月及匹珨啜腔卅屠�反俴中﹜怢杅毛�支允正奶
立件弘卞𠸎毛饜月

OS笚曰
? 由弗永玄玉伕永皿支巨仿奈反逃汜仄卞仁中方丹卞偞隅
? NIC及伉件弘田永白央反郔湮卞(RX反斛�)
? RSS�𡛟NIC毛妏蚚允月
? 犯奈正用奈玉匹旦伐永皿逃汜反祡韜腔
? vm.swappiness=0卞偞隅仄勾勾丟乒伉卞豻啥互丐月井假隅允月引
匹反𠸎毛饜月

妏蚚田奈斥亦件
? MySQL Cluster卞憝仄化反伉伉奈旦今木化中月郔陔田奈
斥亦件毛妏蚚允月
? 7.2炵午7.3炵匹JOIN及俶夔互弁巨伉卞方勻化掛絞卞30捷什日中船
互堤月凶戶
? 7.2炵分午30鏃ゴ摽井井月互7.3炵分午1鏃手井井日卅中弁巨伉
卞灍蕣卞婈郣

? MySQL Cluster反籵都及MySQL午掀廌允月午伊件斥炵及弁
巨伉﹜本市件母伉奶件犯永弁旦毛妏蚚仄凶弁巨伉互賴忒﹝
井勾磐�秶癹互丐月及匹蛁砩互斛猁
? 偞隅毛嶲綃尹卅中癹曰反1怢母它件仄化手扔奈申旦反輥適
允月及匹綅蚚仄化中月�井日允月午炰㜭
? 邈切凶日お�允木壬赻�腔卞覧導允月
? 翋平奈卞�允月const卅SELECT反夫凶允日厒中仄弁巨伉卞
方勻化反犯奈正用奈玉毛�支六壬�忒卞旦弗奈伙允月
? 陔仄中田奈斥亦件卅日JOIN手坋煦詢厒

? MySQL Cluster反㻢𨃨辻夔午仄化⺜墿趙今木月﹜弁巨伉
卞方勻化反用奈玉毛�支六壬旦弗奈伙允月午中勻凶丟伉
永玄反丐月互﹜珂卞伃仆凶籵曰砃五祥砃五﹜秶癹互丐月
及匹籵都及MySQL及源互砃中化中月弗奈旦手嗣中及匹
岈ゴ卞坋煦卞𨈘�毛俴勻化狟今中

端端舝

MySQL Cluster毛綅蚚仄化10仳堎嶲

More Related Content

MySQL Cluster毛綅蚚仄化10仳堎嶲