Pgloader: Heap exhausted, game over
I've ran pgloader with --debug option and I got this error
Heap exhausted during garbage collection: 48 bytes available, 64 requested.
Gen StaPg UbSta LaSta LUbSt Boxed Unboxed LB LUB !move Alloc Waste Trig WP GCs Mem-age
0: 0 0 0 0 0 0 0 0 0 0 0 42949672 0 0 0,0000
1: 0 0 0 0 0 0 0 0 0 0 0 42949672 0 0 0,0000
2: 47576 50793 0 0 11214 24888 40 0 18 1181579552 2721504 815524392 0 1 1,5324
3: 130843 131071 0 0 33944 56370 715 0 225 2977160640 5677632 2000000 22947 0 1,2470
4: 0 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 0 2000000 0 0 0,0000
6: 0 0 0 0 2744 1157 0 0 0 127827968 0 2000000 2568 0 0,0000
Total bytes allocated = 4286568160
Dynamic-space-size bytes = 4294967296
GC control variables:
*GC-INHIBIT* = true
*GC-PENDING* = true
*STOP-FOR-GC-PENDING* = false
fatal error encountered in SBCL pid 24973(tid 140736921958144):
Heap exhausted, game over.
maksimf
All 37 comments
You can try the batch options (batch size, batch rows and batch concurrency), you can try compiling with CCL instead (see http://ccl.clozure.com), or you can tell me more about the use case...
dimitri
on 18 Jan 2016
You said:
It is possible that you have a single giant field, I had reports about that previously. Can you see about that?
I guess this is not the case, since I have 13 distinct data types in mysql DB, which I'm trying to import (it's 25GB):
int(11)
bigint(20)
datetime
varchar(255)
varchar(191)
tinyint(1)
text
date
timestamp
tinytext
float
decimal(5,2)
varchar(40)
AFAIK, nothing should be large here.
I'm trying adjusting batch size, rows and concurrency now.
maksimf
on 19 Jan 2016
Still failing
Heap exhausted during garbage collection: 64 bytes available, 160 requested.
Gen StaPg UbSta LaSta LUbSt Boxed Unboxed LB LUB !move Alloc Waste Trig WP GCs Mem-age
0: 0 0 0 0 0 0 0 0 0 0 0 42949672 0 0 0,0000
1: 0 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 0 42949672 0 0 0,0000
3: 25910 129567 0 0 23454 43786 0 0 19 2200491648 2828672 1317957352 0 1 1,3845
4: 88127 131071 0 0 24733 35198 0 0 108 1960814912 3004096 2000000 14240 0 0,7557
5: 0 0 0 0 0 0 0 0 0 0 0 2000000 0 0 0,0000
6: 0 0 0 0 2744 1157 0 0 0 127827968 0 2000000 2564 0 0,0000
Total bytes allocated = 4289134528
Dynamic-space-size bytes = 4294967296
GC control variables:
*GC-INHIBIT* = true
*GC-PENDING* = true
*STOP-FOR-GC-PENDING* = false
fatal error encountered in SBCL pid 13858(tid 140736921958144):
Heap exhausted, game over.
Few last data entries
2016-01-19T06:44:48.434000Z DATA > #(55 52 53 51 49 56 48 9 55 50 49 57 54 56 56 9 50 52 48 57 57 54 49 56 52 9
50 48 49 53 45 49 48 45 48 57 32 49 56 58 49 52 58 52 51 9 50 48 49 54 45
48 49 45 49 48 32 50 48 58 52 56 58 48 53 9 92 78 9 92 78 10)
2016-01-19T06:44:48.434000Z DATA < #("8171327" "195073979" "656088463" "2015-10-11 09:04:45"
"2016-01-15 03:28:04" NIL NIL)
2016-01-19T06:44:48.434000Z DATA > #(55 52 53 51 49 56 49 9 55 50 49 57 54 56 56 9 50 52 48 57 56 52 55 56 54 9
50 48 49 53 45 49 48 45 48 57 32 49 56 58 49 52 58 52 51 9 50 48 49 54 45
48 49 45 49 48 32 50 48 58 52 56 58 48 54 9 92 78 9 92 78 10)
2016-01-19T06:44:48.435000Z DATA > #(55 52 53 51 49 56 50 9 55 50 49 57 54 56 56 9 50 49 56 50 52 48 52 50 51 9
50 48 49 53 45 49 48 45 48 57 32 49 56 58 49 52 58 52 51 9 50 48 49 54 45
48 49 45 49 48 32 50 48 58 52 56 58 48 54 9 92 78 9 92 78 10)
2016-01-19T06:44:48.435000Z DATA < #("8171328" "195073979" "428143410" "2015-10-11 09:04:45"
"2016-01-15 03:28:05" NIL NIL)
2016-01-19T06:44:48.435000Z DATA < #("8171329" "195073979" "199438591" "2015-10-11 09:04:46"
"2016-01-15 03:28:05" NIL NIL)
2016-01-19T06:44:48.435000Z DATA > #(55 52 53 51 49 56 51 9 55 50 49 57 54 56 56 9 50 52 48 53 57 53 54 56 57 9
50 48 49 53 45 49 48 45 48 57 32 49 56 58 49 52 58 52 51 9 50 48 49 54 45
48 49 45 49 48 32 50 48 58 52 56 58 48 54 9 92 78 9 92 78 10)
2016-01-19T06:44:48.435000Z DATA > #(55 52 53 51 49 56 52 9 55 50 49 57 54 56 56 9 50 51 52 55 54 51 51 56 50 9
50 48 49 53 45 49 48 45 48 57 32 49 56 58 49 52 58 52 51 9 50 48 49 54 45
48 49 45 49 48 32 50 48 58 52 56 58 48 54 9 92 78 9 92 78 10)
My last attempt was with
(defparameter *copy-batch-rows* 500
"How many rows to batch per COPY transaction.")
(defparameter *copy-batch-size* (* 1 1024 1024)
"Maximum memory size allowed for a single batch.")
(defparameter *concurrent-batches* 1
"How many batches do we stack in the queue in advance.")
Still the same.
Also, I tried to build with ccl, I get error as well (different, though)
maksimf
on 19 Jan 2016
You know you can change that right in the WITH clause in the .load file nowadays? Which version of pgloader are you using?
dimitri
on 19 Jan 2016
Can you please provide an example of how to set this params with WITH clause? pgloader --with "copy-batch-rows = 500" or what?
pgloader version "3.3.44a2bd1"
compiled with SBCL 1.2.8-1.el7
You mean examples as in the docs ? ;-)
--with "batch rows = 500"
There's still something quite fishy here, is it possible that you provide me with a dump of your mysql database (or just enough of it so that I can reproduce the problem here), or ssh level access to a Cloud instance where I can easily reproduce that? If that works for you, please send me the details in private by email, on the email listed on my github profile, thanks...
dimitri
on 19 Jan 2016
Looks like I've figured out what is the problem. Default temp directory for pgloader is located under /tmp, which is tmpfs in my case, so the process runs out of RAM. I'm trying to compile pgloader with other location for temporary files.
maksimf
on 19 Jan 2016
Just use the --root-dir option (-D) in the command line.
dimitri
on 19 Jan 2016
I've tried to import 1 table (~ 900MB, 1M rows), still the same error:
pgloader --verbose --debug --root-dir=/root/pgloader/tmp --logfile=/root/pgloader/pgloader.log --with "batch rows = 500" mysql.load
At the moment if you're using a .load file argument then the --with options are not used at all. Didn't realize that before...
dimitri
on 20 Jan 2016
Won't work either, even with only 900MB of data and one table (with batches = 500)
maksimf
on 20 Jan 2016
I've managed to import my database using another tool and dumping tables one-by-one, looks like my DB was fine, there is some error in pgloader, I suppose.
maksimf
on 22 Jan 2016
Didn't have time to try with your dataset yet, sorry about that... still on my list.
dimitri
on 22 Jan 2016
No worries, as soon as you have time
maksimf
on 22 Jan 2016
So I could reproduce the error with the default batch size, and with batch size of 1000, I got the load running to completion with a memory usage oscillating between 200MB and 400MB on my box:
~/dev/pgloader ./build/bin/pgloader --with "batch rows = 1000" mysql://root@localhost/skippr pgsql:///skippr
2016-01-25T00:18:06.161000+01:00 LOG Main logs in '/private/tmp/pgloader/pgloader.log'
2016-01-25T00:18:06.165000+01:00 LOG Data errors in '/private/tmp/pgloader/'
table name read imported errors total time read write
----------------------- --------- --------- --------- -------------- --------- ---------
fetch meta data 6 6 0 0.556s
create, drop 0 0 0 0.258s
----------------------- --------- --------- --------- -------------- --------- ---------
posts 994163 994163 0 43.193s 46.827s 43.192s
----------------------- --------- --------- --------- -------------- --------- ---------
COPY Threads Completion 3 3 0 1m7.498s
Index Build Completion 5 5 0 49.297s
Create Indexes 5 5 0 1m31.614s
Reset Sequences 0 0 0 0.268s
Primary Keys 1 1 0 0.019s
Foreign Keys 0 0 0 0.000s
Install comments 0 0 0 0.000s
----------------------- --------- --------- --------- -------------- --------- ---------
Total import time 994163 994163 0 1m58.295s 46.827s 43.192s
So, does it looks like a bug? Or should i just try to adjust the params and run pgloader again and we can close this issue?
maksimf
on 25 Jan 2016
It's a bug indeed, but I would take it to the SBCL guys where they maintain and improve the Garbage Collector. The way out implemented in pgloader is to tweak the batch size, and we are yet to understand why that method is ineffective on your side. Which version are you using?
One problem we still have in pgloader now is that if you use --with on the command line _and_ a .load command file, then the --with options are silently ignored. Will improve that parts.
dimitri
on 25 Jan 2016
My version is
pgloader version "3.3.3277451"
compiled with SBCL 1.2.8-1.el7
maksimf
on 25 Jan 2016
Ok, I think the problem you had is that you used --with rather than a WITH option in your .load file. Could you try that (add a WITH batch rows = 1000 in your command file) and confirm?
dimitri
on 25 Jan 2016
Of course, let me try that
maksimf
on 25 Jan 2016
I've tried with WITH batch rows = 500 already and failed. Should I try with 1000?
maksimf
on 25 Jan 2016
Rather 100. There's something strange here :/
dimitri
on 25 Jan 2016
I've reduced batch rows to 10 and moved --root-dir for extra space and I'm also seeing this error. Table has 10,000,000 rows, but isn't very wide at 16 columns. The server has 4GB of RAM.
2016-03-04T10:24:55.234000+01:00 DATA < #("131498" "2014-05-13 12:11:04" "2013-11-15" "MT" "0700288000494" NIL NIL
NIL "DE000CB83CE3EUR" "10000.0000" "10000.0000" "10553.6000" "EUR"
"2011-03-22" #(1) "10000.0000")
2016-03-04T10:24:55.234000+01:00 DATA > #(49 51 48 54 48 49 9 50 48 49 52 45 48 53 45 49 51 32 49 50 58 49 49 58 48
52 9 50 48 49 51 45 49 49 45 49 53 9 77 84 9 48 53 48 49 55 50 49 48 48 48
49 54 48 9 92 78 9 92 78 9 92 78 9 48 80 48 48 48 48 87 51 75 90 9 51 48 46
48 48 48 48 9 51 48 46 48 48 48 48 9 49 51 50 53 46 52 48 48 48 9 85 83 68
9 50 48 49 51 45 48 57 45 49 48 9 116 9 51 48 46 48 48 48 48 10)
2016-03-04T10:24:55.234000+01:00 DATA > #(49 51 48 54 49 49 9 50 48 49 52 45 48 53 45 49 51 32 49 50 58 49 49 58 48
52 9 50 48 49 51 45 49 49 45 49 53 9 77 84 9 48 53 48 49 57 57 49 48 48 48
50 57 48 9 92 78 9 92 78 9 92 78 9 48 80 48 48 48 48 48 48 71 89 9 48 46 48Heap exhausted during garbage collection: 16 bytes available, 64 requested.
Gen StaPg UbSta LaSta LUbSt Boxed Unboxed LB LUB !move Alloc Waste Trig WP GCs Mem-age
0: 0 0 0 0 0 0 0 0 0 0 0 42949672 0 0 0.0000
1: 0 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 0 42949672 0 0 0.0000
3: 58080 102974 0 0 22489 49866 0 0 0 2367699424 3229216 2000000 0 0 0.9163
4: 0 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 0 2000000 0 0 0.0000
6: 0 0 0 0 2548 1041 0 0 0 117604352 0 2000000 2257 0 0.0000
Total bytes allocated = 4289307792
Dynamic-space-size bytes = 4294967296
GC control variables:
*GC-INHIBIT* = true
*GC-PENDING* = true
*STOP-FOR-GC-PENDING* = false
fatal error encountered in SBCL pid 1807(tid 140737189607168):
Heap exhausted, game over.
Welcome to LDB, a low-level debugger for the Lisp runtime environment.
ldb>
Built from git on centos
Linux xxxxxx 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
pgloader --version
pgloader version "3.3.eaa5807"
compiled with SBCL 1.1.14
emailgregn
on 4 Mar 2016
You can try to reduce batch concurrency which defaults to 10, down to maybe 3. Also please try to see if there's a single value in the source database that wouldn't fit in RAM, we don't have value (or row) streaming (yet) in pgloader, only batch streaming (a set of rows at a time).
dimitri
on 4 Mar 2016
Thanks, I tried that without success.
WITH include drop, create tables, no truncate,
batch rows = 1000, batch concurrency = 3,
create indexes, reset sequences, foreign keys
Result
2016-03-04T15:18:35.085000+01:00 DATA > #(49 54 54 52 51 56 9 50 48 49 52 45 48 53 45 49 51 32 49 50 58 49 49 58 53
53 9 50 48 49 51 45 49 50 45 49 54 9 77 84 9 77 54 48 50 50 50 48 48 48 48
52 48 54 9 92 78 9 92 78 9 92 78 9 85 83 54 49 55 52 54 66 68 74 50 54 85
83 68 9 49 48 48 48 48 46 48 48 48 48 9 49 48 48 48 48 46 48 48 48 48 9 57
54 54 57 46 55 48 48 48 9 85 83 68 9 50 48 49 51 45 48 50 45 50 53 9 116 9
49 48 48 48 48 46 48 48 48 48 10)
Heap exhausted during garbage collection: 0 bytes available, 32 requested.
Gen StaPg UbSta LaSta LUbSt Boxed Unboxed LB LUB !move Alloc Waste Trig WP GCs Mem-age
0: 0 0 0 0 0 0 0 0 0 0 0 42949672 0 0 0.0000
1: 0 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 0 42949672 0 0 0.0000
3: 33447 92333 0 0 18999 48073 0 0 0 2194782208 3033088 2000000 0 0 1.0467
4: 0 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 0 2000000 0 0 0.0000
6: 0 0 0 0 2548 1041 0 0 0 117604352 0 2000000 2258 0 0.0000
Total bytes allocated = 4289119904
Dynamic-space-size bytes = 4294967296
GC control variables:
*GC-INHIBIT* = true
*GC-PENDING* = true
*STOP-FOR-GC-PENDING* = false
fatal error encountered in SBCL pid 3181(tid 140737194129152):
Heap exhausted, game over.
Welcome to LDB, a low-level debugger for the Lisp runtime environment.
ldb>
I thought maybe it was something about the bit column causing problems so I created a view with just a single unsigned int column and asked pg_loader to MATERIALIZE views. Still running out of memory.
Heres the .load file ( excluding connections):
WITH include drop, create tables, no truncate,
batch rows = 10000, batch concurrency = 10,
create indexes, reset sequences, foreign keys
--SET maintenance_work_mem to '1024MB', work_mem to '128MB', search_path to 'finwms'
SET search_path to 'finwms'
CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
type date drop not null drop default using zero-dates-to-null
MATERIALIZE VIEWS vw_holding
INCLUDING ONLY TABLE NAMES MATCHING 'emptytable001_testing'
BEFORE LOAD DO
$$ CREATE SCHEMA IF NOT EXISTS finwms; $$;
Heres the view definition:
CREATE VIEW vw_holding AS
SELECT `holding`.`holding_id`
FROM `finwms`.`holding`;
< edit >Dump was wrong copy pasta, results unchanged though< / edit >
emailgregn
on 7 Mar 2016
same problem for me.
spent almost 3 to 4 days with multiple options nothing worked out other this memory error.
vadirajks
on 29 Mar 2016
This is with batch concurrency = 1, but a single load script per schema.
I managed to get the whole db loaded by creating a load file per table.
Interestingly, i successfully stepped batch rows up to 20000 and batch
concurrency to 4 with the table by table approach.
On Fri, 4 Mar 2016 15:03 Dimitri Fontaine, [email protected] wrote:
You can try to reduce batch concurrency which defaults to 10, down to
maybe 3. Also please try to see if there's a single value in the source
database that wouldn't fit in RAM, we don't have value (or row) streaming
(yet) in pgloader, only batch streaming (a set of rows at a time).—
Reply to this email directly or view it on GitHub
https://github.com/dimitri/pgloader/issues/327#issuecomment-192294403.
emailgregn
on 29 Mar 2016
By default we have workers = 4. Can you try with a lower value for that? Doing the load one table at a time should be equivalent to having workers = 3. The batch concurrency = 1 option should only allow a single extra batch to wait in memory for the previous table loading, tho...
If that still fails, then I'll see about providing a _one table at a time please_ option to get back the old code path where we would wait for each table to be done with before continuing to the next, while still allowing parallel index creation of the previously loaded table.
dimitri
on 29 Mar 2016
I have 4GB RAM in staging xen vm for both mysql, postgres for testing and having mysql db size is around 45 GB.
Heap exhausted during garbage collection: 0 bytes available, 80 requested.
Gen StaPg UbSta LaSta LUbSt Boxed Unboxed LB LUB !move Alloc Waste Trig WP GCs Mem-age
0: 0 0 0 0 0 0 0 0 0 0 0 42949672 0 0 0.0000
1: 0 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 0 42949672 0 0 0.0000
3: 86918 109414 0 0 18028 58316 0 0 59 2498609840 3030352 2000000 0 0 0.9364
4: 0 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 0 2000000 0 0 0.0000
6: 0 0 0 0 2488 1133 0 0 0 118652928 0 2000000 2307 0 0.0000
Total bytes allocated = 4289534496
Dynamic-space-size bytes = 4294967296
GC control variables:
*GC-INHIBIT* = true
*GC-PENDING* = true
*STOP-FOR-GC-PENDING* = false
fatal error encountered in SBCL pid 14171(tid 140736920909568):
Heap exhausted, game over.
Welcome to LDB, a low-level debugger for the Lisp runtime environment.
[root@NOC1 ~]# pgloader --verbose --debug /root/db.load >> /home/pgloader/pgloader-vadi1.log 2>&1
WITH include drop, truncate, disable triggers, create no indexes, create tables, reset sequences, batch rows = 1000, batch concurrency = 1
SET maintenance_work_mem to '512MB',
work_mem to '16MB',
search_path to 'adx'
CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
type date drop not null drop default using zero-dates-to-null
BEFORE LOAD DO
$$ create schema if not exists stagingdb; $$;
sorry to say , I don’t know further how to troubleshot and to make this work in our environment.
Regards,
Vadiraj
vadirajks
on 30 Mar 2016
BTW, did you try building with Clozure CL? It's known to offer a much better Garbage Collector...
dimitri
on 7 Apr 2016
I just added a Dockerfile showing how to build using CCL to make it easier to try that option. If your local environment allows for it, please consider changing the DYNSIZE to something like 1GB or more rather than only 256MB as it is now, it's just that my environment here wouldn't allow for more :/
dimitri
on 8 Apr 2016
I just scheduled automated builds of docker images using CCL, please consider
docker pull dimitri/pgloader:ccl.latest
And trying again.
dimitri
on 13 Apr 2016
We hit this issue a month ago. It started happening once every 2-3 days.
11 days ago, I recompiled using CCL and haven't experienced any issues since.
reist
on 15 May 2016
Thanks for the feedback @reist!
Given that I think the CCL build should be a good solution for everyone experiencing this issue, I'm closing this Pull Request. Consider opening again if you have an idea how to fix that on SBCL.
dimitri
on 16 May 2016
Related issues
fernandoluizao
·
4Comments
AnrDaemon
·
7Comments
chrisspen
·
8Comments
toobulkeh
·
3Comments
kim-ae
·
5Comments