Emailing scheduled reports in .csv format with multiple tables / queries and some of them are ending up empty.

Products

• CA Performance Management - Usage and Administration

Issue/Introduction

Although the reports are in .csv format with multiple tables / queries, some of them are ending up empty.

Any way to prevent these reports sections to be empty?

Environment

CAPM 3.7.x

DX NetOps 20.x

Cause

We see this error in the DA karaf.log

ERROR: Insufficient resources to execute plan on pool scheduled_short_rib_processing [Timedout waiting for resource request: Request exceeds limits: Queries Exceeded: Requested = 1, Free = 0 (Limit = 4, Used = 4) (queueing threshold)

 

What this means is the pool is setup to only run 4 queries at once.  maxconcurrency = 4.  Each query can only run for runtimecap 30 secs (00:00:30).

We also set limits on how many resources each resource pool can use, this makes sure that no single vertica process can use up all of the memory.

Resolution

Depending on resources, and how much scheduled reporting you are doing, you may be able to increase the memory allocated to the pool as well as the max number of jobs that can run at once.

As your db admin user, log into vsql, and run this, to make short match the long scheduled pool settings. 

What this means is we're allocating an additional 2% of memory to this pool that ONLY this pool can use.

cd /opt/vertica/bin

./vsql -Udradmin -wdbpass (your password may vary)

 

alter resource pool scheduled_short_rib_processing memorysize '5%' maxmemorysize '15%';

 

alter resource pool scheduled_short_rib_processing maxconcurrency 6;

\q

 

Run your scheduled report again, and see if it works.  If not, you could increase the memorysize to 10% and see if that helps. 

Remember that any additional memory you set aside for this pool is not going to be available to other pools.

Additional Information

For any changes to be preserved across DA restarts, you would need to create a file and add the new settings in there.  If you find settings you like, you can... Create/Edit:

/opt/IMDataAggregator/apache-karaf-2.4.3/etc/com.ca.im.dm.core.database.dao.schema.vertica.impl.VerticaDatabaseResourcePoolImpl.scheduled_short_rib_processing.cfg

 

Add/Modify (these are based on previous/current responses - change to whatever you used):

maxConcurrency=6

queueTimeout=00:01:25

memorySize=5%

maxMemorySize=15%

 

For more details on resource pools, see techdoc:

https://knowledge.broadcom.com/external/article/206071/