Editing database properties

You can change the database properties by editing the app.db.properties file.

The database properties file is located at the following location:/usr/local/megha/conf/custom/app.db.properties . You can edit the following properties:

The number of query processing threads: The API server divides a query into subtasks that can be run concurrently using a thread pool. By default, the query thread pool is configured to have two active threads.
Change the number of query processing threads by defining the property QueryProcess.threadpool.size.

Recommendation: Set the number of query processing threads equal to the number of CPUs on the host..
Memory bound for a resource relations cache: The resource relations cache maintains the relations mapping between the resources in memory, for fast access. By default, there is no memory bound on the resource relations cache, but you can put a memory bound by defining the following property:
app.db.memory.bound.for.relation.caches.in.mb

The bound is specified in MBps. If this bound is exceeded, then the cache overflows to disk.

If property value is set as 0, it is unbounded.

Note: This property should be changed only if the server is running out of memory. Putting a memory bound can result in a performance problem. If the size of a resource relations cache goes beyond the configured value, then performance is degraded as disk I/O increases.

The following table shows the sample case study (when you do not set memory bound and when you set memory bound) performed on datasets of various sizes.

Dataset number	Resource count	Resource relations cache size (MB)	Optimum: 100%		90%		75%		50%
Dataset number	Resource count	Resource relations cache size (MB)	Mem. bound (MB)	Time (sec)	Mem. bound (MB)	Time (sec)	Mem. bound (MB)	Time (sec)	Mem. bound (MB)	Time (sec)
1	65815	64	64	1.64	57.6	2.24	48	3.46	32	4.16
2	115404	110	110	1.50	99	2.90	82.5	2.18	55	3.42
3	338818	380	380	4.24	342	5.21	285	7.84	190	10.67
4	500000	625	625	112	562.5	95.00	468.75	126.00	312.5	166.00
5	740000	930	930	115	837	127.30	697.5	166.00	465	208.40
6	1000000	1350	1350	164	1215	176.00	1012.5	271.00	675	344.70

The following describes the terms used in the sample case study (when you do not set memory bound and when you set memory bound) performed on datasets of various sizes:

Resource count Number of resources already in the Analyzer detail view database.
Cache size Memory used by the resource relations cache for resource count mentioned in the Resource Count column.
Optimum - 100% The memory usage and timings when there is no overflow to disk, and the whole relations cache is in-memory.
90% 90% of cache is in-memory, 10% is on disk.
75% 75% of cache is in-memory, 25% is on disk.
50% 50% of cache is in-memory, 50% is on disk.

The test operations are based on the following use case:

For datasets 1 to 3 Update ~5,000 resources
For datasets 4 to 6 Update 20,000 resources

Note: When configuring the bound for your dataset, keep a margin of 15 to 20% above the values specified in the table. For example, for 500,000 resources, the appropriate memory bound to keep the entire cache in-memory is 625 MB, but the recommended value for your dataset is 700 MB.

Summary of tuning properties

All tuning properties are summarized in the following table.

Parameter	Property name	Default value	Minimum value	Maximum value
Number of query processing threads	QueryProcess.threadpool.size	2	1	Should be the number of CPUs on host
Memory bound for Resource Relations cache	app.db.memory.bound.for.relation.caches.in.mb	0	0	Java Integer max value, i.e., 2,147,483,647