[PPT] - Parallel Query Execution in POLARDB for MySQL ystein Grvlen Benny PowerPoint Presentation

SLIDE 1

Parallel Query Execution in POLARDB for MySQL

Øystein Grøvlen Benny Wang Alibaba Cloud

SLIDE 2

Agenda

What is Parallel Query?
Parallel Query Design
How to use Parallel Query
Parallel Query Performance
Future Work

SLIDE 3

Agenda

What is Parallel Query?
Parallel Query Design
How to use Parallel Query
Parallel Query Performance
Future Work

SLIDE 4

What is Parallel Query?

Parallel Query is an innovative method to accelerate MySQL queries from Alibaba Cloud.

Traditionally, 1 MySQL query runs with just 1 thread, and can not take advantage of multiple

cores on modern processors.

Parallel Query takes advantage of modern processors to distribute work across many or all

available cores:

8 parallel threads can be up to 8 times faster
32 parallel threads can be up to 32 times faster

SLIDE 5

Why Parallel Query?

2003: CPUs stopped getting faster
2004-2019 focus on more cores, sockets.
PQ lets MySQL take advantage of last 15 years of progress.

SLIDE 6

How to Use Parallel Query

Parallel Query runs against your existing InnoDB data.

No data extraction to another system is required. No query modifications are required.

Parallel Query within InnoDB (no extraction needed) is an amazing feature exclusive to Alibaba Cloud

SLIDE 7

Query with Parallelism

SELECT count(*) FROM production.product; Serial execution plan: .

1 active thread 63 idle threads

Thread 1: Scan, Count SQL Client

SLIDE 8

Parallel Execution Plan

Sum

Thread 1: Scan, Count Thread 2: Scan, Count Thread 3: Scan, Count Thread 4: Scan, Count Thread 5: Scan, Count Thread 6: Scan, Count Thread 7: Scan, Count . . . Thread 64: Scan, Count

With 64 parallel threads, each thread does < 2% of the work.

SQL Client

SLIDE 9

Agenda

What is Parallel Query?
Parallel Query Design
How to use Parallel Query
Parallel Query Performance
Future Work

SLIDE 10

Parallel Query Architecture

…

… …

Gather

MESSAGE QUEUE WORKERs

… … … … …

PARALLEL SCAN LEADER

⑅ ⑅ ! " T T … T ⑅ ⑅ ! " T T … T ⑅ ⑅ ! " T T … T ⑅ ⑅ ! " T T … T ⑅ ⑅ ! " T T … T … ⑅ ⑅ ! " T T …

SLIDE 11

Partitioning

11 17 25

5 8

1 2 3 4 5 6 7 8 9 10

14

11 12 13 14 15 16

20 22

17 18 19 20 21 22 23 24

28 31

25 26 27 28 29 30 31 32

SLIDE 12

11 17 25

5 8

1 2 3 4 5 6 7 8 9 10

14

11 12 13 14 15 16

20 22

17 18 19 20 21 22 23 24

28 31

25 26 27 28 29 30 31 32

Partitioning

Partition 1 Partition 2

2 partitions

InnoDB partitions the B-tree

SLIDE 13

Partitioning

11 17 25

5 8

1 2 3 4 5 6 7 8 9 10

14

11 12 13 14 15 16

20 22

17 18 19 20 21 22 23 24

28 31

25 26 27 28 29 30 31 32

Partition 1 Partition 2

2 partitions

Workers see only one partition (at a time)

SLIDE 14

11 17 25

5 8

1 2 3 4 5 6 7 8 9 10

14

11 12 13 14 15 16

20 22

17 18 19 20 21 22 23 24

28 31

25 26 27 28 29 30 31 32

Partitioning

Part. 1
Part. 2
Part. 3
Part. 4
Part. 5
Part. 6

6 partitions

2nd level of B-tree may be used to split into many partitions

SLIDE 15

Partitioning

Server will normally request 100 partitions per worker thread
“Fast” workers may process more partitions than “slow” workers
Partitions of more equal size
When finished with one partition, a worker may be automatically attached to a new partition.

SLIDE 16

