Heap Exhausted when migrating MySQL DB to PostgreSQL DB

[navzen2000]

Please provide the following information:

• [ ] pgloader --version

  $ pgloader --version
  pgloader version "3.6.2"
  compiled with SBCL 1.4.0-1.el7
  

• [ ] did you test a fresh compile from the source tree?

  Compiling pgloader from sources is documented in the README, it's easy to do, and if patches are to be made to fix your bug, you're going to have to build from sources to get the fix anyway…

• [ ] did you search for other similar issues?

• [ ] how can I reproduce the bug?

  Incude a self-contained pgloader command file.

  If you're loading from a database, consider attaching a database dump to your issue. For MySQL, use mysqldump. For SQLite, just send over your source file, that's easy. Maybe be the one with your production data, of course, the one with just the sample of data that allows me to reproduce your bug.

  When using a proprietary database system as a source, consider creating a sample database on some Cloud service or somewhere you can then give me access to, and see my email address on my GitHub profile to send me the credentials. Still open a public issue for tracking and as documentation for other users.

LOAD DATABASE
     FROM      mysql://dd:dd@172.18.0.2:3306/dd
     INTO postgresql://dd:dd@x.y.z.q:5432/dd

 WITH include drop, create tables, create indexes, reset no sequences, prefetch rows = 1000, batch rows = 1000,
      workers = 8, concurrency = 1,
      single reader per thread, rows per range = 500

  SET PostgreSQL PARAMETERS
      maintenance_work_mem to '32800MB',
      work_mem to '32000MB',
      search_path to 'defectdojo'

  SET MySQL PARAMETERS
      net_read_timeout  = '120',
      net_write_timeout = '120'

  CAST type bigint when (= precision 20) to bigserial drop typemod,
      type date drop not null drop default using zero-dates-to-null,
      -- type tinyint to boolean using tinyint-to-boolean,
      type year to integer,
      type int when unsigned to bigint,
      type int with extra auto_increment when unsigned to bigserialt;

• [ ] pgloader output you obtain

Heap exhausted during allocation: 65536 bytes available, 131072 requested.
 Gen  StaPg UbSta LaSta Boxed Unbox    LB   LUB !move    Alloc  Waste     Trig   WP GCs Mem-age
   0:  3610     0 131069   164   139  2508  4606   284 237358736 5681520 195697608    0   1  0.0000
   1:  6121  6203     0    60   637     0 17365   893 580299184 11556432 42949672   21   0  0.4994
   2:     0     0     0   944 22545     0 58196   620 2487484704 189169376 2530434376  728   1  0.0000
   3:     0     0     0     0     0     0     0     0        0      0  2000000    0   0  0.0000
   4:     0     0     0     0     0     0     0     0        0      0  2000000    0   0  0.0000
   5:     0     0     0     0     0     0     0     0        0      0  2000000    0   0  0.0000
   6:     0     0     0  1558  1226     0     0     0 88257008 2969104  2000000 1448   0  0.0000
   7:  4302  4792     0     0     0     0     0     0        0      0  2000000    0   0  0.0000
   Total bytes allocated    = 3393399632
   Dynamic-space-size bytes = 4294967296
GC control variables:
   *GC-INHIBIT* = false
   *GC-PENDING* = false
   *STOP-FOR-GC-PENDING* = false

debugger invoked on a SB-KERNEL::HEAP-EXHAUSTED-ERROR in thread
#<THREAD "lparallel" RUNNING {1005738723}>:
  Heap exhausted (no more space for allocation).
65536 bytes available, 131072 requested.

• [ ] data that is being loaded, if relevant

PASTE HERE THE DATA THAT HAS BEEN LOADED

• [ ] How the data is different from what you expected, if relevant How can we increase heap for PGLOADER?

[fabioselau077]

same here

[jgould22]

Using the latest PGLoader built using SBCL 2.4.7 on an ARM machine I was able mitigate this error by increasing the amount of ram pgloader could use like so.

pgloader --dynamic-space-size <some amount of ram in mb>  <some load file> 

I should not that setting the DYNSIZE variable like described elsewhere did not help me at all, only the cli flag seemed to do anything.

[sr-oct]

same here.

There is no --dynamic-space-size option in the docker version.

dimitri/pgloader pgloader --version
pgloader version "3.6.7~devel"
compiled with SBCL 2.1.1.debia

[UnsolvedCypher]

The Docker version does have it, but not the one on Dockerhub, you can use ghcr.io/dimitri/pgloader:latest.