电玩城打鱼捕鱼-专业24小时上下分

15设置配置性能测量检验,压力测量检验

六月 27th, 2019  |  电玩城上下分数据库

安装和使用TPCC-MySQL工具遇到的问题

 TPCC-MYSQL是由percona发布一个用来测试数据库的压力工具,模拟一个电商的业务,

1 下载 Download the cluster tar.gz
wget

 

主要的业务有新增订单,库存查询,发货,支付等模块的测试

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)

本文主要讲述 TPCC-MySQL 工具在获取和使用时遇到的问题。

 

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

 

1.下载

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
[[email protected]
mysql-cluster-gpl-7.2.15-linux2.6-x86_64]# vi ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

       Tpcc-mysql 是 percona 基于 tpcc 衍生出来的产品,专用于 mysql
基准测试,其源码放在 bazaar 上( Bazaar 是一个分布式的版本控制系统,采用
GPL 许可协议,可运行于 Windows、GNU/Linux、UNIX 以及 Mac OS
系统之上。Bazaar 由 Canonical 公司(Ubuntu
母公司)赞助),因此还需要先安装 bazaar 客户端。 

2.安装

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

 

1.解压   cd scr ; make

3.4 create the file of log file store
mkdir -p /var/lib/mysql-cluster

【工具的获取】 

make命令会在tpcc-mysql目录下生成 tpcc 命令行工具 tpcc_load ,tpcc_start

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

 

tpcc_load  提供初始化数据的功能

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

  1. 安装 bzr 客户端 

tpcc_start 进行压力测试

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

      可以通过 yum -y install bzr 方式安装 bzr ,但默认会安装到
/usr/lib64/python2.4/site-packages 之中(我的系统为 CentOS release 5.6
(Final) )。如果希望安装的位置是 /usr/local/lib/python2.7/site-packages
,通过 yum 方式可能无法满足(或者说我不知道怎么操作)。于是采用 pip
方式安装。 

[root@mysql-centos6 src]# make

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

1

cc -w -O2 -g -I. `mysql_config –include`  -c load.c

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

[[email protected]
site-packages]# pip install bzr

cc -w -O2 -g -I. `mysql_config –include`  -c support.c

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

 

cc load.o support.o `mysql_config –libs_r` -lrt -o ../tpcc_load

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

安装成功后,可以看到 

cc -w -O2 -g -I. `mysql_config –include`  -c main.c

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

1

cc -w -O2 -g -I. `mysql_config –include`  -c spt_proc.c

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

[[email protected]
site-packages]# pip show bzr

cc -w -O2 -g -I. `mysql_config –include`  -c driver.c

4.5 配置数据节点连接管理节点
ndb-connectstring=10.88.49.119

2

cc -w -O2 -g -I. `mysql_config –include`  -c sequence.c

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


cc -w -O2 -g -I. `mysql_config –include`  -c rthist.c

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

3

cc -w -O2 -g -I. `mysql_config –include`  -c neword.c

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

Name: bzr

cc -w -O2 -g -I. `mysql_config –include`  -c payment.c

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

4

cc -w -O2 -g -I. `mysql_config –include`  -c ordstat.c

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

Version: 2.6.0

cc -w -O2 -g -I. `mysql_config –include`  -c delivery.c

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
[[email protected]
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).

5

cc -w -O2 -g -I. `mysql_config –include`  -c slev.c

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

Location: /usr/local/lib/python2.7/site-packages

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

You can try to start the mysqld daemon with:

6

 

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

Requires:

2.修改PATH

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

7

[root@mysql-centos6 ~]# vim .bash_profile

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

[[email protected]
site-packages]#

PATH=$PATH:$HOME/bin:/usr/local/xtrabackup/bin:/usr/local/mysql/bin/:/usr/local/mysql/bin/

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

 

3.socket文件

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 .

 

tpcc 默认会读取/var/lib/mysql/mysql.sock
这个socket位置,如果你的测试环境的mysql
socket不在相应路径的话,就需要做个软连接,或者通过TCP/IP的方式连接测试服务器。

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!

  1. 用 bzr 客户端下载 tpcc-mysql 源码 

