Optimization Series | MySQL Cluster 7.2.7 Memory table and disk table comparison test

First, the preparatory work
Since 2009, after testing MySQL Cluster 7.0, I have not paid much attention to it, development is too slow, there are a lot of unreliable places. Pre-array Exit 7.2.7 version, looked at the new features introduced, claiming that the performance is much higher than the previous version, so pay attention to and test.
The deployment process is not much to say, download the PRM package directly after installation. A total of 10 nodes, of which 1 management nodes, the other 9 nodes as both data and SQL nodes, all node server configuration diagram:
650) this.width=650; "Src=" http://dp.imysql.com:8080/files/upload_yejr_imysql/mysql_cluster_tpcc_testing_env_ 20120925.png "title=" mysql_cluster_tpcc_testing_env "style=" border:0px; "alt=" mysql_cluster_tpcc_testing_env_ 20120925. "/>
MySQL Cluster Management node key configuration see below:

# #ndb  config.ini#[tcp default]sendbuffermemory=32mreceivebuffermemory=32m[ndb_mgmd default] Portnumber=1186datadir=/home/cluster/mgm[ndb_mgmd]nodeid=1hostname=x.x.x.xlogdestination=file:filename=ndb_1_ Cluster.log,maxsize=10000000,maxfiles=6arbitrationrank=1[ndbd default]noofreplicas=3datadir=/home/cluster /datafilesystempathdd=/home/cluster/data/diskdatabackupdatadir=/home/cluster/backup#filesystempathundofiles=/ home/cluster/data#filesystempathdatafiles=/home/cluster/datadatamemory=21633mindexmemory= 2705mlockpagesinmainmemory=1# the number of rows of data that can be modified relative to the:  transaction maxnoofconcurrentoperations= 100000maxnoofconcurrenttransactions=16384stringmemory=25maxnooftables=4096maxnooforderedindexes= 2048maxnoofuniquehashindexes=512maxnoofattributes=24576maxnooftriggers=14336### params for redo  log fragmentlogfilesize=256minitfragmentlogfiles=sparsenooffragmentlogfiles=85redobuffer= 64mtransactionbuffermemory=32m#timebetweenglobalcheckpoints=1000timebetweenglobalcheckpoints=100timebetweenepochs=100timebetweenepochstimeout=32000### params for lcp  Diskcheckpointspeedinrestart=100mdiskcheckpointspeed=10mtimebetweenlocalcheckpoints=20### heartbeating  heartbeatintervaldbdb=1500heartbeatintervaldbapi=1500### params for setting logging  memreportfrequency=30backupreportfrequency=10loglevelstartup=15loglevelshutdown=15loglevelcheckpoint= 8loglevelnoderestart=15### params for backup backupmaxwritesize=1mbackupdatabuffersize= 16mbackuplogbuffersize=4mbackupmemory=20m### params for odirect  #Reports  indicates  that odirect=1 can cause io errors  (os err code 5)  on  some systems. you must test. #ODirect =1### watchdog  Timebetweenwatchdogcheckinitial=60000### transactioninactivetimeout  - should be  enabled in Production  #TransactionInactiveTimeout=60000transactioninactivetimeout=6000#transactiondeadlockdetectiontimeout =  1200transactiondeadlockdetectiontimeout = 12000### new 7.1.10 redo logging  parameters redoovercommitcounter=3redoovercommitlimit=20### realtime extensions # realtimescheduler=1### realtime extensions for 6.3 only#schedulerexecutiontimer=80# schedulerspintimer=40### disk data sharedglobalmemory=20mdiskpagebuffermemory=64m###  Multithreading maxnoofexecutionthreads=4### increasing the longmessagebuffer b/c  of a bug  (20090903) longmessagebuffer=16mbatchsizeperlocalscan=512### realtime  aspects - thread binding ### read http://johanandersson.blogspot.com/2008/02/ Mysql-cluster-features-what-they-are.html#realtime#lockexecutethreadtocpu=x#lockmaintthreadstocpu=y[mysqld  default]defaultoperationredoproblemaction=abortbatchsize=512#batchbytesize=2048k#maxscanbatchsize=2048k 

Note here is that need to allocate some API node out, otherwise start cluster, may report no residual Nodeid error, this problem also bothered me for a half day.
By the way, this profile is mainly generated by the free online tool, previously posted: Online generation of MySQL cluster configuration files. The Administrator of the tool is very responsible, and shortly after I generated the configuration file, I also sent an e-mail to ask me how to use, error improvement suggestions, praise.
The main contents of the Data node and SQL node configuration file are as follows:

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

