Vertica parameter settings

RHEL 6.2 CPU 24 memory 128G 8 Node 1. Keep more Event Logs for dc_tuple_mover_events.

select SET_DATA_COLLECTOR_POLICY(‘TupleMoverEvents‘, ‘1000‘, ‘100000‘); 

Default: 1000kb kept in memory, then kb kept on disk.
2. Keep more Event Logs for dc_errors.

select SET_DATA_COLLECTOR_POLICY(‘Errors‘, ‘1000‘, ‘100000‘);  

Default: 1000kb kept in memory, then kb kept on disk.

3. Parameter maxclientsessions: for concurrent queries and data loading jobs

select set_config_parameter(‘MaxClientSessions‘, 500);

4. Avoid "Too using Ros container..." 4.1

select set_config_parameter(‘ActivePartitionCount‘, 1); 

-- Default: 14.2

select set_config_parameter(‘MoveOutInterval‘, 1800); 

-- Default: 3004.3

select set_config_parameter(‘MoveOutMaxAgeTime‘, 1800); 

-- Default: 18004.4

select set_config_parameter(‘MoveOutSizePct‘, 95); 

-- Default: 04.5

select set_config_parameter(‘MergeOutInterval‘, 300); 

-- Default: 6004.6

select set_config_parameter(‘ContainersPerProjectionLimit‘, 102400); 

-- Default: 1024

5. For extension

select ENABLE_LOCAL_SEGMENTS();

---- 24-> 32, default: 4

select SET_SCALING_FACTOR(4);

6. For loading

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.

SELECT SET_CONFIG_PARAMETER(‘ParallelizeLocalSegmentLoad‘, ‘1‘);

Default: 1. If true use a DT per local segment, even when sorting

8. Resource Pool general:

alter resource pool general priority 2 plannedconcurrency 12 maxconcurrency 10 queuetimeout 600;

9. resource pool load_pool:

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:

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:

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

alter resource pool wosdata memorysize ‘24G‘ maxmemorysize ‘24G‘;

13. Alter resource pool TM memorysize default maxconcurrency 4;

alter resource pool tm memorysize ‘1G‘ plannedconcurrency 3 maxconcurrency 4;

14. View modified Parameters

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 | maxclientsession| 500 | 50
All | maxdesiredeeblocksize | 33554432 | 8388608
All | mergeoutinterval | 300 | 600
All | moveoutinterval | 1800 | 300
(5 rows)
*/

select * from elastic_cluster;

select * from resource_pools  where name not in (select name from resource_pool_defaults);

/*
Pool_id | Name | is_internal | memorysize | maxmemorysize | executionparallelism | priority | runtimepriority | priority | queuetimeout | plannedconcurrency | maxconcurrency | runtimecap | singleinitiator
Certificate + ----------- + ------------- + ------------ + --------------- + certificate + ---------- + ----------------- + certificate + ------------ + certificate + ---------------- + -------------- + -----------------
Pool_id | Name | is_internal | memorysize | maxmemorysize | executionparallelism | priority | runtimepriority | priority | queuetimeout | plannedconcurrency | maxconcurrency | runtimecap | singleinitiator
Certificate + ----------- + ------------- + ------------ + --------------- + certificate + ---------- + ----------------- + certificate + ------------ + certificate + ---------------- + -------------- + -----------------
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)
*/

select c.* from resource_pools c, resource_pool_defaults dwhere 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 | priority | queuetimeout | plannedconcurrency | maxconcurrency | runtimecap | singleinitiator
Hour + --------- + ------------- + ------------ + --------------- + hour + ---------- + ----------------- + hour + -------------- + hour + ---------------- + ------------ + -----------------
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)
*/

select d.* from resource_pools c, resource_pool_defaults dwhere 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 | priority | queuetimeout | runtimecap | plannedconcurrency | maxconcurrency | singleinitiator
Certificate + --------- + ------------ + --------------- + certificate + ---------- + ----------------- + certificate + -------------- + ------------ + ------------------ + ---------------- + -----------------
45035996273718910 | General | Special: 95% | auto | 0 | medium | 2 | 300 | auto | f
45035996273718920 | refresh | 0% | auto |-10 | medium | 60 | 300 | auto | T
45035996273718918 | TM | 200 M | auto | 105 | medium | 60 | 300 | auto | 3 | T
45035996273718916 | wosdata | 0% | 2G | auto |
(4 rows)

*/