Lars Aronsson wrote:
Timwi wrote:
First of all, there is no reason to believe that reading from one file out of thousands is any faster than reading one record in the DB out of
I agree that this is how it should be, but I have seen some real systems where file I/O bandwidth was considerably higher than database I/O bandwidth, even after tuning all buffer sizes and kernel parameters.
OK, clearly you have more experience with this than myself, so I won't argue about your cases. But allow me to mention the two small bits of experience that I have to offer:
* A company I worked for produced a (Windows) application with a (MS-SQL) database backend where the database could potentially get rather large. The Blobs stored in this case were images. The thing here is that before my time as an employee, they attempted to use the file system for the images, rather than Blobs. It turned out to be a nightmare for exactly the reasons I mentioned. The customers were complaining about the difficulty of creating consistent backups and ensuring consistency across multiple revisions of the DB.
* The other thing I like to mention is LiveJournal. Their database backend is pretty impressive and handles the load of almost a million active users. They have never even dreamt of placing journal entries, audio posts or user pictures into files in a file system. The way they have it now they can easily create more database clusters and move users (and their data) around between clusters using a little Perl script. With a file system, that would be quite a bit more difficult.
That said, thinking about it in a much less practical and much more abstract/general way, I have to wonder why Blobs and files are any different. They are both a linear arrangement of bits. If the mechanics that file systems such as NTFS or ext-2 use were the most efficient known way of handling this data type, certainly databases would make use of it instead of implementing something knowingly less optimal?
Correct, although not entirely relevant. Atomic transactions is nothing you rely on with MySQL anyway, data seldom moves, and for some uses you can move the filename and let the file stay where it is.
I think you're tying your thoughts too much to the way Wikipedia and MySQL work today. There is no reason to believe that all the data will always be in one physical database. Months ago I had already suggested a system similar to LiveJournal's database clustering, and it was met with enthusiasm save for the fact that not enough servers are available. There is no reason to believe that the site architecture might not change some time in the future in such a way that moving data may become vital, as it is on LiveJournal already. And there is also no reason to believe that MySQL will never support atomic transactions, or if it does, that Wikipedia will never make use of it and start relying on it.
"Database backup" is in general more complex than "file backup"
... but no way as complex as "backup of a data structure comprising a database *and* a file system".
The vast amount of I/O over the database client-server socket is when every page view has to read the blob from the database to the (PHP) application
The way to solve that is MemCacheD. ;-)
Timwi