Cuvanje fajlova: MySql ili fajl sistem (pristup preko PHP-a)

[ Dr:Lun @ 21.06.2005. 21:18 ] @

Pozdrav!

Vec dosta poznata tema ali ne i toliko poznat odgovor - da li je bolje cuvati fajlove u bazi podataka ili ih tamo samo indeksirati a skladistiti ih na fajlsistemu (kao fajlove)?

Na netu postoji vec vise diskusijao o tome, i znam koje su prednosti i mane jednog i drugog ali...

Da li neko ima neko realno iskustvo sa ovom problematikom? (pristup je preko PHP-a)

Ja sam vrsio neka testiranja i evo rezultata:

Iz baze podataka:
---------------------------------------------------------------------------
Document Path: /test/binarydb/download.php?id=1
Document Length: 2167518 bytes

Concurrency Level: 1
Time taken for tests: 31.359 seconds
Complete requests: 1000
Failed requests: 0
Broken pipe errors: 0
Total transferred: -2127206296 bytes
HTML transferred: -2127449296 bytes
Requests per second: 31.89 [#/sec] (mean)
Time per request: 31.36 [ms] (mean)
Time per request: 31.36 [ms] (mean, across all concurrent requests)
Transfer rate: -67834.00 [Kbytes/sec] received
---------------------------------------------------------------------------

i direktno:
---------------------------------------------------------------------------
Document Path: /test/binarydb/duke.jpg
Document Length: 2167518 bytes

Concurrency Level: 1
Time taken for tests: 8.922 seconds
Complete requests: 1000
Failed requests: 0
Broken pipe errors: 0
Total transferred: -2127185296 bytes
HTML transferred: -2127449296 bytes
Requests per second: 112.08 [#/sec] (mean)
Time per request: 8.92 [ms] (mean)
Time per request: 8.92 [ms] (mean, across all concurrent requests)
Transfer rate: -238420.23 [Kbytes/sec] received
---------------------------------------------------------------------------

testirenje je vrseno preko ab-a (apache benchmark) i, kao sto se iz prilozenog moze videti, na 1000 zahteva se pristup slici preko php/mysql-a pokazao oko 4x sporiji. U principu, zadovoljavajuca je cinjenica da je vreme jos uvek prihvatljivo, nema neuspesnih pokusaja...

Ipak, nemam mogucnost da ovo testiram sa bazom od par hiljada slika, razlicitih velicina.

Evo i PHP koda preko koga sam vrsio pristup:

Code:

if(isset($_GET['id']))
{
    $db = mysql_connect("localhost", "", "");
    mysql_select_db("test", $db) or die(mysql_errno() . ": " . mysql_error() . "<br>");

    $id      = $_GET['id'];
    $query   = "SELECT name, type, size, content FROM upload WHERE id = '$id'";
    $result  = mysql_query($query) or die('Error, query failed');
    $row = mysql_fetch_array($result);
    $name = $row['name'];
    $type = $row['type'];
    $size = $row['size'];
    $content = $row['content'];

    header("Content-Disposition: attachment; filename=$name");
    header("Content-length: $size");
    header("Content-type: $type");
    echo $content;

    exit;
}

Dakle, ima li neko iskustva na ovu temu?
Ima li neko predlog kako optimizovati ovakav pristup?

Pozdrav,
Lun