[sqlite] How to make sqlite3_release_memory produce a result?

[Bart Smissaert]

Have compiled sqlite3.dll (latest) compiled with ENABLE_MEMORY_MANAGEMENT,
but sofar
not been able yet to make sqlite3_release_memory produce anything else than
0.
What would be the simplest way to make this happen?
I don't want to do this with C coding, so it should be some SQL scenario or
to do with simple
SQLite functions such as sqlite3_step, _prepare, -finalize etc.

RBS

[sqlite] How to make sqlite3_release_memory produce a result?

[Scott Hess]

On Wed, Jan 6, 2016 at 3:03 PM, Bart Smissaert 
wrote:

> Have compiled sqlite3.dll (latest) compiled with ENABLE_MEMORY_MANAGEMENT,
> but sofar
> not been able yet to make sqlite3_release_memory produce anything else than
> 0.
> What would be the simplest way to make this happen?
> I don't want to do this with C coding, so it should be some SQL scenario or
> to do with simple
> SQLite functions such as sqlite3_step, _prepare, -finalize etc.


Last time I was paying attention to this, I believe that I found that the
biggest effect was to free unpinned pages from the page cache.  So it might
not free pages if you're in a transaction, for instance.  I would guess
that if you had memory-mapped mode on and are doing only reads, there would
be no pages to free (mmap pages aren't in the page cache).  So you should
see results if you start a transaction, do a few update statements, commit
the transaction, the call sqlite3_release_memory(db).

-scott

[sqlite] How to make sqlite3_release_memory produce a result?

[d...@marconpies.com]

Can someone stear me to a good tutorial to setup and use with windows computer 
and visual sudio 2008

Tham\nks





- Original Message -
From: Bart Smissaert 
Reply-To: SQLite mailing list 
To: General Discussion of SQLite Database 
Sent: 1/6/2016 5:03:55 PM
Subject: [sqlite] How to make sqlite3_release_memory produce a result?



Have compiled sqlite3.dll (latest) compiled with ENABLE_MEMORY_MANAGEMENT,
but sofar
not been able yet to make sqlite3_release_memory produce anything else than
0.
What would be the simplest way to make this happen?
I don't want to do this with C coding, so it should be some SQL scenario or
to do with simple
SQLite functions such as sqlite3_step, _prepare, -finalize etc.

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

[sqlite] {Spam?} SQLite take lower performance while usingshared cache on iOS/Mac

[Scott Perry]

The SQLite built into OS X does not support cache sharing for performance 
reasons?, which is probably why your results are statistically identical and 
the OP's results are wildly different.

You can verify this by checking the return value of 
sqlite3_enable_shared_cache; on OS X it returns SQLITE_MISUSE.

---
? On multicore systems, memory barriers are extremely expensive; not sharing 
caches allows them to run lock-free

On Dec 20, 2015, at 7:05 AM, E.Pasma  wrote:
> 
> 20 dec 2015, 14:29, sanhua.zh:
> 
>> Here is the test result for selecting 100,000 items in original test case.
>> 
>> 
>> shared cache mode
>> 2015-12-20 21:24:58.714 Test[1126:11609] cost 2.173480
>> 2015-12-20 21:24:58.714 Test[1126:11610] cost 2.173449
>> 2015-12-20 21:24:58.714 Test[1126:11608] cost 2.173768
>> 2015-12-20 21:24:58.714 Test[1126:11611] cost 2.173169
>> 
>> 
>> without shared cache mode
>> 2015-12-20 21:28:49.647 Test[1286:13077] cost 0.028914
>> 2015-12-20 21:28:49.647 Test[1286:13078] cost 0.028914
>> 2015-12-20 21:28:49.647 Test[1286:13079] cost 0.028964
>> 2015-12-20 21:28:49.647 Test[1286:13076] cost 0.028958
>> 
>> 
>> May be your python code does not run the same thing as mine, I am poor in 
>> python so that I could not figure it out.
> Indeed, I had changed the query to just scan the table in the database and 
> not return all  rows. Now I chaged that and also set the corresponding number 
> of rows. Still cache sharing does not make such a mega differencr. Below are 
> the timings.
> I have no clu now for your mega difference.
> SQLite version? 3.8 here
> hardware? I have (only) Mac OS X 10.5.8 with a 1.22 GHz PowerPC
> 
> Timings for default mode:
> $ python3 larry3.py [B
> sqlite version 3.8.3.1
> cache sharing 0 cache size 2000 rowcount 10
> after split 0.0037369728088378906
> after start 0.8668131828308105
> steps 10 connect+fetch 4.39 connect 0.0
> steps 10 connect+fetch 4.52 connect 0.0
> steps 10 connect+fetch 4.62 connect 0.0
> steps 10 connect+fetch 4.51 connect 0.0
> elapsed 5.21
> 
> Timings in shared cache mode:
> sqlite version 3.8.3.1
> cache sharing 1 cache size 2000 rowcount 10
> after split 0.0035581588745117188
> after start 0.7083160877227783
> steps 10 connect+fetch 6.4 connect 0.0
> steps 10 connect+fetch 6.17 connect 0.0
> steps 10 connect+fetch 6.56 connect 0.0
> steps 10 connect+fetch 6.46 connect 0.0
> elapsed 6.85
> 
> Python script:
> 
> import random, os, time, threading
> import sqlite3 as sqlite
> print ("sqlite version", sqlite.sqlite_version)
> 
> TESTDB='larry.tmp'
> SHARED=0
> SIZE=2000
> ROWCOUNT=10
> print('cache sharing', SHARED, 'cache size', SIZE, "rowcount", ROWCOUNT)
> sqlite.enable_shared_cache(SHARED)
> 
> def connect():
>con= sqlite.Connection (TESTDB, isolation_level=None, check_same_thread=0)
>con.execute ("pragma cache_size=%i"%(SIZE,))
>return con
> 
> def e_str (e):
>" format exception as string "
>return "%s: %s" % (e.__class__.__name__, e)
> 
> class Testthread (threading.Thread):
>"""
>execute query in a thread
>"""
>def __init__ (self, qq, con = None):
>self.con = con
>self.qq = qq
>self.out = "thr%i.out" % id (self)
>open (self.out, 'w').close ()
>os.remove (self.out)
>threading.Thread.__init__ (
>self,
>target=self.__target,
>)
>def start (self):
>threading.Thread.start (self)
>for retry in range (5):
>time.sleep (2 ** retry * .05)
>if os.access (self.out, os.R_OK):
>break
>else:
>print("Testthread: spoolfile does not appear")
>time.sleep (.10) # for the SQL to start
>def __target (self):
>subt0=time.time()
>if not self.con:
>self.con = connect ()
>dt1=round(time.time()-subt0,2)
>f = open (self.out, 'w')
>try:
>try:
>n=0
>for q in self.qq.split (';'):
>i=None
>for i in self.con.execute(q):
>n+=1
>continue
>f.write (str(i)+'\n') # write last line only
>dt2=round(time.time()-subt0,2)
>print("steps", n, "connect+fetch", dt2, "connect", dt1)
>except Exception as e:
>f.write (e_str (e) + '\n')
>finally:
>f.close()
>self.con.close ()
>def join (self, timeout=None):
>if timeout is not None:
>threading.Thread.join (self, timeout)
>else:
>timeout = 7.5 # respond to keyboard interrupts
>while self.isAlive ():
>threading.Thread.join (self, timeout)
>return self
>def get_result (self):
>try:
>return open (self.out, 'r').read ().strip ()
>except IOError as e:
>return None
> 
> def main ():

[sqlite] Caveats using Sqlite on JFFS2 ?

[Stelling Carsten]

Additionally, to prevent early wearout of your persistent storage, make sure to 
configure SQLite, such that temporary files are stored in RAM rather than 
Flash. Compile with SQLITE_TEMP_STORE=3 or use PRAGMA temp_store 
(https://www.sqlite.org/tempfiles.html). In an embedded system, you should 
prefer the compiler switch. Furthermore, you like to tune SQLite's page cache 
and other memory optimization parameters to adapt SQLite to your constrained 
memory resources. At least, consider to use UBIFS over JFFS2 if possible. 
SQLite in normal journaling mode and UBIFS on NAND-Flash is fast and very 
reliable, even in cases of sudden power loss during transactions.


__
 Carsten Stelling
F Hardware / R Hardware

G?RLITZ Aktiengesellschaft | August-Thyssen-Stra?e 32 | D-56070 Koblenz
T: +49-261-9285-336 | M:  | F: +49-261-9285-190
Mail to: Carsten.Stelling at goerlitz.com | www.goerlitz.com

Vorstand | Executive Board: J?rg Figge
Vorsitzender des Aufsichtsrates | Chairman of the supervisory board: Norbert 
Wagner
Registergericht | Court of registration: Amtsgericht Koblenz HRB 5346
Sitz der Gesellschaft | Registered office: Koblenz
Sind Sie bereit f?r das Schl?sselerlebnis? www.goerlitz.com/e-world-2016
Member of the IDS-Group

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von David 
Woodhouse
Gesendet: Dienstag, 5. Januar 2016 23:50
An: Ward WIllats; General Discussion of SQLite Database
Betreff: Re: [sqlite] Caveats using Sqlite on JFFS2 ?

On Mon, 2016-01-04 at 13:17 -0800, Ward WIllats wrote:
> Happy New Year folks.
>
> Subject says it all. Any things to look out for when using Sqlite on
> a JFFS2 filesystem? I see some old stuff from 2011 about WAL mode and
> MMAP_SHARED, but suspect that is no longer germane? Anything else to
> watch out for? (This would be for an Open-WRT style embedded Linux on
> MIPS.)

JFFS2 still doesn't support shared writeable mmap; that kind of thing
is discouraged on flash. But other than that, there should be nothing
that's really specific to JFFS2. You want to avoid doing too many
writes, obviously, but that's true for *all* flash-based storage, even
the ones which are pretending to be spinning rust and hiding the
details from the OS.

--
David WoodhouseOpen Source Technology Centre
David.Woodhouse at intel.com  Intel Corporation