MySQL Cluster7.2.15原始碼安裝部署效能測試

1 下載 Download the cluster tar.gz
wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/mysql-cluster-gpl-7.2.15.tar.gz

MySQL Cluster 7.2.15
Select Version: 下拉框選擇 7.2.15
Select Platform: 下拉框選擇 Linux Generic
然後再下面連線一欄裡面選擇：
Linux - Generic 2.6 (x86, 64-bit), Compressed TAR Archive
(mysql-cluster-gpl-7.2.15-linux2.6-x86_64.tar.gz)

2 cp to other 3 host
 scp mysql-cluster-gpl-7.2.15-linux2.6-x86_64.tar.gz 10.88.49.124:/root
 10.88.49.119
 10.88.49.122
 10.88.49.123
 10.88.49.124
 
3 install management node
tar -xvf mysql-cluster-gpl-7.2.15-linux2.6-x86_64.tar.gz 


3.1 install the directory of install
mkdir -p /usr/local/mysql/bin
mkdir -p /usr/local/mysql/ndbdata


3.2 set the path of file run
[root@mysql mysql-cluster-gpl-7.2.15-linux2.6-x86_64]# vi ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin


3.3 copy the run files
cd mysql-cluster-gpl-7.2.15-linux2.6-x86_64
cp bin/ndb_mgmd /usr/local/mysql/bin
cp bin/ndb_mgm /usr/local/mysql/bin


3.4 create  the file of log file store
 mkdir -p /var/lib/mysql-cluster
 
3.5 create the config file and reset it
mkdir -p /usr/local/mysql/cluster-conf
vi /usr/local/mysql/cluster-conf/config.ini 
[NDBD DEFAULT]
#TotalSendBufferMemory= 256M
NoOfReplicas=2
DataMemory=2500M
IndexMemory=500M
DataDir=/var/lib/mysql-cluster
#FileSystemPath=/data/dbdata
#Redolog
FragmentLogFileSize=32M
InitFragmentLogFiles=SPARSE
NoOfFragmentLogFiles=8
RedoBuffer=1024M
StringMemory=25
LockPagesInMainMemory=1
MaxNoOfTables=4096
MaxNoOfOrderedIndexes=2048
MaxNoOfUniqueHashIndexes=1024
MaxNoOfAttributes=24576
MaxNoOfTriggers=14336
ODirect=1


MaxNoOfConcurrentTransactions=10000
MaxNoOfConcurrentOperations=50000
MaxNoOfLocalOperations=55000
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=100
TimeBetweenWatchdogCheckInitial=60000
TransactionBufferMemory=10M
DiskCheckpointSpeed=10M
DiskCheckpointSpeedInRestart=100M
TimeBetweenLocalCheckpoints=20
SchedulerSpinTimer=400
SchedulerExecutionTimer=100
RealTimeScheduler=1
BackupMaxWriteSize=1M
BackupDataBufferSize=16M
BackupLogBufferSize=4M
BackupMemory=20M


MaxNoOfExecutionThreads=4
TransactionDeadLockDetectionTimeOut=10000
BatchSizePerLocalScan=512
###Increasing the LongMessageBufferb/c of a bug (20090903)
LongMessageBuffer=8M
###Heartbeating
HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000
[MGM DEFAULT]
PortNumber:1186
DataDir:/var/lib/mysql-cluster1
[TCP DEFAULT]
SendBufferMemory:64M


#######################################
#Change HOST1 to the name of the NDB_MGMD host
#Change HOST8 to the name of the NDBD host
#Change HOST9 to the name of the NDBD host
#######################################
[NDB_MGMD]
NodeId:1
HostName:10.88.49.119
ArbitrationRank:1
[NDBD]
NodeId:4
HostName:10.88.49.124


######################################################
#Note: The following can be MySQLDconnections or   #
#      NDB API application connecting to thecluster #
######################################################
[API]
NodeId:10
HostName:10.88.49.122
ArbitrationRank:2


[API]
NodeId:11
HostName:10.88.49.123
ArbitrationRank:2


4 install data node (10.88.49.124)
4.1 install the directory of install
mkdir -p /usr/local/mysql/bin
mkdir -p /usr/local/mysql/ndbdata


4.2 copy the run command file
cd mysql-cluster-gpl-7.2.15-linux2.6-x86_64
cp ./bin/ndbd /usr/local/mysql/bin
    chown -R mysql:mysql /usr/local/mysql


4.3  set the path
vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin


4.4 set the config file
vim /etc/mycluster.cnf
[mysql_cluster]

4.5 配置資料節點連線管理節點
ndb-connectstring=10.88.49.119

5 install sql node (10.88.49.122,10.88.49.123)
 compress the tar.gz file
  tar -zxvf mysql-cluster-gpl-7.2.15-linux2.6-x86_64.tar.gz

5.1 copy the directory
  mv mysql-cluster-gpl-7.2.15-linux2.6-x86_64 /usr/local/mysqlndb

5.2  add mysql accounts
    group add mysql
    useradd -g mysql mysql

5.3 add the permission
    chown -R mysql:mysql /usr/local/mysqlndb

5.4 copy the config file
    cp /usr/local/mysqlndb/my.cnf /etc/my.cnf

5.5 init the database
    cd /usr/local/mysqlndb
    scripts/mysql_install_db --basedir=/usr/local/mysqlndb/ --datadir=/data/  --defaults-file =/etc/myndb.cnf --user=mysql
[root@localhost mysqlndb]#     scripts/mysql_install_db --user=mysql
Installing MySQL system tables...
2014-02-27 01:55:16 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

