473,703 Members | 2,331 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using BLOB for storing data

I'm implementig a java web application using MySQL as database.

The main function of the application is basically to redistribuite
documents. Those documents (PDF, DOC with an average size around 2Mb)
are stored in BLOB column.

The amount of documents for the first year should not exceed 5/6 Giga,
but I cannot make prevision for the next years.

Those documents are mainly just accessed (update and delete are not so
common operation)

I would like to know if someone else is using a similiar approach.
If so, it is scalable?

Thanks for any hint

Oct 26 '05 #1
3 4722
<ha******@gmail .com> wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.com...
I'm implementig a java web application using MySQL as database.

The main function of the application is basically to redistribuite
documents. Those documents (PDF, DOC with an average size around 2Mb)
are stored in BLOB column.

The amount of documents for the first year should not exceed 5/6 Giga,
but I cannot make prevision for the next years.

Those documents are mainly just accessed (update and delete are not so
common operation)

I would like to know if someone else is using a similiar approach.
If so, it is scalable?


BLOBs are a *possible* way to serve binaries.
BUT
I am at a loss to find an advantages to using your relational database in
this manner as opposed to serving binary files from a directory. What
queries would you make against the BLOB containing table? What are the
advantages of parking these files in a BLOB field?

There is a downside too. Tables with BLOB fields are necessarily dynamic,
rather than static. They are more difficult for MySQL to manage since the
record size is highly variable and it is harder for MySQL tools to repair
blown links. This is not to say that it isn't doable. But again - What is
the advantage of parking these binaries in a BLOB?

In a very real sense, your operating system is an efficient database that
specializes in the management of BLOBS (Files!). What does MySQL offer that
trumps your operating system for this particular task? We prefer to serve
files from an SSH directory with the MySQL table storing the filenames along
with other useful lookup information. We query for a filename and then
serve the binary (BLOB) directly from the directory using SSH secure copy or
some such.

Just one opinion.
Thomas Bartkus
Oct 26 '05 #2

Thomas Bartkus wrote:
I am at a loss to find an advantages to using your relational database in
this manner as opposed to serving binary files from a directory. What
queries would you make against the BLOB containing table? What are the
advantages of parking these files in a BLOB field?
No query contains the BLOB column. I have choose this solution
basically because:

1) there is no need to access the files from the fs (using the shell
for example)

2) a second web application will soon access the db with the same
behavior (many read, very few insert, almost non updates). Then I've
thought that using blob will make easier backup procedure (avoiding
inconsistency). Another issue is that the db is accessed from different
timezone so i cannot shutdown the webapplication in nighttime in order
to make the backups.
There is a downside too. Tables with BLOB fields are necessarily dynamic,
rather than static. They are more difficult for MySQL to manage since the
record size is highly variable and it is harder for MySQL tools to repair
blown links. This is not to say that it isn't doable. But again - What is
the advantage of parking these binaries in a BLOB?
Do you have some link (like a white paper) that deal with these issues.
Basically i would like to know if my solution is scalable. In my
prevision the system should generate around 5 giga each year, but is a
system that is supposed to run for a long time (over 10 years, so
arounf 50 giga of blobs).

What if the size will be 500 giga?
Just one opinion.
I really apreciate that
Thomas Bartkus


Oct 26 '05 #3
<ha******@gmail .com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

snip> Do you have some link (like a white paper) that deal with these issues.
Basically i would like to know if my solution is scalable.

<snip>

Just the MySQL documentation with their scattered comments about Dynamic Vs
Static tables. Any table containing a BLOB field is necessarily dynamic. I
always strive to keep them static. I've copied a section if text from the
documentation below.

*** "static format is the simplest and most secure (least subject to
corruption). ..."
***" Easy to reconstruct after a crash, ... "

You may also find it informative to read the following section about dynamic
tables that
follows the text I've copied below.

Other than that, I'm just skeptical about using BLOBS.
They scare me and I've never seen a clear reason to use them.

Alternate opinions welcome!
Thomas Bartkus

----------------------------------------------------------------------------
-
15.1.3.1 Static (Fixed-Length) Table Characteristics

