#1  
Old 4th September 2008, 19:39
bagpiperdude90 bagpiperdude90 is offline
Member
 
Join Date: Mar 2007
Posts: 34
Thanks: 4
Thanked 22 Times in 5 Posts
Default MySQL driven photo gallery

Ok, I have a question. I'm building a photo gallery for an aviation fansite, where people can upload photos they take. All the photo information (including the photo itself) is stored in a MySQL database. I did this for ease of use, and also to be able to limit who can view the photos, since no direct link to the photo exsists.

However, what is the feasible limit of such a design? What if we get 500,000 photos in there? How slow will the database be?

We're on a dedicated Core 2 Duo with 4GB of RAM, running CentOS 5.2 64bit, and two 750GB drives. Obviously the drives will need to be upgraded later.

Is it better to use a database design where the database stores photo information, but the photos are physical files on the server? Still, the DB will be accessed just as much - but it won't be serving the complete photo every time.

This is my first time undertaking such a huge project, so I want to make sure I get it as right as I can from the start.

BTW, I'm making use of a lot of AJAX, too - so people can post comments on the photos, edit photo data, etc all on the same page. However, those are small database queries, so its no big deal. The majority of the db queries will be fetching the photo data. Maybe I should implement a sort of cache, so the DB won't have to work so hard?

Any input would be greatly appreciated! Thanks!
Reply With Quote
Sponsored Links
  #2  
Old 5th September 2008, 08:27
Ben Ben is offline
Moderator
 
Join Date: Jul 2006
Posts: 1,029
Thanks: 7
Thanked 62 Times in 56 Posts
Default

Well the biggest problem I see here is dumping the DB, as it will get very huge the more photos you will need to store.
Another thing are thumbnails, do you plan to use such? If so you will have to store photo + thumb, so calculated with low values of 150kb per foto (which is very small) and 50kb per thumbnail will mean about 20GB ot tablespace just because of storing fotos there. I don't want to know how much time it takes restoring the DB in case of a crash.

I'd suggest storing the images + generated thumbs on a hdd. This will also give you the flexibility to e.g. move the images to a completely different server once as there you will need storage more than a fast cpu.

Regarding the direct access, it's not a big deal preventing the webserver to have access to the imagefolder. Instead code a script serving the pictures.

Quote:
Maybe I should implement a sort of cache, so the DB won't have to work so hard?
Fetching images is not a big deal for a DB as a DB is used to do complex queries. It's like using a sledge-hammer to crack a nut.
Reply With Quote
  #3  
Old 5th September 2008, 17:14
bagpiperdude90 bagpiperdude90 is offline
Member
 
Join Date: Mar 2007
Posts: 34
Thanks: 4
Thanked 22 Times in 5 Posts
Default

Quote:
Originally Posted by Ben View Post
Well the biggest problem I see here is dumping the DB, as it will get very huge the more photos you will need to store.
Another thing are thumbnails, do you plan to use such? If so you will have to store photo + thumb, so calculated with low values of 150kb per foto (which is very small) and 50kb per thumbnail will mean about 20GB ot tablespace just because of storing fotos there. I don't want to know how much time it takes restoring the DB in case of a crash.

I'd suggest storing the images + generated thumbs on a hdd. This will also give you the flexibility to e.g. move the images to a completely different server once as there you will need storage more than a fast cpu.

Regarding the direct access, it's not a big deal preventing the webserver to have access to the imagefolder. Instead code a script serving the pictures.


Fetching images is not a big deal for a DB as a DB is used to do complex queries. It's like using a sledge-hammer to crack a nut.
Well, I'm glad I decided to switch to file-based, then.

I was worried about a few things, namely the backing up the database, and what would happen if the database crashed - how long would it take to restore it, etc.

Thanks for the input!
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Upgradeded to mysql4.1 breaks mysql client PoleCat Installation/Configuration 5 22nd April 2008 17:38
Problem installing MySQL on suse 10.3 Txchaser Installation/Configuration 20 18th April 2008 01:05
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 09:11
Messed up ISPConfig-2.2.8 Upgrade Morons Installation/Configuration 4 29th November 2006 12:17
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 16:04


All times are GMT +2. The time now is 14:35.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.