zfs discuss - Oct 2008 - Setting per-file record size / querying fs/file record size?

I''ve a report that the mismatch between SQLite3''s default
block size and
ZFS'' causes some performance problems for Thunderbird users.

It''d be great if there was an API by which SQLite3 could set its block
size to match the hosting filesystem or where it could set the DB
file''s
record size to match the SQLite3/app default block size (1KB).

Is there such an API?  If not, is there an RFE I could add a call record
to?

Nico
--

On Mon, Oct 20, 2008 at 04:57:22PM -0500, Nicolas Williams
wrote:
> I''ve a report that the mismatch between SQLite3''s default
block size and
> ZFS'' causes some performance problems for Thunderbird users.
> 
> It''d be great if there was an API by which SQLite3 could set its
block
> size to match the hosting filesystem or where it could set the DB
file''s
> record size to match the SQLite3/app default block size (1KB).
> 
> Is there such an API?  If not, is there an RFE I could add a call record
> to?
To answer one of my own questions: I can use statvfs(2) to discover the
preferred block size of a filesystem.

On Mon, 2008-10-20 at 16:57 -0500, Nicolas Williams
wrote:
> I''ve a report that the mismatch between SQLite3''s default
block size and
> ZFS'' causes some performance problems for Thunderbird users.
I was seeing a severe performance problem with sqlite3 databases as used
by evolution (not thunderbird).

It appears that reformatting the evolution databases to a 32KB database
page size and setting zfs''s record size to a matching 32KB has done
wonders for evolution performance to a ZFS home directory.
> It''d be great if there was an API by which SQLite3 could set its
block
> size to match the hosting filesystem or where it could set the DB
file''s
> record size to match the SQLite3/app default block size (1KB).
IMHO some of the fix has to involve sqlite3 using a larger page size by
default when creating the database -- it seems to be a lot more
efficient with the larger page size.

Databases like sqlite3 are being used "under the covers" by growing
numbers of applications -- it seems like there''s a missing interface
here if we want decent out-of-the-box performance of end-user apps like
tbird and evolution using databases on zfs.

						- Bill

On Tue, Oct 21, 2008 at 03:43:08PM -0400, Bill Sommerfeld
wrote:
> On Mon, 2008-10-20 at 16:57 -0500, Nicolas Williams wrote:
> > I''ve a report that the mismatch between SQLite3''s
default block size and
> > ZFS'' causes some performance problems for Thunderbird users.
> 
> I was seeing a severe performance problem with sqlite3 databases as used
> by evolution (not thunderbird).
> 
> It appears that reformatting the evolution databases to a 32KB database
> page size and setting zfs''s record size to a matching 32KB has
done
> wonders for evolution performance to a ZFS home directory.
> 
> > It''d be great if there was an API by which SQLite3 could set
its block
> > size to match the hosting filesystem or where it could set the DB
file''s
> > record size to match the SQLite3/app default block size (1KB).
> 
> IMHO some of the fix has to involve sqlite3 using a larger page size by
> default when creating the database -- it seems to be a lot more
> efficient with the larger page size.
> 
> Databases like sqlite3 are being used "under the covers" by
growing
> numbers of applications -- it seems like there''s a missing
interface
> here if we want decent out-of-the-box performance of end-user apps like
> tbird and evolution using databases on zfs.
Agreed.

I''ve filed:

3452: os_unix.c:unixSectorSize() should use statvfs() to get pref blk size
http://www.sqlite.org/cvstrac/tktview?tn=3452

3454: hardcoded 32KB pagesize max may be too low
http://www.sqlite.org/cvstrac/tktview?tn=3454

and

