Purging the config.idb after splitting frontend/backend

Ignition
1

I recently split up a gateway into 2 into frontend/backend gateways. It was a few weeks ago now, but I must have just imported the original gateway into both and then deleted stuff from each. I’m guessing that a whole bunch of stuff is still stored inside the config.idb (like tag info in the frontend gateway which shouldn’t be there) because they’re both still ~220MB.

What’s the best method to purge out this config? I presume the answer is going to be to add the modules back and then manually delete the stuff from them to then delete it from the config. (In which case, something learnt for next time: start the gateways from scratch and import stuff in!)

2

You carried these backups over from a 7.9 or earlier system?

You can confirm with sqlite_analyzer (highly recommend) but it’s extremely likely that the table using almost all of that 220mB is PROJECT_CHANGES. That table is completely ignored in 8.0.0+; it was used for the ‘rollback’ feature in the legacy project system.
If you’ve got sufficient backups of your project(s) and gateway, you can simply drop the table directly. I would probably not do it while the gateway is running, and you can manually run the VACUUM pragma after to recover the emptied space.

1 Like
3

Yep, project was upgraded to 8.1.5 from 7.9.x a while back. I’ll have a look, cheers!

4

I had a look and the table only had 1000 rows. I deleted and vacuumed, and it didn’t change by much at all, still at 224MB. The TAGSCONFIG table has 51k rows in the frontend gateway that doesn’t need to be there, but I assume just deleting the table data would be bad…

5

sqlite_analyzer can give you a breakdown of space consumed per table; that’s going to be more reliable than checking :slight_smile: Maybe images?

6

image

PROJECT_RESOURCES 173249536 PROJECT_RESOURCES PROJECT_RESOURCES 0 0 15878 9790 3 170751066 166214807 8961 222666 62 6089 163038 8764 1543252 83953 0 173249536
TAGCONFIG 30819328 TAGCONFIG TAGCONFIG 0 0 73163 51029 4 25168422 7793555 2691 4454816 218 22135 7744 28518 4759493 105325 1 30819328
SQLTAGPROP 6888448 SQLTAGPROP SQLTAGPROP 0 0 93292 87214 3 6019425 597513 13 467891 62 6079 586 8100 231747 207 0 6888448
sqlite_autoindex_TAGCONFIG_1 5219328 sqlite_autoindex_TAGCONFIG_1 TAGCONFIG 1 0 51029 46457 5 4749622 0 0 191 524 4573 0 30204 225176 0 1 5219328
IMAGES 4337664 IMAGES IMAGES 0 0 1057 636 3 4190588 3881155 351 591474 5 422 3809 1764 114221 4025 0 4337664
sqlite_autoindex_SQLTAGPROP_1 4331520 sqlite_autoindex_SQLTAGPROP_1 SQLTAGPROP 1 0 87214 83282 5 3862490 0 0 125 297 3933 0 13327 143305 0 1 4331520
SQLTAG 1448960 SQLTAG SQLTAG 0 0 15627 14227 3 1279083 0 0 181 14 1401 0 2976 72588 0 1 1448960
TAGCONFIG_PROVIDERID_IDX 439296 TAGCONFIG_PROVIDERID_IDX TAGCONFIG 1 0 51029 50608 3 276994 0 0 7 7 422 0 1837 2234 0 1 439296
SQLTAGALARMPROP 355328 SQLTAGALARMPROP SQLTAGALARMPROP 0 0 6898 6556 3 300385 0 0 409 4 343 0 1312 14894 0 1 355328
pr_restype 219136 pr_restype PROJECT_RESOURCES 1 0 9790 9584 3 184170 0 0 31 7 207 0 1427 1605 0 1 219136
sqlite_autoindex_SQLTAG_1 158720 sqlite_autoindex_SQLTAG_1 SQLTAG 1 0 14227 14077 3 110763 0 0 8 4 151 0 1827 1593 0 1 158720
SQLTAG_OWNERID_IDX 137216 SQLTAG_OWNERID_IDX SQLTAG 1 0 14227 14097 3 90454 0 0 8 3 131 0 1270 1207 0 1 137216
sqlite_autoindex_SQLTAGALARMPROP_1 134144 sqlite_autoindex_SQLTAGALARMPROP_1 SQLTAGALARMPROP 1 0 6556 6430 3 109743 0 0 26 4 127 0 1016 2149 0 1 134144
sqlite_autoindex_PROJECT_RESOURCES_1 116736 sqlite_autoindex_PROJECT_RESOURCES_1 PROJECT_RESOURCES 1 0 9790 9680 3 83180 0 0 9 3 111 0 1324 1498 0 1 116736
PROJECT_RESOURCES_RESOURCEID_IDX 107520 PROJECT_RESOURCES_RESOURCEID_IDX PROJECT_RESOURCES 1 0 9790 9689 3 74752 0 0 8 3 102 0 1559 583 0 1 107520
SQLTAGEVENTSCRIPTS 60416 SQLTAGEVENTSCRIPTS SQLTAGEVENTSCRIPTS 0 0 180 124 2 43588 887 1 990 1 57 1 564 14510 133 1 60416
7

