Increase the OpenAPI MaxTopLimit from 20,000 to 200,000
We've been using the OpenAPI more now and have are being limited by the TopRowLimit. We've observed that with queries using the Expand option >100,000 rows are returned <120 seconds and queries with ~15,000 complete in <6 seconds. Because of these results we feel that 200,000 should work fine in our environment.
We don't have testing results for changes to this setting as it relates to the various possible environments that are possible for CA Performance Manager. The query plays a role in how much data it's trying to process as well which exponentially expands the testing that would be required due to the vast variety of possible queries and thus query result sizes that could be seen.
Without testing results to rely on it does come down to a small amount of trial and error.
Engineering notes that the OpenAPI has it's own resource pool that has a 2 minute runtime cap. Any query that takes more than 2 minutes will be killed via Vertica. We can try increasing the value and testing how long it takes at each level.
They recommend that we only go as high as 1 minute 30 seconds, leaving a 30 second buffer to handle larger requests that would come in from time to time.
We can also enable some of the OpenAPI debugging to get an idea of the length of time it's taking to run the queries. How to do so and how to review the data is documented in the Wiki Docs for CAPM here: