Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Bob Friesenhahn]

On Thu, 18 May 2017, Kim Gräsman wrote:


The request is issued early on when the connection is first opened so no
actual queries have been issued at that time.


Then my (black-box) guess is that you're seeing the bump from
allocating heap space for whatever structures the schema needs.


Our schema takes a bit over 1MB to load on a 32-bit CPU.  The 
increased usage we are seeing is on the order of 200k so it is not the 
schema.  Regardless, the application is using the database immediately 
so it must always consume the schema.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Kim Gräsman]

On Thu, May 18, 2017 at 8:27 PM, Bob Friesenhahn
 wrote:
> On Thu, 18 May 2017, Kim Gräsman wrote:
>
>> On Thu, May 18, 2017 at 6:10 PM, Bob Friesenhahn
>>  wrote:
>>>
>>> Does anyone know why using 'PRAGMA cache_size=0' (or some other small
>>> value)
>>> to attempt to decrease memory usage (and it is reported as immediately
>>> decreased in the shell by .stats) actually significantly increases heap
>>> memory usage?
>>
>>
>> It sounds like you have active operation with a larger cache size
>> before issuing the PRAGMA, is that so?
>
>
> The request is issued early on when the connection is first opened so no
> actual queries have been issued at that time.

Then my (black-box) guess is that you're seeing the bump from
allocating heap space for whatever structures the schema needs.

> An earlier developer had tried the same thing almost 5 years ago (with a
> much older sqlite) and noticed a 200k jump in heap usage.

We're at 3.14.1, so I'm also speaking from an older timeframe.

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

Re: [sqlite] SQLite in memory

[Gabriele Lanaro]

Thanks everyone for all the tips! This is all very useful.

We are using SQLite’s FTS5 feature to search a large number of text files.
There are 50M records in total but they are split across 1000 smaller
databases of 50K records each. Each DB is 250MB in size.

I am trying to test query performance and for that I am issuing queries for
same term over and over on a random subset of the databases.

Each query will execute on its own thread (I am using Python’s CherryPy
server) and the connection to each DB will be kept alive (multiple requests
will certainly hit the same connection). I am using PRAGMA
cache_size=256MB, so it should cache the entire DB in memory.

If I only use a single database then I would expect that the query could be
served entirely from cached the copy of DB. Since we have 100 of those, I
could reasonably expect that there will be some reading from disk.

But for a smaller number of DBs, say 10, I would expect the performance
similar to in-memory.

The schema is roughly constituted of two tables, one is a full text index,
fts5_table, while the other is called datatable.

The query in question is a full text query on the full text index joined
with another table.

*SELECT* fts5.column, datatable.column2 *FROM* fts5_table, datatable *WHERE*
 fts5_table *MATCH* ‘term’ *AND* datatable.id = fts5_column.rowid *ORDER BY*
 rank *ASC* *LIMIT* 10;

The total number 'term' matches in the documents is about 3 documents
(given the orderby query, this means that all of those documents will need
to be ranked).

As I increase the number of threads I reach a peak performance at 4
threads, which is less than the number of available cores (after that, the
performance degrades). If I make my application use more threads (i.e. more
requests get processed in paralle) the performance linearly degrades.

All I am getting is ~40 requests per seconds (when we have 100 users making
queries in parallel). But I believe SQLite is capable of doing much more. I
am running on a Red Hat Linux on an Intel machine with 8-cores, 16-threads
and 64GB of system memory. Disks are SSD.

Thanks,

Gabriele


On Thu, May 18, 2017 at 7:51 AM, Eduardo Morras  wrote:

