Technical Tip: Understanding FortiAnalyzer HA analytics database size differences
Description
This article describes why analytics database size may differ between FortiAnalyzer HA cluster members, even when HA synchronization is healthy.
Scope
FortiAnalyzer.
Solution
In a FortiAnalyzer HA cluster, archive logs are synchronized between members.
Archive logs and the analytics database are separate. Archive logs are the stored log files. The analytics database is built locally from the available archive logs and is used by Log View, FortiView, and reports.
Because archive logs are synchronized, but the analytics database is built from the archive logs available at the time of rebuild, HA members can show different analytics database sizes even when HA status and configuration synchronization are healthy.
A difference in analytics database size does not always mean that archive logs are missing or that HA synchronization is broken. The difference should first be isolated to one of the following areas:
Archive log usage.
Analytics database usage.
Hcache usage.
Internal database usage.
ClickHouse storage usage in FortiAnalyzer v7.6 and later.
The most common reason for a large difference in analytics database size is that the HA members do not share the same indexed analytics database date range.
This can happen after an upgrade or rebuild activity if the members rebuild analytics from different start dates, or if rebuilds are performed at different times while the available archive log window changes.
Example:
Primary analytics database range: 2025/11/28 to 2026/06/17.
Secondary analytics database range: 2025/10/16 to 2026/06/17.
In this example, the secondary member is expected to have a larger analytics database because it contains a longer indexed analytics range.
Confirm the total SQL database size by running the following command on both HA members.
diagnose sql show db-size
Primary unit:
Size: 30200.15 GB
Secondary unit:
Size: 33550.80 GB
This confirms the total SQL database size difference, but it does not identify which database component is different.
Identify which database component accounts for the difference.
Run the following command on both HA members:
diagnose log device
Primary unit:
Device Name Device ID Used Space(logs / quarantine / content / IPS) Allocated Space Used%
Example_Device_01 FGTXXXXXXXXXXX 120.5GB(120.5GB / 0.0KB / 0.0KB / 0.0KB) unlimited n/a
Example_Device_02 FGTYYYYYYYYYYY 85.2GB( 85.2GB / 0.0KB / 0.0KB / 0.0KB) unlimited n/a
Total usage: 18 ADOMs, logs=29050.4GB(29050.4GB/0.0KB/0.0KB/0.0KB) database=30210.6GB(ADOMs usage:30193.8GB(30186.5GB, 0.0KB) + Internal Usage:16.8GB)
Secondary unit:
Device Name Device ID Used Space(logs / quarantine / content / IPS) Allocated Space Used%
Example_Device_01 FGTXXXXXXXXXXX 120.3GB(120.3GB / 0.0KB / 0.0KB / 0.0KB) unlimited n/a
Example_Device_02 FGTYYYYYYYYYYY 85.1GB( 85.1GB / 0.0KB / 0.0KB / 0.0KB) unlimited n/a
Total usage: 18 ADOMs, logs=29025.9GB(29025.9GB/0.0KB/0.0KB/0.0KB) database=33550.8GB(ADOMs usage:33537.2GB(33530.1GB, 0.0KB) + Internal Usage:13.6GB)
The Total usage line can be read as follows:
logs= <total archive usage> (logs/quarantine/content/IPS).
database= <total database usage> (ADOMs usage:<total ADOM DB usage> (analytics DB,hcache) + Internal Usage:<internal DB usage>).
In this example:
Hcache is 0.0KB on both HA members.
Internal database usage is small.
The large difference is in ADOM analytics database usage.
The next step is to check the analytics database range with 'diagnose test application logfiled 4'.
Confirm whether the analytics database ranges are different.
Run the following command on both HA members:
diagnose test application logfiled 4
Primary unit:
ADOM Example_ADOM[FSFADOM3] last-dvm-update:2h10m24s(ago)
quota : 74800.0GB(log=29920.0GB, db=44880.0GB), split_ratio=60%(db), trim_thres=95%
retention : log=365 days, db=365 days(actual from 2025/11/28 00:00:00 to 2026/06/17 13:00:00)
log-db : 30420.7GB(SIEM-DB:0.0KB) 67.7%
log-files : 29136.9GB 97.3%
Secondary unit:
ADOM Example_ADOM[FSFADOM3] last-dvm-update:2h12m11s(ago)
quota : 74800.0GB(log=29920.0GB, db=44880.0GB), split_ratio=60%(db), trim_thres=95%
retention : log=365 days, db=365 days(actual from 2025/10/16 00:00:00 to 2026/06/17 13:00:00)
log-db : 33776.1GB(SIEM-DB:0.0KB) 75.2%
log-files : 29115.0GB 97.2%
In this example, both HA members have the same configured retention and quota, but the actual analytics database range is different. The secondary member has an older analytics database start date, so it has a larger analytics database.
In FortiAnalyzer v7.6 and later, ClickHouse storage may also show the analytics database usage difference.
Run the following command on both HA members:
diagnose system disk usage -h /var/private/clickhouse/store -c -d 2
Primary unit:
29.1T /var/private/clickhouse/store
Secondary unit:
32.4T /var/private/clickhouse/store
In this example, the ClickHouse storage difference follows the same direction as the SQL database size difference.
Explanation.
The analytics database is rebuilt from archive logs that are available at the time of the rebuild. If HA members are rebuilt at different times, or with different configured start dates, the indexed analytics range may differ.
Configured retention is the intended retention target. It does not guarantee that the full period is physically available if quota or disk limits cause older archive logs to be trimmed.
For example, one HA member may rebuild analytics while archive logs are available from 2025/10/16. Another HA member may rebuild later when the oldest available archive logs start from 2025/11/28. In that case, the two members will have different analytics database ranges and different analytics database sizes.
Archive file roll differences.
Archive logs can be synchronized between HA members, but rolled archive files are not always expected to match as identical file objects.
The following values may differ:
Rolled file timestamp.
Compressed file size.
File roll order.
File name sequence.
These differences do not confirm log loss by themselves. To validate that all archive logs exist on both HA members, compare raw logs for the same device, same log type, and same timestamps with matching log IDs.
Hcache and internal database usage.
Some hcache or internal database usage may differ between HA members. These values should also be checked before concluding that any difference in the SQL databases on both members is caused by the analytics database range.
If hcache is the same on both HA members, hcache is not the cause of the database size difference.
Realigning analytics databases for both members.
If HA synchronization is healthy, archive usage is close, and the difference is mainly in the analytics database range, treat the issue as an analytics database indexing/range mismatch. Before rebuilding:
Confirm that the required archive logs exist.
Review ADOM quota allocation.
Confirm that archive usage is not close to the quota.
Confirm the required analytics database start date.
For the closest matching result, rebuild both HA members from the same start date and during the same maintenance window, after storage allocation and archive log availability have been confirmed.
For rebuild syntax, see this article: Technical Tip: FortiAnalyzer SQL database rebuild.