3

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

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?

| improve this answer | |
  • Thanks for your response. I will try setting max_allowed_packet back to a normal value when I am back in the office - I didn't know it had anything to do with memory allocation, just wanted to fix MySQL server has gone away. I'm not sure where you got that table size figure from, but the SQL files are approximately a total of 300 GB in size. And all of the troubleshooting stuff I did was just purely troubleshooting, none of it fixed the issue anyway. Regarding the swap memory, I am not intending for MySQL to use it, I just allocated it so that it doesn't run out of memory and crash. – Billy Sep 9 '19 at 17:20
  • @Billy - The size of the dump file is approximately the size of the table(s) that it can create. (This estimate can be off by a factor of 2, possibly more.) Swap: Good; have it just in case, but avoid using it. – Rick James Sep 9 '19 at 22:17
  • @RickJames Good answer :-) I think you wrote a small typo: "Not not use swap space". Don't you mean "Do not use swap space" instead? – ricmarques Sep 10 '19 at 9:36
  • @RickJames So I have modified max_allowed_packet to be 256M, and the memory issue doesn't even get a chance to appear because I just get "MySQL server has gone away." Increasing it brings back the memory issue but mitigates that error message. – Billy Sep 10 '19 at 10:37
  • I solved the issue by splitting the INSERTs into groups of 500 rows. Thanks for the help guys :) – Billy Sep 10 '19 at 12:56
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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