Database Storage Comparison...

[VictorValdez]

Hello,

I have successfully written our Online Board Agenda software in PHP/MySQL thanks in part to the great assistance and discussion here...I have also successfully setup a database table that accepts the supporting documentation for these agenda items.

I chose to store the files in the database tables because I can control the files easier and associate them with the agenda item, along with the confidentiality associated with the file. I felt it also holds the attachment in a more secure location, in the DB, as opposed to in a file directory on the server which people can probably view by scanning directories or something.

This has been very successful for about 6 months now and I have not noticed any decrease in database performance...some of these supporting documents range in size from 100K - 10MB - 85 MB!

This of course is a controlled group of people, say 20 or so, that view this at once during board meetings. Now, I am redesigning our school districts web site and would like to do the same thing...but of course our school district is one of the largest in Texas, and get a considerable number of more hits.

I will have school boundary maps, publications, school videos, and other images that will be a part of this more interactive site in either JPG, PDF, or even VIDEO formats. Any suggestions on whether I should continue the same structure as the board agenda software? If not, any suggestions on how to manage the files in a directory structure method?

Thanks very much for your help. Great info in here.

Victor

[Sxooter]

The only way to know is to load test it. Write a script that beats the database with inserts / reads / deletes etc. through the web pages.

Don't forget to test it under parallel load. you might want to test both firebirdsql and postgresql under parallel load as well to see how they all three behave here under increasing load. You don't wanna find out you picked the wrong database for the job after it's all set and running, as conversion is way more expensive on a live system than in a test system.

If the databases have problems handling that much load, then look at storing the path in the database and the data in a file system to decrease load, but I/O is I/O. If you've got to deliver tons of files a second, it's gonna be more dependent on whether you've got a fast I/O subsystem than whether or not you're storing in the database or not.