Check and adjust report hcache

diagnose test application sqlrptcached 2

This command shows if hcache creation is able to catch up.

Following is a sample result of running this command.

Number of log table read: all=6453(fortiview=0, rpt=6453) pending=1

Number of log table done: all=6453(fortiview=0, rpt=6453) rpt=6453

Current hcache table entries: 155750

Number of hcache requests sent: 70999

Number of log table vacuums: 39401, pending=2

FortiView hcache load: rounds=817, tbl=653600

ncmdb:

cache hit: sch=0, config=27, chart=140, macro=0, dataset=140 config=27

calls : sch=130, config=11, chart=23, macro=0, dataset=23

The following table provides notes about some output lines in the example.

Line	Notes
Number of log table read	pending=0 means hcache creation is able to catch up. If pending is above 0, see What to look for below.
Number of log table done	The number of primary tables used to calculate the Number of hcache requests sent.
Current hcache table entries	Total hcache on the system.
Number of hcache requests sent	The number of charts per report * the number of primary tables * the number of reports.
Number of log table vacuums	The postgres built-in status. A pending number above 0 indicates insufficient postgres resources.
FortiView hcache load	rounds is the number of FortiView caches proactively loaded into memory.
ncmdb	Report configuration database.
cache hit	config is the number of enabled auto cache.

What to look for

• In Number of log table read, if the pending number is continuously above 0 or is increasing, that indicates there are too many pending log tables to read and the system lacks resources to create cache. In this case, consider disabling auto-cache on some reports. See Enabling auto-cache and Reports Settings tab in the FortiAnalyzer Administration Guide.
• Run execute sql-report list-schedule <ADOM> and check if there are too many scheduled reports and if auto-cache is enabled. See Scheduling reports and Enabling auto-cache in the FortiAnalyzer Administration Guide.
• Run execute top to check which applications are using the most system resources.

execute sql-report hcache-check <ADOM> <schedule-id>

This command shows a specific report’s hcache status.

If necessary, check the hcache status of a specific report that you think might be a problem.

For example, if the ADOM is root and schedule-id is 10004, then run execute sql-report hcache-check root 10004.

To get the schedule-id, run execute sql-report list-schedule root and see the NAME column.

Following is a sample result of running the execute sql-report hcache-check <ADOM> <schedule-id> command.

layout_num:1

start [0] get layout-id:10004.

start report_process, layout-id:10004, layout title:Admin and System Events Report.

device list:All_FortiGates.

reports num:1.

device list[0].FWF60C3G13006291[root].

device list[1].FG3K2C3Z11800039[root].

......

> checking (10004_t10004-Admin and System Events Report) ...

checking chart Admin-Login-Summary...

8/8 (100%) done 0.131 secs used.

checking chart Admin-Login-Summary-By-Date...

8/8 (100%) done 0.128 secs used.

...

What to look for

• If a few reports are causing a bottleneck, check those reports’ Check the report diagnostic log and consider reconfiguring those reports. See also Check and adjust report auto-cache daemon.