Installation of system tables failed!  Examine the logs in
/app/mysql for more information.

You can try to start the mysqld daemon with:

    shell> /usr/local/mysql5615/bin/mysqld --skip-grant &

and use the command line tool /usr/local/mysql5615/bin/mysql
to connect to the mysql database and look at the grant tables:

    shell> /usr/local/mysql5615/bin/mysql -u root mysql
    mysql> show tables

Try 'mysqld --help' if you have problems with paths.  Using --log
gives you a log in /app/mysql that may be helpful.

Please consult the MySQL manual section
'Problems running mysql_install_db', and the manual section that
describes problems on your OS.  Another information source are the
MySQL email archives available at

Please check all of the above before mailing us!  And remember, if
you do mail us, you MUST use the /usr/local/mysql5615/scripts/mysqlbug script!


[]必須使用全路徑/usr/local/mysqlndb/scripts/mysql_install_db，不然識別，就會自動呼叫為原來的初始化路徑。
[root@localhost mysqlndb]# cp  /etc/myndb.cnf /usr/local/mysqlndb/my.cnf
[root@localhost mysqlndb]# /usr/local/mysqlndb/scripts/mysql_install_db --defaults-file=/usr/local/mysqlndb/my.cnf --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK


To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:


/usr/local/mysqlndb//bin/mysqladmin -u root password 'new-password'
/usr/local/mysqlndb//bin/mysqladmin -u root -h localhost password 'new-password'


Alternatively you can run:
/usr/local/mysqlndb//bin/mysql_secure_installation


which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.


See the manual for more instructions.


You can start the MySQL daemon with:
cd /usr/local/mysqlndb/ ; /usr/local/mysqlndb//bin/mysqld_safe &


You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysqlndb//mysql-test ; perl mysql-test-run.pl


Please report any problems with the /usr/local/mysqlndb//scripts/mysqlbug script!



5.7 copy the protect process
    cp /usr/local/mysqlndb/support-files/mysql.server /etc/init.d/ndbdsql
 配置守護程式
    chkconfig --add ndbdsql
    chkconfig --level 35 ndbdsql on
 新增執行檔案路徑
    vim ~/.bash_profile
 PATH=$PATH:$HOME/bin:/usr/local/mysqlndb/bin
 更改配置檔案
    vim /etc/my.cnf
 分別編輯如下資訊：
[mysqld]#配置SQL節點與管理節點連線
 port= 3307
 socket= /tmp/mysql.sock
 ndbcluster
 ndb-connectstring=10.88.49.119
 
 [root@localhost mysqlndb]# /etc/init.d/mysqldndbd status
 ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists
 [root@localhost mysqlndb]# /etc/init.d/mysqldndbd status
 ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists
 [root@localhost mysqlndb]# mv /var/lock/subsys/mysql /var/lock/subsys/mysql.bak
 [root@localhost mysqlndb]# /etc/init.d/mysqldndbd start
Starting MySQL... SUCCESS! 
 
6 start the cluster 
6.1 start the management node
 ndb_mgmd -f /usr/local/mysql/cluster-conf/config.ini
 
 [root@mysql ~]#  ndb_mgmd -f /usr/local/mysql/cluster-conf/config.ini
MySQL Cluster Management Server mysql-5.5.35 ndb-7.2.15
2014-02-25 18:57:04 [MgmtSrvr] ERROR    -- at line 1: Value specified outside section
2014-02-25 18:57:04 [MgmtSrvr] ERROR    -- at line 1: Could not parse name-value pair in config file.
2014-02-25 18:57:04 [MgmtSrvr] ERROR    -- Could not load configuration from '/usr/local/mysql/cluster-conf/config.ini'
2014-02-25 18:57:04 [MgmtSrvr] ERROR    -- Could not determine which nodeid to use for this node. Specify it with --ndb-nodeid= on command line


檢查是從window下copy到liux下，某些空格失效了。
修改/usr/local/mysql/cluster-conf/config.ini，將[NDBDDEFAULT]換成[NDBD DEFAULT]，中間少了一個空格。




6.2 start the data node
  ndbd –initial #(第一次啟動必須新增選項，另外備份/恢復，修改配置檔案也需要執行)
  ndbd    #不是第一次啟動需要執行的命令


  /usr/local/mysql/bin/ndbd --defaults-file=/etc/mycluster.cnf --initial
  啟動時候指定讀取的my.cnf配置檔案。
  
  去check下狀態
 [root@mysql ~]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     1 node(s)
id=4 (not connected, accepting connect from 10.88.49.124)


