AWS Glue is taking very long time to read data from MySQL table (61 millions)

Question

I have a glue job reading a table from MySQL (61 millions records). Job is taking ages when performing join operation with mapping file (400 records). I tried parallel JDBC read (from catalog and from options) but no luck with performance.

code : `connection_option= {"url": "jdbc:mysql://", "user": "", "password": "","dbtable": "dbname", "hashexpression":"column name","hashpartitions":"10"}
dy_trades = glueContext.create_dynamic_frame.from_options ('mysql',connection_options=connection_option, transformation_ctx = "dy_trades")`

Even if don't perform any transformations and try to load data in S3 file (connection with bulkSize 10), job is taking very long time. 
Running job with 100 workers, glue 3.0 - spark.

Can you please guide on performance improvement? am I missing anything in the job? Please

Answer

Hello,

With regards to job taking ages while performing the join operation ---
In cases where one of the tables in the join is small (here the one with 400 records), we can indicate Spark to handle it differently reducing the overhead of shuffling data. This is performed by [hinting Apache Spark](https://spark.apache.org/docs/latest/sql-performance-tuning.html#join-strategy-hints-for-sql-queries) that the smaller table should be broadcasted instead of partitioned and shuffled across the network. The Spark parameter `spark.sql.autoBroadcastJoinThreshold` configures the maximum size, in bytes, for a table that will be broadcast to all worker nodes when performing a join.

Also, I could observe you are using G.1X worker. Given that G.1X worker type may not necessarily be the most optimal worker types when it comes to memory-intensive jobs, I would recommend you to upgrade it to the G.2X worker. For more details on AWS Glue Worker types, see the documentation on [AWS Glue Jobs](https://docs.aws.amazon.com/glue/latest/dg/add-job.html).

---
References:
1. Performance Tuning - Spark 3.3.0 Documentation (apache.org) [[link](https://spark.apache.org/docs/latest/sql-performance-tuning.html#broadcast-hint-for-sql-queries)] 
2. Optimize memory management in AWS Glue | AWS Big Data Blog (amazon.com) [[link](https://aws.amazon.com/blogs/big-data/optimize-memory-management-in-aws-glue/)]
3. Best practices to scale Apache Spark jobs and partition data with AWS Glue | AWS Big Data Blog (amazon.com) [[link](https://aws.amazon.com/blogs/big-data/best-practices-to-scale-apache-spark-jobs-and-partition-data-with-aws-glue/)]

AWS Glue is taking very long time to read data from MySQL table (61 millions)

Contenuto pertinente