首页 > 代码库 > Vertica参数的设置

Vertica参数的设置

2024-11-15 08:40:02   203人阅读

注：只是经验值设定，仅供参考。
RHEL 6.2 CPU 24  Memory 128G  8节点
1.keep more event logs for dc_tuple_mover_events.
1 select SET_DATA_COLLECTOR_POLICY(‘TupleMoverEvents‘, ‘1000‘, ‘100000‘);
default: 1000KB kept in memory, 10000KB kept on disk.

2.keep more event logs for dc_errors.
1 select SET_DATA_COLLECTOR_POLICY(‘Errors‘, ‘1000‘, ‘100000‘); 
default: 1000KB kept in memory, 10000KB kept on disk.

3.PARAMETER MaxClientSessions: for concurrent queries and data loading jobs
1 select set_config_parameter(‘MaxClientSessions‘, 500);
4.avoid "too many ROS container..."
4.1
1 select set_config_parameter(‘ActivePartitionCount‘, 1);
-- default: 1
4.2
1 select set_config_parameter(‘MoveOutInterval‘, 1800); 
-- default: 300
4.3
1 select set_config_parameter(‘MoveOutMaxAgeTime‘, 1800);
-- default: 1800
4.4
1 select set_config_parameter(‘MoveOutSizePct‘, 95); 
-- default: 0
4.5
1 select set_config_parameter(‘MergeOutInterval‘, 300); 
--default: 600
4.6
1 select set_config_parameter(‘ContainersPerProjectionLimit‘, 102400);
--default: 1024

5.for extension
1 select ENABLE_LOCAL_SEGMENTS();
---- 24->32, default: 4
1 select SET_SCALING_FACTOR(4);

6.for loading
1 select set_config_parameter(‘MaxDesiredEEBlockSize‘,33554432);
default: 8388608.
Maximum desired size of an EE block (used to move tuples between operators), actual block size be larger (must have capacity for at least 2 rows)
7.
1 SELECT SET_CONFIG_PARAMETER(‘ParallelizeLocalSegmentLoad‘, ‘1‘);
default: 1 .If true use a DT per local segment, even when sorting

8.RESOURCE POOL general:
1 alter resource pool general priority 2 plannedconcurrency 12 maxconcurrency 10 queuetimeout 600;

9.RESOURCE POOL load_pool:
1 alter resource pool load_pool priority 2 runtimepriority MEDIUM plannedconcurrency 12 maxconcurrency 5 queuetimeout NONE;
-- set session resource_pool=load_pool;

10.RESOURCE POOL app_pool:
1
2
alter resource pool app_pool priority 2 plannedconcurrency 12 maxconcurrency 10 queuetimeout 600;
alter resource pool app_pool queuetimeout 1200;
11.RESOURCE POOL web_pool:
1
2
alter resource pool web_pool priority 100 memorysize ‘5G‘ maxmemorysize ‘10G‘ plannedconcurrency 12 maxconcurrency 10 queuetimeout 600;
alter resource pool web_pool priority 100 memorysize ‘5G‘ maxmemorysize ‘10G‘;
12.RESOURCE POOL wosdata
1 alter resource pool wosdata memorysize ‘24G‘ maxmemorysize ‘24G‘;
13.alter resource pool tm memorysize default maxconcurrency 4;
1 alter resource pool tm memorysize ‘1G‘ plannedconcurrency 3 maxconcurrency 4;

14.view modified parameters
1 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       | ContainersPerProjectionLimit | 102400        | 1024
ALL       | MaxClientSessions            | 500           | 50
ALL       | MaxDesiredEEBlockSize        | 33554432      | 8388608
ALL       | MergeOutInterval             | 300           | 600
ALL       | MoveOutInterval              | 1800          | 300
(5 rows)
*/
15.elastic_cluster
1 select * from elastic_cluster;
16.view user-defined pools
1
2
select * from resource_pools
  where name not in (select name from resource_pool_defaults);
