Fighting HPE Vertica loads

A typical “just in time” scenario of the data warehouse looks like this: dozens of (ETL) sessions almost continuously capture data from sources and insert them into the storage. In parallel, many other (ELT) sessions track data flow, fill in a consolidated layer, and calculate units and storefronts. At the same time, users, BI and other systems perform queries on incoming primary and calculated data. All this porridge should be finely brewed within the data warehouse server, without brakes and gags, whatever the peak loads.

HPE Vertica has developed a special mechanism for scheduling server work under loads, called resource pools. The idea is that each server user works within the allocated resource pool, which regulates the priority of access to cluster resources, limits the competitiveness of query execution, and describes the rules for backing up and working with server memory.

By default, after installing the Vertica server on the created database, it looks like this:
')


On each server (node) of the cluster, the memory available to Vertica is designated as the General resource pool. For various needs of the server, service pools are automatically created, which cut off pieces of memory for themselves from General:

• WOS - for storing portioned data in the memory area
• TM - for background service process Tuple Mover, performing moveoute and mergeout operations
• Other service - Vertica has additional resource pools for performing cluster node recovery operations, building projections, performing service requests, etc.

By default, server users are running under the control of the General pool itself. You can see its current settings:

dbadmin=> SELECT * FROM resource_pools WHERE name = 'general'; -[ RECORD 1 ]------------+------------------ pool_id | 45035996273721212 name | general is_internal | t memorysize | maxmemorysize | 30G executionparallelism | AUTO priority | 0 runtimepriority | MEDIUM runtimeprioritythreshold | 2 queuetimeout | 00:05 plannedconcurrency | 10 maxconcurrency | 20 runtimecap | singleinitiator | f cpuaffinityset | cpuaffinitymode | ANY cascadeto | 

Decipher some options:

• memorysize - the size of the reserved pool memory, this size is cut off from the General pool, reducing its size of available memory, for General, respectively, is not specified.
• maxmemorysize is the maximum allowed size of the pool. In the case of using a pool of memory, more than the specified memorysize, the missing memory is temporarily taken from the General pool, if there is of course something to take.
• plannedconcurrency - a divider to set the size of the memory allocated for the session when the request is started in the pool, in this case for the pool General maxmemorysize: 30 GB to divide 10 plannedconcurrency = 3 GB of pool memory is allocated for each session at start, which is mildly fat.
• maxconcurrency - the maximum number of competitive sessions executed on a pool.

Such “default” settings actually provide a 100% guarantee of Vertica operation brakes:

• 10 sessions starts the execution of requests
• 3 GB of pool memory is allocated for each session, even if you don’t need that much
• If the sessions have not yet had time to work and the new sessions have launched more requests, then instead of the memory of 30 GB, they get a job in the swap.
• If a session has eaten all the memory of the pool in the course of work, then other sessions also end up in a swap.

Let's help the "grief":

 ALTER RESOURCE POOL general PLANNEDCONCURRENCY 60 MAXCONCURRENCY 10; 

Now each session at the start of the request in the pool is allocated 0.5 GB, and only 10 sessions can be executed at the same time. At the start of 10 sessions, 5 GB of pool memory will be consumed, another 25 GB will remain as a reserve for memory transfer to heavy requests and other resource pools.

I want to draw attention to the parameter MAXCONCURRENCY - the lower it is, the faster your requests will work. Each hardware has a limit of loads, above which everything “stands up”. The higher the competitiveness, the greater the load on the processors and disk arrays, the lower their speed. It is more efficient to execute 10 requests and then execute the following 10 requests from the queue than to try to simultaneously execute 20 requests. Naturally, MAXCONCURRENCY will primarily depend on the pool tasks set for solving problems and on the characteristics of the cluster's iron. Your task is to identify the limits and set it slightly below the limits, so that in case of peak loads the simultaneous execution of many heavy queries, the cluster suddenly does not slow down for all its users. .

