Storing images

[SaranacLake]

Is it a problem storing images in MySQL?

Long ago I heard it would blow up a non-enterprose database like MySQL. 

Is this still a concern?/

[requinix]

MySQL is enterprise quality.

It is perfectly fine to store files in a database, but it does mean some extra work to be able to serve those images through a web server.

[SaranacLake]

1 hour ago, requinix said:

MySQL is enterprise quality.

It is perfectly fine to store files in a database, but it does mean some extra work to be able to serve those images through a web server.

So what is needed to make this a reality?

First off, how exactly do you store images in MySQL?

And what other considerations are there as far as the server and network setup and anything else?

I do have a VPS, so I am guess that helps, but everything is on one box at this time.

My immediate interest in story all thumbnails and slightly larger product images for my product catalog which is very small (e.g. 100-200 items).

But down the road it would be nice to store things like member profile images whch I hope grows very very large in number as my site grows.

 

[requinix]

First, is this what you should be doing? Do you have a CDN? If so then you should be storing files there. If not then... well, it's kinda complicated. What's your architecture? How many servers and databases?

[SaranacLake]

Just now, requinix said:

First, is this what you should be doing? Do you have a CDN? If so then you should be storing files there. If not then... well, it's kinda complicated. What's your architecture? How many servers and databases?

Right now for my meber images I just have them stored in a directory.

I don't have a CDN because I haven't went live yet.  Will likely use CloudFlare - more so for privacy/protection - when I get closer to go live.

As stated, I have a Virtual Provate Server.  One.  And one MySQL database.  Small potatoes!

I just think storing everything in the database would be easier.

You are starting to make it sound like it can be monumental?

Maybe I don't want to go down that slippery slope after all?

 

[requinix]

I would say storing in a database is not easier. The web server is capable of serving out static images very well, and if you store them in a database then the work to create a PHP script to support the same features as the server is not fun. Caching, MIME types, byte ranges... there's a lot to cover.

You can do it, of course, but you only have one server so storing them as files is going to be much easier.

[SaranacLake]

Just now, requinix said:

I would say storing in a database is not easier. The web server is capable of serving out static images very well, and if you store them in a database then the work to create a PHP script to support the same features as the server is not fun. Caching, MIME types, byte ranges... there's a lot to cover.

You can do it, of course, but you only have one server so storing them as files is going to be much easier.

Looks like I asked the right person!

So then what would be the advantages of storing images in MySQL?

I have heard that it is easier to add/remove images, and that in general a database just does a cleaner job of storing and manipulating the image files.

What do you say?

*********

As far as caching goes, can't you store images in the database AND use a CDN in parallel?

What do you mean as far as MIME types and byte ranges?

 

[requinix]

1 minute ago, SaranacLake said:

So then what would be the advantages of storing images in MySQL?

If you have multiple web servers, no CDN, and no other form of centralized storage, putting files in a database is how you can distribute the files across all the servers.

Another is replication - databases exchanging data with other databases. If you have multiple database servers running with replication already, putting files in there means those files are replicated as well.

1 minute ago, SaranacLake said:

I have heard that it is easier to add/remove images, and that in general a database just does a cleaner job of storing and manipulating the image files.

I disagree. I'm rather surprised to even hear that statement being made.

1 minute ago, SaranacLake said:

As far as caching goes, can't you store images in the database AND use a CDN in parallel?

But why? Why store them in multiple places? There's no need.

1 minute ago, SaranacLake said:

What do you mean as far as MIME types and byte ranges?

A file is more than just binary data. Smart browsers can recognize the data from a PNG image and not confuse it with a JPEG image. But not all browsers are good at that, and sometimes file data looks similar across different types. Which means you need to know the MIME type for a file and send it to the browser. Which means identifying what it is, storing it in your database, and sending it through your PHP script.

Additionally, a request for a file doesn't have to be for the entire file. If you had a large file, a browser could try to download it, and if that process fails (eg, internet disconnected) recover from where it left off. When it tries to continue it tells the server that it doesn't have to send the whole thing - only parts of it, or starting from a certain offset. Managing that on your own in PHP is annoying.

Web servers can do all that for you.

[SaranacLake]

Just now, requinix said:

But why? Why store them in multiple places? There's no need.

 

I thought the way it worked was that you stored the *original* image locally either on your web server or in your database, and then the CDN replicated that same image across the country/globe so someone in Boston doesn't have to go to L.A. to download the image.

So in that scenario, I thought the original image was in L.A. on the webserver so people like Requinix could download it, but then the person in Boston would download the image from the CDN server in Boston.

Point being, by definition, you always have duplicate images (i.e. the "master", and the copies) when using a CDN.

So you were talking about caching being a pain with a database, and I was like why?

So you stor ethe original image in the database, and then CDN does whatever it wants to with its local copies, no?

 

[requinix]

Ah yes, you are right. I said "CDN" but actually I meant some form of online storage, like Amazon S3. You could have a real CDN with it, or not.

Whatever technically, point was a secondary location outside your web servers that was a singular location where you could keep the files. Perhaps your own file server, or S3/Cloudfront, or something else.

[SaranacLake]

Just now, requinix said:

Ah yes, you are right. I said "CDN" but actually I meant some form of online storage, like Amazon S3. You could have a real CDN with it, or not.

