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.

@kekoav I know that in certain environments it is be better to store files in filesystem than in DB, but independent of this discussion I must know how it works to store data like this in order to be able to try it and form an opinion on advantages and disadvantages of both solutions (storing in filesystem and storing in DB). Thank you for your understanding in advance! — oli_arborum
– oli_arborum, Commented Jun 3, 2009 at 15:49

dda · Accepted Answer · 2015-07-27 17:12:41Z

3

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.

edited Jul 27, 2015 at 17:12

dda

6,2212 gold badges27 silver badges37 bronze badges

answered Jun 3, 2009 at 15:47

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

oli_arborum Over a year ago

So i need a 64Bit machine w/ >12GB of RAM to be able to insert 4 GB BLOBs?

Quassnoi Over a year ago

@oli_arborum: Seems like that. See here: mysql.com/news-and-events/newsletter/2003-09/a0000000237.html

cachius Over a year ago

"MySQL supports BLOBs (Binary Large Objects), which means you can store any binary file into MySQL. Many people ask, what is the maximum size of a BLOB in MySQL. The theoretical limit in MySQL 4.0 is 2G, however each blob requires generally to have 3 copies of it in the memory (stored in various buffers) so you need a lot of memory, if you have large BLOBs stored in MySQL. This is the reason, why the theoretical limit can be reached only on 64bit systems. The Practical limits are around some hundreds of megs per BLOB." - Last available archive.org snapshot 20100208201732

cachius Over a year ago

Still online at mailing list archive nyphp.org/pipermail/talk/2003-September/005737.html.

Pang · Accepted Answer · 2024-05-17 02:25:47Z

1

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.

edited May 17, 2024 at 2:25

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Jun 3, 2009 at 15:53

Arne Burmeister

20.7k8 gold badges57 silver badges98 bronze badges

1 Comment

oli_arborum Over a year ago

In order not to need sizeof(BLOB) RAM on the client, I used the mysql command-line client and the LOAD_FILE() function, which only uses server's RAM.

Collectives™ on Stack Overflow

Handling of huge Blobs in MySQL?

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related