[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.88.49.119  (mysql-5.5.35 ndb-7.2.15)


[mysqld(API)]   2 node(s)
id=10 (not connected, accepting connect from 10.88.49.122)
id=11 (not connected, accepting connect from 10.88.49.123)


ndb_mgm> 
data node沒有show出來，再去check下data node的日誌：
more /var/lib/mysql-cluster/ndb_4_out.log

 
  報錯如下
  Started thread, index = 33, id = 27480, type = NdbfsThread
WOPool::init(61, 9)
RWPool::init(22, 14)
LCPFragWatchdog : WarnElapsed : 20000(ms) MaxElapsed 60000(ms) : period millis : 10000
2014-02-25 10:03:10 [ndbd] WARNING  -- timerHandlingLab, expected 10ms sleep, not scheduled for: 2638 (ms)
2014-02-25 10:03:10 [ndbd] WARNING  -- Time moved forward with 3688 ms
blockSz: 800, wpp: 8188 -> 624 (76)
RWPool::init(29, 624)
RWPool::init(42, 16)
RWPool::init(62, 12)2014-02-25 10:03:11 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Job Handling elapsed=100


2014-02-25 10:03:11 [ndbd] INFO     -- Watchdog: User time: 27  System time: 439
2014-02-25 10:03:11 [ndbd] INFO     -- Watchdog: User time: 27  System time: 441
2014-02-25 10:03:11 [ndbd] WARNING  -- Watchdog: Warning overslept 453 ms, expected 100 ms.
2014-02-25 10:03:11 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Job Handling elapsed=554
2014-02-25 10:03:11 [ndbd] INFO     -- Watchdog: User time: 27  System time: 441
2014-02-25 10:03:11 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Job Handling elapsed=749
2014-02-25 10:03:14 [ndbd] ALERT    -- Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 9.


一直在調整/usr/local/mysql/cluster-conf/config.ini的引數值，但是一直報同樣的錯誤。
記憶體和索引引數都調整100M以下了，依然報同樣的錯誤，肯定不是記憶體不足，去看管理節點的日誌資訊
[root@mysql mysql-cluster1]# tail -f ndb_1_cluster.log 
2014-02-25 20:08:25 [MgmtSrvr] ALERT    -- Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 9.
2014-02-25 20:19:00 [MgmtSrvr] INFO     -- Loaded config from '/usr/local/mysql/mysql-cluster/ndb_1_config.bin.1'
2014-02-25 20:19:00 [MgmtSrvr] INFO     -- Id: 1, Command port: *:1186
2014-02-25 20:19:00 [MgmtSrvr] INFO     -- Node 1: Node 1 Connected
2014-02-25 20:19:00 [MgmtSrvr] INFO     -- MySQL Cluster Management Server mysql-5.5.35 ndb-7.2.15 started
2014-02-25 20:19:00 [MgmtSrvr] INFO     -- Node 1 connected
2014-02-25 20:19:18 [MgmtSrvr] INFO     -- Nodeid 4 allocated for NDB at 10.88.49.124
2014-02-25 20:19:19 [MgmtSrvr] INFO     -- Node 1: Node 4 Connected
2014-02-25 20:19:31 [MgmtSrvr] ALERT    -- Node 1: Node 4 Disconnected
2014-02-25 20:19:33 [MgmtSrvr] ALERT    -- Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 9.


/usr/local/mysql/mysql-cluster/ndb_1_config.bin.1這個是幹什麼用的？貌似理論上沒有這個，刪除試試。
rm -rf /usr/local/mysql/mysql-cluster/ndb_1_config.bin.1

然後重新啟動管理節點以及資料節點。
[root@mysql mysql-cluster1]#  ndb_mgmd -f /usr/local/mysql/cluster-conf/config.ini
MySQL Cluster Management Server mysql-5.5.35 ndb-7.2.15
[root@monitor01 ~]# /usr/local/mysql/bin/ndbd --defaults-file=/etc/mycluster.cnf --initial
2014-02-25 10:42:29 [ndbd] INFO     -- Angel connected to '10.88.49.119:1186'
2014-02-25 10:42:29 [ndbd] INFO     -- Angel allocated nodeid: 4


去管理節點上show下，檢視資料節點狀態：
[root@mysql mysql-cluster1]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     1 node(s)
id=4    @10.88.49.124  (mysql-5.5.35 ndb-7.2.15, Nodegroup: 0, *)


[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.88.49.119  (mysql-5.5.35 ndb-7.2.15)


[mysqld(API)]   2 node(s)
id=10 (not connected, accepting connect from 10.88.49.122)
id=11 (not connected, accepting connect from 10.88.49.123)


ndb_mgm> 

OK，資料節點已經啟動起來了。


6.3 start the sql node.
[root@localhost mysql5615]# /etc/init.d/ndbdsql start
/etc/init.d/ndbdsql: line 276: cd: /usr/local/mysql: No such file or directory
Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)


vi /etc/init.d/ndbdsql
將/usr/local/mysql/bin改成/usr/local/mysqlndb/bin


[root@localhost data]# /etc/init.d/ndbdsql start
Starting MySQL..... ERROR! The server quit without updating PID file (/data/mysql.pid).
[root@localhost data]#
[root@localhost data]# more mysql.err 
140227 02:47:51 mysqld_safe Starting mysqld daemon with databases from /data/
140227  2:47:51 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use
 the full name instead.
140227  2:47:51 [Note] Plugin 'FEDERATED' is disabled.
140227  2:47:51 InnoDB: The InnoDB memory heap is disabled
140227  2:47:51 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140227  2:47:51 InnoDB: Compressed tables use zlib 1.2.3
140227  2:47:51 InnoDB: Using Linux native AIO
140227  2:47:51 InnoDB: Initializing buffer pool, size = 256.0M
140227  2:47:51 InnoDB: Completed initialization of buffer pool
InnoDB: Error: auto-extending data file ./ibdata1 is of a different size
InnoDB: 1152 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 65536 pages, max 0 (relevant if non-zero) pages!
140227  2:47:51 InnoDB: Could not open or create data files.
140227  2:47:51 InnoDB: If you tried to add new data files, and it failed here,
140227  2:47:51 InnoDB: you should now edit innodb_data_file_path in my.cnf back
140227  2:47:51 InnoDB: to what it was, and remove the new ibdata files InnoDB created
140227  2:47:51 InnoDB: in this failed attempt. InnoDB only wrote those files full of
140227  2:47:51 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
140227  2:47:51 InnoDB: remove old data files which contain your precious data!
140227  2:47:51 [ERROR] Plugin 'InnoDB' init function returned error.
140227  2:47:51 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140227  2:47:51 [Note] NDB: NodeID is 10, management server '10.88.49.119:1186'
140227  2:47:52 [Note] NDB[0]: NodeID: 10, all storage nodes connected
140227  2:47:52 [Note] Starting Cluster Binlog Thread
140227  2:47:52 [ERROR] Unknown/unsupported storage engine: innodb
140227  2:47:52 [ERROR] Aborting


140227  2:47:52 [Note] Stopping Cluster Utility thread
140227  2:47:53 [Note] Stopping Cluster Binlog
140227  2:47:53 [Note] Stopping Cluster Index Stats thread
140227  2:47:53 [Note] Stopping Cluster Index Statistics thread
140227  2:47:53 [Note] /usr/local/mysqlndb/bin/mysqld: Shutdown complete


140227 02:47:53 mysqld_safe mysqld from pid file /data/mysql.pid ended


[]刪除資料目錄/data/ibdata1檔案
[root@localhost data]# /etc/init.d/ndbdsql start
Starting MySQL..... ERROR! The server quit without updating PID file (/data/mysql.pid).
[root@localhost data]#


140227  2:49:38 [Note] Plugin 'FEDERATED' is disabled.
140227  2:49:38 InnoDB: The InnoDB memory heap is disabled
140227  2:49:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140227  2:49:38 InnoDB: Compressed tables use zlib 1.2.3
140227  2:49:38 InnoDB: Using Linux native AIO
140227  2:49:38 InnoDB: Initializing buffer pool, size = 256.0M
140227  2:49:38 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
140227  2:49:38  InnoDB: Setting file ./ibdata1 size to 1024 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!
140227  2:49:40 [ERROR] Plugin 'InnoDB' init function returned error.
140227  2:49:40 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140227  2:49:40 [Note] NDB: NodeID is 10, management server '10.88.49.119:1186'
140227  2:49:40 [Note] NDB[0]: NodeID: 10, all storage nodes connected
140227  2:49:40 [Note] Starting Cluster Binlog Thread
140227  2:49:40 [ERROR] Unknown/unsupported storage engine: innodb
140227  2:49:40 [ERROR] Aborting


140227  2:49:40 [Note] Stopping Cluster Utility thread
140227  2:49:41 [Note] Stopping Cluster Binlog
140227  2:49:41 [Note] Stopping Cluster Index Stats thread
140227  2:49:41 [Note] Stopping Cluster Index Statistics thread
140227  2:49:42 [Note] /usr/local/mysqlndb/bin/mysqld: Shutdown complete
[]刪除/data/ib_logfile* 檔案


[root@localhost data]# /etc/init.d/ndbdsql start
Starting MySQL...................... SUCCESS! 
[root@localhost data]# 




[root@localhost data]# /usr/local/mysqlndb/bin/mysql -uroot -p -P3307 --socket=/data/mysql.socket
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.35-ndb-7.2.15-cluster-gpl-log MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> show engines; -- 檢測ndbcluster儲存引擎是否已經生效。
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | YES     | Clustered, fault-tolerant tables                               | YES          | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ndbinfo            | YES     | MySQL Cluster system information storage engine                | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.11 sec)




