Performance degredation using SymlinkTextInputFormat vs MapredParquetInputFormat input

[jordanemarcel]

Hello,

We have noticed recently that the performance of simple queries counting the number of records of two tables pointing to the same Parquet data was drastically different.

We are running Trino in AWS and accessing data on S3.

The tables differ only in their input format: one accesses the Parquet data directly on S3 (MapredParquetInputFormat), one uses a manifest file that points to the same set of files in the same location (SymlinkTextInputFormat).

The following query was used during these tests:

SELECT count(*) FROM <table>
WHERE "date" = '2023-03-12'

where <table> could either be direct_parquet_table (for the MapredParquetInputFormat input) or symlink_parquet_table (for the SymlinkTextInputFormat).

The query against the direct_parquet_table takes around 2 seconds.
The query against the symlink_parquet_table takes around 25 seconds.

Note that exactly the same Parquet files are used for both queries.
For direct_parquet_table, the Parquet files are retrieved from the target partition using an S3 List operation.
For symlink_parquet_table, the Parquet files are retrieved from the manifest file itself on S3.

Why do we have such a drastic difference of performance?

I have also attached the plans of EXPLAIN ANALYSE VERBOSE for both queries, and we can see that, in the case of the Symlink table, the LocalExchange / RemoteSource of the last stage are blocked for most of the time.
parquet-plan.txt
symlink-plan.txt

As a note, we have also seen exactly the same performance degradation using Athena v2 and v3.

Please also find below the table definitions for both tables.

Many thanks!

CREATE EXTERNAL TABLE `direct_parquet_table`(
  `dp_date` string, 
  `dp_id` string, 
  `dp_server_type` string, 
  `dp_src` string, 
  `dp_tailed_path` string, 
  `dp_timestamp` string, 
  `ed_hostname` string, 
  `ed_ldate` string, 
  `ed_ltime` string, 
  `ed_region` string, 
  `sd_accauthmethod` string, 
  `sd_acode` string, 
  `sd_aresp` string, 
  `sd_attcnt` string, 
  `sd_attsize` string, 
  `sd_authact` string, 
  `sd_bcode` string, 
  `sd_bdat` string, 
  `sd_bdatbytes` string, 
  `sd_bulk` string, 
  `sd_cc` string, 
  `sd_ccode` string, 
  `sd_cerenfmod` string, 
  `sd_cermatch` string, 
  `sd_cernames` string, 
  `sd_chost` string, 
  `sd_cost` string, 
  `sd_cphr` string, 
  `sd_cpst` string, 
  `sd_ctype` string, 
  `sd_dcode` string, 
  `sd_dir` string, 
  `sd_dom` string, 
  `sd_ecode` string, 
  `sd_ehlo` string, 
  `sd_err` string, 
  `sd_esra` string, 
  `sd_extdom` string, 
  `sd_fcode` string, 
  `sd_fromhdremail` string, 
  `sd_fromhdrname` string, 
  `sd_gcode` string, 
  `sd_hcode` string, 
  `sd_hostowner` string, 
  `sd_hst` string, 
  `sd_ip` string, 
  `sd_ipowner` string, 
  `sd_lmethod` string, 
  `sd_localip` string, 
  `sd_lthread` string, 
  `sd_msgid` string, 
  `sd_mxlistres` string, 
  `sd_node` string, 
  `sd_o365res` string, 
  `sd_o365resdtail` string, 
  `sd_odom` string, 
  `sd_odommatch` string, 
  `sd_odommf` string, 
  `sd_omni` string, 
  `sd_ptr` string, 
  `sd_rcpt` string, 
  `sd_rcptacttype` string, 
  `sd_rcpthdrtype` string, 
  `sd_rcv` string, 
  `sd_rejcode` string, 
  `sd_rejinfo` string, 
  `sd_rejtype` string, 
  `sd_routing` string, 
  `sd_rply` string, 
  `sd_sni` string, 
  `sd_snt` string, 
  `sd_time` string, 
  `sd_tls` string, 
  `sd_tlsauth` string, 
  `sd_tlscn` string, 
  `sd_tlsctp` string, 
  `sd_tlsextkeyusage` string, 
  `sd_tlskeyusage` string, 
  `sd_tlsvrfd` string, 
  `sd_tlsvrfyres` string)
