Re: [sqlite] [Legacy Email] Re: memory leak?

[Reid Thompson]

On Thu, 2018-04-12 at 13:00 -0600, Warren Young wrote:
> [EXTERNAL SOURCE]
> 
> 
> 
> On Apr 12, 2018, at 11:14 AM, Jens Alfke  wrote:
> > 
> > I'm a Mac/iOS developer so I use the 'leaks' tool and Instruments app; I 
> > don't know how this is done on Linux.
> 
> Valgrind: 
> https://urldefense.proofpoint.com/v2/url?u=http-3A__valgrind.org_=DwIGaQ=w8fEmIi-B5lsC0MnOiGTloUx5YmN70-3jVCmqqbXNxM=jgazr1zjWOphYRYUjdZQeDxY8qk_8iVga1Rl0Pl9NMY=rF1yII94jNuHVoSMnM7
> Kf9HlTpsS50guoefTHI1PyME=w9Qpp2hG7-6tKMX_odOKaYnYVZpSFRRgtsHlHtI1LGg=
> 
> Also: 
> https://urldefense.proofpoint.com/v2/url?u=http-3A__valgrind.org_docs_manual_faq.html-23faq.pronounce=DwIGaQ=w8fEmIi-B5lsC0MnOiGTloUx5YmN70-3jVCmqqbXNxM=jgazr1zjWOphYRYUjdZQeDxY8qk_8iVga
> 1Rl0Pl9NMY=rF1yII94jNuHVoSMnM7Kf9HlTpsS50guoefTHI1PyME=jGYySPWcDyGzXECRNB_RIZ9KEIb0KA99JbnwmnjbrhM=
> 
> :)

I've found that scan-build, part of llvm/clang,  is very useful.

https://developer.arm.com/products/system-design/fast-models/docs/dui0741/latest/overview-of-the-arm-compiler-6-toolchain/clang-and-llvm-documentation

reid

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

Re: [sqlite] feature request: MIN() and MAX() of set of row values

[Mark Brand]

On 13/04/18 14:12, Simon Slavin wrote:

On 13 Apr 2018, at 8:40am, Mark Brand  wrote:


