{"id":3366,"date":"2018-03-02T11:08:12","date_gmt":"2018-03-02T11:08:12","guid":{"rendered":"http:\/\/bicortex.com\/?p=3366"},"modified":"2018-06-15T12:40:40","modified_gmt":"2018-06-15T12:40:40","slug":"vertica-mpp-database-overview-and-tpc-ds-benchmark-performance-analysis-part-3","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/vertica-mpp-database-overview-and-tpc-ds-benchmark-performance-analysis-part-3\/","title":{"rendered":"Vertica MPP Database Overview and TPC-DS Benchmark Performance Analysis (Part 3)"},"content":{"rendered":"

In Post 1<\/a> I outlined the key architectural principles behind Vertica\u2019s design and what makes it one of the top MPP\/analytical databases available today. In Post 2<\/a> I went over the installation process across one and multiple nodes as well as some of Vertica\u2019s ‘knobs and buttons’ which give the administrator a comprehensive snapshot of the system performance. In this instalment I will focus on loading the data into Vertica\u2019s cluster and running some analytical queries across TPC-DS datasets to compare execution times for single and multi-node environments. The comparison is not attempting to contrast Vertica\u2019s performance with any of its competing MPP database vendors e.g. Greenplum or AWS Redshift (that may come in the future posts). Rather, the purpose of this post is to ascertain how queries execution are impacted based on the number of nodes in the cluster and whether the performance increases or decreases yielded were linear. Also, given that Vertica is a somewhat esoteric DBMS, I wanted to highlight the fact that with a fairly minimum set up and configuration, this MPP database can provide a lot of bang for your buck when dealing with analytical workloads. Yes, most developers or analysts would love nothing more than to jump on the cloud bandwagon and use ‘ops-free’ Google’s BigQuery or even AWS Redshift but the reality is that most SMBs are not ready to pivot their operational model and store their customers’ data in the public cloud. Databases like Vertica provide a reasonable alternative to a long established players in this market e.g. Oracle DB or IBM DB2 and allow the so-called big data demands to be addressed with relative ease i.e. multi-model deployment, full-featured SQL API, MPP architecture, in-database machine learning etc.<\/p>\n

Cluster Setup and Data Load<\/h3>\n

There are quite a few data sets and methodologies that can be used to gauge data storage and processing system\u2019s performance e.g.\u00a0TLC Trip Record Data<\/strong><\/a>\u00a0released by the New York City Taxi & Limousine Commission has gained a lot of traction amongst big data specialists, however, \u00a0TPC-approved benchmarks have long been considered as the most objective, vendor-agnostic way to perform data-specific hardware and software comparisons, capturing the complexity of modern business processing to a much greater extent than its predecessors. I have briefly outlined data generation mechanism used in the TPC-DS suite of tools in my previous blog HERE<\/a> so I will skip the details and use three previously generated datasets for this demo \u2013 one with the scaling factor of 100 (~100GB), one with the scaling factor of 200 (~200GB) and another with the scaling factor of 300 (~300GB). All three datasets have not turned out to be perfectly uniform in terms of expected rows count (presumably due to selecting the unsupported scaling factors), with large deviations recorded across some files e.g. ‘inventory’ table containing fewer records in 200GB dataset than in the 100GB one. However, the three datasets were quite linear in terms of raw data volume increases which was also reflected in runtimes across number of subsequent queries.<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

As mentioned in Post 2, the hardware used for this demo is not exactly what you would have in mind when provisioning an enterprise-class database cluster. With that in mind, the results of all the analytical queries I run are only indicative of the performance level in the context of the same data and configuration used. Chances are that with a proper, server-level hardware this comparison would be like going from the Wright brothers first airplane to Starship Enterprise but at least I endeavoured to make this as empirical as possible. To be completely transparent, here is the view of how these tiny Lenovo desktops are configured.<\/p>\n

\"\"<\/a><\/p>\n

Also, for good measure I run a quick test on the storage performance using the ‘measure_locatioon_performance’ vsql function run on one of the machines (all three nodes are identical in terms of the individual components setup). These units are equipped with an SSD each (as opposed to a slower mechanical drive), however, their SATA2 interface limits the transfer rates of what these Crucial MX300 are technically capable of to just over 360 MB\/sec as per the image below.<\/p>\n

\"\"<\/a><\/p>\n

Finally, the fact I\u2019m pushing between 100 and 300 gigabytes of data through this cluster with only 16GB of memory available per node means that most of the data needs to be read from disk. Most analytical databases are designed to cache data in memory, providing minimal latency and fast response time. As this hardware does not do justice to the data volumes queried in this demo, with proper reference architecture for this type of system I am certain that the performance would increase by an order of magnitude.<\/p>\n

Vertica documentation provides a detail outline of different techniques used to load, transform and monitor the acquisitions. As reiterating the details of all the options and approaches is outside the scope of this post, I will only touch on some of the core concepts – for a comprehensive guide on data loading best practices and different options available please refer to their documentation or\u00a0THIS<\/a> visual guide. By far, the most effective way to load the data into Vertica is using COPY command. The COPY statement loads data from a file stored on the host or client (or in a data stream) into a database table. You can pass the COPY statement many different parameters to define various options such as: the format of the incoming data, metadata about the data load, which parser COPY should use, how to transform data as it is loaded or how to handle errors. Vertica\u2019s hybrid storage model provides a great deal of flexibility for loading and managing data.<\/p>\n

\"\"<\/a><\/p>\n

For this demo I copied and staged the three data sets \u2013 100GB, 200GB and 300GB – on the m92p1 host (node no1 in my makeshift 3-node cluster) and used the following shell script to (1) create necessary tables on the previously created ‘vtest’ database and ‘tpc_ds’ schema and (2) load the data into the newly created tables.<\/p>\n

\r\n#!\/bin\/bash\r\nVPASS="YourPassword"\r\nDBNAME="vtest"\r\nSCHEMANAME="tpc_ds"\r\nDATAFILEPATH="\/home\/dbadmin\/vertica_stuff\/TPC_DS_Data\/100GB\/*.dat"\r\nSQLFILEPATH="\/home\/dbadmin\/vertica_stuff\/TPC_DS_SQL\/create_pgsql_tables.sql"\r\n\r\n\/opt\/vertica\/bin\/vsql -f "$SQLFILEPATH" -U dbadmin -w $VPASS -d $DBNAME\r\nfor file in $DATAFILEPATH\r\ndo\r\n    filename=$(basename "$file")\r\n    tblname=$(basename "$file" | cut -f 1 -d '.')\r\n    echo "Loading file "$filename" into a Vertica host..."\r\n    #single node only:\r\n    echo "COPY $SCHEMANAME.$tblname FROM LOCAL '\/home\/dbadmin\/vertica_stuff\/TPC_DS_Data\/100GB\/$filename' \\\r\n        DELIMITER '|' DIRECT;" | \\\r\n         \/opt\/vertica\/bin\/vsql \\\r\n         -U dbadmin \\\r\n         -w $VPASS \\\r\n         -d $DBNAME\r\ndone\r\n<\/pre>\n
The sql file with all the DDL statements for tables\u2019 creation can be downloaded from my OneDrive folder HERE<\/a>. The script loaded the data using DIRECT option thus straight into ROS (Read Optimised Store) to avoid engaging the Tuple Mover – database optimiser component that moves data from memory (WOS) to disk (ROS). The load times (1000 Mbps Ethernet), along with some other supporting information, for each of the three datasets are listed below.<\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
Testing Methodology and Results<\/h3>\n
A full TPC-DS benchmark is comprised of 99 queries and governed by very specific set of rules. For brevity, this demo only includes 20 queries i.e. (query number 5, 9, 10, 13, 17, 24, 31, 33, 34, 35, 44, 46, 47, 54, 57, 64, 74, 75, 97 and 99) as a subset sample for two reasons.<\/p>\n