SitePoint.com

Floezen · Apr 14, 2004, 09:24

Hi,

I've read a couple of threads here where people tried to find out how to store image files in a mysql database.
The answer was always: Don't do it.

Now I have two questions:

1) Why not? As far as I know even this board (vBulletin) stores attachments an avatars in the mysql database - so why shouldn't i do it???
Is it not even smarter sometimes? Like for backup reasons. Imagen you hav vB running and images ar always stored in some folders. For a backup you don't only have to backup the databes, but also those folders...

2)Now if I really want to do it - no matter if wise or not. What do I have to make different to queries?

Flözen

Dangermouse · Apr 14, 2004, 10:54

If you're reading/writing data to the db it may get corrupt, you may miss say one byte off and screw it all up. Its not very portable, its probably slower seems you have to query the DB, will add extra queries to the page, more code just to display an image..

vgarcia · Apr 14, 2004, 11:02

BLOB data can hinder performance of the database if done on a massive scale. Since database space/time/etc. are usually more expensive than disk space, it's usually more efficient to store a reference to the image's path on the filesystem in the database rather than the actual image.

Floezen · Apr 14, 2004, 11:22

So why vB doesn't do it?

maxor · Apr 14, 2004, 17:24

Are you sure vB stores the actual image and not just a path to the image in the db?

r937 · Apr 14, 2004, 20:48

why not?

one really good reason: an image pulled from the database has a URL that is not going to allow the browser to cache the image

browser image caching is very important if you want to have a fast-loading site

KLB · Apr 14, 2004, 21:15

From the fact that some web hosts like the one I use explicitly prohibit the storing of images or blobs within databases, I imagine doing such much have a tremendous hit in performance.

I also agree that the browser being able to cache an image is important if one wants to improve how quickly a site loads.

fillup07 · Apr 14, 2004, 22:35

Quote:

Are you sure vB stores the actual image and not just a path to the image in the db?

Yep

Look at everyone's avatar image locations, for example.

fillup07 · Apr 14, 2004, 22:36

Quote:

an image pulled from the database has a URL that is not going to allow the browser to cache the image

are you sure about this? why can't a browswer cache http://www.sitepoint.com/forums/imag...753&dateline=0 as an image?

platinum · Apr 14, 2004, 23:26

Quote:

Originally Posted by fillup07

are you sure about this? why can't a browswer cache http://www.sitepoint.com/forums/imag...753&dateline=0 as an image?

I beleive it's because the date of image creation will appear different everytime?

As for vBulletin storing images this way, I can only assume it's to prevent hassles with downloading/uploading full backups, not having to worry about different hosting setups and permissions. Bad idea IMO though! first thing I do with any of my forums is swap to the 'filesystem' setup (vB3 has that built in as an option now!)

Floezen · Apr 15, 2004, 01:04

Quote:

Originally Posted by KLB

From the fact that some web hosts like the one I use explicitly prohibit the storing of images or blobs within databases, I imagine doing such much have a tremendous hit in performance.

Sure, if you are having some hundred people on your webserver, all using blobs in their database and call the everytime, this might take performance.
But since root-servers became quit cheep, that should not be an argument for someone having his own 2GHz Server...

Quote:

Originally Posted by KLB

I also agree that the browser being able to cache an image is important if one wants to improve how quickly a site loads.

Well, first I guess you can use mod_rewrite for images too. After that these images should appear for the browser like nomal image files.
Secound, what about things like jpcache on the serverside? You could set the cachhing of images to 1 hour, that should speed up the thing!

Quote:

Originally Posted by Dangermouse

If you're reading/writing data to the db it may get corrupt, you may miss say one byte off and screw it all up.

So I better not use a database at all?
Why is the risk screwing up the database when write blobs higher than with text??

Flözen

Betcour · Apr 15, 2004, 04:04

There are good reasons to keep images INSIDE the database :
- backups are easy (everything fits into one file, backup and restore is a single line operation)
- if you have a cluster of several web servers, the database then act as a central repository for images so that each web server can get the images, without having synchronisation/distribution/concurency problems (doing the same thing with plain files is a lot more tricky)
- coherency : if you use an image file and a file path in the database, you could have the file go away and the entry still there in the database (your data structure would then be corrupted). You don't have that problem if you keep the image in the database itself (and you can use transactions to make sure everything is always right)

