by Nailah Bissoon

Big SQL offers an array of performance features to help improve performance of queries in the Hadoop ecosystem. One of these features is the ability to create and automatically match materialized query tables (MQTs) to incoming queries. MQTs can simplify query processing and improve performance because expensive join or aggregation operations can be calculated and cached in MQTs. Once these MQTs are created, Big SQL will automatically rewrite queries to take advantage of the newly created MQTs when possible. This is beneficial especially when lots of queries are involved because it may take time to manually rewrite the queries.

DB2 MQTs have been available since Big SQL v3, however these DB2 MQTs could only be created on the head node. On the head node, there maybe limited storage capacity and creating too many DB2 MQTs could mean that the capacity on the local disks of the head node could get exceeded. Also since the data is stored on the head node, there can be additional network traffic when data flows from this node to the other nodes to facilitate distributed query processing. This additional network traffic can negatively impact performance as well.

In IBM Db2 Big SQL 5.0.2, the Big SQL team introduced the concept of Hadoop MQTs. These Hadoop MQTs have an advantage over DB2 MQTs because the MQTs are stored on HDFS. This means that the data from these Hadoop MQTs can be accessible from any node in the cluster. As compared to DB2 MQTs, there would be less network traffic when Hadoop MQTs are used. Also, since there is usually more storage capacity on HDFS compared to local disks, capacity becomes less of an issue when using Hadoop MQTs. MQT matching or automatic query rewrite is still possible with Hadoop MQTs. This means that queries do not need to be manually rewritten to take advantage of any Hadoop MQT. In Big SQL 5.0.2, Hadoop MQTs can not be created as partitioned tables and Hadoop MQTs need to be maintained by the user. Partitioned Hadoop MQT support will be available in a future release.

A guide to creation of these Hadoop MQTs will be presented in this blog. The process involves figuring out which Hadoop MQTs to create, creating, populating, gathering statistics on these tables and then informing the Big SQL compiler to start utilizing the newly created Hadoop MQTs. Once the compiler is given the okay that it can use them, it automatically rewrites any incoming queries to take advantage of the newly created Hadoop MQT when possible.

1. Identify Expensive Queries

Hadoop MQTs can help to improve the performance of resource intensive queries. Identification of expensive queries can be done by utilizing event monitors to determine the most time consuming or resource intensive queries in the workload or by comparing actual query execution times. For example, here is one long running query from the TPC-DS benchmark.


