Handling of huge Blobs in MySQL?

Question

How can I insert huge BLOBs into a MySQL database (InnoDB)?

Fields of type LONGBLOB support data sizes of up to 4 GB according to the MySQL manual. But how does data of such a huge size get into the database?

I tried to use

INSERT INTO table (bindata) VALUES ( LOAD_FILE('c:/tmp/hugefile') );

which fails if the size of hugefile is bigger than about 500 MB. I have set max_allowed_packet to an appropriate size; the value of innodb_buffer_pool_size doesn't seem to have an influence.

My server machine runs Windows Server 2003 and has 2 GB RAM. I'm using MySQL 5.0.74-enterprise-nt.

Answer 1

BLOBs are cached in memory, that's why you will have 3 copies of a BLOB as you are inserting it into a database.

Your 500 MB BLOB occupies 1,500 MB in RAM, which seems to hit your memory limit.