On Wed, Oct 17, 2012 at 11:58 AM, Ivan P <iva...@cloudberrylab.com> wrote:
> Hello! > > I've got Out-Of-Memory error when delete records from a table that has > about 150,000,000 records. > > The table is created as: > CREATE TABLE differential_parts_temp (plan_id TEXT, [version_id] INT NOT > NULL, [page_index] INT NOT NULL, [md5low] INT64 NOT NULL, [md5high] INT64 > NOT NULL); > CREATE INDEX differentialpartstemp_versionid ON differential_parts_temp > (version_id, plan_id); > > It has approx 150,000,000 records. > The VERY MOST (if not all) of those records have > plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43' > > The following request leads to "Out of Memory" error (the application eats > all the free memory up to 3.5 Gb , and then fails) > DELETE FROM differential_parts_temp WHERE > plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43' > In order to do a DELETE, SQLite first identifies every row that needs deleting. It remembers the rowid of each such row in memory. Normally this works fine, but it can give problems when you are trying to delete 150M rows, apparently. One possible work-around: CREATE TABLE new_dpt AS SELECT * FROM differential_parts_temp WHERE plan_id<>bf43c9ae-d681-4f2a-be19-0e0426db2b43'; DROP TABLE differential_parts_temp; ALTER TABLE new_dpt RENAME AS differential_parts_temp; Another possible work-around: DELETE FROM differential_parts_temp WHERE rowid IN (SELECT rowid FROM differential_parts_temp WHERE plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43' LIMIT 1000000); -- repeat the previous statement 150 times, or until sqlite3_changes() returns zero. > > I tried different SQLite clients including the following precompiled > binary: > http://www.sqlite.org/sqlite-shell-win32-x86-3071401.zip > > Why the DELETE statement can eat so much memory? > > The total database size is 20 GBytes. > SQLite Expert shows the following DB properties: > auto_vacuum=none > automatic_index=on > cache_size=2000 > case_sensitive_like=off > collation_list=[NOCASE], [RTRIM], [BINARY] > count_changes=off > default_cache_size=2000 > empty_result_callbacks=off > encoding=UTF-8 > foreign_keys=on > freelist_count=0 > full_column_names=off > fullfsync=off > journal_mode=delete > journal_size_limit=-1 > legacy_file_format=off > locking_mode=normal > max_page_count=1073741823 > page_count=20719252 > page_size=1024 > read_uncommitted=off > recursive_triggers=off > reverse_unordered_selects=off > schema_version=27 > secure_delete=off > short_column_names=on > synchronous=full > temp_store=default > temp_store_directory="" > user_version=0 > wal_autocheckpoint=1000 > > Thanks, > IP > _______________________________________________ > 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