with ws as (select d_year AS ws_sold_year, ws_item_sk, ws_bill_customer_sk ws_customer_sk, sum(ws_quantity) ws_qty, sum(ws_wholesale_cost) ws_wc, sum(ws_sales_price) ws_sp from web_sales left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk join date_dim on ws_sold_date_sk = d_date_sk where wr_order_number is null group by d_year, ws_item_sk, ws_bill_customer_sk ), cs as (select d_year AS cs_sold_year, cs_item_sk, cs_bill_customer_sk cs_customer_sk, sum(cs_quantity) cs_qty, sum(cs_wholesale_cost) cs_wc, sum(cs_sales_price) cs_sp from catalog_sales left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk join date_dim on cs_sold_date_sk = d_date_sk where cr_order_number is null group by d_year, cs_item_sk, cs_bill_customer_sk ), ss as (select d_year AS ss_sold_year, ss_item_sk, ss_customer_sk, sum(ss_quantity) ss_qty, sum(ss_wholesale_cost) ss_wc, sum(ss_sales_price) ss_sp from store_sales left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk join date_dim on ss_sold_date_sk = d_date_sk where sr_ticket_number is null group by d_year, ss_item_sk, ss_customer_sk ) select ss_sold_year, ss_item_sk, ss_customer_sk, round(cast(ss_qty as double)/cast((coalesce(ws_qty, 0)+coalesce(cs_qty, 0)) as double), 2) ratio, ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price, coalesce(ws_qty, 0)+coalesce(cs_qty, 0) other_chan_qty, coalesce(ws_wc, 0)+coalesce(cs_wc, 0) other_chan_wholesale_cost, coalesce(ws_sp, 0)+coalesce(cs_sp, 0) other_chan_sales_price from ss left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk) left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and cs_customer_sk=ss_customer_sk) where (coalesce(ws_qty, 0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=1998 order by ss_sold_year, ss_item_sk, ss_customer_sk, ss_qty desc, ss_wc desc, ss_sp desc, other_chan_qty, other_chan_wholesale_cost, other_chan_sales_price, round(ss_qty/(coalesce(ws_qty+cs_qty, 1)), 2) fetch first 100 rows only;

2. Generate Query Access Plan

Once the long running queries have been identified, generate the query access plans to help in the identify the expensive parts of the query. The aim is to replace the expensive part/s of the query with Hadoop MQT/s. When there are lots of queries, it is recommended to try to create common Hadoop MQTs that can be utilized by multiple queries. That is instead of creating 1 unique Hadoop MQTs for each query, perhaps 1 MQT can benefit multiple queries. On the other hand, for more elaborate queries which may have greater prioritization compared to other queries, multiple Hadoop MQTs could also be created per query. But keep in mind that maintenance of the Hadoop MQT is up to the DB administrator.
The query execution plan can be generated by using the following commands:


db2 -tvf /home/bigsql/sqllib/misc/EXPLAIN.DDL
db2 “explain plan for 'query_text';
 db2exfmt -d ${dbname} -n % -s % -g TIC -o query.exfmt -w -1 -# 0 

3. Inspection of the Query Access Plan

The access plan is made up of a few different parts. Big SQL tries to do a smart rewrite of any query coming into the system. The Optimized Statement section displays the rewrite of the query. Start by looking at the Optimized Statement when looking for what MQT to create. For example, here is the optimized statement for the above query. We can identify possible MQTs by inspecting this text. I have highlighted some possibilities in red. Note that more elaborate MQTs can also be created in this example but for the purpose of illustration, we choose to compare 3 possibilities below.


Optimized Statement: ------------------- SELECT Q27.$C0 AS "SS_SOLD_YEAR", Q27.SS_ITEM_SK AS "SS_ITEM_SK", Q27.SS_CUSTOMER_SK AS "SS_CUSTOMER_SK", Q27.$C3 AS "RATIO", Q27.$C4 AS "STORE_QTY", Q27.$C5 AS "STORE_WHOLESALE_COST", Q27.$C6 AS "STORE_SALES_PRICE", Q27.$C7 AS "OTHER_CHAN_QTY", Q27.$C8 AS "OTHER_CHAN_WHOLESALE_COST", Q27.$C9 AS "OTHER_CHAN_SALES_PRICE", Q27.$C10 FROM (SELECT 1998, Q26.SS_ITEM_SK, Q26.SS_CUSTOMER_SK, ROUND((DOUBLE(Q26.$C2) / DOUBLE((COALESCE(Q26.$C10, 0) + COALESCE( Q26.$C7, 0)))), 2), Q26.$C2, Q26.$C1, Q26.$C0, (COALESCE(Q26.$C10, 0) + COALESCE( Q26.$C7, 0)), (COALESCE(Q26.$C9, +0.00000000000000E+000) + COALESCE( Q26.$C6, +0.00000000000000E+000)), (COALESCE(Q26.$C8, +0.00000000000000E+000) + COALESCE( Q26.$C5, +0.00000000000000E+000)), ROUND((Q26.$C2 / COALESCE((Q26.$C10 + Q26.$C7), 1)), 2) FROM (SELECT Q25.$C0, Q25.$C1, Q25.$C2, Q25.SS_CUSTOMER_SK, Q25.SS_ITEM_SK, Q8.$C4, Q8.$C3, Q8.$C2, Q25.$C5, Q25.$C6, Q25.$C7 FROM (SELECT Q7.CS_ITEM_SK, Q7.CS_BILL_CUSTOMER_SK, Q7.$C2, Q7.$C3, Q7.$C4 FROM (SELECT Q6.CS_ITEM_SK, Q6.CS_BILL_CUSTOMER_SK, SUM(Q6.CS_QUANTITY), SUM(Q6.CS_WHOLESALE_COST), SUM(Q6.CS_SALES_PRICE) FROM (SELECT 1998, Q5.CS_ITEM_SK, Q5.CS_BILL_CUSTOMER_SK, Q5.CS_QUANTITY, Q5.CS_WHOLESALE_COST, Q5.CS_SALES_PRICE FROM (SELECT Q3.CS_SALES_PRICE, Q3.CS_WHOLESALE_COST, Q3.CS_QUANTITY, Q3.CS_BILL_CUSTOMER_SK, Q3.CS_ITEM_SK FROM (SELECT Q1.CS_SALES_PRICE, Q1.CS_WHOLESALE_COST, Q1.CS_QUANTITY, Q1.CS_BILL_CUSTOMER_SK, Q1.CS_ITEM_SK, Q1.CS_ORDER_NUMBER FROM CATALOG_SALES AS Q1, DATE_DIM AS Q2 WHERE (Q2.D_YEAR = 1998) AND (Q1.CS_SOLD_DATE_SK = Q2.D_DATE_SK) ) AS Q3 LEFT OUTER JOIN CATALOG_RETURNS AS Q4 ON (Q3.CS_ITEM_SK = Q4.CR_ITEM_SK) AND (Q4.CR_ORDER_NUMBER = Q3.CS_ORDER_NUMBER) ) AS Q5 ) AS Q6 GROUP BY Q6.CS_BILL_CUSTOMER_SK, Q6.CS_ITEM_SK, Q6.$C0 ) AS Q7 ) AS Q8 RIGHT OUTER JOIN (SELECT Q24.$C4, Q24.$C3, Q24.$C2, Q24.SS_CUSTOMER_SK, Q24.SS_ITEM_SK, Q16.$C4, Q16.$C3, Q16.$C2 FROM (SELECT Q15.WS_ITEM_SK, Q15.WS_BILL_CUSTOMER_SK, Q15.$C2, Q15.$C3, Q15.$C4 FROM (SELECT Q14.WS_ITEM_SK, Q14.WS_BILL_CUSTOMER_SK, SUM(Q14.WS_QUANTITY), SUM(Q14.WS_WHOLESALE_COST), SUM(Q14.WS_SALES_PRICE) FROM (SELECT 1998, Q13.WS_ITEM_SK, Q13.WS_BILL_CUSTOMER_SK, Q13.WS_QUANTITY, Q13.WS_WHOLESALE_COST, Q13.WS_SALES_PRICE FROM (SELECT Q11.WS_SALES_PRICE, Q11.WS_WHOLESALE_COST, Q11.WS_QUANTITY, Q11.WS_BILL_CUSTOMER_SK, Q11.WS_ITEM_SK FROM (SELECT Q9.WS_SALES_PRICE, Q9.WS_WHOLESALE_COST, Q9.WS_QUANTITY, Q9.WS_BILL_CUSTOMER_SK, Q9.WS_ITEM_SK, Q9.WS_ORDER_NUMBER FROM WEB_SALES AS Q9, DATE_DIM AS Q10 WHERE (Q10.D_YEAR = 1998) AND (Q9.WS_SOLD_DATE_SK = Q10.D_DATE_SK) ) AS Q11 LEFT OUTER JOIN WEB_RETURNS AS Q12 ON (Q11.WS_ITEM_SK = Q12.WR_ITEM_SK) AND (Q12.WR_ORDER_NUMBER = Q11.WS_ORDER_NUMBER) ) AS Q13 ) AS Q14 GROUP BY Q14.WS_BILL_CUSTOMER_SK, Q14.WS_ITEM_SK, Q14.$C0 ) AS Q15 ) AS Q16 RIGHT OUTER JOIN (SELECT Q23.SS_ITEM_SK, Q23.SS_CUSTOMER_SK, Q23.$C2, Q23.$C3, Q23.$C4 FROM (SELECT Q22.SS_ITEM_SK, Q22.SS_CUSTOMER_SK, SUM(Q22.SS_QUANTITY), SUM(Q22.SS_WHOLESALE_COST), SUM(Q22.SS_SALES_PRICE) FROM (SELECT Q21.SS_ITEM_SK, Q21.SS_CUSTOMER_SK, Q21.SS_QUANTITY, Q21.SS_WHOLESALE_COST, Q21.SS_SALES_PRICE FROM (SELECT Q19.SS_SALES_PRICE, Q19.SS_WHOLESALE_COST, Q19.SS_QUANTITY, Q19.SS_CUSTOMER_SK, Q19.SS_ITEM_SK FROM (SELECT Q17.SS_SALES_PRICE, Q17.SS_WHOLESALE_COST, Q17.SS_QUANTITY, Q17.SS_CUSTOMER_SK, Q17.SS_ITEM_SK, Q17.SS_TICKET_NUMBER FROM STORE_SALES AS Q17, DATE_DIM AS Q18 WHERE (Q18.D_YEAR = 1998) AND (Q17.SS_SOLD_DATE_SK = Q18.D_DATE_SK) ) AS Q19 LEFT OUTER JOIN STORE_RETURNS AS Q20 ON (Q19.SS_ITEM_SK = Q20.SR_ITEM_SK) AND (Q20.SR_TICKET_NUMBER = Q19.SS_TICKET_NUMBER) ) AS Q21 ) AS Q22 GROUP BY Q22.SS_CUSTOMER_SK, Q22.SS_ITEM_SK ) AS Q23 ) AS Q24 ON (Q16.WS_BILL_CUSTOMER_SK = Q24.SS_CUSTOMER_SK) AND (Q16.WS_ITEM_SK = Q24.SS_ITEM_SK) ) AS Q25 ON (Q8.CS_BILL_CUSTOMER_SK = Q25.SS_CUSTOMER_SK) AND (Q8.CS_ITEM_SK = Q25.SS_ITEM_SK) ) AS Q26 WHERE ((COALESCE(Q26.$C10, 0) > 0) OR (COALESCE(Q26.$C7, 0) > 0)) ) AS Q27 ORDER BY Q27.SS_ITEM_SK, Q27.SS_CUSTOMER_SK, Q27.$C4 DESC, Q27.$C5 DESC, Q27.$C6 DESC, Q27.$C7, Q27.$C8, Q27.$C9, Q27.$C10

The next question we need to answer is which of these possible MQTs may have the largest impact. The Access Plan section shows a graphical representation of how the query is broken down. The access plan is usually read bottom up. Studying the Access Plan section can give some clues as to the impact that these MQTs may have. I have circled the 3 possible MQTs in red in the Access Plan below:


AccessPlanNoMQT

4. Evaluate the impact of creating the MQT

Of the 3 proposed MQTs the question now is which one could have the most impact. Note that all these MQTs are joining with the DATE_DIM table. Note that the TBSCAN of the STORE_SALES (771707) seems to be the costliest compared to the TBSCAN of WEB_SALES (247299) and CATALOG_SALES (267666). Also note that the TBSCAN of this STORE_SALES return the most rows (4.24359X10^8) compared to TBSCAN of WEB_SALES (1.02341×10^8) and CATALOG_SALES (1.10768×10^8). Therefore, we can conclude that of the 3 proposed MQTs, the join between STORE_SALES and DATE_DIM could have the most impact. You can confirm that the STORE_SALES table is the largest by issuing the following statement:


db2 "select tabname, card from syscat.tables where tabschema = 'HADOOPDS1000G_ORC_PTN' order by card desc limit 6";
TABNAME CARD
STORE_SALES 5516668225
CATALOG_SALES 1439980416
INVENTORY 1438363629
WEB_SALES 1330427446
STORE_RETURNS 528764947

5. Create the Hadoop MQT

The Hadoop MQT can be created using the CREATE HADOOP TABLE … AS statement with some additional clauses highlighted in red. The following is an example of the proposed Hadoop MQT for the join of the STORE_SALES and DATE_DIM tables:


CREATE HADOOP TABLE hadoopmqt AS (
SELECT
                          Q17.SS_SALES_PRICE,
                          Q17.SS_WHOLESALE_COST,
                          Q17.SS_QUANTITY,
                          Q17.SS_CUSTOMER_SK,
                          Q17.SS_ITEM_SK,
                          Q17.SS_TICKET_NUMBER
                        FROM
                          HADOOPDS1000G_ORC_PTN.STORE_SALES AS Q17,
                          HADOOPDS1000G_ORC_PTN.DATE_DIM AS Q18
                        WHERE
                          Q18.D_YEAR = 1998 AND
                          Q17.SS_SOLD_DATE_SK = Q18.D_DATE_SK )
DATA INITIALLY DEFERRED REFRESH DEFERRED MAINTAINED BY USER DISABLE QUERY OPTIMIZATION;

Note the DATA INITIALLY DEFERRED clause in the syntax above, this tells Big SQL that the population of the Hadoop MQT will be done later. Also note the REFRESH DEFERRED MAINTAINED BY USER clause, this means that it is up to the DB administrator to refresh the MQT at their convenience. The keywords DISABLE QUERY OPTIMIZATION informs the Db2 Big SQL compiler not to start using the Hadoop MQT yet. This is also the default behaviour because the MQT is not populated and there can be wrong results generated by utilizing the Hadoop MQT at this time.

6. Populate the Hadoop MQT

INSERT..SELECT or LOAD Hadoop can be used to populate the Hadoop MQT just like a regular Hadoop table. The select clause needs to be the same as the text used to create the Hadoop MQT. For example, here is the INSERT…SELECT statement to populate this Hadoop MQT:


INSERT INTO hadoopmqt
SELECT
                          Q17.SS_SALES_PRICE,
                          Q17.SS_WHOLESALE_COST,
                          Q17.SS_QUANTITY,
                          Q17.SS_CUSTOMER_SK,
                          Q17.SS_ITEM_SK,
                          Q17.SS_TICKET_NUMBER
                        FROM
                          HADOOPDS1000G_ORC_PTN.STORE_SALES AS Q17,
                          HADOOPDS1000G_ORC_PTN.DATE_DIM AS Q18
                        WHERE
                          Q18.D_YEAR = 1998 AND
                          Q17.SS_SOLD_DATE_SK = Q18.D_DATE_SK ;

7. Gather statistics of the Hadoop MQT

By default, statistics are automatically collected for Big SQL tables that are populated when LOAD HADOOP is used (since Big SQL 4.2). After the LOAD HADOOP command is completed, an automatic ANALYZE command is triggered. If tables are populated with INSERT however, the auto-analyze feature is triggered to gather statistics on this table. Depending on what is or is not on the queue for auto-analyze, the gathering of the statistics may not be instantaneous. If you want to start using the MQTs immediately then you can manually trigger the ANALYZE statement using the following command:


ANALYZE TABLE hadoopmqt COMPUTE STATISTICS FOR ALL COLUMNS;

8. Enable MQT optimization

Since the Hadoop MQT is now populated, the Big SQL compiler can be informed that it can start matching this Hadoop MQT to incoming queries. This automatic matching of MQTs to queries is quite appealing because it means that time does not need to be wasted figuring out which MQTs match to which queries and altering the queries ourselves. When there are 100s of queries and MQTs then manual matching and query alteration can become a daunting task. The following command tells the Big SQL Optimizer to start matching MQTs to incoming queries:


     ALTER MATERIALIZED QUERY hadoopmqt SET ENABLE QUERY OPTIMIZATION;
       SET CURRENT REFRESH AGE ANY;
       SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION USER;

9. Check the Query Access Plan

Within the session you can use the SET CURRENT REFRESH AGE ANY and the SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION USER statements to trigger the selection of the Hadoop MQT. Gather the query access plan by issuing the following statements to determine whether the newly created Hadoop MQT will be used when running the query:


connect to bigsql;
SET CURRENT REFRESH AGE ANY
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION USER
explain plan for 'original_query_text'
db2exfmt -d  -n % -s % -g TIC -o query.exfmt -w -1 -# 0 

The following is a snippet of the new access plan note that HADOOPMQT is being used here circled in red:


AccessPlanMQT
These additional messages in the access plan tell us that the MQT is being used:
Diagnostic Identifier:  3
Diagnostic Details:     EXP0148W  The following MQT or statistical view was
                        considered in query matching:
                        "HADOOPDS1000G_ORC_PTN"."HADOOPMQT".
Diagnostic Identifier:  4
Diagnostic Details:     EXP0149W  The following MQT was used (from those
                        considered) in query matching:
                        "HADOOPDS1000G_ORC_PTN"."HADOOPMQT".

10. Run the Query

The SET CURRENT REFRESH AGE ANY and SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION USER statements should be executed after a connection to the database have been made and before running the queries. For example


db2 connect to bigsql;
db2 SET CURRENT REFRESH AGE ANY;
db2 SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION USER;
db2 “original_query_text”;
db2 “subsequent_query_text”;

11. Performance Comparison

To study the performance of Hadoop MQTs we chose to use the SSB benchmark. SSB is a more suitable BI (Business Intelligence) benchmark compared to TPC-DS and TPC-H. It is derived from TPC-H but uses a more realistic star schema and the queries are more representative of BI. There is 1 fact and 4 dimension tables with 13 star-join queries. The schema is shown in the figure below:

SSBSchema

For this performance comparison we used a 5 nodes system (1 head node and 4 data nodes) each with 128G and 40 CPU v-cores at a Scale Factor of 1000 ie. 1TB db size(6 Billion lineitem rows, 30 million customer rows). The build time for the Hadoop MQTs was 3240 seconds or little less than 1 hour.
The table below shows the results with and without the Hadoop MQT. Note the significant performance improvement with the use of the Hadoop MQT:

Results1

We also did a comparison of DB2 MQTs to Hadoop MQTs and noticed that Hadoop MQTs were also faster than the DB2 MQTs. The graph below shows the results of that comparison where the lower the number, the better the performance:

DB2vsHadoopMQT

12. Refresh of the Hadoop MQT

Since the table is not automatically refreshed by Big SQL, after any bulk inserts to the base tables have been done, it is necessary to re-populate the Hadoop MQT. Disable the MQT query optimization when the underlining tables used in the MQT have changed to avoid potential wrong results. Since MQT definitions can be complex with aggregation functions involved, the recommended approach is to TRUNCATE the table and issue the INSERT…SELECT or LOAD statement to re-populate the Hadoop MQT. Also since these MQTs are Hadoop tables, issuing too many ingest statements to populate these MQT tables could result in many small files which can have adverse affects on performance. Therefore, it is recommended to choose a maintenance window and refresh these Hadoop MQTs in that window. The following commands can be used to refresh the Hadoop MQT used in the example:


db2 "ALTER MATERIALIZED QUERY hadoopmqt SET DISABLE QUERY OPTIMIZATION";
db2 "TRUNCATE TABLE hadoopmqt";

Conclusion

In this blog we studied Hadoop MQTs and provided best practices to configuring Hadoop MQTs in IBM Db2 Big SQL 5.0.2. We gave some guide to determining which Hadoop MQTs could have the most impact on the system, we studied how the Hadoop MQTs can be created and how to check the access plan to determine whether they are matched correctly. We also showed a performance comparison comparing the use of MQTs to no MQTs where the use of Hadoop MQTs even outperformed the DB2 MQT. Lastly, we talked about how to refresh the Hadoop MQT. One of the biggest advantages with the use of Hadoop MQTs is that the original query does not need to be changed. The Big SQL compiler can automatically match incoming queries to existing Hadoop MQTs.

Happy MQTing!
Thanks to the following folks for developing and testing this feature: Qi Cheng, Tony Lai, Kenneth Chen, Andre Suzumura, Nailah Bissoon

Join The Discussion

Your email address will not be published. Required fields are marked *