Suppose, I have large (about 1 GB) strings in ASCII encoding. I have to choose whether to store them as separate blob fields or split each one of them into smaller pieces and then store them as several blob fields. After I get them in my DB, I will have to update these strings (not completely, just some small parts) and select some parts of them. It seems to me that standard MySQL functions REPLACE() and SUBSTRING() should fit just well for these purposes. What I want to ask is does a blob field get loaded completely into the memory of server when these functions are invoked? For me it's important because if my database is accessed by several users via a client application and they modify different strings simultaneously, the server may run out of memory. And if it's true I definitely have to divide my strings into parts. Also it will lead to more sophisticated queries on the client side.

Surely, if you come up with a different solution for the problem of storing large strings I'd really appreciate it.

  • Character strings should be stored in clobs (character large objects), not blobs (binary large objects). – Colin 't Hart Apr 4 '15 at 17:34
  • @Colin'tHart, guess there's no clobs in MySQL, there's text instead. Yet, thanks for your advice. – Ivan Apr 4 '15 at 18:13
  • Yes you should consider splitting the contents into smaller pieces. Updating part of a field does not exist in SQL. Changing one character means duplicating/rewriting the entire field. It will also tend to allocate memory for the entire field anytime it has anything to do with it. – Daniel Vérité Apr 5 '15 at 12:17
  • @DanielVérité, thank you. That's the answer indeed – Ivan Apr 5 '15 at 17:09

1GB requires LONGTEXT or LONGBLOB. But that datatype may give you headaches. I suggest you experiment with one row with 1GB of data in a column declared LONGTEXT CHARACTER SET ascii. If it is just ASCII, then LONGTEXT makes sense. LONGBLOB would work very similarly.

You will probably need to set max_allowed_packet. I have seen a few systems with it set to 1G.

Replication may have issues -- are you using that?

What version of MySQL are you using? I seem some Changelog entries relating to max_packet_length.

Presumably, when you use REPLACE() etc. it will need to allocate memory to perform the operation. This may include multiple 1GB buffers. Therefore, be sure to leave some extra space in RAM for such. I assume you have at least 8GB of RAM; I suspect you would not succeed below that.

| improve this answer | |
  • Currently, replication is out of my scope. I use 5.5 version with InnoDB engine. Think, I'll try to follow your suggestions after weekend. – Ivan Apr 5 '15 at 7:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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