[MCOL-4503] query executime is not consistent - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 1.2.5
Fix Version/s: Icebox
Component/s: None
Labels:
None
Environment:

Hide
hyperV cluster

* Cores = 96 per HV host Networking = dual 10g OS = server 2019 Storage = fast. Dual 16g fiber to netapp flashpool accelerated 10k SAS aggregate

* x2 UMs (cpu,ram,disk) 32 64GB 500GB OS 2TB Storage

* x5 PMs 16 32GB 500GB OS 10TB DB

* 2UMs, 5PMs

Show
hyperV cluster * Cores = 96 per HV host Networking = dual 10g OS = server 2019 Storage = fast. Dual 16g fiber to netapp flashpool accelerated 10k SAS aggregate * x2 UMs (cpu,ram,disk) 32 64GB 500GB OS 2TB Storage * x5 PMs 16 32GB 500GB OS 10TB DB * 2UMs, 5PMs

Description

The customer reported that they didn't get consistent query executime for the same query.

query

SELECT COUNT(*) as total, action from ( select case when ll_cat_id = 4002 then 'allowed' when ll_cat_id = 4003 then 'denied' end as action FROM q_2020_demo AS t0 WHERE t0.ll_cat_id IN (4002, 4003) AND log_source_id IN (SELECT log_source_id FROM sys_lookup_log_sources where log_source_identifier IN ('37-001311-033392-02','per4-durh00nc-5512','per5-denv00co-100d-1','per5-denv00co-100D-2','per5-denv00co-100d-3','3144-001311-034725-16','37-001311-034725-13','VPRLDEMO01','37-001311-034725-15','37-001311-034725-08','37-001311-034725-10','per4-durh00nc-100D-W1','37-001311-034725-20','00-000000-000000-01','01-001311-000985-03')) AND q_epoch BETWEEN 1609446898 AND 1610051698 UNION ALL select case when ll_cat_id = 4002 then 'allowed' when ll_cat_id = 4003 then 'denied' end as action FROM q_2021_demo AS t0 WHERE t0.ll_cat_id IN (4002, 4003) AND log_source_id IN (SELECT log_source_id FROM sys_lookup_log_sources where log_source_identifier IN ('37-001311-033392-02','per4-durh00nc-5512','per5-denv00co-100d-1','per5-denv00co-100D-2','per5-denv00co-100d-3','3144-001311-034725-16','37-001311-034725-13','VPRLDEMO01','37-001311-034725-15','37-001311-034725-08','37-001311-034725-10','per4-durh00nc-100D-W1','37-001311-034725-20','00-000000-000000-01','01-001311-000985-03')) AND q_epoch BETWEEN 1609446898 AND 1610051698 ) as t1 group by action UNION ALL SELECT COUNT(*) as total, action from ( select 1, 'total' as action FROM q_2020_demo AS t0 WHERE t0.ll_cat_id IN (4002, 4003) AND log_source_id IN (SELECT log_source_id FROM sys_lookup_log_sources where log_source_identifier IN ('37-001311-033392-02','per4-durh00nc-5512','per5-denv00co-100d-1','per5-denv00co-100D-2','per5-denv00co-100d-3','3144-001311-034725-16','37-001311-034725-13','VPRLDEMO01','37-001311-034725-15','37-001311-034725-08','37-001311-034725-10','per4-durh00nc-100D-W1','37-001311-034725-20','00-000000-000000-01','01-001311-000985-03')) AND q_epoch BETWEEN 1609446898 AND 1610051698 UNION ALL select 1, 'total' as action FROM q_2021_demo AS t0 WHERE t0.ll_cat_id IN (4002, 4003) AND log_source_id IN (SELECT log_source_id FROM sys_lookup_log_sources where log_source_identifier IN ('37-001311-033392-02','per4-durh00nc-5512','per5-denv00co-100d-1','per5-denv00co-100D-2','per5-denv00co-100d-3','3144-001311-034725-16','37-001311-034725-13','VPRLDEMO01','37-001311-034725-15','37-001311-034725-08','37-001311-034725-10','per4-durh00nc-100D-W1','37-001311-034725-20','00-000000-000000-01','01-001311-000985-03')) AND q_epoch BETWEEN 1609446898 AND 1610051698 ) as t1 group by action

