Re: [sqlite] [PATCH] cache preloading

[Scott Hess]

On Mon, Aug 16, 2010 at 7:58 PM, Shawn Wilsher  wrote:
> On Mon, Aug 16, 2010 at 5:13 PM, Paweł Hajdan, Jr.  
> wrote:
>> Is it something you'd like to include in SQLite? If so, does the patch need
>> any adjustments before that's possible?
> I'm slightly concerned about licensing here - do we know what license
> this patch is?  Can we get clarification from the chromium team?

All of fts1/2/3 was provided by Google, and this code was written
before Chromium was open-sourced, so we should be good on the
licensing front.

That said, it's possible we should have appropriate documentation
filed for the Chromium project as distinct from Google?  Or is it
sufficient to just make sure that such things are submitted from
@google.com email addresses?  [The Google people working on Chromium
generally have accounts at both domains.]

-scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [PATCH] cache preloading

[Shawn Wilsher]

On Mon, Aug 16, 2010 at 5:13 PM, Paweł Hajdan, Jr.
 wrote:
> Is it something you'd like to include in SQLite? If so, does the patch need
> any adjustments before that's possible?
I'm slightly concerned about licensing here - do we know what license
this patch is?  Can we get clarification from the chromium team?

Cheers,

Shawn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [PATCH] cache preloading

[Simon Slavin]

On 17 Aug 2010, at 1:13am, Paweł Hajdan, Jr. wrote:

> Could you take a look at the original Chromium patch:
> http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/preload-cache.patch?revision=26596=markup?
> 
> Is it something you'd like to include in SQLite? If so, does the patch need
> any adjustments before that's possible?

Do you have any figures for testing, please ?  In other words, do you have 
proof that in real world usage this does actually have some advantage ?  If 
possible, produce figures for more than one OS, since Windows takes a great 
deal of advantage of read-ahead caching, whereas Unix gets hardly any increase 
in speed from it.

Note that a fast million writes to a database is not a real-world situation.  
Chromium, for instance, is unlikely to ever write a million rows without pauses 
for reading.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [PATCH] cache preloading

[Paweł Hajdan , Jr .]

ping

Could you take a look at the original Chromium patch:
http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/preload-cache.patch?revision=26596=markup?

Is it something you'd like to include in SQLite? If so, does the patch need
any adjustments before that's possible?

On Wed, Aug 11, 2010 at 23:23, Damian Pietras  wrote:

> On Tue, Aug 10, 2010 at 05:46:26PM -0700, Paweł Hajdan, Jr. wrote:
> > So this is another chromium patch I'd like to submit:
> >
> http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/preload-cache.patch?revision=26596=markup
> >
> > I'm not the author of that one, but the main idea seems to be that with
> > preloading we get better performance with many scenarios chromium uses
> > sqlite (like for omnibox local suggestions). What do you think about the
> > patch and the idea? Is there better way to do the same thing? Would you
> like
> > to see some benchmarks?
>
> I thought about something like that. I took a little different approach:
> I've put posix_fadvise(fd, 0, 0, POSIX_FADV_WILLNEED) just after opening
> the database file and in my case with a fragmented database (may
> insert/update/delete without vacuum) and cold page it was a huge
> difference.
>
> Using posix_fadvise() is asynchronous without any separate thread and
> opening the database isn't slowed down - you don't need to wait
> until the file is loaded.
>
> I'm interested in speeding up SQLite when opening a database that is not
> in the OS's page cache. Next thing I want to try is to preload only
> selected tables/indexes with help of posix_fadvise(). I want to call
> posix_fadvice for every child page when traveling through the btree just
> before moving down to the first child of the current page.  I hope
> this will be faster that a regular table/index read because the OS/disk
> will have the IO queue full and can reorder read requestes if necessary.
> It will be slower than preloading the whole DB, but I don't want to
> waste disk bandwidth for that.
>
> --
> Damian Pietras
>
> http://www.linuxprogrammingblog.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [PATCH] cache preloading

[Damian Pietras]

On Tue, Aug 10, 2010 at 05:46:26PM -0700, Paweł Hajdan, Jr. wrote:
> So this is another chromium patch I'd like to submit:
> http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/preload-cache.patch?revision=26596=markup
> 
> I'm not the author of that one, but the main idea seems to be that with
> preloading we get better performance with many scenarios chromium uses
> sqlite (like for omnibox local suggestions). What do you think about the
> patch and the idea? Is there better way to do the same thing? Would you like
> to see some benchmarks?

I thought about something like that. I took a little different approach:
I've put posix_fadvise(fd, 0, 0, POSIX_FADV_WILLNEED) just after opening
the database file and in my case with a fragmented database (may
insert/update/delete without vacuum) and cold page it was a huge
difference.

Using posix_fadvise() is asynchronous without any separate thread and
opening the database isn't slowed down - you don't need to wait
until the file is loaded.

I'm interested in speeding up SQLite when opening a database that is not
in the OS's page cache. Next thing I want to try is to preload only
selected tables/indexes with help of posix_fadvise(). I want to call
posix_fadvice for every child page when traveling through the btree just
before moving down to the first child of the current page.  I hope
this will be faster that a regular table/index read because the OS/disk
will have the IO queue full and can reorder read requestes if necessary.
It will be slower than preloading the whole DB, but I don't want to
waste disk bandwidth for that.

-- 
Damian Pietras

http://www.linuxprogrammingblog.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [PATCH] cache preloading

[Jim Wilcoxson]

On 8/10/10, Paweł Hajdan, Jr.  wrote:
> So this is another chromium patch I'd like to submit:
> http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/preload-cache.patch?revision=26596=markup
>
> I'm not the author of that one, but the main idea seems to be that with
> preloading we get better performance with many scenarios chromium uses
> sqlite (like for omnibox local suggestions). What do you think about the
> patch and the idea? Is there better way to do the same thing? Would you like
> to see some benchmarks?

The benefit of preloading is to replace random I/O with sequential
I/O.  I have definitely seen this be effective in some cases.  For
those who want something like this, doing a cat or dd command, or
using OS reads in a loop, will probably give the same benefit: the
database won't be loaded into SQLite's cache, but will be loaded into
the OS buffer cache.

An advantage of preloading outside SQLite is that the dd/cat/read
could happen in a separate thread, so it wouldn't impact app startup
time.  And it could be that one use of a database might benefit from
preloading, whereas another use might not, so it seems that preloading
inside SQLite would have to be something requested with a pragma
rather than always happening by default.

What I would like to see more than SQLite preloading is better page
allocation strategies and incremental defragmentation, to cut down on
the amount of random I/O and keep related pages in sequential order.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [PATCH] cache preloading

[Paweł Hajdan , Jr .]

So this is another chromium patch I'd like to submit:
http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/preload-cache.patch?revision=26596=markup

I'm not the author of that one, but the main idea seems to be that with
preloading we get better performance with many scenarios chromium uses
sqlite (like for omnibox local suggestions). What do you think about the
patch and the idea? Is there better way to do the same thing? Would you like
to see some benchmarks?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users