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