We don’t delete the project resources table after upgrading… but you could.

2 Likes
8

What is it for? or was it for?

9

It held… project resources :surprised-pikachu:

They are converted and living on disk now in 8.x.

10

Haha. So none of these are used anymore inside the table?

11

No, shouldn’t be. On a fresh Ignition 8 the table would either be empty or not exist, not sure which.

1 Like
12

Cool, that will free up 165MB :slight_smile:

13

I’ve deleted both the PROJECT_CHANGES and PROJECT_RESOURCES tables and all seems to be up and running :+1: and my config.idb is now 59MB down from 223MB :slight_smile:

Does deleting a tag provider also delete the tags associated with it? I’m still a bit miffed why the tagsconfig is so big considering I deleted the tag providers and added remote tag providers

14

Backend
TAGPROVIDERSETTINGS:

TAGPROVIDERSETTIGNS_ID NAME PROVIDERID DESCRIPTION ENABLED TYPEID ALLOWBACKFILL
0 default 8d3e8257-3c5e-41c7-aa73-9d69eee0ef7a Default tag provider 1 STANDARD 0
1 proj_name2 89ab6acd-53ad-48d5-b13e-e5cac0a22c65 Tag provider for proj_name2 project. 1 STANDARD 0

TAGCONFIG distinct providerIDs are 0,1,2

Frontend

TAGPROVIDERSETTIGNS:

TAGPROVIDERSETTIGNS_ID NAME PROVIDERID DESCRIPTION ENABLED TYPEID ALLOWBACKFILL
1 proj_name2 72a4a107-e81a-4972-bfec-ec9c91a48f9e 1 gantagprovider 0
2 default f18491f7-d1fb-4dfa-8246-422fbaa4731d 1 gantagprovider 0

TAGCONFIG distinct providerIDs are 0,2

15

For the frontend, I deleted all TAGCONFIG rows where the PROVIDERID = 0 which provider doesn't exist and used to be the old 'default' provider. Now it's dropped from 59MB to 18MB, much better!

Now to check if it broke anything... doesn't look like it

Now I'm really interested in the answer to this question

16

The answer appears to be ‘no’; deleting the provider itself doesn’t trigger a deletion of the associated records. It should still be safe to manually delete…but that’s probably something we should fix.

1 Like
17

Maybe with a warning that it will also remove all tags

18

Hello,

Any comments on SQLT* tables? They're huge in 7.9>8.1 auto upgrade, but empty when we perform a "Manual" upgrade. Or are we missing something?

"Manual" upgrade consists of 7.9>8.1 auto upgrade, export everything, wipe/reinstall, import everything.

19

sqlt_* tables are the historian's recordings. They aren't part of a gateway backup. If you upgrade in place, your upgraded server will simply keep using the existing tables in the database. If you upgrade into a new environment, your history starts over.

History is not part of the upgrade process itself.

2 Likes
20

The idb tables corresponding to 'legacy' tags (SQLTAG, SQLTAGPROP) are ignored in versions >= 8.X and slated for deletion in a future version.

The SQLTH_X tables generated in your RDBMS are subject to your own migration concerns.

1 Like