Internal database size

Hi,

Using Ignition 7.9.2, a gateway backup has 118 MB, of which the database backup (.idb) has 114MB.

The project_resources table in this database takes 97% of this space.

Looking at the largest rows in this table by blob size, they are mostly old/deleted windows, which no longer appear in the designer.

An export from the designer for all windows takes 11MB.

We took a look at this database because the internal db backups were taking longer and longer, blocking the gateway.

My question is, how do we compact/clean the project_resources table and if it is advisable.

Thanks!

Backing up the internal DB takes one minute:

Created auto-backup of internal database "config.idb" in 57 seconds

The internal db needs to hold on to some older versions of windows (well, actually all project resources), whether deleted or not, for rollback. These older versions should be getting pruned on a regular basis, but if you think there’s a bug in the pruning the best bet is to contact support and forward them your gateway backup so we can look at it and see what’s going on.

Hello Kathy,

Our problem comes from this:

...
INFO   | jvm 1    | 2017/05/02 16:01:16 | E [T.P.Config                    ] [13:01:16]: Error storing tag values. tag-provider=default
INFO   | jvm 1    | 2017/05/02 16:01:16 | simpleorm.utils.SException$Jdbc: Opening com.inductiveautomation.ignition.gateway.localdb.sqlite.SingleConnectionDatasource@29369965
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at simpleorm.sessionjdbc.SSessionJdbc.innerOpen(SSessionJdbc.java:113)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at com.inductiveautomation.ignition.gateway.localdb.persistence.PersistenceSession.initialize(PersistenceSession.java:28)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at com.inductiveautomation.ignition.gateway.localdb.PersistenceInterfaceImpl.getSession(PersistenceInterfaceImpl.java:69)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at com.inductiveautomation.ignition.gateway.localdb.PersistenceInterfaceImpl.getSession(PersistenceInterfaceImpl.java:46)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at com.inductiveautomation.ignition.gateway.sqltags.tagproviders.internal.InternalTagStore.openIfNot(InternalTagStore.java:1300)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at com.inductiveautomation.ignition.gateway.sqltags.tagproviders.internal.InternalTagStore.internalStoreTagValues(InternalTagStore.java:1339)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at com.inductiveautomation.ignition.gateway.sqltags.tagproviders.internal.InternalTagStore.storeTagValues(InternalTagStore.java:1248)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at com.inductiveautomation.ignition.gateway.sqltags.providers.AbstractStoreBasedTagProvider.tagValuesChanged(AbstractStoreBasedTagProvider.java:2449)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at com.inductiveautomation.ignition.gateway.sqltags.scanclasses.SimpleExecutableScanClass$ScanClassTagEvaluationContext.processAndReset(SimpleExecutableScanClass.java:1131)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at com.inductiveautomation.ignition.gateway.sqltags.scanclasses.SimpleExecutableScanClass.run(SimpleExecutableScanClass.java:920)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$SelfSchedulingRunner.run(BasicExecutionEngine.java:511)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:593)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at java.lang.Thread.run(Thread.java:745)
INFO   | jvm 1    | 2017/05/02 16:01:16 | Caused by: java.sql.SQLException: Connection is locked. Datasource only allows one connection at a time. More information was logged to the gateway console.
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at com.inductiveautomation.ignition.gateway.localdb.sqlite.SingleConnectionDatasource.getConnection(SingleConnectionDatasource.java:58)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	at simpleorm.sessionjdbc.SSessionJdbc.innerOpen(SSessionJdbc.java:111)
INFO   | jvm 1    | 2017/05/02 16:01:16 | 	... 18 common frames omitted
INFO   | jvm 1    | 2017/05/02 16:01:25 | E [c.i.i.g.l.s.SingleConnectionDatasource] [13:01:25]: The following stack successfully received a connection. A new attempt was blocked for over 30000 ms 
INFO   | jvm 1    | 2017/05/02 16:01:25 | java.lang.Throwable: null

...

In practice the designer loses connection with the gateway 10-20 times per day, for ~30 seconds.

The project is quite stable (as in the number of windows created/deleted) for about 2 months.

If you suggest I can send a gateway backup to support.

Yes, please contact support with this.

We ended up modifying the following parameters in gateway.xml:

<entry key="localdb.autobackup.delay">120</entry>
<entry key="localdb.autobackup.count">5</entry>

This reduced the interval to 2 hours.