3 ms·

"read/write to a DB is always slower than a filesystem" This is patently false, reads/writes on a DB are often much faster which is why we use DB systems. Eve

by fleitz 15y ago

"read/write to a DB is always slower than a filesystem" This is patently false, reads/writes on a DB are often much faster which is why we use DB systems. Even image data especially on write can be faster when written to a DB vs. flat file because of sequential writes. Small files are slow to write on filesystems which is why we have DBs. In most cases with a high read / low write load it's certainly a better idea to put thumbnails on a CDN for a variety of reasons but to think that writing small chunks of data to a file system is always faster than a DB is to misunderstand what DBs do and when and why they are useful.

gnaritas 15y ago
> reads/writes on a DB are often much faster which is why we use DB systems. Not really. We use db's because file systems aren't ACID and we like to not lose data, and we like to be able to query data, and because we like multiple machines having access to that data. But the file system is generally much faster, especially when appending something like a log (yea, db's do that too, that's beside the point). > but to think that writing small chunks of data to a file system is always faster than a DB is to misunderstand what DBs do and when and why they are useful. I don't think so. Perhaps you can expand on that.
- maerek 15y ago
  There was a article published by Microsoft where they tested BLOB storage in a database with different size files. They found that there was actually an inflection point where the relational databases performed faster than the NTFS file system. Link to paper: http://research.microsoft.com/apps/pubs/default.aspx?id=64525 http://research.microsoft.com/apps/pubs/default.aspx?id=6452...
- fleitz 15y ago
  Write a program that creates 100,000 1k-8k byte files. Write a program that creates 100,000 1k-8k byte files in a zip file with no compression. Run multiple copies of these programs, see which writes files faster. Keep running the program(s) until the drive is 90% full. (Put the files in different directories if you feel this is fair.) Run the programs again. You'll notice that on a decent 7200 RPM drive the ZIP code is writing about 40-50MB/sec on a good drive. You'll probably be lucky to get 4-5MB/sec from the file code (unless you're on an SSD, even then it will probably be half) If you feel like it also create a database and do the same with a file table like "CREATE TABLE data (id int NOT NULL PRIMARY KEY, data varbinary(8000))" Inject 1000 to 2000 rows between commits. The DB should also outperform the file code, but probably not the ZIP because it has to write the data twice. The reason is that writing file to disk quickly produces a random IO pattern as the FS tries to allocate files all over the disk. Also, everytime you create a file you have to update a whole bunch of file descriptors, a modern file system is a B-tree and journal. Sounds awfully like a database right? Except this database is optimized for large files, at least 32-64kbytes and preferably in the hundreds of MB.
  - dmpk2k 15y ago
    This is specific to the type of filesystem in use. Something like ZFS does not exhibit these artifacts.
  - gnaritas 15y ago
    You're biassing the test towards small files and particular file systems that don't deal with them as well, and edge cases where you think the db is highly optimized; I'd imagine that's because you know outside those bounds the raw file system will kick its butt, especially given the tools that let you avoid the application entirely by streaming the file from disk.
    - AlisdairO 15y ago
      In fairness, while his test is biased towards dbs, it's also testing the claims that he made in his top level comment, which specified smaller files.
      - gnaritas 15y ago
        Which again, presumes I have to use multiple files. I can append small data to a log file faster than I can insert small rows of data to a database, and with much less effort. And my main point was, speed is not the reason we use databases, transactional semantics and query capabilities are.
        AlisdairO 15y ago
        Right, but he was talking about image files. edit: It's not that your point is wrong by any means, it's just that you're arguing against a point that he wasn't really emphasising. I don't think anyone, including him, would try to tell you that constant appending to a log file would be faster through a db than just a direct write to the file.

by fleitz 15y ago

gnaritas 15y ago

> reads/writes on a DB are often much faster which is why we use DB systems. Not really. We use db's because file systems aren't ACID and we like to not lose data, and we like to be able to query data, and because we like multiple machines having access to that data. But the file system is generally much faster, especially when appending something like a log (yea, db's do that too, that's beside the point). > but to think that writing small chunks of data to a file system is always faster than a DB is to misunderstand what DBs do and when and why they are useful. I don't think so. Perhaps you can expand on that.

maerek 15y ago
There was a article published by Microsoft where they tested BLOB storage in a database with different size files. They found that there was actually an inflection point where the relational databases performed faster than the NTFS file system. Link to paper: http://research.microsoft.com/apps/pubs/default.aspx?id=64525 http://research.microsoft.com/apps/pubs/default.aspx?id=6452...
fleitz 15y ago
Write a program that creates 100,000 1k-8k byte files. Write a program that creates 100,000 1k-8k byte files in a zip file with no compression. Run multiple copies of these programs, see which writes files faster. Keep running the program(s) until the drive is 90% full. (Put the files in different directories if you feel this is fair.) Run the programs again. You'll notice that on a decent 7200 RPM drive the ZIP code is writing about 40-50MB/sec on a good drive. You'll probably be lucky to get 4-5MB/sec from the file code (unless you're on an SSD, even then it will probably be half) If you feel like it also create a database and do the same with a file table like "CREATE TABLE data (id int NOT NULL PRIMARY KEY, data varbinary(8000))" Inject 1000 to 2000 rows between commits. The DB should also outperform the file code, but probably not the ZIP because it has to write the data twice. The reason is that writing file to disk quickly produces a random IO pattern as the FS tries to allocate files all over the disk. Also, everytime you create a file you have to update a whole bunch of file descriptors, a modern file system is a B-tree and journal. Sounds awfully like a database right? Except this database is optimized for large files, at least 32-64kbytes and preferably in the hundreds of MB.
- dmpk2k 15y ago
  This is specific to the type of filesystem in use. Something like ZFS does not exhibit these artifacts.
- gnaritas 15y ago
  You're biassing the test towards small files and particular file systems that don't deal with them as well, and edge cases where you think the db is highly optimized; I'd imagine that's because you know outside those bounds the raw file system will kick its butt, especially given the tools that let you avoid the application entirely by streaming the file from disk.
  - AlisdairO 15y ago
    In fairness, while his test is biased towards dbs, it's also testing the claims that he made in his top level comment, which specified smaller files.
    - gnaritas 15y ago
      Which again, presumes I have to use multiple files. I can append small data to a log file faster than I can insert small rows of data to a database, and with much less effort. And my main point was, speed is not the reason we use databases, transactional semantics and query capabilities are.
      - AlisdairO 15y ago
        Right, but he was talking about image files. edit: It's not that your point is wrong by any means, it's just that you're arguing against a point that he wasn't really emphasising. I don't think anyone, including him, would try to tell you that constant appending to a log file would be faster through a db than just a direct write to the file.