We're running Matillion (v1.54) on an AWS EC2 instance (CentOS), based on Tomcat 8.5. We have developped a few ETL jobs by now, and their execution takes quite a lot of time (that is, up to hours). We'd like to speed up the execution of our jobs, and I wonder how to identify the bottle neck.
What confuses me is that both the m5.2xlarge EC2 instance (8 vCPU, 32G RAM) and the database (Snowflake) don't get very busy and seem to be sort of idle most of the time (regarding CPU and RAM usage as shown by top).
Our environment is configured to use up to 16 parallel connections.
We also added JVM options -Xms20g -Xmx30g to /etc/sysconfig/tomcat8 to make sure the JVM gets enough RAM allocated.
Our Matillion jobs do transformations and loads into a lot of tables, most of which can (and should) be done in parallel. Still we see, that most of the tasks are processed in sequence.
How can we enhance this?
Depends what kind of jobs you have. For some use cases we wanted to increase parallelism so we just used clustered instances (2 node and 3 node clusters which allows us to run 32 or 48 jobs in parallel). This option will be more expensive.
For other cases we were changing server parameters like
In this case you need to be careful not to increase number of total threads. For example, let's say you have instance with 2 vCPU. This instance by default can run 2 threads per job (TASK_POOL_MULTIPLIER =2). By default every Matillion server will run 16 concurrent jobs no matter of size of instance. So total number of threads you can run in instance would be:
2 vCPU x 16 concurrent jobs x 2 threads per job = 64 Total threads
In scenario where you want your job to execute faster, then i would try to increase number of threads per job like TASK_POOL_MULTIPLIER = 4 but then you will be able to run 8 concurrent jobs.
TASK_POOL_MULTIPLIER= 4,
TASK_RUNNER_POOL_SIZE =8,
SCHEDULER_THREADS = 8
There is no guidance for this, you have to change it and test it to see what works the best for your case.