New OLAP Wikistat benchmark: Introduction and call for feedbacks

I’ve seen my posts on Ontime Air traffic and Star Schema Benchmark got a lot of interest
(links:

• • https://www.percona.com/blog/2010/01/07/star-schema-bechmark-infobright-infinidb-and-luciddb/

• • https://www.percona.com/blog/2009/10/02/analyzing-air-traffic-performance-with-infobright-and-monetdb/

• • https://www.percona.com/blog/2009/10/26/air-traffic-queries-in-luciddb/

• • https://www.percona.com/blog/2009/11/02/air-traffic-queries-in-infinidb-early-alpha/

).
However benchmarks by itself did not cover all cases I would want, so I was thinking about better scenario. The biggest problem is to get real big enough dataset, and I thank to Bradley C. Kuszmaul, he pointed me on Wikipedia statistics on access to Wikipedia pages, and thank to Domas, who made stats accessible. Link to the archives: http://dammit.lt/wikistats/archive/ or the original Domas’s announcement .

Although the table does not have very much different information, I think it is good enough to represent cases you can face in Web application ( log processing, page visits, clickstream, etc).

I made some efforts to normalize data to have model in classic star schema and prepared queries that could be run on proposed dataset (John Sichi, lead of LucidDB helped me to draft some queries).
You can see details on our Percona Wikistat benchmark Wiki.

I have next goals with proposed benchmark:

• • Compare engines in OLAP queries for planning, predicting growth, analyzing access patterns to wiki pages, draw trends.

• • Compare engines in statistical queries for end users, which can be executed in real-time. I.e. How many times that or another page was accessed yesterday vs today.

• • Understand specific features and characteristic of each engine.

• • Compare throughput on simple queries (queries and scenario to be drafted yet)

• • Check ability to load data and serve queries at the same time ( availability during data load ) (queries and scenario to be drafted yet)

So in proposed schema I have four tables:

(fact table), and

(dimensions tables).

Dimensions tables are supposed to be static and not changed, and we can change datasize
by varying amount of months loaded into fact table (so this is scale factor).

EER diagram

( made with MySQL Workbench )

In current dataset, which you can download from Amazon snapshot (name: “percona-wikistat”, ID:snap-a5f9bacc) we have:

• • Table
    
    pages

    1

    pages

    
    : 724.550.811 rows. data size: 40476M

• • Table
    
    datesinfo

    1

    datesinfo

    
    : 9624 rows, one entry represents 1 hour

• • Table
    
    projects:

    1

    projects:

    
    2025 rows

• • Table
    
    pagestats

    1

    pagestats

    
    Data for 2009-06: # 3.453.013.109 rows / size 68352M
    Data for 2009-07: # 3.442.375.618 rows / size 68152M

So with two months of stats we have about 172GB of data with about 7 billion rows in fact table.

Example of query ( again, full list on Benchmark Wiki)

I am going to load data and run queries against available engines:

• • MySQL MyISAM / InnoDB (to have reference results)

• • InfoBright

• • InfiniDB

• • MonetDB

• • LucidDB

• • Greenplum

and I will report my results ( so stay with MySQLPerformanceBlog ;))

I’d like also to test also Paraccel, Vertica and KickFire systems, but I do not have access to.

I welcome your feedback on the benchmark, and what else you would like to see here.