1 of 1

Analyzing Queries

Determining Active Queries

SHOW PROCESSLIST

The MariaDB SHOW PROCESSLIST statement is used to see a list of active queries on that UM:

getActiveSQLStatements

getActiveSQLStatements is a mcsadmin command that shows which SQL statements are currently being executed on the database:

Analysis of Individual Queries

Query Statistics

The calGetStats function provides statistics about resources used on the node, and network by the last run query. Example:

The output contains information on:

MaxMemPct - Peak memory utilization on the , likely in support of a large (User Module) based hash join operation.
NumTempFiles - Report on any temporary files created in support of query operations larger than available memory, typically for unusual join operations where the smaller table join cardinality exceeds some configurable threshold.
TempFileSpace - Report on space used by temporary files created in support of query operations larger than available memory, typically for unusual join operations where the smaller table join cardinality exceeds some configurable threshold.

The output is useful to determine how much physical I/O was required, how much data was cached, and how many partition blocks were eliminated through use of extent map elimination. The system maintains min / max values for each extent and uses these to help implement where clause filters to completely bypass extents where the value is outside of the min/max range. When a column is ordered (or semi-ordered) during load such as a time column this offer very large performance gains as the system can avoid scanning many extents for the column.

Query Plan / Trace

While the MariaDB Server's utility can be used to look at the query plan, it is somewhat less helpful for ColumnStore tables as ColumnStore does not use indexes or make use of MariaDB I/O functionality. The execution plan for a query on a ColumnStore table is made up of multiple steps. Each step in the query plan performs a set of operations that are issued from the to the set of in support of a given step in a query.

Full Column Scan - an operation that scans each entry in a column using all available threads on the Performance Modules. Speed of operation is generally related to the size of the data type and the total number of rows in the column. The closest analogy for a traditional system is an index scan operation.
Partitioned Column Scan - an operation that uses the Extent Map to identify that certain portions of the column do not contain any matching values for a given set of filters. The closest analogy for a traditional row-based DBMS is a partitioned index scan, or partitioned table scan operation.
Column lookup by row offset - once the set of matching filters have been applied and the minimal set of rows have been identified; additional blocks are requested using a calculation that determines exactly which block is required. The closest analogy for a traditional system is a lookup by

These operations are automatically executed together in order to execute appropriate filters and column lookup by row offset.

Viewing the ColumnStore Query Plan

In MariaDB ColumnStore there is a set of SQL tracing stored functions provided to see the distributed query execution plan between the nodes.

The basic steps to using these SQL tracing stored functions are:

Start the trace for the particular session.
Execute the SQL statement in question.
Review the trace collected for the statement. As an example, the following session starts a trace, issues a query against a 6 million row fact table and 300,000 row dimension table, and then reviews the output from the trace:

The columns headings in the output are as follows:

Desc – Operation being executed. Possible values:
- BPS - Batch Primitive Step: scanning or projecting the column blocks.
- CES - Cross Engine Step: Performing Cross engine join

Note: The time recorded is the time from PrimProc and ExeMgr. Execution time from withing mysqld is not tracked here. There could be extra processing time in mysqld due to a number of factors such as ORDER BY.

Cache Clearing to Enable Cold Testing

Sometimes it can be useful to clear caches to allow understanding of un-cached and cached query access. The calFlushCache() function will clear caches on all servers. This is only really useful for testing query performance:

Viewing Extent Map Information

It can be useful to view details about the extent map for a given column. This can be achieved using the edit item process on any ColumnStore server. Available arguments can be provided by using the -h flag. The most common use is to provide the column object id with the -o argument which will output details for the column and in this case the -t argument is provided to show min / max values as dates:

Here it can be seen that the extent maps for the o_orderdate (object id 3032) column are well partitioned since the order table source data was sorted by the order_date. This example shows 2 separate DBRoot values as the environment was a 2-node combined deployment.

Column object ids may be found by querying the calpontsys.syscolumn metadata table (deprecated) or information_schema.columnstore_columns table (version 1.0.6+).

Query Statistics History

MariaDB ColumnStore query statistics history can be retrieved for analysis. By default the query stats collection is disabled. To enable the collection of query stats, the element in the ColumnStore.XML configuration file should be set to Y (default is N).

