[Feature request] Order source tables by size descending to reduce migration time

[stumbaumr]

Hi,

first thank you for this great software! I am used to write my migration scripts by hand for other destination DBs and pgloader is really wonderful!

From previous migrations I have the experience to sort the tables by size descending, so the biggest tables in a parallel migration are started first.

By using this strategy the runtime of the migration is in most cases the time for the biggest table - more so if there is one table outstanding in size.

I am currently migrating a zabbix DB from mysql to postgresql, the history and trends tables are huge compared to the rest (260GByte vs. 2GByte).

All the best and thanks!
Rainer

[stumbaumr]

So, if the migration of trends and trends_uint would start first...

2020-03-13T20:18:39.793000Z LOG report summary reset
               table name     errors       read   imported      bytes      total time       read      write
-------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
          fetch meta data          0         14         14                     0.172s    
             Drop Indexes          0          8          8                     0.097s    
                 Truncate          0          7          7                     0.250s    
Check Concurrency Support          0          7          0                     0.000s    
-------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
           public.history          0  732823681  732823681    26.9 GB     4h5m57.098s  4h5m56.216s  9h2m35.528s
       public.history_log          0   12800464   12800464     8.1 GB      17m33.713s  17m33.033s  48m36.097s
      public.history_uint          0  650589631  650589631    23.9 GB    3h37m22.916s  3h37m21.757s  7h42m24.475s
       public.history_str          0   38692234   38692234     1.8 GB       15m6.045s  15m5.322s  34m38.903s
      public.history_text          0  126147061  126147061   105.4 GB    6h20m10.881s  6h20m7.683s  15h24m50.167s
            public.trends          0  2137417722  2137417722   103.5 GB    12h45m2.034s  12h44m59.125s  1 days 4h26m4.929s
       public.trends_uint          0  1290568638  1290568638    50.1 GB    8h18m26.942s  8h18m22.589s  18h49m33.487s
-------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  COPY Threads Completion          0         21         21               12h45m2.051s    
           Create Indexes          0          8          8               2h26m28.836s    
   Index Build Completion          0          8          8                 50m19.617s    
          Reset Sequences          0          0          0                     0.188s    
             Primary Keys          2          2          0                     0.068s    
         Install Comments          0          0          0                     0.000s    
-------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
        Total import time          ?  4989039431  4989039431   319.8 GB    16h1m50.758s    
2020-03-13T20:18:39.858000Z INFO Stopping monitor

[dimitri]

Thanks for the feedback and opening an issue. It's also always nice to see such a summary were pgloader processed and migrated about 320 GB of data unattended!

Ordering the tables to optimise the load duration is an idea that crossed my mind in the past already, but being smart about it looks more involved that sorting in decreasing size order.In your example we can see that the longest imports are for tables public.trends and then public.trends_uint and then only public.history_text, but in terms of bytes the ordering is already different with public.history_text being the largest table in the summary.

So we would need to take into account number of rows, it seems that the ordering would then be the same. In general though getting the count(*) of each table is going to take a non-trivial amount of time, and getting an estimation is quick and easy but can be quite disconnected from the actual numbers.

I think we need to explore the best way to attack this optimisation problem before we get with some code. Maybe we can experiment with some code and several use-cases before taking a decision... and if it's optional, then I could accept a very naive patch that fixes your problem first and reduce your migration down to 12h45 total.

[stumbaumr]

As I said above the rest of the tables is only about 2GBytes. So I split the migration into two parts. But in the end I was to lazy to split further and write a wrapping script...

So the above summary contains only the 7 big tables.

A count(*) indeed takes time, that is why I restricted myself to the table sizes in my previous migrations. And there I sorted the export files by size descending to push them into the destination DB system. Size is good enough and a low hanging fruit and can be used with SQL/CSV-imports as well.

This migration was a one-time migration. No idea if I am going to use your tool again soon...

[dimitri]

If you still are in a position to, it would be nice if you could test PR #1120 which implements your idea here. I still think it's pretty naive, but maybe that's all we need also... and it sounds better than going through the alphabetical table ordering anyways.

[AccArc]

Thanks for pgload, it is a great software!

This example above is a Zabbix db.
This table trends_uint has a PK itemid which is bigint(20) unsigned.
This default rule applies: type bigint when (< 19 precision) to numeric drop typemod
With numeric PK, the concurrency is not enabled, so the multiple readers are not enabled.
The trends_uint table has 1.2G rows with small row size.
The single reader thread is cpu heavy and stalls one cpu at 100% effectively throttling the throughput.
When the cast is removed, with multiple readers it has much higher throughput.

The table trends_uint does not have a PK, so no concurrency is even considered.
It has a normal composite key which starts with itemid bigint(20), but it is not considered.
It would be nice if it could be overridden to consider a field explicitly somehow.

Thanks again!

[stumbaumr]

Hi dimitri,

unfortunately I already had to drop the source MySQL database, so I am not able to test this here anymore.

But your fast support is incredible!

There are more MySQL DBs sitting around here. I will try the PR on them as soon as I get a budget to migrate them.

Thanks
Rainer