멋지게 놀아라

/usr/local/mariadb/columnstore/etc/Columnstore.xml  

        <QueryStats>

                <Enabled>Y</Enabled>

        </QueryStats>

/usr/local/mariadb/columnstore/bin/mcsadmin restartsystem

MariaDB [(none)]> use tpch ;

Database changed

MariaDB [tpch]> show tables ;

+----------------+

| Tables_in_tpch |

+----------------+

| customer       |

| lineitem       |

| nation         |

| orders         |

| part           |

| partsupp       |

| region         |

| supplier       |

+----------------+

8 rows in set (0.01 sec)

MariaDB [tpch]> select count(*) from customer ;

+----------+

| count(*) |

+----------+

|   150000 |

+----------+

1 row in set (0.05 sec)

MariaDB [tpch]> select calGetStats();

+---------------+

| calGetStats() |

+---------------+

| NULL          |

+---------------+

1 row in set (0.01 sec)

MariaDB [tpch]> use columnstore ;

Database changed

MariaDB [columnstore]> show tables ;

+-----------------------+

| Tables_in_columnstore |

+-----------------------+

| customer              |

| lineitem              |

| nation                |

| orders                |

| part                  |

| partsupp              |

| region                |

| supplier              |

+-----------------------+

8 rows in set (0.00 sec)

MariaDB [columnstore]> select count(*) from customer ;

+----------+

| count(*) |

+----------+

|   150000 |

+----------+

1 row in set (0.16 sec)

MariaDB [columnstore]> select calGetStats();

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| calGetStats()                                                                                                                                                                              |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-0; CacheI/O-75; BlocksTouched-75; PartitionBlocksEliminated-0; MsgBytesIn-2KB; MsgBytesOut-587B; Mode-Distributed |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

MaxMemPct - Peak memory utilization on the User Module, 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.

PhyI/O - Number of 8k blocks read from disk, SSD, or other persistent storage.

CacheI/O - Approximate number of 8k blocks processed in memory, adjusted down by the number of discrete PhyI/O calls required.

BlocksTouched - Approximate number of 8k blocks processed in memory.

PartitionBlocksEliminated - The number of block touches eliminated via the Extent Map elimination behavior.

MsgBytesIn, MsgByteOut - Message size in MB sent between nodes in support of the query.

MariaDB [infinidb_querystats]> use tpch

Database changed

MariaDB [tpch]> explain

    -> select count(*) from customer ;

+------+-------------+----------+-------+---------------+--------------+---------+------+--------+-------------+

| id   | select_type | table    | type  | possible_keys | key          | key_len | ref  | rows   | Extra       |

+------+-------------+----------+-------+---------------+--------------+---------+------+--------+-------------+

|    1 | SIMPLE      | customer | index | NULL          | CUSTOMER_FK1 | 5       | NULL | 149251 | Using index |

+------+-------------+----------+-------+---------------+--------------+---------+------+--------+-------------+

1 row in set (0.00 sec)

MariaDB [tpch]> select calSetTrace(1);

+----------------+

| calSetTrace(1) |

+----------------+

|              0 |

+----------------+

1 row in set (0.01 sec)

MariaDB [tpch]> select count(*) from customer ;

+----------+

| count(*) |

+----------+

|   150000 |

+----------+

1 row in set (0.05 sec)

MariaDB [tpch]> select calGetTrace();

+---------------+

| calGetTrace() |

+---------------+

| NULL          |

+---------------+

1 row in set (0.00 sec)

MariaDB [tpch]> use columnstore

Database changed

MariaDB [columnstore]> explain

    -> select count(*) from customer ;

+------+-------------+----------+------+---------------+------+---------+------+------+-------+

| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |

+------+-------------+----------+------+---------------+------+---------+------+------+-------+

|    1 | SIMPLE      | customer | ALL  | NULL          | NULL | NULL    | NULL | 2000 |       |

+------+-------------+----------+------+---------------+------+---------+------+------+-------+

1 row in set (0.00 sec)

