in reply to Re^2: Mysql and Images
in thread Mysql and Images

As that happens, some databases caching and disk operations outperform those of the operating system... yes that's hard to believe.

Imagine you find out it's true. Would you now go for storing large binary thingies in the database if you are asked to deliver them quickest possible?

In the OP's case, we're looking at an HTTP server running on the same host, so any gain in image delivery is going to have to contend with disk seeks initiated by the HTTP server which is probably either forking or multithreaded and reliant upon the file system provided by the OS. Under any appreciable load, huge gains in the service of images will be very small gains in system performance -- tortoises have an optimally low coefficient of drag but they still don't go very fast. :)

If the server in question is on an ethernet (plain old 10Mbps) link, then the bottleneck is almost certain to be the network and just about any relatively recent/decent machine will be able to keep it swamped 24/7 without much optimization outside of the HTTP server itself. If it's on fast ethernet (100Mbps) or faster, then it's conceivable that the file system is the bottleneck -- but we're approaching the hardware limitations of most commonly deployed disk systems and cannot afford a lame operating system anyway.

I'd consider overcoming a lame OS by shifting the burden to the database server if:

  1. The file system implementation (not the hardware itself) is proven to be the content delivery bottleneck;
  2. The database server is significantly faster *and* is capable of supporting the greatest anticipated production load *with headroom* for the inevitable invocations of Murphy's Law;
  3. The task of image service cannot be offloaded to another host; and
  4. There is an insurmountable obstacle between the current reality and a more suitable solution, such as installing a free Linux OS to eliminate the lame OS.

Most often, the simpler solution is the best one. Unless/until benchmarking (in this case, from a remote host) proved that the performance of the system as a whole was lacking, I'd just let the HTTP server deal with file service, and do my best to get out of its way as quickly as possible.

Replies are listed 'Best First'.
Re^4: Mysql and Images
by Braindead_One (Monk) on Dec 19, 2005 at 19:56 UTC
    I don't use the DB for performance reasons - i use it because it's easier to handle (well, at least should be ;) ).
    The images will change pretty often. If you consider file system vs. database performance, you also have to keep fragmentation in mind. Although modern *nix-filesystems tend to be resistant against fragmentation issues, a _very_ large fluctuation can still cause serious performance loss (as you can see with Gentoo Linux's portage tree).

    But as said before - its just easy to handle. You don't have to care about potential security headaches with uploading binary files into your cgi-dir, permissions etc. Just 'insert' and 'select', and there you (should) go. And i still have to get the content-type, either by extracting it from the image (bad idea) or by getting it from the Database.

    Some more Information about my problem: System is a current Gentoo-Linux with perl 5.8.6, mysql-4.1.14, DBD::Mysql 2.9007, DBI 1.46. When using the console-client 'mysql', everything works as expected. Same with phpMyAdmin.
    The following code also works: 

     my $sql = "select type from bilder where id =?";
    my $sth = $dbh->prepare($sql);
    $sth->execute( $q->param('id') );
    my $error = 0;
    my $type = $sth->fetchrow || ( $error = 1 );
    $sql = "select data from bilder where id =?";
    $sth = $dbh->prepare($sql);
    $sth->execute( $q->param('id') );
    my $data = $sth->fetchrow();
    binmode(STDOUT);
    ...

    [download]
[reply]
[d/l]
      Hmmm... I've never noticed fragmentation being a source of unreasonable latency in an HTTP environment on a *n(i|u)x machine, but I'll take your word for it. Moving right along:

      I would never think to put images into my CGI directory -- they'd live out in the public HTML directory tree somewhere, and if they were coming from untrusted sources that directory would be in a partition mounted with the noexec option. The CGI application, if it has to, should be able to reach into that directory just fine, it being under HTTP document root. I'd still think very seriously about $q -> redirect($image_url); though.

      Back to the original problem: Without more information it's impossible to be certain, but if it were mine to fix I'd start by looking at whether or not there's a source of Unicode data in the system, based upon that log line you got when you stuffed a warn into the code.

[reply]
[d/l]
        Sorry for the late reply ;)
        Ther was indeed some kind of unicode-problem. My Mysql version had Unicode enabled which turned out to be a bad idea. I noticed this when i tried to insert some non-ascii-chars like ä ö ü into the Database. I now recompiled without unicode-support and can now insert those chars without problems, but the select of binary and text-data in 1 query still goes wrong :/.
        Since this project hast to be finished before saturday i'll go with 2 selects for now.
        Thank you for your suggestions!
[reply]

In Section Seekers of Perl Wisdom