On Sun, Sep 6, 2009 at 5:23 PM, Benjamin Rutt<rut...@osu.edu> wrote:
> I noticed sqlite is using a lot of memory (up to 300MB before it hits a data
> segment size ulimit and fails) during a delete operation. This is
> reproducable using the 'sqlite3' executable distributed with sqlite. My
> platform is sqlite 3.6.13 with the OS being solaris 10 on sparc
> architecture, but the problem is reproducible on linux as well if I copy the
> database file to a linux machine. The relevant table schema is as follows:
>
> CREATE TABLE old_conns (a TEXT, b INTEGER, c TEXT, d TEXT, e TEXT, start
> INTEGER, end INTEGER, f INTEGER, g INTEGER, h INTEGER, i INTEGER)
> CREATE INDEX end_idx ON old_conns ( end )
>
> The delete operation fails as follows:
>
> sqlite> DELETE FROM old_conns WHERE end < strftime('%s', 'now', '-7
> days');
> SQL error: out of memory
>
Find out if the DELETEion is chewing up the memory or the SELECTion. Try
SELECT * FROM old_conns WHERE "end" >= strftime('%s', 'now', '-7 days');
If the above is quick, you can simply create a new table with that,
and then drop the old table.
CREATE TABLE new_conns AS SELECT * FROM old_conns WHERE "end" >=
strftime('%s', 'now', '-7 days');
DROP TABLE old_conns;
Of course, do change the name of the column "end" to something other
than a reserved keyword.
> I would have thought that the memory used would be small for a delete
> operation, but maybe there's some large temporary table being created in
> memory for this operation? Perhaps the fact that it has to update the index
> along with the delete is causing memory usage where it wouldn't otherwise if
> there was no index? It still fails if I set "pragma temp_store=1" which I
> believe instructs sqlite to put temporary tables on disk instead of memory.
>
> The sqlite file observable via 'ls -al' is about 8GB in size (although I've
> never vacuumed it), and the total size of the 'old_conns' table is about 68
> million rows. I expect roughly 50% of them would be deleted by the above
> delete operation, but I have yet to see it succeed.
>
> Is there any obvious explanation for this? Any administrative controls I
> can use to prevent it from happening?
>
> Thanks.
> --
> Benjamin Rutt
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