Cross Engine Support must also be enabled before enabling Query Statistics. See the section.

For Querystats Cross Engine User needs INSERT Privilege on querystats table.

Example:

When enabled the history of query statistics across all sessions along with execution time, and those stats provided by calgetstats() is stored in a table in the infinidb_querystats schema. Only queries in the following ColumnStore syntax are available for statistics monitoring:

SELECT
INSERT
UPDATE

Query Statistics Table

When QueryStats is enabled, the query statistics history is collected in the querystats table in the infinidb_querystats schema.

The columns of this table are:

queryID - A unique identifier assigned to the query
Session ID (sessionID) - The session number that executed the statement.
queryType - The type of the query whether insert, update, delete, select, delete, insert select or load data infile

Query Statistics Viewing

Users can view the query statistics by selecting the rows from the query stats table in the infinidb_querystats schema. Examples listed below:

Example 1: List execution time, rows returned for all the select queries within the past 12 hours:

Example 2: List the three slowest running select queries of session 2 within the past 12 hours:

Example 3: List the average, min and max running time of all the INSERT SELECT queries within the past 12 hours:

Analyzing Queries

Determining Active Queries

SHOW PROCESSLIST

The MariaDB SHOW PROCESSLIST statement is used to see a list of active queries on that UM:

getActiveSQLStatements

getActiveSQLStatements is a mcsadmin command that shows which SQL statements are currently being executed on the database:

Analysis of Individual Queries

Query Statistics

The calGetStats function provides statistics about resources used on the node, and network by the last run query. Example:

The output contains information on:

MaxMemPct - Peak memory utilization on the , likely in support of a large (User Module) based hash join operation.
NumTempFiles - Report on any temporary files created in support of query operations larger than available memory, typically for unusual join operations where the smaller table join cardinality exceeds some configurable threshold.
TempFileSpace - Report on space used by temporary files created in support of query operations larger than available memory, typically for unusual join operations where the smaller table join cardinality exceeds some configurable threshold.

Query Plan / Trace

Full Column Scan - an operation that scans each entry in a column using all available threads on the Performance Modules. Speed of operation is generally related to the size of the data type and the total number of rows in the column. The closest analogy for a traditional system is an index scan operation.
Partitioned Column Scan - an operation that uses the Extent Map to identify that certain portions of the column do not contain any matching values for a given set of filters. The closest analogy for a traditional row-based DBMS is a partitioned index scan, or partitioned table scan operation.
Column lookup by row offset - once the set of matching filters have been applied and the minimal set of rows have been identified; additional blocks are requested using a calculation that determines exactly which block is required. The closest analogy for a traditional system is a lookup by

These operations are automatically executed together in order to execute appropriate filters and column lookup by row offset.

Viewing the ColumnStore Query Plan

In MariaDB ColumnStore there is a set of SQL tracing stored functions provided to see the distributed query execution plan between the nodes.

The basic steps to using these SQL tracing stored functions are:

Start the trace for the particular session.
Execute the SQL statement in question.
Review the trace collected for the statement. As an example, the following session starts a trace, issues a query against a 6 million row fact table and 300,000 row dimension table, and then reviews the output from the trace:

The columns headings in the output are as follows:

Desc – Operation being executed. Possible values:
- BPS - Batch Primitive Step: scanning or projecting the column blocks.
- CES - Cross Engine Step: Performing Cross engine join

Cache Clearing to Enable Cold Testing

Viewing Extent Map Information

Column object ids may be found by querying the calpontsys.syscolumn metadata table (deprecated) or information_schema.columnstore_columns table (version 1.0.6+).

Query Statistics History

Cross Engine Support must also be enabled before enabling Query Statistics. See the section.

For Querystats Cross Engine User needs INSERT Privilege on querystats table.

Example:

SELECT
INSERT
UPDATE

Query Statistics Table

When QueryStats is enabled, the query statistics history is collected in the querystats table in the infinidb_querystats schema.

The columns of this table are:

queryID - A unique identifier assigned to the query
Session ID (sessionID) - The session number that executed the statement.
queryType - The type of the query whether insert, update, delete, select, delete, insert select or load data infile

Query Statistics Viewing

Users can view the query statistics by selecting the rows from the query stats table in the infinidb_querystats schema. Examples listed below:

