Pgloader: Heap exhausted error

[richardji7]

Hi,

I got error below when trying to migrate a gitlab env from MySQL to PostgreSQL per their recommendation on using pgloader. https://docs.gitlab.com/ee/update/mysql_to_postgresql.html#converting-a-gitlab-backup-file-from-mysql-to-postgres

Any help, pointers are greatly appreciated.

Thanks.

2018-01-08T15:07:18.587000-05:00 ERROR Error while formating a row from "gitlabhq_production.merge_request_diffs":
2018-01-08T15:07:18.587000-05:00 ERROR A SB-KERNEL::HEAP-EXHAUSTED-ERROR condition without bindings for heap statistics.  (If
you did not expect to see this message, please report it.
Heap exhausted during allocation: 32768 bytes available, 816800 requested.
 Gen  StaPg UbSta LaSta Boxed Unbox    LB   LUB !move    Alloc  Waste     Trig   WP GCs Mem-age
   0:  9098     0 131070  1015     0  2985     0     0 122991776 8080224 42949672    0   0  0.0000
   1:     0     0     0     0     0     0     0     0        0      0 42949672    0   0  0.0000
   2:     0     0     0     0     0     0     0     0        0      0 42949672    0   0  0.0000
   3:     0     0     0     0     0     0     0     0        0      0 42949672    0   0  0.0000
   4:     0     0     0     0     0     0     0     0        0      0 42949672    0   0  0.0000
   5:     0     0     0   146 14211   300 90490    93 3298833504 146623392 3341783176  364  25  0.0000
   6:     0     0     0  1398  1136     0     0     0 80331456 2702656  2000000 1299   0  0.0000
   7:  3142 119872     0     0     0     0     0     0        0      0  2000000    0   0  0.0000
   Total bytes allocated    = 3502156736
   Dynamic-space-size bytes = 4294967296
GC control variables:
   *GC-INHIBIT* = false
   *GC-PENDING* = false
   *STOP-FOR-GC-PENDING* = false
Heap exhausted during allocation: 32768 bytes available, 838816 requested.
 Gen  StaPg UbSta LaSta Boxed Unbox    LB   LUB !move    Alloc  Waste     Trig   WP GCs Mem-age
2018-01-08T15:07:18.605000-05:00 ERROR Error while formating a row from "gitlabhq_production.merge_request_diffs":
2018-01-08T15:07:18.605000-05:00 ERROR A SB-KERNEL::HEAP-EXHAUSTED-ERROR condition without bindings for heap statistics.  (If
you did not expect to see this message, please report it.
   0:  9098     0 131070  1015     0  2985     0     0 122991776 8080224 42949672    0   0  0.0000
   1:     0     0     0     0     0     0     0     0        0      0 42949672    0   0  0.0000
   2:     0     0     0     0     0     0     0     0        0      0 42949672    0   0  0.0000
   3:     0     0     0     0     0     0     0     0        0      0 42949672    0   0  0.0000
   4:     0     0     0     0     0     0     0     0        0      0 42949672    0   0  0.0000
   5:     0     0     0   146 14211   300 90490    93 3298833504 146623392 3341783176  364  25  0.0000
   6:     0     0     0  1398  1136     0     0     0 80331456 2702656  2000000 1299   0  0.0000
   7:  3142 119872     0     0     0     0     0     0        0      0  2000000    0   0  0.0000
   Total bytes allocated    = 3502156736
   Dynamic-space-size bytes = 4294967296
GC control variables:
   *GC-INHIBIT* = false
   *GC-PENDING* = false
   *STOP-FOR-GC-PENDING* = false
2018-01-08T15:07:18.612000-05:00 ERROR Error while formating a row from "gitlabhq_production.merge_request_diffs":
2018-01-08T15:07:18.612000-05:00 ERROR A SB-KERNEL::HEAP-EXHAUSTED-ERROR condition without bindings for heap statistics.  (If
you did not expect to see this message, please report it.
Heap exhausted during allocation: 32768 bytes available, 206256 requested.
 Gen  StaPg UbSta LaSta Boxed Unbox    LB   LUB !move    Alloc  Waste     Trig   WP GCs Mem-age
   0:  9100     0 131070  1018     0  2985     0     0 123084672 8085632 42949672    0   0  0.0000
   1:     0     0     0     0     0     0     0     0        0      0 42949672    0   0  0.0000
   2:     0     0     0     0     0     0     0     0        0      0 42949672    0   0  0.0000
   3:     0     0     0     0     0     0     0     0        0      0 42949672    0   0  0.0000
   4:     0     0     0     0     0     0     0     0        0      0 42949672    0   0  0.0000
   5:     0     0     0   146 14211   300 90490    93 3298833504 146623392 3341783176  363  25  0.0000
   6:     0     0     0  1398  1136     0     0     0 80331456 2702656  2000000 1299   0  0.0000
   7:  3142 119872     0     0     0     0     0     0        0      0  2000000    0   0  0.0000
   Total bytes allocated    = 3502249632
   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 {100667C763}>:
  Heap exhausted (no more space for allocation).
32768 bytes available, 206256 requested.

PROCEED WITH CAUTION.
^CKABOOM!
FATAL error: Interactive interrupt at #x7FFFF71EA657.
An unhandled error condition has been signalled:
   Interactive interrupt at #x7FFFF71EA657.

[dimitri]

Hi, which version of pgloader are you using? please try with a local build of the current tree, we might have fixed some related problems...

You can try to reduce batch size, concurrency level, or compile with CCL which is known to be slower and much more efficient when it comes to handling memory and Garbage Collection.

[richardji7]

Hi Dimitri,

Thanks for the tips. I built from source using git clone from github just a few days ago. I am using the commands below. It is already concurreny 1. I will try the CCL.

gitlab-conversion:~/git/pgloader/build/bin # ./pgloader --version
pgloader version "3.4.b683292"
compiled with SBCL 1.4.1-100.1-suse

LOAD DATABASE
     FROM mysql://###:###@172.17.119.201/gitlabhq_production
     INTO postgresql://gitlab_app_test:###@172.17.119.201/gitlab_test

WITH include no drop, truncate, disable triggers, create no tables,
     create no indexes, preserve index names, no foreign keys,
     data only, workers = 8, concurrency = 1,
     multiple readers per thread, rows per range = 50000

;

[richardji7]

Getting an error from CCL. libsqlite is installed and I compiled with SBCL.

gitlab-conversion:~/git/pgloader # make CL=ccl pgloader
ccl --no-init --load build/quicklisp/setup.lisp                   \
             --eval '(push "/root/git/pgloader/" ql:*local-project-directories*)' \
             --eval '(ql:quickload "pgloader")'                       \
             --eval '(quit)'
To load "pgloader":
  Load 1 ASDF system:
    pgloader
; Loading "pgloader"
...
> Error: Unable to load any of the alternatives:
>           ("libsqlite3.so.0" "libsqlite3.so")
> While executing: CFFI::FL-ERROR, in process listener(1).
> Type :GO to continue, :POP to abort, :R for a list of available restarts.
> If continued: Skip evaluation of (ql:quickload "pgloader")
> Type :? for other options.
1 > ^\
Makefile:104: recipe for target 'build/libs.stamp' failed
make: *** [build/libs.stamp] Quit (core dumped)
gitlab-conversion:~/git/pgloader # ll /usr/lib64/libsqlite3.so*
lrwxrwxrwx 1 root root     19 Jan  4 14:44 /usr/lib64/libsqlite3.so -> libsqlite3.so.0.8.6
lrwxrwxrwx 1 root root     19 Jul 25 02:51 /usr/lib64/libsqlite3.so.0 -> libsqlite3.so.0.8.6
-rwxr-xr-x 1 root root 813912 Apr  4  2017 /usr/lib64/libsqlite3.so.0.8.6

[richardji7]

Good news. I was able to get it running without error after recompile with DYNSIZE=8192 without CCL. Although I am still interested to know why CCL compile failed with libsqlite error.

[dimitri]

I think you've been using the 32-bits version of CCL, and the 64-bits version of SBCL and more importantly libsqlite3.so. You can use make CL=ccl64 to be sure to use the 64 bits version.

I'm glad it works with 8GB of memory!

[revolunet]

@richardji7 can you please share the command you used to build with higher DYNSIZE ?

[richardji7]

make DYNSIZE=8192 pgloader

[MRigal]

@dimitri just to let you know, I had the same issue on MacOS with Mojave, installing via Brew the 3.5.2 version.

Then I tried to build using brew and --HEAD and I get the following:

buildapp    --require sb-posix --require sb-bsd-sockets --require sb-rotate-byte                        \
                       --sbcl sbcl                            \
                       --load-system pgloader               \
                       --load src/hooks.lisp                   \
                       --entry pgloader:main                   \
                       --dynamic-space-size 4096         \
                       --compress-core                    \
                       --output build/bin/pgloader
;; loading system "pgloader"
Fatal SIMPLE-ERROR:
  Compilation failed: Undefined instruction: MOVZXD in
 (INST MOVZXD RESULT MEMREF) in /private/tmp/pgloader-20190115-29109-pqqbc4/lib/nibbles/sbcl-opt/x86-64-vm.lisp
make: *** [pgloader-standalone] Error 1

[dimitri]

Hi @MRigal ; I don't maintain the brew image. One of the build-dependencies of pgloader is having that bug, and needs to be updated. In the meanwhile, consider building pgloader from the current pre-release available at https://github.com/dimitri/pgloader/releases/tag/v3.6.0.