Using BLOB for storing data

[hamvil79]

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

[Thomas Bartkus]

<ha******@gmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.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

[hamvil79]

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

[Thomas Bartkus]

<ha******@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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.