Troubleshooting Ignition Reporting Slow Historian Queries

An Ignition report built on historical data takes a long time to render and often ends on a red screen with a database icon, while the Gateway database connection still shows valid. The Gateway, the database engine and the report client all run on one Core i3 PC with 4 GB RAM under Windows 7, and about 3 GB is in use during the query. The same data pulled through a Power Table comes back normally. That combination points at a slow SQL query on a memory-starved host, not at a broken connection. The checks below run in order, and each one names the reading that decides the next.

Where does the red database icon come from when the connection is valid?

The Gateway connection status reflects a validation test on the datasource. It does not measure how long a real query takes. A datasource can validate in milliseconds and still take minutes to return a large historical result set.

Trace the request hop by hop on this single-PC install:

1. The report preview (Designer or client) asks the Gateway to run the report data query.
2. The Gateway passes the SQL to the database over JDBC on the loopback interface. No network hop is involved, so cable and switch faults are excluded.
3. The database engine reads the index and data pages from disk, or from RAM if they are cached, and returns rows.
4. The Gateway holds the rows in JVM memory while the report engine lays out the document.

The red icon appears when step 3 or 4 does not complete in time or fails. The slowest resource in that chain is the disk, because every page the database cannot find in RAM becomes a disk read. On a 4 GB machine that also hosts the Gateway JVM and the OS, that is the likely bottleneck.

Check 1: Is the query slow without Reporting in the path?

Run the exact report SQL in the query playground (the Designer's database query tool) with real values filled in for the t_stamp range and the tagid list. Take three readings: elapsed time, number of rows returned, and whether the tool itself freezes.

• Slow or timing out here: Reporting is not at fault. Go to Check 2 (hardware) and Check 3 (query plan).
• Fast here: the query is acceptable. The problem sits in what the report does with the rows. Go to Check 5.

Record the time and row count. You need both as the baseline for the verification step at the end.

Check 2: Is the database starving on the same host as the Gateway?

Database and Gateway share one PC because the client supplied only one machine. That is an allowed layout, but it makes two large memory consumers compete: the Gateway JVM and the database cache. Databases perform well when their indexes stay in RAM. When RAM runs short, index pages get evicted and every lookup turns into disk reads. The same database moved to a machine with less RAM and a slightly slower disk has been seen to run about 30 times slower for exactly this reason.

Take these readings while the slow query runs:

The planned fix is 8 GB RAM. Before buying it, confirm the Windows 7 installation is 64-bit. A 32-bit Windows edition cannot use more than roughly 4 GB of address space, so the extra RAM would sit unused. Check under System properties for the system type and the installed edition. Also confirm the edition's memory limit covers 8 GB.

New RAM does nothing for the database until the engine is allowed to use it. If the database is MySQL with InnoDB, its buffer pool size setting decides how much RAM caches indexes and data. The Gateway JVM has its own maximum heap setting in the Gateway service configuration. Set both deliberately so the two do not push the OS into paging. Read the current values before changing them, and restart the affected service after each change.

If the disk is a spinning drive, an SSD gives the larger gain for random index reads. The client may not be able to change the drive, but it is worth stating in the recommendation.

Check 3: Does the query plan use an index on tagid and t_stamp?

Run EXPLAIN against the report SQL from the command line or MySQL Workbench, with sample values in place of the report parameters:

EXPLAIN SELECT ... FROM <history_table> WHERE tagid IN (<id_list>) AND t_stamp BETWEEN <start> AND <end>;

Use the same table, columns and timestamp format the report query uses. Read the plan for three things:

• Access type is a full table scan (ALL) or the key column is empty: the query does not use an index. Check that the WHERE clause matches the leading columns of the existing index and that no function wraps t_stamp or tagid.
• An index is used but the rows estimate is very large: the time range or tag list is too wide. Narrow it (Check 5).
• Index used and row estimate small: the plan is good, and the slowness comes from memory pressure (Check 2).

The default historian indexes on this table may already be the best available. The plan often shows nothing to improve, and the fix then lies in hardware and query scope. Run the check anyway, because it rules out a full scan in a few seconds.

Check 4: Why does the Power Table return the same data quickly?

A fast Power Table and a slow report do not prove the database is healthy. The two components can issue different queries. Compare them on these points before trusting the comparison:

If both queries are identical in scope and the Power Table is still much faster, the extra time is spent in the report layer. Go to Check 5. If the report pulls far more rows, cut the scope so the two match.

Check 5: Is the report holding more rows than it needs?

The Gateway holds the whole result set in JVM memory while the report renders. On a machine with 1 GB free, a large raw history pull can push the Gateway into heavy garbage collection or paging. The database then looks disconnected to the report, even though the datasource is fine. Reduce the work in this order:

1. Shorten the time range to the smallest window the report needs.
2. Reduce the tag list to the tags the report displays.
3. Aggregate in SQL (for example, average per interval) so the database returns hundreds of rows instead of raw samples.
4. Rerun the report and watch the Gateway Java process memory in Task Manager. A steady climb to the heap limit means the result set is still too large.

The Gateway console log from the failed run states the actual exception behind the red icon (a timeout, a connection error, or a memory error). Read it after each failed run and match it to the branch above. A timeout points to Checks 2 and 3, and a memory error points to Check 5 and the JVM heap setting.

Apply the RAM upgrade and confirm the fix

1. Record the baseline: query time and row count from Check 1, plus peak memory use and disk active time from Check 2.
2. Confirm 64-bit Windows and the edition's memory limit, then install the 8 GB.
3. Confirm Windows reports the full 8 GB in System properties.
4. Set the database cache size and the Gateway JVM heap so neither takes so much that Windows starts paging. Restart the database service and the Gateway service.
5. Run EXPLAIN again and confirm the index is used with the expected rows estimate.
6. Run the same query in the query playground, using the same range and tags as the baseline. Record time and rows. Run it twice, because the second run shows warm-cache behavior.
7. Run the report over the same range three times. The first run loads the cache, and the second and third show steady-state timing. The red database icon should not appear, and the run time should track the playground time.
8. During the report run, watch memory in Task Manager. Free memory should stay well above the 1 GB seen before, and disk active time should fall below the pinned reading from the baseline.