6762083 sqlite3 default pagesize selection could be better (SQLite3 tickets
#3452 and 3454)

Nico
--

Hello Nicolas,

Monday, October 20, 2008, 10:57:22 PM, you wrote:

NW> I''ve a report that the mismatch between SQLite3''s
default block size and
NW> ZFS'' causes some performance problems for Thunderbird users.

NW> It''d be great if there was an API by which SQLite3 could set its
block
NW> size to match the hosting filesystem or where it could set the DB
file''s
NW> record size to match the SQLite3/app default block size (1KB).

NW> Is there such an API?  If not, is there an RFE I could add a call record
NW> to?

Maybe it would be also useful to provide an extra API so application
could set zfs recordsize for a given file instead a file system wide
propery (which should be a maximum allowed value). That way, assuming
MySQL, Oracle and other would start using such an extension, Oracle
would ask ZFS to match its recordsize to db_block_size automatically
and OOTB user experience would be much better.





-- 
Best regards,
 Robert Milkowski                            mailto:milek at task.gda.pl
                                       http://milek.blogspot.com

Bill Sommerfeld wrote:
> On Mon, 2008-10-20 at 16:57 -0500, Nicolas Williams wrote:
>> I''ve a report that the mismatch between SQLite3''s
default block size and
>> ZFS'' causes some performance problems for Thunderbird users.
> 
> I was seeing a severe performance problem with sqlite3 databases as used
> by evolution (not thunderbird).
> 
> It appears that reformatting the evolution databases to a 32KB database
> page size and setting zfs''s record size to a matching 32KB has
done
> wonders for evolution performance to a ZFS home directory.
I''m assuming this is local filesystem rather than ZFS backed NFS (which
is what I have).

What as setting the 32KB recordsize done for the rest of your home dir, 
or did you give the evolution directory its own dataset ?

-- 
Darren J Moffat

On Wed, 2008-10-22 at 10:30 +0100, Darren J Moffat
wrote:
> I''m assuming this is local filesystem rather than ZFS backed NFS
(which
> is what I have).
Correct, on a laptop.
> What has setting the 32KB recordsize done for the rest of your home
> dir, or did you give the evolution directory its own dataset ?
The latter, though it occurs to me that I could set the recordsize back
up to 128K once the databases (one per mail account) are created -- the
recordsize dataset property is read only at file create time when the
file''s recordsize is set.  (Having a new interface to set the
file''s
recordsize directly at create time would bypass this sort of gyration).

(Apparently the sqlite file format uses 16-bit within-page offsets; 32kb
is its current maximum page size and 64k may be as large as it can go
without significant renovations..)

						- Bill


						- Bill

[Default] On Tue, 21 Oct 2008 15:43:08 -0400, Bill Sommerfeld
<sommerfeld at sun.com> wrote:
>On Mon, 2008-10-20 at 16:57 -0500, Nicolas Williams wrote:
>>
>> I''ve a report that the mismatch between SQLite3''s
default block size
>> and ZFS'' causes some performance problems for Thunderbird
users.
>
> I was seeing a severe performance problem with sqlite3 databases
> as used by evolution (not thunderbird).
>
> It appears that reformatting the evolution databases to a 32KB database
> page size and setting zfs''s record size to a matching 32KB has
done
> wonders for evolution performance to a ZFS home directory.
Just a remark:
Increasing the SQLite page_size while keeping the same
[default_]cache_size will effectively increase the amount of memory
allocated to the SQLite cache, because the SQLite cache_size is
expressed in pages.
IMHO, for a fair performance comparison, one should half the
cache_size when the page_size is doubled.

[snip]
>
>						- Bill
>
-- 
  (  Kees Nuyt
  )
c[_]

On Wed, Oct 22, 2008 at 11:05:09PM +0200, Kees Nuyt
wrote:
> [Default] On Tue, 21 Oct 2008 15:43:08 -0400, Bill Sommerfeld
> <sommerfeld at sun.com> wrote:
> 
> >On Mon, 2008-10-20 at 16:57 -0500, Nicolas Williams wrote:
> >>
> >> I''ve a report that the mismatch between
SQLite3''s default block size
> >> and ZFS'' causes some performance problems for Thunderbird
users.
> >
> > I was seeing a severe performance problem with sqlite3 databases
> > as used by evolution (not thunderbird).
> >
> > It appears that reformatting the evolution databases to a 32KB
database
> > page size and setting zfs''s record size to a matching 32KB
has done
> > wonders for evolution performance to a ZFS home directory.
> 
> Just a remark:
> Increasing the SQLite page_size while keeping the same
> [default_]cache_size will effectively increase the amount of memory
> allocated to the SQLite cache, because the SQLite cache_size is
> expressed in pages.
> IMHO, for a fair performance comparison, one should half the
> cache_size when the page_size is doubled.
I''m not sure that I agree.  While the cache gets larger if you increase
the page size, the cache nonetheless caches the same number of integral
pages.  Each miss requires reading a whole page, and each addition to
the cache, when full, requires evicting another.

Going from 1KB pages to 32KB pages would require going from
cache_size=2000 to cache_size=62 by your logic, but 64 is a rather small
number -- sure there will be so many evictions for a sufficiently large
DB (at least 2MB) that you''ll be thrashing.

Conversely, 64MB of cache is a lot for most of the apps we''re talking
about, so perhaps I should leave the max default page size at 8KB.

Nico
--

On Wed, Oct 22, 2008 at 04:31:43PM -0500, Nicolas Williams
wrote:
> On Wed, Oct 22, 2008 at 11:05:09PM +0200, Kees Nuyt wrote:
> > Just a remark:
> > Increasing the SQLite page_size while keeping the same
> > [default_]cache_size will effectively increase the amount of memory
> > allocated to the SQLite cache, because the SQLite cache_size is
> > expressed in pages.
> > IMHO, for a fair performance comparison, one should half the
> > cache_size when the page_size is doubled.
> 
> I''m not sure that I agree.  While the cache gets larger if you
increase
> the page size, the cache nonetheless caches the same number of integral
> pages.  Each miss requires reading a whole page, and each addition to
> the cache, when full, requires evicting another.
On second thought, making the sqlite cache thrash != thrashing the disk.
The filesystem cache will still help -- there will be more data copies
or VM page flips, but that''s probably OK.

I''ll see how to make the default cache_size determination take the
DB''s
page_size into account.

Bill Sommerfeld writes:
 > On Wed, 2008-10-22 at 10:30 +0100, Darren J Moffat wrote:
 > > I''m assuming this is local filesystem rather than ZFS backed
NFS (which
 > > is what I have).
 > 
 > Correct, on a laptop.
 > 
 > > What has setting the 32KB recordsize done for the rest of your home
 > > dir, or did you give the evolution directory its own dataset ?
 > 
 > The latter, though it occurs to me that I could set the recordsize back
 > up to 128K once the databases (one per mail account) are created -- the
 > recordsize dataset property is read only at file create time when the
 > file''s recordsize is set.  

...almost.

The definitive recordsize for a file is set when the
filesize grows, for the first time, above the filessystem
recordsize property. Touching a file is not enough here.

 > (Having a new interface to set the file''s
 > recordsize directly at create time would bypass this sort of gyration).
 > 

I kind of agree here but we would need to change also how it
works also.

-r

 > (Apparently the sqlite file format uses 16-bit within-page offsets; 32kb
 > is its current maximum page size and 64k may be as large as it can go
 > without significant renovations..)
 > 
 > 						- Bill
 > 
 > 
 > 						- Bill
 > 
 > _______________________________________________
 > zfs-discuss mailing list
 > zfs-discuss at opensolaris.org
 > http://mail.opensolaris.org/mailman/listinfo/zfs-discuss