Whatever technically, point was a secondary location outside your web servers that was a singular location where you could keep the files. Perhaps your own file server, or S3/Cloudfront, or something else.

Several years ago I built a website similar to PHPFreaks where people could build profiles, send PM's, post comments (but not in forums).

For the profles I just dumped all of the files into a directory.  I honestly don't remember the specifics of how I implemented things.  I do recall that I spent a lot of time making sure my upload script would catch any suspicous files.

When I display a profile, I think I just query my database, it looks up the filename, and then I point to the pictures directory and load up that file?

My point of mentioning is all of this is since you are saying it is better to do what I have done (i.e. story images on the webserver), do you have any words of wisdom for DO's and DONT's in this scenario?

[requinix]

8 minutes ago, SaranacLake said:

When I display a profile, I think I just query my database, it looks up the filename, and then I point to the pictures directory and load up that file?

Yes.

8 minutes ago, SaranacLake said:

My point of mentioning is all of this is since you are saying it is better to do what I have done (i.e. story images on the webserver), do you have any words of wisdom for DO's and DONT's in this scenario?

It kinda depends how paranoid you need to be.

If you only care about images then it's pretty simple. Do restrict upload size through your php.ini or similar PHP setting. Do not use the "type" in $_FILES. Do not use the original filename, if you can avoid it, or if you want it then use basename() to get that portion of it and ignore the extension. Do determine the type of image yourself, like with getimagesize(). Do store the file using the correct extension for the image type. Do make sure your server will not try to execute any files in the upload directory (how you do depends on the server).

[SaranacLake]

3 minutes ago, requinix said:

Yes.

It kinda depends how paranoid you need to be.

If you only care about images then it's pretty simple. Do restrict upload size through your php.ini or similar PHP setting. Do not use the "type" in $_FILES. Do not use the original filename, if you can avoid it, or if you want it then use basename() to get that portion of it and ignore the extension. Do determine the type of image yourself, like with getimagesize(). Do store the file using the correct extension for the image type. Do make sure your server will not try to execute any files in the upload directory (how you do depends on the server).

Yes, my script does all of that much more.

I guess i was asking more from a management standpoint.  See that is why i am thinking that storing images in MySQL would be advantageous... 

Right now I just have a naked file sitting out on my Webserver, where it doesn't seem like I have much control.

If it was in a database, I would have controls that restrict how images can be ADDED, DELETED, UPDATED, and there would be metadata telling me when things happened.

I'm not sure if you culd argue whether backups and recovery is easier if the images are on the webserver or in a database.

Anyways...  I am not arguing your earlier advice, but just following up to see if there are any other controls I should add to protect things.

It's one thing to have 100 product image files in a directory, and quite another to have 20,000 member rofile images in a directory?!

That is what I was asking for as far as DO's and DONT's.

 

[requinix]

I'm talking strictly about where the file data is being stored. You can have your metadata (which you should, that's a good thing to do) in the database certainly, but there are more advantages to storing the actual data and transmitting the files from the web server than from the database. Having logs of adding, deleting, and updating these files doesn't require that you store the files themselves in the database too.

Regarding too many files, for small sites it's not an issue, but it is good to partition them some. There are limitations to how many files can be in a directory, even if you are unlikely to hit that limit anytime soon.
You could partition them by upload date (year/month/filename.ext) or uploader (user id-user name/filename.ext) or unique ID (last two digits of ID/filename.ext) or really anything else you can think of. You can always change this later, of course.

[SaranacLake]

Just now, requinix said:

I'm talking strictly about where the file data is being stored. You can have your metadata (which you should, that's a good thing to do) in the database certainly, but there are more advantages to storing the actual data and transmitting the files from the web server than from the database. Having logs of adding, deleting, and updating these files doesn't require that you store the files themselves in the database too.

True.

 

Just now, requinix said:

Regarding too many files, for small sites it's not an issue, but it is good to partition them some. There are limitations to how many files can be in a directory, even if you are unlikely to hit that limit anytime soon.
You could partition them by upload date (year/month/filename.ext) or uploader (user id-user name/filename.ext) or unique ID (last two digits of ID/filename.ext) or really anything else you can think of. You can always change this later, of course.

Any idea the order of magnitude when it comes to directory limitations regarding file count?

My VPS is on a Linux box.

Are we talking thousands or tens of thousands of images, or something dinky like 500?

Basically i am trying to figure out how much time I have before it would become an issue.

And since I haven't built anything like this in my code and filename scheme, what would I do when I reached a limit?

I mean it's easy to say, "Well design it right the first time?!"

But if I listen to every person every time that comes up, I will *never* finish this website!!!

If I can just complete the ecommerce module for this site in the next month or two, i can finally go live after years of pissing around.

I am hoping I have 3+ years with my current design, which should give me enough time to build a much more robust v2.0

Thoughts?

 

 

[requinix]

3 minutes ago, SaranacLake said:

Any idea the order of magnitude when it comes to directory limitations regarding file count?

Billions.

Quote

Basically i am trying to figure out how much time I have before it would become an issue.

It's cheap to do now, you might as well. IMO looks more professional too.

Quote

And since I haven't built anything like this in my code and filename scheme, what would I do when I reached a limit?

You'd have to move everything into partitions.

Quote

But if I listen to every person every time that comes up, I will *never* finish this website!!!

A wise observation.