RE: [PHP] GD to database directly
[snip] ... some research ... [/snip] So, am I to assume that this issue about storing images in databases is dead? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] GD to database directly
At 12:36 PM -0500 7/19/06, Jay Blanchard wrote: [snip] ... some research ... [/snip] So, am I to assume that this issue about storing images in databases is dead? Yes, it was dead before it started, as it was the last time this issue was discussed. Simply put, there are tradeoffs, but both sides are so entrenched in their beliefs that they find it difficult to acknowledge that. So, as I did, try both and figure out what works for you. tedd -- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] GD to database directly
[snip] Kevin, you have more than once pointed out using a RAW format for operating the data system, what exactly do you mean? The database becomes the OS? If so, how do you set that up? It is something that I am not totally familiar with. [/snip] I did some research and went back to Kevin's original statement; [kevin] databases can be stored on RAW partitions, thus eliminating FILE SYSTEM overhead [/kevin] Here is what I found; [quote] raw partition vs filesystem store? What are pros/cons as far as performance, reliability, and ease of backup/restore? Anyone have any experience running Innodb on raw partition? raw partitions are beneficial only in some old OS/hardware configurations where fsync is extremely slow. In most computers, you only get a 5 % performance improvement from raw partitions. Any thoughts as to best filesystem for Innodb? What about pros/cons of journaled filesystems when in use with Innodb (i.e. transactions)? All major Linux file systems seem to have almost the same performance. [/quote - Heikki Tuuri] Use of MySQL on a RAW partition I limited to InnoDB type; (note the use of 'may' in the quote) [quote] http://dev.mysql.com/doc/refman/5.0/en/innodb-raw-devices.html You can use raw disk partitions as data files in the shared tablespace. By using a raw disk, you can perform non-buffered I/O on Windows and on some Unix systems without filesystem overhead, which may improve performance. [/quote] [quote] A raw device can be bound to an existing block device (e.g. a disk) and be used to perform raw IO with that existing block device. Such raw IO bypasses the caching that is normally associated with block devices. Hence a raw device offers a more direct route to the physical device and allows an application more control over the timing of IO to that physical device. This makes raw devices suitable for complex applications like Database Management Systems that typically do their own caching. [/quote - SCSI HOWTO] Based on what I read utilizing a RAW would probably require most PHP'ers to reconfigure their systems in major ways, including the database type, disk partitions, etc. I don't know about you, but most would consider Heikki Tuuri an expert on MySQL systems and Tuuri's thoughts indicate that RAW partitions vs. file systems are a wash unless you have fsync problems. It would appear that the only benefit comes from not having the file system doing the caching. [previous statement] There are a lot of us using MySQL (and PostGreSQL) along with PHP and in practice we have found that storing images in the database to be less than ideal from both a performance and backup POV. The reasons range from speed to overhead to ease of use. On our hardware. It not only has to do with storing and retrieving BLOB data, but also things like indexing, OS qwirks and the like. [/previous statement] I still stand by this, and amend by saying that using a RAW partition for database storage is typically beyond the needs of most PHP'ers as it gains nothing in performance and requires that much additional maintenance be performed on the database as well as the OS for maintenance of the RAW partition. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
Richard Lynch wrote: Given the number of posts here in PHP-General alone, of people getting tripped up by these things, I have concluded that cramming images into the DB is far more trouble than it is worth. It *seems* like a Good Idea until you actually do it for awhile, and then run into all these snags. Oddly enough, I have never run into them. Nor has terraserver which stores hundreds of thousands of images in its db, although not mysql. You may well have the needed skill set. Alas, the sheer number of problem posts here would indicate that that skillset is not the norm. I'll stop advising against images in the DB when you answer all the posts here from the problems it causes. :-) Given the right database, then posting raw data to it is not a problem. I would not classify MySQL as one that one would use or that. Personally I still use the rather crude database called Windows to store binary data and live with the backup and crash problems that involves. Simply because the customers have insisted, and signed to accept that they know the problems - even after showing how much more stable Linux is for the server! At the end of the day, binary data is stored on the disk. Whether it is stored in a 'proper' BLOB page set with an id in the related record or direct on the disk with the file name in the record, it still has to be accessed from the disk in order to use it. None of the existing OS/Database options provide the ideal solution and it is that which needs fixing :) - perhaps in another 100 years when Windows has been replaced with a real operating system ;) -- Lester Caine - G8HFL - L.S.Caine Electronic Services - http://home.lsces.co.uk Model Engineers Digital Workshop - http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/ Treasurer - Firebird Foundation Inc. - http://www.firebirdsql.org/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
This one time, at band camp, Richard Lynch [EMAIL PROTECTED] wrote: Assume, for the sake of argument, that your hard drive crashed. And your backup tape was invalid. And the weekly backup tape is also invalid. And, for good measuere, the monthly tape is just so out-of-date, that recovering from the crashed hard drive actually looks like an attractive option. In this scenario, it would not matter where your images were stored, however it would be a simple matter of hot swapping one of the raid drives. LONGBLOB Not big enough. 4GB is big enough for any sane image Kind regards Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] GD to database directly
[snip] ...a lot of stuff started by my original answer... [/snip] While this has been a fine debate I find that the discussion has deteriorated badly. Can we bring it back on point? There are a lot of us using MySQL (and PostGreSQL) along with PHP and in practice we have found that storing images in the database to be less than ideal from both a performance and backup POV. The reasons range from speed to overhead to ease of use. On our hardware. It not only has to do with storing and retrieving BLOB data, but also things like indexing, OS qwirks and the like. Kevin, you have more than once pointed out using a RAW format for operating the data system, what exactly do you mean? The database becomes the OS? If so, how do you set that up? It is something that I am not totally familiar with. If you are talking about RAW photo data I am familiar with that. As far as backup, I have a routine that backs up my databases AND the images, PDF's, DOC's, and other binary file format items that the data system points to. That wasn't hard to set up, and it is very reliable. I never have a problem with integrity. And finally, benchmarking. There would be three benchmarks to be conducted and concerned with here, as I have stated before; PHP and images in the OS's file system PHP and images in the MySQL database PHP and images in the OS's file system pointed to by data stored in the MySQL database. Usually only two are compared. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
On Fri, July 14, 2006 9:52 pm, Kevin Waterson wrote: I'm more concerned about the disaster recovery of a DB from a crashed hard drive, which has been cluttered up with binary data, making data recovery. One of the greatest benifits of binary DB storage is a single point of back up mysqldump db_name backup.sql -u username -p It really is as simple as that and no need to bother about referential integrity when rebuilding, simple mysql db_name back.sql -u username -p How much easier does it get? Assume, for the sake of argument, that your hard drive crashed. And your backup tape was invalid. And the weekly backup tape is also invalid. And, for good measuere, the monthly tape is just so out-of-date, that recovering from the crashed hard drive actually looks like an attractive option. Your images will make this final last stand not viable. Then your field storage size has to be large enough. LONGBLOB Not big enough. If it *WAS* big enough, we wouldn't have the posts here asking why their monster image didn't work in their database, and got corrupted. Given the number of posts here in PHP-General alone, of people getting tripped up by these things, I have concluded that cramming images into the DB is far more trouble than it is worth. It *seems* like a Good Idea until you actually do it for awhile, and then run into all these snags. Oddly enough, I have never run into them. Nor has terraserver which stores hundreds of thousands of images in its db, although not mysql. You may well have the needed skill set. Alas, the sheer number of problem posts here would indicate that that skillset is not the norm. I'll stop advising against images in the DB when you answer all the posts here from the problems it causes. :-) -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
On Thu, July 13, 2006 6:49 pm, Kevin Waterson wrote: This one time, at band camp, Richard Lynch [EMAIL PROTECTED] wrote: If you've benchmarked on YOUR hardware and have a proven savings, fine, post your tests and output. Already done in previous threads. Actually, to be pedantic, you've provided a reference to a dead tree magazine, which I don't happen to have... nowhere do I say the db is faster than file system. Just that various methods of db access are faster than others. I fail to see you point here? Do you have one? Yes. If your primary consideration is performance, then you need to test on YOUR hardware with YOUR setup. I have simply asked a question of those debunking the idea of binary storage how much of a performance hit do they think it takes, I have done _my_ homework, I wonder if others have? You are quick to ask for benchmarks. Where are yours? My primary consideration has never been performance. I'm more concerned about the disaster recovery of a DB from a crashed hard drive, which has been cluttered up with binary data, making data recovery. Other considerations are that your DB client/server buffer size has to be large enough to handle the images. Then your field storage size has to be large enough. Given the number of posts here in PHP-General alone, of people getting tripped up by these things, I have concluded that cramming images into the DB is far more trouble than it is worth. It *seems* like a Good Idea until you actually do it for awhile, and then run into all these snags. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
This one time, at band camp, Richard Lynch [EMAIL PROTECTED] wrote: I'm more concerned about the disaster recovery of a DB from a crashed hard drive, which has been cluttered up with binary data, making data recovery. One of the greatest benifits of binary DB storage is a single point of back up mysqldump db_name backup.sql -u username -p It really is as simple as that and no need to bother about referential integrity when rebuilding, simple mysql db_name back.sql -u username -p How much easier does it get? Then your field storage size has to be large enough. LONGBLOB Given the number of posts here in PHP-General alone, of people getting tripped up by these things, I have concluded that cramming images into the DB is far more trouble than it is worth. It *seems* like a Good Idea until you actually do it for awhile, and then run into all these snags. Oddly enough, I have never run into them. Nor has terraserver which stores hundreds of thousands of images in its db, although not mysql. Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
This one time, at band camp, Richard Lynch [EMAIL PROTECTED] wrote: It's coming FROM THE FILE SYSTEM. databases can be stored on RAW partitions, thus eliminating FILE SYSTEM overhead Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
This one time, at band camp, Richard Lynch [EMAIL PROTECTED] wrote: You really need to TEST your assumption about the DB being faster. Do you _really_ think I am speaking without testing any of this?? I once wrote an article on this very topic in PHP mag and published the benchmarks. Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
On Tue, July 11, 2006 11:38 am, Eric Butera wrote: I don't see a problem with storing images in the DB IF they aren't going to be continually accessed that way. For example say you have a script that lets a user upload an image and creates a small, medium, and large view out of it. Stick the original in the Db and then make your 3 sizes on the fly and cache the output so they are not continually created. This way you could down the road re-do all the sizes if you go through a site redesign or the client chooses they want them 450 instead of 400. =) And this would be better than just throwing the original in the file system in what way?... I'm sure not seeing a plus to having the original images in the DB instead of the file system here. I *just* do not understand why you'd want to cram this ginormous chunk of binary data into the DB. You're NOT going to sort on it. You're NOT going to filter it based on what's in the binary data. There are ZERO functions internal to SQL that can do anything at all useful with the data. Maybe if MySQL had GD integrated, and you could do: SELECT imagescaled(image, 400, 300) FROM images But it doesn't. And you're not the CIA using AI feature recognition to compare the photos in your DB with an SQL function. You're just shuffling data through layer after layer after layer of DB calls so it can sit on the same damned hard drive, clogging up the DB space, and making HD disaster recovery of the data in the DB far far far more difficult. And for what? To avoid a few lines of code in your web application to keep the db and file-system in sync? Puhlease. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
On Thu, July 13, 2006 1:25 pm, Kevin Waterson wrote: This one time, at band camp, Richard Lynch [EMAIL PROTECTED] wrote: It's coming FROM THE FILE SYSTEM. databases can be stored on RAW partitions, thus eliminating FILE SYSTEM overhead And are you actually doing this, or merely introducing a non-existent scenario? Even on a raw partition, the database still has to do *something* to move the drive heads and read the data. Provide a benchmark with the raw partition, images in DB, compared on equivalent hardware with a static file system approach, by all means. I'm sure you'll prove it provides some performance gain. And it probably has almost nothing to do with any list reader's real-world scenario. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
This one time, at band camp, Richard Lynch [EMAIL PROTECTED] wrote: If you've benchmarked on YOUR hardware and have a proven savings, fine, post your tests and output. Already done in previous threads. nowhere do I say the db is faster than file system. Just that various methods of db access are faster than others. I fail to see you point here? Do you have one? I have simply asked a question of those debunking the idea of binary storage how much of a performance hit do they think it takes, I have done _my_ homework, I wonder if others have? You are quick to ask for benchmarks. Where are yours? Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] GD to database directly
[snip] How much of a performance hit? [/snip] Here is an interesting read; http://mysqldump.azundris.com/archives/36-Serving-Images-From-A-Database .html Your system receives a number of file read requests, requesting it to load a number of blocks from the disk into the mysqld process. Eventually, the blocks show up in the buffer cache, and mysqld is woken up, receiving file data for the read requests. But I will say this, in this day and age there may not be the performance hit that we had in days gone by due to improvements in software and hardware, so I may have misspoken. I do believe in a one for one comparison where we use PHP and the file system or PHP and MySQL on the same box that, however minimal, there will be a difference favoring the file system using the same images. I also believe that these methods should be compared to using MySQL to hold data about the image so that the benchmarks could be run like this; PHP + File System PHP + MySQL (where MySQL stores a BLOB) PHP + MySQL (data only) + File System The reason for the last one is that is the likely way these technologies would be used together. I spend a lot of time utilizing the MySQL command line and other similar query analysis tools for MS-SQL and Oracle. Image BLOBs become more of a hindrance than a help at this level because these tools are not designed to properly parse and display the files. This is important because it adds to my bias against storing images in a database. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
On 7/11/06, Adam Zey [EMAIL PROTECTED] wrote:
The time taken per request, though (and that's about all we can get with
a concurrency as low as 5) doesn't tell us much. We also don't know
exactly what the PHP code is doing, how it does it, how your database is
organized/indexed/accessed, if you have any PHP accelerators installed
(Normally the PHP script would be reconverted to bytecode every
execution), etc.
Additionally, your test isn't really MySQL versus filesystem, it's
PHP+MySQL versus filesystem. Perhaps a more useful comparison would be
PHP+Filesystem versus PHP+MySQL. As in, the same PHP script for both
benchmarks, except one copy uses file_get_contents and echo (Closer
match than readfile, since MySQL would require loading the file into
memory) and the other uses MySQL. This would be a closer match that
would tell us how much latency is induced by the actual database itself
rather than PHP and loading stuff into memory.
Regards, Adam.
I was just running a simple test based on how I work with images. I
usually upload one, resize it on the fly, and save a new image which I
directly link to. Therefore I don't need anything to read and send a
raw image through PHP. If you think that is a valid test, then
perhaps you should use your own time to achive your adequate
benchmark.
As for my script:
?php
$link = mysql_connect('localhost', 'test', 'test') or die('unable to connect');
mysql_select_db('test') or die('unable to select');
$sql = SELECT imagedata FROM images WHERE id = 1;
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
header('Content-Type: image/jpeg');
echo $row['imagedata'];
There is an auto inc primary on the id column.
I have APC disabled during this test so that isn't a problem.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
On Tue, July 11, 2006 1:48 am, Kevin Waterson wrote: This one time, at band camp, Austin Denyer [EMAIL PROTECTED] wrote: It is generally accepted that storing things like that in a database is a Bad Thing. Much better to store the images as files and store the path in the database. Storing paths and databases in slower than just storing images in the db or simnply storing them on the server. You are making a db request and a file system request. Rather than a single request to the db. What is a file system if not a database? Operating System File System: A highly-optimized well-tested database for large-sized binary data. Or... What is a database if not a file system? :-) You really need to TEST your assumption about the DB being faster. Where do you think the DB gets the humongous chunk of binary data? You think it's magic? No. It's coming FROM THE FILE SYSTEM. Ultimately, the hard drive is still getting pounded just as hard, by most DBs for most images, if you cram the image into the DB, because the chunk of data is: A) too large to be in RAM, and B) stored as a 'BLOB', which in most DBs, means it's stuck into a separate file, or at least into a separate offset within a monster file, which means the hard drive still has to do a seek to get to it. So, really, you're not saving anything, on MOST hardware setups, *unless* the images are all very very very tiny. If you've benchmarked on YOUR hardware and have a proven savings, fine, post your tests and output. If you have no benchmarks, I suggest you do that before you claim it's faster :-) -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
This one time, at band camp, Jay Blanchard [EMAIL PROTECTED] wrote: 1. Do not store images in a database, it is just a bad idea from a performance perspective (as has been covered many times heretofore). rubbish, has been proven other wise, you are quoting old wives tales Please provide benchmarks to back this. Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
This one time, at band camp, Austin Denyer [EMAIL PROTECTED] wrote: It is generally accepted that storing things like that in a database is a Bad Thing. Much better to store the images as files and store the path in the database. Storing paths and databases in slower than just storing images in the db or simnply storing them on the server. You are making a db request and a file system request. Rather than a single request to the db. What is a file system if not a database? Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
This one time, at band camp, Larry Garfield [EMAIL PROTECTED] wrote: There may be other reasons you'd want to store binary data in an SQL database, but it will always be a performance hit over just passing a normal file that can be streamed right off the disk to the server's NIC. How much of a performance hit? Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
On 7/11/06, Kevin Waterson [EMAIL PROTECTED] wrote: How much of a performance hit? Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Here's to being bored on lunch. :) Now to add my two cents to the convo: I don't see a problem with storing images in the DB IF they aren't going to be continually accessed that way. For example say you have a script that lets a user upload an image and creates a small, medium, and large view out of it. Stick the original in the Db and then make your 3 sizes on the fly and cache the output so they are not continually created. This way you could down the road re-do all the sizes if you go through a site redesign or the client chooses they want them 450 instead of 400. =) Via MySQL 4.1 PHP 5.1.4: erics:~ eric$ ab -n500 -c5 http://localhost/fs_vs_db/viewimage_db.php This is ApacheBench, Version 1.3d $Revision: 1.73 $ apache-1.3 Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Copyright (c) 1998-2002 The Apache Software Foundation, http://www.apache.org/ Benchmarking localhost (be patient) Completed 100 requests Completed 200 requests Completed 300 requests Completed 400 requests Finished 500 requests Server Software:Apache/1.3.33 Server Hostname:localhost Server Port:80 Document Path: /fs_vs_db/viewimage_db.php Document Length:25256 bytes Concurrency Level: 5 Time taken for tests: 4.323 seconds Complete requests: 500 Failed requests:0 Broken pipe errors: 0 Total transferred: 12727000 bytes HTML transferred: 12628000 bytes Requests per second:115.66 [#/sec] (mean) Time per request: 43.23 [ms] (mean) Time per request: 8.65 [ms] (mean, across all concurrent requests) Transfer rate: 2944.02 [Kbytes/sec] received Connnection Times (ms) min mean[+/-sd] median max Connect:0 00.4 010 Processing:1442 12.6 38 147 Waiting: 1442 12.6 38 147 Total: 1442 12.6 38 147 Percentage of the requests served within a certain time (ms) 50% 38 66% 40 75% 44 80% 46 90% 56 95% 67 98% 78 99% 97 100%147 (last request) Raw Image: erics:~ eric$ ab -n500 -c5 http://localhost/fs_vs_db/cloud.jpg This is ApacheBench, Version 1.3d $Revision: 1.73 $ apache-1.3 Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Copyright (c) 1998-2002 The Apache Software Foundation, http://www.apache.org/ Benchmarking localhost (be patient) Completed 100 requests Completed 200 requests Completed 300 requests Completed 400 requests Finished 500 requests Server Software:Apache/1.3.33 Server Hostname:localhost Server Port:80 Document Path: /fs_vs_db/cloud.jpg Document Length:25256 bytes Concurrency Level: 5 Time taken for tests: 0.624 seconds Complete requests: 500 Failed requests:0 Broken pipe errors: 0 Total transferred: 12775000 bytes HTML transferred: 12628000 bytes Requests per second:801.28 [#/sec] (mean) Time per request: 6.24 [ms] (mean) Time per request: 1.25 [ms] (mean, across all concurrent requests) Transfer rate: 20472.76 [Kbytes/sec] received Connnection Times (ms) min mean[+/-sd] median max Connect:0 10.7 1 5 Processing: 3 56.6 4 131 Waiting:1 46.7 3 130 Total: 3 66.5 5 131 Percentage of the requests served within a certain time (ms) 50% 5 66% 5 75% 5 80% 6 90% 7 95% 10 98% 11 99% 19 100%131 (last request) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
Eric Butera wrote: On 7/11/06, Kevin Waterson [EMAIL PROTECTED] wrote: How much of a performance hit? Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Here's to being bored on lunch. :) Now to add my two cents to the convo: I don't see a problem with storing images in the DB IF they aren't going to be continually accessed that way. For example say you have a script that lets a user upload an image and creates a small, medium, and large view out of it. Stick the original in the Db and then make your 3 sizes on the fly and cache the output so they are not continually created. This way you could down the road re-do all the sizes if you go through a site redesign or the client chooses they want them 450 instead of 400. =) Via MySQL 4.1 PHP 5.1.4: erics:~ eric$ ab -n500 -c5 http://localhost/fs_vs_db/viewimage_db.php This is ApacheBench, Version 1.3d $Revision: 1.73 $ apache-1.3 Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Copyright (c) 1998-2002 The Apache Software Foundation, http://www.apache.org/ Benchmarking localhost (be patient) Completed 100 requests Completed 200 requests Completed 300 requests Completed 400 requests Finished 500 requests Server Software:Apache/1.3.33 Server Hostname:localhost Server Port:80 Document Path: /fs_vs_db/viewimage_db.php Document Length:25256 bytes Concurrency Level: 5 Time taken for tests: 4.323 seconds Complete requests: 500 Failed requests:0 Broken pipe errors: 0 Total transferred: 12727000 bytes HTML transferred: 12628000 bytes Requests per second:115.66 [#/sec] (mean) Time per request: 43.23 [ms] (mean) Time per request: 8.65 [ms] (mean, across all concurrent requests) Transfer rate: 2944.02 [Kbytes/sec] received Connnection Times (ms) min mean[+/-sd] median max Connect:0 00.4 010 Processing:1442 12.6 38 147 Waiting: 1442 12.6 38 147 Total: 1442 12.6 38 147 Percentage of the requests served within a certain time (ms) 50% 38 66% 40 75% 44 80% 46 90% 56 95% 67 98% 78 99% 97 100%147 (last request) Raw Image: erics:~ eric$ ab -n500 -c5 http://localhost/fs_vs_db/cloud.jpg This is ApacheBench, Version 1.3d $Revision: 1.73 $ apache-1.3 Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Copyright (c) 1998-2002 The Apache Software Foundation, http://www.apache.org/ Benchmarking localhost (be patient) Completed 100 requests Completed 200 requests Completed 300 requests Completed 400 requests Finished 500 requests Server Software:Apache/1.3.33 Server Hostname:localhost Server Port:80 Document Path: /fs_vs_db/cloud.jpg Document Length:25256 bytes Concurrency Level: 5 Time taken for tests: 0.624 seconds Complete requests: 500 Failed requests:0 Broken pipe errors: 0 Total transferred: 12775000 bytes HTML transferred: 12628000 bytes Requests per second:801.28 [#/sec] (mean) Time per request: 6.24 [ms] (mean) Time per request: 1.25 [ms] (mean, across all concurrent requests) Transfer rate: 20472.76 [Kbytes/sec] received Connnection Times (ms) min mean[+/-sd] median max Connect:0 10.7 1 5 Processing: 3 56.6 4 131 Waiting:1 46.7 3 130 Total: 3 66.5 5 131 Percentage of the requests served within a certain time (ms) 50% 5 66% 5 75% 5 80% 6 90% 7 95% 10 98% 11 99% 19 100%131 (last request) The time taken per request, though (and that's about all we can get with a concurrency as low as 5) doesn't tell us much. We also don't know exactly what the PHP code is doing, how it does it, how your database is organized/indexed/accessed, if you have any PHP accelerators installed (Normally the PHP script would be reconverted to bytecode every execution), etc. Additionally, your test isn't really MySQL versus filesystem, it's PHP+MySQL versus filesystem. Perhaps a more useful comparison would be PHP+Filesystem versus PHP+MySQL. As in, the same PHP script for both benchmarks, except one copy uses file_get_contents and echo (Closer match than readfile, since MySQL would require loading the file into memory) and the other uses MySQL. This would be a closer match that would tell us how much latency is induced by the actual database itself rather than PHP and loading stuff into memory. Regards, Adam. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
On Monday 10 July 2006 09:31, Peter Lauri wrote: [snip] 1. Do not store images in a database, it is just a bad idea from a performance perspective (as has been covered many times heretofore). [/snip] Is that really the case? Is this not depending on the application? :) My application will never grow, and I can easily just change the file storage procedure by changing in my file storage class if a change will be necessary. /Peter The process of loading a script that then loads a database driver and makes an SQL call to retrieve a bitstream and set the correct header and then print the bitstream is slower than the process of sending a URL to the browser which can then make a simple HTTP request against a static file. Always. There may be other reasons you'd want to store binary data in an SQL database, but it will always be a performance hit over just passing a normal file that can be streamed right off the disk to the server's NIC. -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it. -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] GD to database directly
Best group member, I am creating images via GD and want to save them to the database. Right now I save them to the disk and then save them to the database. Is it possible to write them directly to the database and skip the middle step where I temporary write it to the hard disk? Best regards, Peter Lauri
RE: [PHP] GD to database directly
[snip]
I am creating images via GD and want to save them to the database. Right
now
I save them to the disk and then save them to the database. Is it
possible
to write them directly to the database and skip the middle step where I
temporary write it to the hard disk?
[/snip]
1. Do not store images in a database, it is just a bad idea from a
performance perspective (as has been covered many times heretofore).
2. If you insist on storing the image in a database you can use any of
the image output functions, for instance;
INSERT INTO `table` (`picture`)
VALUE ('.imagegd($resource, 'imagename').')
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] GD to database directly
[snip] 1. Do not store images in a database, it is just a bad idea from a performance perspective (as has been covered many times heretofore). [/snip] Is that really the case? Is this not depending on the application? :) My application will never grow, and I can easily just change the file storage procedure by changing in my file storage class if a change will be necessary. /Peter -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] GD to database directly
Peter Lauri wrote: [snip] 1. Do not store images in a database, it is just a bad idea from a performance perspective (as has been covered many times heretofore). [/snip] Is that really the case? Is this not depending on the application? :) My application will never grow, and I can easily just change the file storage procedure by changing in my file storage class if a change will be necessary. It is generally accepted that storing things like that in a database is a Bad Thing. Much better to store the images as files and store the path in the database. Regards, Austin. signature.asc Description: OpenPGP digital signature
