Thursday, May 15, 2014

Trying out HyPer

At TUM we have built a very fast main-memory database system named HyPer. It offers fairly complete SQL92 support plus some SQL99 features, and is much faster than "traditional" database systems.

The easiest way to play with it is the online demo. It provides you with an easy to use interface for entering queries, running them, and inspecting the execution plan. All queries are evaluated against a SF1 TPC-H database which contains roughly 1GB of data.


HyPer web interface


The web interface is easy to use and requires no setup, but it runs on a fairly weak server machine and queries only 1GB of data. For larger experiments you might be interested in a local installation of HyPer. For that, download the hyper demo, unpack it with tar xvfJ, and try out one of the included demo scripts (or your own queries, of course).

For TPC-C experiments you can use the demo-tpcc script:


 neumann@tester:~/hyperdemo$ ./demo-tpcc   
 Executing scripts...  
 2044ms  
 Loading...  
 5598ms (722MB)  
 OLTP...  
  wallclock    total  primary    tps   time    mem  
 --------------------------------------------------------------  
         0s   100000   52946  119904   834ms   19MB  
         1s   200000   55108  124843   801ms   31MB  
         2s   300000   52573  120192   832ms   142MB  
         3s   400000   54219  122549   816ms   179MB  
         4s   500000   54896  124533   803ms   199MB  
         4s   600000   54576  123609   809ms   217MB  
         5s   700000   54812  124069   806ms   230MB  
         6s   800000   54391  123609   809ms   236MB  
         7s   900000   52794  119617   836ms   329MB  
         8s   1000000  53557  122100   819ms   347MB  
 --------------------------------------------------------------  
         8s   1000000  53914  122339  8174ms   347MB  
TPC-C run

In the example run above we get 122,339 transactions per second (53,913 neworder transactions per second), and grow the database by 347MB.

For TPC-H experiments you can use the demo-tpch script. Note that the demo does not include the TPC-H data itself, it has to be generated by using the official dbgen tool (in ../tpch). Sample run:

 neumann@tester:~/hyperdemo$ ./demo-tpch   
 Executing scripts...
 6792ms
 Writing database state to tpch.dump
 10287ms
 TPC-H is now available in tpch.dump. Access with ./bin/sql tpch.dump

 Running all 22 TPC-H queries in sequence
 query 1: 23ms
 query 2: 3ms
 query 3: 29ms
 query 4: 13ms
 query 5: 7ms
 query 6: 7ms
 query 7: 18ms
 query 8: 10ms
 query 9: 100ms
 query 10: 27ms
 query 11: 7ms
 query 12: 13ms
 query 13: 57ms
 query 14: 8ms
 query 15: 9ms
 query 16: 116ms
 query 17: 14ms
 query 18: 71ms
 query 19: 35ms
 query 20: 9ms
 query 21: 31ms
 query 22: 8ms
TPC-H run

As indicated in the output the database is now available as a dump, so you can use the command line interface for experiments:

 neumann@tester:~/hyperdemo$ ./sql tpch.dump
 > select count(*) from lineitem;
 1
 6001215
 1 row (0.00176411 s)
 > \q
command-line interface

This should get you started with trying out HyPer. Feel free to contact us if you encounter any issues, or if you get performance numbers that are significantly different from what is shown here.

4 comments:

  1. ./sql
    ./sql: error while loading shared libraries: /u01/hyper/hyperdemo/bin/../lib/libstdc++.so.6: ELF file OS ABI invalid

    ReplyDelete
    Replies
    1. On which system are you trying that? The binaries should work on any reasonably recent Linux x86-64 system. But of course not on 32bit systems, ARM, or anything else.

      Delete
  2. Dear HyPer Team

    Thank you very much for the demo, it is amazing and really helps to get a feeling for that stuff :-)

    One question: I tried to populate my database (1 table, 500 columns) with 10,000 records (~30MB of raw data) on my 16-GB-RAM Desktop machine. However, HyPer ran out of memory (I got a std::bad_alloc exception). Can you explain what went wrong? Population with 1000 records worked, but it also consumed ~14GB of main memory...

    Looking forward to the extended demo with client sessions :-)
    Keep up the good work!

    Best regards,
    Lucas Braun

    ReplyDelete
    Replies
    1. Hm, difficult to guess without seeing your schema. Did you perhaps define a lot of large string fields? The HyPer demo allocates the maximum string length per field, which can lead to such effects.

      We have changed that a while ago, but the demo was not updated yet. If you are interested in trying it out just send me a mail, I can send you an unstable snapshot.

      Delete