vertica性能优化汇总

查看系统参数
SELECT
node_name,
parameter_name,
current_value,
default_value
FROM configuration_parameters
WHERE current_value <> default_value order by 2, 1;

-----------------------------------------------------------------
node_name | parameter_name | current_value | default_value
-----------+----------------------+---------------+---------------
ALL | DMLTargetDirect | 0 | 1
ALL | JavaBinaryForUDx | /usr/bin/java |
ALL | MaxClientSessions | 250 | 50
ALL | MaxParsedQuerySizeMB | 10240 | 1024
ALL | MergeOutInterval | 250 | 600
ALL | MoveOutInterval | 500 | 300
(6 rows)

查看队列设置，默认最多4个
SELECT
name,
memorysize,
maxmemorysize,
priority,
runtimepriority,
QUEUETIMEOUT,/*等待超时：时:分*/
runtimeprioritythreshold,
runtimecap,
maxconcurrency /*最大并行*/
FROM V_CATALOG.RESOURCE_POOLS
WHERE name='general';

-----------------------------------------------------------------
name | memorysize | maxmemorysize | priority | runtimepriority | QUEUETIMEOUT | runtimeprioritythreshold | runtimecap | maxconcurrency
---------+------------+---------------+----------+-----------------+--------------+--------------------------+------------+----------------
general | | Special: 90% | 0 | MEDIUM | 00:05 | 2 | | 4

修改资源池的参数
alter resource pool general MAXMEMORYSIZE '80%';

查看当前资源队列
select *from resource_queues;

查看当前正在执行，以及正在等待的SQL
select
substr(current_statement, 1, 200) as stmt,
count(1)
from sessions
where not current_statement is null
and (transaction_id, statement_id)<>(current_trans_id(), current_statement())
group by 1;

查看当前值正在执行的会话：
select * from query_requests where is_executing='true'
根据session_id关闭指定会话：
select CLOSE_SESSION ( 'verdb-1091.eastmone-143932:0x35909')
COMMIT;

查看当前每个节点的资源使用情况
select
node_name,
pool_name,
running_query_count as running,
floor(memory_size_kb /1000000) as memory_gb,
floor(memory_inuse_kb/1000000) as memory_inuse_gb,
floor(query_budget_kb/1000000) as query_budget_gb
from resource_pool_status
where running_query_count > 0
order by pool_name, node_name;

提高SQL大小限制
SELECT get_config_parameter('MaxParsedQuerySizeMB');

SELECT set_config_parameter('MaxParsedQuerySizeMB', 10240);
COMMIT;

修改最大会话数：
SELECT GET_CONFIG_PARAMETER ('MaxClientSessions');

SELECT SET_CONFIG_PARAMETER ('MaxClientSessions', 100);
COMMIT;

优化WOS和ROS
SELECT GET_CONFIG_PARAMETER('MoveOutInterval');
SELECT GET_CONFIG_PARAMETER('MergeOutInterval');

SELECT SET_CONFIG_PARAMETER('MoveOutInterval', 60);
SELECT SET_CONFIG_PARAMETER('MergeOutInterval', 30);
COMMIT;

Vertica 表发生死锁后, 通过下面3个查询即可解锁.

--步骤1: 找到被锁表的 transaction_id
select transaction_id, t.* from v_monitor.locks t;

--步骤2:根据 transaction_id 获得 session_id, 这一步需要登录账号有较高的权限, 否则会差不到对应的session
select session_id from v_monitor.sessions t where transaction_id in (45035996274476153);

--步骤3: 根据session_id, 关闭session
select close_session('v_bigdata_node0001-52084:0xf271e');

commit;