Pgloader: Clarification concurrency vs. workers

[mfn]

Apologies upfront, I read several times through https://github.com/dimitri/pgloader/blob/master/pgloader.1.md#a-note-about-parallelism but couldn't make up how I best tune my setup in terms of pgloader. The dosc say:

The parameter workers allows to control how many worker threads are allowed to be active at any time (that's the parallelism level);
...
With a concurrency of 2, we start 1 reader thread, 2 transformer threads and 2 writer tasks, that's 5 concurrent tasks to schedule into workers threads.

How can one conclude the information from the docs to arrive that this example gives 5 concurrent tasks?

So with workers = 4, concurrency = 2, the parallel scheduler will maintain active only 4 of the 5 tasks that are started.

Is that "5" referring to:

• 1 reader
• 2 transformer
• 2 writers

?

Would this mean the performance of the reader is impacted, too?

Also: does this (directly/indirectly) affect, how parallel reading works, if?

With workers = 8, concurrency = 1, we then are able to work on several units of work at the same time.
... so those settings allow pgloader to be active on as many as 3 tables at any time in the load process.

I don't quite understand this calculation. If concurrency=1 means, next to the 1 reader there's 1 transformer and 1 writer and

concurrency allows to control how many tasks are started to handle the data

it would seem to me that workers could be arbitrary high but we don't get much more parallel work, because we always only have concurrency=1?

I'm trying to get a better understanding how things work together, my setup is

• 1 mysql server, 8 cores, 32GB RAM
• 1 pgsql server, 8 cores, 32GB RAM

I'm trying to figure out what setting make most use of this hardware so that the migration is as fast as possible.

Would a setting of:

• workers=8
• concurrency=8

mean that I would have:

• 1 reader
• 8 transformers
• 8 writers

and then I would be able to max the machine without overloading?

With the default settings (i.e. not specifying anything) the load on mysql like 0 (probably expected, only reads data) and pgsql is only around a load of 1.5. I would like to max the load and expect the migration of the data to be faster (always gonna be constrained by disks anyway).

I'm failing to come to a conclusion as I don't exactly understand how the calculations are supposed to be.

thanks!

[dimitri]

You say you want to maximize the load on the MySQL side. To do that you need to have many concurrent readers, so I would lower the concurrency, thus allowing pgloader to process several tables at the same time. What happens if you try workers=8, concurrency=1?

Also, related, how many tables do you have to migrate over?

[mfn]

What happens if you try workers=8, concurrency=1?

Sorry, no progress yet because I experience the same problem as https://github.com/dimitri/pgloader/issues/337 at the moment. Will try a newer version, currently using pgloader version "3.3.1" and compiled with SBCL 1.2.4.debian

Also, related, how many tables do you have to migrate over?

I've 21 tables to migrate. The largest one is ~75GB and ~60 million rows, then a few with ~10-15 million rows with a size, but their size drops of significantly after 15GB.
All in all I'm trying to migrate around 113GB of raw MySQL data+index.

To do that you need to have many concurrent readers, so I would lower the concurrency

Sorry, I think that's were I've a problem wrapping my head around. Why does lowering the "concurrency" give me "many concurrent readers"? :)

[dimitri]

Basically, concurrency is per-table. In your case I think you want to process several tables in parallel, so have workers active on more than one table at a time...

[mfn]

concurrency is per-table.

Ahhh ... !!

so have workers active on more than one table at a time...

Ok, so this means workers=n is the total number of parallel threads that should be available for consumption (sans the reader, will always include 1 transformer + 1 writer).

So concurrency=8 would mean 8 concurrent operations on the same table during MySQL->PgSQL transfer?

I.e. I've one really very large table which will probably take most of the time. So, disregarding the other tables, workers=8, concurrency=8 would try to get the most out of this?

[dimitri]

Here's my bet: do the largest table and all the other ones in parallel, and your total migration time will be the largest table, all the other having been done already by the time this one is over. Do you need to have more than one reader on this one I don't know, currently that will not happen in pgloader though.

Can you setup your migration using 2 pgloader instances, one doing only the big table, and the other one doing all of the others (use INCLUDING ONLY and EXCLUDING in your load files); run the commands in parallel and report here? If my guess is right then I will consider adding some (kind of) smarts about that in pgloader itself.

[mfn]

Regarding the initial "Clarification": I didn't know that this was a recent feature, but I just came over https://github.com/dimitri/pgloader/releases/tag/v3.3.1 which clearly explains how the math is supposed to work.

It would be great if this example there regarding "parallelism" can be included in the official docs.

[mfn]

Can you setup your migration using 2 pgloader instances, one doing only the big table, and the other one doing all of the others

I tried this approach but ultimately failed because of the constant hanging of pgloader when I processed multiple, big tables as I outlined in https://github.com/dimitri/pgloader/issues/337#issuecomment-269543876 .

I'm not done evaluating yet, but my current approach is to run one instance of pgloader per table. I do this via scripting, running them in parallel via virtual windows inside/ptys inside tmux.

This also maxes out the destinations machine resources at the beginning where all tables start to being migrated and drops off the longer it takes.

The biggest table takes 3h16m currently with this config:

WITH
 batch rows = 1000

CAST
 type varchar to varchar,
 type bigint with extra auto_increment to bigserial drop typemod,
 type bigint to bigint drop typemod

I hope I can find a way to even speed this up.

[dimitri]

Can you try building with CCL? It's known to be much better than SBCL at Garbage Collection and thus often faster for pgloader workloads.

[mfn]

Sorry, I think I won't do that.

I've a system in place already using tmux, spawning pgloader in parallel on all the tables and afterwards applying manually all the FKs I missed due using independent table imports (also in parallel for each table).

This system, and tuning postgres for that load, brought me already down to not quite 2h for everything, including a vacuum analyze afterwards.

From the benchmarks I made, pgloader is effectively at it's peak already with singular tables and due using posgres COPY I don't think performance will improve much for now. Maybe a higher batch size can do it, but because of https://github.com/dimitri/pgloader/issues/487#issuecomment-269631446 I can't currently.

[dimitri]

CCL is interesting because it would allow you much higher batch size. Your call of course!

[mfn]

Ok, I'll bite.

But, for starters, what is CCL and how do I use it? This whole setup for pgloader, the language etc., is completely foreign to me.

[dimitri]

CCL is Clozure Common Lisp, another implementation of the Common Lisp Standard (compiler, runtime, APIs, libs, etc). See http://ccl.clozure.com for more details. For building with CCL the docs are covering how to do that for pgloader (make CL=ccl basically) and also we have an automated build of a docker image available at https://hub.docker.com/r/dimitri/pgloader/tags/