(DEHEMS) Benchmark results

== Setup ==

=== Machine specification ===

* product name: IBM x3750;
* storage: 2 x 500Gb, SCSI, RAID 0;
* RAM: 8Gb;
* processor:
** type: 2 x Intel Xeon 5160 x 2 core => 4 cores;
** frequency: cache 2 x 3Ghz;
** L2 cache: 4Mb / core;

=== BerkeleyDB ===

* configuration:
** database:
*** type: BTree;
*** page size: 64kb;
*** minimum keys per page: 1792;
** cache: 4Gb;
* encoding:
** key: '''client identifier''' (32bit) + '''sensor identifier''' (32bit);
** data: '''timestamp''' (32bit) + '''value''' (32bit);

=== Hypertable ===

* schema:
** key column: '''/''' + '''client identifier''' (as 8 hex chars) + '''/''' + '''sensor identifier''' (as 8 hex chars) + '''/''' + '''timestamp''' (as 8 hex chars) + '''/''';
** value column: '''value''' (as 8 hex chars);

==== Hypertable (local) ====

* servers: only 1 server as master and range server;
* storage broker: local;

==== Hypertable (HDFS) ====

* servers: only 1 server as master and range server;
* storage broker: HDFS with 8 storage nodes;

=== Postgres ===

* configuration:
** out of the box with the following differences (made postgres.conf):
*** max_connections: 20 (from 100);
*** shared_buffers: 1024Mb (from 24Mb);
*** work_mem: 64Mb (from 1Mb);
*** maintenance_work_mem: 256Mb (from 16Mb);
*** fsync: off (from on);
*** wal_buffers: 64Mb (from 64kb);
*** checkpoint_segments: 256 (from 3);
*** random_page_cost: 2.0 (from 4.0);
*** effective_cache_size: 4096Mb (from 128Mb);
** connections: through local UNIX sockets;
** tables: the fill-factor was set to 100%;
** indices: the fill-factor was set to 10%;
* tuning references:
** advice was asked on mailing list: [http://archives.postgresql.org/pgsql-general/2008-11/threads.php#01051] (about 50 emails were exchanged);
** [http://www.powerpostgresql.com/PerfList  PostgreSQL 8.0 Performance Checklist];
** [http://www.revsys.com/writings/postgresql-performance.html Performance Tuning PostgreSQL];
** [http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Tuning PostgreSQL for performance];
* schema:
** a table with four columns for client, sensor, timestamp and value as integer;
** an index made on client, sensor, and timestamp (in this order);
* observations:
** inserts were done in batches (5k) with the COPY command;
** after each 500k inserts an ANALYZE command was issued;

=== SQLite ===

* configuration:
** fullsync: 0 (off);
** journalmode: off;
** synchronuous: off;
** cachesize: 65536 (pages);
** auto_vacuum: none;
* schema: the same as in the case of Postgres;
* observations:
** inserts were done in batches (5k) -- each batch being a transaction -- with prepared statement;

=== MonetDB ===

* configuration: none (MonetDB is auto-tunable);
* schema: the same as in the case of Postgres;
* observations:
** inserts were done in a similar manner as in the case of SQLite;

== Methods ==

=== Insertion benchmark ===

# create an empty store (either BerkeleyDB environment / database or Hypertable table);
# set '''timestamp''' to 0
# iterate 100 million times:
## select a random '''client identifier''', uniformly distributed in the range 0 to 10 thousand;
## select a random '''sensor identifier''', uniformly distributed in the range 0 to 10;
## select a random '''value''' as the reading;
## insert the reading;
## increment '''timestamp''';
## at each 10 thousand inserts, create a data point comprised of:
##* total inserted readings until now;
##* the time elapsed for inserting the last 10 thousand inserts;
##* the speed for the last 10 thousand inserts;

=== Scan benchmark ===

# iterate '''client identifier''' and '''sensor identifier''' from 0 to 10 thousand, respectively from 0 to 10:
## select the readings sorted by '''timestamp''';
## check that the current reading '''timestamp''' is indeed greater (or equal) to the previous reading '''timestamp''';
## at each 10 thousand readings, create a data point just like in the case of insertion;
# check that we have read exactly the same number of records that we have inserted;

== Benchmarks ==

=== 100m-c ===

* means 100 million inserts clustered (described previously);
* we have compared only BerkeleyDB, Hypertable and MonetDB;
* Postgres and SQLite weren't able to cope with the test in timely manner (for example Postgres run for half a day and didn't finish the inserts);

{| class="wikitable" border="1"
|-
! System !! min inserts/s !! avg inserts/s !! max inserts/s
|-
| bdb-2-ins-o/s || 1.86k (1859.31) || 42.26k (42258.39) || 476.69k (476690.46)
|-
| hypertable-local-ins-o/s || 973.58 (973.58) || 235.59k (235586.60) || 368.73k (368733.26)
|-
| hypertable-hdfs-ins-o/s || 717.90 (717.90) || 232.61k (232613.11) || 373.64k (373637.40)
|-
| monetdb-ins-o/s || 7.31k (7313.49) || 10.14k (10136.34) || 17.25k (17252.12)
|-
! System !! min scans/s !! avg scans/s !! max scans/s
|-
| bdb-2-scn-o/s || 11.35k (11350.54) || 2.51m (2509827.80) || 2.61m (2608235.81)
|-
| hypertable-local-scn-o/s || 60.14k (60143.13) || 124.67k (124668.64) || 252.19k (252188.85)
|-
| hypertable-hdfs-scn-o/s || 2.68k (2680.57) || 30.36k (30364.88) || 171.84k (171842.07)
|-
| monetdb-scn-o/s || 87.78 (87.78) || 106.99k (106994.71) || 133.30k (133295.96)
|-
|}

* BerkeleyDB:
[[Image:Benchmarks -- Sensor Data Storage -- 100m-c -- bdb-2.png|240px]]
* Hypertable:
[[Image:Benchmarks -- Sensor Data Storage -- 100m-c -- hypertable-local.png|240px]]
[[Image:Benchmarks -- Sensor Data Storage -- 100m-c -- hypertable-hdfs.png|240px]]
* Hypertable comparisons:
[[Image:Benchmarks -- Sensor Data Storage -- 100m-c -- comparison-hypertable.png|240px]]
[[Image:Benchmarks -- Sensor Data Storage -- 100m-c -- comparison-hypertable-log.png|240px]]
* MonetDB:
[[Image:Benchmarks -- Sensor Data Storage -- 100m-c -- monetdb.png|240px]]
* comparisons:
[[Image:Benchmarks -- Sensor Data Storage -- 100m-c -- comparison-all.png|240px]]
[[Image:Benchmarks -- Sensor Data Storage -- 100m-c -- comparison-all-log.png|240px]]

=== 4m-c ===

* means 4 million inserts clustered;
* we have tested only Postgres, SQLite3 and MonetDB;

{| class="wikitable" border="1"
|-
! System !! min inserts/s !! avg inserts/s !! max inserts/s
|-
| postgres-ins-o/s || 115.94 (115.94) || 53.70k (53695.05) || 200.26k (200255.15)
|-
| sqlite3-ins-o/s || 301.11 (301.11) || 34.77k (34771.54) || 182.49k (182489.58)
|-
| monetdb-ins-o/s || 6.90k (6900.09) || 10.19k (10188.88) || 17.28k (17275.51)
|-
! System !! min scans/s !! avg scans/s !! max scans/s
|-
| postgres-scn-o/s || 161.31 (161.31) || 60.89k (60893.09) || 70.98k (70980.42)
|-
| sqlite3-scn-o/s || 3.17k (3165.91) || 341.26k (341262.62) || 344.52k (344518.33)
|-
| monetdb-scn-o/s || 43.26k (43256.30) || 107.48k (107475.73) || 110.70k (110698.03)
|-
|}

* Postgres:
[[Image:Benchmarks -- Sensor Data Storage -- 4m-c -- postgres.png|240px]]
* SQLite3:
[[Image:Benchmarks -- Sensor Data Storage -- 4m-c -- sqlite3.png|240px]]
* MonetDB:
[[Image:Benchmarks -- Sensor Data Storage -- 4m-c -- monetdb.png|240px]]
* comparisons:
[[Image:Benchmarks -- Sensor Data Storage -- 4m-c -- comparisons-all.png|240px]]
[[Image:Benchmarks -- Sensor Data Storage -- 4m-c -- comparisons-all-log.png|240px]]

== Observations ==

* only one test was executed for each storage system (but each test used 100 million records);
* due to lack of proper documentation for Hypertable we were not able to deploy it with distributed range servers; we have deployed it only with distributed storage;
* the nature (and flow) of operations (inserts and scans) impacts heavily the storage architecture and performance:
** '''the following notes impact in the same way both BerkeleyDB and Hypertable''';
** if the most frequent queries on the records are all the items in an interval, the key should be composed as '''timestamp'' + '''client identifier''' + '''sensor identifier''';
** if the most frequent queries on the records are all the items for a given client, and then for a given sensor (as we have considered to be the case in our benchmarks), the key should be composed as '''client identifier''' + '''sensor identifier''' + '''timestamp''';
** if we need both types of queries with the same frequency (priority), then we are obliged to store the same data with both of the schemes;
* the nature (and flow) of the data impacts heavily the storage performance:
** if the data stream to be inserted is already sorted (or at least nearly sorted) -- this is the case of inserting the data with the keys '''timestamp''' + '''client identifier''' + '''sensor identifier''' -- increases the performance for BerkeleyDB by a factor of 5 to 10;
** if the data to be inserted is clustered (thus not sorted, but somehow interleaved) -- this is the case of inserting the data with the keys '''client identifier''' + '''sensor identifier''' + '''timestamp''', as we have considered to be the case in our benchmarks -- decreases the performance for BerkeleyDB somehow logarithmically;
** it seems that -- at least for insertions -- the performance of Hypertable is not impacted in any way;

== Conclusion ==

=== Scans ===

* BerkeleyDB is far superior (by 10 times) to Hypertable (at least for the data amount tested);
* average scan speeds;
** BerkeleyDB average scan speed: 2378k, that is 2 million records per second;
** Hypertable (with local storage) average scan speed: 124k, nearly 20 times slower than BerkeleyDB;
** Hypertable (with HDFS storage) average scan speed: 30k, nearly 100 times slower than BerkeleyDB;
** MonetDB average scan speed: 106k, comparable in speed with Hypertable;
* observations:
** it seems that the scan performance decreases dramatically in the case of Hypertable and distributed storage;
** in the case of BerkeleyDB the spead remains steady;
** in the case of MonetDB the scan speed remains also steady;
** in the case of Hypertable (local) the scan speed evolves in (big, repeating) steps, maybe due no internal cache flushes;
** in the case of Hypertable (HDFS) the scan speed is good at the beginning, and drops drastically after 10 million record scans;
** in the case of MonetDB at the beginning the scan speed is worst (67 records / second), but it improves as the database learns from the queries and builds proper indices;

=== Inserts ===

* Hypertable is superior to BerkeleyDB (by 2 times);
* average insert speeds:
** Hypertable (with local storage) average insert speed: 235k;
** Hypertable (with HDFS storage) average insert speed: 232k (just like in the case of local storage);
** BerkeleyDB average insert speed: 91k, almost twice as slow as Hypertable (with any kind of storage);
* observations:
** in the case of Hypertable (with any storage) the insert speed is constant;
** in the case of MonetDB the insert speed is also steady;
** in the case of BerkeleyDB the insert speed decreases logarithmically, maybe due to the nature of the backing data structure (BTree);

=== Relational stores ===

* this section applies to Postgres and SQLite, as MonetDB behaved Ok;
* observations:
** it seems that the initial insert speed is good for the first couple million records;
** as the data accumulates and new inserts are done, the indices start to be rewritten, and consume the entire disk bandwidth; (the initial good speed is due the fact that the indices fit into the RAM memory;)
** if the inserts are done without any indices defined, the insert speed is incredible (600k in the case of Postgres), but the scan speed is under 100;
** maybe, it is possible, that this behavior is specific to any database which uses trees (like BerkeleyDB?);