13

My MySQL instance is getting killed by the Linux oom_killer due to huge consumption of memory by mysql when inserting into longblob columns. This occurs when restoring a mysqldump which contains a very large longblob column.

I've run through things like this blog which suggests setting various read/write buffers to different sizes in order to limit memory consumption. But despite the mentioned script outputting a "TOTAL (MAX)" memory of 350MB after tweaks, mysql will still happily gobble up gigabytes of memory before eventually getting killed.

Here's a reproduction via Docker:

docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=foobar -d --name mysql-longblob mysql:5.7

mysql -h 127.0.0.1 -P 3306 -u root --password -e "CREATE DATABASE blobs; USE blobs; CREATE TABLE longblob_test (bigcol LONGBLOB NOT NULL) ENGINE = InnoDB;"

mysql -h 127.0.0.1 -P 3306 -u root --password -e \
 "SET GLOBAL max_allowed_packet=536870912;" # 512MB

mysql -h 127.0.0.1 -P 3306 -u root --password -D blobs -e "source ./500MB.sql"

In this case, docker stats reported consumption of ~1.8GB memory before it got OOM killed for exceeding its limit. At idle, mysql was reporting ~200MB memory.

Where 500MB.sql is a file inserting a 500MB blob of text in the shape:

INSERT INTO longblob_test VALUES ('500MB_WORTH_OF_TEXT_HERE')

So a couple of questions:

1) Why does mysql need to eat 1.6GB of memory to ingest a 500MB column?

2) How do I set a hard upper limit on mysql to prevent it ever exceeding "x" amount of memory?

P.S: You might be tempted to tell me that this is very silly, and you shouldn't be storing 500MB+ blobs in a database. I absolutely 100% agree! But it's an unfortunate situation, and rearchitecting the data storage is not possible in this situation.

5
  • Could you share your config data or the output of the scripts in the linked article? Jul 27 at 9:06
  • Which version of MySQL? Have you tested this with any other versions?
    – dbdemon
    Jul 27 at 11:26
  • @dbdemon The same behaviour was observed for both MySQL 5.7.35 and 8.0.26
    – cjheppell
    Jul 27 at 11:59
  • It is conceivable that the server stores multiple copies of your 500 MB BLOB while processing this statement, so if you were to limit memory consumption you would not be able to process it at all.
    – mustaccio
    Jul 27 at 12:18
  • @mustaccio that sounds exactly like what is happening... but I can't help but think that's a bad idea. It seems like at least 3 copies are held in memory during the insert (as observed by the 1.6GB memory consumed before getting oom killed), which makes it almost impossible unless I allocate an enormous amount of memory for this instance. Is this just "normal" behaviour for MySQL? If so, it seems... less than ideal.
    – cjheppell
    Jul 27 at 12:55
0

Are both the client and server in the same docker space?

I would expect the client to load 500MB, expand it into hex (1.0GB), send that to the server. Then the server might need similar space for parsing.

So, I guess 6x is needed. Note that the "log file" needs to be 10x the largest blob. So maybe 10x is the number you need. That's just on the server.

Re the 10x:

----- 2014-12-01 5.6.22 General Availability -- Bugs Fixed -- InnoDB -----

The MySQL 5.6.20 patch for Bug #16963396 / MySQL BLOB writes to 10% of the redo log file size. This limitation has been relaxed. Redo log BLOB writes are now limited to 10% of the total redo log size (innodb_log_file_size * innodb_log_files_in_group). As a result, innodb_log_file_size * innodb_log_files_in_group should be 10 times larger than the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). No action is required if innodb_log_file_size * innodb_log_files_in_group is already sufficiently large or if your tables contain no BLOB data. (Bug #73707, Bug #19498877)

References: See also: Bug #16963396.

----- 2014-12-01 5.6.22 General Availability -- Bugs Fixed -- InnoDB -----

The MySQL 5.6.20 patch for Bug #16963396 / MySQL BLOB writes to 10% of the redo log file size. This limitation has been relaxed. Redo log BLOB writes are now limited to 10% of the total redo log size (innodb_log_file_size * innodb_log_files_in_group).

As a result, innodb_log_file_size * innodb_log_files_in_group should be 10 times larger than the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). No action is required if innodb_log_file_size * innodb_log_files_in_group is already sufficiently large or if your tables contain no BLOB data. (Bug #73707, Bug #19498877)

2
  • > Are both the client and server in the same docker space? In my case, no. The server is running in docker and the client is running natively on my machine. > So, I guess 6x is needed. Forgive me if I'm being dense, but where does the 6x come from? > So maybe 10x is the number you need. Interesting. Could you share the documentation that explains the log file needing to be 10x the largest blob? And does MySQL really need to include "10x the largest blob" in memory at a given time?
    – cjheppell
    Jul 28 at 9:00
  • @cjheppell - 5.6.20 changelog: dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html - IIRC, the limit has been relaxed in some recent version. (No, I can't answer "why"; I mentioned it because it might relate to your Question.)
    – Rick James
    Jul 28 at 16:22
0

You might have to have the client doing the insert break up the huge blob into fragments. Depending on how you get that SQL file, this may or may not be practical. Also, this would require instead of using the mysql cli, using a programmatic MySQL client (such as from a Java, Python, or C program).

A starting point could be how in this other answer: https://stackoverflow.com/a/4846674/1680777, in which the user was able to circumvent the max_allowed_packet by having the MySQL client driver automatically segment the blob into small units during the insert operation.

I'm not positive that this will totally solve the memory issues, as there are things like various InnoDB and transaction buffers in play.

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.