]]>
]]>

InfiniDB

Issues:

CREATE TABLE `pages` (   `id` int(10)   ,   `page` varchar(1024)  ) engine=InfiniDB;

CREATE TABLE `pagestat` (
   `date_id` smallint,
   `project_id` smallint,
   `page_id` int,
   `page_count` int ) engine=InfiniDB;

CREATE TABLE `datesinfo` (
  `id` smallint,
  `datestring` char(11),
  `caldate` date,
  `calday` int,
  `calmonth` tinyint,
  `calyear` smallint,
  `dayofweek` tinyint,
  `dayhour` tinyint
) engine=InfiniDB;

CREATE TABLE `projects` ( `id` smallint,  `project` varchar(50) ) Engine=InfiniDB;

Load of tables:

pages.txt:
./cpimport -j 299                                                                                             

Bulkload root directory : /usr/local/Calpont/data/bulk
job description file : Job_299.xml
2010-01-08 16:37:47 (26310) INFO : successfully load job file /usr/local/Calpont/data/bulk/job/Job_299.xml
2010-01-08 16:37:47 (26310) INFO : PreProcessing check starts
2010-01-08 16:37:47 (26310) INFO : PreProcessing check completed
2010-01-08 16:37:47 (26310) INFO : preProcess completed, total run time : 0 seconds
2010-01-08 16:37:47 (26310) INFO : No of Read Threads Spawned = 1
2010-01-08 16:37:47 (26310) INFO : No of Parse Threads Spawned = 3
2010-01-08 17:08:57 (26310) INFO : For table wikistat.pages: 724533199 rows processed and 724533199 rows inserted.
2010-01-08 17:08:57 (26310) INFO : Bulk load completed, total run time : 1870 seconds

200906.out
Bulkload root directory : /usr/local/Calpont/data/bulk
job description file : Job_299.xml
2010-01-08 18:47:21 (26867) INFO : successfully load job file /usr/local/Calpont/data/bulk/job/Job_299.xml
2010-01-08 18:47:21 (26867) INFO : PreProcessing check starts
2010-01-08 18:47:21 (26867) INFO : PreProcessing check completed
2010-01-08 18:47:21 (26867) INFO : preProcess completed, total run time : 0 seconds
2010-01-08 18:47:21 (26867) INFO : No of Read Threads Spawned = 1
2010-01-08 18:47:21 (26867) INFO : No of Parse Threads Spawned = 3
2010-01-08 19:32:34 (26867) INFO : For table wikistat.pagestat: 3453013109 rows processed and 3453013109 rows inserted.
2010-01-08 19:32:34 (26867) INFO : Bulk load completed, total run time : 2713 seconds

200907.out
2010-01-08 19:39:12 (27018) INFO : successfully load job file /usr/local/Calpont/data/bulk/job/Job_299.xml
2010-01-08 19:39:12 (27018) INFO : PreProcessing check starts
2010-01-08 19:39:12 (27018) INFO : PreProcessing check completed
2010-01-08 19:39:12 (27018) INFO : preProcess completed, total run time : 0 seconds
2010-01-08 19:39:12 (27018) INFO : No of Read Threads Spawned = 1
2010-01-08 19:39:12 (27018) INFO : No of Parse Threads Spawned = 3
2010-01-08 20:47:07 (27018) INFO : For table wikistat.pagestat: 3442375618 rows processed and 3442375618 rows inserted.
2010-01-08 20:47:07 (27018) INFO : Bulk load completed, total run time : 4075 seconds

mysql> LOAD DATA INFILE '/data/vol2/wikistat/in/datesinfo.txt' INTO TABLE datesinfo FIELDS TERMINATED BY '|';
Query OK, 9624 rows affected (1.78 sec)
Records: 9624  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 0 rows affected (4.69 sec)

mysql> 
mysql> LOAD DATA INFILE '/data/vol2/wikistat/in/projects.txt' INTO TABLE projects FIELDS TERMINATED BY '|';                         Query OK, 2025 rows affected (0.68 sec)
Records: 2025  Deleted: 0  Skipped: 0  Warnings: 0

Queries

(on 8-Jan-2010, loaded only 6 and 7 months)