> On Wed, 17 May 2017 22:18:19 -0700
> Gabriele Lanaro  wrote:
>
> > Hi, I'm trying to assess if the performance of my application is
> > dependent on disk access from sqlite.
> >
> > To rule this out I wanted to make sure that the SQLite DB is
> > completely accessed from memory and there are no disk accesses.
> >
> > Is it possible to obtain this effect by using pragmas such as
> > cache_size?
> >
> > Another solution is to copy the existing db to a :memory: db but I'd
> > like to achieve the same effect without doing so (because it will
> > require substantial modification of the application). For the sake of
> > argument, let's image that using :memory: db is not an option.
> >
> > Also using a ramdisk is not an option because I don't have root
> > access to the machine.
>
> What OS are you using?
>
> You can next tips to make the app less dependant on disk I/O access:
>
> a) change where store temporal tables (mat views, subqueries, temp
> tables) and indices (transient, to use ram always (pragma
> temp_store=2),
>
> b) increase cache size, the more, the better (os disk cache is shared
> with other processes and is slower), if cache is equal or bigger than
> your db, it'll be fit in ram,
>
> c) set config SQLITE_CONFIG_STMTJRNL_SPILL=-1 if you use statement
> journal files (check http://www.sqlite.org/tempfiles.html#stmtjrnl),
>
> d) use wal mode to avoid *-shm files, and set wal_checkpointing,
>
> e) use mmap_size pragma to minimize I/O (check
> http://www.sqlite.org/mmap.html it has disadvanteges too)
>
>
> > Thanks,
> >
> > Gabriele
>
> HTH
>
> ---   ---
> Eduardo Morras 
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Bob Friesenhahn]

On Thu, 18 May 2017, Kim Gräsman wrote:


On Thu, May 18, 2017 at 6:10 PM, Bob Friesenhahn
 wrote:

Does anyone know why using 'PRAGMA cache_size=0' (or some other small value)
to attempt to decrease memory usage (and it is reported as immediately
decreased in the shell by .stats) actually significantly increases heap
memory usage?


It sounds like you have active operation with a larger cache size
before issuing the PRAGMA, is that so?


The request is issued early on when the connection is first opened so 
no actual queries have been issued at that time.


An earlier developer had tried the same thing almost 5 years ago (with 
a much older sqlite) and noticed a 200k jump in heap usage.



If not, 'PRAGMA cache_size' loads the schema under the hood, and
depending on how large/complex it is, this can make quite a footprint.


Our schema is quite large/complex.

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Kim Gräsman]

On Thu, May 18, 2017 at 6:10 PM, Bob Friesenhahn
 wrote:
> Does anyone know why using 'PRAGMA cache_size=0' (or some other small value)
> to attempt to decrease memory usage (and it is reported as immediately
> decreased in the shell by .stats) actually significantly increases heap
> memory usage?

It sounds like you have active operation with a larger cache size
before issuing the PRAGMA, is that so?

If not, 'PRAGMA cache_size' loads the schema under the hood, and
depending on how large/complex it is, this can make quite a footprint.

I noticed this first hand when I issued 'PRAGMA cache_size=128' with a
fixed heap on a large schema and immediately ran out of memory.

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

Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Bob Friesenhahn]

On Thu, 18 May 2017, Simon Slavin wrote:



On 18 May 2017, at 5:10pm, Bob Friesenhahn  wrote:


Does anyone know why using 'PRAGMA cache_size=0' (or some other small value) to 
attempt to decrease memory usage (and it is reported as immediately decreased 
in the shell by .stats) actually significantly increases heap memory usage?


Which OS ?


Linux with uclibc.


What are you using to report heap memory size/usage ?


A Python script named 'ps_mem.py' which is available from 
"https://github.com/pixelb/ps_mem;.  It tallies data from /proc so it 
is very accurate about actual usage.


It is not clear to me if setting the pragma jumbles up the heap a bit 
so more memory is consumed, or if the cache is more efficient than the 
alternative.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOT NULL integer primary key

[Paul Sanderson]

Ahh being dull and in a hurry
thanks


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 18 May 2017 at 17:26, Gwendal Roué  wrote:

>
> > Le 18 mai 2017 à 18:16, Paul Sanderson  a
> écrit :
> >
> > Is this a bug?
> >
> > Create table test (id integer not null primary key, data text);
> > insert into test values (null, 'row1');
> > select * from test;
> > 1, row1
> >
> > I know that if you provide a NULL value to a column define as integer
> > primary key that SQLite will provide a rowid, but should the not null
> > constraint be obeyed?
>
> Hello Paul,
>
> The constraint is obeyed, since there is no NULL values in the database.
>
> To put it in another way: constraints are properties of the *database
> content*, not of the *operations* on content. They're static, not dynamic.
>
> That's why constraints can be checked with PRAGMA
> schema.foreign_key_check, which tells if the current state of the database
> content is valid.
>
> That's also why the insert statement above succeeds, as long as the value
> that is eventually inserted in the database is NOT NULL.
>
> Gwendal Roué
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Simon Slavin]

