Shared-Nothing Anayltics und In-Memory Technology

Technical White Paper Content 1.  Best of Breed Approach for High Performance Analytics .................................... 3  2.  Shared-Nothing Architecture .................................................................... 4  3.  In-Memory Technology ............................................................................ 6  4.  Column-based Data Storage ...................................................................... 6  5.  Scalability ........................................................................................... 7  6.  Tuning-free Database ............................................................................. 8  7.  EXAPowerlytics ..................................................................................... 9  8.  High Availability ................................................................................. 10  9.  Conclusion ........................................................................................ 11  -2- © EXASOL AG - EXASolution Technical White Paper 1. Best of Breed Approach for High Performance Analytics EXASolution is a relational in-memory database management system (DBMS) that was developed specifically for use in the business intelligence process. By focusing on analytics, the EXASolution architecture can meet the special requirements placed on data warehouses more effectively than universal brands. Alongside the gain in pure performance, maintenance costs are lower because the complicated optimization steps typically done by administrators are not necessary. The EXASolution system is deployed on a high performance cluster. Based on highly specialized distributed algorithms and over 20 years of experience gained through research, the DBMS delivers previously unseen performance and scalability. EXASolution distributes stored data uniformly across all nodes in the cluster (shared nothing). Persistent data is stored on local hard disks, resulting in very high cluster-wide throughput for read and write operations. The DBMS EXASolution is based on a parallel 64-bit cluster architecture, on which the internal processing is done. The data is distributed evenly across the cluster nodes and those parts are loaded into the main memory which are needed to compute an analysis. In contrast to most of the database vendors, the data is stored column-wise and highly compressed on the local server storage. By introducing sophisticated “least-recently-used” cache mechanisms and very efficient column-wise compression techniques, it is possible to process large amounts of data inside the main memory. All algorithms of EXASolution are optimized to take advantage of fast main memory access times. A further advantage is the possibility to simply enlarge the cluster by several servers if requirements increase, without the necessity of a complete hardware upgrade. As a result of redundant data storage and fail-over mechanisms, availability can even be ensured in the event of failure of individual cluster nodes. Thanks to standard interfaces (JDBC, ODBC, ADO.net, MDX), integrating EXASolution into an existing IT infrastructure is easy. The self-learning system automatically adapts to the users’ habits and, together with the support for well-known business intelligence applications (e.g. Business Objects, Cognos, MicroStrategy, SAS) and ETL tools (like Informatica, Datastage, Talend) this increases acceptance by specialist departments. -3- © EXASOL AG - EXASolution Technical White Paper 2. Shared-Nothing Architecture Massive Parallel Data Processing EXASolution was developed as a parallel system and is constructed according to the shared-nothing principle. Data is distributed across all nodes in a cluster. When responding to queries, all nodes co-operate and special parallel algorithms ensure that most data is processed locally in each individual node’s main memory. If a query is sent to the system, it is first accepted by the node the client is connected to: The query is then distributed to all nodes. Intelligent algorithms optimize the query, determine the best plan of action and generate needed indices on the fly. The EXASOL optimizer decides whether the data should be reorganized for the specific profile. The system then processes the partial results based the local datasets. This processing paradigm is also known as SPMD (single program multiple data). All cluster nodes operate on an equal basis, there is no Master Node. The global query result is delivered back to the user via connection nodes. Example of Parallel Join Execution SELECT customer.last_name, city.name FROM customer, city WHERE customer.city_id = city.id AND city.name LIKE 'New%'; -4- © EXASOL AG - EXASolution Technical White Paper Both tables are evenly distributed across the nodes: Step 1: Filtering At first the filter will be applied to the smaller table CITIES. Only the cities beginning with 'NEW' will take part on further operations: Step 2: Matching Then, for each city the system looks up customers with the matching city-id. If no special distribution is given, matching is performed across all nodes: Step 3: Result Set The generated result set remains locally on the corresponding nodes. If the query is part of a multipath SQL, this part of the execution is finished at this point. If the result set has to be sent to a user, this will be done in portions requested by the corresponding frontend. Only these portions will be transferred to the connection node: -5- © EXASOL AG - EXASolution Technical White Paper 3. In-Memory Technology Intelligent In-Memory Algorithms EXASolution achieves its high performance as a result of innovative main memory algorithms. Unlike the hard-disk-based algorithms of traditional solutions, EXASolution can specifically access any single value within nanoseconds. The algorithms that process the queries take advantage of these characteristics of the main memory and thus enable optimum performance. EXASolution further enhances performance by automatically adjusting the contents of main memory according to the respective usage profile. Upon completion of a write operation, data is committed to the local hard disks. Built-in redundant data distribution also guarantees high database availability. This method of data processing is fully transparent for the user. EXASolution license fee is independent from number of servers, cpu, users, data volume or physically available RAM. You pay only for the RAM, the database may use for data processing. Case Study: Database Size vs. RAM Size 3% 4% 6% 6% 26% 16% 32% Use Case  Use Case  Use Case  Use Case  Use Case  Use Case  Use Case  1 2 3 4 5 6 7 Typically, the required RAM Size is about 10-20 percent of uncompressed data volume. 4. Column-based Data Storage Vertical/Column-based Storage Due to EXASolution's specialization on data warehousing it benefits from the column-wise data storage reducing the number of I/O operations and the overall amount of processed data. Typically queries in a data warehouse access only few columns (e.g. when joining tables). -6- © EXASOL AG - EXASolution Technical White Paper In order to optimize access to the hard disk, columns are partitioned into blocks. This facilitates maximum throughput and prevents dispensable data from being imported. Gaps can emerge in the blocks as a result of various operations (e.g. deletions or updates); however, the system automatically conducts a defragmentation if certain limits are exceeded. Data Compression To optimize RAM utilization, table data is compressed element by element already in main memory; on basis of the data types and content of the columns, the system automatically selects a sufficiently effective compression algorithm. Compression is fully transparent to the user. Unicode EXASolution fully supports Unicode and can therefore store all common characters in the database. All identifiers such as schema, table or column names can contain Unicode- characters. You don't need any special settings at table creation (e.g. CREATE TABLE t (v VARCHAR(50)) either.