blob versus file
Andy
I am wondering if anybody has experiance in saving images to blob in mysql.
I do save images with 1 K and 4 KB to blob fields while I used to save them
to file. It seams to me that this is much slower accessing the files. The
images take a bit (really short but absolutly noticable) to show up on the
site. Is there a way to improve the performance, and why is this happening?
I thought the performance might even boost after storing them to blobs.
Thank you for any comments on that,
Andy
PS: I was planing to store all the other images (20 - 50 KB) to blobs as
well (thousands) but after this first experiance I am a bit confused if the
performance would even more suffer. Would you guys recommend this?
Pierre-Alain Joye
"andy" <news.l...@gmx.de> wrote:
> Hi there,
>
> I am wondering if anybody has experiance in saving images to blob in mysql.
>
> I do save images with 1 K and 4 KB to blob fields while I used to save them
> to file. It seams to me that this is much slower accessing the files. The
> images take a bit (really short but absolutly noticable) to show up on the
> site. Is there a way to improve the performance, and why is this happening?
> I thought the performance might even boost after storing them to blobs.
Not really, the OS filesystem contains features that makes it always faster than a sql query, that will increase your network traffic too.
Inserting images or whatever binary data in a database does not have much sense, you could not do a query with this field, cannot be indexed (dunno if exists a DB that implement a image indexer ;) ). Storing relative pathes gave me always more portabilities between DBM.
In some case, you have to insert images (or every others binary data) in DB (due to global permissions system only avaible for the DB and not for the filesystem, for example), but as far is possible, I avoid to do it so.
IMHO :)
pa
Mirza Muharemagic
i tested it a few times, and there is actually no difference. why
make it complicated, when it can be simple. my opinion is there
is no difference.
Mirza mi...@php.co.ba
01.07.2002 14:17
Richard Lynch
>to file. It seams to me that this is much slower accessing the files. The
>images take a bit (really short but absolutly noticable) to show up on the
>site. Is there a way to improve the performance, and why is this happening?
Each image request is a separate HTTP connection.
This is true REGARDLESS of whether they are stored in file system or in
MySQL.
The file system will almost certainly be *WAY* faster than MySQL.
(I'm sure somebody can come up with a two-tier system with burning fast
MySQL and dog-slow hard-drive on the HTTP server, but... let's be real
here...)
If the images are "too slow" you need to look at your BANDWIDTH, the HTTP
server speed, and the hard drive speed, and the caching system of the OS.
Moving the images into MySQL is unlikely to really solve any of these.
You could run a quick test, though, in about 15 minutes of work, with a
single image, just to be sure.
--
Like Music? http://l-i-e.com/artists.htm
Andy
comming from the FS are cached just fine. Do u think thats because of the
blob?
Andy
"Mark" <magg...@mminternet.com> schrieb im Newsbeitrag
news:200207020349...@mail.mminternet.com...
On Mon, 1 Jul 2002 14:20:30 +0200, Pierre-Alain Joye wrote:
>On Mon, 1 Jul 2002 14:17:53 +0200
>"andy" <news.l...@gmx.de> wrote:
>
>>Hi there,
>>
>>I am wondering if anybody has experiance in saving images to blob
>>in mysql.
>>
>>I do save images with 1 K and 4 KB to blob fields while I used to
>>save them
>>to file. It seams to me that this is much slower accessing the
>>files. The
>>images take a bit (really short but absolutly noticable) to show up
>>on the
>>site. Is there a way to improve the performance, and why is this
>>happening?
>>I thought the performance might even boost after storing them to
>>blobs.
>
>Not really, the OS filesystem contains features that makes it always
>faster than a sql query
not necessarily for small files like he's talking about. it depends
on the fs but I would guess they'd be pretty close.
>that will increase your network traffic too.
only if the db is accessed across a network (he didn't say it was)
>Inserting images or whatever binary data in a database does not
have>much sense, you could not do a query with this field, cannot
be>indexed (dunno if exists a DB that implement a image indexer ;) ).
the blob field can't be indexed but others (id, filename, keywords,
caption etc..) can, there's lots of cases where it makes sense to put
images in a database.
if I had to guess the problem I'd say its either
1) the db is across a network like you said
or (more likely)
2) you need to create an index on the table. try running the query
manually and see how long it takes to get a result set. when you get
it down to .01 seconds you;re in good shape
hth,
Mark
>"andy" <news.l...@gmx.de> wrote:
>
>>Hi there,
>>
>>I am wondering if anybody has experiance in saving images to blob
>>in mysql.
>>
>>I do save images with 1 K and 4 KB to blob fields while I used to
>>save them
>>to file. It seams to me that this is much slower accessing the
>>files. The
>>images take a bit (really short but absolutly noticable) to show up
>>on the
>>site. Is there a way to improve the performance, and why is this
>>happening?
>>I thought the performance might even boost after storing them to
>>blobs.
>
>Not really, the OS filesystem contains features that makes it always
not necessarily for small files like he's talking about. it depends
on the fs but I would guess they'd be pretty close.
>that will increase your network traffic too.
only if the db is accessed across a network (he didn't say it was)
>Inserting images or whatever binary data in a database does not
have>much sense, you could not do a query with this field, cannot
be>indexed (dunno if exists a DB that implement a image indexer ;) ).
the blob field can't be indexed but others (id, filename, keywords,
Andy
saving images to the OS FS is way faster than to blob in mysql.
The main reason why I am trying to do so is because I would like to seperate
my application from the content. Whenever I make an update of the
application I have to move around files which are in a data folder inside
the application. So I was searching for a way to avoid this and keep the
data untouched.
Maybe someone knows a better way to do that?
Thanx for your help guys,
Andy
- Knud I thought this might be of interest for u 2
"Richard Lynch" <ri...@phpbootcamp.com> schrieb im Newsbeitrag
news:20020701232018.VNIV6023.sccrmhc02.attbi.com@[192.168.1.103]...
> >I do save images with 1 K and 4 KB to blob fields while I used to save
them
> >to file. It seams to me that this is much slower accessing the files. The
> >images take a bit (really short but absolutly noticable) to show up on
the
> >site. Is there a way to improve the performance, and why is this
happening?
>
>
> The file system will almost certainly be *WAY* faster than MySQL.
> (I'm sure somebody can come up with a two-tier system with burning fast
> MySQL and dog-slow hard-drive on the HTTP server, but... let's be real
> here...)
>
> If the images are "too slow" you need to look at your BANDWIDTH, the HTTP
> server speed, and the hard drive speed, and the caching system of the OS.
>
> Moving the images into MySQL is unlikely to really solve any of these.
>
> You could run a quick test, though, in about 15 minutes of work, with a
> single image, just to be sure.
>
> --
> Like Music? http://l-i-e.com/artists.htm
Not really, the OS filesystem contains features that makes it always faster
than a sql query, that will increase your network traffic too.
Inserting images or whatever binary data in a database does not have much
sense, you could not do a query with this field, cannot be indexed (dunno if
Andy
just text. Do u really think this might increase the traffic?
I also noticed that the image is not cached anymore. Is this true for all
blobs, or do I just access them in a wron way?
(I am requesting a php file in the <img tag with the statement inside and
output them before sending a jpeg header with an echo)
Thanx for your help,
Andy
"Pierre-Alain Joye" <p...@pearfr.org> schrieb im Newsbeitrag
news:2002070114203...@pearfr.org...
Richard Lynch
>blobs, or do I just access them in a wron way?
Your browser is only going to cache URLs without a ? in them, probably
maybe...
Or, less likely, your PHP code with the MySQL code may be sending out
different headers() about cache-ing, if you snagged it from somebody and
didn't pay close attention...
Pierre-Alain Joye
"Andy" <news.l...@gmx.de> wrote:
> is the increase of the network traffic noticable? The query is pretty small
> just text. Do u really think this might increase the traffic?
Try to heavely charge a DB with images inside it.
Do the same without DB, that depends if the dbms is in another server or not, note localhost server can use the network interface and not the socket.
then compare the two methods, and use the best :).
Tests are always the best way to get the best methods :).
> I also noticed that the image is not cached anymore. Is this true for all
> blobs, or do I just access them in a wron way?
Cached ? Client side ?
hth
pa
Richard Lynch
>caption etc..) can, there's lots of cases where it makes sense to put
>images in a database.
This makes no sense to me.
You would still index the filename, the keywords, the caption, make them all
searchable, but you do *NOT* shove the image data into a BLOB to do that.
The image raster pixel data and the meta-info about the image are completely
orthogonal.
When you start using SQL to dig into the actual image data, parsing the GIF
LZW compression within it (or JPEG, or whatever) and comparing the actual
contents of the images in SQL, it makes sense to shove the images into a
BLOB for index/search reasons.
Now, network traffic and off-loading some work from your web-server to your
db-server *MIGHT* *MIGHT* *MIGHT* make sense, but I sure doubt it for *most*
users/applications.
If you've got an actual two-tier solution with real-world performance
testing where it makes sense, post the numbers and any other rules of thumb
for when you really, really do want GIF BLOBs, by all means.
eric
> Hi there,
>
> I am wondering if anybody has experiance in saving images to blob in
> mysql.
Hi Andy,
I'm about to start doing something similar so I've been quite interested in
the replies. However, I wonder if you (or anyone else) has any related
info:
First - how to upload from the form?
Doesn't the post method have a limit?
In which case how do I get around this length limit?
Second -
My users will be able to upload a file (jpeg) from their PC using a form.
As they will be images from digital cameras I want to be able to optimize
the images and reduce their size so less space is taken up on the server.
Any ideas on how this can be done?
I haven't found any way to do it using PHP but thought that I may be able
to run some software on the server (Cobalt RAQ running Linux I think).
However the software would need to automated as below.
E.g.
1. PHP script receives the contents of the form.
2. Save file to disk.
3. Use exec() to run the software that will do the optimizing presumably by
passing parameters to the software telling it what size the final jpeg
should be.
4. Take the contents of the optimized jpeg & insert into database.
Any one have any thoughts?
--
TIA, eric