Parallel Query Sort

SELECT col1, col2, col3 FROM t1 ORDER BY 1,2;

1. Parallel data access (table scan or index)
2. Parallel order by of the data handled by each worker
3. Final merge sort of the results and return to client.

Merge Sort

Thread 1: Scan, Sort Thread 2: Scan, Sort Thread 3: Scan, Sort Thread 4: Scan, Sort Thread 5: Scan, Sort Thread 6: Scan, Sort Thread 7: Scan, Sort . . . Thread 64: Scan, Sort

Parallel threads run local sort

SQL Client

SLIDE 17

Parallel Query GROUP BY

SELECT col1, col2, SUM(col3) FROM t1 GROUP BY 1,2;

1. Parallel data access (table scan or index)
2. Parallel group by of the data handled by each worker
3. Final merge of the local group by and return results

DISTINCT operation will be similar to GROUP BY.

Merge Groups

Thread 1: Scan, Group Thread 2: Scan, Group Thread 3: Scan, Group Thread 4: Scan, Group Thread 5: Scan, Group Thread 6: Scan, Group Thread 7: Scan, Group . . . Thread 64: Scan, Group

Parallel threads run local group

SQL Client

SLIDE 18

Parallel Query Nested-Loops JOIN

SELECT * FROM t1 JOIN t3 ON t1.id = t3. id;

1. Parallel data access (table scan or index) of driving table
2. Parallel join of the local data handled by each worker
3. Final merge of the and return to client

Merge

Thread 1: Scan, Join Thread 2: Scan, Join Thread 3: Scan, Join Thread 4: Scan, Join Thread 5: Scan, Join Thread 6: Scan, Join Thread 7: Scan, Join . . . Thread 64: Scan, Join

Parallel scan and join

SQL Client

SLIDE 19

Parallel Query Usage

System variables to control parallel query

To enable parallel execution for a session: set max_parallel_degree = n Maximum n worker threads will be used. MySQL may still decide to not use parallelization. If so, parallel execution may be forced with set force_parallel_mode = on

SLIDE 20

Parallel Query Usage

Hints to control parallel query

To force parallel query execution for a single query: SELECT /+ PARALLEL() / * FROM ... To force the use of a specific number of worker threads, n : SELECT /+ PARALLEL(n) / * FROM ... More hints are on the way.

SLIDE 21

Agenda

What is Parallel Query?
Parallel Query Design
How to use Parallel Query
Parallel Query Performance
Future Work

SLIDE 22

Parallel Query Resource control

Control number of parallel workers

To control worker threads for parallel query: set max_parallel_workers = n Only maximum n worker threads will be allowed in total. MySQL will decide to whether to use parallel query and how many workers for current query. total number_of_workers <= max_parallel_workers; actual_workers_number <= max_data_partitions; actual_workers_number <= max_parallel_degree;

SLIDE 23

Parallel Query Resource control

Monitor the running status of parallel query

************* 1. row * THREAD_ID: 94 PARENT_THREAD_ID: 0 PARALLEL_TYPE: GATHER EVENT_ID: 11 END_EVENT_ID: NULL EVENT_NAME: parallel_query STATE: COMPLETED PLANNED_DOP: 16 ACTUAL_DOP: 16 NUMBER_OF_PARTITIONS: 36 PARTITIONED_OBJECT: t1 ROWS_SCANED: 10189 ROWS_SENT: 77 ROWS_SORTED: 0 EXECUTION_TIME: 435373818 NESTING_EVENT_ID: 9 NESTING_EVENT_TYPE: STATEMENT * 2. row * THREAD_ID: 95 PARENT_THREAD_ID: 94 PARALLEL_TYPE: WORKER EVENT_ID: 2 END_EVENT_ID: NULL EVENT_NAME: parallel_query STATE: COMPLETED PLANNED_DOP: 0 ACTUAL_DOP: 0 NUMBER_OF_PARTITIONS: 0 PARTITIONED_OBJECT: ROWS_SCANED: 718 ROWS_SENT: 8 ROWS_SORTED: 0 EXECUTION_TIME: 423644016 NESTING_EVENT_ID: 1 NESTING_EVENT_TYPE: STATEMENT 3. row***********

