最佳化系列 | MySQL Cluster 7.2.7記憶體表和磁碟表對比測試

標籤：mysql最佳化   mysql cluster   

一、準備工作
自從2009年測試MySQL Cluster 7.0之後，就沒怎麼關注過它，發展實在太慢了，還有很多不靠譜的地方。前陣子退出7.2.7版本後，看了看新特性介紹，號稱效能比以往版本高了很多，於是再關注並進行測試。
部署過程不多說，下載PRM包後直接安裝即可。共10個節點，其中1個管理節點，其他9個節點同時作為資料和SQL節點，所有節點伺服器配置圖：
650) this.width=650;" src="http://dp.imysql.com:8080/files/upload_yejr_imysql/mysql_cluster_tpcc_testing_env_20120925.png" title="mysql_cluster_tpcc_testing_env" style="border:0px;" alt="mysql_cluster_tpcc_testing_env_20120925." />
MySQL Cluster管理節點關鍵配置見下：

##ndb config.ini#[TCP DEFAULT]SendBufferMemory=32MReceiveBufferMemory=32M[NDB_MGMD DEFAULT]PortNumber=1186Datadir=/home/cluster/mgm[NDB_MGMD]NodeId=1Hostname=x.x.x.xLogDestination=FILE:filename=ndb_1_cluster.log,maxsize=10000000,maxfiles=6ArbitrationRank=1[NDBD DEFAULT]NoOfReplicas=3Datadir=/home/cluster/dataFileSystemPathDD=/home/cluster/data/diskdataBackupDataDir=/home/cluster/backup#FileSystemPathUndoFiles=/home/cluster/data#FileSystemPathDataFiles=/home/cluster/dataDataMemory=21633MIndexMemory=2705MLockPagesInMainMemory=1#相對應: 一次事務中,可以修改的資料行數MaxNoOfConcurrentOperations=100000MaxNoOfConcurrentTransactions=16384StringMemory=25MaxNoOfTables=4096MaxNoOfOrderedIndexes=2048MaxNoOfUniqueHashIndexes=512MaxNoOfAttributes=24576MaxNoOfTriggers=14336### Params for REDO LOG FragmentLogFileSize=256MInitFragmentLogFiles=SPARSENoOfFragmentLogFiles=85RedoBuffer=64MTransactionBufferMemory=32M#TimeBetweenGlobalCheckpoints=1000TimeBetweenGlobalCheckpoints=100TimeBetweenEpochs=100TimeBetweenEpochsTimeout=32000### Params for LCP DiskCheckpointSpeedInRestart=100MDiskCheckpointSpeed=10MTimeBetweenLocalCheckpoints=20### Heartbeating HeartbeatIntervalDbDb=1500HeartbeatIntervalDbApi=1500### Params for setting logging MemReportFrequency=30BackupReportFrequency=10LogLevelStartup=15LogLevelShutdown=15LogLevelCheckpoint=8LogLevelNodeRestart=15### Params for BACKUP BackupMaxWriteSize=1MBackupDataBufferSize=16MBackupLogBufferSize=4MBackupMemory=20M### Params for ODIRECT #Reports indicates that odirect=1 can cause io errors (os err code 5) on some systems. You must test.#ODirect=1### Watchdog TimeBetweenWatchdogCheckInitial=60000### TransactionInactiveTimeout  - should be enabled in Production #TransactionInactiveTimeout=60000TransactionInactiveTimeout=6000#TransactionDeadlockDetectionTimeout = 1200TransactionDeadlockDetectionTimeout = 12000### New 7.1.10 redo logging parameters RedoOverCommitCounter=3RedoOverCommitLimit=20### REALTIME EXTENSIONS #RealTimeScheduler=1### REALTIME EXTENSIONS FOR 6.3 ONLY#SchedulerExecutionTimer=80#SchedulerSpinTimer=40### DISK DATA SharedGlobalMemory=20MDiskPageBufferMemory=64M### Multithreading MaxNoOfExecutionThreads=4### Increasing the LongMessageBuffer b/c of a bug (20090903)LongMessageBuffer=16MBatchSizePerLocalScan=512### REALTIME ASPECTS - THREAD BINDING ### READ http://johanandersson.blogspot.com/2008/02/mysql-cluster-features-what-they-are.html#realtime#LockExecuteThreadToCPU=X#LockMaintThreadsToCPU=Y[MYSQLD DEFAULT]DefaultOperationRedoProblemAction=ABORTBatchSize=512#BatchByteSize=2048K#MaxScanBatchSize=2048K

在這裡要注意的是，需要多分配一些 API 節點出來，否則啟動cluster時候，可能會報告無剩餘 nodeid 的錯誤，這個問題也困擾了我小半天。
順便提一下，這個設定檔主要依靠免費線上工具產生的，之前發文介紹過：線上產生MySQL Cluster設定檔。該工具的管理員非常負責任，在我組建組態檔案後不久，還親自發郵件問我用的怎麼樣，有誤改進建議，贊。
資料節點和SQL節點設定檔主要內容如下：

## ndbcluster##ndb-cluster-connection-pool=4ndbcluster=1ndb-connectstring="x.x.x.x:1186"ndb-force-send=1ndb-use-exact-count=0ndb-extra-logging=1ndb-batch-size=32Mndb-autoincrement-prefetch-sz=1024engine-condition-pushdown=1default-storage-engine=ndbcluster

