测试infinidb 2.0.2

〇〇 · 发表于 2010-12-19 18:47

再测试整数
mysql> CREATE TABLE REN41
-> (  R0 tinyint,
->    ID bigint,
->    R01 tinyint,
->    R02 tinyint,
->    R03 tinyint,
->    R041 smallint,
->    R042 tinyint,
->    R05 tinyint,
->    R061 tinyint,
->    R062 tinyint,
->    R063 tinyint,
->    R07 tinyint,
->    R081 tinyint,
->    R082 tinyint,
->    R09 tinyint,
->    R101 tinyint,
->    R102 tinyint,
->    R103 tinyint,
->    R104 tinyint,
->    R11 tinyint,
->    R12 tinyint,
->    R131 tinyint,
->    R132 tinyint,
->    R14 tinyint,
->    R151 tinyint,
->    R152 tinyint,
->    R16 tinyint,
->    R17 tinyint,
->    R18 tinyint,
->    R19 smallint,
->    R20 smallint,
->    R211 tinyint,
->    R212 smallint,
->    R22 tinyint,
->    R23 tinyint,
->    R241 smallint,
->    R242 tinyint,
->    R251 tinyint,
->    R252 tinyint,
->    R253 tinyint,
->    R254 tinyint,
->    R261 tinyint,
->    R262 tinyint,
->    R263 tinyint,
->    R264 tinyint,
->    R265 tinyint,
->    RA0 tinyint,
->    RA1 smallint,
->    RA2 tinyint,
->    RA20 tinyint,
->    RA21 tinyint,
->    RA22 tinyint
-> )ENGINE=INFINIDB;
Query OK, 0 rows affected (1.69 sec)

mysql>
mysql> select r03, r05,count(*)cnt
-> from tiny.ren41 where r05<3
-> group by r03,r05;
+------+------+----------+
| r03  | r05  | cnt    |
+------+------+----------+
。。。
+------+------+----------+
4 rows in set (3.21 sec)

mysql> select r03, r05,count(*)cnt
-> from test.ren41 where length(r05)=1 and r05<'3'
-> group by r03,r05;
+------+------+-----+
| r03  | r05  | cnt |
+------+------+-----+
。。
+------+------+-----+
4 rows in set (2.59 sec)

mysql> select r03, r05,count(*)cnt
-> from test.ren41 where (length(r05)=1 and r05<'3') or (length(r05)=2 and r05<'03')
-> group by r03,r05;
+------+------+----------+
| r03  | r05  | cnt    |
+------+------+----------+
。。。
+------+------+----------+
8 rows in set (3.29 sec)

mysql>
插入时间也缩短
F:\Calpont\bin>colxml tiny -j503 -d , -x csv -t ren41
Running colxml with the following parameters:
2010-12-19 18:35:07 (19148) INFO :
      Schema: tiny
      Tables: ren41
      Load Files:
      -b    0
      -c    1048576
      -d    ,
      -e    10
      -f    CSV
      -j    503
      -n
      -p    F:/Calpont/bulk/job/
      -r    5
      -s
      -u
      -w    10485760
      -x    csv

Creating job description file: F:/Calpont/bulk/job/Job_503.xml
File completed for tables:
      tiny.ren41

Normal exit.

F:\Calpont\bin>cpimport -j 503

Bulkload root directory : F:/Calpont/bulk
job description file : F:/Calpont/bulk/job/Job_503.xml
2010-12-19 18:35:18 (39556) INFO : successfully load job file F:/Calpont/bulk/job/Job_503.xml
2010-12-19 18:35:18 (39556) INFO : PreProcessing check starts
2010-12-19 18:35:18 (39556) INFO : PreProcessing check completed
2010-12-19 18:35:18 (39556) INFO : preProcess completed, total run time : 0 seconds
2010-12-19 18:35:18 (39556) INFO : No of Read Threads Spawned = 1
2010-12-19 18:35:18 (39556) INFO : No of Parse Threads Spawned = 3
2010-12-19 18:40:55 (39556) INFO : For table tiny.ren41: 91236854 rows processed and 91236854 rows inserted.
2010-12-19 18:40:55 (39556) INFO : Bulk load completed, total run time : 337 seconds