It also occurs to me that COUNT() should work (but doesn't) over sets of row 
values:

 sqlite> select count((1,2));
 Error: row value misused

I would expect it to return the number of non-NULL row values in the set.

What should this do, and why ?

CREATE TABLE MyTable (a INTEGER, b, INTEGER, c INTEGER);
INSERT INTO MyTable VALUES (1, 1, 1);
INSERT INTO MyTable VALUES (2, 2, 2);
...
INSERT INTO MyTable VALUES (10, 10, 10);

SELECT COUNT(a, b, c) FROM MyTable;


Hi Simon,

I would expect COUNT() to count row values just as it counts normal 
values. In your example, it should return 10 because there are 10 rows 
in the MyTable.


When counting normal values, COUNT() excludes NULLs. If there is a such 
as thing as a NULL row value, COUNT() should exclude it too.


Sqlite doesn't seem to distinguish between a row value made up of only 
NULLs and a NULL row value, at least in this context:


    sqlite> select (NULL, NULL) IS (SELECT 1, 2 WHERE 0);  -- The right 
side would probably satisfy anybody's idea of what "NULL row value" means.

    1

    sqlite> select (NULL, NULL) IS (SELECT NULL, NULL WHERE 1); -- The 
right side is a row containing all NULLs.

    1

Therefore, probably COUNT() should exclude row values made up of only 
NULLs on the grounds that these qualify as NULL row values.


You wrote

    COUNT(a, b, c)

but I would have expected

    COUNT((a, b, c))

to make it clear that COUNT() has one argument which is a row value. For 
aggregate MIN() and MAX(), the "extra" parenthesis would have the 
additional motivation of distinguishing the aggregate functions from the 
non-aggregate MIN() and MAX() which have 2 arguments.



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

Re: [sqlite] feature request: MIN() and MAX() of set of row values

[Simon Slavin]

On 13 Apr 2018, at 8:40am, Mark Brand  wrote:

> It also occurs to me that COUNT() should work (but doesn't) over sets of row 
> values:
> 
> sqlite> select count((1,2));
> Error: row value misused
> 
> I would expect it to return the number of non-NULL row values in the set.

What should this do, and why ?

CREATE TABLE MyTable (a INTEGER, b, INTEGER, c INTEGER);
INSERT INTO MyTable VALUES (1, 1, 1);
INSERT INTO MyTable VALUES (2, 2, 2);
...
INSERT INTO MyTable VALUES (10, 10, 10);

SELECT COUNT(a, b, c) FROM MyTable;

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

Re: [sqlite] memory leak?

[Peter Da Silva]

It is normal in all modern operating systems for unused memory to be allocated 
to buffer cache, so over time the "free" memory Is expected to go down unless 
disk activity is near quiescent.  Some operating systems will combine "cache" 
and "free" memory to hide this from the casual user, but I consider this 
deceptive.

On 4/12/18, 9:34 PM, "sqlite-users on behalf of king3306" 
 
wrote:

first thanks you reply 

After running for some time  i found sqlite3_memory_used return value keep
constant about 2M,but linux free memory is fewer and fewer,i make sure no
other places are leaked,why?

this whether or not a normal behavior?

if not ,how can i to analysis this problem?  

Looking forward to your answer



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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] __gnu_strerror_r undefined when building for android api-24, no issue with api-22

[Frank van Vugt]

L.S.

Op vrijdag 13 april 2018 12:18:52 CEST schreef Frank van Vugt:
> Probably the unified headers in NDK are to blame as well, but:
> * when using ndk r16b to build sqlite for api-24 then the resulting library
> has __gnu_strerror_r unresolved

Oh, one addition: the toolchain used has been built using '--stl=libc++', since 
gnustl has 
been deprecated for a while now and beginning with NDKr16 libc++ should be used.






Best, 



Frank.

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

[sqlite] __gnu_strerror_r undefined when building for android api-24, no issue with api-22

[Frank van Vugt]

L.S.

Probably the unified headers in NDK are to blame as well, but:

* when using ndk r16b to build sqlite for api-22 then the resulting library 
has no issues

* when using ndk r16b to build sqlite for api-24 then the resulting library 
has __gnu_strerror_r unresolved


Relevant info on building:

export CC=clang 
export CPPFLAGS="-DSQLITE_OMIT_DEPRECATED -DSQLITE_ENABLE_STAT4 -
DSQLITE_DEFAULT_FOREIGN_KEYS=1 -DSQLITE_ENABLE_JSON1=1 -
DSQLITE_ENABLE_COLUMN_METADATA=1" 

$SQLITESRCDIR/configure --host=arm-linux-androideabi --enable-threadsafe



The relevant part of /sysroot/usr/include/string.h:129

#if defined(__USE_GNU) && __ANDROID_API__ >= 23 
char* strerror_r(int __errno_value, char* __buf, size_t __n) 
__RENAME(__gnu_strerror_r) __INTRODUCED_IN(23); 
#else /* POSIX */ 
int strerror_r(int __errno_value, char* __buf, size_t __n); 
#endif


The relevant parts in sqlite.c can be found by looking for strerror_r



Obviously it'd be nice to be able to build and use sqlite in serialized mode 
for Android api-24 and up 'just like that' ;)



-- 

Best,




Frank.

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

Re: [sqlite] [EXTERNAL] Preupdate hook column name

[Hick Gunter]

Then do it when connecting to the database

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von João Ramos
Gesendet: Freitag, 13. April 2018 11:58
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Preupdate hook column name

I can't do that (execute other statements) inside a sqlite3_preupdate_hook 
callback.

On Fri, Apr 13, 2018 at 6:50 AM, Hick Gunter  wrote:

> See pragma table_info;
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von João Ramos
> Gesendet: Donnerstag, 12. April 2018 20:54
> An: SQLite mailing list 
> Betreff: [EXTERNAL] [sqlite] Preupdate hook column name
>
> Hi,
>
> I've successfully added support for the sqlite3_preupdate_hook(), but
> I also need to get the column name when calling either
> sqlite3_preupdate_old() or sqlite3_preupdate_new().
> I know that I can obtain the type of the value of the column at index
> i by calling sqlite3_value_type() but I also need the name of the
> column and I can't find a way to do this.
>
> Is this even possible?
>
> Thank you,
>
> --
> *João Ramos*
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH
> | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013
> | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
*João Ramos*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Preupdate hook column name

[João Ramos]

I can't do that (execute other statements) inside a sqlite3_preupdate_hook
callback.

On Fri, Apr 13, 2018 at 6:50 AM, Hick Gunter  wrote:

> See pragma table_info;
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von João Ramos
> Gesendet: Donnerstag, 12. April 2018 20:54
> An: SQLite mailing list 
> Betreff: [EXTERNAL] [sqlite] Preupdate hook column name
>
> Hi,
>
> I've successfully added support for the sqlite3_preupdate_hook(), but I
> also need to get the column name when calling either
> sqlite3_preupdate_old() or sqlite3_preupdate_new().
> I know that I can obtain the type of the value of the column at index i by
> calling sqlite3_value_type() but I also need the name of the column and I
> can't find a way to do this.
>
> Is this even possible?
>
> Thank you,
>
> --
> *João Ramos*
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
*João Ramos*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

[Hick Gunter]

Just to be sure of the real issue:

- your application runs just fine with a small amount of memory (2M) for most 
queries
- a small subset of queries requires "the whole db" to be kept in memory for 
performance reasons
- the application should revert to "small memory" when the "whole db" query is 
finished

The most common case where throwing memory at a query helps is when the query 
in question is doing a lot of re-reading of rows. This aagain is commonly 
caused by choosing an inferior data model (that makes the required information 
"hard to get") and/or an inferior execution plan (that does a lot of avoidable 
re-reading of rows).

- Revisit your data model to make the desired information less "hard to get", 
e.g. reduce the number of joins required.
- Run ANALYZE on a production-sized, typical data set; this allows the QP to 
devise a better plan
- Add more indexes, rerun ANALYZE, see if the QP has changed plans, drop unused 
indexes, check performance. Rinse and repeat.
- Manually adjust the order of joins (use CROSS JOIN syntax to prevent 
reordering of tables by the QP)

I have found that most queries that join a multitude of tables can be split 
into "determine result set" and "add info for display purposes". Put the 
"determine result set" tables first, and the "add info for display" tables last.

These measures should shave several orders of magnitude from execution times.

To answer the orginal question: Turn up the mmap_size before running the "whole 
db" query and turn it back down afterwards.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nick
Gesendet: Freitag, 13. April 2018 10:07
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

I find I confused several concepts of memory. Sorry for that.
And I guess I finally understand what my question really is:

Still there is only one process doing a SELECT * in a 256M db file.
Then 256M physical memory should be used when doing the query. (Ignore the
cache_size.)
So the PSS of my program should be 256M at that time. That is OK.

But from now on, the PSS will be 256M for a long time as my process will be 
active for hours doing insert-select-insert-select without closing. My system 
can not afford a 256M-PSS program.
In another word, the most important thing is there is no opportunity to call
unmmap() in my program.

Can I find a reasonable time to call unmmap or sqlite3OsUnfetch? Or is there 
any way to solve the problem other than pragma mmap_size=2M?

Really thanks for your help.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Wal index format questions

[Harmen]

Hello,

thanks for the good documentation of the various database file formats. The wal
index one is a bit unclear to me in a few spots, maybe someone can help me out?
https://sqlite.org/walformat.html

