FWD » Core Development » Database

• performance improvement: re-architect threading to distribute work in a more granular way (preserve prioritization by table, but allow multiple threads to run within the import of a single table to avoid the "critical path" problem);
• make schema generation independent of Hibernate (today we rely on Hibernate tools past end-of-life, which have some issues for us);
• we currently cannot re-create the schema to properly use text instead of varchar for string data, due to some changes we made in the P2J<-->Hibernate data bindings;
• make index creation independent of Hibernate (today we let Hibernate generate the index creation DDL; we need more flexibility);
• abstract out PostgreSQL-specific code (running the ANALYZE command after each table is imported) into a more database-independent model.

From email, in connection with a customer import issue:

I think the core problem is the way we import one table per thread, creating this "biggest table" bottleneck. I suspect if we refactored the import to split the *.d reading work from the import work (and possibly the DMO creation/population work), we could leverage a higher number of threads better, and we would have better throughput overall.

I expect we can read *.d files very fast. I believe the heavy lifting of import probably is in:

• the runtime work to convert the strings to BDTs and populate the DMOs; and
• the JDBC and database work to insert the records.

I don't know exactly how this heavy lifting part breaks down between Java and the database, nor even how much relative work there is in reading the *.d files. I haven't profiled the import in forever.

I am imagining a process where multiple threads read *.d files and fill a queue with "work". Other threads read from the queue and manage the import side. I'm not sure how this "work" is represented in the queue: Finished DMOs? Arrays of BDTs? Something else? We probably want it to be fairly lightweight, so that the queue supplier threads can stay ahead of the consumer threads.

To keep the reading simple, I don't think we necessarily would use more than one thread per individual *.d file, so the supplier work would have to be fast, lest we still have the "biggest table" bottleneck. So, this might mean putting something less complex than fully populated DMOs into the queue. OTOH, if we determine that the database is the main bottleneck, then maybe it makes sense to fully create and populate the DMOs on the supplier side.

Or we make a more radical shift and bypass the DMOs altogether, instead leveraging the runtime only for interpreting the *.d file values, and generating dialect specific INSERT statements directly. The more I think about it, I actually like this idea best.

Also, to leverage JDBC batching, I think we need to batch INSERT statements for the same table together, but I'm not 100% sure on this point. I think we definitely want to keep using batching for performance, even though I don't know the ideal value.

Greg added:

I think we should:

• eliminate the usage of DMOs
• eliminate the usage of TRPL
• possibly eliminate the usage of the the .p2o documents (though we still need something to tell us how to read the .d files)

Ovidiu added:

The current implementation is struggling too much to maintain compatibility with normal FWD execution:

• we parse the .d using streams classes designed for 4GL;
• we create/initialize/save DMO objects using a lower level persistency, but still, coupled with 4GL usage;
• we allocate a single table to a single thread.

Of course, there is a good part in these. We re-use a lot of code and directly benefit from all improvements of the stream/persistence, but the cost is performance.

If we aiming for performance we can try various solutions (Eric already mentioned several of them). Here are a couple of additional ideas:

• using 1 (or more if needed) thread for reading .d files and creating 'chunks' of data (ex: max 1MB of a single .d). Then, a pool of DB-writers to consume these chunks. This way a single table/.d file can be saved using multiple threads (although this is just a PoC, writing in parallel to same table might not improve performance, but at least we need to test this);
• generating a SQL file instead of the actual SQL import, in a file similar to a SQL dump. Even if it has several GB, it can be processed and then executed by 'native' console applications like psql. I am assuming that this is the optimal way to do the 'restore'. This has the advantage that the FWD import can be done 'offline' and only the restore operation requires SQL connection.

I think beside this new 'optimal performance' import, we should still keep the current solution which provides the maximum compatibility at the expense of time (and probably other resources).

Constantin added (verbally) in a meeting:

• Optimally, don't use BDTs at all, to avoid the overhead of allocating many millions of small objects and forcing the GC to clean them up.
• Use INSERT statements directly.

possibly eliminate the usage of the the .p2o documents (though we still need something to tell us how to read the .d files)

Instead of the .p2o, it is OK to use the DMO annotations to understand the .d contents. I just don't want to create DMO instances as part of the import. If we need to add any data to the DMOs to enable this, it is OK to do that.

Greg Shah wrote:

I think we should:

