A Fast Database for Large
Observational or Simulation
Datasets
Adrian M. Partl
Leibniz-Institut für Astrophysik Potsdam (AIP)

Monday, October 22, 12

Big Data in Astronomy andAstrophysics
a tsunami of data...
Size of individual data set• Raw data usuallywith clear data
formats, huge insize

Number of data sets

Monday, October 22, 12

Big Data in Astronomy andAstrophysics
a tsunami of data...
Size of individual data set• Raw data usuallywith clear data
formats, huge insize• Derived / processeddata highly irregularin formats, large insize

Number of data sets

Monday, October 22, 12

Big Data in Astronomy andAstrophysics

Monday, October 22, 12

a tsunami of data...
Size of individual data set• Raw data usuallywith clear data
formats, huge insize• Derived / processeddata highly irregularin formats, large insize• Analysed resultswith complex
formats, small insize

Number of data setswww.multidark.org• Total row count:
2.34 1011• MS SQL Server• Apache Tomcat• similar setup toMillenium DB• Most queries 100 sec• significant amount ofqueries > 1000 sec

Monday, October 22, 12

Problems while building theMultiDark DB• Data ingest time:
Need to convert binary to ASCII CSV format(highly inefficient)• Data transformation:
Computing values after ingest slow - best during ingest• Data indexing:
Index on particle data (~1010 particles) around one week
• Data retrieval times slow on full table scans:cannot build index for every query• Spatial queries in 3D hard, impossible in 6D
nearest neighbour search also inefficient

Monday, October 22, 12

Why RDBMS?
• SQL - it took long time for the community to adopt SQL(we think this is the main problem with NoSQL)• proven, widely available, large user base• good for structured data
• Problems:– Built for different purposes (business, web, ...)
result sets usually small - mostly in memory solutions– parallelisation of data / sharding– can be expensive

Monday, October 22, 12

Our vision:• Open source DB solution for scientific purposes:
A one size fits all solution built by the community for thecommunity• Developments at AIP:
– DB independent ingestion library and datatransformation tool (DBIngestor and AsciiIngest)– MySQL sharding solution for scientific queries (PaQu)– MySQL plugins for data analysis, spatial queries andindexing, job queueing...
– MySQL storage engine plugins for simulation raw data
– Common web framework for data access (Daiquiri)
Monday, October 22, 12

Our Developments with MySQL
Spider engine:
Great MySQL sharding engine
developed by Kentoku ShibaFederated engine:
Used for temporary results injoins and aggregates:
MySQL

http://spiderformysql.com

Query queue
Spider engine

SpiderFederatedMySQL

Monday, October 22, 12MySQL

MySQL

MySQL

Distributed with MySQL toaccess tables on other servers.
(would need rewrite to performbetter though)

MySQL

MySQL

Our Developments with MySQLUserAdmin

MySQL
Daiquiri

PaQu

Query queue

DBIngestor / AsciiIngest

Spider engine

SpiderFederated

MySQL

Monday, October 22, 12MySQL

MySQL

MySQL

MySQL

MySQL

PaQu: Parallel Query Reformulation
Implicit Joins:

Aggregates:

SELECTa.*, b.*, c.*FROMa, b, cWHERE
b=2 AND
b.id=c.b_id AND
a.id=b.a_id;

SELECTFROM
GROUP BY

a.bar, AVG(a.foo)aa.bar;

SELECTa.bar,
SUM(a.sum)/SUM(a.cnt)

SELECTa.*, tmp.*
FROMa,(SELECT b.*, c.*
FROMc,(SELECT b.*
FROMb
WHERE b=2) as b
WHEREb.id=c.b_id)
WHERE
a.id=tmp.b.a_ids

Monday, October 22, 12

FROM(SELECTa.bar as bar,
SUM(a.foo) as sum,
COUNT(a.foo) as cnt
FROMa
GROUP BY a.bar) as a
GROUP BY a.bar;

Preliminary Performance Results

number of nodes

indexed aggregate

speedupunindexed range

speedupspeedupunindexed aggregate

number of nodes

number of nodes• Strong correlation with hardware setup:– Cache sizes, size of data files (smaller is better /partitioning?), network and I/O performance
Monday, October 22, 12

Conclusions• Scientific data increases rapidly in size
problems for off-the-shelf database systems• Slow acceptance by scientific community of anything
new poses problems for NoSQL solutions andfavours SQL• Open source solution for scientific use of open
source databases needed• Promising results for using MySQL + Spider + PaQu
for scientific data mining and fast full table scans

Monday, October 22, 12

Interested? Help us in thedevelopment!
DBIngestor/AsciiIngest:
http://github.com/adrpar
Thank you!

Monday, October 22, 12