Speed up retrieval of files from stores

[lfrank]

Feature Request

Problem

Right now the hash of large files in a store can take quite a long time. For example, on a ~200GB raw data file, the has took >200 seconds, which results in a long delay before data from that file can be used.

Requirements

Ideally the system might store the hash, the size, and the modification date, and would first check the size and date for a match. Given a matching size and date, that seem sufficient to ensure that it's the same file. This could also be a .config option for the store.

Justification

Provide the key benefits in making this a supported feature. Ex. Adding support for this feature would ensure [...]

Alternative Considerations

Do you currently have a work-around for this? Provide any alternative solutions or features you've considered.

Related Errors

Add any errors as a direct result of not exposing this feature.

Please include steps to reproduce provided errors as follows:

• OS (WIN | MACOS | Linux)
• Python Version OR MATLAB Version
• MySQL Version
• MySQL Deployment Strategy (local-native | local-docker | remote)
• DataJoint Version
• Minimum number of steps to reliably reproduce the issue
• Complete error stack as a result of evaluating the above steps

Screenshots

If applicable, add screenshots to help explain your feature.

Additional Research and Context

Add any additional research or context that was conducted in creating this feature request.

For example:

• Related GitHub issues and PR's either within this repository or in other relevant repositories.
• Specific links to specific line or focus within source code.
• Relevant summary of Maintainers development meetings, milestones, projects, etc.
• Any additional supplemental web references or links that would further justify this feature request.

[guzman-raphael]

@lfrank Thank you for raising this. We had a larger discussion with the core team last week and I'll post below a summary of what was covered.

• Reproducible steps were identified that highlights the performance issues with fetch and insert on large external filepath objects.
• Primary root cause of the slow response is related to evaluating the checksum for the full file contents which affects all external datatypes. This check is important to ensure data integrity across the entire data model. However, it is likely 'overkill' and there may be less costly methods with reasonable assurance.
• A hybrid approach was identified where we would replace the existing checksum logic (in a phased approach over 2 releases with proper deprecation warnings) with one that performs the following ordered checks failing early when possible:
  1. verify the file size
  2. 'seek' specific multi-byte chunks on a normalized range to perform the proper checksum only on these chunks
• Once implemented, this hybrid checksum will work as expected on any newly generated data. Existing external data looking to benefit from these performance improvements will need to manually update the internal tracking tables with the new checksums. This will be facilitated by a provided admin script.
• Priorities have been shifted to accommodate the escalation of this. We are targeting this in the next release (0.14.0) expected on 2021-06-25.
• Modified date was also considered as an alternative but it presents too many challenges between support on various operating systems and S3 compatibility.

[lfrank]

Thank you; that sounds like an excellent solution and I appreciate the prioritization.

…

On Jun 14, 2021, at 7:34 AM, Raphael Guzman ***@***.***> wrote:  @lfrank Thank you for raising this. We had a larger discussion with the core team last week and I'll post below a summary of what was covered. Reproducible steps were identified that highlights the performance issues with fetch and insert on large external filepath objects. Primary root cause of the slow response is related to evaluating the checksum for the full file contents which affects all external datatypes. This check is important to ensure data integrity across the entire data model. However, it is likely 'overkill' and there may be less costly methods with reasonable assurance. A hybrid approach was identified where we would replace the existing checksum logic (in a phased approach over 2 releases with proper deprecation warnings) with one that performs the following ordered checks failing early when possible: verify the file size 'seek' specific multi-byte chunks on a normalized range to perform the proper checksum only on these chunks Once implemented, this hybrid checksum will work as expected on any newly generated data. Existing external data looking to benefit from these performance improvements will need to manually update the internal tracking tables with the new checksums. This will be facilitated by a provided admin script. Priorities have been shifted to accommodate the escalation of this. We are targeting this in the next release (0.14.0) expected on 2021-06-25. Modified date was also considered as an alternative but it presents too many challenges between support on various operating systems and S3 compatibility. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[guzman-raphael]

@lfrank Wanted to provide a quick update on the above. We are running a bit behind on schedule than originally anticipated; about a week. However, this issue is still at the top of our priority. You may follow the ongoing progress of the fix in PR #931. Apologies for any inconvenience.

[guzman-raphael]

@lfrank We have just made a DataJoint Python release 0.13.6 which includes an option to disable checksum verification. Feel free to have a look at our documentation about this new feature.