/*
      pool_id      |   name    | is_internal | memorysize | maxmemorysize | executionparallelism | priority | runtimepriority | runtimeprioritythreshold | queuetimeout | plannedconcurrency | maxconcurrency | runtimecap | singleinitiator
-------------------+-----------+-------------+------------+---------------+----------------------+----------+-----------------+--------------------------+--------------+--------------------+----------------+------------+-----------------
      pool_id      |   name    | is_internal | memorysize | maxmemorysize | executionparallelism | priority | runtimepriority | runtimeprioritythreshold | queuetimeout | plannedconcurrency | maxconcurrency | runtimecap | singleinitiator
-------------------+-----------+-------------+------------+---------------+----------------------+----------+-----------------+--------------------------+--------------+--------------------+----------------+------------+-----------------
45035996517090874 | load_pool | f           | 0%         |               | AUTO                 |        2 | MEDIUM          |                        2 |           -1 | 12                 |              5 |            | f
45035997831587844 | app_pool  | f           | 0%         |               | AUTO                 |        2 | MEDIUM          |                        2 |          600 | 12                 |             10 |            | f
58546795771314766 | web_pool  | f           | 1G         | 5G            | AUTO                 |        5 | MEDIUM          |                        2 |          300 | 10                 |             12 |            | f
(3 rows)
*/
17.view the current config of the modified pools
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select c.* from resource_pools c, resource_pool_defaults d
where c.name=d.name
   and (
     c.memorysize::varchar <> d.memorysize::varchar
     or c.maxmemorysize::varchar <> d.maxmemorysize::varchar
     or c.executionparallelism::varchar <> d.executionparallelism::varchar
     or c.priority::varchar <> d.priority::varchar
     or c.runtimepriority::varchar <> d.runtimepriority::varchar
     or c.runtimeprioritythreshold::varchar <> d.runtimeprioritythreshold::varchar
     or c.queuetimeout::varchar <> d.queuetimeout::varchar
     or c.runtimecap::varchar <> d.runtimecap::varchar
     or c.plannedconcurrency::varchar <> d.plannedconcurrency::varchar
     or c.maxconcurrency::varchar <> d.maxconcurrency::varchar
     or c.singleinitiator::varchar <> d.singleinitiator::varchar
   );
/*
      pool_id      |  name   | is_internal | memorysize | maxmemorysize | executionparallelism | priority | runtimepriority | runtimeprioritythreshold | queuetimeout | plannedconcurrency | maxconcurrency | runtimecap | singleinitiator
-------------------+---------+-------------+------------+---------------+----------------------+----------+-----------------+--------------------------+--------------+--------------------+----------------+------------+-----------------
45035996273718910 | general | t           |            | Special: 95%  | AUTO                 |        2 | MEDIUM          |                        2 |          600 | 12                 |             10 |            | f
45035996273718920 | refresh | t           | 4G         |               | AUTO                 |       10 | HIGH            |                       60 |          300 | 8                  |              8 |            | t
45035996273718918 | tm      | t           | 1G         |               | AUTO                 |      105 | MEDIUM          |                       60 |          300 | 3                  |              4 |            | t
45035996273718916 | wosdata | t           | 24G        | 24G           |                      |          |                 |                          |              | AUTO               |                |            |
(4 rows)
*/
18.view the default config of the modified pools
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select d.* from resource_pools c, resource_pool_defaults d
where c.name=d.name
   and (
     c.memorysize::varchar <> d.memorysize::varchar
     or c.maxmemorysize::varchar <> d.maxmemorysize::varchar
     or c.executionparallelism::varchar <> d.executionparallelism::varchar
     or c.priority::varchar <> d.priority::varchar
     or c.runtimepriority::varchar <> d.runtimepriority::varchar
     or c.runtimeprioritythreshold::varchar <> d.runtimeprioritythreshold::varchar
     or c.queuetimeout::varchar <> d.queuetimeout::varchar
     or c.runtimecap::varchar <> d.runtimecap::varchar
     or c.plannedconcurrency::varchar <> d.plannedconcurrency::varchar
     or c.maxconcurrency::varchar <> d.maxconcurrency::varchar
     or c.singleinitiator::varchar <> d.singleinitiator::varchar
   );
/*
      pool_id      |  name   | memorysize | maxmemorysize | executionparallelism | priority | runtimepriority | runtimeprioritythreshold | queuetimeout | runtimecap | plannedconcurrency | maxconcurrency | singleinitiator
-------------------+---------+------------+---------------+----------------------+----------+-----------------+--------------------------+--------------+------------+--------------------+----------------+-----------------
45035996273718910 | general |            | Special: 95%  | AUTO                 |        0 | MEDIUM          |                        2 |          300 |            | AUTO               |                | f
45035996273718920 | refresh | 0%         |               | AUTO                 |      -10 | MEDIUM          |                       60 |          300 |            | AUTO               |                | t
45035996273718918 | tm      | 200M       |               | AUTO                 |      105 | MEDIUM          |                       60 |          300 |            | AUTO               |              3 | t
45035996273718916 | wosdata | 0%         | 2G            |                      |          |                 |                          |              |            | AUTO               |                |
(4 rows)

*/

Vertica参数的设置


联系
我们