mysql> select * from performance_schema.events_parallel_query_current\G

SLIDE 24

Parallel Query Resource control

Constrain total memory

To control maximum memory consumed by parallel query: set query_memory_hard_limit = n set query_memory_soft_limit = n If the total memory usage is above query_memory_hard_limit, the parallel query will be aborted and an error will be reported. If the total memory usage is above query_memory_soft_limit, parallel query execution will not be chosen. The following memory buffers we considered mainly

Internal temporary table
Sort buffer
Join cache

SLIDE 25

Parallel Query Resource control

Constrain total memory - example

mysql> set global query_memory_soft_limit = 99 * 1024 * 1024; Query OK, 0 rows affected (0.00 sec) mysql> set join_buffer_size = 100 * 1024 * 1024; Query OK, 0 rows affected (0.00 sec) mysql> select x.b from t1 x, t1 y where x.a = y.a; … mysql> show warnings; +---------+-------+----------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+-------+----------------------------------------------------------------------------------------------------------------+ | Warning | 13250 | Could not choose parallel plan due to total query memory temporarily overflow, consider increasing @@query_memory_soft_limit value. | +---------+-------+----------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) n set, 1 warning (0.00 sec)

SLIDE 26

Parallel Query Resource control

Control total memory - example

mysql> set global query_memory_hard_limit = 99 * 1024 * 1024; Query OK, 0 rows affected (0.00 sec) mysql> set join_buffer_size=100 * 1024 * 1024; Query OK, 0 rows affected (0.00 sec) mysql> select x.b from t1 x, t1 y where x.a = y.a; ERROR 13249 (HY000): Failed to allocate memory for query due to overflow, consider increasing @@query_memory_hard_limit value.

SLIDE 27

Parallel Query Resource control

Monitor memory status

SHOW GLOBAL STATUS WHERE variable_name = ‘total_running_parallel_workers’; SHOW GLOBAL STATUS WHERE variable_name = ‘total_used_query_memory';

SLIDE 28

Parallel Query Explain

Simple Join

mysql> EXPLAIN SELECT SUM(l_quantity) FROM lineitem where l_returnflag = 'A'; +----+-------------+-----------+------------+------+---------------+------+-----

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+-----

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

| 1 | SIMPLE | <gather2> | NULL | ALL | NULL | NULL | NULL | NULL | 5938499 | 10.00 | NULL | | 2 | SIMPLE | lineitem | NULL | ALL | NULL | NULL | NULL | NULL | 742312 | 10.00 | Parallel scan (8 workers); Using where | +----+-------------+-----------+------------+------+---------------+------+-----

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

2 rows in set, 1 warning (0.00 sec)

SLIDE 29

Parallel Query Explain

Join with derived table

mysql> EXPLAIN SELECT * FROM t, (SELECT b FROM tt GROUP BY b) AS tt; +----+-------------+------------+------------+------+---------------+------+---------+------ +------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------ +------+----------+---------------------------------------+ | 1 | SIMPLE | <gather2> | NULL | ALL | NULL | NULL | NULL | NULL | 9247 | 100.00 | NULL | | 2 | PRIMARY | t | NULL | ALL | NULL | NULL | NULL | NULL | 2311 | 100.00 | Parallel scan (4 workers) | | 2 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using join buffer (Block Nested Loop) | | 3 | DERIVED | <gather4> | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using shared temporary | | 4 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Parallel scan (4 workers); Using temporary | 5 rows in set, 1 warning (0.00 sec)

SLIDE 30

Parallel Query Explain

Join with subquery

mysql> explain select ps_partkey, sum(ps_supplycost * ps_availqty) as value from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'UNITED STATES’ group by ps_partkey having sum(ps_supplycost * ps_availqty) > (select sum(ps_supplycost * ps_availqty) * 0.0000100000 from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'UNITED STATES’ ) order by value desc; (Q11)

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

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

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

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

SLIDE 31

Agenda