• eliminate the usage of DMOs
• eliminate the usage of TRPL
• possibly eliminate the usage of the the .p2o documents (though we still need something to tell us how to read the .d files)

Even if we don't use the DMOs as vehicles for the imported records, I think we need either the DMOs or the P2O document to understand the legacy schema well enough to parse the .d files. I don't think we can get rid of both, without creating a lot more work for ourselves. The new database schema is lossy, so whether we would parse the DDL files or scan the schema directly, we would need to infer a lot of information that we already have available in the DMOs or P2O AST.

If we use the DMOs, the assumption is that we have the full set available to us at import time. If we use a jar file and we can identify which files within that jar file represent the Java DMO interfaces, this, combined with a set of .d files, should be enough to run the import.

The P2O is already a well-structured representation of the AST, with sufficient legacy schema information. We know that this, combined with a set of .d files, is enough to run the import, because that is how we do it today. I think it makes sense to continue to use this as an input. We should be able to read it from a jar file (if we don't already).

I agree that we can/should remove TRPL and the DMOs as dependencies.

The AST has little meaning because the structure is shallow and completely regular. Parsing it brings extra work. It is much easier to just read the annotations out of the DMOs.

Eric Faulhaber wrote:

Constantin added (verbally) in a meeting:

---

• Optimally, don't use BDTs at all, to avoid the overhead of allocating many millions of small objects and forcing the GC to clean them up.
• Use INSERT statements directly.

While I agree with both of these, we have a big dependency on the existing BDT hierarchy to parse the string-formatted values from a .d file into data we can use with JDBC. Even if we cut out the DMO use and thus the need for BDT instances, ultimately, we need Java primitives and objects to use as substitution values for INSERT statements. I agree that not instantiating the intermediate BDT instances must be a good thing. However, I think this will require some major refactoring or duplication of this BDT parsing code. Maybe those closer to that code have different opinions.

Greg Shah wrote:

The AST has little meaning because the structure is shallow and completely regular. Parsing it brings extra work. It is much easier to just read the annotations out of the DMOs.

The reason I like the P2O is because we know it represents the complete schema for a database. It is up to the customer how DMOs are packaged, so I am concerned that finding/identifying the full set for a database can introduce irregularities.

The work of parsing the AST is done once per table, which is negligible in comparison to the work of processing millions of records, and is not necessarily slower than reading Java annotations.

For now, we have the P2O present in the jar file(s) with the DMOs, for dynamic database support. So, not using it for import doesn't drop the dependency on the artifact.

• we go with the DMOs and ignore p2o. This is a bit more conservator. The DMO has all needed information, but it require a bit of processing. For example:
  • @Table annotation has dumpName attribute which identifies the source .d file;
  • each property has the column attribute its @Property annotation beside the data type. We use this for creating the INSERT statements in Persister. (See composeInsertStatements()). In fact these are the artefacts we need for import.
• we use only p2o. As Eric said it has full information on the DMOs. After all, the DMOs were generated from p2o. Even if this might require more coding, the result is more lightweight, decoupling the import from other classes needed when loading the DMOs.

Ovidiu Maxiniuc wrote:

generating a SQL file instead of the actual SQL import, in a file similar to a SQL dump. Even if it has several GB, it can be processed and then executed by 'native' console applications like psql. I am assuming that this is the optimal way to do the 'restore'. This has the advantage that the FWD import can be done 'offline' and only the restore operation requires SQL connection.

I agree there is some value in this approach in producing a SQL backup file from legacy data. However, I don't think it gives us the performance boost we want to perform an initial import in a tight maintenance window. Even if we leverage multiple threads to produce the SQL file, the restore of it (at least for PostgreSQL, possibly others) is single-threaded with a tool like psql. If I am unaware of a multi-threaded way to do the restore, I will happily be corrected.

• we can disable (comment) the execution of SQL INSERTS to take PostgreSQL out of the equation;
• we can ignore the read data from stream and not creating any DMOs, which would reveal the legacy stream read overhead;
• skip creating the non-unique indices - to time the 'pure' import. The indices can also be created AFTER all the data is persisted, maybe even with the FWD server started (the downside here is that any query on these tables will be really slow until the indices are active).

Alternatively, we could rather simple write the SQL INSERT statements to one (or more) SQL file(s). This would allow to execute it (or them, in parallel) using a tool like PSQL and time the process and compare this with FWD counterpart.