executime time

1st

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

| total | action |

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

| 210787 | denied |

| 480355 | allowed |

| 691142 | total |

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

3 rows in set (25.305 sec)

2nd

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

| total | action |

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

| 691142 | total |

| 210787 | denied |

| 480355 | allowed |

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

3 rows in set (4.784 sec)

3rd

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

| total | action |

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

| 691142 | total |

| 210787 | denied |

| 480355 | allowed |

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

3 rows in set (10.612 sec)

Then, tried to different query with relatively small query.

(1)

MariaDB [qradar]> select calFlushCache();select ll_cat_id,count(*) as ct FROM q_2021_demo AS t0 WHERE t0.ll_cat_id IN (4002, 4003) AND q_epoch BETWEEN 1609943318 AND 1610548118 group by ll_cat_id; select calGetTrace();

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

| calFlushCache() |

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

| 0 |

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

1 row in set (0.005 sec)

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

| ll_cat_id | ct |

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

| 4003 | 226292 |

| 4002 | 485903 |

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

2 rows in set, 1 warning (0.832 sec)

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

|calGetTrace() |

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

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows

BPS PM t0 604560 (ll_cat_id,q_epoch) 3016 2202 0 0.128 40

TAS UM - - - - - - 0.112 2

TNS UM - - - - - - 0.000 2

1 row in set (0.001 sec)

(2)

MariaDB [qradar]> select calFlushCache();select ll_cat_id,count(*) as ct FROM q_2021_demo AS t0 WHERE t0.ll_cat_id IN (4002, 4003) AND q_epoch BETWEEN 1609943318 AND 1610548118 group by ll_cat_id; select calGetTrace();

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

| calFlushCache() |

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

| 0 |

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

1 row in set (0.003 sec)

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

| ll_cat_id | ct |

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

| 4003 | 226292 |

| 4002 | 485903 |

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

2 rows in set, 1 warning (1.800 sec)

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

|calGetTrace() |

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

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows

BPS PM t0 604560 (ll_cat_id,q_epoch) 3016 2202 0 0.158 40

TAS UM - - - - - - 0.136 2

TNS UM - - - - - - 0.000 2

1 row in set (0.001 sec)

(3)

MariaDB [qradar]> select calFlushCache();select ll_cat_id,count(*) as ct FROM q_2021_demo AS t0 WHERE t0.ll_cat_id IN (4002, 4003) AND q_epoch BETWEEN 1609943318 AND 1610548118 group by ll_cat_id; select calGetTrace();

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

| calFlushCache() |

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

| 0 |

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

1 row in set (0.005 sec)

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

| ll_cat_id | ct |

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

| 4003 | 226292 |

| 4002 | 485903 |

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

2 rows in set, 1 warning (0.607 sec)

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

|calGetTrace() |

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

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows

BPS PM t0 604560 (ll_cat_id,q_epoch) 3016 2202 0 0.181 40

TAS UM - - - - - - 0.146 2

TNS UM - - - - - - 0.000 2

(4)

MariaDB [qradar]> select calFlushCache();select ll_cat_id,count(*) as ct FROM q_2021_demo AS t0 WHERE t0.ll_cat_id IN (4002, 4003) AND q_epoch BETWEEN 1609943318 AND 1610548118 group by ll_cat_id; select calGetTrace();

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

| calFlushCache() |

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

| 0 |

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

1 row in set (0.005 sec)

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

| ll_cat_id | ct |

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

| 4003 | 226292 |

| 4002 | 485903 |

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

2 rows in set, 1 warning (0.351 sec)

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

|calGetTrace() |

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

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows

BPS PM t0 604560 (ll_cat_id,q_epoch) 3016 2202 0 0.121 40

TAS UM - - - - - - 0.099 2

TNS UM - - - - - - 0.000 2

1 row in set (0.001 sec)

(5)

MariaDB [qradar]> select calFlushCache();select ll_cat_id,count(*) as ct FROM q_2021_demo AS t0 WHERE t0.ll_cat_id IN (4002, 4003) AND q_epoch BETWEEN 1609943318 AND 1610548118 group by ll_cat_id; select calGetTrace();

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