Example 1: List execution time, rows returned for all the select queries within the past 12 hours:

Example 2: List the three slowest running select queries of session 2 within the past 12 hours:

Example 3: List the average, min and max running time of all the INSERT SELECT queries within the past 12 hours:

MariaDB [test]> SELECT calSetTrace(1);
+----------------+
| calSetTrace(1) |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT c_name, sum(o_totalprice)
    -> FROM customer, orders
    -> WHERE o_custkey = c_custkey
    -> AND c_custkey = 5
    -> GROUP BY c_name;
+--------------------+-------------------+
| c_name             | sum(o_totalprice) |
+--------------------+-------------------+
| Customer#000000005 |         684965.28 |
+--------------------+-------------------+
1 row in set, 1 warning (0.34 sec)

MariaDB [test]> SELECT calGetTrace();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    ----------------------------------------------------------------------------------------------------------+
| calGetTrace()                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    ----------------------------------------------------------------------------------------------------------+
|
Desc Mode Table           TableOID ReferencedColumns        PIO LIO PBE Elapsed Rows
BPS  PM   customer        3024     (c_custkey,c_name)       0   43  36  0.006   1
BPS  PM   orders          3038     (o_custkey,o_totalprice) 0   766 0   0.032   3
HJS  PM   orders-customer 3038     -                        -   -   -   -----   -
TAS  UM   -               -        -                        -   -   -   0.021   1
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    ----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT calSetTrace(1);
+----------------+
| calSetTrace(1) |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT c_name, sum(o_totalprice)
    -> FROM customer, orders
    -> WHERE o_custkey = c_custkey
    -> AND c_custkey = 5
    -> GROUP BY c_name;
+--------------------+-------------------+
| c_name             | sum(o_totalprice) |
+--------------------+-------------------+
| Customer#000000005 |         684965.28 |
+--------------------+-------------------+
1 row in set, 1 warning (0.34 sec)

MariaDB [test]> SELECT calGetTrace();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    ----------------------------------------------------------------------------------------------------------+
| calGetTrace()                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    ----------------------------------------------------------------------------------------------------------+
|
Desc Mode Table           TableOID ReferencedColumns        PIO LIO PBE Elapsed Rows
BPS  PM   customer        3024     (c_custkey,c_name)       0   43  36  0.006   1
BPS  PM   orders          3038     (o_custkey,o_totalprice) 0   766 0   0.032   3
HJS  PM   orders-customer 3038     -                        -   -   -   -----   -
TAS  UM   -               -        -                        -   -   -   0.021   1
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    ----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Analyzing Queries

hashtagDetermining Active Queries

hashtagSHOW PROCESSLIST

hashtaggetActiveSQLStatements

hashtagAnalysis of Individual Queries

hashtagQuery Statistics

hashtagQuery Plan / Trace

hashtagViewing the ColumnStore Query Plan

hashtagCache Clearing to Enable Cold Testing

hashtagViewing Extent Map Information

hashtagQuery Statistics History

hashtagQuery Statistics Table

hashtagQuery Statistics Viewing

Analyzing Queries

hashtagDetermining Active Queries

hashtagSHOW PROCESSLIST

hashtaggetActiveSQLStatements

hashtagAnalysis of Individual Queries

hashtagQuery Statistics

hashtagQuery Plan / Trace

hashtagViewing the ColumnStore Query Plan

hashtagCache Clearing to Enable Cold Testing

hashtagViewing Extent Map Information

hashtagQuery Statistics History

hashtagQuery Statistics Table

hashtagQuery Statistics Viewing

Determining Active Queries

SHOW PROCESSLIST

getActiveSQLStatements

Analysis of Individual Queries

Query Statistics

Query Plan / Trace

Viewing the ColumnStore Query Plan

Cache Clearing to Enable Cold Testing

Viewing Extent Map Information

Query Statistics History

Query Statistics Table

Query Statistics Viewing

Determining Active Queries

SHOW PROCESSLIST

getActiveSQLStatements

Analysis of Individual Queries

Query Statistics

Query Plan / Trace

Viewing the ColumnStore Query Plan

Cache Clearing to Enable Cold Testing

Viewing Extent Map Information

Query Statistics History

Query Statistics Table

Query Statistics Viewing