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.