Static format is the default for MyISAM tables. It is used when the table
contains no variablelength
columns (VARCHAR, BLOB, or TEXT). Each row is stored using a fixed number of
bytes.
Of the three MyISAM storage formats, static format is the simplest and most
secure (least
subject to corruption). It is also the fastest of the on-disk formats. The
speed comes from
the easy way that rows in the data file can be found on disk: When looking
up a row based
on a row number in the index, multiply the row number by the row length.
Also, when
scanning a table, it is very easy to read a constant number of records with
each disk read
operation.
The security is evidenced if your computer crashes while the MySQL server is
writing to
a fixed-format MyISAM file. In this case, myisamchk can easily determine
where each row
starts and ends, so it can usually reclaim all records except the partially
written one. Note
that MyISAM table indexes can always be reconstructed based on the data
rows.
General characteristics of static format tables:
² All CHAR, NUMERIC, and DECIMAL columns are space-padded to the column
width.
² Very quick.
² Easy to cache.
768 MySQL Technical Reference for Version 5.0.0-alpha
² Easy to reconstruct after a crash, because records are located in fixed
positions.
² Reorganization is unnecessary unless you delete a huge number of records
and want
to return free disk space to the operating system. To do this, use OPTIMIZE
TABLE or
myisamchk -r.
² Usually require more disk space than for dynamic-format tables.
Oct 26 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
7433
by: Sugapablo | last post by:
Is there any PHP function that will allow me to determine the MIME type of a blob stored in MySQL? Specifically, if I'm storing an image as a blob in MySQL, is there any PHP function that can determine whether it's a gif, jpeg, png, etc? -- http://www.sugapablo.com <--music ] http://www.sugapablo.net <--personal ]
1
5827
by: Rune Hansen | last post by:
I'm storing gzipped data in a MySQL blob field. I can fetch the blob and "wb" write the data to a file. It becomes a file containg gz data. I can't take the same data and do anything sensible with it in python - like say zlib.decompress(data). How can I convert the binary data from the blob field to the gzipped string it was stored as (java stores the string, Pickle is not an option)? regards
1
3009
by: konsu | last post by:
Hello, I am planning to implement an image and movie library (e.g. a photoalbum), and one of the ways that I see to store image and movie files is to put them in to BLOB fields in an sql table. I would greatly appreciate any information as to how this method compares with simply storing the images and the movies as plain files on disk. Does MySQL add a lot of overhead when retrieving images from BLOBs compared to reading the files from...
5
1309
by: ichor | last post by:
hi i have a blob field in my database and am able to display it on my asp.net page i would like to know how can i position the Blob on the page or can i use an image object? byte Picture; Picture = (byte)dr; Response.Buffer=true;
6
2912
by: Steve | last post by:
Hi, I've developed a testing application that stores formatted results in a database. Recently it was requested that I add the ability to generate graphs from the raw, un formatted test results (100,000+ float values) I don't intend to store all of the 100,000 datapoints, but rather a subset of say 250. Due to the volume of testing that we need and the volume of results stored, I need to be VERY careful with data size and keep things...
1
3038
by: sang | last post by:
Hi i am doing my project in Mysql and PHP. My problem is i want to send a Blob field to my email using the Php. That is i am storing Candidates Resume in Blob field(Document format). I want to Choose the Particular Candidates Resume and the send to some Email address.But i want to know how can i get the field from mysql and how to send it to the particular emailid. Please any one help to solve my problem. Thanks and Regards Sang
55
24746
ADezii
by: ADezii | last post by:
Of all the questions asked here at TheScripts, the one which appears with the most frequency relates to the storing of Graphic Images within Access. There are basically three techniques available to manipulate Image Files in Access and they are: Store the Image in an OLE Object Field and display it in a Bound Object Frame. The Image can either be Linked or Embedded. Store the Path to the Image in a Text Field and use an Image Control...
1
1832
by: akaley | last post by:
Hi All, My system file upload functionality..iam storing file content in DataBase by using Blob data type. But my problem is MultipartRequest is automaticlly store the file in tomcat/bin directory.. Iam using following code; MultipartRequest multi = new MultipartRequest(request, ".",100 * 1024 * 1024); it's storing the file in "Default directory".i don't want that one,because iam storing file in data base itself.
16
6663
by: wizard | last post by:
Here is a piece of code below for writing and reading blobs in SQLITE database. I ' ve a problem to read from blob struct called 'Points' Any help would be appreciated Reagards Greg #include <stdlib.h> #include <stdio.h>
0
8759
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8669
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9251
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9017
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7872
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6588
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3125
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2069
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.