[sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
Hi, I'm using PRAGMA temp_store=2 so all temp tables are created in memory. I have temp table with 1 000 000 000 records. Memory used by my test application grow up to ~80 MB. If I delete all records from this table or drop table, then my application still use 80 MB of memory. I have tried also PRAGMA shrink_memory. Why sqlite don't free memory? Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
On 28 Dec 2012, at 3:04pm, Krzysztof dib...@wp.pl wrote: If I delete all records from this table or drop table, then my application still use 80 MB of memory. I have tried also PRAGMA shrink_memory. Why sqlite don't free memory? SQLite does not free up space from a database until it does a VACUUM. Either use the command for it: http://www.sqlite.org/lang_vacuum.html or use the auto-vacuum pragma: http://www.sqlite.org/pragma.html#pragma_auto_vacuum Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
Can I call vacuum on custom tables (for example only on temp tables) ? Because vacuum takes some time (my normal tables are big and often modified, trim of those tables are not important for me) and I would like to free up memory taken only by temp tables ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
On 28 Dec 2012, at 6:01pm, Krzysztof dib...@wp.pl wrote: Can I call vacuum on custom tables (for example only on temp tables) ? Because vacuum takes some time (my normal tables are big and often modified, trim of those tables are not important for me) and I would like to free up memory taken only by temp tables VACUUM is something you do to the database as a whole, not individual tables. It may be that instead of using temp=2 you should specifically be using the 'memory' database space: http://www.sqlite.org/inmemorydb.html That way, to retrieve all the memory you're missing you can just close the connection you have open, open a new one, and CREATE new TABLEs. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
You may be hitting the memory fragmentation issue. Try to run your application with different memory managers (Hoard is my favorite - http://www.hoard.org/) and see if the memory consumption is the same. Also if you close all connections to your database (and other SQLite databases too) does amount of used memory get lower? Pavel On Fri, Dec 28, 2012 at 7:04 AM, Krzysztof dib...@wp.pl wrote: Hi, I'm using PRAGMA temp_store=2 so all temp tables are created in memory. I have temp table with 1 000 000 000 records. Memory used by my test application grow up to ~80 MB. If I delete all records from this table or drop table, then my application still use 80 MB of memory. I have tried also PRAGMA shrink_memory. Why sqlite don't free memory? Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
On Fri, Dec 28, 2012 at 10:04 AM, Krzysztof dib...@wp.pl wrote: Hi, I'm using PRAGMA temp_store=2 so all temp tables are created in memory. I have temp table with 1 000 000 000 records. Memory used by my test application grow up to ~80 MB. If I delete all records from this table or drop table, then my application still use 80 MB of memory. I have tried also PRAGMA shrink_memory. Why sqlite don't free memory?' The memory is available for reuse and will be reused for subsequent TEMP tables. The memory is not freed until the database connection closes, however. After 12 years and over 1,000,000 applications, you are the first person to request that memory from deleted TEMP tables actually be freed. Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
I thought about reopen connection (this would solve my problem in simple way) but my dependencies are tricky. I'm using free pascal which have TSQLite3Connection. This class handle sqlite connection, so I can attach multiple tables in the same session, so I can share temp tables between all tables in my database. If I close connection, I lose data from all temp tables :/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
On 28 Dec 2012, at 10:16pm, Krzysztof dib...@wp.pl wrote: I thought about reopen connection (this would solve my problem in simple way) but my dependencies are tricky. I'm using free pascal which have TSQLite3Connection. This class handle sqlite connection, so I can attach multiple tables in the same session, so I can share temp tables between all tables in my database. Okay. Well as you can guess, the memory is reused immediately you start inserting new rows. It's always available to SQLite, it's just SQLite isn't releasing it to other parts of your application. If I close connection, I lose data from all temp tables :/ True. If you need to do operations on multiple tables at once, there's a good reason to keep all the tables accessible using the same connection. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
I don't understand :/ So what is solution in my case? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
On Sat, 29 Dec 2012 00:04:12 +0100, Krzysztof dib...@wp.pl wrote: I don't understand :/ So what is solution in my case? You could try : PRAGMA temp_store=file; immediately followed by: PRAGMA temp_store=memory; Please note: http://sqlite.org/pragma.html#pragma_temp_store When the temp_store setting is changed, all existing temporary tables, indices, triggers, and views are immediately deleted. Warning: I didn't do any real research on this, and not all your requirements so I'm not sure this is of any help. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
On 28/12/2012 4:04 PM, Krzysztof wrote: I don't understand :/ So what is solution in my case? What is the problem you need to solve? If I understand correctly, your app repeatedly creates and deletes ~80MB of temp data. If so, it's actually a good thing that sqlite doesn't release the memory, because it would just have to re-allocate it soon anyway. I see nothing to worry about unless your platform is really memory-starved (to the point that freeing up 80MB actually matters), or you see the memory footprint growing with each create/delete cycle (indicating a memory leak). Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
