2.6亿条数据测试.docx

1 测试环境使用4台32g内存24核服务器进行测试。在2.6亿条数据下进行测试，ddl.sql语句如下：CREATE TABLE YH_DYYH ( HH VARCHAR(25) NOT NULL, CJDYID VARCHAR(25) NOT NULL, DWDM VARCHAR(25) NOT NULL, TQID VARCHAR(25) NOT NULL, YHLX VARCHAR(25) NOT NULL, SFYZX VARCHAR(25) NOT NULL, AZRQ VARCHAR(25) NOT NULL, PRIMARY KEY(HH);PARTITION TABLE YH_DYYH ON COLUMN HH;CREATE TABLE SB_DYSJZB ( SJID VARCHAR(25) NOT NULL, DWDM VARCHAR(25) NOT NULL, HH VARCHAR(25) NOT NULL, ZDJH VARCHAR(25) NOT NULL, CLDJH VARCHAR(25) NOT NULL, BJZSBL FLOAT, SFYX INt, PRIMARY KEY(SJID);PARTITION TABLE SB_DYSJZB ON COLUMN HH;CREATE TABLE SB_DYDLSJ ( SJID VARCHAR(25) NOT NULL, SJSJ VARCHAR(25) NOT NULL, CT FLOAT, PT FLOAT, ZXYGZ FLOAT, ZXYGZ1 FLOAT, ZXYGZ2 FLOAT, ZXYGZ3 FLOAT, ZXYGZ4 FLOAT, BQBJ INt, PRIMARY KEY(SJID,SJSJ);PARTITION TABLE SB_DYDLSJ ON COLUMN SJID;CREATE INDEX HHINDEX ON YH_DYYH(HH);2 csvloader工具加载数据对加载数据的测试，对YH_DYYH表加载1000w条数据，对SB_DYSJZB加载5000w条数据，对SB_DYDLSJ表加载1.5亿条数据，分步进行执行，每个文件保存5000w条数据。下面主要针对加载时间和每秒执行的事务数进行测试，测试结果如下：加载YH_DYYH表数据：csvloader elaspsed: 125.827 secondsNumber of rows read from input: 10000000Number of rows successfully inserted: 10000000Number of rows that could not be inserted: 0CSVLoader rate: 79474.195 row/s加载SB_DYSJZB表数据：CSVLoader elapsed: 642.436 secondsNumber of rows read from input: 50000000Number of rows successfully inserted: 50000000Number of rows that could not be inserted: 0CSVLoader rate: 77828.764 row/s加载SB_DYDLSJ表数据:csvloader elaspsed: 879.899 secondsNumber of rows read from input: 50000000Number of rows successfully inserted: 50000000Number of rows that could not be inserted: 0CSVLoader rate: 56824.703 row/scsvloader elaspsed: 763.452 secondsNumber of rows read from input: 50000000Number of rows successfully inserted: 50000000Number of rows that could not be inserted: 0CSVLoader rate: 65492.002 row/scsvloader elaspsed: 733.842 secondsNumber of rows read from input: 50000000Number of rows successfully inserted: 50000000Number of rows that could not be inserted: 0CSVLoader rate: 68134.557 row/s3 占用空间导入数据的文件大小YH_DYYH占磁盘空间大小为557M，SB_DYSJZB表占用磁盘空间大小为2.6G,SB_DYDLSJ表占用磁盘空间大小为18.9G，总共的磁盘空间占用22G左右。主要进行测试数据的磁盘占用空间和导入后内存占用空间，现在测试kfactor=0的情况下内存占用空间情况，调用系统存储过程，得到集群中每个节点内存占用情况。测试结果如下：TIMESTAMP HOST_ID HOSTNAME RSS JAVAUSED JAVAUNUSED TUPLEDATA TUPLEALLOCATED INDEXMEMORY STRINGMEMORY TUPLECOUNT POOLEDMEMORY - - - - - - - - - - - -1364969665752 2 12102928 266482 122253 6330556 6344704 5045304 0 52499978 33710 1364969665752 0 11981088 297942 89321 6330563 6344704 5045304 0 52500022 337101364969665752 1 11916096 302946 84253 6330563 6342656 5045303 0 52500020 337101364969665752 3 11968784 300899 86364 6330557 6340608 5045303 0 52499980 33710上面的测试数据可知java占用3.8G左右，TUPLEDATA 实际数据记录占用内存，大概占用6.3G,TUPLEALLOCATED 数据记录分配的内存大小（包括自由空间）。INDEXMEMORY 索引占用的内存量，大概占用5.0G左右。每个节点大约占用11.3G左右，内存空间总共占用45.2G左右。内存占用空间/磁盘空间=2倍左右kfactor=1的时候，保存了一份副本，内存占用空间/磁盘空间=4倍左右。4 存储过程测试4.1 带索引字段单表查询在yh_dyyh表中使用索引hh字段进行查询，测试其查询时间，存储过程sql语句：SELECT HH,CJDYID,DWDM,TQID,YHLX,SFYZX,AZRQ FROM YH_DYYH WHERE HH = ?rootclient2 stategrid2# ./run.sh selectonedata 0Selected: hh = 0 CJDYID = 57fbwkltmx dwdm = 6hk cldjh = 2011-07-27 16:56:26take times: 32msrootclient2 stategrid2# ./run.sh selectonedata 1000Selected: hh = 1000 CJDYID = ls dwdm = aq1 cldjh = 2011-08-05 05:02:47take times: 29ms查询时间在30ms左右4.2 带索引多表联合查询三表联合查询，使用yh_dyyh中hh字段进行查询，查询到SB_DYSJZB中的sjid，再通过sjid和sjsj的时间范围查询到SB_DYDLSJ中存在的记录。是在一个存储过程中查询，以多分区方式进行。第一个参数是hh，第二个、第三个参数是时间格式的参数。该存储过程sql语句如下：SELECT sjzb.SJID,yd.YHLX FROM SB_DYSJZB sjzb,YH_DYYH yd WHERE sjzb.HH = yd.HH AND sjzb.HH = ?SELECT SJID,SJSJ,CT,PT,ZXYGZ,ZXYGZ1,ZXYGZ2,ZXYGZ3,ZXYGZ4,BQBJ FROM SB_DYDLSJ WHERE SJID = ?SELECT SJID,SJSJ,CT,PT,ZXYGZ,ZXYGZ1,ZXYGZ2,ZXYGZ3,ZXYGZ4,BQBJ FROM SB_DYDLSJ WHERE SJID = ? AND SJSJ = ? AND SJSJ = ? AND BQBJ = ?测试情况如下：rootclient2 stategrid2# ./run.sh deletemultipdata 1000 10000take times: 2903msrootclient2 stategrid2# ./run.sh deletemultipdata 10000 11000take times: 1736msrootclient2 stategrid2# ./run.sh deletemultipdata 11000 11500take times: 1520ms4.6 事务测试模拟多个客户端，对公有资源进行竞争，当资源为0，返回错误信息，资源不足。成功竞争资源的hh，在yh_dyyh记录中的SFYZX字段成功修改为success，sql语句如下：SELECT * from SB_DYSJZB where sjid=0;/得到公有资源sfyx字段值SELECT * from SB_DYDLSJ where sjid=0;/得到公有资源bqbj字段值Update SB_DYSJZB set SFYX=SFYX-1 where SJID = ?;update SB_DYDLSJ set BQBJ=BQBJ-1 where SJID=? AND SJSJ=?;update YH_DYYH set SFYZX=success where HH=?;测试整个过程的执行情况，模拟10、100、1000个客户端，测试情况如下：rootclient2 stategrid2# ./run.sh tranction-sync-benchmark 10Connecting to servers: - 31:21212Credentials: - NoneOptions: - Heavyweight: no - MaxTxnQueue: (default)Connected.Traction Exec Take times: 216msrootclient2 stategrid2# ./run.sh tranction-sync-benchmark 100Connecting to servers: - 31:21212Credentials: - NoneOptions: - Heavyweight: no - MaxTxnQueue: (default)Connected.包含10条以下出错信息：LIGHTNINGDB ERROR: USER ABORT SFYX is 0,can not finshed! at com.cloudian.countrynetwork.Procedures.dyyh.TranctionDemo.run(TranctionDemo.java:47)Traction Exec Take times: 2827msrootclient2 stategrid2# ./run.sh tranction-sync-benchmark 100Connecting to servers: - 31:21212Credentials: - NoneOptions: - Heavyweight: no - MaxTxnQueue: (default)Connected.包含1000条以下出错信息：LIGHTNINGDB ERROR: USER ABORT SFYX is 0,can not finshed! at com.cloudian.countrynetwork.Procedures.dyyh.TranctionDemo.run(TranctionDemo.java:47)com.cloudian.countrynetwork.Procedures.dyyh.TranctionDemo.run(TranctionDemo.java:47)Traction Exec Take times: 14619ms4.7 总结测试项目索引单表查询索引多表联合查询不带索引查询删除