7 功能測試 TPCC-mysql
mysql> create logfile group NDB_LOGGRP ADD UNDOFILE 'NDB_UNDO_01.dbf' 
    ->   INITIAL_SIZE=1024M ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (1 min 16.75 sec)


mysql> alter logfile group NDB_LOGGRP ADD UNDOFILE 'NDB_UNDO_02.dbf' 
    ->   INITIAL_SIZE=1024M ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (1 min 25.30 sec)


基準測試工具之tpcc-mysql

rpm -Uvh
[root@localhost ~]# rpm -Uvh
Retrieving
warning: /var/tmp/rpm-tmp.GZF4dV: Header V3 DSA/SHA1 Signature, key ID 217521f6: NOKEY
Preparing...                ########################################### [100%]
        package epel-release-6-8.noarch (which is newer than epel-release-5-4.noarch) is already installed
[root@localhost ~]# 


然後就可以開始安裝bzr客戶端了:
yum install bzr 


之後，就可以開始用bzr客戶端下載tpcc-mysql原始碼了。
cd tmp
bzr branch lp:~percona-dev/perconatools/tpcc-mysql


[root@localhost ~]# bzr branch lp:~percona-dev/perconatools/tpcc-mysql
You have not informed bzr of your Launchpad ID, and you must do this to
write to Launchpad or access private data.  See "bzr help launchpad-login".
bzr: ERROR: Target directory "tpcc-mysql" already exists. 
這個錯誤，需要登入，去 註冊帳號並登入，這樣之後再執行bzr branch lp:~percona-dev/perconatools/tpcc-mysql
[root@localhost tmp]# bzr branch lp:~percona-dev/perconatools/tpcc-mysql
You have not informed bzr of your Launchpad ID, and you must do this to
write to Launchpad or access private data.  See "bzr help launchpad-login".
Branched 48 revision(s).                                                                                                                                              


[]check，tpcc-mysql目錄就有了
[root@localhost tmp]# ll
total 72
drwx------. 2 root root  4096 Apr 16  2013 lp71h5jPt_
-rwxr-xr-x. 1 root root 10899 Feb 26 13:19 mysql
-rwxr-xr-x. 1 root root 10869 Feb 25 16:42 mysqld
-rwx------. 1 root root 10646 Apr 16  2013 mysqld55
-rwxrwx---. 1 root root 10646 Feb 18  2013 mysqld56
-rwxr-xr-x. 1 root root 10880 Feb 27 02:00 mysqldndbd
-rw-r--r--. 1 root root   101 Feb 23 17:12 percona-version-check
drwxr-xr-x. 6 root root  4096 Feb 27 17:12 tpcc-mysql
-rw-------. 1 root root     0 Feb 18  2013 yum.log


