Databases
StackOverflow2013
Postsdbo
POStoring large files / binary data in a mysql database: when is it ok?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStoring large files / binary data in a mysql database: when is it ok?
    primarykey
    data
    text
    <p>Ok, I have searched about this and read a few points of view about storing binary data in a [MySQL] database. Generally I consider this a bad idea and try to avoid it, favouring traditional file transfers and just storing a reference to the file in a database.</p> <p>However, I am working on a project which requires database synchronisation with a remote/cloud database, not just for files, but also for settings and other user content. For this, and other reasons, I felt this might be an appropriate situation for binary storage in a database.</p> <p>I have written a general system for the database sync which works well using Reflection and XML. I have also (against my instincts) integrated the file storage in to this system. Again, it works well - I chop files in to 64Kb BLOBs and store them in a table, with a file_id reference (linked to a seperate table which contains meta data such as file name/size/mime type).</p> <p>This enables me to send bits and pieces as and when a connection is available, and also allows me to limit each request size to keep things running smoothly.</p> <p>So far I have not found any issues with this, and have successfully imported and transferred over 1gb of data in both directions (over about 10-15 files / 16000 rows), but I worry about its scalability - will it slow down once there is 20gb+ data in there, or can MySQL handle it provided my queries are well structured?</p> <p>Another reason for my decision to store the data in the database was that I figured I could simply add another HDD/storage device to MySQL if space ran low, in the hope of efficient scaling/replication/etc.</p> <p>I would very much appreciate any views or comments as to whether this is a good or bad approach, and have I missed any obvious problems I'm likely to see once used in a production environment?</p> <p>edit: I forgot to mention, the file sizes could range from 1KB to ~1GB</p> <p><strong>[Rough] Conclusion</strong> Firstly: thanks very much to those who contributed a considered answer. Choosing the accepted answer here has been quite difficult as each has something decent to offer.</p> <p>In the end (despite my hopes), I have decided that a pure MySQL storage server is at best only an ok solution (I still can't help wondering why they bother including the BLOB types though).</p> <p>As the alternative, I am torn between @Nick Coons file system approach and @tadman's suggestion of a hybrid using a light weight key/value database engine such as leveldb. Provided the practicalities of using leveldb in this project are not an issue, this is most likely the approach I will work towards.</p> <p>I have accepted tadman's answer on this basis; his answer was also most applicable and useful to my situation.</p> <p>That being said, and for those that are interested: I have enjoyed quite a lot of success using only MySQL so far. I have tested a table storing over 15gb of binary data without any noticable negative side effects from to inserting/retrieving data from large tables (with careful queries). However, I am certain this is still very inefficient and either of the alternative methods mentioned will be significantly better.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAlfie
    1. USAlfie
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POStoring large files / binary data in a mysql database: when is it ok?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POStoring large files / binary data in a mysql database: when is it ok?
      1. USBlender
      1. VTFavorite
    3. VO
      singulars
      1. POStoring large files / binary data in a mysql database: when is it ok?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf you are going to down-vote, please can you at least provide an explanation so I can avoid the same mistake again?
      singulars
      1. POStoring large files / binary data in a mysql database: when is it ok?
      1. USAlfie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload