Solutions Assignment Week 1 | In-Memory Data Management 2015

Dear learning community,

as many of you asked for some more details on the correct answers for the closed assignments or access to the past questions if you missed them, we decided to put the solutions online in the respective week.

If there are any questions left, please do not hesitate to ask in the forum.

Best regards,
Ralf for the IMDB Teaching Team

Week 1
Question Name: Hardware Trends

Text: What were the major hardware trends that affected the design of SanssouciDB as an in-memory column-oriented database?

Correct answer:

Increasing main memory capacities and increasing parallelism of modern CPUs

Incorrect answers:

Increasing disk speeds and disk lifetimes
Enormously increasing clock speed of CPUs
Increasing disk capacities and decreasing power consumption of hardware in general

General explanation: Increasing main memory capacities and increasing parallelism of modern CPUs were the major trends that affected the design of SanssouciDB. A significant increase in disk speed and disk lifetimes did not happen. Even though, it would not affect SanssouciDB to a great extent since disk is not the primary storage. Clock speeds did not further increase, because the ratio of increased speed to additional power consumption is not favorable. Disk capacities and an overall increase in power consumption also did not influence SanssouciDB.

Question Name: Aggregations in the 60s

Text: How was data typically aggregated back in the 60s and 70s?

Correct answer:

Most recent data (e.g. daily transactions) is read from punchcards, aggregated, and combined with older aggregates from a tape.

Incorrect answers:

All data was read from tapes, aggregations could only be done there
All data was read from punchcards to ensure that the results are up to date
All punchcards are read, afterwards it is checked whether all data is also available on tape. Then, only the data which could be found on both devices is used to prevent faulty results.

General explanation: Tapes were mainly used for keeping the history in one place and storing aggregated values. New transactions were recorded on punchcards. Therefore, to get up-to-date values, the pre-aggregated values of the past (on tape) had to be combined with the values of the new entries from the punchcards. Using only the punchcards would be theoretically possible, but really time consuming and therefore not done in the standard case. Only using the values from tape would result in incomplete, outdated results. Just considering values that are on punchcards as well as on tape logically makes no sense, since the storage format was different. Direct matches therefore could not be found.

Question Name: Definition Materialized Aggregates

Text: What are materialized aggregates?

Correct answer:

Materialized aggregates are persisted results of aggregation functions like sum or average applied to certain entries in the database.

Incorrect answers:

Materialized aggregates are aggregates which are build on the material relation of the enterprise system. This is especially important for the manufacturing industry.
Materialized aggregates is another term for values, that are stored but never retrieved.
All source values, that contribute to the result of an aggregation query, are called materialized aggregates.

General explanation: Materialized aggregates are persisted results of aggregation functions like sum or average applied to certain entries in the database. The other answers are just made up, there are no special terms for them. Materialized aggregates are often held in so called materialized views. The view defines the aggregation function and the subset of data to operate on. The view is build up on creation and then stored, therefore reflecting the status valid on the creation time.

Question Name: Enterprise Data Characteristics

Text: Which characteristic does NOT apply to enterprise data?

Correct answer:

High entropy in many columns

Incorrect answers:

NULL and default values are dominant in many columns
Large number of columns (attributes)
Very low entropy in many columns

General explanation: Various analyses of different enterprise systems from actual customers showed that most tables are "sparse and wide". Many columns are not even used. Furthermore, the columns are often dominated by default or NULL values. So there is a large number of columns, with very low entropy. The correct answer is therefore, that high entropy in columns is a characteristic that enterprise systems usually do NOT have.

Question Name: Motivation for Aggregates

Text: What were the reasons for introducing materialized aggregates?

Correct answer:

For performance reasons

Incorrect answers:

For security reasons
For legal reasons
To increase information

General explanation: The analytical workload on former, row-based systems hindered the normal transactional business, if all aggregations were computed on the fly. Given the changed storage format and the capabilities of modern hardware, the additional layer of aggregates to ensure performance of analytical queries can be abolished.

Question Name: Latencies

Text: What is the correct order of the following components according to their access speeds from fastest to slowest?

Correct answer:

CPU registers, CPU caches, main memory, SSD

Incorrect answers:

SSD, RAM, CPU caches, CPU registers
All access speeds are the same, just the bandwidth varies
CPU registers, main memory, CPU caches, HDD

General explanation: The nearer the storage component is to the CPU, the faster, smaller and more expensive it usually is. So the correct order is CPU registers, which are directly wired to the CPU, then CPU caches, which may include several layers, then main memory (we use it as the primary storage device for SanssouciDB) and last but not least SSDs for logging and recovery.

Question Name: NUMA

Text: What is NUMA (non uniform memory access)?

Correct answer:

In multi-core setups, it means that each processor can access the local memory of all other processors

Incorrect answers:

It means that data types can have variable size now and do not need to be uniform any longer
It is a standard that describes the physical structure of memory chips to fit in server-blades

General explanation: In NUMA systems, each processor has its own part of main memory that can be accessed very fast. Data, which is not in that local storage of a processor, has to be requested from non-local storage, i.e. another processor's local memory. In that implementation, all processors share the same adress space which simplifies memory management.

Question Name: NUMA and Cache Coherency

Text: Which statement concerning NUMA and cache coherency is correct?

Correct answer:

Most currently sold NUMA realizations come with special-purpose hardware to maintain cache coherency

Incorrect answers:

Most NUMA realizations currently in the market use software layers to maintain cache coherency
Every program gains a huge performance boost from NUMA; no adaption of the software is needed to fully exploit the potential
Cache coherency is no longer a concern when using NUMA architectures, since NUMA does not use caches at all

General explanation: As stated in the reading material, non ccNUMA hardware is practically non existent, because it is harder to program. Therefore, the terms NUMA and ccNUMA are usually used identically.

Question Name: Determining Factors of Compression Rate

Text: What leads to a higher compression rate with dictionary encoding?

Correct answer:

Lower number of distinct values in a column

Incorrect answers:

Higher number of distinct values in a column
Ascending sorting order of a column
Descending sorting order of a column

General explanation: Dictionary encoding benefits from a low entropy, what means that the ratio of distinct values to total values in a column is low. This also means that we have many duplicate values in the column. If we represent these potentially large values with the minimum number of bits required, we decrease memory consumption and thereby achieve a good compression rate. In consequence, this also decreases the bandwith consumption and thereby increases the overall performance. Dictionary encoding is not affected by the order of the entries in a column, these only affect additional compression mechanisms on top of the dictionary encoded values.

Question Name: Bit Representation of Distinct Values

Text: How many bits are minimally required to represent 1,140 distinct values per entry in the attribute vector when using dictionary encoding?

Correct answer:

Incorrect answers:

General explanation: log_2(1,140) is about 10.154... , if we round up to the next integer number, we get 11, which is the number of bits required.

Question Name: Data in Cold Store

Text: Which data is moved to the cold store?

Correct answer:

Data that is no longer necessary to conduct the day-to-day business, so completely closed cases

Incorrect answers:

Data that stems from transactions in the Arctic region
Data that is mainly needed for queries that can be answered very quickly (under 0.5 seconds)
Data that is mainly needed for queries that need extremely long to be answered (longer than 5 seconds)

General explanation: The cold store hold data from closed business processes. The other, wrong answers are just made up.