Hi Aneesh, I collect some information(see below 3 steps to optimize mem use) to answer your questions.
Step 1 ** PRAGMA temp_store ** Step 2 ** Incrment default cache size PRAGMA default_cache_size ** Step 3 ** PRAGMA synchronous ** Step 1 ** PRAGMA temp_store ** Temporary storage is where SQLite keeps transient data such as temporary tables, indexes, and other objects. By default, SQLite uses a compiled-in location, which varies between platforms. There are two pragmas that govern temporary storage: temp_store and temp_store_directory. The first pragma determines whether SQLite uses memory or disk for temporary storage. There are actually three possible values: DEFAULT, FILE, or MEMORY. DEFAULT uses the compiled-in default, FILE uses an operating system file, and MEMORY uses RAM. If FILE is set as the storage medium, then the second pragma, temp_store_directory, can be used to set the directory in which the temporary storage file is placed. Step 2 ** Incrment default cache size PRAGMA default_cache_size ** To set the default cache size for the current session, you use the cache_size pragma: sqlite> PRAGMA cache_size; cache_size --------------- 2000 sqlite> PRAGMA cache_size=10000; sqlite> PRAGMA cache_size; cache_size --------------- 10000 You can permanently set the cache size for all sessions using the [ default_cache_size ] pragma (See http://www.sqlite.org/pragma.html). This setting is stored in the database. This will only take effect for sessions created after the change, not for currently active sessions. If you or your program(s) perform many updates or deletes on a database that is being used by many other sessions, it may help you to increase the cache size. The larger the cache size, the more modifications a session can cache change before it has to get an EXCLUSIVE lock. This not only allows a session to get more work done before having to wait, it also cuts down on the time the exclusive locks needs to be held, as all the work is done up front. In this case, the EXCLUSIVE lock only needs to be held long enough to flush the changes in the cache to disk. The cache_size controls the size of the page cache. The bigger the page cache, the more modified pages the pager can store, and the more work the connection can do before having to enter EXCLUSIVE. Step 3 ** PRAGMA synchronous ** Please see parameters at http://www.sqlite.org/pragma.html Differnt parameter has different impact to the speed of database journaling mechanism. The "FULL" seesm safer but slower in flush cache. The "Norma" is a trade-off. The "NONE" maybe unsafe to you. Thanks, Peter On Thu, Apr 23, 2009 at 8:00 PM, <sqlite-users-requ...@sqlite.org> wrote: > Send sqlite-users mailing list submissions to > sqlite-users@sqlite.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > or, via email, send a message with subject or body 'help' to > sqlite-users-requ...@sqlite.org > > You can reach the person managing the list at > sqlite-users-ow...@sqlite.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of sqlite-users digest..." > > > Today's Topics: > > 1. Re: TEMP_STORE not working ?(3.5.9) (Kris Groves) > 2. Creating procedures in sqlite (Jyoti Seth) > 3. Re: Creating procedures in sqlite (Kris Groves) > 4. Sqlite flushing from cache to disk (Aneesh) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Thu, 23 Apr 2009 07:33:16 +0200 > From: Kris Groves <kris.gro...@mmlab.de> > Subject: Re: [sqlite] TEMP_STORE not working ?(3.5.9) > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Message-ID: <49effd9c.6000...@mmlab.de> > Content-Type: text/plain; charset=ISO-8859-1 > > Hi, > > Just want to bump this, I really need to get to the bottom of this. > > Thanks for any info, > Kris. > > Kris Groves wrote: > > Hi, > > From what I understand : > > - default behavior is to use files for temporary stuff. > > - the directory that will be used for these temporary files can be > > defined via pragma (temp_store_directory). If the pragma is not used, > > it will default to the first hardcoded directory (linux), in the order > > that follows: /var/tmp, /usr/tmp, /tmp, or finally current directory. > > > > So, in the environment I am running in, either those directories do not > > exist, or are not writable to the user under which the process is > > running. The result being an "error 14: unable to open database file" > > as soon as temporary files are needed. > > > > After a little digging I discover SQLITE_TEMP_STORE compilation flag. > > So I export CFLAGS=-DSQLITE_TEMP_STORE=3, run configure and remake, > > figuring that the temp files will now reside in memory, and need no > > writing into a directory. However, the problem remains. > > > > When I look through the code, there is no instance of SQLITE_TEMP_STORE, > > only TEMP_STORE... So I repeat the above with -DTEMP_STORE. Same result. > > > > Then I add a path that I know is accessible to the user under which the > > process runs, to the azDirs array in the unixGetTempname function. > > Voila.. working now.. > > > > I've retested with default TEMP_STORE and TEMP_STORE compiled in a > > 3(memory only). And regardless of the setting, it only works if there > > is a readable/writable directory... > > > > I would think that if TEMP_STORE=3, then no directory is required ? Is > > this a bug, or am I misunderstanding something ? > > > > Thanks, > > Kris. > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > ------------------------------ > > Message: 2 > Date: Thu, 23 Apr 2009 14:48:42 +0530 > From: "Jyoti Seth" <jyotiseth2...@gmail.com> > Subject: [sqlite] Creating procedures in sqlite > To: <sqlite-users@sqlite.org> > Message-ID: <000001c9c3f4$81ebf190$85c3d4...@com> > Content-Type: text/plain; charset="us-ascii" > > Hi All, > > > > SQLite lacks built-in support for stored procedures. We need to create it > in > our application. Please guide me any alternative method to achieve this > functionality. > > > > Thanks, > > Jyoti > > > > > > ------------------------------ > > Message: 3 > Date: Thu, 23 Apr 2009 10:37:04 +0200 > From: Kris Groves <kris.gro...@mmlab.de> > Subject: Re: [sqlite] Creating procedures in sqlite > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Message-ID: <49f028b0.7030...@mmlab.de> > Content-Type: text/plain; charset=ISO-8859-1 > > Perhaps this helps ? > http://sqlite.org/c3ref/create_function.html > > Jyoti Seth wrote: > > Hi All, > > > > > > > > SQLite lacks built-in support for stored procedures. We need to create it > in > > our application. Please guide me any alternative method to achieve this > > functionality. > > > > > > > > Thanks, > > > > Jyoti > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > Kris Groves > > Project Manager / Software Engineer mm-lab GmbH > Phone: +49 7154 827 323 Stammheimer Str. 10 > Fax: +49 7154 827 350 D-70806 Kornwestheim > kris.gro...@mmlab.de www.mmlab.de > > Domicile of Company: Kornwestheim, Germany > District Court - Court of Registration Stuttgart HRB 207257 > Managing Directors: Bernd Herrmann, Lothar Krank, > Michael Meiser, Dr. Andreas Streit > > > > ------------------------------ > > Message: 4 > Date: Thu, 23 Apr 2009 10:34:52 +0530 > From: Aneesh <anees...@cms.com> > Subject: [sqlite] Sqlite flushing from cache to disk > To: sqlite-users@sqlite.org > Message-ID: <1240463092.2370.9.ca...@ani> > Content-Type: text/plain > > Hi All, > I would like to use sqlite for an arm-linux embedded project. My > storage space is a flash drive and it have some life time and it depends > on write cycles. > So i wold like to minimize the disk hitting of data write. I am planning > to store all data in to sqlite cache and whenever data filled the cache > I want t o flush the cache to disk database automatically.Whether it is > possible? If yes , How i can do that? Incrementing default cache size > will give a solution? > Expecting an answer !! > > Thanks And Regards > Aneesh > > > > > ------------------------------ > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > End of sqlite-users Digest, Vol 16, Issue 67 > ******************************************** > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
