Serving Images From A Database
So, most of the “I want images in MySQL” conversations are terminated with “Don’t.”People do say this for a reason. Here is the story of your request going through the system:
Your systems network buffer fills with a new packet containing a new request from the network card. The system delivers the packet to your Apache, switching from kernel mode into user mode.
Apache processes the packet, handing it internally to its mod_php for processing. The mod_php parses the request, constructing a SQL query for the image blob, and pushes the SQL query into a kernel buffer, switching from user mode into kernel mode.
Your systems network buffer fills with a new packet containing a new SQL query from the network layer. The system delivers the packet to your mysqld, switching from kernel mode into user mode.
mysqld processes the packet, handing it internally to the query parser, generating a query cache miss, because the result set is too large to enter the query cache, ever. The parser, the optimizer and the storage engine work to access a very wide table, with a badly packed index tree (especially if it happens to be an InnoDB table). They generate a number of file system accesses on the way. mysqld goes to sleep on the read requests, and the context switches from user mode to kernel mode.
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. The context switches from kernel to user mode.
mysqld takes the data read, and constructs a result set from it. It pushes the result set into the network buffers, and goes to sleep on the writes, switching from user mode to kernel mode.
The kernel pushes the network buffer with the result set into the Apache process, and switches from kernel mode to user mode.
mod_php inside Apache receives a result set from the network, and copies it into a network buffer, pushing it to the requesting user. It goes to sleep on the write, switching from user mode to kernel mode.
The kernel receives a network buffer, eventually pushing the result into the network card, writing it to the user.
The entire process is repeated multiple times, because the image is too large to be read and written in a single operation. A thread in mysqld, and an instance of httpd are occupied doing this, until the end users client connection finally has sucked off the last few bytes of the image through its slow connection.
This is your lighttpd, serving a static file from a filesystem:
Your systems network buffer fills with a new packet containing a new request from the network card. The system delivers the packet to your lighty, switching from kernel mode into user mode.
Lighty parses the request, mapping it into a static file on disk. Lighty calls sendfile, switching from user mode to kernel mode.
The kernel sendfile() function processes filesystem buffer cache blocks, turning them inside the kernel into network buffers with no return to user mode. The kernel pushes these blocks out using the network card. When the file has been served, lighty is notified about that.
Moral: You never want to feed from a database what can be fed as static content.
Trivia: Lighttpd is a fast and light httpd server with extremely good support for static images and FCGI. It was written and and is currently maintained by MySQL employee Jan Kneschke.
MySQL-dump on : Serving Images from a File System
In Serving Images from a Database, Sheeri responded to me: I appreciate your ideas, but you haven't read what I've written: "the sheer number of files is enough to kill any os." I'm serious about that. we're talking 36 million images -- that's 36 mill
Die wunderbare Welt von Isotopp on : Ihr Imageserver ist ein Datenleck
Eine monstergefährliche Sicherheitslücke bei StudiVZ wird derzeit als Sau der Woche durch Kleinbloggersdorf getrieben. Was passiert? StudiVZ betreibt einen Webserver für dynamische Anwendungen und einen zweiten Webserver für statische Daten, zum Beispi
Display comments as Linear | Threaded
Jan Steinman on :
Never say never!
I guess TEXT fields should be the next to go. But why stop there? Let's make every read access from a database supply that information from a file instead?
I've benchmarked my application with images in BLOBs and as separate files. The difference was single-digit percentages. I'm willing to pay that for the ease of backup and other niceties of using a single storage mechanism.
That's not to say that single-digit performance increases may be worth pursuing, or that there aren't other reasons for storing images in files instead of in BLOBs. I'm not even arguing against most images being in files.
But a dogmatic banning of images in the database does not impress me at all!
Isotopp on :
One interesting question in benchmarking is performance difference, and you say you have seen only a little performance difference between both variants.
The other interesting question in benchmarking is "What is the benchmark maxing out on?". A benchmark is usually "ramp up the load on a system and measure the number of results as well as the latency seen in generating these". Usually you get a typical saturation curve such as those shown in my Linuxtag talk.
When the system is saturated you have not only established a performance plateau, but you also can tell why there is a plateau here - what is the ressource that is limiting performance in this particular case.
Knowing the reason is important, because in some cases the ressource that is maxed out is not the one you are interested in for performance metering. For example, if you in your case see only a few percent performance difference in your two cases, it may be because both times the system maxes out on something unrelated such as the performance limit of the load generator, the network interface or external internet connection or something else that is not really part of the "network buffer - webserver - database server - disk" pipeline, which is what you really want to meter here.
Dave on :
I'm willing to bet you didn't do this while load-testing. For a single request, you are very correct, the performance difference is negligible. But try serving 100 users each grabbing images from a database, and see if you're still hitting single digit performance differences. I'm willing to bet the relationship is almost exponential for performance by the number of requests. In the end, the filesystem is always the best container for information you don't plan on changing a lot. Leave the database to hold DATA.
Peter Hodge on :
Yep, so you have a background script which runs every 2 minutes and pulls new images out of the database and writes them to disk.
sheeri on :
darix on :
even taking into account some overhead for syncing the new files to all servers. it is still lower than the DB overhead for each request.
you could even use some kind of smart caching:
1. write to DB
2. on request check if there is a local file. (if you are a lighttpd user, checkout mod_cml and power-magnet.)
3. on local cache miss -> request file from DB -> write to disk. serve file.
in the next round trip you serve the static file.
4. in case you are an apache user mod_rewrite's REWRITE_COND ! -f
should help you.
Isotopp on :
In another life I was working with a large freemail provider over here in Germany. This outfit had quite a few image server, and in their case, rsync was being used as a distribution mechanism for static files, and a specially configured webserver as a delivery mechanism for static files.
Using dynamically generated content instead has a very noticeable performance impact, because of the interprocess communication overhead outlines above, and because the memory usage per slot is much higher in the dynamic case, limiting the degree of available parallelism.
Databases have their place, but (compared to filesystems) handling large binary objects is not one of their strengths - and that is even before you start thinking about handling objects larger than max_allowed_packet inside your database.
Sheeri on :
I'm serious about that. we're talking 36 million images -- that's 36 million files (no thumbnails, they're all reduced size) plus directories to store them in. We have cacheing tuned very well, and it's still kills the OS.
Physically we CANNOT store these images on disk as individual files. That's what we've been doing, and the performance is horrible. In a MyISAM database, each table is one file, and thus MUCH easier to handle. In an innodb database, all innodb tables are encompassed in one file (or you can split them out).
So performance will be MUCH improved. We have a unique situation, to be sure; photos are CONSTANTLY being looked at. These are personal ad images and so are being looked at at a rate that is much higher than, say, a photo site.
I agree that in most situations, your solution works. I can tell you from having done it that way, that we're reaching the limit on that. So as the first poster said, "never say never".
Wouter Verhelst on :
The problem really is not your operating system, but the efficiency with which the file system implements "finding a file in the file system" on such high numbers. Some file systems (e.g., ReiserFS, though I wouldn't recommend using that) have been specifically optimized for "having millions of files in one directory"; if your needs are that specific, you really want to look into such things.
Sheeri on :
Daniel on :
Ever heard of ZFS? ZFS wouldn't break a sweat under that kind of setup I'm more than willing to bet. So please, don't go spreading FUD, do your homework next time and use ZFS if you've seriously got that many photos.
PHP Dreamer on :
In essence, what should I be shooting for? A "Flash" 'ala defunkt Macromedia" server operation? Anything in MYSQL land that is competitive?
Please don't say Mircrosoft anything since I will break out in hives at the sound of Mr. Softy!!
KesheR on :
David Sheldon on :
alex on :
to static content. Instead of sendind that redirect back to browser lighty might serve that redirect the same way if browser sent a new request for that URL in response to a redirect.
Saying "redirect" I mean 3xx HTTP response code family.
darix on :
alex on :
I want fast-cgi script to see _all_ requests and decide what static contet should be served since there is no static mapping between any information sent by browser and static content.
Lets consider current (not so good) scenario:
* suppose some URL is handled by some script that gets request from browser
* this script makes decision which static content matches a request based on some criteria and sends a redirect to browser
* given such a response browser sends another request for that static resource
* HTTP server handles request for static content
In better scenario there should be no phase when browser receives redirect and send another request. That could be handled by HTTP server internally
darix on :
alex on :
I thought it was about mod_cml of lighttpd and http://www.lighttpd.net/documentation/cml.html says the following:
CML (Cache Meta Language) wants to solves several problems:
* dynamic content needs caching to perform
* checking if the content is dirty inside of the application is usually more expensive than sending out the cached data
* a dynamic page is usually fragmented and the fragments have different livetimes
* the different fragements can be cached independently
darix on :
see the complete documentation of it. :)
alex on :
RewriteMap mapname prg:/path/to/request-mapper
Jay on :
1) using Apache to serve images stored in the database
2) using Lighttpd to serve images stored as files
It seems to me that your message would be more effective if you would stick to one server and use that server in both sides of the comparison. (ie., use the server as the control in the experiment.)
As it stands, you are making the second part of the comparison appear shorter and simpler than it probably would be if it also used Apache.
Tom on :
Rails will automatically create a cached version of the image after it's first requested - just as it would a dynamic page - if you set up your controllers correctly. Pretty cool.
Anthony on :
Some one snottily asks: Why not write text out the files system? Because text can be queried and BLOBs can’t. Is that not obvious? That is why rational database are used: To retrieve information.
And why all this talk about NFS? Use a cluster! They are cheap and easy to set up. You have to put some thought into it. Take a MD5 hash of the filename or the file’s data. MOD that number with the number of computers in your cluster and write the file to that computer number.
For example, you get an MD5 of 123456789. Then you MOD it with 10 because you have ten computers in the cluster getting 9. Store the file on computer number 9. And store the file in a directory path like /1/2/3/4/5/6/7/8/9/My Picture.jpg. Then put all the metadata in MySQL. Someone wants the picture:
The web server goes directly to its local harddrive. Well it should be a LUN over fibre channel in a SAN cluster with redundant failover and WAN replication. Or just use rsync. There is so much stuff out there for files. I will leave researching that as exercise for the reader.
BTW, You can MOD it again and spread it out over several local harddrives. As harddrives fill you can easily move the data and increase the number of drives. Oh and fibre really is spelled ‘fibre’ and not fiber channel.
Sheeri on :
The most common case is if the entry is deleted in the database but the file remains, or the file is deleted but the entry remains.
There is simply no way to ensure data integrity if you store files on a filesystem. If data integrity is not important, that's OK, but make sure it's a conscious decision.
Isotopp on :
In Linux, the standard way to do this is fam.
Fam is a file alteration monitoring service. With it, you can receive
signals when files are created or changed.
Bryan Ledford on :