Matillion: How to identify performance bottleneck

Question

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?

There might be other chances as well based on the complexity of any custom scripts like SQL, Python. Are you having any custom SQL scripts in the orchestration or any python scripts. Please have a check on this thread for OOO issue certain SQL/Python scripts might consume lot of memory which might lead to performance issue. https://metlcommunity.matillion.com/s/question/0D54G00007iwhJKSAY/matillion-out-of-memory — Maran, Aug 21 '23 at 17:27

score 1 · Accepted Answer · answered Jun 14 '23 at 14:12

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

TASK_POOL_MULTIPLIER - number of threads per job
TASK_RUNNER_POOL_SIZE - number of concurrent jobs
SCHEDULER_THREADS-scheduler threads (at least as many as concurrent jobs and additional for housekeeping)

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.

peterb · Answer 2 · 2021-12-10T21:31:53.337

0

Because the Matillion server is just generating SQL statements and running them in Snowflake, the Matillion server is not likely to be the bottleneck. You should make sure that your orchestration jobs are submitting everything to Snowflake at the same time and there are no dependencies (unless required) built into your flow. These steps will be done in sequence:

These steps will be done in parallel (and will depend on Snowflake warehouse size to scale):

Also - try the Alter Warehouse Component with a higher concurrency level

edited Dec 10 '21 at 21:31

answered Nov 29 '21 at 17:27

peterb

697
3
11

Our job tasks are modelled to run in parallel just as your example job shows – dovregubben Dec 01 '21 at 14:30
Have you tried increasing the MAX_CONCURRENCY_LEVEL warehouse parameter? the default is 8, but some queries count as >1 – peterb Dec 10 '21 at 21:30
yes, we have tried to adjust that parameter as well, but to no effect – dovregubben Dec 15 '21 at 13:58

score 0 · Answer 3 · answered Nov 30 '21 at 06:19

By default there is only one JDBC connection to Snowflake, so your transformation jobs might be getting forced serial for that reason.

You could try bumping up the number of concurrent connections under the Edit Environment dialog, like this:

There is more information here about concurrent connections.

If you do that, a couple of things to avoid are:

Transactions (begin, commit etc) will force transformation jobs to run in serial again
If you have a parameterized transformation job, only one instance of it can ever be running at a time. More information on that subject is here

Yes, first we had set the number of concurrent connections to 5, then increased it to 16, but no effect whatsoever. We're checking your hints regarding transactions and parameterized transformation jobs now... — dovregubben, Dec 01 '21 at 14:29

Matillion: How to identify performance bottleneck

3 Answers3