Once the configuration is complete, you can start the test. This process is relatively simple, you can read the manual, just remember the data node on the first start to add --initial option.

II. Test Process & Results
1.) Create disk table space
(a) Creation of logfile GROUP

Create logfile group ndb_loggrp ADD undofile ' ndb_undo_01.dbf ' initial_size=1024m engine=ndbcluster;alter logfile Group Ndb_loggrp ADD undofile ' ndb_undo_02.dbf ' initial_size=1024m engine=ndbcluster;

Attention:

• There can only be one logfile group

• Only one undofile can be added at a time

• Redo_buffer_size is already set in the configuration file, the command line cannot be modified or reset +

(b) Create a data table space group

Create tablespace ndb_tbspc_01 ADD datafile ' ndb_tbspc_01.dbf ' use LOGFILE GROUP ndb_loggrp extent_size = 64M Initial_ SIZE = 1024M Autoextend_size = 64M ENGINE = ndbcluster; Alter tablespace ndb_tbspc_01 ADD datafile ' ndb_tbspc_02.dbf ' initial_size=1024m engine=ndbcluster;...alter tablespace ndb_tbspc_01 ADD datafile ' ndb_tbspc_10.dbf ' initial_size=1024m engine=ndbcluster; #类似地, creating ndb_tbspc_02, also 10 data files, Do not repeat the demo

Attention:

• Multiple data table space files can be created

• Multiple data table spaces, including log space, files cannot have the same name

• Before deleting a data table space, you need to delete the data file

Shared

• 1 x Redo LOGFILE GROUP, Space 2GB;

• 2 data table space, 20 data files, each data file 1GB, a total of 20GB;

you can see that in the initialization phase, the disk table is not much slower than the memory table.
The table space used by each table in the disk table is allocated as follows:
650) this.width=650; "Src=" Http://dp.imysql.com:8080/files/upload_yejr_imysql/mysql_ Cluster_tpcc_testing_prepare_20120925.png "title=" mysql_cluster_tpcc_testing_env "style=" border:0px; "alt=" Mysql_ Cluster_tpcc_testing_prepare_20120 "/>

3.) Comparison of the statistics of the tables after TPCC initialization
After the execution of the TPCC data initialization, the memory table and disk table of the table related data statistics are not exactly the same, see below:
650) this.width=650; "Src=" Http://dp.imysql.com:8080/files/upload_yejr_imysql/mysql_cluster_tpcc_table_status_ 20120925.png "title=" mysql_cluster_tpcc_testing_env "style=" border:0px; "alt=" Mysql_cluster_tpcc_table_status_ 20120925 "/>
Reason: Not clear, later to learn more about the next update this article.

4.) Comparison of TPCC concurrency test results
Perform multiple TPCC concurrent tests, and after averaging, the corresponding results are shown below:
650) this.width=650; "Src=" Http://dp.imysql.com:8080/files/upload_yejr_imysql/mysql_cluster_tpcc_testing_result_ 20120925.png "title=" mysql_cluster_tpcc_testing_env "style=" border:0px; "alt=" Mysql_cluster_tpcc_testing_result_ 201209 "/>

Summary: From the test results, MySQL cluster measurement performance is still very poor, the same machine configuration, changed to 2 10K RPM SAS disk to do RAID 1, 1000 DW,TPMC the highest can run to more than 1900. and MySQL cluster performance is indeed more general, of course, this and my test environment has a lot of relationship, after all, just hundred M network. In any case, MySQL cluster is still not suitable for high-concurrency OLTP scenarios, which are more appropriate in low concurrency but require highly available scenarios. It is said that there are many similar telecommunications enterprises have been fooled on the MySQL Cluster, hehe.
Limited resources, the opportunity to re-test and update the blog post, O (∩_∩) o haha ~

Add: Try to make a node ndbd and SQL process forcibly killed, memory table total size about 6414MB, disk table total size about: 5568MB (in fact, the data is the same amount of storage space is not the same) scenario, the node started recovery data, a total of 28 minutes, In the Hundred Trillion network environment, still can accept.

--------------------------------------Split Line--------------------------------------

http://zhishuedu.comTraining is a professional quality training brand jointly launched by senior MySQL expert Ye Jinlong and Wu Bingxi, with MySQL dba combat optimization and Python devops Development course, which is the most conscientious and The most quality training courses.

This article is from the "Lao Ye teahouse" blog, please be sure to keep this source http://imysql.blog.51cto.com/1540006/1879743

Optimization Series | MySQL Cluster 7.2.7 Memory table and disk table comparison test