FWD » Core Development » Database

In a large customer application, it was noticed a heap usage increase on a freshly started FWD server. The assumption is the changes in trunk/11347 related to the new metadata tables (or loading) increases the heap requirement, for the in-memory H2 meta database.

With trunk 11328, the heap usage for a fresh FWD server was ~50% lower.

What's interesting is that FWD is loading all records from the meta.xml for the database in question, and loads them again from the DMO annotations. Also, all DMO interface and impl classes are being loaded in the JVM, which on its own may be a reason for the heap increase.

The meta database has this primary index:

ADD INDEX "_File-Name" ON "_File" 
  AREA "Schema Area" 
  PRIMARY
  INDEX-FIELD "_File-Name" ASCENDING ABBREVIATED

So this allows multiple records for the same file (and if my interpretation of the code above is correct, it would explain why FWD doesn't fail when loading the _File table twice).

Constantin Asofiei wrote:

So this allows multiple records for the same file (and if my interpretation of the code above is correct, it would explain why FWD doesn't fail when loading the _File table twice).

The _File table is not read from the meta.xml, so we are good here.

In fact, albeit meta.xml is still parsed on startup, very few records are actually created from it (see the log). It should become obsolete at all in the near future.

Igor, I have these stats:

2845632    META_FIELD__64
656384    META_INDEX_FIELD__64
355704    META_FIELD__8
302656    META_INDEX__64
135296    META_FILE__64
82048    META_INDEX_FIELD__8
44463    META_FIELD
37832    META_INDEX__8
16912    META_FILE__8
10256    META_INDEX_FIELD
4729    META_INDEX
2114    META_FILE

Do you know if before you made your changes, the extent fields in the _Field and _File tables were created or not?

Constantin Asofiei wrote:

Igor, I have these stats:
[...]

Do you know if before you made your changes, the extent fields in the _Field and _File tables were created or not?

Constantin,
Yes, I know that there are extent fields in _File and _Field tables. Where these statistics are taken from and with which FWD branch?
Thank you.

Constantin Asofiei wrote:

... and post here a list of all new fields added plus their extent, in each table.

Look only at the meta tables which are populated in FWD in the H2 meta database, don't check each and every table.

Constantin Asofiei wrote:

Constantin Asofiei wrote:

... and post here a list of all new fields added plus their extent, in each table.

Look only at the meta tables which are populated in FWD in the H2 meta database, don't check each and every table.

See below:

ADD FIELD "_File-Attributes" OF "_File" AS logical 
  FORMAT "yes/no" 
  INITIAL "no" 
  LABEL "File-Attributes" 
  POSITION 61
  MAX-WIDTH 512
  EXTENT 64
  ORDER 61

ADD FIELD "_category" OF "_File" AS character 
  FORMAT "x(32)" 
  INITIAL "" 
  LABEL "category" 
  POSITION 62
  MAX-WIDTH 64
  ORDER 62
  MANDATORY

ADD FIELD "_Fil-Misc3" OF "_File" AS integer 
  FORMAT ">>>9" 
  INITIAL "?" 
  LABEL "Fil-Misc3" 
  POSITION 63
  MAX-WIDTH 80
  EXTENT 8
  ORDER 63

ADD FIELD "_Fil-Misc4" OF "_File" AS character 
  FORMAT "x(12)" 
  INITIAL "?" 
  LABEL "Fil-Misc4" 
  POSITION 64
  MAX-WIDTH 208
  EXTENT 8
  ORDER 64
  CASE-SENSITIVE
---------------
ADD FIELD "_Field-Attributes" OF "_Field" AS logical 
  FORMAT "yes/no" 
  INITIAL "no" 
  LABEL "Field-Attributes" 
  POSITION 58
  MAX-WIDTH 512
  EXTENT 64
  ORDER 58

ADD FIELD "_Fld-Misc3" OF "_Field" AS integer 
  FORMAT ">>>9" 
  INITIAL "?" 
  LABEL "Fld-Misc3" 
  POSITION 59
  MAX-WIDTH 80
  EXTENT 8
  ORDER 59

ADD FIELD "_Fld-Misc4" OF "_Field" AS character 
  FORMAT "x(12)" 
  INITIAL "?" 
  LABEL "Fld-Misc4" 
  POSITION 60
  MAX-WIDTH 208
  EXTENT 8
  ORDER 60
  CASE-SENSITIVE
---------------
ADD FIELD "_Index-Attributes" OF "_Index" AS logical 
  FORMAT "yes/no" 
  INITIAL "no" 
  LABEL "Index-Attributes" 
  POSITION 25
  MAX-WIDTH 512
  EXTENT 64
  ORDER 25
---------------
ADD FIELD "_If-Attributes" OF "_Index-Field" AS logical 
  FORMAT "yes/no" 
  INITIAL "?" 
  LABEL "If-Attributes" 
  POSITION 18
  MAX-WIDTH 512
  EXTENT 64
  ORDER 18
---------------
ADD FIELD "_TenantId" OF "_User" AS integer 
  FORMAT "->>>>9" 
  INITIAL "0" 
  LABEL "TenantId" 
  POSITION 30
  MAX-WIDTH 4
  ORDER 30
  MANDATORY

ADD FIELD "_Domain-Name" OF "_User" AS character 
  FORMAT "X(64)" 
  INITIAL "" 
  LABEL "Domain-Name" 
  POSITION 31
  MAX-WIDTH 64
  ORDER 31
  MANDATORY

ADD FIELD "_sql-only-user" OF "_User" AS logical 
  FORMAT "yes/no" 
  INITIAL "no" 
  LABEL "sql-only-user" 
  POSITION 32
  MAX-WIDTH 1
  ORDER 32
  MANDATORY
---------------
ADD FIELD "_Connect-TenantId" OF "_Connect" AS integer 
  FORMAT "->>>>9" 
  INITIAL "0" 
  LABEL "Tenant ID" 
  POSITION 28
  MAX-WIDTH 4
  ORDER 28
  MANDATORY

ADD FIELD "_Connect-DomainId" OF "_Connect" AS int64 
  FORMAT "->>>>>>>>9" 
  INITIAL "0" 
  LABEL "Domain ID" 
  POSITION 29
  MAX-WIDTH 8
  ORDER 29
  MANDATORY
---------------
ADD FIELD "_MyConn-TenantId" OF "_MyConnection" AS integer 
  FORMAT "->>>>>>>>>9" 
  INITIAL "?" 
  LABEL "Tenant Id" 
  POSITION 7
  MAX-WIDTH 4
  ORDER 7
---------------
ADD FIELD "_Startup-nGovernor" OF "_Startup" AS integer 
  FORMAT "->>>>>>>>>9" 
  INITIAL "0" 
  LABEL "Login governor (-nGovernor)" 
  POSITION 32
  MAX-WIDTH 4
  ORDER 32
  MANDATORY

ADD FIELD "_Startup-LGovernor" OF "_Startup" AS integer 
  FORMAT "->>>>>>>>>9" 
  INITIAL "0" 
  LABEL "Lock governor (-LGovernor)" 
  POSITION 33
  MAX-WIDTH 4
  ORDER 33
  MANDATORY
---------------
ADD FIELD "_Lock-TenantId" OF "_Lock" AS integer 
  FORMAT "->>>>>>>>>9" 
  INITIAL "0" 
  LABEL "Tenant Id" 
  POSITION 11
  MAX-WIDTH 4
  ORDER 11
  MANDATORY

ADD FIELD "_Lock-PartitionId" OF "_Lock" AS integer 
  FORMAT "->>>>>>>>>9" 
  INITIAL "0" 
  LABEL "Partition Id" 
  POSITION 12
  MAX-WIDTH 4
  ORDER 12
  MANDATORY

ADD FIELD "_Lock-DomainId" OF "_Lock" AS int64 
  FORMAT "->>>>>>>>9" 
  INITIAL "0" 
  LABEL "Domain Id" 
  POSITION 13
  MAX-WIDTH 8
  ORDER 13
  MANDATORY

ADD FIELD "_Lock-Tty" OF "_Lock" AS character 
  FORMAT "x(10)" 
  INITIAL "?" 
  LABEL "TTY" 
  POSITION 14
  MAX-WIDTH 20
  ORDER 14
  MANDATORY

ADD FIELD "_Lock-TransId" OF "_Lock" AS integer 
  FORMAT "->>>>>>>>>9" 
  INITIAL "0" 
  LABEL "Trans Id" 
  POSITION 15
  MAX-WIDTH 4
  ORDER 15
  MANDATORY

ADD FIELD "_Lock-Trans-State" OF "_Lock" AS character 
  FORMAT "x(10)" 
  INITIAL "?" 
  LABEL "Trans State" 
  POSITION 16
  MAX-WIDTH 20
  ORDER 16
  MANDATORY

ADD FIELD "_Lock-Trans-Flags" OF "_Lock" AS character 
  FORMAT "x(10)" 
  INITIAL "?" 
  LABEL "Trans Flags" 
  POSITION 17
  MAX-WIDTH 20
  ORDER 17
  MANDATORY
---------------
ADD FIELD "_UserTableStat-PartitionId" OF "_UserTableStat" AS integer 
  FORMAT "->>>>>>>>>9" 
  INITIAL "0" 
  LABEL "Partition Id" 
  POSITION 9
  MAX-WIDTH 4
  ORDER 9
  MANDATORY

ADD FIELD "_UserTableStat-TenantId" OF "_UserTableStat" AS integer 
  FORMAT "->>>>>>>>>9" 
  INITIAL "0" 
  LABEL "Tenant Id" 
  POSITION 10
  MAX-WIDTH 4
  ORDER 10
  MANDATORY
---------------
ADD FIELD "_Seq-Attributes" OF "_Sequence" AS logical 
  FORMAT "yes/no" 
  INITIAL "no" 
  LABEL "Seq-Attributes" 
  POSITION 13
  MAX-WIDTH 512
  EXTENT 64
  ORDER 13

From Eric:

If all this data is correct and needed (i.e., there is not a bug/leak), we should take the metadata database (or at least the parts that recently have gotten really big) out of memory and put them into a separate, file-backed database. This should be ok, as long as applications do not have performance critical modules that rely on quick access to these tables (and this should be rare). However, we probably should leave the "live" tables (e.g., _lock, _connect, etc.) in memory for now, at least until we re-implement them to be just-in-time, at which time the implementation will look very different anyway.

While moving to a file-backed database is mostly working out the proper split of which tables go to file and which stay in memory and a JDBC URL change, we also will need to start with a "clean slate" at every server startup, so there is some extra housekeeping code needed. Do you already have a Redmine task about the heap problem?

Igor, what URL needs to change so that the H2 meta database is on disk, instead of memory?

Please create a patch for this, and post it here. I want to test if the FWD server's initial heap is lower after moving the H2 meta database from memory to disk.

The issue described at #4742-12 is a result of the fact that the login/password for the metadatabase is generated at the server start. So one has to drop it before restart.

Igor, thanks for the help. The server heap was reduced with ~300MB, which is the size of the _meta database on disk, but this is not enough.

OTOH, I think the DMO instances (or their data) remain pinned in memory, in Hibernate or H2.

With trunk/11347, Persistence.save will not immediately insert the record, but instead (as Hibernate is involved) the insert will be postponed until commit. So, even if FWD is reporting ~3 seconds to populate the _File meta table, this is not including inserting the records (not a problem per se, but something to keep in mind).

Eric, what's the proper way to ensure the Hibernate session is really flushed of cached records? I've done a p.flushBatch(); in MetadataManager.populateDatabase before the p.commit call, and this seems to help.

Igor Skornyakov wrote:

The issue described at #4742-12 is a result of the fact that the login/password for the metadatabase is generated at the server start. So one has to drop it before restart.

For the file-based, H2 _meta database, instead of using a randomly generated user/pw that we use for in-memory H2 databases, we should use the same user/pw as for the primary database associated with that metadata. We should have a clean-up method invoked with a server shutdown hook. The same should be invoked at startup, in case something went wrong during shutdown. In the common case, it should have little to do on server startup and should be relatively fast to return.

Ideally, I would want the clean-up method to drop the database entirely, so that we start the server with a clean slate. If this is not possible from H2, dropping all the tables and indices should suffice, but this would be slower.

Constantin Asofiei wrote:

Eric, what's the proper way to ensure the Hibernate session is really flushed of cached records? I've done a p.flushBatch(); in MetadataManager.populateDatabase before the p.commit call, and this seems to help.

That's the proper way, pre-4011b. We may need to put that back for 4011b, or better yet, allow for a cache-less Session. There is little value in the cache for a bulk insert scenario in 4011b. Still thinking over the best way to divorce the cache from Session...

Eric, after clearing the H2 meta from the heap, a real leak appeared, related to my TRPL changes for incremental conversion support.

I'll commit the flush and the incremental conversion leak to 3821c and 4011b after some tests.