As far as speed goes :
- caching on the browser is not an issue, provided you write your code to issue the right HTTP header with correct expiration info
- the web server can cache locally the pictures issued by the database, this avoid stressing the database and gets you the same speed as files
- mod_rewrite can make it look like the image is really a file, while it is actually served by a script from the database/server-cache

The only issue is size (databases don't like GB of blob datas), but if the amount of picture is reasonable, the database will handle them fine.

MattR · Apr 15, 2004, 07:23

Our DBMS had 10GB of text data and handled it just fine. The performance issues are mostly myths. Put the images where ever you please.

platinum · Apr 15, 2004, 08:00

Quote:

Originally Posted by MattR

Our DBMS had 10GB of text data and handled it just fine. The performance issues are mostly myths. Put the images where ever you please.

While I'd hate to disagree with the database jedi

I know of a few vBulletin based forums who have suffered horribly from attachments being stored in this way, simply moving them to be stored in the filesystem solved it all.

KLB · Apr 15, 2004, 08:58

From what MattR and Platinum stated, I suspect that putting blogs inside of a database is a fine practice -- if it is done and used correctly. The key word is "if". I suspect not completely understanding the issue can cause greater performance issues and reliablity issues than simply storing files in the file system. Since most "webmasters" don't fully understand the issues, and large enough population know just enough to be dangerous, web hosts like mine simply ban the practice.

MattR · Apr 15, 2004, 09:12

Quote:

Originally Posted by platinum

While I'd hate to disagree with the database jedi

I know of a few vBulletin based forums who have suffered horribly from attachments being stored in this way, simply moving them to be stored in the filesystem solved it all.

That is, perhaps, a symptom of a mis-configured server. A filesystem *is* a database (and an OS contains part of a DBMS) and vice versa, so really it's a matter of implementation whether or not your DBMS can pull from a filesystem as efficiently. However, DBMS products usually offer finer-grained access control, page caching, page fetching, and optimzations not found in filesystems, so properly configured you can make it just as fast, if not moreso.

I should have clarified but that was taken care of: KLB's post is spot-on.

Widow Maker · Apr 15, 2004, 13:42

Put images in a database ? Never heard as much crap in my life.

The role of a database is to relate dynamic data, not dumb data.

Cannot agree in any way with the comments posted so far about it being okay to dump images to a database.

Ahem

Betcour · Apr 15, 2004, 14:28

Quote:

Originally Posted by Widow Maker

Put images in a database ? Never heard as much crap in my life.

The role of a database is to relate dynamic data, not dumb data.

Images are not anymore "dumb" than text, it's just a different form of encoding. Just as one needs ACID to manipulate text, one can need ACID to handle pictures.

I don't see why database should be confined to handling text forever, the days of COBOL and text-only terminal is over. If anything, I believe database vendors shoull work to make their database smarter when handling non-text datas (there are already data types for geographical coordinates, why not for picture and sound ?)

MattR · Apr 15, 2004, 14:35

Indeed. If your DBMS was smart, then you could use it to perform all sorts of things on images that you'd be hard pressed to do now without some sort of image library.

For example, your DBMS could include functions to return the height and width of the image, or methods to compare two images, etc.

Widow Maker · Apr 16, 2004, 09:19

Quote:

For example, your DBMS could include functions to return the height and width of the image, or methods to compare two images, etc.

What a load of ********

Why'd you want to implement logic such as this into a database for ? There is absolutely no point.

MattR · Apr 16, 2004, 09:25

Because it's not a database -- it's a database management system. You'd need equals (=) in order to implement uniqueness constraints.

Height and width functions would be used (internally) to allow you to define attributes for the image.

For example, you'd have something like:
image(ID, image_data (unique), etc. )
image_view( ID, height, width, bit_depth, image_data, etc. )

When you insert into image, the rest would be populated for you in the view.

IJoeR · Apr 16, 2004, 12:16

I'd say don't store the images in the database. I don't and i probably never will, though it is a cool feature, but maybe not the best.

From what I can see it will effect speed:
Compare the time it will take to load an image from the database vs. a path to an image file. Naturally, the path will load much quicker because it is a million times smaller.

Plus, i don't like to put all my eggs in one basket, so putting images and all my text in a database, if my db goes corrupt and i have no backup (that would be my fault) I lose everything.

Thats my 2 cents. Maybe run some loading tests. See how fast a query to load an image stored in the DB takes vs. making a query to the db to load the just the path -- ftp your image up in the proper location.

techmonkey · Apr 16, 2004, 12:21

Quote:

Originally Posted by platinum

While I'd hate to disagree with the database jedi

I know of a few vBulletin based forums who have suffered horribly from attachments being stored in this way, simply moving them to be stored in the filesystem solved it all.

I'm one of those that have run into BIG problems with VBs image table in the DB. I had to restore my database with a backup and lost days worth of data due to a images table related crash.

If you store a lot of photos - store them in the file system.

Sahajin · Apr 16, 2004, 12:36

the only thing I have heard is if you keep images in the db it becomes huge fast. some people talk about keeping 100K+ files in it. if vb does it then the files are very small compared to what everybody else wants to do with them. when the size of the db gets big then it leads to perfomance problems. but I think if the image is small 1-10K then I don't see a issue, unless you keep a million of those then it could lead to bigger problems.

techmonkey · Apr 16, 2004, 12:38

From Berber's blog:

"If you are dealing with a big number of images (tens of thousands or hundreds of thousands) you need to keep a very specific directory structure with sub directories to avoid file system performance limitations. In this case, storing the images in a database may result in better performance. "

This is what you need to be careful with. A database table can only be 2GB in size.

And from the replies:

"I've dealt with cases in excess of 20K images, and have always found it easier to manage via the filesystem, with reference in the database. It required some rigid directory structure rules, but was definitely manageable."

Can anyone elaborate on the rigid directory structure rules mentioned here?

View Poll Results: Do you store images in your sql database?
Yes, always	1	1.41%
Sometimes	11	15.49%
No, never	48	67.61%
I would, but I don't know how	11	15.49%
what ist a mysql database?	0	0%
Voters: 71. You may not vote on this poll

Apr 14, 2004, 09:24	#1
Floezen SitePoint Addict Join Date: Feb 2003 Location: Berlin Posts: 316	Why not save images in SQL Database? Hi, I've read a couple of threads here where people tried to find out how to store image files in a mysql database. The answer was always: Don't do it. Now I have two questions: 1) Why not? As far as I know even this board (vBulletin) stores attachments an avatars in the mysql database - so why shouldn't i do it??? Is it not even smarter sometimes? Like for backup reasons. Imagen you hav vB running and images ar always stored in some folders. For a backup you don't only have to backup the databes, but also those folders... 2)Now if I really want to do it - no matter if wise or not. What do I have to make different to queries? Flözen

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 14, 2004, 10:54	#2
Dangermouse SitePoint Wizard Join Date: Oct 2003 Posts: 1,078	If you're reading/writing data to the db it may get corrupt, you may miss say one byte off and screw it all up. Its not very portable, its probably slower seems you have to query the DB, will add extra queries to the page, more code just to display an image..

Apr 14, 2004, 11:02	#3
vgarcia ☆★☆★ Join Date: Jan 2002 Location: in transition Posts: 21,481	BLOB data can hinder performance of the database if done on a massive scale. Since database space/time/etc. are usually more expensive than disk space, it's usually more efficient to store a reference to the image's path on the filesystem in the database rather than the actual image.

Apr 14, 2004, 11:22	#4
Floezen SitePoint Addict Join Date: Feb 2003 Location: Berlin Posts: 316	So why vB doesn't do it?

Apr 14, 2004, 17:24	#5
maxor With More ! for your $ Join Date: Feb 2004 Location: Scottsdale, Arizona Posts: 937	Are you sure vB stores the actual image and not just a path to the image in the db?

Apr 14, 2004, 20:48	#6
r937 SQL Consultant Join Date: Jul 2002 Location: Toronto, Canada Posts: 31,015	why not? one really good reason: an image pulled from the database has a URL that is not going to allow the browser to cache the image browser image caching is very important if you want to have a fast-loading site

Apr 14, 2004, 21:15	#7
KLB SitePoint Wizard Join Date: Nov 2003 Location: Maine USA Posts: 3,910	From the fact that some web hosts like the one I use explicitly prohibit the storing of images or blobs within databases, I imagine doing such much have a tremendous hit in performance. I also agree that the browser being able to cache an image is important if one wants to improve how quickly a site loads.