On 18 May 2017, at 5:10pm, Bob Friesenhahn  wrote:

> Does anyone know why using 'PRAGMA cache_size=0' (or some other small value) 
> to attempt to decrease memory usage (and it is reported as immediately 
> decreased in the shell by .stats) actually significantly increases heap 
> memory usage?

Which OS ?
What are you using to report heap memory size/usage ?

> I find this to be an interesting phenomena.

Indeed.

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

Re: [sqlite] NOT NULL integer primary key

[Gwendal Roué]

> Le 18 mai 2017 à 18:16, Paul Sanderson  a écrit 
> :
> 
> Is this a bug?
> 
> Create table test (id integer not null primary key, data text);
> insert into test values (null, 'row1');
> select * from test;
> 1, row1
> 
> I know that if you provide a NULL value to a column define as integer
> primary key that SQLite will provide a rowid, but should the not null
> constraint be obeyed?

Hello Paul,

The constraint is obeyed, since there is no NULL values in the database.

To put it in another way: constraints are properties of the *database content*, 
not of the *operations* on content. They're static, not dynamic.

That's why constraints can be checked with PRAGMA schema.foreign_key_check, 
which tells if the current state of the database content is valid.

That's also why the insert statement above succeeds, as long as the value that 
is eventually inserted in the database is NOT NULL.

Gwendal Roué

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

[sqlite] NOT NULL integer primary key

[Paul Sanderson]

Is this a bug?

Create table test (id integer not null primary key, data text);
insert into test values (null, 'row1');
select * from test;
1, row1

I know that if you provide a NULL value to a column define as integer
primary key that SQLite will provide a rowid, but should the not null
constraint be obeyed?


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PRAGMA cache_size=0 increases memory usage

[Bob Friesenhahn]

Does anyone know why using 'PRAGMA cache_size=0' (or some other small 
value) to attempt to decrease memory usage (and it is reported as 
immediately decreased in the shell by .stats) actually significantly 
increases heap memory usage?


I find this to be an interesting phenomena.

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite in memory

[Eduardo Morras]

On Wed, 17 May 2017 22:18:19 -0700
Gabriele Lanaro  wrote:

> Hi, I'm trying to assess if the performance of my application is
> dependent on disk access from sqlite.
> 
> To rule this out I wanted to make sure that the SQLite DB is
> completely accessed from memory and there are no disk accesses.
> 
> Is it possible to obtain this effect by using pragmas such as
> cache_size?
> 
> Another solution is to copy the existing db to a :memory: db but I'd
> like to achieve the same effect without doing so (because it will
> require substantial modification of the application). For the sake of
> argument, let's image that using :memory: db is not an option.
> 
> Also using a ramdisk is not an option because I don't have root
> access to the machine.

What OS are you using?

You can next tips to make the app less dependant on disk I/O access:

a) change where store temporal tables (mat views, subqueries, temp
tables) and indices (transient, to use ram always (pragma
temp_store=2),
 
b) increase cache size, the more, the better (os disk cache is shared
with other processes and is slower), if cache is equal or bigger than
your db, it'll be fit in ram, 

c) set config SQLITE_CONFIG_STMTJRNL_SPILL=-1 if you use statement
journal files (check http://www.sqlite.org/tempfiles.html#stmtjrnl), 

d) use wal mode to avoid *-shm files, and set wal_checkpointing, 

e) use mmap_size pragma to minimize I/O (check
http://www.sqlite.org/mmap.html it has disadvanteges too)


> Thanks,
> 
> Gabriele

HTH

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

Re: [sqlite] SQLite in memory

[Keith Medcalf]

On Wednesday, 17 May, 2017 23:18, Gabriele Lanaro  
wrote:

> Hi, I'm trying to assess if the performance of my application is dependent
> on disk access from sqlite.

Of course it is.  Depending on what your application is doing.
 
> To rule this out I wanted to make sure that the SQLite DB is completely
> accessed from memory and there are no disk accesses.

You still haven't stated why you think disk access may be the bottleneck and 
what sort of operations that you are doing that you think are problematic.
 
> Is it possible to obtain this effect by using pragmas such as cache_size?

Yes and No.  It depends what kind of operations you are doing.  For example, 
assuming you have enough RAM then once pre-charged even a modern crappy 
filesystem cache will short-circuit I/O reads so that they do not hit the disk. 
 Nothing can be done for writes though (except that the old timer adage that 
the fastest way to do I/O is not to do it, applies in spades).
 
> Another solution is to copy the existing db to a :memory: db but I'd like
> to achieve the same effect without doing so (because it will require
> substantial modification of the application). For the sake of argument,
> let's image that using :memory: db is not an option.

Ok.
 
> Also using a ramdisk is not an option because I don't have root access to
> the machine.

That would imply that the machine has a concept of "root", which means that it 
is not Windows, MVS, or another operating system that does not call that level 
of access "root" access.

Perhaps you can provide some actually useful information such as:  the OS you 
are using, what type of operations you think are impacted (made slow) by I/O, 
whether or not the I/O channel "gets full" (ie, is the bottleneck), whether the 
CPU is fully consumed.  Etc.

And yes, Disk I/O makes a huge difference.  I have things that run on a "real 
computer (my laptop)" with an SSD hard drive that does I/O at 2.5 GB/s (yes, 
those are big-B Bytes and big G american billions) and has a significant sized 
"block" cache (as opposed to the modern preponderance of mostly useless 
filesystem caches) which can run a workload in 1/250th of the time taken 
compared to running the exact same workload on a typical "server class" machine 
configured with a high-speed hardware raid attached spinning rust.  The "server 
class" hardware has significantly better CPU, but the bottleneck (in this 
particular case) is apparently I/O.

Have you identified the "parts you think are slow" and instrumented them to see 
what is happening?  Although the SQLite query optimizer is very good, it does 
sometimes make incorrect or unnecessary optimizations that can severely impact 
performance.  Not to mention that there are a lot of other factors in play such 
as OS, filesystem, CPU, RAM, Virtualization, etc.






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

Re: [sqlite] Request for ISO Week in strftime()

[Olivier Mascia]

In ISO the weeks are indeed from Monday (1) to Sunday (7) and all days between 
a Monday and Sunday belong to the same week.

The first week (1) of a year is the one containing the first Thursday of the 
year. Or said differently containing the 4th of January.

This implies that : week 1 can start in the year-1 and there can be a week 53 
some years.

-- 
Best regards, Meilleures salutations, Met vriendelijke groeten,  
Olivier Mascia (from mobile device), http://integral.software

> Le 18 mai 2017 à 07:31, John McMahon  a écrit :
> 
> Sorry, re-sending to list.
> 
> Point of Clarification: The ISO Week begins as day 1 on Monday and ends as 
> day 7 on Sunday, hump day (colloq.) is Thursday. There may be other 
> repercussions in terms of week counts if this has not been implemented 
> correctly. I haven't checked, I do not use this personally.
> 
> John
> 
> 
> 
> 
>> On 17/05/2017 19:07, no...@null.net wrote:
>> The current '%W' week substitution appears to be US-specific. I would
>> like to make a feature request for a '%V' (or similar) substitution
>> that inserts the ISO-8601 week number.
> 
> -- 
> Regards
>   John McMahon
>  li...@jspect.fastmail.fm
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite in memory

[Paul]

If by any chance you have access to Linux or alike, you can just mount a ramfs 
and move database file over there.
It is a usual file system that lives in RAM. This will 100% guarantee you that 
no disk access will be made by SQLite.


18 May 2017, 08:18:47, by "Gabriele Lanaro" :
 
>   Hi, I'm trying to assess if the performance of my application is dependent
> on disk access from sqlite.
> 
> To rule this out I wanted to make sure that the SQLite DB is completely
> accessed from memory and there are no disk accesses.
> 
> Is it possible to obtain this effect by using pragmas such as cache_size?
> 
> Another solution is to copy the existing db to a :memory: db but I'd like
> to achieve the same effect without doing so (because it will require
> substantial modification of the application). For the sake of argument,
> let's image that using :memory: db is not an option.
> 
> Also using a ramdisk is not an option because I don't have root access to
> the machine.
> 
> Thanks,
> 
> Gabriele
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users