MariaDB [columnstore]> select calSetTrace(1);

+----------------+

| calSetTrace(1) |

+----------------+

|              1 |

+----------------+

1 row in set (0.00 sec)

MariaDB [columnstore]> select count(*) from customer ;

+----------+

| count(*) |

+----------+

|   150000 |

+----------+

1 row in set, 1 warning (0.18 sec)

MariaDB [columnstore]> select calGetTrace();

+---------------------------------------------------------------------------------------+

| calGetTrace()                                                                         |

+---------------------------------------------------------------------------------------+

| Desc Mode Table    TableOID ReferencedColumns PIO LIO PBE Elapsed Rows                |

| BPS  PM   customer 3045     (c_custkey)       0   75  0   0.012   19                  |

| TAS  UM   -        -        -                 -   -   -   0.000   1                   |

+---------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

Desc – Operation being executed. Possible values:

    BPS - Batch Primitive Step : scanning or projecting the column blocks.

    CES - Cross Engine Step: Performing Cross engine join

    DSS - Dictionary Structure Step : a dictionary scan for a particular variable length string value.

    HJS - Hash Join Step : Performing a hash join between 2 tables

    HVS - Having Step: Performing the having clause on the result set

    SQS - Sub Query Step: Performaning a sub query

    TAS - Tuple Aggregation step : the process of receiving intermediate aggregation results at the UM from the PM nodes.

    TNS - Tuple Annexation Step : Query result finishing, e.g. filling in constant columns, limit, order by and final distinct cases.

    TUS = Tuple Union step : Performing a SQL union of 2 sub queries.

    TCS = Tuple Constant Step: Process Constant Value Columns

    WFS = Window Function Step: Performing a window function.

Mode – Where the operation was performed: UM or PM

Table – Table for which columns may be scanned/projected.

TableOID – ObjectID for the table being scanned.

ReferencedOIDs – ObjectIDs for the columns required by the query.

PIO – Physical I/O (reads from storage) executed for the query.

LIO – Logical I/O executed for the query, also known as Blocks Touched.

PBE – Partition Blocks Eliminated identifies blocks eliminated by Extent Map min/max.

Elapsed – Elapsed time for a give step.

Rows – Intermediate rows returned

MariaDB [(none)]> use infinidb_querystats

Database changed

MariaDB [infinidb_querystats]> show tables ;

+-------------------------------+

| Tables_in_infinidb_querystats |

+-------------------------------+

| priority                      |

| user_priority                 |

+-------------------------------+

2 rows in set (0.00 sec)

[root@localhost ~]# cd /usr/local/mariadb/columnstore/mysql

[root@localhost mysql]# ls

bin                        calshowprocesslist.sql    columnstoreSupportReport.columnstore-1.tar.gz  dumpcat_mysql.sql         lib          my.cnf_bak         scripts

calremoveuserpriority.sql  columnstore_info.sql      db                                             init.d                    logrotate.d  my.cnf.d           share

calsetuserpriority.sql     columnstoreSupportReport  db_bak                                         install_calpont_mysql.sh  my.cnf       mysql-Columnstore  syscatalog_mysql.sql

[root@localhost mysql]# cat install_calpont_mysql.sh

.....

CREATE TABLE IF NOT EXISTS infinidb_querystats.querystats

(

  queryID bigint NOT NULL AUTO_INCREMENT,

  sessionID bigint DEFAULT NULL,

  host varchar(50),

  user varchar(50),

  priority char(20),

  queryType char(25),

  query varchar(8000),

  startTime timestamp NOT NULL,

  endTime timestamp NOT NULL,

  `rows` bigint,

  errno int,

  phyIO bigint,

  cacheIO bigint,

  blocksTouched bigint,

  CPBlocksSkipped bigint,

  msgInUM bigint,

  msgOutUm bigint,

  maxMemPct int,

  blocksChanged bigint,

  numTempFiles bigint,

  tempFileSpace bigint,

  PRIMARY KEY (queryID)

);