2.1. The WAL-Index Header
there is a second copy of the WAL index information, but it's not specified
what to do with that information. Can it be ignored? Does it have to be
correct?

2.1.3. WAL Locks
does a read connection usually keep a read lock on WAL_READ_LOCK(0), and
switch to any of WAL_READ_LOCK(1..4) when it starts a transaction? Does that
also mean there is a maximum of 4 concurrent read-only transactions?

2.2. WAL-Index Hash Tables
I think I figured out how it works, but both explanations of the hashing
algorithm doesn't use the `aHash` field. It's either unclear or wrong :)
(e.g., in point 2., `aPgno[j%8192]!=0` should be `aHash[j%8192]!=0` if I
understand things correctly)

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

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

[Nick]

I find I confused several concepts of memory. Sorry for that.
And I guess I finally understand what my question really is:

Still there is only one process doing a SELECT * in a 256M db file.
Then 256M physical memory should be used when doing the query. (Ignore the
cache_size.)
So the PSS of my program should be 256M at that time. That is OK.

But from now on, the PSS will be 256M for a long time as my process will be
active for hours doing insert-select-insert-select without closing. My
system can not afford a 256M-PSS program.
In another word, the most important thing is there is no opportunity to call
unmmap() in my program.

Can I find a reasonable time to call unmmap or sqlite3OsUnfetch? Or is there
any way to solve the problem other than pragma mmap_size=2M?

Really thanks for your help.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: MIN() and MAX() of set of row values

[Mark Brand]

On 13/04/18 09:32, Mark Brand wrote:

On 30/03/18 18:55, Igor Tandetnik wrote:



Row values support less-than comparison, so it kind of makes sense to 
expect MIN to work on them, too.


That's what I was thinking too. One would expect aggregate MIN() and 
MAX() to work over row values.


While we're on the subject of row values, the error on the first query 
below seems unexpected:


   sqlite> select (2, 3) = (select (2, 3));
    Error: row value misused

    sqlite> select (2, 3) = (2, 3);
    1

    sqlite> select (2, 3) = (values(2, 3));
    1



It also occurs to me that COUNT() should work (but doesn't) over sets of 
row values:


    sqlite> select count((1,2));
    Error: row value misused

I would expect it to return the number of non-NULL row values in the set.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: MIN() and MAX() of set of row values

[Mark Brand]

On 30/03/18 18:55, Igor Tandetnik wrote:



Row values support less-than comparison, so it kind of makes sense to 
expect MIN to work on them, too.


That's what I was thinking too. One would expect aggregate MIN() and 
MAX() to work over row values.


While we're on the subject of row values, the error on the first query 
below seems unexpected:


   sqlite> select (2, 3) = (select (2, 3));
    Error: row value misused

    sqlite> select (2, 3) = (2, 3);
    1

    sqlite> select (2, 3) = (values(2, 3));
    1


Mark

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

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

[Hick Gunter]

AFAICT the showmap program is just a pretty-printer for the /proc//smaps 
file on android.

You need to compare three states:

1) before mmap() is called (there should be no section referring to your file)
2) after mmap() is called (the section should be there, but RSS=PSS=0)
3) after the loop (the section should be there and RSS=PSS > 0)

Which length(s) did you try? Perhaps android mmap is trying to be clever and is 
preloading parts of the file (you would see this in state 2).

With respect to PSS, there is no difference between setting the page cache size 
and setting the mmap size to the amount of PSS you are willing to allow for db 
access (instead of the database size). What makes you think that using mmap 
will be superior to using the page cache?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nick
Gesendet: Freitag, 13. April 2018 05:37
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

OK, I understand.

I ran a simple program to test if mmap will cause the increasing of PSS. But I 
did not find the PSS increase according to showmap:
addr = (char *)mmap(NULL, length, PROT_READ, MAP_SHARED, fd, 0);
for(i=0; i