全部配置完後，就可以啟動進行測試了。這個過程相對比較簡單，自己看手冊就可以，只要記得資料節點上第一次啟動要加 --initial 選項即可。

二、測試過程&結果
1.) 建立磁碟資料表空間
(a) 建立LOGFILE GROUP

create logfile group NDB_LOGGRP ADD UNDOFILE ‘NDB_UNDO_01.dbf‘   INITIAL_SIZE=1024M ENGINE=NDBCLUSTER;alter logfile group NDB_LOGGRP ADD UNDOFILE ‘NDB_UNDO_02.dbf‘   INITIAL_SIZE=1024M ENGINE=NDBCLUSTER;

注意：

• 只能有一個logfile group

• 一次只能添加一個undofile

• 設定檔中已經設定redo_buffer_size的話，命令列中不可修改或重新設定+

(b) 建立資料資料表空間組

create tablespace NDB_TBSPC_01 ADD DATAFILE ‘NDB_TBSPC_01.dbf‘   USE LOGFILE GROUP NDB_LOGGRP   EXTENT_SIZE = 64M INITIAL_SIZE = 1024M AUTOEXTEND_SIZE = 64M  ENGINE = NDBCLUSTER; alter tablespace NDB_TBSPC_01 ADD DATAFILE ‘NDB_TBSPC_02.dbf‘   INITIAL_SIZE=1024M ENGINE=NDBCLUSTER;...alter tablespace NDB_TBSPC_01 ADD DATAFILE ‘NDB_TBSPC_10.dbf‘   INITIAL_SIZE=1024M ENGINE=NDBCLUSTER;#類似地，建立 NDB_TBSPC_02，也是10個資料檔案，不重複示範

注意：

• 可以建立多個資料資料表空間檔案

• 多個資料資料表空間，包括日誌空間，檔案不可同名

• 刪除資料資料表空間前，需要先刪除資料檔案

共：

• 1個REDO LOGFILE GROUP，空間2GB；

• 2個資料資料表空間，20個資料檔案，每個資料檔案1GB，共20GB；

2.) TPCC資料初始化
在這裡分別測試了10個DW和100個DW的對比情況：
650) this.width=650;" src="http://dp.imysql.com:8080/files/upload_yejr_imysql/mysql_cluster_tpcc_testing_prepare_20120925.png" title="mysql_cluster_tpcc_testing_env" style="border:0px;" alt="mysql_cluster_tpcc_testing_prepare_20120" />
可以看到，在初始化階段，磁碟表並沒有比記憶體錶慢多少，比較贊。
其中，磁碟表各表所使用的資料表空間分配如下：
650) this.width=650;" src="http://dp.imysql.com:8080/files/upload_yejr_imysql/mysql_cluster_tpcc_testing_prepare_20120925.png" title="mysql_cluster_tpcc_testing_env" style="border:0px;" alt="mysql_cluster_tpcc_testing_prepare_20120" />

3.) TPCC初始化後各表統計情況對比
執行完TPCC資料初始化後，記憶體表和磁碟表的各表相關資料統計不完全一樣，見下：
650) this.width=650;" src="http://dp.imysql.com:8080/files/upload_yejr_imysql/mysql_cluster_tpcc_table_status_20120925.png" title="mysql_cluster_tpcc_testing_env" style="border:0px;" alt="mysql_cluster_tpcc_table_status_20120925" />
原因：暫不清楚，稍後去深入瞭解下再更新本文。

4.) TPCC並發測試結果對比
執行多次TPCC並發測試，求平均值後，相應結果見下：
650) this.width=650;" src="http://dp.imysql.com:8080/files/upload_yejr_imysql/mysql_cluster_tpcc_testing_result_20120925.png" title="mysql_cluster_tpcc_testing_env" style="border:0px;" alt="mysql_cluster_tpcc_testing_result_201209" />

小結：從測試結果來看，MySQL Cluster測效能還很差；相同機器配置，改成2塊10K RPM SAS盤做RAID 1，1000個DW，TPMC最高都能跑到1900多。而MySQL Cluster的表現確實比較一般，當然了，這和我的測試環境有很大關係，畢竟只是百M網路。不管怎樣，MySQL Cluster仍不適用於高並發的OLTP情境，在低並發但要求高可用的情境還比較合適。據說就有不少類似電信企業被忽悠著上了MySQL Cluster，呵呵。
資源有限，以後有機會再進行測試並更新博文，O(∩_∩)O哈哈~

補充：嘗試做把一個節點的ndbd和sql進程強行殺掉，記憶體表總大小約6414MB、磁碟表總大小約：5568MB(事實上資料量是一樣的，只是所佔儲存空間不一樣)的情境下，該節點啟動恢複資料，總共耗時28分鐘，在百兆網路環境下，還是可以接受的。

--------------------------------------分割線--------------------------------------

知數堂 （http://zhishuedu.com）培訓是由資深MySQL專家葉金榮、吳炳錫聯合推出的專業優質培訓品牌，主要有MySQL DBA實戰最佳化和Python營運開發課程，是業內最有良心、最有品質的培訓課程。

本文出自 “老葉茶館” 部落格，請務必保留此出處http://imysql.blog.51cto.com/1540006/1879743

最佳化系列 | MySQL Cluster 7.2.7記憶體表和磁碟表對比測試