In data 29 settembre 2008 alle ore 21:00:41, Mark Stosberg  
<mark@[...].com>  ha scritto:

>  This question isn't so much a mod_perl question, as it is a question
>  about building high performance websites with Perl.
> 
>  We have a large, busy, database application that relates to millions of
>  photos, which we also need to store and display. We've been keeping the
>  meta data about the photos in PostgreSQL, and the files on the file
>  system. (Implemented much like CGI::Uploader ).

We have:

a) ~150,000 avatar tiny pictures (50x50);
b) ~300,000 user photos (320x240 originals), but
    also available in 4 more sizes;
c) tens of millions of album pictures in original
    and thumbnail sizes;

We're using MySQL 5.0 with MyISAM storage engine. Yes.
Until recently, a) & b) were stored into a MySQL blob field. Yes.
Did you hear me screaming? :-)

Problems I found when I started working here:

- our mod_perl backends were serving 20-40% of picture requests,
   which is completely insane;

- our picture-serving code was fetching the picture from the database,
   eventually scaling it on the fly (!), saving it in memcached
   and $r-> print()ing it out down the wire.
   That's completely insane. The scaling even disabled caching.

- when you update a picture metadata (this is MySQL), you _LOCK_
   the _ENTIRE_ table with hundreds of thousands images.

- fetching from a blob field in MySQL is expensive.

Now instead:

- avatars were our first experiment. They are stored as static resources
   with a hierachical and balanced filesystem structure
   (using digests and splitting them up);

   We completely removed the caching layer from our mod_perl code,
   because caching happens directly in the browser for static
   resources;

   We managed to move away 500,000 req/day from mod_perl to static
   HTTP servers.

- User photos are coming. We wrote a nice application layer that
   can upload a single resource to many pools of static servers and
   in different sizes with automatic thumbnailing. The filesystem path
   scheme can be defined into the resource perl class, but basically
   is the same digest + splitting, like in:

     http://static.myapp.com/pool1/a1/b2c/d3f4g5h6.../123456_m.jpg

   We are mass-exporting pictures from the database blobs to our
   filesystems via DAV. Using DAV is not the most efficient way but
   allows you to attach arbitrary metadata to the filesystem.

   We now use that to resolve inconsistencies and "sync" the
   metadata in the database.
   This should go live with next release. I'll let you know :)

I'm not sure what to do for album pictures. They are already out
of the database, thank god, but the "design" guys now want 3-4
thumbnails even for those pics. Suggestions?

>  This has worked great in terms of performance, but with so much data to
>  manage, over time we have run into data inconsistency issues between the
>  file system and the database.

Can you explain the issues you found?
I'd really like to know, so I'm prepared. :-)

>  So, I'm asking if anyone has had experience successfully storing photos
>  (or othe files) directly in database? That would solve the consistency
>  issue, but may create a performance issue.

Yes, performance issues.

>  Perhaps the right kind of caching layer could solve that.

I'm not sure. If you throw caching into the "pics-in-the-db" mess,
IMHO you only make the situation worse.

-- 
Cosimo