Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Do you have different size files available? I would be very interested to see if it's OK with 63M but fails on 65M. –  Gaius Apr 20 '11 at 11:02
    
So far, it fails with a ~60MB file. –  Ben Jenkinson Apr 20 '11 at 11:32
    
What does ulimit say, is there a limit on process size? –  Gaius Apr 20 '11 at 11:43
    
Unlimited apparently. –  Ben Jenkinson Apr 20 '11 at 11:51

4 Answers 4

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

share|improve this answer

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.

share|improve this answer
2  
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. –  Ben Jenkinson Apr 20 '11 at 10:49
    
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)? –  Michael McGowan Apr 20 '11 at 17:07
    
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. –  DrColossos Apr 20 '11 at 18:04
    
@DrColossus: how do you guarantee consistency of your backups? –  reinierpost Apr 7 at 10:29
    
@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. –  DrColossos Apr 7 at 11:29

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.

share|improve this answer
    
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. –  Ben Jenkinson Apr 20 '11 at 10:29
    
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. –  StanleyJohns Apr 20 '11 at 14:42
    
As I mentioned in the question, there is 4GB of memory available to the server. Hence my confusion when it has apparently run out. –  Ben Jenkinson Apr 20 '11 at 15:16

I hate speculation, so I won't speculate, but we ran into the same issue and it turned out to be we were copying a database from a 64-bit OS installed version of MySQL to a 32-bit OS installed version of MySQL. When the InnoDB file exceed 4GB we got the out of memory error.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.