0

The database I would like to import is approximately 55GB in binary format, about 36 GB exported by mysqldump. But as we know even the postgres compatible mysqldump is not 100% compatible, so I'm trying pgloader:

pgloader mysql://user:pw@localhost/db181126 postgresql://user:pw@localhost/db181126

The command creates the 330 tables in the PostgreSQL schema, and starts to iterate through. There are warnings because Django generates very long index names which need to be truncated, but that would not be a problem. However at the first really big table the command errors out:

2018-11-27T17:49:25.660000Z ERROR A thread failed with error: An I/O error occurred: undocumented reason (return code: 5). SSL error queue is empty. 2018-11-27T17:49:25.660000Z ERROR A thread failed with error: An I/O error occurred: undocumented reason (return code: 5). SSL error queue is empty. 2018-11-27T17:49:25.660000Z FATAL An unhandled error condition has been signalled: An I/O error occurred: undocumented reason (return code: 5). SSL error queue is empty.

I don't see issue related to that in https://github.com/dimitri/pgloader/issues . I'm not sure if it's a buffer size or some kind of network timeout due to the size.

My system is Debian Stretch based Devuan ascii, I'm running a 4.18.0 kernel. MySQL version: 5.7.24, PostgreSQL version 9.6.10. I needed to increase buffer sizes for MySQL to be able to handle the import of 36 GB mysqldump.

I tuned several memory and cache/buffer related settings severely up to match a 32GB box (my developer laptop). The crash came later, but harder, and it's a dreaded OOM.

[23015.687178] Out of memory: Kill process 16091 (postgres) score 149 or sacrifice child
[23015.687184] Killed process 16091 (postgres) total-vm:6554284kB, anon-rss:2100600kB, file-rss:0kB, shmem-rss:2826676kB
[23015.821162] oom_reaper: reaped process 16091 (postgres), now anon-rss:0kB, file-rss:0kB, shmem-rss:2826676kB

The above is towards the end of the crash pattern in dmesg. I wonder if this is clearly just OOM or there can be any file system error contributing, since ext4_filemap_fault is mentioned on the call stack. Since this is a laptop with an SSD no swap is configured (that could wear out the SSD).

Out of memory: Kill process 7201 (chromium) score 300 or sacrifice child
[23005.751596] Killed process 7201 (chromium) total-vm:1383968kB, anon-rss:18160kB, file-rss:0kB, shmem-rss:808kB
[23005.759355] oom_reaper: reaped process 7201 (chromium), now anon-rss:0kB, file-rss:0kB, shmem-rss:120kB
[23006.811391] postgres invoked oom-killer: gfp_mask=0x6200ca(GFP_HIGHUSER_MOVABLE), nodemask=(null), order=0, oom_score_adj=0
[23006.811392] postgres cpuset=/ mems_allowed=0
[23006.811396] CPU: 12 PID: 16090 Comm: postgres Tainted: G           O      4.18.0 #1
[23006.811397] Hardware name: ASUSTeK COMPUTER INC. GL702ZC/GL702ZC, BIOS GL702ZC.305 05/10/2018
[23006.811398] Call Trace:
[23006.811405]  dump_stack+0x5c/0x7b
[23006.811408]  dump_header+0x6b/0x28c
[23006.811410]  oom_kill_process+0x28f/0x490
[23006.811412]  ? oom_badness+0x23/0x130
[23006.811413]  out_of_memory+0x122/0x4b0
[23006.811415]  __alloc_pages_slowpath+0xd60/0xe60
[23006.811417]  ? get_page_from_freelist+0x18a/0x1300
[23006.811419]  __alloc_pages_nodemask+0x241/0x280
[23006.811421]  filemap_fault+0x3a1/0x730
[23006.811423]  ? alloc_set_pte+0x3ac/0x540
[23006.811425]  ? filemap_map_pages+0x184/0x390
[23006.811428]  ext4_filemap_fault+0x38/0x57
[23006.811430]  __do_fault+0x2d/0x110
[23006.811431]  __handle_mm_fault+0xc00/0x1120
[23006.811433]  handle_mm_fault+0x10f/0x230
[23006.811436]  __do_page_fault+0x27d/0x510
[23006.811438]  do_page_fault+0x46/0x160
[23006.811440]  ? page_fault+0x8/0x30
[23006.811442]  page_fault+0x1e/0x30
[23006.811445] RIP: 0033:0x55b8ad335afa
[23006.811445] Code: Bad RIP value.
[23006.811450] RSP: 002b:00007ffe5985bb70 EFLAGS: 00010246
[23006.811451] RAX: 00007f83f76f7948 RBX: 00007ffe5985bcb0 RCX: 00007f83f76f7958
[23006.811452] RDX: 00007f83f76f7938 RSI: 0000000000000001 RDI: 00000000007ba6d8
[23006.811452] RBP: 00007ffe5985bd10 R08: 00007f85749d0ad0 R09: 00007ffe5985c240
[23006.811453] R10: 00007ffe5985c190 R11: 00007f85749d0aa8 R12: 00007ffe5985bbb0
[23006.811454] R13: 0000000000000008 R14: 0000000000000000 R15: 00007ffe5985be40
[23006.811455] Mem-Info:
[23006.811458] active_anon:7853946 inactive_anon:192019 isolated_anon:0
                active_file:151 inactive_file:0 isolated_file:0
                unevictable:12 dirty:0 writeback:0 unstable:0
                slab_reclaimable:26224 slab_unreclaimable:27989
                mapped:1132098 shmem:1133272 pagetables:41564 bounce:0
                free:49671 free_pcp:360 free_cma:0
Improve this question

1 Answer 1

Reset to default
0

I/O Error aka return code: 5 is an operating system error. So this could be a result of a storage/filesystem problem on the postgres side or a network error (on localhost - odd, and probably less likely). Does dmesg show kernel errors?

Improve this answer
3
  • I didn't see kernel errors in dmesg, but I'll certainly try again and watch out more for that.
    – Csaba Toth
    Commented Nov 30, 2018 at 18:41
  • I tuned the config file a little and tuned up several memory related settings since my developer laptop 32GB. With these the process hang in there for longer, but crashed at the same giant table. Close to the crash the system became sluggish and unresponsive. The dmesg shows out of memory kernel crash logs which informs me that several processes were sacrificed to keep the whole OS from crashing, which includes PostgreSQL itself as well. I'll include more details in the question.
    – Csaba Toth
    Commented Dec 7, 2018 at 22:48
  • try adding swap
    – Jasen
    Commented Jan 21, 2020 at 3:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.