PARTITIONED BY ( 
  `date` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://<bucket>/compacted_data'
TBLPROPERTIES (
  'transient_lastDdlTime'='1678874097')

CREATE EXTERNAL TABLE `symlink_parquet_table`(
  `dp_date` string, 
  `dp_id` string, 
  `dp_server_type` string, 
  `dp_src` string, 
  `dp_tailed_path` string, 
  `dp_timestamp` string, 
  `ed_hostname` string, 
  `ed_ldate` string, 
  `ed_ltime` string, 
  `ed_region` string, 
  `sd_accauthmethod` string, 
  `sd_acode` string, 
  `sd_aresp` string, 
  `sd_attcnt` string, 
  `sd_attsize` string, 
  `sd_authact` string, 
  `sd_bcode` string, 
  `sd_bdat` string, 
  `sd_bdatbytes` string, 
  `sd_bulk` string, 
  `sd_cc` string, 
  `sd_ccode` string, 
  `sd_cerenfmod` string, 
  `sd_cermatch` string, 
  `sd_cernames` string, 
  `sd_chost` string, 
  `sd_cost` string, 
  `sd_cphr` string, 
  `sd_cpst` string, 
  `sd_ctype` string, 
  `sd_dcode` string, 
  `sd_dir` string, 
  `sd_dom` string, 
  `sd_ecode` string, 
  `sd_ehlo` string, 
  `sd_err` string, 
  `sd_esra` string, 
  `sd_extdom` string, 
  `sd_fcode` string, 
  `sd_fromhdremail` string, 
  `sd_fromhdrname` string, 
  `sd_gcode` string, 
  `sd_hcode` string, 
  `sd_hostowner` string, 
  `sd_hst` string, 
  `sd_ip` string, 
  `sd_ipowner` string, 
  `sd_lmethod` string, 
  `sd_localip` string, 
  `sd_lthread` string, 
  `sd_msgid` string, 
  `sd_mxlistres` string, 
  `sd_node` string, 
  `sd_o365res` string, 
  `sd_o365resdtail` string, 
  `sd_odom` string, 
  `sd_odommatch` string, 
  `sd_odommf` string, 
  `sd_omni` string, 
  `sd_ptr` string, 
  `sd_rcpt` string, 
  `sd_rcptacttype` string, 
  `sd_rcpthdrtype` string, 
  `sd_rcv` string, 
  `sd_rejcode` string, 
  `sd_rejinfo` string, 
  `sd_rejtype` string, 
  `sd_routing` string, 
  `sd_rply` string, 
  `sd_sni` string, 
  `sd_snt` string, 
  `sd_time` string, 
  `sd_tls` string, 
  `sd_tlsauth` string, 
  `sd_tlscn` string, 
  `sd_tlsctp` string, 
  `sd_tlsextkeyusage` string, 
  `sd_tlskeyusage` string, 
  `sd_tlsvrfd` string, 
  `sd_tlsvrfyres` string)
PARTITIONED BY ( 
  `date` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://<bucket>/symlink_root/'
TBLPROPERTIES (
  'transient_lastDdlTime'='1678874659')

[jordanemarcel]

Alright, I have investigated this issue on my laptop, and this seems to be coming from the very poor symlink file listing that is done on all symlinks of a Manifest file.

There is an option to enable an optimize symlink listing, here.
However, there seems to be no good reason to have the recursiveDirWalkerEnabled set to false to enable the optimisation, as this flag is not used at all by the symlink optimiser.

Moreover, we could improve this feature a bit better by allowing more than one parent location for the optimizer code.
In our real-world case scenario, the Parquet files were split between two S3 parent directories.
Performing two S3 listings would be better than running hundreds of "S3 getMetadata" calls for each file.

I might raise a pull request with these suggestions later on.

In the meantime, we are unblocked by enabling the optimiser and making sure all our Parquet files are in the same directory.