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
max_allowed_packet=999999999Gand 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.
[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
show global variables: