Group_concat() performance and gettrace() elapsed time

Build tested: 1.0.11-1

---

Query Stats: MaxMemPct-13; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-0; CacheI/O-1611421; BlocksTouched-1611421; PartitionBlocksEliminated-0; MsgBytesIn-12GB; MsgBytesOut-1MB; Mode-Distributed

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
AAA PM b 6955 (col1,col2) 0 42 0 0.006 42261
AAA PM a 6965 (col3,col2,col4) 0 1611379 0 153.791 942885171
BBB PM a-b 6965 - - - - ----- -
CCC UM - - - - - - 153.812 22311

From this it seems the elapsed time is 154seconds, yet fetching the data is taking 600 seconds, with intermittent low traffic on the network (we are on 10G), the server is showing ExeMGR at 100%.

---

Gettrace reported elapsed time of 154 seconds, but the query actually took 600 seconds to finished.

1) Is gettrace() reporting the correct elapsed time?
2) Elapsed time for what processes or steps are not being included in the gettrace() output?

There is another ticket, MCOL-901, opened against group_concat() for its excessive memory consumption.

Using a VM with 60gb memory, I did similar tests using a 1gb dbt3 database.

select l_orderkey, group_concat(l_partkey) from t1 group by l_orderkey;

1500000 rows in set, 1 warning (3 min 56.94 sec)

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
AAA PM t1 3092 (l_orderkey,l_partkey) 5870 5886 0 202.183 6001215
CCC UM - - - - - - 231.155 1500000

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

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

select l_orderkey, group_concat(l_partkey order by o_custkey separator '') as g from t1, t2 where l_orderkey = o_orderkey group by l_orderkey;

1500000 rows in set, 1 warning (4 min 13.48 sec)

MariaDB [tpch1]> select calgettrace();
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
AAA PM t2 3044 (o_custkey,o_orderkey) 1474 1486 0 0.102 1500000
AAA PM t1 3092 (l_orderkey,l_partkey) 0 5866 0 215.113 6001215
BBB PM t3 3092 - - - - ----- -
CCC UM - - - - - - 244.862 1500000

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

MariaDB [tpch1]> select calgetstats();
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

MariaDB [tpch1]> select l_orderkey, group_concat(replace(replace(l_partkey,3,1),4,1) order by o_custkey separator '') as g from t1, t2 where l_orderkey = o_orderkey group by l_orderkey;

1500000 rows in set, 1 warning (5 min 15.17 sec)

MariaDB [tpch1]> select calgettrace();
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
AAA PM t2 3044 (o_custkey,o_orderkey) 0 1470 0 0.133 1500000
AAA PM t1 3092 (l_orderkey,l_partkey) 0 5866 0 262.193 6001215
BBB PM t3 3092 - - - - ----- -
CCC UM - - - - - - 280.540 1500000

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

MariaDB [tpch1]> select calgetstats();
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

With 2gb of dbt3 data.

MariaDB [tpch1]> select l_orderkey, group_concat(replace(replace(l_partkey,3,1),4,1) order by o_custkey separator '') as g from t1, t2 where l_orderkey = o_orderkey group by l_orderkey;

1500000 rows in set, 1 warning (11 min 3.55 sec)

MariaDB [tpch1]> select calgettrace();
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
AAA PM t2 3044 (o_custkey,o_orderkey) 0 1470 0 0.100 1500000
AAA PM t1 3092 (l_orderkey,l_partkey) 0 11729 0 599.220 12002430
BBB PM t3 3092 - - - - ----- -
CCC UM - - - - - - 605.390 1500000

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

MariaDB [tpch1]> select calgetstats();
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)