CODEWITHSUNDEEP
mysqlblob
oli_arborum
oli_arborum

Reputation: 398

Handling of huge Blobs in MySQL?

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.

Upvotes: 8

Views: 6291

Answers (2)

Arne Burmeister
Arne Burmeister

Reputation: 20594

I do not know which client/api you use, but when trying to use blobs from own Java and Objective-C clients it seems, MySQL does not really support streaming of blobs. You need enough memory to hold the whole blob as byte array in ram (server and client side) more than once! Moving to a 64 bit linux helps, but is not desired solution.

MySQL is not made for BLOB handling (ok for small BLOBs :-). It occupies twice or three times the ram to be stored/read in the "BLOB".

You have to use another database like PostgreSQL to get real BLOB support, sorry.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425281

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.

Upvotes: 3

Related Questions