Oracle Custom SQL queries - execution time (Cache_masage=X)

Troubleshooting
1

Hi CheckMK-ians,

Please help me out, because i dont see where to set the right config.

So i have a bunch of custom sql queries running against an oracle DB on linux. What i want to achieve is to run all the custom queries every 300 sec/5min. Within my mk_oracle.cfg file i have set the following:

...
# Cache time (i.e. check interval) for async sections
CACHE_MAXAGE=600

#OUR-Custom-SQL-Section
SQLS_SECTIONS="test,test1"
SQLS_DIR="/etc/check_mk/mk_oracle.d"
SQLS_SIDS=DB

#--------------------------------------
#
#
test () {
    SQLS_MAX_CACHED_AGE=300
    SQLS_SQL=test.sql
}

test1 () {
    SQLS_MAX_CACHED_AGE=300
    SQLS_SQL=test1.sql
}
...

This should ensure the queries are executed every 300 sec. But talking and cross checking with my oracle department they see the queries are executed every 2 min.

So i have been investigation further: reading the link.

So if i move the mk_oracle plugin from the folder 60 to 300 i have the right execution time but then also all the normal monitoring of oracle is executed only every 5 min. That is not what i want, id like only the custom queries to be executed every 5 min.

mv mk oracle from 
/usr/lib/check_mk_agent/plugins/60

to here..
/usr/lib/check_mk_agent/plugins/300

Just to complete the informations all the custom queries are distributed to the checkmk agents via file deploy.

Additional request: And maybe someone can tell me where can i definitely check or see when or how often the query is executed i.e. wihtin a 10 min time frame.

Thanks for your help.

1 Like
2

Hi Miha

The parameter CACHE_MAXAGE=600 is only used for the mk_oracle’s own internal sections that are listed in ASYNC_SECTIONS. If you want to run one or more of the internal sections asynchronously, you can move them from SYNC_SECTIONS to ASYNC_SECTIONS. Keep in mind that you can only configure one interval for all internal sections.

For the custom queries, you must use the variable SQLS_MAX_CACHE_AGE=300. When set, this value will be used as the default for all custom queries. If you require a different interval for a specific check, you can override it by setting, for example, SQLS_MAX_CACHE_AGE=600 in that individual check section.

I would leave the mk_oracle plugin to the standard plugin folder:

/usr/lib/check_mk_agent/plugins

When you place the plugin in ../60 and MAX_CACHE_AGE is set to 60 then the custom check will run only all 2 minutes. This happens because the agent runs mk_oracle in the background and stores its output in a cache file. During the next run, the agent first outputs the content of this cache file and then checks its age. Since the cache file is at this moment usually younger than 60 seconds, it is not refreshed at that point. It will only be updated during the following run, which effectively results in a query interval of about 2 minutes.

Workaround: Use always a slightly smaller AGE value than the targeted interval. This takes into account the delay caused by the asynchronous execution and ensures that the cache file is reliably refreshed during each check cycle. Depending on the query runtime, the AGE value must be reduced accordingly to compensate the runtime.

3 
#OUR-Custom-SQL-Section

SQLS_MAX_CACHE_AGE=600                # Default for all custom queries

SQLS_SECTIONS="test,test1"
SQLS_DIR="/etc/check_mk/mk_oracle.d"
SQLS_SIDS=DB
4

Hi Lars,

Thanks for looking into it and again explaining. As i wrote the first sentence of this post i told myself the failing point straight away. After reading your post 5x i saw that that my settings are

test () {
    SQLS_MAX_CACHED_AGE=300
    SQLS_SQL=test.sql
}

instead of this one.

SQLS_MAX_CACHE_AGE=300

If you dont see the difference as i didnt, its the word CACHE vs. CACHED … and i have now f…. idea where the D came frome. Now it works like a charme.

One more important info, if some is reading this later .. if you don’t see the following information in the service overview of that certain custom sql query service … than the setting was not read.

slika

Cache generated 4 minutes 36 seconds ago, cache interval: 5 minutes 0 seconds, elapsed cache lifespan: 91.92%

All the best and happy Monitoring. MK.

1 Like