# 连接数据库
$ ksql -U system -d fgedudb
# 查看当前并行DML相关参数
SHOW max_worker_processes;
SHOW max_parallel_workers_per_gather;
SHOW parallel_leader_participation;
SHOW work_mem;
# 修改并行DML相关参数
ALTER SYSTEM SET max_worker_processes = 8;
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET parallel_leader_participation = on;
ALTER SYSTEM SET work_mem = ’64MB’;
# 重启数据库使参数生效
$ systemctl restart kingbase
# 验证参数设置
$ ksql -U system -d fgedudb -c “SHOW max_worker_processes;”
$ ksql -U system -d fgedudb -c “SHOW max_parallel_workers_per_gather;”
$ ksql -U system -d fgedudb -c “SHOW parallel_leader_participation;”
$ ksql -U system -d fgedudb -c “SHOW work_mem;”

# 连接数据库
$ ksql -U fgedu -d fgedudb
# 启用并行DML
SET parallel_launch_cost = 0;
SET parallel_setup_cost = 0;
SET min_parallel_table_scan_size = 0;
SET min_parallel_index_scan_size = 0;
# 验证并行DML设置
SHOW parallel_launch_cost;
SHOW parallel_setup_cost;
SHOW min_parallel_table_scan_size;
SHOW min_parallel_index_scan_size;

# 连接数据库
$ ksql -U fgedu -d fgedudb
# 创建测试表
CREATE TABLE fgedu_parallel_insert (
id INTEGER PRIMARY KEY,
name VARCHAR(50),
age INTEGER,
department_id INTEGER,
salary NUMERIC(10,2)
);
# 启用并行DML
SET max_parallel_workers_per_gather = 4;
# 并行插入数据
EXPLAIN ANALYZE INSERT INTO fgedu_parallel_insert
SELECT
generate_series(1, 1000000),
‘Name ‘ || generate_series(1, 1000000),
floor(random() * 60) + 20,
floor(random() * 10) + 1,
floor(random() * 10000) + 3000;
# 查看执行计划，确认使用了并行执行

# 连接数据库
$ ksql -U fgedu -d fgedudb
# 启用并行DML
SET max_parallel_workers_per_gather = 4;
# 并行更新数据
EXPLAIN ANALYZE UPDATE fgedu_parallel_insert
SET salary = salary * 1.1
WHERE department_id = 1;
# 查看执行计划，确认使用了并行执行

# 连接数据库
$ ksql -U fgedu -d fgedudb
# 启用并行DML
SET max_parallel_workers_per_gather = 4;
# 并行删除数据
EXPLAIN ANALYZE DELETE FROM fgedu_parallel_insert
WHERE department_id = 1;
# 查看执行计划，确认使用了并行执行

# 连接数据库
$ ksql -U fgedu -d fgedudb
# 创建源表
CREATE TABLE fgedu_source_table (
id INTEGER PRIMARY KEY,
name VARCHAR(50),
age INTEGER,
department_id INTEGER,更多视频教程www.fgedu.net.cn
salary NUMERIC(10,2)
);
# 插入源数据
INSERT INTO fgedu_source_table
SELECT
generate_series(1, 100000),
‘Name ‘ || generate_series(1, 100000),
floor(random() * 60) + 20,
floor(random() * 10) + 1,
floor(random() * 10000) + 3000;
# 启用并行DML
SET max_parallel_workers_per_gather = 4;
# 并行MERGE数据
EXPLAIN ANALYZE MERGE INTO fgedu_parallel_insert t
USING fgedu_source_table s
ON t.id = s.id
WHEN MATCHED THEN
UPDATE SET salary = s.salary
WHEN NOT MATCHED THEN
INSERT (id, name, age, department_id, salary)
VALUES (s.id, s.name, s.age, s.department_id, s.salary);
# 查看执行计划，确认使用了并行执行

# 连接数据库
$ ksql -U system -d fgedudb
# 查看并行DML执行情况
SELECT
pid,
usename,
application_name,
state,
query
FROM pg_stat_activity
WHERE query LIKE ‘%INSERT%’ OR query LIKE ‘%UPDATE%’ OR query LIKE ‘%DELETE%’ OR query LIKE ‘%MERGE%’;
# 查看并行工作进程
SELECT
pid,
usename,
application_name,
state,
query
FROM pg_stat_activity
WHERE backend_type = ‘parallel worker’;
# 查看系统负载
$ top
# 查看I/O性能
$ iostat -x

# 连接数据库
$ ksql -U system -d fgedudb
# 查看查询性能统计信息
SELECT
queryid,
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
WHERE query LIKE ‘%INSERT%’ OR query LIKE ‘%UPDATE%’ OR query LIKE ‘%DELETE%’ OR query LIKE ‘%MERGE%’
ORDER BY total_exec_time DESC
LIMIT 10;
# 查看系统资源使用情况
SELECT
name,
current_setting(name),
source
FROM pg_settings
WHERE name IN (
‘max_worker_processes’,
‘max_parallel_workers_per_gather’,
‘parallel_leader_participation’,
‘work_mem’
);