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