進入/tmp/tpcc-mysql/src目錄
[root@localhost src]# make
cc -w -O2 -g -I. `mysql_config --include`  -c load.c
load.c:18:19: error: mysql.h: No such file or directory
In file included from load.c:20:
spt_proc.h:1: error: expected 鈥鈥before 鈥鈥token
load.c:26: error: expected 鈥鈥 鈥鈥 鈥鈥 鈥榓sm鈥or 鈥榑_attribute__鈥before 鈥鈥token
......
[]設定變數：export C_INCLUDE_PATH=/usr/local/mysqlndb/include/
[root@localhost src]# export C_INCLUDE_PATH=/usr/local/mysqlndb/include/
[root@localhost src]# 
[root@localhost src]# 
[root@localhost src]# make
cc -w -O2 -g -I. `mysql_config --include`  -c load.c
cc -w -O2 -g -I. `mysql_config --include`  -c support.c
cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load
/usr/bin/ld: cannot find -lmysqlclient_r
collect2: ld returned 1 exit status
make: *** [../tpcc_load] Error 1
[]繼續設定變數：export LD_LIBRARY_PATH=/usr/local/mysqlndb/lib, export PATH=/usr/local/mysqlndb/bin:$PATH, export LD_LIBRARY_PATH=/usr/local/mysqlndb/lib


[]總結：連續設定如下3個變數：
export C_INCLUDE_PATH=/usr/local/mysqlndb/include/
export LD_LIBRARY_PATH=/usr/local/mysqlndb/lib
export PATH=/usr/local/mysqlndb/bin:$PATH
此處參考：http://blog.csdn.net/songxixi/article/details/8506796


[root@localhost src]# make
cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load
cc -w -O2 -g -I. `mysql_config --include`  -c main.c
cc -w -O2 -g -I. `mysql_config --include`  -c spt_proc.c
cc -w -O2 -g -I. `mysql_config --include`  -c driver.c
cc -w -O2 -g -I. `mysql_config --include`  -c sequence.c
cc -w -O2 -g -I. `mysql_config --include`  -c rthist.c
cc -w -O2 -g -I. `mysql_config --include`  -c neword.c
cc -w -O2 -g -I. `mysql_config --include`  -c payment.c
cc -w -O2 -g -I. `mysql_config --include`  -c ordstat.c
cc -w -O2 -g -I. `mysql_config --include`  -c delivery.c
cc -w -O2 -g -I. `mysql_config --include`  -c slev.c
cc main.o spt_proc.o driver.o support.o sequence.o rthist.o neword.o payment.o ordstat.o delivery.o slev.o `mysql_config --libs_r` -lrt -o ../tpcc_start
[root@localhost src]# 


[]check 生成的指令碼
[root@localhost tpcc-mysql]# ll
total 248
-rw-r--r--. 1 root root   1621 Feb 27 17:12 add_fkey_idx.sql
-rw-r--r--. 1 root root    317 Feb 27 17:12 count.sql
-rw-r--r--. 1 root root   3105 Feb 27 17:12 create_table.sql
-rw-r--r--. 1 root root    763 Feb 27 17:12 drop_cons.sql
-rw-r--r--. 1 root root    477 Feb 27 17:12 load.sh
-rw-r--r--. 1 root root    851 Feb 27 17:12 README
drwxr-xr-x. 2 root root   4096 Feb 27 17:12 schema2
drwxr-xr-x. 5 root root   4096 Feb 27 17:12 scripts
drwxr-xr-x. 2 root root   4096 Feb 27 17:29 src
-rwxr-xr-x. 1 root root  60281 Feb 27 17:29 tpcc_load
-rwxr-xr-x. 1 root root 154208 Feb 27 17:29 tpcc_start


[]建庫，建表，新增索引
/usr/local/mysqlndb/bin/mysql -uroot -p -P3307 --socket=/data/mysql.socket -e "CREATE DATABASE tpcc100;"
/usr/local/mysqlndb/bin/mysql -uroot -p -P3307 --socket=/data/mysql.socket tpcc100 <.> /usr/local/mysqlndb/bin/mysql -uroot -p -P3307 --socket=/data/mysql.socket tpcc100 <.>

[]匯入資料
[root@localhost tpcc-mysql]# ./tpcc_load localhost tpcc1000 root "" 20
*************************************
*** ###easy### TPC-C Data Loader  ***
*************************************

     [server]: localhost
     [port]: 3306
     [DBname]: tpcc1000
       [user]: root
       [pass]: 
  [warehouse]: 20


2002, HY000, Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
 
 軟連線
ln -s /data/mysql.socket /tmp/mysql.sock
在這裡，需要注意的是 tpcc 預設會讀取 /tmp/mysql.sock這個socket位置，因此如果你的socket不在相應路徑的話，就需要做個軟連線，或者透過TCP/IP的方式連線測試伺服器。


[root@localhost tpcc-mysql]# ./tpcc_load localhost tpcc100 root "" 20
*************************************
*** ###easy### TPC-C Data Loader  ***
*************************************

     [server]: localhost
     [port]: 3306
     [DBname]: tpcc100
       [user]: root
       [pass]: 
  [warehouse]: 20
TPCC Data Load Started...
Loading Item 
.....
1297, HY000, Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER
Retrying ...
.....
1297, HY000, Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER
Retrying ...
.....
1297, HY000, Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER
Retrying ...
.....
1297, HY000, Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER
Retrying ...






