MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?

Question

I'm using a MySQL database to store files.

The table I'm using is structured as follows:

+--------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| AttachmentID | int(11) | NO | PRI | NULL | |
| Data | longblob | NO | | NULL | |
+--------------+----------+------+-----+---------+-------+

The INSERT command I'm using is as simple as possible:

INSERT INTO table (AttachmentID, Data) VALUES (attachmentID, attachmentData);

Obviously, where attachmentID is an int, and attachmentData is a large byte array.

Having read several guides on how to store files in a database, I increased the max_allowed_packet setting in the config file to "512M", more than enough for the files that I would actually be inserting.

This worked fine for several files of 30MB - 40MB. However, now that I am inserting larger files in the 90MB+ range, I am recieving an "Out of Memory" exception, with a number of bytes needed equal to the size of the file I tried to insert.

The server is a virtual server, with 4GB allocated and nothing else running that could be interfering.

Why do I get an Out of Memory error for the larger files, and not the smaller? Where is this file-size limitation arising?

I've included my config file below:

[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 256M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
max_allowed_packet = 512M
ft_min_word_len=2
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

---

EDIT: I can successfully INSERT a file of size 41,586 KB, but not one of 44,119 KB. The limit is somewhere between them.

• Gaius: Do you have different size files available? I would be very interested to see if it's OK with 63M but fails on 65M.
• Ben Jenkinson: So far, it fails with a ~60MB file.
• Gaius: What does ulimit say, is there a limit on process size?
• Ben Jenkinson: Unlimited apparently.

Answer 1

I saw an interesting answer to a question about the biggest blob you may have

Here is the statement I saw in ServerFault

innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones

I did not see any InnoDB Settings in your my.cnf

I see you disabled the InnoDB Built-in and you plugged in another InnoDB

See if you can adjust innodb_log_file_size and innodb_log_buffer_size to accommodate.

Since you only have 4GB of RAM reserved, try making your InnoDB Log Files Much Bigger

Change innodb_log_file_size to 1G in /etc/my.cnf

1. service mysql stop
2. sleep 30
3. rm -f /var/lib/mysql/in_logfile[01]
4. service mysql start (recreates ib_logfile0 and ib_logfile1)

Give it a Try !!!

Answer 2

This is not the answer per se, but a very good advice for this topic:

Don't store files that big in a database.

Why are they supposed to be stored in a DB anyway? Accessing it via the filesystem is way more efficient to handle and you avoid massive bloat from the tables. If these files are changed often (don't know if this is the case in your scenario), there is also a massive load on the DB-server. Reading the files is also way faster since a webserver+filesystem is there for sending files to a client (or somewhere else).

It's is (IMO) a better practice to just store the path of the file in the database as a reference. If you do this for protecting your files, you should use either .htaccess or block direct file access to your files via the webserver itself and use a wrapper around these file (in PHP, something like ...file.php?file=myfile.pdf so that ACL and other cecks can be performed, reading that you use .NET, I am sure there is something like this available, too) or use other machenisms your server/language provides.

• Ben Jenkinson: There are pros and cons to both approaches. As politely as possible though, that is entirely beside the point. My question was not "Should I store files in a database?", but about a specific problem I am having now that I have.
• Michael McGowan: Is your issue with storing it in a DB merely the fact that the files are ~90 MB, or is it more to do with the structure of the table (an ID and a longblob)?
• DrColossos: As I said, this is not the answer to the question and I don't expect any rep from it. It was more a "thought" why i consider it bad. This referrs to the size of the file since this may result in extreme performance penalties and database bloat.
• reinierpost: @DrColossus: how do you guarantee consistency of your backups?
• DrColossos: @reinierpost we include our directory for files (it's usually a single directory with many subdirectories) in our backup routines. We usually use a CMS system that handles the consistency of the filesystem. Additionally, we don't care if there are too many files in the backup that might not have been removed via the CMS.

Answer 3

You should also check your front end application/programming language. For example, if you are using PHP, check the php.ini file (or ) for upload_max_filesize and post_max_size. Set these to your desired value: upload_max_filesize = 100M post_max_size = 100M.

• Ben Jenkinson: I'm using C# and ASP.NET. The equivalent setting in ASP.NET is maxRequestLength which is already set to 500MB. The error I'm getting appears to be thrown by MySQL, not the site.
• StanleyJohns: How much physical memory is available on the server? The attachment will be loaded into memory/temp area, before it goes to disk. This may be a limitation from MySQL's side.
• Ben Jenkinson: As I mentioned in the question, there is 4GB of memory available to the server. Hence my confusion when it has apparently run out.