|Video storage - Spec needed|
MYSQL or files system?
Videos and images - MYSQL storage or files system.
Hi, be grateful if someone could help me out.
Inherited a site which allows users to upload music and video files.
I need to know..
1. what kind of server spec i should be looking for?
2. whether there maybe any issues with using MYSQL storage over file system storage i need to be aware of?
Video, and audio files maybe up to 5mb in size. With just a months worth of data, the database is 1Gb. Guess this could easily reach 20gb over next 12 months. Will this be an issue for MYSQL versus file system storage.
Will a rapidly growing mysql database eventually degrade in performance versus the file system? Will a larger DB require more ram, CPU?
Store the path to the videos/images in the DB and store the the actual videos/images in normal folders.
Actually, I don't see any good reason NOT to store the actual videos in the database.
MySQL permits "huge blobs", it stores them in a different place, so it doesn't cause problems with more conventionally-sized records. MySQL can span volumes, too, so that's not an issue.
I have to beleive that accessing huge blobs is no less efficient than accessing a flat file. After all, what is it doing for 999,999 bytes out a 1,000,000 byte file? A memory copy from a mapped file, and that is going to be the same for the file system or MySQL.
The disadvantage of storing the videos externally is that then you have to keep track of them. Lots of unnecessary code. And you have the possibility of having the videos get inconsistent with the rest of the database ("lost", inaccssible videos because the record was erase, the video wasn't, etc.)
what happens when you need to expand to 2, 5, 10, 50 servers? You going to run 50 copies of MySQL? Would make a lot more sense running the videos off NAS or something similiar, no need for the extra overhead of having MySQL pumping out video bits.
Backups would probably be easier if everything was just in 1 SQL database though.
|what happens when you need to expand to 2, 5, 10, 50 servers? You going to run 50 copies of MySQL? Would make a lot more sense running the videos off NAS or something similiar, no need for the extra overhead of having MySQL pumping out video bits. |
There's no reason why you would need to run one MySQL server per web server. It's easy to forget that MySQL is a database SERVER. Although MySQL is commonly run on the same machine as the web server on small websites, there's certainly no requirement that you do this.
Your MySQL database can certainly reside on NAS. Or, you can consider the MySQL server and it's local storage as a form of NAS.
Bottom line, I doubt that serving videos from a MySQL database is measurably slower or more CPU or disk intensive than serving them from files. In either case, there is a bit of houskeeping when opening the file or row, and then it is just streaming bytes from disk. The overhead is practically non-existent.
|There's no reason why you would need to run one MySQL server per web server. |
how are you going to run multiple servers without running multiple MySQL installs if you're storing all your videos on MySQL? If you outgrow your server, you're going to have to add a second one. Now you could take this opportunity to split up your servers, 1 web server and 1 DB server. What happens when you have to move to 2, 5, 10 web servers, is that 1 DB server going to be able to keep up with the throughput it will take to server all those video files? Wouldn't it make more sense storing all the videos on storage devices, use the DB to keep track of links and just push the videos from their file location?
It's how google, myspace and youtube setup their network. Not saying you have to do as they do, but I'm sure they looked at all the possibilities and chose the way they did for price and performance reasons.
I've never tried storing videos on a DB, but I did try storing images and that failed. I think with better hardware it would have worked out OK, but it wouldn't have been scalable without getting costly.
|What happens when you have to move to 2, 5, 10 web servers, is that 1 DB server going to be able to keep up with the throughput it will take to server all those video files? |
Possibly. Possibly not. But it probably is NOT a 1-1 correspondence. It will depends on what ELSE those webservers are doing, as well as what ELSE the MySQL server(s) are doing. BTW, MySQL *does* support clustering. Maybe yo need 10 web servers and 3 database servers. Maybe you need 2 web servers and 10 database servers. In any case, the flexibility is there to deal with whatever is required.
Again, streaming video is not paracticulary demanding - for a web server, a SQL server, or a file system. There isn't a lot of overhead involved, because once the data is located, it's a matter of streaming it - a lot of it - requiring little or no additional overhead.
It's not at all the same as the more typical database usage - that of bringing together serveral rows/columns of small data according to some sort and filtering critera. That is MUCH more intensive kind of activity than video streaming, and is much more likely to be the limiation on the SQL server(s). In fact, one thing that might help is to maintain several servers for video and for text data.
You need to do a calculation of average stream bandwidth vs. available hardware bandwidth. You need to study this for your broadband connection(s) to the Internet, your internal network connections, your web server(s), your SQL server(s), your disk system, your NAS (if any).
|Wouldn't it make more sense storing all the videos on storage devices, use the DB to keep track of links and just push the videos from their file location? |
No, for the various reasons stated above. I am not convinved that storing the vidoes in a database represents any significant increase in overhead.
|It's how google, myspace and youtube setup their network. |
Do you know that for a fact? Can you cite references?
From what I know, many of the largest players have multiple datacenters around the country (and world) where they basically replicate their entire system. This is probably the most important aspect of scaling that the big boys do that most of us never have to worry about. Yahoo, for example, peers directly with Cox. I think that some big players even colocate servers at Cox, Comcast, etc.
|I've never tried storing videos on a DB, but I did try storing images and that failed. |
How did it fail? Did you optimize the database for this use? For example, make a seperate table for the images (with no other field other than an ID), designate seperate storage for that table, choose an appropriate concurency model, etc.?
BTW, I'm not necessarily advocating the approach of storing the vidoes in MySQL. I'm just saying that I don't see any good reason NOT TO. I think either way will work out about the same overhead-wise. So, if one approach or the other seems better for your application, I think you can make that choice without concern that the choice is going to a make a big difference in how much hardware you need.