./tpcc_start  -hlocalhost  -d tpcc1000  -u root  -p '' -w 1000  -c  32  -r 120   -l  3600 -ftpcc_mysql_20120314


一直卡在這裡，所以暫時放棄用tpcc測試，用mysqlslap來進行測試。




7 功能測試 mysqlslap
mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=8 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=500 --debug-info 


-- ndbcluster
[root@localhost mysqlndb]# 
mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=8 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=500 --debug-info 


Benchmark
        Running for engine ndbcluster
        Average number of seconds to run all queries: 0.308 seconds
        Minimum number of seconds to run all queries: 0.308 seconds
        Maximum number of seconds to run all queries: 0.308 seconds
        Number of clients running queries: 8
        Average number of queries per client: 62


[root@localhost mysqlndb]# 
mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=16 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=500 --debug-info 

[root@localhost mysqlndb]# mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=16 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=500 --debug-info 
Benchmark
        Running for engine ndbcluster
        Average number of seconds to run all queries: 0.353 seconds
        Minimum number of seconds to run all queries: 0.353 seconds
        Maximum number of seconds to run all queries: 0.353 seconds
        Number of clients running queries: 16
        Average number of queries per client: 31


mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=32 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=500 --debug-info 


[root@localhost mysqlndb]# mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=24 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=500 --debug-info 
Benchmark
        Running for engine ndbcluster
        Average number of seconds to run all queries: 0.346 seconds
        Minimum number of seconds to run all queries: 0.346 seconds
        Maximum number of seconds to run all queries: 0.346 seconds
        Number of clients running queries: 24
        Average number of queries per client: 20



-- innodb
mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=8 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=500 --debug-info 
[root@localhost mysqlndb]# mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=8 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=500 --debug-info 
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.460 seconds
        Minimum number of seconds to run all queries: 0.460 seconds
        Maximum number of seconds to run all queries: 0.460 seconds
        Number of clients running queries: 8
        Average number of queries per client: 62


mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=16 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=500 --debug-info 
[root@localhost mysqlndb]# mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=16 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=500 --debug-info 
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.266 seconds
        Minimum number of seconds to run all queries: 0.266 seconds
        Maximum number of seconds to run all queries: 0.266 seconds
        Number of clients running queries: 16
        Average number of queries per client: 31


mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=32 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=500 --debug-info 
[root@localhost mysqlndb]# mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=24 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=500 --debug-info 
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.203 seconds
        Minimum number of seconds to run all queries: 0.203 seconds
        Maximum number of seconds to run all queries: 0.203 seconds
        Number of clients running queries: 24
        Average number of queries per client: 20


mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=32 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=500 --debug-info 




============================================================================================================================
-- ndbcluster
[root@localhost mysqlndb]# 
mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=8 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=1000 --debug-info 
[root@localhost mysqlndb]# mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=8 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=1000 --debug-info 
Benchmark
        Running for engine ndbcluster
        Average number of seconds to run all queries: 0.558 seconds
        Minimum number of seconds to run all queries: 0.558 seconds
        Maximum number of seconds to run all queries: 0.558 seconds
        Number of clients running queries: 8
        Average number of queries per client: 125

mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=16 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=1000 --debug-info 
[root@localhost mysqlndb]# mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=16 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=1000 --debug-info 
Benchmark
        Running for engine ndbcluster
        Average number of seconds to run all queries: 0.564 seconds
        Minimum number of seconds to run all queries: 0.564 seconds
        Maximum number of seconds to run all queries: 0.564 seconds
        Number of clients running queries: 16
        Average number of queries per client: 62
mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=32 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=1000 --debug-info 
mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=64 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=1000 --debug-info 
mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=8 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=ndbcluster --number-of-queries=2000 --debug-info 


mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=8 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=1000 --debug-info 
[root@localhost mysqlndb]# mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=8 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=1000 --debug-info 
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.710 seconds
        Minimum number of seconds to run all queries: 0.710 seconds
        Maximum number of seconds to run all queries: 0.710 seconds
        Number of clients running queries: 8
        Average number of queries per client: 125
mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=16 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=1000 --debug-info 
[root@localhost mysqlndb]# mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=16 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=1000 --debug-info 
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.453 seconds
        Minimum number of seconds to run all queries: 0.453 seconds
        Maximum number of seconds to run all queries: 0.453 seconds
        Number of clients running queries: 16
        Average number of queries per client: 62
mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=32 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=1000 --debug-info 
[root@localhost mysqlndb]# mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=32 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=1000 --debug-info 
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.541 seconds
        Minimum number of seconds to run all queries: 0.541 seconds
        Maximum number of seconds to run all queries: 0.541 seconds
        Number of clients running queries: 32
        Average number of queries per client: 31
mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=64 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=1000 --debug-info 
[root@localhost mysqlndb]# mysqlslap --defaults-file=/usr/local/mysqlndb/my.cnf  --concurrency=64 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=1000 --debug-info 
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.388 seconds
        Minimum number of seconds to run all queries: 0.388 seconds
        Maximum number of seconds to run all queries: 0.388 seconds
        Number of clients running queries: 64
        Average number of queries per client: 15



8 功能測試 sysbench
8.1 down tar.gz
wget


8.2
tar -xvf sysbench-0.4.12.tar.gz


以上方法適用於 MySQL 安裝在標準預設目錄下的情況，如果 MySQL 並不是安裝在標準目錄下的話，那麼就需要自己指定 MySQL 的路徑了。比如我的 MySQL 喜歡自己安裝在 /usr/local/mysql 下，則按照以下方法編譯：
8.3
configure
./configure --with-mysql-includes=/usr/local/mysqlndb/include --with-mysql-libs=/usr/local/mysqlndb/lib