So what about the pools? So far we have only set up General, however, keeping users in it is actually a bad practice. Let's make typical pools by user task groups:

 --    CREATE RESOURCE POOL writers MEMORYSIZE '2G' MAXMEMORYSIZE '10G' PLANNEDCONCURRENCY 10 MAXCONCURRENCY 10 PRIORITY -50 RUNTIMECAP '3 MINUTE' RUNTIMEPRIORITYTHRESHOLD 0; --      CREATE RESOURCE POOL slowly MEMORYSIZE '0%' MAXMEMORYSIZE '20G' MAXCONCURRENCY 7 RUNTIMEPRIORITY LOW QUEUETIMEOUT '15 MINUTE' RUNTIMEPRIORITYTHRESHOLD 0; --    CREATE RESOURCE POOL readers MEMORYSIZE '4G' MAXMEMORYSIZE '10G' PLANNEDCONCURRENCY 20 MAXCONCURRENCY 10 RUNTIMECAP '5 MINUTE' PRIORITY 50 RUNTIMEPRIORITYTHRESHOLD 3 CASCADE TO slowly; 

General now we have "lost":


What we did:

1. Squatting, reading and performing long, heavy queries were scattered in session groups.
2. The initial memory was allocated to pools of readers and writers so that for the majority of short requests, the memory was taken immediately from the pool reserved for the pool without swapping with General.
3. They did not allocate memory to the pool of heavy requests, all the same, their sessions will want a lot of memory and reserve for them, cutting off precious memory from General, it makes no sense, but this pool was allowed to take up to 20 GB of memory from General if necessary.
4. We set a low request for the pool of heavy requests to perform LOW.
5. For the pool of writers and readers, the priority of access to resources was set so that, by the priority of MEDIUM, the pool of writers was an outsider (-50 priority), the General pool was in the middle (zero priority) and the pool for readers was higher than these pools (50 priority).
6. Rationally set the values ​​of competition pools, where 10 queries of writers, 10 readers requests and 7 heavy queries can be executed simultaneously.
7. For situations where the user has launched a heavy query in the reader pool that lasts more than 5 minutes, they have indicated a cascade of such queries to a pool of heavy queries. This ensures that the readers pool does not ask for long queries on it that will clog competitor execution slots and slow down the execution queue of quick queries. For the pool of writers, we placed restrictions on the execution of queries for no more than 3 minutes, so that non-optimized insertion or update queries should get stuck.
8. For a pool of heavy requests, we set a waiting time in the queue for 15 minutes. If all competing slots in the pool are occupied, then after 15 minutes of waiting, queuing requests will be terminated with an error. This will make it clear to users that the server is not hanging, but simply everything is taken up within their pool.
9. For a pool of readers set the time to 3 seconds, during which the request after the launch has the highest priority on resources. This allows you to quickly perform short queries, freeing up space in the pool for other queries.

Now we assign the necessary pools to users and the job is done:

 ALTER USER user_writer RESOURCE POOL writers; ALTER USER user_reader RESOURCE POOL readers; ALTER USER user_analytical RESOURCE POOL slowly RUNTIMECAP '1 HOUR' TEMPSPACECAP '10G'; 

Here, in addition to the pool, we have restricted the user_analytical for requests for the execution time to one hour and allowed to use no more than 10 GB of space in the TEMP.

I want to note that all of the above in the article is an action “where to dig”, and not an example of “what to configure”. How many and what will be the resource pools, with what characteristics - all you have to decide. You can start small - create for example such 3 pools and continue to look at the cluster loads, gradually balancing the parameters of the pools and highlighting groups of users in different pools. The main thing to remember is that:

1. The General pool is a common source of memory for all pools and it is better not to use it directly for user sessions.
2. The fewer competitors, the lower the rate of iron subsidence in peak loads
3. The sum of the maximum allowed memory of all pools should not overlap the memory. General