Apr 15, 2004, 04:04	#12
Betcour SitePoint Enthusiast Join Date: Feb 2004 Location: France Posts: 58	There are good reasons to keep images INSIDE the database : - backups are easy (everything fits into one file, backup and restore is a single line operation) - if you have a cluster of several web servers, the database then act as a central repository for images so that each web server can get the images, without having synchronisation/distribution/concurency problems (doing the same thing with plain files is a lot more tricky) - coherency : if you use an image file and a file path in the database, you could have the file go away and the entry still there in the database (your data structure would then be corrupted). You don't have that problem if you keep the image in the database itself (and you can use transactions to make sure everything is always right) As far as speed goes : - caching on the browser is not an issue, provided you write your code to issue the right HTTP header with correct expiration info - the web server can cache locally the pictures issued by the database, this avoid stressing the database and gets you the same speed as files - mod_rewrite can make it look like the image is really a file, while it is actually served by a script from the database/server-cache The only issue is size (databases don't like GB of blob datas), but if the amount of picture is reasonable, the database will handle them fine.

Apr 15, 2004, 07:23	#13
MattR Database Jedi Join Date: Jan 2001 Location: buried in the database shell (Washington, DC) Posts: 1,110	Our DBMS had 10GB of text data and handled it just fine. The performance issues are mostly myths. Put the images where ever you please.

Apr 15, 2004, 08:58	#15
KLB SitePoint Wizard Join Date: Nov 2003 Location: Maine USA Posts: 3,910	From what MattR and Platinum stated, I suspect that putting blogs inside of a database is a fine practice -- if it is done and used correctly. The key word is "if". I suspect not completely understanding the issue can cause greater performance issues and reliablity issues than simply storing files in the file system. Since most "webmasters" don't fully understand the issues, and large enough population know just enough to be dangerous, web hosts like mine simply ban the practice.

Apr 15, 2004, 13:42	#17
Widow Maker Non-Member Join Date: Jan 2004 Location: Planet Earth Posts: 1,807	Put images in a database ? Never heard as much crap in my life. The role of a database is to relate dynamic data, not dumb data. Cannot agree in any way with the comments posted so far about it being okay to dump images to a database. Ahem

Apr 15, 2004, 14:35	#19
MattR Database Jedi Join Date: Jan 2001 Location: buried in the database shell (Washington, DC) Posts: 1,110	Indeed. If your DBMS was smart, then you could use it to perform all sorts of things on images that you'd be hard pressed to do now without some sort of image library. For example, your DBMS could include functions to return the height and width of the image, or methods to compare two images, etc.

Apr 16, 2004, 09:25	#21
MattR Database Jedi Join Date: Jan 2001 Location: buried in the database shell (Washington, DC) Posts: 1,110	Because it's not a database -- it's a database management system. You'd need equals (=) in order to implement uniqueness constraints. Height and width functions would be used (internally) to allow you to define attributes for the image. For example, you'd have something like: image(ID, image_data (unique), etc. ) image_view( ID, height, width, bit_depth, image_data, etc. ) When you insert into image, the rest would be populated for you in the view.

Apr 16, 2004, 12:16	#22
IJoeR SitePoint Evangelist Join Date: Feb 2003 Location: Somewhere in, MD Posts: 404	I'd say don't store the images in the database. I don't and i probably never will, though it is a cool feature, but maybe not the best. From what I can see it will effect speed: Compare the time it will take to load an image from the database vs. a path to an image file. Naturally, the path will load much quicker because it is a million times smaller. Plus, i don't like to put all my eggs in one basket, so putting images and all my text in a database, if my db goes corrupt and i have no backup (that would be my fault) I lose everything. Thats my 2 cents. Maybe run some loading tests. See how fast a query to load an image stored in the DB takes vs. making a query to the db to load the just the path -- ftp your image up in the proper location.

Apr 16, 2004, 12:36	#24
Sahajin SitePoint Guru Join Date: Feb 2004 Location: Oregon Posts: 686	the only thing I have heard is if you keep images in the db it becomes huge fast. some people talk about keeping 100K+ files in it. if vb does it then the files are very small compared to what everybody else wants to do with them. when the size of the db gets big then it leads to perfomance problems. but I think if the image is small 1-10K then I don't see a issue, unless you keep a million of those then it could lead to bigger problems.

Apr 16, 2004, 12:38	#25
techmonkey Non-Member Join Date: Jan 2003 Posts: 1,091	From Berber's blog: "If you are dealing with a big number of images (tens of thousands or hundreds of thousands) you need to keep a very specific directory structure with sub directories to avoid file system performance limitations. In this case, storing the images in a database may result in better performance. " This is what you need to be careful with. A database table can only be 2GB in size. And from the replies: "I've dealt with cases in excess of 20K images, and have always found it easier to manage via the filesystem, with reference in the database. It required some rigid directory structure rules, but was definitely manageable." Can anyone elaborate on the rigid directory structure rules mentioned here?