IRDB Setup Recommendations

11Aug2016 John Sullivan wrote->

Hi Charles,

I wanted to paste in the irdb.ini options, that could be used to optimize your IRDB configuration. My recommendations appear after. Give me a call in the morning, if you have time.

nocache          Setting this to true, will cause subselect inner queries not to be cached. This causes nocache to become the default behaviour.
maxcpus         Specify an integer here to limit the maximum number of cores that that IRDB uses to process a query. When running on computers with large number of cores > 16, setting a value of 50% of the number of cores, can help performance.
unload_timer Specify an integer here that causes inactive databases to be unloaded after that amount of time in minutes. This helps reduce memory consumption, when you have large numbers of databases, of which only a % are being used concurrently. Default is 0, which causes inactive databases not to be unloaded.
unload_temptables    Specify an integer here that causes inactive temp to be released automatically after that amount of time in minutes. The system default is 30 minutes.
log_dir            Lets you specify a directory to place the irdb.log irdbServer log file.
Querycache    =true  … allows results of queries to be cached and reused as necessary (saving time).
Querycache_timeout =20 can be used in irdb.ini to change the default timeout,  e.g. to 20 minutes.

Recommendations

Currently you have unload_timer set to 60. Reducing this to 30, and then 15, would help reduce memory usage, as it will IRDB’s only in memory for 30 or 15 mins. Reload time is about File Size / 150MB in seconds. Your current config means it keeps a file loaded for an hour. 

You are not currently using the unload_temptables setting. So it is probably setting at the default. Reducing this value to say 15 and then 10 means some of the temp tables that IRDB creates inside will get cleaned up sooner and help reduce memory usage.

You are not currently using maxcpus. Setting this to say 4 ( out of your 8 ) cpus, will help make sure, IRDB doesn’t use all its resources on one particular query or customer.

There’s a query cache feature, where IRDB can be programmed to cache the results of executing queries. Turning this on could reduce CPU usage, but I would leave it off at the moment, as CPU usage is not a problem.

I was thinking about your configuration, and I believe one optimization we could make is, during your late night data updates, we trigger a reload event. This could be unnecessarily loading data into IRDB, when no one is currently connected. A new feature e.g. /reloadifloaded option could be worth while looking into adding, or just do a /onload for a scheduled load, and /reload for user triggered.

                        John