增加8G数据后,数据目录增加2G,10.0 GB (10,793,476,096 字节),压缩比大约25%

〇〇 · 发表于 2010-12-19 18:48

F:\Calpont\bulk\data\import\ren41.csv
8.13 GB (8,730,938,880 字节)

〇〇 · 发表于 2011-2-11 11:55

[root@redflag11012601 ssb2]# /usr/local/Calpont/bin/colxml -j1 -thu -d, -x csv rk
Running colxml with the following parameters:
2011-02-11 11:20:12 (25500) INFO :
      Schema: rk
      Tables: hu
      Load Files:
      -b    0
      -c    1048576
      -d    ,
      -e    10
      -f    CSV
      -j    1
      -n
      -p    /usr/local/Calpont/data/bulk/job/
      -r    5
      -s
      -u
      -w    10485760
      -x    csv

Creating job description file: /usr/local/Calpont/data/bulk/job/Job_1.xml
File completed for tables:
      rk.hu

Normal exit.
[root@redflag11012601 ssb2]# /usr/local/Calpont/bin/cpimport -j1 -f/user1/app -r8 -w8
number of read threads : 8
number of parse threads : 8

Bulkload root directory : /usr/local/Calpont/data/bulk
job description file : /usr/local/Calpont/data/bulk/job/Job_1.xml
2011-02-11 11:23:25 (25624) INFO : successfully load job file /usr/local/Calpont/data/bulk/job/Job_1.xml
2011-02-11 11:23:25 (25624) INFO : PreProcessing check starts
2011-02-11 11:23:25 (25624) INFO : PreProcessing check completed
2011-02-11 11:23:25 (25624) INFO : preProcess completed, total run time : 0 seconds
2011-02-11 11:23:25 (25624) INFO : No of Read Threads Spawned = 8
2011-02-11 11:23:25 (25624) INFO : No of Parse Threads Spawned = 8
2011-02-11 11:41:06 (25624) INFO : For table rk.hu: 351233699 rows processed and 351233699 rows inserted.
2011-02-11 11:41:06 (25624) INFO : Bulk load completed, total run time : 1061 seconds

〇〇 · 发表于 2011-2-11 12:11

mysql> select count(*) from rk.hu;
+-----------+
| count(*)  |
+-----------+
| 351233699 |
+-----------+
1 row in set (5.05 sec)

mysql> select count(*) from rk.hu where ha20='';
+-----------+
| count(*)  |
+-----------+
| 349231838 |
+-----------+
1 row in set (6.38 sec)

mysql>  select count(*) from rk.hu where ha20<>'';
+----------+
| count(*) |
+----------+
|  2001861 |
+----------+
1 row in set (2.04 sec)

mysql> select count(*), substr(id,1,2) from rk.hu group by substr(id,1,2);
+----------+----------------+
| count(*) | substr(id,1,2) |
+----------+----------------+
+----------+----------------+
31 rows in set (1 min 43.78 sec)

mysql> select count(*), substr(id,1,2) from rk.hu group by substr(id,1,2) order by 2;
+----------+----------------+
| count(*) | substr(id,1,2) |
+----------+----------------+
+----------+----------------+
31 rows in set (1 min 34.68 sec)

mysql> select count(distinct h0),count(distinct h02) from rk.hu;
+--------------------+---------------------+
| count(distinct h0) | count(distinct h02) |
+--------------------+---------------------+
|                2 |                2 |
+--------------------+---------------------+
1 row in set (9.12 sec)

mysql> select h0,h02,count(h0),count(h02) from rk.hu group by h0,h02;
+------+------+-----------+------------+
| h0 | h02  | count(h0) | count(h02) |
+------+------+-----------+------------+
| 1 | 1 |  33664871 | 33664871 |
| 1 | 2 | 851846 |    851846 |
| 0 | 2 | 9890655 | 9890655 |
| 0 | 1 | 306826326 |  306826326 |
| 1 | NULL |       1 |       0 |
+------+------+-----------+------------+
5 rows in set (9.49 sec)

mysq

edwardking888 · 发表于 2011-2-12 11:18

博主，infinidb 2.0.2
是不是必须通过FILE to DATABASE方式导数据啊?