Currently, when we set the option to write the query output to a file for validation reasons, we use a presto client that receives the results from the coordinator. During performance testing on nvl72, it was seen that certain query outputs are too large to be sent from worker to coordinator efficiently. One such query is TPC-H Q11 which takes 47s just to report results https://github.com/rapidsai/hercules-planning/issues/149. This is out of a total TPC-H runtime of 206s.
It was observed that if the workers were asked to directly write their results to a file using a CTAS, Q11 runtime drops to 3s.
Requesting an option to let every query run in this mode with slurm on nvl72. Optionally, when using this mode to write tables, the returned results for validation should be copying these tables to output.
Things to look out for:
- we don't want the results to persist between runs and also don't want to pollute the dataset directory
- similarly, we don't want to pollute the hive metastore. the launch-run script should be resilient enough to clear out the result tables if it encounters them before launching queries.
A follow up request to this issue will be asking for changes that allow for CTE materialization. It'd be great if that were also possible within the scope of this issue.
Currently, when we set the option to write the query output to a file for validation reasons, we use a presto client that receives the results from the coordinator. During performance testing on nvl72, it was seen that certain query outputs are too large to be sent from worker to coordinator efficiently. One such query is TPC-H Q11 which takes 47s just to report results https://github.com/rapidsai/hercules-planning/issues/149. This is out of a total TPC-H runtime of 206s.
It was observed that if the workers were asked to directly write their results to a file using a CTAS, Q11 runtime drops to 3s.
Requesting an option to let every query run in this mode with slurm on nvl72. Optionally, when using this mode to write tables, the returned results for validation should be copying these tables to output.
Things to look out for:
A follow up request to this issue will be asking for changes that allow for CTE materialization. It'd be great if that were also possible within the scope of this issue.