What is Parallel Query?
Parallel Query Design
How to use Parallel Query
Parallel Query Performance
Future Work

SLIDE 32

Parallel Query Performance

Parallel Query delivers near-perfect linear acceleration for DBT3 Query 6:

select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date '1994-01-01’ and l_shipdate < date '1995-01-01’ and l_discount between 0.06 - 0.01 and 0.06 + 0.01 and l_quantity < 24

Tested at 30, 60, 120, and 240 million rows. Examples: 89 seconds to 3.4 seconds. 177 seconds to 6.3 seconds.

SLIDE 33

Parallel Query Performance

DBT3 Query 1:

Scales 30x with 32 worker

threads

Close to linear scalability

(dashed line)

SLIDE 34

Why do users care about linear scalability?

Users care about

Business growth. DB must

deliver stable performance as business grows

Faster decisions. Faster

analysis driving faster action

Faster: 85 seconds to 6 seconds 22.6 seconds 2x data size - 21.6 seconds 4x data size - 21.6 seconds 22.6 21.6 21.6

SLIDE 35

Linear scalability also for join (DBT3 Q12)

SLIDE 36

DBT3 Query Performance

0x 5x 10x 15x 20x 25x 30x 35x Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21 Q22

Speedup

Speedup with 32 worker threads on 32 cores, DBT3 scale factor 1

SLIDE 37

Query Performance

Good scalability for table scans and joins on primary key (25x – 30x)
Moderate scalability on secondary index scans and joins on secondary index (10x – 18x)
Known issue that concurrent non-covering index scans does not scale:

http://smalldatum.blogspot.com/2014/10/low-concurrency-performance-for-range.html (Mark Callaghan)

Limited scalability for aggregation queries with many groups (6x for Q10)
Poor scalability for queries with subqueries (2x – 7x)
Currently not supported:
Join queries where first large table in join order is accessed by ref access
Join queries with subqueries in WHERE clause

Current scalability issues

SLIDE 38

Moderate Scalability with Secondary Index Scans

DBT3 Query 14:

SELECT 100.00 * SUM(CASE …) / SUM(l_extendedprice * (1 - l_discount)) AS promo_revenue FROM lineitem, part WHERE l_partkey = p_partkey AND l_shipdate >= '1994-11-01’ AND l_shipdate < DATE_ADD('1994-11-01’, INTERVAL '1' MONTH);

Scalability: 8 workers: 6.4x 32 workers: 15.5x Note: innodb_adaptive_hash_index = off

SLIDE 39

Subquery Support

1. Parallel execution of subqueries

Done

2. Distribute result of non-correlated scalar subqueries to workers

TBD

3. Let workers share result of materialized subqueries

TBD

4. Execution of correlated subqueries by workers

TBD

Work in progress

SLIDE 40

Join queries

Supported: Not yet supported:

Work in progress

Parallel Access Normal Access Nested Loop Join Gather

t1 t2

Parallel Access Normal Access BNL Join Gather

t1 t2

JOIN BUFFER

Parallel Access Normal Access Indexed Nested Loop Join Gather

t1 t2

SLIDE 41

Agenda

What is Parallel Query?
Parallel Query Design
How to use Parallel Query
Parallel Query Performance
Future Work

SLIDE 42

Parallel Query – Current Limitations

Parallel query currently only supports SELECT queries
Some limitations on support for join queries and subqueries
Parallel query does not currently execute in parallel:
Recursive CTEs
Windows functions
WITH ROLLUP
Procedures
JSON
GIS
UDFs
Full text indexes
SELECT … FOR UPDATE etc.
SERIALIZABLE isolation level

SLIDE 43

Parallel Query – Future Work

1. Support all query plans with nested-loop join
2. Full support for subqueries
3. Performance optimizations/tuning of existing

functionality

4. Improved diagnostics support (performance

schema)

5. Support more operations in workers (JSON,

GIS, Window functions)

6. Repartition operators (broadcast/redistribute,

multiple gather operations)

7. Parallel hash join
8. Modify query plans to support more efficient

parallelization

9. Rewrite optimizer to take parallelization into

account

SLIDE 44