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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
