[sqlite] 64-bit Windows Command Shell
Udi Karni
Is there - or can we kindly request that a 64-bit version of the Command
Shell be offered on the download page?
A version that could run on Windows 7 64-bit and effectively use large RAM?
Thanks !
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Roger Binns
Hash: SHA1
On 21/03/12 05:28, Udi Karni wrote:
> Is there - or can we kindly request that a 64-bit version of the
> Command Shell be offered on the download page?
>
> A version that could run on Windows 7 64-bit and effectively use large
> RAM?
Which bit of the shell operation do you believe is limited by RAM and how
did you work that out?
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
iEYEARECAAYFAk9qFpoACgkQmOOfHg372QRL7gCgmWpOxSgcuh1kvjwepNRJSGuy
+JoAoIWoBPGk4aMzxor8fbNCLNsMEQm5
=QSVU
-----END PGP SIGNATURE-----
Black, Michael (IS)
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: sqlite-use...@sqlite.org [sqlite-use...@sqlite.org] on behalf of Roger Binns [rog...@rogerbinns.com]
Sent: Wednesday, March 21, 2012 12:57 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] 64-bit Windows Command Shell
Roger Binns
Hash: SHA1
On 21/03/12 11:09, Black, Michael (IS) wrote:
> Cache is the primary (and obvious) thing I can think of.
With a 32 bit compilation you'll be able to bump it up to about 2GB.
However by that point you will long have passed diminishing returns and
can just let the OS do its own caching.
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
iEYEARECAAYFAk9qK2IACgkQmOOfHg372QQVdwCfbJTAzhCPR4ARPxhYHewLvvcT
4lYAoI4QFXFfxILtsQGxVWm8BRM/mbIX
=e0aW
Roger Binns
Hash: SHA1
On 21/03/12 16:57, Udi Karni wrote:
> Frankly I don't know if a 64-bit version and Big RAM would make a
> difference and if so - up to what point.
A 64 bit Windows command shell won't wave a magic wand. In any event you
can compile one yourself and do your own measurements on your own data.
However you are wasting your time.
> With SQLite being a single process - assigned for the most part to a
> single CPU - even if everything was done in RAM - there is a limit to
> what 1 CPU can do.
The operating system can use other CPUs doing I/O scheduling, read ahead etc.
> I am just noticing anecdotally that SQlite uses cache and dealing with
> tables of a few hundred MB or less doesn't seem to generate IO.
The default cache size in SQLite is 2MB. If you are getting no I/O then
it means your working set fits in that much space.
> Also - when there is IO - it often comes from the swap file (under
> Windows 7).
You'll need to analyze what is happening. For example it could be other
programs being paged out due to memory pressure.
> So the questions are -
They all depend on your workload. There is no magic bullet you are
missing that will suddenly make things ten times faster.
> (1) how much RAM is the point of diminishing returns on 32-bit
Depends on what you are doing. In my comment specifically it was about
the SQLite cache. When something is not in SQLite's cache it will ask the
operating system which is almost instantaneous (if in the cache).
Consequently you can consider the two complementary. Making SQLite's
larger reduces the amount available for the operating system. You aren't
going to see an appreciable difference in data coming from SQLite's cache
versus the operating system cache. There will of course be a huge
difference when data is fetched from an I/O device.
> (2) is there value to going 64-bit
Unless you are running out of address space, no. (In theory there are
more registers and CPU bound activity could be faster, but SQLite isn't
doing that much anyway.) You also incur a penalty of memory pointers
doubling in size.
Note that you can still run the 32 bit SQLite process on a 64 bit
operating system with oodles of RAM. The OS can use all of that RAM for
caching. However caches are only useful when you have working sets - ie
data that is accessed more than once.
> (3) if there was a 64-bit version - would it use more RAM more
> effectively?
Nope. You just get to be able to have more address space in a process.
> (4) as a fallback - let's say the 32-bit version and 4GB are as good as
> you can pretty much expect. Would getting a server with 4 CPUs and 16GB
> (a high-end home-version PC) - reasonably enable me to run 3-4 SQLite
> jobs concurrently? In other words - no great speed improvement per job
> - but in aggregate more work could get done?
Depends what you are limited by. If the I/O systems for each database are
completely independent (ie separate buses) and memory bandwidth is not
saturated then you'll get an appropriate speedup/concurrency.
However again you seem to be seeking out some magic bullet. There isn't
one. You'll only get good answers by running your own workloads.
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
iEYEARECAAYFAk9qfiwACgkQmOOfHg372QQNYACgqCwN6+ME23LNZcntUMI3hMXM
G3gAoKvc2+a4N66M6p0vqYxCbDM72VmF
=xVxc
Don V Nielsen
5) Could sqlite3 take advantage of multiple cpu's by parsing a single task
into one thread per cpu and segment data to be worked by each thread? Big
league stuff. But I don't think sqlite3 is meant to compete in that
market. It already exceeds expectations in its current market.
dvn
On Wed, Mar 21, 2012 at 6:57 PM, Udi Karni <uka...@gmail.com> wrote:
> Frankly I don't know if a 64-bit version and Big RAM would make a
> difference and if so - up to what point. With SQLite being a single process
> - assigned for the most part to a single CPU - even if everything was done
> in RAM - there is a limit to what 1 CPU can do.
>
> I am just noticing anecdotally that SQlite uses cache and dealing with
> tables of a few hundred MB or less doesn't seem to generate IO. Also - when
> there is IO - it often comes from the swap file (under Windows 7).
>
> So the questions are -
>
> (1) how much RAM is the point of diminishing returns on 32-bit
> (2) is there value to going 64-bit
> (3) if there was a 64-bit version - would it use more RAM more effectively?
> (4) as a fallback - let's say the 32-bit version and 4GB are as good as you
> can pretty much expect. Would getting a server with 4 CPUs and 16GB (a
> high-end home-version PC) - reasonably enable me to run 3-4 SQLite jobs
> concurrently? In other words - no great speed improvement per job - but in
> aggregate more work could get done?
>
> Thanks !
Udi Karni
I got the "out of memory" message.
In my particular scenario - while the raw data being attached and read is
hundreds of GB - the result sets are only a few GB.
A 64-bit version of SQLite that could handle an in-memory DB of 5-10 GB
would be quite useful and interesting to test. Unfortunately I am not
versed enough in compiling my own version - so hopefully this would be
offered one of these days.
Whether a RAM-only version would truly run subtantially faster than an
SSD-based DB - remains to be seen - but the <2GB experiments seem to show
that in-memory is quite promising.
Roger Binns
Hash: SHA1
On 24/03/12 13:48, Udi Karni wrote:
> I tried the SQLite in-memory DB feature - but when it exceeded about
> 2GB - I got the "out of memory" message.
Technically it ran out of address space, not ram.
> In my particular scenario - while the raw data being attached and read
> is hundreds of GB - the result sets are only a few GB.
2GB is the threshold to switch to 64 bits. Also note that SQLite does not
calculate the entire result set in advance. Instead when you call
sqlite3_step() it does whatever work is necessary to find the next result
row. It is only if you do something like require sorting that cannot be
satisfied by an index that the whole result set has to be calculated and
then sorted.
> A 64-bit version of SQLite that could handle an in-memory DB of 5-10
> GB would be quite useful and interesting to test.
You can also use a ramdisk for the files with journalling turned off.
Microsoft used to have one, but now a search shows a number of companies.
I have no idea which ones are the most reputable. Under Linux you can use
tmpfs which makes things very easy.
> Unfortunately I am not versed enough in compiling my own version - so
> hopefully this would be offered one of these days.
Unfortunately making the free Microsoft dev tools generate 64 bit binaries
is a PITA. Another alternative is to use a 64 bit version Linux in which
case all this stuff just works.
> Whether a RAM-only version would truly run subtantially faster than an
> SSD-based DB - remains to be seen - but the <2GB experiments seem to
> show that in-memory is quite promising.
Any memory used to store databases is memory that cannot be used for I/O
caching.
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
iEYEARECAAYFAk9uaHgACgkQmOOfHg372QQhXQCg0c7MYT5s9WBSGcwyYWra7ZNx
3qsAmwRSECp48Z2KkhfkMGvyvR7X1LsJ
=M4tq
Udi Karni
(1) On the SQLite Download page - the Linux version just says "x86" while
the Windows version is "Win-32 x86". Does this mean that the Linux version
is a 64-bit version? In other words - if run on, say, RedHat 5.5 64-bit -
it will be able to use >4GB of RAM?
(2) On Windows - a RamDisk "drive letter" can be used beneficially in 2
ways -
(a) to contain the database itself
(b) to serve as a destination for the TEMP and TMP - which SQLite (through
Windows I suppose) uses often as a scratchpad.
Is it possible - good/bad idea - to point the Windows TMP and TEMP to a
"virtual" drive letter on top of a RamDisk? The RamDisk gets established as
Windows comes up - but the question is whether Windows might need the TMP /
TEMP prior to that point in time - when they are not yet going to be
available - causing startup problems?
Jay A. Kreibich
> > In my particular scenario - while the raw data being attached and read
> > is hundreds of GB - the result sets are only a few GB.
>
> 2GB is the threshold to switch to 64 bits.
That depends on the OS. Some 64-bit OSes will give nearly all the
4GB address space to a 32-bit application. Others, like Windows,
will only give half the space, and reserve the other half of the
address space for system mappings.
> > Whether a RAM-only version would truly run subtantially faster than an
> > SSD-based DB - remains to be seen - but the <2GB experiments seem to
> > show that in-memory is quite promising.
>
> Any memory used to store databases is memory that cannot be used for I/O
> caching.
True, but in-memory databases do not require I/O. Additionally, I/O
caching is done by the OS. Even with a 32-bit SQLite process, if the
system has more than 2GB of RAM, a 64-bit OS is going to be able to
use any additional RAM (including that beyond 4GB) for I/O caching.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
Simon Slavin
On 25 Mar 2012, at 2:16am, Udi Karni <uka...@gmail.com> wrote:
> (2) On Windows - a RamDisk "drive letter" can be used beneficially in 2
> ways -
> (a) to contain the database itself
> (b) to serve as a destination for the TEMP and TMP - which SQLite (through
> Windows I suppose) uses often as a scratchpad.
> Is it possible - good/bad idea - to point the Windows TMP and TEMP to a
> "virtual" drive letter on top of a RamDisk? The RamDisk gets established as
> Windows comes up - but the question is whether Windows might need the TMP /
> TEMP prior to that point in time - when they are not yet going to be
> available - causing startup problems?
There is a separate mechanism for creating in-memory databases: specify ':memory:' as the file name. So SQLite lets the programmer choose which one they want. By moving TMP/TEMP to memory too all you're doing is reducing your options.
Simon.
Roger Binns
Hash: SHA1
On 24/03/12 18:16, Udi Karni wrote:
> (1) On the SQLite Download page - the Linux version just says "x86"
> while the Windows version is "Win-32 x86". Does this mean that the
> Linux version is a 64-bit version? In other words - if run on, say,
> RedHat 5.5 64-bit - it will be able to use >4GB of RAM?
When you install Linux you will generally find SQLite already installed or
one simple command away. The approach is "batteries included" so
everything is already there, rather than having to go all over the
Internet to fetch stuff.
> (a) to contain the database itself
Your databases should be many times the size of scratchpads so that is
where you will get the best effect.
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
iEYEARECAAYFAk9ueckACgkQmOOfHg372QS9nwCcDRZdLxActvFKUnhh40xyByxW
uGsAoMdUBYTGG4sDaghF0NsnE8/vZf5f
=p17y
Roger Binns
Hash: SHA1
On 24/03/12 18:23, Jay A. Kreibich wrote:
> That depends on the OS. Some 64-bit OSes will give nearly all the 4GB
> address space to a 32-bit application.
Those same OSes make it very easy to compile SQLite in 64 bits so this
isn't an issue.
> Others, like Windows, will only give half the space, and reserve the
> other half of the address space for system mappings.
See also /3GB
> True, but in-memory databases do not require I/O. Additionally, I/O
> caching is done by the OS. Even with a 32-bit SQLite process, if the
> system has more than 2GB of RAM, a 64-bit OS is going to be able to use
> any additional RAM (including that beyond 4GB) for I/O caching.
The scenario was using a ramdisk. If the system has 16GB of memory and
15GB of memory is used for a ramdisk then only 1GB is available for I/O
caching. A 32 bit SQLite process can still happily make a 15GB database
on a ramdisk, but not in :memory:.
The point is to be careful using memory for single purposes (files on a
ramdisk) since it can't be used for other things at the same time. Under
normal circumstances the OS can use memory for whatever is most needed at
a point in time be it caching or for process heap and code.
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
iEYEARECAAYFAk9ue0AACgkQmOOfHg372QTYOQCgwq8U2/NmEMqrbt5OlQdwTXVX
JDgAn0zFcIxaEa8r43Cbi6W5U+NwdcL8
=1tE1
-----END PGP SIGNATURE-----
Jay A. Kreibich
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 24/03/12 18:23, Jay A. Kreibich wrote:
> > That depends on the OS. Some 64-bit OSes will give nearly all the 4GB
> > address space to a 32-bit application.
>
> Those same OSes make it very easy to compile SQLite in 64 bits so this
> isn't an issue.
Not to nit-pick, but an OS doesn't make it, or not make it, difficult
to compile something. That's usually due to crappy tools and poor
support.
Then again, the last time I compiled SQLite on Windows, I was using a
non-Visual Studio, command line compiler.
> > Others, like Windows, will only give half the space, and reserve the
> > other half of the address space for system mappings.
>
> See also /3GB
Under Windows, yes, but not all OSes that split application address
space allow the boundary to be moved. Perhaps, "Others, like Windows
defaults," would have been more specific.
Your point that most of these OSes also have solid tool support is
well taken, however.
> > True, but in-memory databases do not require I/O. Additionally, I/O
> > caching is done by the OS. Even with a 32-bit SQLite process, if the
> > system has more than 2GB of RAM, a 64-bit OS is going to be able to use
> > any additional RAM (including that beyond 4GB) for I/O caching.
>
> The scenario was using a ramdisk.
Yeah, I missed that "in memory database" wasn't a ":memory:" database.
> If the system has 16GB of memory and 15GB of memory is used for a
> ramdisk then only 1GB is available for I/O caching.
True, but the OS doesn't typically cache filesystems on a ramdisk.
Caching slows things down and wastes memory.
> The point is to be careful using memory for single purposes (files on a
> ramdisk) since it can't be used for other things at the same time. Under
> normal circumstances the OS can use memory for whatever is most needed at
> a point in time be it caching or for process heap and code.
True enough... although there is some assumption you know how to
measure and analyze resource allocations when you start to do stuff
like allocate ramdisks and such.
Or not. I suppose everyone knows someone like an old (and thankfully
"ex") colleague of mine that got all excited about the possibility of
moving his swap files to a RAM disk... "Sure it will swap a bit more,
but it will be *SOOO* fast!!"
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
Roger Binns
Hash: SHA1
On 24/03/12 20:14, Jay A. Kreibich wrote:
> Not to nit-pick, but an OS doesn't make it, or not make it, difficult
> to compile something. That's usually due to crappy tools and poor
> support.
Yes, but the tools primarily come from the OS vendor. After all they have
to be able to compile and debug the code making up the operating system
and base applications.
> Then again, the last time I compiled SQLite on Windows, I was using a
> non-Visual Studio, command line compiler.
Compiling 32 bit isn't a big deal and numerous compilers do so. MinGW is
the most popular non-commercial compiler and is fine for 32 bit although
there are some issues I and others ignore to do with CRT support. It has
also tended to have stable releases in the distant past and numerous
unstable updates. It wasn't that long ago that GCC 4 become stable!
However to my knowledge there is currently no stable 64 bit Windows support.
Microsoft has various free "Express" products as well as varying priced
forms of Visual Studio. One of the differentiators is that the free
products only produce 32 bit binaries.
Separately they do have things like a Windows 7 SDK which also happens to
include compiler support. You can whack together the Express C/C++ and
SDK and actually produce 64 bit binaries. It is what I do and is also
command line only. But it is not a pleasant experience.
> True, but the OS doesn't typically cache filesystems on a ramdisk.
Huh? There was no claim of ramdisks being used by the operating system as
a filesystem cache. The claim was that memory used by a ramdisk is not
available for use as a cache.
> Caching slows things down and wastes memory.
Really? Caches as a rule do not slow anything down. For sequentially
accessed files they store read ahead data which improves performance and
for randomly accessed files they store previously read data in case it
gets accessed again. Both of these things improve performance(*).
Modern operating systems dynamically alter system memory allocation based
on workload so that memory is used in whatever way is most effective. I'd
hardly call that a waste!
(*) Nit pickers corner: If your random access patterns exceed the cache
size and you do not reread previously read data then the cache could end
up with few or no hits. In any event read data had to be put in some
memory somewhere so that isn't inherently a waste. The only waste would
be CPU managing a cache that gets no hits. But since CPU is so many
orders of magnitude faster than I/O it doesn't really matter.
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
iEYEARECAAYFAk9upNsACgkQmOOfHg372QTPRgCbBzUBbH2nTI2izubKKAO+0/4U
A/oAn1We5oteTDhhF+wahFPRRxTpfI8S
=tTvm
-----END PGP SIGNATURE-----
Jay A. Kreibich
> Yes, but the tools primarily come from the OS vendor. After all they have
> to be able to compile and debug the code making up the operating system
> and base applications.
Which makes it all the more disappointing that so many of the vendor
tools of are such moderate quality-- even the pay-for versions.
> > True, but the OS doesn't typically cache filesystems on a ramdisk.
>
> Huh? There was no claim of ramdisks being used by the operating system as
> a filesystem cache.
No, there wasn't. I'm saying the OS shouldn't cache blocks from
filesystems that reside on a ramdisk volume. There is no need.
Device I/O is just as fast as the cache.
> The claim was that memory used by a ramdisk is not available for use
> as a cache.
Yes, but if you're database is on a ramdisk-- and that's your
primary performance concern for this system-- there is a greatly
reduced need for filesystem cache space. The OS shouldn't need to
cache blocks off the ramdisk, so the overall need for filesystem
cache space is reduced.
Things change if this is a general purpose system with many different
process profiles, but if you have a single need strong enough to
justify a ramdisk, you're already somewhat past that point.
> > Caching slows things down and wastes memory.
>
> Really? Caches as a rule do not slow anything down.
Having the OS cache pages off a ramdisk based filesystem adds a layer
of indirection and consumes more memory. The point of almost all
types of caches is to reduce the cost of an upstream process-- in
this case physical I/O. If the upstream process (ramdisk I/O) is
just as fast as the cache (filesystem pages in RAM), then the
cache servers no real purpose, yet still consumes resources. The
cache only slows things down and wastes memory.
Filesystem caches are a huge win for traditional media, but not for
ramdisks. That said, if a database on traditional media is not
completely read dominated, the bottleneck is still going to be
flushing pages all the way to non-volatile storage. If you want to
be transaction safe, you can't get around that, and OS based
filesystem caches can't speed up verified writes. Device-based
non-volatile caches, perhaps, but that's getting pretty exotic for
most SQLite deployments.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
Roger Binns
Hash: SHA1
On 25/03/12 08:44, Jay A. Kreibich wrote:
> No, there wasn't. I'm saying the OS shouldn't cache blocks from
> filesystems that reside on a ramdisk volume.
We were talking at cross purposes. I was talking about the OPs issue and
moving a database from slow spinning media to a ramdisk, and how the
memory consumed by the ramdisk is then not available for program usage or
caching of non-ramdisk media. You were talking about caching of ramdisks
themselves.
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
iEYEARECAAYFAk9viiUACgkQmOOfHg372QQQbgCfTQpzpH7ul9YOiSqmdHxmATs0
K7IAoL0khXD1VTqPAoRovB7biu/qlSd4
=V1cZ
-----END PGP SIGNATURE-----