ln -s /var/run/mysqld/mysql.sock /var/lib/mysql/mysql.sock

[]必须使用全路径/usr/local/mysqlndb/scripts/mysql_install_db,不然识别,就会自动调用为原来的初始化路径。
[[email protected]
mysqlndb]# cp /etc/myndb.cnf /usr/local/mysqlndb/my.cnf
[[email protected]
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

 

3.创建数据库和表结构

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

      首先需要登录到
上注册帐号(假设注册后用户 ID 为 special-agent ),并登陆。 

# 创建测试用的数据库

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

 

[root@mysql-centos6 src]# mysqladmin create tpcc1000

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

      然后需要在 linux 下生成 SSH 使用的密钥。 

mysqladmin: connect to server at ‘localhost’ failed

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

01

error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’

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.

[[email protected]
WGET_DIR]# ssh-keygen -t rsa

[root@mysql-centos6 src]# mysqladmin -uroot -p123123 create tpcc1000

See the manual for more instructions.

02

Warning: Using a password on the command line interface can be insecure.

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

Generating public/private rsa key pair.

# 创建测试用的表

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

03

[root@mysql-centos6 tpcc-mysql]# mysql -uroot -p123123 -h127.0.0.1
-P3306 -S /var/run/mysqld/mysql.sock tpcc1000 < create_table.sql

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

Enter file in which to save the key (/root/.ssh/id_rsa):

Warning: Using a password on the command line interface can be insecure.

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

04

 

[[email protected]
mysqlndb]# /etc/init.d/mysqldndbd status
ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql)
exists
[[email protected]
mysqlndb]# /etc/init.d/mysqldndbd status
ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql)
exists
[[email protected]
mysqlndb]# mv /var/lock/subsys/mysql /var/lock/subsys/mysql.bak
[[email protected]
mysqlndb]# /etc/init.d/mysqldndbd start
Starting MySQL… SUCCESS!

Created directory ‘/root/.ssh’.

# 创建FK和索引

6 start the cluster
6.1 start the management node
ndb_mgmd -f /usr/local/mysql/cluster-conf/config.ini

05

[root@mysql-centos6 tpcc-mysql]# mysql -uroot -p123123 -h127.0.0.1
-P3306 -S /var/run/mysqld/mysql.sock tpcc1000 < add_fkey_idx.sql

[[email protected]
~]# 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=<nodeid>
on command line

Enter passphrase (empty for no passphrase):

Warning: Using a password on the command line interface can be insecure.

检查是从window下copy到liux下,某些空格失效了。
修改/usr/local/mysql/cluster-conf/config.ini,将[NDBDDEFAULT]换成[NDBD
DEFAULT],中间少了一个空格。

06

4.加载数据

6.2 start the data node
ndbd –initial
#(第一次启动必须添加选项,另外备份/恢复,修改配置文件也需要执行)
ndbd #不是第一次启动需要执行的命令

Enter same passphrase again:

用法:

/usr/local/mysql/bin/ndbd –defaults-file=/etc/mycluster.cnf –initial
启动时候指定读取的my.cnf配置文件。

07

# ./tpcc_load –help

去check下状态
[[email protected]
~]# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186

Your identification has been saved in /root/.ssh/id_rsa.

