--ヒント句
select /*+ parallel(2) */
A.*
from tab1 A
inner join tab1 B
on A.col1 = B.col1
;
--セッション変更
ALTER SESSION FORCE PARALLEL QUERY;
select
A.*
from tab1 A
inner join tab1 B
on A.col1 = B.col1
;
--テーブル定義
create table tab21(col1 int) parallel 4;
insert into tab21 values(123);
commit;
select * from tab21;
--確認方法
select * from V$PQ_SESSTAT;
※MySQLは実装されていない
※8.0.14から一部のSQLについてパラレル実行可能
select @@innodb_parallel_read_threads;
https://www.ntt-tx.co.jp/column/postgresql_blog/20180222/
要件：
max_parallel_workers_per_gatherは0より大きい値に設定
dynamic_shared_memory_typeはnone以外
(9.6)
show max_worker_processes;
show max_parallel_workers_per_gather;
show parallel_tuple_cost;
show parallel_setup_cost;
show min_parallel_relation_size;
/usr/pgsql-9.6/bin/pgbench -i -s 200 test
set max_parallel_workers_per_gather = 1;
select count(*) from pgbench_accounts ;
explain analyze select count(aid) from pgbench_accounts;
(10)
show max_worker_processes;
show max_parallel_workers_per_gather;
show max_parallel_workers;
show parallel_tuple_cost;
show parallel_setup_cost;
show min_parallel_table_scan_size;
show min_parallel_index_scan_size;
/usr/pgsql-10/bin/pgbench -i -s 200 test
select count(*) from pgbench_accounts ;
explain analyze select count(aid) from pgbench_accounts;
(11)
show max_worker_processes;
show max_parallel_workers_per_gather;
set max_parallel_workers_per_gather = 4;
show max_parallel_maintenance_workers;
set max_parallel_maintenance_workers = 2;
show max_parallel_workers;
set max_parallel_workers = 8;
show parallel_tuple_cost;
set parallel_tuple_cost = 0;
show parallel_setup_cost;
set parallel_setup_cost = 0;
show force_parallel_mode;
set force_parallel_mode = 'on';
show min_parallel_index_scan_size;
set min_parallel_index_scan_size = 0;
show min_parallel_table_scan_size;
set min_parallel_table_scan_size = 0;
パラレル実行の効果確認
drop table tab1;
drop table tab2;
create table tab1(
col1 int,
col2 int
)
partition by list (mod(col2,10));
create table tab1p0 partition of tab1 for values in (0);
create table tab1p1 partition of tab1 for values in (1);
create table tab1p2 partition of tab1 for values in (2);
create table tab1p3 partition of tab1 for values in (3);
create table tab1p4 partition of tab1 for values in (4);
create table tab1p5 partition of tab1 for values in (5);
create table tab1p6 partition of tab1 for values in (6);
create table tab1p7 partition of tab1 for values in (7);
create table tab1p8 partition of tab1 for values in (8);
create table tab1p9 partition of tab1 for values in (9);
insert into tab1 select a,floor(random() * 100)+1 from generate_series(1,10000000) a;
create table tab2(
col1 int,col2 int
);
insert into tab2 select a,floor(random() * 100)+1 from generate_series(1,1000) a;
analyze tab1;
analyze tab2;
explain analyze
/*+ hashjoin (tab1 tab2) */
select tab1.col1
from tab1 inner join tab2
on tab1.col2 = tab2.col2
where tab2.col1 = 1
;
set max_parallel_workers_per_gather = 0;
時間: 944.598 ミリ秒
set max_parallel_workers_per_gather = 2;
時間: 326.052 ミリ秒
set max_parallel_workers_per_gather = 4;
時間: 321.990 ミリ秒
cost threshold for parallelism オプション
→クエリの並列プランが作成および実行されるときのしきい値
sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
sp_configure 'cost threshold for parallelism', 10;
GO
reconfigure;
GO
max degree of parallelism オプション
→並列プランで使用する CPU
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 2;
GO
RECONFIGURE WITH OVERRIDE;
GO
クエリ ステートメントに MAXDOP クエリ ヒントを指定して、
クエリの max degree of parallelism 値をオーバーライドできます
SELECT * FROM tab1
ORDER BY col1
OPTION (MAXDOP 4)