mysql> SELECT count(distinct page_id) FROM pagestat JOIN datesinfo di ON ( di.id=date_id ) WHERE di.calmonth=6 and di.calyear=2009; ERROR 122 (HY000): IDB-2003: Aggregation/Distinct memory limit is exceeded.

SELECT caldate, sum(page_count) FROM pagestat JOIN datesinfo di ON ( di.id=date_id ) WHERE di.calmonth=7 and di.calyear=2009 GROUP BY caldate; +————+—————–+

caldate sum(page_count)

+————+—————–+

2009-07-01 375154489
2009-07-02 355513518
2009-07-03 335760602
2009-07-04 306147164
2009-07-05 327740307
2009-07-06 389883791
2009-07-07 386175896
2009-07-08 382882243
2009-07-09 370407438
2009-07-10 355997838
2009-07-11 304173053
2009-07-12 329987996
2009-07-13 373983867
2009-07-14 375018689
2009-07-15 367551324
2009-07-16 361616841
2009-07-17 358519492
2009-07-18 321683549
2009-07-19 342184532
2009-07-20 395184396
2009-07-21 389761140
2009-07-22 383119228
2009-07-23 382347583
2009-07-24 361728589
2009-07-25 313462647
2009-07-26 333288535
2009-07-27 391458614
2009-07-28 389526317
2009-07-29 378409629
2009-07-30 369870751
2009-07-31 340975971

+————+—————–+ 31 rows in set (28 min 31.28 sec)

Q_TOP20_PROJECTS_2009_7

+-----------+------------+
| project   | sm         |
+-----------+------------+
| en        | 5801646978 |
| ja        | 1107649695 |
| de        |  846000568 |
| es        |  587914725 |
| fr        |  415447893 |
| it        |  312223726 |
| ru        |  257933941 |
| pl        |  244611880 |
| pt        |  211796551 |
| commons.m |  200707823 |
| nl        |  119577556 |
| zh        |   72398482 |
| sv        |   59818628 |
| tr        |   58640780 |
| fi        |   49663489 |
| en.d      |   43871816 |
| cs        |   38004806 |
| he        |   31454617 |
| th        |   30436900 |
| ar        |   28067066 |
+-----------+------------+
20 rows in set (41 min 44.13 sec)
  • Q_NOPROJECTS_2009_7_14

Empty set (2 min 53.76 sec)

  • Q_DAYWEEKSTAT_de_2009_6
+-----------+-----------+
| dayofweek | sm        |
+-----------+-----------+
|         2 | 165450710 |
|         3 | 163106534 |
|         4 | 124997530 |
|         5 | 125253914 |
|         6 | 113266541 |
|         7 |  96271121 |
|         1 | 120597382 |
+-----------+-----------+
7 rows in set (8 min 12.02 sec)
  • Q_HOURPROJECTSTAT_es_2009_7_21
+---------+---------+
| dayhour | sm      |
+---------+---------+
|       0 | 1240624 | 
|       1 | 1142891 | 
|       2 | 1024656 | 
|       3 |  883060 | 
|       5 |  604610 | 
|       4 |  752229 | 
|       6 |  443406 | 
|       7 |  362154 | 
|       8 |  331105 | 
|       9 |  333356 | 
|      10 |  352360 | 
|      11 |  393850 | 
|      12 |  449171 | 
|      13 |  479068 | 
|      14 |  643588 | 
|      15 |  893173 | 
|      16 | 1092079 | 
|      17 | 1179218 | 
|      18 | 1210127 | 
|      19 | 1286693 | 
|      20 | 1393787 | 
|      21 | 1455615 | 
|      22 | 1498337 | 
|      23 | 1477064 | 
+---------+---------+
24 rows in set (7 min 3.22 sec)
 
benchmark/wikistat/infinidb.txt · Last modified: 2011/07/17 21:05 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Contact Us 24 Hours A Day
SupportContact us 24×7
Emergency? Contact us for help now!
Sales North America(888) 316-9775 or
(208) 473-2904
Sales
Europe
+44-208-133-0309 (UK)
0-800-051-8984 (UK)
0-800-181-0665 (GER)
Training(855) 55TRAIN or
(925) 271-5054

 

Share This
]]> ]]>