On 6 Sep 2009, at 11:23pm, Benjamin Rutt 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
You're doing this on 68 million rows. While it probably shouldn't give an error in this way, I can imagine various things that might cause it. To help with debugging, and also as a suggested fix until the problem can be investigated, could you pre-calculate your 'strftime' value, and use that in the command instead ? It would help to see whether the calculation of this is the thing causing the error. You can use SQLite to do the calculation sqlite> SELECT strftime('%s', 'now', '-7 days'); 1251679819 if you like. Then take whatever value you get and plug it into the DELETE command: sqlite> DELETE FROM old_conns WHERE end < 1251679819; I have an observation though I don't think it's related. You're using the word 'end' as a column name. In the table on http://www.sqlite.org/lang_keywords.html it's listed as a keyword. You might want to avoid this in case it causes problems in the future, when you try to use the word in a context where it's ambiguous. Perhaps use conn_start and conn_end ? Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users