Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Handling of huge Blobs in MySQL?

Tags:

mysql

blob

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.

like image 651
oli_arborum Avatar asked Jun 03 '09 15:06

oli_arborum


1 Answers

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.

like image 96
Quassnoi Avatar answered Sep 19 '22 08:09

Quassnoi