0

Please help,how to trigger Mysql parallel query function to start? MySQL version is 8.0.19.

mysql> show variables like 'innodb_parallel_read_threads';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_parallel_read_threads | 4     |
+------------------------------+-------+
1 row in set (0.00 sec)

Judging from the execution plan, although the cost value is high, it does not trigger parallel queries.

mysql> explain analyze SELECT count(a.name) FROM b,a WHERE b.id = a.id AND a.id < 10000\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(a.`name`)  (actual time=79199.970..79199.971 rows=1 loops=1)
    -> Inner hash join (b.id = a.id)  (cost=29804029261222.55 rows=29803521726988) (actual time=52129.791..79198.823 rows=9999 loops=1)
        -> Table scan on b  (cost=31.81 rows=18758239) (actual time=0.385..19630.712 rows=20000000 loops=1)
        -> Hash
            -> Filter: (a.id < 10000)  (cost=4909601.51 rows=15888229) (actual time=2.266..52117.583 rows=9999 loops=1)
                -> Table scan on a  (cost=4909601.51 rows=47669458) (actual time=2.262..48985.413 rows=50000000 loops=1)

1 row in set (1 min 19.25 sec)
  • please try to run select without WHERE conditions – Nikita Feb 22 at 9:31
  • mysql> set local innodb_parallel_read_threads=4; Query OK, 0 rows affected (0.00 sec) mysql> explain analyze select name from para_rel\G *************************** 1. row *************************** EXPLAIN: -> Table scan on para_rel (cost=4909601.52 rows=47669458) (actual time=0.094..54744.867 rows=50000000 loops=1) 1 row in set (58.01 sec) The single select also did not find the descriptive information that triggered the parallel query in the execution plan.@Nikita – HappyGuitarBoy Feb 22 at 10:27
  • 1
    I mean "SELECT COUNT(*) FROM b" for example. MySQL has very limited ability to perform parallel query execution. – Nikita Feb 22 at 12:22
  • @Nikita ` mysql> explain analyze SELECT COUNT(*) FROM para_rel\G *************************** 1. row *************************** EXPLAIN: -> Count rows in para_rel 1 row in set (3.36 sec) ` – HappyGuitarBoy Feb 22 at 12:42
  • Please provide SHOW CREATE TABLE, EXPLAIN FORMAT=JSON SELECT ... – Rick James Feb 28 at 1:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.