MySQL using ridiculous amount of memory to import 1-2GB SQL files

Question

I have a MySQL server running on an Ubuntu 18.04 machine via Docker. The machine has 32 GB of RAM.

I have about 300 1-2GB SQL files which need importing into a database on the server, into a single table.

The database itself is completely empty, and there is only one table in total.

When I try to import some of the files, I can see memory usage spike to 32 GB (100%), and after allocating 100 GB of swap memory, the highest I've seen it go to is 60 GB (60 GB + 32 GB = 92 GB!!!)

Bearing in mind that MySQL is trying to use 92 GB of RAM to import a 1 GB SQL file into a single table on an empty database, what could it possibly be doing? It does not seem to be a memory leak, because once the file is finished importing, the memory becomes unallocated.

I should probably mention that MySQL is using a Docker volume which the host has direct file access to to store its data.

I have tried many different configurations to fix this problem, and additionally, I sometimes will get a MySQL server has gone away error.

I have tried the following:

• Changing the table from InnoDB to MyISAM and running ALTER TABLE tbl_name DISABLE KEYS
• Setting autocommit=0, unique_checks=0, foreign_key_checks=0
• Setting max_allowed_packet=999999999G and the relevant timeout variables to similar values
• Using mysqltuner.pl to generate some optimal InnoDB config options (shown below)

The SQL files themselves are literally a single INSERT statement with thousands of rows.

What can I do? I have considered chunking the INSERT statement into multiple different INSERTs, but this would require some extensive code refactoring due to the multiprocessing flow of my program which generates the SQL files.

my.cnf:

[mysqld]
max_allowed_packet = 9999999G
wait_timeout = 99999999999
key_buffer_size=10M
innodb_buffer_pool_size=21G
innodb_log_file_size=2G
innodb_buffer_pool_instances=21
innodb_file_per_table
net_read_timeout=999999999999
net_write_timeout=999999999999

pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Custom config should go here
!includedir /etc/mysql/conf.d/

The problem consistently persisted even after adding and fiddling with the innodb_* variables.

show global variables:

https://pastebin.com/raw/pXK4MgFb

Many thanks

Answer 1

No amount of RAM or disk space in the civilized world could hold what max_allowed_packet = 9999999G wants to allocate! Put that back to the default, or at most 256M.

The SQL files themselves are literally a single INSERT statement with thousands of rows.

This is optimal.

So, the total table size is somewhere around half a terabyte? The time taken to write that much disk is in the hours, depending on type of disk.

Not not use swap space; that only slows down MySQL. A lot. However, your settings do not seem to actually use the swap space. 21G for the buffer_pool should keep RAM usage well under the 32G physical size. However, you say it spiked to 100%? I guess I am missing something. If there is any swapping, MySQL will slow down, so lower the buffer_pool_size a little to avoid swapping.

autocommit=0 with InnoDB is not efficient -- If there is no COMMIT, then the data is inserted, then rolled back. If there is a COMMIT, then it has to do a lot of work to prepare for a rollback. Set to ON.

Is it disabling and re-enabling "keys" 300 times? That means that the indexes are rebuilt 300 times. Did the files run slower and slower as you went through through the 300?

Answer 2

From my experience I would say:

• during the import set:
  
  • max_allowed_packet=10G (to make memory available for the INSERT)
    
  • innodb_buffer_pool_size=10G (or less) to free up server memory, you can increase this later when you are using the server for queries, but for inserts it's almost useless
    
  • innodb_flush_log_at_trx_commit = 0 for better I/O performance (definitely remove or set to 1 or 2 for production use!)

Also, there is a problem with INSERT statements that are too big for one transaction. Basically, if your transaction is too big for the redo log, it will fail. Definitely set AUTOCOMMIT=1, remove any START TRANSACTION lines from your dump files and if this doesn't help, increase the log file size: https://dba.stackexchange.com/a/1265/12685

---

In addition I realized you are generating those SQL files programmatically. It will be more efficient to fire those inserts one by one to the database server, especially with the settings above, and it will be fast if you use INSERT DELAYED.