Lars Aronsson wrote: For reasons I don't understand, many people keep coming up with this idea, but it's a really bad idea. 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 thousands. Secondly, it makes atomic transactions impossible. It makes backing up a consistent state of the database/file-system mix virtually impossible. It is too difficult to move data around without violating the consistency of the construct. And lastly, it makes creating a full-text search index quite a bit harder. Convinced yet? ;-) Timwi

Timwi wrote: 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. I don't know if that is the case with Wikipedia's system using PHP + MySQL in 2003, but it was the case with one system using ASP/VB + MS SQL Server in 1999 and with another system using Java + Oracle 9i in 2002. In these years I made my entire living from telling people they should avoid blobs, and everybody was happy with the results. The fact is that Wikipedia, using blob I/O extensively, has been slow at times. This could of course be a coincidence. Now, much of the suspicion in these cases was drawn to the implementation of the ODBC/JDBC/equivalent drivers for the database communication, which are not open source in the case of Oracle and Microsoft, so there is reason to believe that MySQL + PHP would do better than the others. 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 agree that these are drawbacks, but they are not necessarily worse than the I/O bandwidth limitation. "Database backup" is in general more complex than "file backup", and the smaller the database, the easier it goes. You can still store a copy of each text (cur) in the database, and use that for searching. 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, through the socket where the bandwidth might be limited. No, sorry, only a real test would convince me. Right now Wikipedia is fast, so nobody is going to test this. -- Lars Aronsson (lars(a)aronsson.se) Aronsson Datateknik - http://aronsson.se/

Lars Aronsson lars-at-aronsson.se |wikipedia| wrote: Can you elaborate on how performance problems caused by slow sockets may manifest themselves? I executed the following query, which extract 17 MB of blobs in 1.5 seconds (on a slow computer). Are you perhaps referring to the general slowness added by retrieving data over network connections instead of using local files? $ time echo "SELECT cur_text FROM cur"|mysql wikidb >/dev/null real 0m1.449s user 0m0.990s sys 0m0.130s //E23

Lars Aronsson wrote: 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? 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. ... but no way as complex as "backup of a data structure comprising a database *and* a file system". The way to solve that is MemCacheD. ;-) Timwi

On Sat, Dec 13, 2003 at 02:01:30AM +0000, Timwi wrote: No at all. There are good reasons to expect much higher performance. * filesystem resides in kernel and uses extremely very fast context switches between kernelspace and userspace, database server has to communicate using sockets, what's MUCH slower. Because it's inside a kernel it has more access to disk drivers, can implement zero-copy data transfer, can use much faster locks, more efficient SMP etc. * unless you store databases on raw partitions, you have to cope with problems like inefficient double caching of data, discontiguous storage, and lack of information about physical structure of data on disk * filesystems are very specialized - they only provide a few operations for which they're extremely fast. If the operations needed happen to be those provided by the filesystem, we're likely to achieve really great performance, otherwise, we have to implement the operations needed ourselves, and the results probably won't be as good. I think that we're going to see databases that are at least partially kernel-space some day (some databases use raw partitions already). Just look at the difference in speed between Apache and Tux. Reiserfs4 is supposed to be something between a filesystem and a database, so what I'm saying isn't as off-base as it may seem to people used to LAMP paradigm. Maybe it'll become LTuxReiserfs4P soon. Backing up a Wiki was much easier in Phase I, when it was possible to use rsync. Mirroring Wikipedia after the switch to MySQL started to require about two orders of magnitude more bandwidth and CPU, and it became impossible to do it in real time. Now you can only synchronize once a day. And with regard to full-text index, it isn't any good now - it's slow, turned off most of the time, and usually returns rather bad results. Moving away from MySQL would allow us to create index that would provide better results.

On Mon, Dec 15, 2003 at 05:31:28AM +0000, Timwi wrote: They're not on the same machine any more. One big DB server + many small apache servers is not the only architecture possible. Of course we have to use what hardware we have available, so moving everything to filesystem is unrealistic at the moment. But maybe for images and the math cache, it's better to keep everything mirrored in real-time on every server. Then, filesystem-to-socket copying is being done by sendfile() system call. In the most optimal circumstances, it's going to tell network card driver to use disk cache as the source of data to be sent, without moving data around. Reasonably recent Apache should have some option to use it.