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 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.

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.