title: AWS Aurora out of memory error with large import
body: <p>I'm trying to import a database from a 4.5 GB <code>mysqldump</code> file into an AWS Aurora DB instance. There's ~80 tables in my DB and the largest table has ~13 million rows (the rest are much smaller). My dump file has multi-valued inserts which <code>mysqldump</code> has capped at 64MB each, as per the max_allowed_packet that I've specified in <code>my.cnf</code>.  </p>
  
  <p>I had various problems initially with the import, but I was able to fix those by setting Aurora parameter group options i.e.  </p>
  
  <blockquote>
    <p>max_allowed_packet = 1073741824 (1GB)<br>
    wait_timeout = 10800 (3 hours)<br>
    net_read_timeout = 10800<br>
    net_write_timeout = 10800<br>
    interactive_timeout = 10800   </p>
  </blockquote>
  
  <p>At first, my Aurora instance was a <code>db.t2.small</code> (2GB RAM), but when I tried the import e.g. <code>mysql -u ... mydb &lt; dump.sql</code> from an EC2 instance (<code>m3.medium</code>, 4GB RAM) the process failed after running for 1 minute. The RDS log tells me it was an out of memory error. I bumped the Aurora instance up to a db.t2.medium (4GB RAM), but the process failed again after about ~20 mins with the same kind of out of memory error.  </p>
  
  <p>I don't want to jump up to the next instance type (15GB RAM) but in any case, it doesn't make sense that I would have to. I've been importing the same <code>mysqldump</code> file regularly into a local MySQL server on the m3.medium EC2 instance I'm using and I've never had any problems. It takes ~40 mins to import.  </p>
  
  <p>Here's the Aurora error log from the last import I tried:</p>
  
  <pre><code>Available memory is low. Trying to avoid OOM crash: system KB: 4050724 available KB: 101748 low-threshold KB: 202536 decline query: no tune caches: no kill query: no kill connection: no
  OOM crash avoidance result: success: no num success: 0 system KB: 4050724 available KB: 111688 low-threshold KB: 202536 recovery time: 11 num declined query: 0 num killed query: 0 num killed connection: 0
  OOM crash avoidance result: success: yes num success: 1 system KB: 4050724 available KB: 529464 low-threshold KB: 202536 recovery time: 24 num declined query: 0 num killed query: 0 num killed connection: 0
  Available memory is low. Trying to avoid OOM crash: system KB: 4050724 available KB: 200956 low-threshold KB: 202536 decline query: no tune caches: no kill query: no kill connection: no
  OOM crash avoidance result: success: yes num success: 2 system KB: 4050724 available KB: 556020 low-threshold KB: 202536 recovery time: 5 num declined query: 0 num killed query: 0 num killed connection: 0
  Available memory is low. Trying to avoid OOM crash: system KB: 4050724 available KB: 170392 low-threshold KB: 202536 decline query: no tune caches: no kill query: no kill connection: no
  OOM crash avoidance result: success: yes num success: 3 system KB: 4050724 available KB: 554108 low-threshold KB: 202536 recovery time: 7 num declined query: 0 num killed query: 0 num killed connection: 0
  Available memory is low. Trying to avoid OOM crash: system KB: 4050724 available KB: 194900 low-threshold KB: 202536 decline query: no tune caches: no kill query: no kill connection: no
  OOM crash avoidance result: success: yes num success: 4 system KB: 4050724 available KB: 554340 low-threshold KB: 202536 recovery time: 8 num declined query: 0 num killed query: 0 num killed connection: 0
  Available memory is low. Trying to avoid OOM crash: system KB: 4050724 available KB: 198780 low-threshold KB: 202536 decline query: no tune caches: no kill query: no kill connection: no
  OOM crash avoidance result: success: no num success: 4 system KB: 4050724 available KB: 133160 low-threshold KB: 202536 recovery time: 11 num declined query: 0 num killed query: 0 num killed connection: 0
  OOM crash avoidance result: success: yes num success: 5 system KB: 4050724 available KB: 556540 low-threshold KB: 202536 recovery time: 25 num declined query: 0 num killed query: 0 num killed connection: 0
  Available memory is low. Trying to avoid OOM crash: system KB: 4050724 available KB: 170224 low-threshold KB: 202536 decline query: no tune caches: no kill query: no kill connection: no
  OOM crash avoidance result: success: yes num success: 6 system KB: 4050724 available KB: 579368 low-threshold KB: 202536 recovery time: 1 num declined query: 0 num killed query: 0 num killed connection: 0
  Available memory is low. Trying to avoid OOM crash: system KB: 4050724 available KB: 175612 low-threshold KB: 202536 decline query: no tune caches: no kill query: no kill connection: no
  &lt;jemalloc&gt;: Error in mmap(): err: 12, msg: Cannot allocate memory
  &lt;jemalloc&gt;: Error in malloc(): out of memory
  &lt;jemalloc&gt;: System-wide: MemTotal: 4050724kb, MemFree: 137440kb, Buffers: 20428kb, Cached: 62340kb, Active: 2188968kb, Dirty: 204kb, Inactive: 37228kb, Mapped: 41592kb
  &lt;jemalloc&gt;: terminating process due to out of resources
  10:58:03 UTC - mysqld got signal 6 ;
  This could be because you hit a bug. It is also possible that this binary
  or one of the libraries it was linked against is corrupt, improperly built,
  or misconfigured. This error can also be caused by malfunctioning hardware.
  We will try our best to scrape up some info that will hopefully help
  diagnose the problem, but since we have already crashed, 
  something is definitely wrong and this may fail.
  
  key_buffer_size=16777216
  read_buffer_size=262144
  max_used_connections=6
  max_threads=90
  thread_count=5
  connection_count=5
  It is possible that mysqld could use up to 
  key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 63814 K bytes of memory
  Hope that's ok; if not, decrease some variables in the equation.
  
  Thread pointer: 0x2ab59d623000
  Attempting backtrace. You can use the following information to find out
  where mysqld died. If you see no messages after this, something went
  terribly wrong...
  stack_bottom = 2ab524642c08 thread_stack 0x40000
  /rdsdbbin/oscar/bin/mysqld(my_print_stacktrace+0x2c)[0x9897ec]
  /rdsdbbin/oscar/bin/mysqld(handle_fatal_signal+0x491)[0x6f0651]
  /lib64/libpthread.so.0(+0xf5b0)[0x2ab5165405b0]
  /lib64/libc.so.6(gsignal+0x39)[0x2ab51925cbe9]
  /lib64/libc.so.6(abort+0x148)[0x2ab51925dfe8]
  /rdsdbbin/oscar/lib/libjemalloc.so(malloc+0x1226)[0x2ab5160f62e6]
  /rdsdbbin/oscar/bin/mysqld(my_malloc+0x25)[0x986525]
  /rdsdbbin/oscar/bin/mysqld(alloc_root+0x8f)[0x9824bf]
  /rdsdbbin/oscar/bin/mysqld[0x5b57a5]
  /rdsdbbin/oscar/bin/mysqld(_Z10MYSQLparsePv+0xc1ce)[0x826ade]
  /rdsdbbin/oscar/bin/mysqld(_Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx+0xb5)[0x772175]
  /rdsdbbin/oscar/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0xf2)[0x772502]
  /rdsdbbin/oscar/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xf43)[0x774003]
  /rdsdbbin/oscar/bin/mysqld(_ZN22OscarSchedulerConsumer7consumeEjj+0xd3)[0x803963]
  /rdsdbbin/oscar/bin/mysqld(_ZN22OscarSchedulerConsumer5startEv+0x98)[0x803a98]
  /rdsdbbin/oscar/bin/mysqld(_ZN22OscarSchedulerConsumer11drain_queueEPv+0x6a)[0x803cda]
  /lib64/libpthread.so.0(+0x7f18)[0x2ab516538f18]
  /lib64/libc.so.6(clone+0x6d)[0x2ab51930bb2d]
  
  Trying to get some variables.
  Some pointers may be invalid and cause the dump to abort.
  Query (2ab5ad400010): INSERT INTO `Documents` VALUES (478555572,150317,1321817,1,9,627609600,0,60,5471267,0,639014400,''),(478555571,150317,1321816,1,1,623980800,0,60,0,0,623980800,''),(478555575,150318,1321820,1,1,623980800,0,60,0,0,623980800,'')
  Connection ID (thread ID): 6
  Status: NOT_KILLED
  </code></pre>
  
  <p>Given that my EC2 instance with 4GB RAM can handle the import OK, surely this must be a configuration issue. Is there some other parameter group option(s) I can try changing? </p>
  
  <p>I've also tried disabling binary logging by setting the <code>binlog_format</code> parameter to <strong>OFF</strong> in my DB cluster parameter group (as per the instructions <a href="https://aws.amazon.com/premiumsupport/knowledge-center/enable-binary-logging-aurora/" rel="nofollow noreferrer">here</a>) and restarting the instance, but when I run the query <code>select @@binlog_format</code> I get the result <strong>STATEMENT</strong>.</p>