make
make install


8.4 ndbcluster測試
 [root@localhost sysbench-0.4.12]#  
 sysbench --test=oltp --mysql-table-engine=ndbcluster --mysql-db=ndb --oltp-table-size=100 --mysql-user=root --mysql-password="" --mysql-socket=/data/mysql.socket --db-driver=mysql --num-threads=16  prepare
sysbench 0.4.12:  multi-threaded system evaluation benchmark


Creating table 'sbtest'...
Creating 100 records in table 'sbtest'...


sysbench --test=oltp --mysql-table-engine=ndbcluster --mysql-db=ndb --oltp-table-size=100 --mysql-user=root --mysql-password=""  --mysql-socket=/data/mysql.socket --db-driver=mysql  --num-threads=16  run
[root@localhost sysbench-0.4.12]# sysbench --test=oltp --mysql-table-engine=ndbcluster --mysql-db=ndb --oltp-table-size=100 --mysql-user=root --mysql-password=""  --mysql-socket=/data/mysql.socket --db-driver=mysql  --num-threads=16  run
sysbench 0.4.12:  multi-threaded system evaluation benchmark


Running the test with following options:
Number of threads: 16


Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000

[]run的時候卡在這裡了，可能錄入的資料量太少了，run不起來
手動自己寫一個儲存過程錄入資料：
DELIMITER $$
DROP PROCEDURE test.wuxin_erp_insert $$
CREATE PROCEDURE test.wuxin_erp_insert()
BEGIN
    DECLARE i INT;
    SET i=0;
    WHILE i< 100000 DO
REPLACE  INTO ndb.sbtest SELECT i,0,'','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt';
END WHILE;
COMMIT;
END$$
DELIMITER $$


錄入資料： call test.wuxin_erp_insert ();


[]在繼續測試，併發執行緒數量：8
[root@localhost sysbench-0.4.12]# sysbench --test=oltp --mysql-table-engine=ndbcluster --mysql-db=ndb --oltp-table-size=100000 --mysql-user=root --mysql-password=""  --mysql-socket=/data/mysql.socket --db-driver=mysql  --num-threads=8  run
sysbench 0.4.12:  multi-threaded system evaluation benchmark


Running the test with following options:
Number of threads: 8


Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.


OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (345.75 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (6569.20 per sec.)
    other operations:                    20000  (691.49 per sec.)


Test execution summary:
    total time:                          28.9228s
    total number of events:              10000
    total time taken by event execution: 231.2841
    per-request statistics:
         min:                                 14.34ms
         avg:                                 23.13ms
         max:                                402.38ms
         approx.  95 percentile:              33.59ms


Threads fairness:
    events (avg/stddev):           1250.0000/5.83
    execution time (avg/stddev):   28.9105/0.01
[]OK，謝天謝地，有測試資料出來了。

[]16個執行緒
sysbench --test=oltp --mysql-table-engine=ndbcluster --mysql-db=ndb --oltp-table-size=100000 --mysql-user=root --mysql-password=""  --mysql-socket=/data/mysql.socket --db-driver=mysql  --num-threads=8  run

[root@localhost sysbench-0.4.12]# sysbench --test=oltp --mysql-table-engine=ndbcluster --mysql-db=ndb --oltp-table-size=100000 --mysql-user=root --mysql-password=""  --mysql-socket=/data/mysql.socket --db-driver=mysql  --num-threads=16  run
sysbench 0.4.12:  multi-threaded system evaluation benchmark


Running the test with following options:
Number of threads: 16


Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.


OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (437.78 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (8317.76 per sec.)
    other operations:                    20000  (875.55 per sec.)


Test execution summary:
    total time:                          22.8427s
    total number of events:              10000
    total time taken by event execution: 365.2217
    per-request statistics:
         min:                                 23.03ms
         avg:                                 36.52ms
         max:                                563.69ms
         approx.  95 percentile:              43.03ms


Threads fairness:
    events (avg/stddev):           625.0000/5.30
    execution time (avg/stddev):   22.8264/0.01

[]32個執行緒
[root@localhost sysbench-0.4.12]# sysbench --test=oltp --mysql-table-engine=ndbcluster --mysql-db=ndb --oltp-table-size=100000 --mysql-user=root --mysql-password=""  --mysql-socket=/data/mysql.socket --db-driver=mysql  --num-threads=32  run
sysbench 0.4.12:  multi-threaded system evaluation benchmark


Running the test with following options:
Number of threads: 32


Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
ALERT: MySQL error: Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER


FATAL: Error fetching result: ``
ALERT: MySQL error: Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER


FATAL: Error fetching result: `p ALERT: MySQL error: Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER


FATAL: Error fetching result: `0烋`
ALERT: MySQL error: Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER


FATAL: Error fetching result: `橡`
ALERT: MySQL error: Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER


FATAL: Error fetching result: ``
ALERT: MySQL error: Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER


FATAL: Error fetching result: `嘣`
ALERT: MySQL error: Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER


FATAL: Error fetching result: `Pm`
ALERT: MySQL error: Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER


FATAL: Error fetching result: ``
ALERT: MySQL error: Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER


FATAL: Error fetching result: `貞`
ALERT: MySQL error: Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER


FATAL: Error fetching result: `?g`
ALERT: MySQL error: Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER


FATAL: Error fetching result: ``




[]報錯了，磁碟空間不足以支撐


8.5 Innodb測試
[]準備資料
 sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=test --oltp-table-size=100000 --mysql-user=root --mysql-password="" --mysql-socket=/data/mysql.socket --db-driver=mysql --num-threads=8  prepare
[root@localhost sysbench-0.4.12]#  sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=test --oltp-table-size=100000 --mysql-user=root --mysql-password="" --mysql-socket=/data/mysql.socket --db-driver=mysql --num-threads=8  prepare
sysbench 0.4.12:  multi-threaded system evaluation benchmark


Creating table 'sbtest'...
Creating 100000 records in table 'sbtest'...


[]開始測試 8執行緒
sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=test --oltp-table-size=100000 --mysql-user=root --mysql-password=""  --mysql-socket=/data/mysql.socket --db-driver=mysql  --num-threads=8  run
[root@localhost sysbench-0.4.12]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=test --oltp-table-size=100000 --mysql-user=root --mysql-password=""  --mysql-socket=/data/mysql.socket --db-driver=mysql  --num-threads=8  run
sysbench 0.4.12:  multi-threaded system evaluation benchmark


Running the test with following options:
Number of threads: 8


Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.


OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (382.77 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (7272.71 per sec.)
    other operations:                    20000  (765.55 per sec.)


Test execution summary:
    total time:                          26.1251s
    total number of events:              10000
    total time taken by event execution: 208.8009
    per-request statistics:
         min:                                  1.25ms
         avg:                                 20.88ms
         max:                                204.22ms
         approx.  95 percentile:              38.38ms


Threads fairness:
    events (avg/stddev):           1250.0000/13.32
    execution time (avg/stddev):   26.1001/0.01

[]16個執行緒
[root@localhost sysbench-0.4.12]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=test --oltp-table-size=100000 --mysql-user=root --mysql-password=""  --mysql-socket=/data/mysql.socket --db-driver=mysql  --num-threads=16  run
sysbench 0.4.12:  multi-threaded system evaluation benchmark


Running the test with following options:
Number of threads: 16


Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.


OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (398.89 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (7578.88 per sec.)
    other operations:                    20000  (797.78 per sec.)


Test execution summary:
    total time:                          25.0697s
    total number of events:              10000
    total time taken by event execution: 400.5857
    per-request statistics:
         min:                                  1.32ms
         avg:                                 40.06ms
         max:                                212.90ms
         approx.  95 percentile:              80.09ms


Threads fairness:
    events (avg/stddev):           625.0000/3.46
    execution time (avg/stddev):   25.0366/0.01


[]32個執行緒
[root@localhost sysbench-0.4.12]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=test --oltp-table-size=100000 --mysql-user=root --mysql-password=""  --mysql-socket=/data/mysql.socket --db-driver=mysql  --num-threads=32  run
sysbench 0.4.12:  multi-threaded system evaluation benchmark


Running the test with following options:
Number of threads: 32


Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.


OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (421.68 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (8011.90 per sec.)
    other operations:                    20000  (843.36 per sec.)


Test execution summary:
    total time:                          23.7147s
    total number of events:              10000
    total time taken by event execution: 757.1587
    per-request statistics:
         min:                                  1.36ms
         avg:                                 75.72ms
         max:                                365.32ms
         approx.  95 percentile:             135.06ms


Threads fairness:
    events (avg/stddev):           312.5000/2.69
    execution time (avg/stddev):   23.6612/0.02



最後測試結果：

	測試環境： 管理節點：1個，900M記憶體 資料節點：1個，3.6G記憶體 SQL節點：2個，900M記憶體 叢集4個點CPU：Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz，單核 叢集4個點：Raid1+0
	tpcc-mysql測試
	ERROR，壓資料壓不進去，ERROR， 資源不足
	mysqlslap測試 500寫入操作
	執行每一個寫入所花的時間，單位秒
併發執行緒數量	8	16	32	64
NDB	0.308	0.353	0.346	Error
InnoDB	0.46	0.266	0.203	0.212
	mysqlslap測試 1000寫入操作
	執行每一個寫入所花的時間，單位秒
併發執行緒數量	8	16	32	64
NDB	0.558	0.564	90.247	Error
InnoDB	0.701	0.453	0.541	0.388
	總結：mysqlslap測試， 8執行緒下ndb平均執行一個操作時間少於innodb，當併發越多的情況下，ndb平均執行一個操作時間多於innodb，併發 越多，ndb效率會越來越低。
	sysbench測試 100000記錄表
併發執行緒數量	8	16	32
NDB
transactions:	10000  (345.75 per sec.)	10000  (437.78 per sec.)	Error  ，報錯了資源不足
deadlocks:	0      (0.00 per sec.)	0      (0.00 per sec.)
read/write requests:	190000 (6569.20 per sec.)	190000 (8317.76 per sec.)
other operations:	20000  (691.49 per sec.)	20000  (875.55 per sec.)
InnoDB
transactions:	10000  (382.77 per sec.)	10000  (398.89 per sec.)	10000  (421.68 per sec.)
deadlocks:	0      (0.00 per sec.)	0      (0.00 per sec.)	0      (0.00 per sec.)
read/write requests:	190000 (7272.71 per sec.)	190000 (7578.88 per sec.)	190000 (8011.90 per sec.)
other operations:	20000  (765.55 per sec.)	20000  (797.78 per sec.)	20000  (843.36 per sec.)
	總結：用sysbench測試，執行緒次數越多，每秒執行的效率，ndb要高於innodb。

最後歸納總結：由於測試機器的配置過於低端，導致不能進行大資料量的測試，所以這些資料僅供參考而已。

參考：
http://www.mysqlperformanceblog.com/2013/07/01/tpcc-mysql-simple-usage-steps-and-how-to-build-graphs-with-gnuplot/