MariaDB [infinidb_querystats]> CREATE TABLE IF NOT EXISTS infinidb_querystats.querystats

    -> (

    ->   queryID bigint NOT NULL AUTO_INCREMENT,

    ->   sessionID bigint DEFAULT NULL,

    ->   host varchar(50),

    ->   user varchar(50),

    ->   priority char(20),

    ->   queryType char(25),

    ->   query varchar(8000),

    ->   startTime timestamp NOT NULL,

    ->   endTime timestamp NOT NULL,

    ->   `rows` bigint,

    ->   errno int,

    ->   phyIO bigint,

    ->   cacheIO bigint,

    ->   blocksTouched bigint,

    ->   CPBlocksSkipped bigint,

    ->   msgInUM bigint,

    ->   msgOutUm bigint,

    ->   maxMemPct int,

    ->   blocksChanged bigint,

    ->   numTempFiles bigint,

    ->   tempFileSpace bigint,

    ->   PRIMARY KEY (queryID)

    -> );

Query OK, 0 rows affected (0.03 sec)

MariaDB [infinidb_querystats]> show tables ;

+-------------------------------+

| Tables_in_infinidb_querystats |

+-------------------------------+

| priority                      |

| querystats                    |

| user_priority                 |

+-------------------------------+

MariaDB [infinidb_querystats]> select * from querystats ;

+---------+-----------+-----------+------+----------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+---------+-------+-------+---------+---------------+-----------------+----------+----------+-----------+---------------+--------------+---------------+

| queryID | sessionID | host      | user | priority | queryType | query                                                                                                                                                                    | startTime           | endTime             | rows    | errno | phyIO | cacheIO | blocksTouched | CPBlocksSkipped | msgInUM  | msgOutUm | maxMemPct | blocksChanged | numTempFiles | tempFileSpace |

+---------+-----------+-----------+------+----------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+---------+-------+-------+---------+---------------+-----------------+----------+----------+-----------+---------------+--------------+---------------+

|       1 |        21 | localhost | root | LOW      | SELECT    | select count(*) from customer                                                                                                                                            | 2018-03-09 01:30:39 | 2018-03-09 01:30:39 |       1 |     0 |     0 |      75 |            75 |               0 |     2356 |      587 |         0 |             0 |            0 |             0 |

|       2 |        21 | localhost | root | LOW      | SELECT    | select count(*) from columnstore.region                                                                                                                                  | 2018-03-09 01:32:52 | 2018-03-09 01:32:53 |       1 |     0 |     2 |       3 |             2 |               0 |      124 |      526 |         0 |             0 |            0 |             0 |

|       3 |        21 | localhost | root | LOW      | SELECT    | select count(*) from columnstore.region                                                                                                                                  | 2018-03-09 01:33:25 | 2018-03-09 01:33:25 |       1 |     0 |     0 |       2 |             2 |               0 |      124 |      526 |         0 |             0 |            0 |             0 |

|       4 |        21 | localhost | root | LOW      | SELECT    | select * from columnstore.region                                                                                                                                         | 2018-03-09 01:34:09 | 2018-03-09 01:34:09 |       5 |     0 |     8 |       6 |             6 |               0 |      635 |      786 |         0 |             0 |            0 |             0 |

|       5 |        27 | localhost | root | LOW      | SELECT    | select count(*) from customer                                                                                                                                            | 2018-03-09 02:06:57 | 2018-03-09 02:06:57 |       1 |     0 |     0 |      75 |            75 |               0 |     2356 |      587 |         0 |             0 |            0 |             0 |

|       6 |        27 | localhost | root | LOW      | SELECT    | select O_ORDERDATE from orders limit 1                                                                                                                                   | 2018-03-09 02:11:40 | 2018-03-09 02:11:41 |       1 |     0 |   737 |     149 |           144 |               0 |  6360202 |     1049 |         0 |             0 |            0 |             0 |

