On 24 Aug 2015, at 2:32am, Roger Binns <rogerb at rogerbinns.com> wrote:
> On 08/19/2015 05:56 PM, Paolo Bolzoni wrote: >> I left running the pragma quick check during the night and finished >> in 2 hours and 46 minutes, so it is about 8 times slower than in >> ext4. Zfs is an advanced filesystem plenty of features, but this >> speed difference is too much I think. > > I use btrfs which like zfs is also a copy on write filesystem. It is > possible for the files to get very fragmented which can result in > dismal performance, even on an SSD even for reads. Random small > writes especially aggravate this. btrfs has an autodefrag option that > addresses this in the background, and SQLite is specifically mentioned > as all the browsers use it behind the scenes as do many email clients. Paolo is using Linux which does not do read-ahead optimization like some versions of Windows. Therefore if he really is using an SSD then fragmentation is not an issue. The other thing that makes me think fragmentation is a red herring is the extent to which ZFS is slower. I have seen fragmentation make something take twice as long. I might even believe, under some weird situation, it makes something take 4 times as long. But not 6 or 8. My suspicion here is that there's a design fault in ZFS. I can't call it a bug under the rules of this list, since it is giving the right result, just ridiculously slowly. But whatever it is I'm betting that it's not a problem in the code for SQLite, it's a problem in the code for ZFS. And it'll take a ZFS expert to solve it, probably on a ZFS mailing list, not this one. The one possible exception would be if the SQLite database page size and the ZFS sector size are causing problems with one-another. But here too the best diagnosis would be done by a ZFS expert. And anyway, /nothing/ should cause a slow-down of a factor of 8. Simon.