tpcc_load [server] [DB] [user] [pass] [warehouse]

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以下了,依然报同样的错误,肯定不是内存不足,去看管理节点的日志信息
[[email protected]
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

然后重新启动管理节点以及数据节点。
[[email protected]
mysql-cluster1]# ndb_mgmd -f
/usr/local/mysql/cluster-conf/config.ini
MySQL Cluster Management Server mysql-5.5.35 ndb-7.2.15
[[email protected]
~]# /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下,查看数据节点状态:
[[email protected]
mysql-cluster1]# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186

08

server :    服务器名:端口号

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.
[[email protected]
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

[[email protected]
data]# /etc/init.d/ndbdsql start
Starting MySQL….. ERROR! The server quit without updating PID file
(/data/mysql.pid).
[[email protected]
data]#
[[email protected]
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文件
[[email protected]
data]# /etc/init.d/ndbdsql start
Starting MySQL….. ERROR! The server quit without updating PID file
(/data/mysql.pid).
[[email protected]
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* 文件

[[email protected]
data]# /etc/init.d/ndbdsql start
Starting MySQL…………………. SUCCESS!
[[email protected]
data]#

[[email protected]
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

[[email protected]
~]# 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
[[email protected]
~]#

然后就可以开始安装bzr客户端了:
yum install bzr

之后,就可以开始用bzr客户端下载tpcc-mysql源码了。
cd tmp
bzr branch lp:~percona-dev/perconatools/tpcc-mysql

[[email protected]
~]# 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
[[email protected]
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目录就有了
[[email protected]
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目录
[[email protected]
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/
[[email protected]
src]# export C_INCLUDE_PATH=/usr/local/mysqlndb/include/
[[email protected]
src]#
[[email protected]
src]#
[[email protected]
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
此处参考:

[[email protected]
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
[[email protected]
src]#

[]check 生成的脚本
[[email protected]
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 <./create_table_ndb.sql
/usr/local/mysqlndb/bin/mysql -uroot -p -P3307
–socket=/data/mysql.socket tpcc100 <./add_fkey_idx.sql

[]导入数据
[[email protected]
tpcc-mysql]# ./tpcc_load localhost tpcc1000 root “” 20
*************************************
*** ###easy### TPC-C Data Loader ***
*************************************
<Parameters>
[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的方式连接测试服务器。

[[email protected]
tpcc-mysql]# ./tpcc_load localhost tpcc100 root “” 20
*************************************
*** ###easy### TPC-C Data Loader ***
*************************************
<Parameters>
[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
[[email protected]
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

[[email protected]
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

[[email protected]
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

[[email protected]
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
[[email protected]
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
[[email protected]
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
[[email protected]
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
[[email protected]
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
[[email protected]
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
[[email protected]
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
[[email protected]
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
[[email protected]
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
[[email protected]
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
[[email protected]
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测试
[[email protected]
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
[[email protected]
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
[[email protected]
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

[[email protected]
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个线程
[[email protected]
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
[[email protected]
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
[[email protected]
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个线程
[[email protected]
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个线程
[[email protected]
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。

最后归纳总结:由于测试机器的配置过于低端,导致不能进行大数据量的测试,所以这些数据仅供参考而已。

参考:

下载 Download the cluster tar.gz wget

MySQL Cluster 7.2.15 Select Version: 下拉框选择…

Your public key has been saved in /root/.ssh/id_rsa.pub.

DB     :    数据库名

09

user   :    用户名

The key fingerprint is:

pass   :    密码

10

warehouse: 仓库的数量

17:b3:91:e6:b2:47:5c:15:5f:d6:ee:8a:d8:b1:5c:da
[email protected]

加载数据需要一段时间,并且会产生大量输出信息,我们一般将程序输出定向到文件中。避免丢失滚动信息

11

[root@mysql-centos6 tpcc-mysql]# ./tpcc_load 127.0.0.1:3306 tpcc1000
root 123123 10

[[email protected]
WGET_DIR]#

5.执行测试

 

# ./tpcc_start –help

      之后将公钥所在文件 id_rsa.pub 的内容复制到

中的输入框中,并提交(红色部分换成你自己的 UserID)。 

-h server_host:      服务器名

 

-P port :             端口号,默认为3306

最后通过 bzr 命令下载 tpcc-mysql 源码。 

-d database_name:     数据库名

01

-u mysql_user :        用户名

[[email protected]
WGET_DIR]# bzr help launchpad-login

-p mysql_password :    密码

02

-w warehouses:        仓库的数量

Purpose: Show or set the Launchpad user ID.

-c connections :       线程数,默认为1 指定并发连接数

03

-r warmup_time :       热身时间,单位:s,默认为10s
,热身是为了将数据加载到内存。

Usage:   bzr launchpad-login [NAME]

-l running_time:      测试时间,单位:s,默认为20s

04

-i report_interval     指定生成报告间隔时长

 

-f report_file :       测试结果输出文件

05

[root@mysql-centos6 tpcc-mysql]# ./tpcc_start -h127.0.0.1 -P3306 -d
tpcc1000 -uroot -p123123 -w 10 -c 12 -r 120 -l 15 -f
tpcc_mysql_20160512

Options:

***************************************

06

*** ###easy### TPC-C Load Generator ***

  –usage        Show usage message and options.

***************************************

07

option h with value ‘127.0.0.1’

  –no-check     Don’t check that the user name is valid.

option P with value ‘3306’

08

option d with value ‘tpcc1000’

  -q, –quiet    Only display errors and warnings.

option u with value ‘root’

09

option p with value ‘123123’

  -v, –verbose  Display more information.

option w with value ’10’

10

option c with value ’12’

  -h, –help     Show help message.

option r with value ‘120’

11

option l with value ’15’

 

option f with value ‘tpcc_mysql_20160512’

12

<Parameters>

Description:

     [server]: 127.0.0.1

13

     [port]: 3306

  When communicating with Launchpad, some commands need to know your

     [DBname]: tpcc1000

14

       [user]: root

  Launchpad user ID.  This command can be used to set or show the

       [pass]: 123123

15

  [warehouse]: 10

  user ID that Bazaar will use for such communication.

 [connection]: 12

16

     [rampup]: 120 (sec.)

 

    [measure]: 15 (sec.)

17

 

Examples:

RAMP-UP TIME.(120 sec.)

18

  Show the Launchpad ID of the current user:

19

 

<TpmC>

20

                 1536.000 TpmC

      bzr launchpad-login

测试成功!

21

 

22

  Set the Launchpad ID of the current user to ‘bob’:

23

 

24

      bzr launchpad-login bob

25

 

26

Aliases:  lp-login

27

From:     plugin “launchpad”

1

[[email protected]
WGET_DIR]# bzr launchpad-login special-agent

1

[[email protected]
WGET_DIR]# bzr branch lp:~percona-dev/perconatools/tpcc-mysql

2

The authenticity of host ‘bazaar.launchpad.net (91.189.95.84)’ can’t be
established.

3

RSA key fingerprint is 9d:38:3a:63:b1:d5:6f:c4:44:67:53:49:2e:ee:fc:89.

4

Are you sure you want to continue connecting (yes/no)? yes

5

Warning: Permanently added ‘bazaar.launchpad.net,91.189.95.84’ (RSA) to
the list of known hosts.

6

Branched 48 revisions.

7

[[email protected]
WGET_DIR]#

 

查看下载下来的文件。 

01

[[email protected]
WGET_DIR]# ll tpcc-mysql/

02

total 36

03

-rw-r–r– 1 root root  851 Jul 30 14:30 README

04

-rw-r–r– 1 root root 1621 Jul 30 14:30 add_fkey_idx.sql

05

-rw-r–r– 1 root root  317 Jul 30 14:30 count.sql

06

-rw-r–r– 1 root root 3105 Jul 30 14:30 create_table.sql

07

-rw-r–r– 1 root root  763 Jul 30 14:30 drop_cons.sql

08

-rw-r–r– 1 root root  477 Jul 30 14:30 load.sh

09

drwxr-xr-x 2 root root 4096 Jul 30 14:30 schema2

10

drwxr-xr-x 5 root root 4096 Jul 30 14:30 scripts

11

drwxr-xr-x 2 root root 4096 Jul 30 14:30 src

12

[[email protected]
WGET_DIR]#

 

可能需要对 Makefile 文件做调整才能通过编译。我的 改动如下: 

01

#

02

# “make all” to build necessary executables.

03

#

04

 

05

LIBS=           `mysql_config –libs_r` -lrt

06

INC=            -I. `mysql_config –include`
 -I/usr/local/mysql/include/

07

#DEFS=          -DDEBUG

08

CFLAGS=         -w -O2 -g

09

TRANSACTIONS=   neword.o payment.o ordstat.o delivery.o slev.o

10

OBJS=           main.o spt_proc.o driver.o support.o sequence.o
rthist.o $(TRANSACTIONS)

11

 

12

.SUFFIXES:

13

.SUFFIXES: .o .c

14

 

15

.c.o:

16

        $(CC) $(CFLAGS) $(INC) $(DEFS) -c $*.c

17

 

18

all: ../tpcc_load ../tpcc_start

19

 

20

../tpcc_load : load.o support.o

21

        $(CC) load.o support.o $(LIBS) -L/usr/local/mysql/lib/ -o
../tpcc_load

22

 

23

../tpcc_start : $(OBJS)

24

        $(CC) $(OBJS) $(LIBS) -L/usr/local/mysql/lib/ -o ../tpcc_start

25

 

26

clean :

27

        rm -f *.o

 

执行 make 后生成工具 tpcc_load 和 tpcc_start 。 

01

[[email protected]
src]#

02

[[email protected]
src]# make

03

cc -w -O2 -g -I. `mysql_config –include`
 -I/usr/local/mysql/include/  -c load.c

04

cc -w -O2 -g -I. `mysql_config –include`
 -I/usr/local/mysql/include/  -c support.c

05

cc load.o support.o `mysql_config –libs_r` -lrt
-L/usr/local/mysql/lib/ -o ../tpcc_load

06

cc -w -O2 -g -I. `mysql_config –include`
 -I/usr/local/mysql/include/  -c main.c

07

cc -w -O2 -g -I. `mysql_config –include`
 -I/usr/local/mysql/include/  -c spt_proc.c

08

cc -w -O2 -g -I. `mysql_config –include`
 -I/usr/local/mysql/include/  -c driver.c

09

cc -w -O2 -g -I. `mysql_config –include`
 -I/usr/local/mysql/include/  -c sequence.c

10

cc -w -O2 -g -I. `mysql_config –include`
 -I/usr/local/mysql/include/  -c rthist.c

11

cc -w -O2 -g -I. `mysql_config –include`
 -I/usr/local/mysql/include/  -c neword.c

12

cc -w -O2 -g -I. `mysql_config –include`
 -I/usr/local/mysql/include/  -c payment.c

13

cc -w -O2 -g -I. `mysql_config –include`
 -I/usr/local/mysql/include/  -c ordstat.c

14

cc -w -O2 -g -I. `mysql_config –include`
 -I/usr/local/mysql/include/  -c delivery.c

15

cc -w -O2 -g -I. `mysql_config –include`
 -I/usr/local/mysql/include/  -c slev.c

16

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
-L/usr/local/mysql/lib/ -o ../tpcc_start

17

[[email protected]
src]#

 

 

查看 README 文件的内容(简单实用的说明) 

01

[[email protected]
tpcc-mysql]# vi README

02

 

03

  1. Build binaries

04

   * cd scr ; make

05

   ( you should have mysql_config available in $PATH)

06

 

07

 

08

  1. Load data

09

   * create database

10

     mysqladmin create tpcc1000

11

   * create tables

12

     mysql tpcc1000 < create_table.sql

13

   * create indexes and FK ( this step can be done after loading data)

14

     mysql tpcc1000 < add_fkey_idx.sql

15

   * populate data

16

     – simple step

17

       tpcc_load 127.0.0.1:33000 tpcc1000 root “” 1000

18

                 |hostname:port| |dbname| |user| |password| |WAREHOUSES|

19

       ref. tpcc_load –help for all options

20

     – load data in parallel

21

       check load.sh script

22

 

23

 

24

  1. start benchmark

25

   * ./tpcc_start -h127.0.0.1 -P33000 -dtpcc1000 -uroot -w1000 -c32
-r10 -l10800

26

                  |hostname| |port| |dbname| |user| |WAREHOUSES|
|CONNECTIONS| |WARMUP TIME| |BENCHMARK TIME|

27

   * ref. tpcc_start –help for all options

 

查看两个工具的帮助命令。 

01

[[email protected]
tpcc-mysql]# ./tpcc_load –help

02

*************************************

03

*** ###easy### TPC-C Data Loader  ***

04

*************************************

05

 

06

 usage: tpcc_load [server] [DB] [user] [pass] [warehouse]

07

      OR

08

        tpcc_load [server] [DB] [user] [pass] [warehouse]
[part] [min_wh] [max_wh]

09

 

10

           * [part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS

 

1

[[email protected]
tpcc-mysql]# ./tpcc_start –help

2

***************************************

3

*** ###easy### TPC-C Load Generator ***

4

***************************************

5

./tpcc_start: invalid option — –

6

Usage: tpcc_start -h server_host -P port -d database_name -u
mysql_user -p mysql_password -w warehouses -c connections -r
warmup_time -l running_time -i report_interval -f report_file -t
trx_file

7

[[email protected]
tpcc-mysql]#

 

 

【工具的简单使用】 

 

创建库 tpcc100 。 

1

[[email protected]
tpcc-mysql]# mysqladmin create tpcc100

2

mysqladmin: connect to server at ‘localhost’ failed

3

error: ‘Can’t connect to local MySQL server through socket
‘/var/lib/mysql/mysql.sock’ (2)’

4

Check that mysqld is running and that the socket:
‘/var/lib/mysql/mysql.sock’ exists!

5

[[email protected]
tpcc-mysql]#

6

[[email protected]
tpcc-mysql]# mysqladmin -S /tmp/mysql.sock create tpcc100

 

创建表。 

1

[[email protected]
tpcc-mysql]# mysql -S /tmp/mysql.sock tpcc100 < create_table.sql

 

创建索引和 FK 。 

1

[[email protected]
tpcc-mysql]# mysql -S /tmp/mysql.sock tpcc100 < add_fkey_idx.sql

 

创建 100 个数据仓库并填充数据(即使100个仓库也花了好久时间)。 

01

[[email protected]
tpcc-mysql]#

02

[[email protected]
tpcc-mysql]# ./tpcc_load localhost tpcc100 root “” 100

03

*************************************

04

*** ###easy### TPC-C Data Loader  ***

05

*************************************

06

<Parameters>

07

     [server]: localhost

08

     [port]: 3306

09

     [DBname]: tpcc100

10

       [user]: root

11

       [pass]:

12

  [warehouse]: 100

13

TPCC Data Load Started…

14

Loading Item

15

………………………………………….. 5000

16

………………………………………….. 10000

17

………………………………………….. 15000

18

 

19

 

20

21

 

22

 

23

Loading Orders for D=4, W= 100

24

………. 1000

25

………. 2000

26

………. 3000

27

Orders Done.

28

Loading Orders for D=5, W= 100

29

………. 1000

30

………. 2000

31

………. 3000

32

Orders Done.

33

Loading Orders for D=6, W= 100

34

………. 1000

35

………. 2000

36

………. 3000

37

Orders Done.

38

Loading Orders for D=7, W= 100

39

………. 1000

40

………. 2000

41

………. 3000

42

Orders Done.

43

Loading Orders for D=8, W= 100

44

………. 1000

45

………. 2000

46

………. 3000

47

Orders Done.

48

Loading Orders for D=9, W= 100

49

………. 1000

50

………. 2000

51

………. 3000

52

Orders Done.

53

Loading Orders for D=10, W= 100

54

………. 1000

55

………. 2000

56

………. 3000

57

Orders Done.

58

 

59

 

60

…DATA LOADING COMPLETED SUCCESSFULLY.

61

[[email protected]
tpcc-mysql]#

 

对 100 个数据仓库,预热 120 秒,100 个并发连接,运行 3600 秒,
结果存放在文件 tpcc100_20130730 中。 

001

[[email protected]
tpcc-mysql]#

002

[[email protected]
tpcc-mysql]# ./tpcc_start -h localhost -d tpcc100  -u root -p ” -w
100 -c 100 -r 120 -l 3600 -f tpcc100_20130730

003

***************************************

004

*** ###easy### TPC-C Load Generator ***

005

***************************************

006

option h with value ‘localhost’

007

option d with value ‘tpcc100’

008

option u with value ‘root’

009

option p with value ”

010

option w with value ‘100’

011

option c with value ‘100’

012

option r with value ‘120’

013

option l with value ‘3600’

014

标签:

Your Comments

近期评论

    功能


    网站地图xml地图