search

sudsk / memsql-perf

Memsql performance tests
https://sudsk.github.io/memsql-perf/
0 stars 0 forks source link

Slow response for SME user with 1-10 outlet access #1

Open sudsk opened 6 years ago

sudsk commented 6 years ago

I’m getting faster response time for a user having ~6k outlet access compared with a user with 1-5 outlet access. This was observed even after repeatedly running the queries. The user with 1 outlet is accessing very small dataset.

/**** Query#2 for a SME user (1 outlet) ****/

select a.*
from mymemsqldb.transactions a , mymemsqldb.outlet b
where a.outlet_key = b.OUTLET_KEY 
and transaction_datetime > '2017-07-01' 
and transaction_datetime < '2018-03-01' 
and b.USER_KEY = 20
order by transaction_datetime asc 
limit 10 offset 100;

User-Key = 21 (5 outlets)

First run - 3.47s Second run - 2.71s Third run - 1.10s Fourth run - 0.97s

User-Key = 20 (1 outlet)

First run - 2.25s Second run - 1.08s

sudsk commented 6 years ago

Profile for SME query

Top offset:[?] limit:[?] actual_rows: 10 exec_time: 0ms
--
GatherMerge [remote_0.TRANSACTION_DATETIME] partitions:all alias:remote_0 actual_rows: 110 exec_time: 0ms start_time: 00:00:00.000 end_time: 00:00:00.907
Project [a.OUTLET_KEY, a.TRADING_DATE, a.PROCESSING_DATE, a.TRANSACTION_DATETIME, a.TRANSACTION_DATE, a.CARD_NUMBER_LENGTH, a.CARD_NUMBER_LEFT, a.CARD_NUMBER_RIGHT, a.CARD_EXPIRY_DATE, a.CARD_ISSUER_NUMBER, a.CARD_START_DATE, a.TRANSACTION_TYPE_KEY, a.TR...
[network_traffic: 17.764000 KB \| max:17,764 at partition_0, average: 740.166667, std dev: 3,549.714633]
Top limit:[?] actual_rows: 110 exec_time: 0ms
HashJoin [b.OUTLET_KEY = a.OUTLET_KEY] actual_rows: 110 exec_time: 0ms start_time: 00:00:00.906 memory_usage: 3,145.728027 KB build_start_time: [00:00:00.000, 00:00:00.003] build_exec_time: 0ms
\|---Project [b_0.OUTLET_KEY] est_rows:5,985 alias:b actual_rows: 1 exec_time: 0ms start_time: 00:00:00.001
\|   Filter [b_0.USER_KEY = ?] actual_rows: 1 exec_time: 0ms start_time: 00:00:00.001
\|   ColumnStoreScan mymemsqldb.outlet AS b_0, KEY USER_KEY (USER_KEY) USING CLUSTERED COLUMNSTORE est_table_rows:120,110 est_filtered:5,986 actual_rows: 24,954 exec_time: 0ms start_time: [00:00:00.000, 00:00:00.002] memory_usage: 3,145.728027 KB segments...
\|    [actual_rows: 24,954 \| max:5,087 at partition_0, average: 1,039.750000, std dev: 2,027.035998]
Filter [a.TRANSACTION_DATETIME > (?!:>datetime(6) NULL) AND a.TRANSACTION_DATETIME < (?!:>datetime(6) NULL)] actual_rows: 5,196 exec_time: 178ms start_time: 00:00:00.003
[actual_rows: 5,196 \| max:5,196 at partition_0, average: 216.500000, std dev: 1,038.297525]
BloomFilter table:b fields:a.OUTLET_KEY actual_rows: 16,384 exec_time: 170ms start_time: 00:00:00.003
[actual_rows: 16,384 \| max:16,384 at partition_0, average: 682.666667, std dev: 3,273.954320]
OrderedColumnStoreScan mymemsqldb.transactions AS a, KEY TRANSACTION_DATETIME (TRANSACTION_DATETIME, CARD_SCHEME_KEY, TRANSACTION_TYPE_KEY, TRANSACTION_CURRENCY_KEY, PROCESSING_DATE) USING CLUSTERED COLUMNSTORE est_table_rows:2,348,777,760 est_filtered:1...
[actual_rows: 43,157,880 \| max:43,157,880 at partition_0, average: 1,798,245.000000, std dev: 8,624,080.057639]
sudsk commented 6 years ago

Profile for Corporate query

Top offset:[?] limit:[?] actual_rows: 10 exec_time: 0ms
GatherMerge [remote_0.TRANSACTION_DATETIME] partitions:all alias:remote_0 actual_rows: 2,640 exec_time: 0ms start_time: 00:00:00.000 end_time: 00:00:01.556
Project [a.OUTLET_KEY, a.TRADING_DATE, a.PROCESSING_DATE, a.TRANSACTION_DATETIME, a.TRANSACTION_DATE, a.CARD_NUMBER_LENGTH, a.CARD_NUMBER_LEFT, a.CARD_NUMBER_RIGHT, a.CARD_EXPIRY_DATE, a.CARD_ISSUER_NUMBER, a.CARD_START_DATE, a.TRANSACTION_TYPE_KEY, a.TR...
Top limit:[?] actual_rows: 2,640 exec_time: 0ms
HashJoin [b.OUTLET_KEY = a.OUTLET_KEY] actual_rows: 2,640 exec_time: 0ms start_time: [00:00:00.250, 00:00:01.554] memory_usage: 3,145.728027 KB build_start_time: [00:00:00.056, 00:00:00.070] build_exec_time: 0ms
|---Project [b_0.OUTLET_KEY] est_rows:5,985 alias:b actual_rows: 6,008 exec_time: 0ms start_time: [00:00:00.056, 00:00:00.070]
|   Filter [b_0.USER_KEY = ?] actual_rows: 6,008 exec_time: 1ms start_time: [00:00:00.056, 00:00:00.070]
|   ColumnStoreScan mymemsqldb.outlet AS b_0, KEY USER_KEY (USER_KEY) USING CLUSTERED COLUMNSTORE est_table_rows:120,110 est_filtered:5,986 actual_rows: 120,110 exec_time: 7ms start_time: [00:00:00.054, 00:00:00.061] memory_usage: 3,145.728027 KB segment...
Filter [a.TRANSACTION_DATETIME > (?!:>datetime(6) NULL) AND a.TRANSACTION_DATETIME < (?!:>datetime(6) NULL)] actual_rows: 107,440 exec_time: 7ms start_time: [00:00:00.059, 00:00:00.112]
BloomFilter table:b fields:a.OUTLET_KEY actual_rows: 1,054,327 exec_time: 5ms start_time: [00:00:00.059, 00:00:00.112]
OrderedColumnStoreScan mymemsqldb.transactions AS a, KEY TRANSACTION_DATETIME (TRANSACTION_DATETIME, CARD_SCHEME_KEY, TRANSACTION_TYPE_KEY, TRANSACTION_CURRENCY_KEY, PROCESSING_DATE) USING CLUSTERED COLUMNSTORE est_table_rows:2,348,777,760 est_filtered:1,002,299,196 actual_rows: 19,361,792 exec_time: 1,467ms start_time: [00:00:00.056, 00:00:00.072] memory_usage: 155,713.531250 KB segments_scanned: 86 segments_skipped: 1,298 segments_fully_contained: 0
sudsk commented 6 years ago

Explain plan for SME query

EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
Top offset:100 limit:10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
GatherMerge [remote_0.TRANSACTION_DATETIME] partitions:all alias:remote_0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
Project [a.OUTLET_KEY, a.TRADING_DATE, a.PROCESSING_DATE, a.TRANSACTION_DATETIME, a.TRANSACTION_DATE, a.CARD_NUMBER_LENGTH, a.CARD_NUMBER_LEFT, a.CARD_NUMBER_RIGHT, a.CARD_EXPIRY_DATE, a.CARD_ISSUER_NUMBER, a.CARD_START_DATE, a.TRANSACTION_TYPE_KEY, a.TRANSACTION_SOURCE_KEY, a.SETTLEMENT_AMOUNT, a.SETTLEMENT_CURRENCY_KEY, a.TRANSACTION_AMOUNT, a.TRANSACTION_CURRENCY_KEY, a.ACQUIRED_PROCESSED_KEY, a.CARD_PRODUCT_KEY, a.CARD_PRODUCT_TYPE_KEY, a.CARD_SCHEME_KEY, a.SEQUENCE_NO, a.AUTH_CODE, a.AUTH_METHOD_KEY...]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Top limit:[?]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
HashJoin [b.OUTLET_KEY = a.OUTLET_KEY]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
|---Project [b_0.OUTLET_KEY] est_rows:24 alias:b                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
|   Filter [b_0.USER_KEY = 20]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
|   ColumnStoreScan mymemsqldb.outlet AS b_0, KEY USER_KEY (USER_KEY) USING CLUSTERED COLUMNSTORE est_table_rows:120,110 est_filtered:3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
Filter [a.TRANSACTION_DATETIME > ('2017-07-01'!:>datetime(6) NULL) AND a.TRANSACTION_DATETIME < ('2018-03-01'!:>datetime(6) NULL)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
BloomFilter table:b fields:a.OUTLET_KEY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
OrderedColumnStoreScan mymemsqldb.transactions AS a, KEY TRANSACTION_DATETIME (TRANSACTION_DATETIME, CARD_SCHEME_KEY, TRANSACTION_TYPE_KEY, TRANSACTION_CURRENCY_KEY, PROCESSING_DATE) USING CLUSTERED COLUMNSTORE est_table_rows:2,348,777,760 est_filtered:669,130,341                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

11 rows selected.
sudsk commented 6 years ago

Explain plan for Corporate query

EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
Top offset:100 limit:10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
GatherMerge [remote_0.TRANSACTION_DATETIME] partitions:all alias:remote_0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
Project [a.OUTLET_KEY, a.TRADING_DATE, a.PROCESSING_DATE, a.TRANSACTION_DATETIME, a.TRANSACTION_DATE, a.CARD_NUMBER_LENGTH, a.CARD_NUMBER_LEFT, a.CARD_NUMBER_RIGHT, a.CARD_EXPIRY_DATE, a.CARD_ISSUER_NUMBER, a.CARD_START_DATE, a.TRANSACTION_TYPE_KEY, a.TRANSACTION_SOURCE_KEY, a.SETTLEMENT_AMOUNT, a.SETTLEMENT_CURRENCY_KEY, a.TRANSACTION_AMOUNT, a.TRANSACTION_CURRENCY_KEY, a.ACQUIRED_PROCESSED_KEY, a.CARD_PRODUCT_KEY, a.CARD_PRODUCT_TYPE_KEY, a.CARD_SCHEME_KEY, a.SEQUENCE_NO, a.AUTH_CODE, a.AUTH_METHOD_KEY...]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Top limit:[?]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
HashJoin [b.OUTLET_KEY = a.OUTLET_KEY]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
|---Project [b_0.OUTLET_KEY] est_rows:6,011 alias:b                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
|   Filter [b_0.USER_KEY = 1]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
|   ColumnStoreScan mymemsqldb.outlet AS b_0, KEY USER_KEY (USER_KEY) USING CLUSTERED COLUMNSTORE est_table_rows:120,110 est_filtered:6,012                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
Filter [a.TRANSACTION_DATETIME > ('2017-07-01'!:>datetime(6) NULL) AND a.TRANSACTION_DATETIME < ('2018-03-01'!:>datetime(6) NULL)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
BloomFilter table:b fields:a.OUTLET_KEY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
OrderedColumnStoreScan mymemsqldb.transactions AS a, KEY TRANSACTION_DATETIME (TRANSACTION_DATETIME, CARD_SCHEME_KEY, TRANSACTION_TYPE_KEY, TRANSACTION_CURRENCY_KEY, PROCESSING_DATE) USING CLUSTERED COLUMNSTORE est_table_rows:2,348,777,760 est_filtered:669,130,341                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

11 rows selected.
sudsk commented 6 years ago

CPU usage

SME query - 2 vCPUs in each VM, so 4 in total across cluster. Corp query - all 16 vCPUs across the cluster.

sudsk commented 6 years ago

Partitions and Count

User_Key = 22 (1 outlet access, newly added to confirm the issue is not limited to a partition) Outlet_key = 6, partition_id=20, count= 8352

User_Key = 21 (5 outlet access) Outlet_key in (1,2,3,4,5), partition_id=11,count=8352; partition_id=19,count=8352; partition_id=9,count=8352; partition_id=12,count=16704

User_Key = 20 (1 outlet access) Outlet_key = 0, partition_id=0, count= 8340