| calFlushCache() |

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

| 0 |

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

1 row in set (0.003 sec)

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

| ll_cat_id | ct |

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

| 4003 | 226292 |

| 4002 | 485903 |

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

2 rows in set, 1 warning (0.440 sec)

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

|calGetTrace() |

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

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows

BPS PM t0 604560 (ll_cat_id,q_epoch) 3016 2202 0 0.120 40

TAS UM - - - - - - 0.098 2

TNS UM - - - - - - 0.000 2

1 row in set (0.001 sec)

(6)

MariaDB [qradar]> select calFlushCache();select ll_cat_id,count(*) as ct FROM q_2021_demo AS t0 WHERE t0.ll_cat_id IN (4002, 4003) AND q_epoch BETWEEN 1609943318 AND 1610548118 group by ll_cat_id; select calGetTrace();

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

| calFlushCache() |

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

| 0 |

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

1 row in set (0.004 sec)

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

| ll_cat_id | ct |

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

| 4003 | 226292 |

| 4002 | 485903 |

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

2 rows in set, 1 warning (0.296 sec)

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

|calGetTrace() |

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

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows

BPS PM t0 604560 (ll_cat_id,q_epoch) 3016 2202 0 0.177 40

TAS UM - - - - - - 0.160 2

TNS UM - - - - - - 0.000 2

1 row in set (0.001 sec)

(7)

MariaDB [qradar]> select calFlushCache();select ll_cat_id,count(*) as ct FROM q_2021_demo AS t0 WHERE t0.ll_cat_id IN (4002, 4003) AND q_epoch BETWEEN 1609943318 AND 1610548118 group by ll_cat_id; select calGetTrace();

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

| calFlushCache() |

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

| 0 |

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

1 row in set (0.005 sec)

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

| ll_cat_id | ct |

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

| 4003 | 226292 |

| 4002 | 485903 |

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

2 rows in set, 1 warning (9.793 sec)

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

|calGetTrace() |

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

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows

BPS PM t0 604560 (ll_cat_id,q_epoch) 3016 2202 0 0.120 40

TAS UM - - - - - - 0.098 2

TNS UM - - - - - - 0.000 2

1 row in set (0.001 sec)

Summary of above results

(1) 2 rows in set, 1 warning (0.832 sec)

(2) 2 rows in set, 1 warning (1.800 sec)

(3) 2 rows in set, 1 warning (0.607 sec)

(4) 2 rows in set, 1 warning (0.351 sec)

(5) 2 rows in set, 1 warning (0.440 sec)

(6) 2 rows in set, 1 warning (0.296 sec)

(7) 2 rows in set, 1 warning (9.793 sec)

free -m

per5-qradar-maria-um1

              total        used        free      shared  buff/cache   available

Mem:          31976        2319       27749         162        1908       29130

Swap:         16127           0       16127

per5-qradar-maria-um2

              total        used        free      shared  buff/cache   available

Mem:          31976        3367       28033          98         576       28177

Swap:         16127           0       16127

per5-qradar-maria-pm1

              total        used        free      shared  buff/cache   available

Mem:          31976        7627         224         885       24124       23072

Swap:         16127         569       15558

per5-qradar-maria-pm2

              total        used        free      shared  buff/cache   available

Mem:          31976        8266         224         391       23486       22929

Swap:         16127         231       15896

per5-qradar-maria-pm3

              total        used        free      shared  buff/cache   available

Mem:          31976        8330         226         400       23419       22854

Swap:         16127         233       15894

per5-qradar-maria-pm4

              total        used        free      shared  buff/cache   available

Mem:          31976        6112         663         398       25200       25075

Swap:         16127         217       15910

per5-qradar-maria-pm5

              total        used        free      shared  buff/cache   available

Mem:          31976        7487         219         391       24270       23707

Swap:         16127         236       15891

*Note that that executime time is random without any type of workload during this test. *

Attachments

Issue Links

duplicates

MCOL-4527 Simple query performace is degraded between 5.4 and 5.5

Closed

query executime is not consistent

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration