Re: [sqlite] Windows I/O (was: Initial read speed greater than subsequent)

[Udi Karni]

Experimenting with Data Warehouse - which should really be run on a more
"mainstream" DB. Sqlite was supposed to be just for piloting and testing -
but it's such an incredible little database engine - it's hard to let it go
- so I try big things on it just for kicks - delaying the inevitable.

It very easily handles billion row/100GB tables - multi-table joins, etc. -
it just chugs for a while because it's single threaded, and will gobble up
memory and swap - but it gets the job done.

It's quite amazing for a DB engine that's not even 1 MB.

While it's happiest when it can do all the work in memory versus disk - it
reads fairly quickly from disk the first time around - but not subsequently
- which is why I posed the question.

For now - my workaround is to attach and detach for every SQL statement -
but a better solution is probably to get a server with big RAM and tune
some of those OS settings - and ultimately, maybe MySQL?

On Fri, Jul 13, 2012 at 9:16 PM, Keith Medcalf  wrote:

>
> I know the newer versions of Windows are fantastically bloated (and slower
> every version), but what are you running that uses more than 16 GB of
> committed memory?
>
> > Thanks. More RAM would clearly be helpful - but first I need a bigger
> > machine that can take it. For some reason - the "home" line of PC is
> > typically capped at 16GB or so. I'll Need more of a workstation to go
> > higher and experiment with the settings you suggested.
> >
> > On Fri, Jul 13, 2012 at 8:35 PM, Keith Medcalf 
> wrote:
> >
> > >
> > > Windows is really atrociously bad at I/O.  Windows has the same basic
> > > model of how to perform I/O as a 6 year-old.  Scratch that, the six
> year
> > > old could probably understand I/O better than whoever wrote/designed
> the
> > > crap in Windows that passes for I/O routines.
> > >
> > > Anyway, make sure that you have all the fanciful magical features
> turned
> > > OFF (they make things slower, not faster).  That is all the various
> > > "SpeedBooster" crap and so forth that Microsoft crappifies their OS
> with to
> > > give that "gee wiz" wonderful warm and fuzzy feeling to the mass
> consumer
> > > market.
> > >
> > > Second, make sure you have turned off "Large System Cache".  Force
> Windows
> > > to forgo the magic, forgo the dreadful cache design, and do I/O
> properly.
> > >
> > > Disable "Magical Virtual Machine Resizing"-- set a fixed pagefile size
> --
> > > or better yet get sufficient RAM and disable swapping altogether -- it
> is
> > > pure bull droppings that you need a pagefile that is a percentage of
> RAM
> > > size.  If it works with 4GB of RAM and a 4GB swapfile, then it will
> work
> > > better with 8 GB of RAM and no pagefile.
> > >
> > > Then increase the IOPageLockLimit to something reasonable.
> > >
> > > And if your DASD driver supports it, enable block-level I/O
> optimization
> > > and/or caching.
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
>
>
> ___
> 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] Initial read speed greater than subsequent

[Udi Karni]

Thanks.

Here is something that seems to work - I open a Sqlite "scratchpad" DB into
which I select subsets from master tables - which are the problem because
they are large.

Initially I put each master table in its own DB and attached them all in
the beginning of the script. The first read was fast and the subsequent
ones slow.

I now attach the master DBs I need for a given SELECT right before the
SELECT and then detach them. I repeat this on every SELECT statement where
I refer to the large external tables.

Definitely not pretty - but it works !  The master tables are now read fast
every time. Somehow this is faking Windows to think it's the "first time"
every time. Sigh...
So this is bad -
==
attach TABLE1...
SELECT TABLE1  (fast)
SELECT TABLE1  (slow)
SELECT TABLE1  (slow)
detach TABLE1

And this is good -

attach TABLE1...
SELECT TABLE1 (fast)
detach TABLE1
attach TABLE1...
SELECT TABLE1 (fast)
detach TABLE1
attach TABLE1...
SELECT TABLE1 (fast)
detach TABLE1

Whatever works...


On Fri, Jul 13, 2012 at 7:11 PM, Simon Slavin  wrote:

>
> On 14 Jul 2012, at 3:07am, Udi Karni  wrote:
>
> > Thanks. I understand. I tried to set PRAGMA CACHE_SIZE=0; in the hope of
> > not cache anything and forcing Sqlite to always go to disk - but that
> > didn't help.
> >
> > I see some reads on pagefile.sys - but both the DB and the pagefile are
> on
> > SSD - so you would think it shouldn't be too costly to read one or both -
> > even in a more random fashion.
> >
> > The SSD disk read queue is just over 1 - so it doesn't quite fit the
> > typical scenario of IO bottlenecks - a high read queue and a disk arm
> > moving frantically.
>
> I don't think you can alter this inside SQLite.  The caching you have
> noticed is not being done by SQLite.  It's either a part of Windows'
> read-ahead caching, or something being done by your storage driver.
>
> Windows gets a lot of its speed by doing things like read-head caching.
>  It can lead to strange results like the ones you reported.
>
> Simon.
> ___
> 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] Windows I/O (was: Initial read speed greater than subsequent)

[Keith Medcalf]

I know the newer versions of Windows are fantastically bloated (and slower 
every version), but what are you running that uses more than 16 GB of committed 
memory?

> Thanks. More RAM would clearly be helpful - but first I need a bigger
> machine that can take it. For some reason - the "home" line of PC is
> typically capped at 16GB or so. I'll Need more of a workstation to go
> higher and experiment with the settings you suggested.
> 
> On Fri, Jul 13, 2012 at 8:35 PM, Keith Medcalf  wrote:
> 
> >
> > Windows is really atrociously bad at I/O.  Windows has the same basic
> > model of how to perform I/O as a 6 year-old.  Scratch that, the six year
> > old could probably understand I/O better than whoever wrote/designed the
> > crap in Windows that passes for I/O routines.
> >
> > Anyway, make sure that you have all the fanciful magical features turned
> > OFF (they make things slower, not faster).  That is all the various
> > "SpeedBooster" crap and so forth that Microsoft crappifies their OS with to
> > give that "gee wiz" wonderful warm and fuzzy feeling to the mass consumer
> > market.
> >
> > Second, make sure you have turned off "Large System Cache".  Force Windows
> > to forgo the magic, forgo the dreadful cache design, and do I/O properly.
> >
> > Disable "Magical Virtual Machine Resizing"-- set a fixed pagefile size --
> > or better yet get sufficient RAM and disable swapping altogether -- it is
> > pure bull droppings that you need a pagefile that is a percentage of RAM
> > size.  If it works with 4GB of RAM and a 4GB swapfile, then it will work
> > better with 8 GB of RAM and no pagefile.
> >
> > Then increase the IOPageLockLimit to something reasonable.
> >
> > And if your DASD driver supports it, enable block-level I/O optimization
> > and/or caching.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] Windows I/O (was: Initial read speed greater than subsequent)

[Udi Karni]

Thanks. More RAM would clearly be helpful - but first I need a bigger
machine that can take it. For some reason - the "home" line of PC is
typically capped at 16GB or so. I'll Need more of a workstation to go
higher and experiment with the settings you suggested.

On Fri, Jul 13, 2012 at 8:35 PM, Keith Medcalf  wrote:

>
> Windows is really atrociously bad at I/O.  Windows has the same basic
> model of how to perform I/O as a 6 year-old.  Scratch that, the six year
> old could probably understand I/O better than whoever wrote/designed the
> crap in Windows that passes for I/O routines.
>
> Anyway, make sure that you have all the fanciful magical features turned
> OFF (they make things slower, not faster).  That is all the various
> "SpeedBooster" crap and so forth that Microsoft crappifies their OS with to
> give that "gee wiz" wonderful warm and fuzzy feeling to the mass consumer
> market.
>
> Second, make sure you have turned off "Large System Cache".  Force Windows
> to forgo the magic, forgo the dreadful cache design, and do I/O properly.
>
> Disable "Magical Virtual Machine Resizing"-- set a fixed pagefile size --
> or better yet get sufficient RAM and disable swapping altogether -- it is
> pure bull droppings that you need a pagefile that is a percentage of RAM
> size.  If it works with 4GB of RAM and a 4GB swapfile, then it will work
> better with 8 GB of RAM and no pagefile.
>
> Then increase the IOPageLockLimit to something reasonable.
>
> And if your DASD driver supports it, enable block-level I/O optimization
> and/or caching.
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
>
>
> ___
> 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

[sqlite] Windows I/O (was: Initial read speed greater than subsequent)

[Keith Medcalf]

Windows is really atrociously bad at I/O.  Windows has the same basic model of 
how to perform I/O as a 6 year-old.  Scratch that, the six year old could 
probably understand I/O better than whoever wrote/designed the crap in Windows 
that passes for I/O routines.

Anyway, make sure that you have all the fanciful magical features turned OFF 
(they make things slower, not faster).  That is all the various "SpeedBooster" 
crap and so forth that Microsoft crappifies their OS with to give that "gee 
wiz" wonderful warm and fuzzy feeling to the mass consumer market.

Second, make sure you have turned off "Large System Cache".  Force Windows to 
forgo the magic, forgo the dreadful cache design, and do I/O properly.

Disable "Magical Virtual Machine Resizing"-- set a fixed pagefile size -- or 
better yet get sufficient RAM and disable swapping altogether -- it is pure 
bull droppings that you need a pagefile that is a percentage of RAM size.  If 
it works with 4GB of RAM and a 4GB swapfile, then it will work better with 8 GB 
of RAM and no pagefile.

Then increase the IOPageLockLimit to something reasonable.

And if your DASD driver supports it, enable block-level I/O optimization and/or 
caching.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] Initial read speed greater than subsequent

[Simon Slavin]

On 14 Jul 2012, at 3:07am, Udi Karni  wrote:

> Thanks. I understand. I tried to set PRAGMA CACHE_SIZE=0; in the hope of
> not cache anything and forcing Sqlite to always go to disk - but that
> didn't help.
> 
> I see some reads on pagefile.sys - but both the DB and the pagefile are on
> SSD - so you would think it shouldn't be too costly to read one or both -
> even in a more random fashion.
> 
> The SSD disk read queue is just over 1 - so it doesn't quite fit the
> typical scenario of IO bottlenecks - a high read queue and a disk arm
> moving frantically.

I don't think you can alter this inside SQLite.  The caching you have noticed 
is not being done by SQLite.  It's either a part of Windows' read-ahead 
caching, or something being done by your storage driver.

Windows gets a lot of its speed by doing things like read-head caching.  It can 
lead to strange results like the ones you reported.

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

Re: [sqlite] Initial read speed greater than subsequent

[Udi Karni]

Thanks. I understand. I tried to set PRAGMA CACHE_SIZE=0; in the hope of
not cache anything and forcing Sqlite to always go to disk - but that
didn't help.

I see some reads on pagefile.sys - but both the DB and the pagefile are on
SSD - so you would think it shouldn't be too costly to read one or both -
even in a more random fashion.

The SSD disk read queue is just over 1 - so it doesn't quite fit the
typical scenario of IO bottlenecks - a high read queue and a disk arm
moving frantically.

But who knows. More research I suppose.


On Fri, Jul 13, 2012 at 5:58 PM, Ryan Johnson
wrote:

> On 13/07/2012 5:37 PM, Udi Karni wrote:
>
>> Hello,
>>
>> Running on Windows 7 - I am noticing that tables in :memory: DBs are read
>> (SELECTED) at a constant rate. However - conventional DBs on disk - even
>> on
>> SSD - are read fast the first time, and much slower subsequently. Closing
>> and reopening a DB for every SQL statement seems to cure this - but
>> obviously is not a solution when you want to run a multi-step SQL script
>> on
>> a database.
>>
>> Is this a Windows "feature" - caching or otherwise? Is it Sqlite? Looking
>> at perfmon - the initial read "chunk" is > 100K while subsequently it's 4K
>> (the page size). Is there some prefetching taking place the first time
>> around? How do you make it permanent? How do you make Sqlite consistently
>> table-scan from disk?
>>
> This is a common problem with database buffer caches unless great pains
> are taken to avoid it (as in, not a "lite" product).
>
> What happens is that the first time through the data, it's read
> sequentially... but not all of it fits in the page cache. What gets evicted
> is *not* sequential, so on the second time around the disk requests are
> randomly scattered and  take about 100x longer to complete.
>
> Ryan
>
> __**_
> 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] Initial read speed greater than subsequent

[Ryan Johnson]

On 13/07/2012 5:37 PM, Udi Karni wrote:

Hello,

Running on Windows 7 - I am noticing that tables in :memory: DBs are read
(SELECTED) at a constant rate. However - conventional DBs on disk - even on
SSD - are read fast the first time, and much slower subsequently. Closing
and reopening a DB for every SQL statement seems to cure this - but
obviously is not a solution when you want to run a multi-step SQL script on
a database.

Is this a Windows "feature" - caching or otherwise? Is it Sqlite? Looking
at perfmon - the initial read "chunk" is > 100K while subsequently it's 4K
(the page size). Is there some prefetching taking place the first time
around? How do you make it permanent? How do you make Sqlite consistently
table-scan from disk?
This is a common problem with database buffer caches unless great pains 
are taken to avoid it (as in, not a "lite" product).


What happens is that the first time through the data, it's read 
sequentially... but not all of it fits in the page cache. What gets 
evicted is *not* sequential, so on the second time around the disk 
requests are randomly scattered and  take about 100x longer to complete.


Ryan

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

[sqlite] Initial read speed greater than subsequent

[Udi Karni]

Hello,

Running on Windows 7 - I am noticing that tables in :memory: DBs are read
(SELECTED) at a constant rate. However - conventional DBs on disk - even on
SSD - are read fast the first time, and much slower subsequently. Closing
and reopening a DB for every SQL statement seems to cure this - but
obviously is not a solution when you want to run a multi-step SQL script on
a database.

Is this a Windows "feature" - caching or otherwise? Is it Sqlite? Looking
at perfmon - the initial read "chunk" is > 100K while subsequently it's 4K
(the page size). Is there some prefetching taking place the first time
around? How do you make it permanent? How do you make Sqlite consistently
table-scan from disk?

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

[sqlite] leap seconds

[Doug Currie]

The SQLite3 date & time functions are designed assuming

> […] that every day is exactly 86400 seconds in duration.

Before I start implementing TAI (or GPS time) to/from UTC translator plugin, 
has anyone already done this?

Why? In a device that logs data with sub-second resolution, in my case a 
medical device, timestamps need to account for leap seconds, and support 
translation to/from UTC (and local time) for human readability.

-- e

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

Re: [sqlite] Native API for C# (C sharp) and Visual Studio

[Simon Slavin]

On 13 Jul 2012, at 3:11pm, joe.fis...@tanguaylab.com 
 wrote:

> We need to move a MS Access database to something more portable. Is the 
> “http://system.data.sqlite.org” the way to go for getting SQLite API access 
> from the Microsoft stuff (Visual Studio, other)?
> The programmer needs to use C# (c sharpe) for his coding that interacts with 
> the database. Is it a native API or wrapper?

It's a wrapper.  The native API is C code.  It turns out that C# is not a 
superset of C, so if you need access from that you'll have to use a wrapper or 
shim of some kind.

The page you cite is for a commonly-used ADO.NET adapter which should meet your 
needs, though you should allow the programmer herself or himself to make their 
own selection.

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

[sqlite] Native API for C# (C sharp) and Visual Studio

[joe.fis...@tanguaylab.com]

We need to move a MS Access database to something more portable. Is the 
“http://system.data.sqlite.org” the way to go for getting SQLite API 
access from the Microsoft stuff (Visual Studio, other)?
The programmer needs to use C# (c sharpe) for his coding that interacts 
with the database. Is it a native API or wrapper?


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

Re: [sqlite] Correction needed in shell.c of 3071300

[Richard Hipp]

On Thu, Jul 12, 2012 at 10:25 AM, Roy Soltoff  wrote:

> Folks, in the latest linux amalgamation download, I needed to add braces
> surrounding some of the code in the find_home_dir function to be able to
> ccompile without an error. The added braces are identified by the arrows.
> The struct declaration cannot occur after a statement; thus the need for
> the
> braces.
>
>
Joe has already fixed this here:  http://www.sqlite.org/src/info/d3d491a546


>
>
> static char *find_home_dir(void){
>
>   static char *home_dir = NULL;
>
>   if( home_dir ) return home_dir;
>
>
>
> #if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__) &&
> !defined(_WIN32_WCE) && !defined(__RTP__) && !defined(_WRS_KERNEL)
>
>   {ç
>
> struct passwd *pwent;
>
> uid_t uid = getuid();
>
> if( (pwent=getpwuid(uid)) != NULL) {
>
>   home_dir = pwent->pw_dir;
>
> }
>
>   }ç
>
> #endif
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS: Phrase queries

[Fabian]

Ever since I started using FTS extensively, I frequently ran into this
limitation:

  ** TODO: Strangely, it is not possible to associate a column specifier
  ** with a quoted phrase, only with a single token. Not sure if this was
  ** an implementation artifact or an intentional decision when fts3 was
  ** first implemented. Whichever it was, this module duplicates the
  ** limitation.

Is it ever planned to be fixed, because it doesn't seem to break any
backwards compatibilty?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Correction needed in shell.c of 3071300

[Roy Soltoff]

Folks, in the latest linux amalgamation download, I needed to add braces
surrounding some of the code in the find_home_dir function to be able to
ccompile without an error. The added braces are identified by the arrows.
The struct declaration cannot occur after a statement; thus the need for the
braces.

 

static char *find_home_dir(void){

  static char *home_dir = NULL;

  if( home_dir ) return home_dir;

 

#if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__) &&
!defined(_WIN32_WCE) && !defined(__RTP__) && !defined(_WRS_KERNEL)

  {ç

struct passwd *pwent;

uid_t uid = getuid();

if( (pwent=getpwuid(uid)) != NULL) {

  home_dir = pwent->pw_dir;

}

  }ç

#endif

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

Re: [sqlite] Improving access speed to fetch indexed column.

[Alexey Pechnikov]

The patch doesn't attached, please apply link to the patch.

2012/7/12 Filip Navara 

> This roughly resembles an issue I witnessed on our databases about
> year ago (thread "Improving the query optimizer" on this mailing
> list). SQLite doesn't use covering index for fulfilling queries unless
> the query is filtered/ordered by a column included in the index. In
> many cases the covering index is actually smaller than the full table
> and thus it's less data to read.
>
> We use a modified version of SQLite with the attached patch to
> alleviate the issue.
>
> Best regards,
> Filip Navara
>
> On Thu, Jul 12, 2012 at 12:59 PM, Richard Hipp  wrote:
> > On Wed, Jul 11, 2012 at 7:49 PM, Kohji Nakamura  >wrote:
> >
> >> Hello all,
> >>
> >> I found that the access to an indexed column without "order by" is
> slower
> >> than the one with "order by" in SQLite 3071300.
> >> Using an index rather than an actual column is faster even if there is
> no
> >> need to use the index when the column has index.
> >> In general, to fetch column value, there is no need to access actual
> >> column when it has a dedicated index or it is a first column of
> composite
> >> index.
> >> I hope SQLite would do this optimization which is common to other DBMSs.
> >>
> >> Followings are the results of the comparison. Time column of main table
> >> has an index.
> >>
> >> After disk cache is cleared,
> >> SQL: select time from main order by time;
> >> Total : 38.1312 sec
> >>
> >> SQL: select time from main;
> >> Total : 95.395 sec
> >>
> >
> > Can you please send us the output of EXPLAIN QUERY PLAN for these two
> > queries on your schema?
> >
> >
> >>
> >> When data is cached,
> >> SQL: select time from main order by time;
> >> Total : 0.497981 sec
> >>
> >> SQL: select time from main;
> >> Total:: 0.925122 sec
> >>
> >> Thank you for developing a very cool DBMS, SQLite!
> >> Kohji Nakamura
> >> --
> >> k.nakam...@nao.ac.jphttp://www.nao.ac.jp/E/index.html
> >> National Astronomical Observatory of Japan
> >>
> >>
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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
>
>


-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users