|       7 |        27 | localhost | root | LOW      | SELECT    | SELECT a.C_CUSTKEY , b.O_ORDERDATE , count(O_ORDERKEY)  FROM  customer a INNER JOIN orders b ON a.C_CUSTKEY = b.O_CUSTKEY  GROUP BY a.C_CUSTKEY , b.O_ORDERDATE          | 2018-03-09 02:13:00 | 2018-03-09 02:13:03 | 1477407 |     0 |  1474 |   70139 |         70123 |               0 | 28071174 |  2702728 |         0 |             0 |            0 |             0 |

|       8 |        27 | localhost | root | LOW      | SELECT    | SELECT a.C_CUSTKEY , b.O_ORDERDATE , count(O_ORDERKEY)  FROM  customer a INNER JOIN orders b ON a.C_CUSTKEY = b.O_CUSTKEY  GROUP BY a.C_CUSTKEY , b.O_ORDERDATE limit 10 | 2018-03-09 02:13:37 | 2018-03-09 02:13:37 |      10 |     0 |     0 |   70123 |         70123 |               0 | 28071174 |  2702728 |         0 |             0 |            0 |             0 |

+---------+-----------+-----------+------+----------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+---------+-------+-------+---------+---------------+-----------------+----------+----------+-----------+---------------+--------------+---------------+

8 rows in set (0.01 sec)

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 - The text of the query

Host (host) - The host that executed the statement.

User ID (user) - The user that executed the statement.

Priority (priority) The priority the user has for this statement.

Query Execution Times (startTime, endTime) Calculated as end time – start time.

start time - the time that the query gets to ExeMgr, DDLProc, or DMLProc

end time - the time that the last result packet exits ExeMgr, DDLProc or DMLProc

Rows returned or affected (rows) -The number of rows returned for SELECT queries, or the number of rows affected by DML queries. Not valid for DDL and other query types.

Error Number (errNo) - The IDB error number if this query failed, 0 if it succeeded.

Physical I/O (phyIO) - The number of blocks that the query accessed from the disk, including the pre-fetch blocks. This statistic is only valid for the queries that are processed by ExeMgr, i.e. SELECT, DML with WHERE clause, and INSERT SELECT.

Cache I/O (cacheIO) - The number of blocks that the query accessed from the cache. This statistic is only valid for queries that are processed by ExeMgr, i.e. SELECT, DML with WHERE clause, and INSERT SELECT.

Blocks Touched (blocksTouched) - The total number of blocks that the query accessed physically and from the cache. This should be equal or less than the sum of physical I/O and cache I/O. This statistic is only valid for queries that are processed by ExeMgr, i.e. SELECT, DML with WHERE clause, and INSERT SELECT.

Partition Blocks Eliminated (CPBlocksSkipped) - The number of blocks being eliminated by the extent map casual partition. This statistic is only valid for queries that are processed by ExeMgr, i.e. SELECT, DML with WHERE clause, and INSERT SELECT.

Messages from UM to PM (msgOutUM) - The number of messages in bytes that ExeMgr sends to the PrimProc. If a message needs to be distributed to all the PMs, the sum of all the distributed messages will be counted. Only valid for queries that are processed by ExeMgr, i.e. SELECT, DML with WHERE clause, and INSERT SELECT.

Messages from PM to UM (msgInUM) - The number of messages in bytes that PrimProc sends to the ExeMgr. Only valid for queries that are processed by ExeMgr, i.e. SELECT, DML with where clause, and INSERT SELECT.

Memory Utilization (maxMemPct) - This field shows memory utilization for the User Module (UM) in support of any UM join, group by, aggregation, distinct, or other operation.

Blocks Changed (blocksChanged) - Total number of blocks that queries physically changed on disk. This is only for delete/update statements.

Temp Files (numTempFiles) - This field shows any temporary file utilization for the User Module (UM) in support of any UM join, group by, aggregation, distinct, or other operation.

Temp File Space (tempFileSpace) - This shows the size of any temporary file utilization for the User Module (UM) in support of any UM join, group by, aggregation, distinct, or other operation.