How much free disk space do you have? Your temp tables might be exceeding capacity.
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ammon_lymphater Sent: Thursday, January 24, 2013 1:41 PM To: sqlite-users@sqlite.org Subject: [sqlite] bug report: out of memory error for most operations on a large database Summary: except for select count(*) , all operations on a large table (500m rows/80gb) result in out-of-memory error Details: a. Using SQLite3 ver 3.7.15.2, Windows 7 64 bit AMD(but the error also in 3.6.xx and on other Windows platforms) b. Created a table (schema attached), imported data from a csv file using sqlite3 (no problems) c. "select Name5, count(*) from LargeData group by name5 order by name5 results" in Error: out of memory (the cardinality of Name5 is 12) d. "sqlite> select count(*) from StorageHourlyBySIDCL2;" gives 587608348, as it should e. The above independent of cache_size (from 0.3 to 1.5gb); happens for page_size 32kb and 64kb (others not tested) (personally not urgent for me - just testing the limits - but may be useful for others) ==== table schema & sqlite3_analyzer output CREATE TABLE largedata ( name1 smalldatetime , Name2 uniqueidentifier , Name3 varchar (16) , Name4 varchar (8) , Name5 varchar (80) , Name6 real , Name7 real , Name8 real , Name9 real , Name10 real , Name11 real , Name12 real , Name13 real , Name14 smallint , Name15 tinyint , Name16 tinyint , Name17 smalldatetime , Name18 real , Name19 tinyint ); -- SQLITE3_ANALYZER output /** Disk-Space Utilization Report For h:\temp\convert\import2.db Page size in bytes.................... 32768 Pages in the whole file (measured).... 2578119 Pages in the whole file (calculated).. 2578118 Pages that store data................. 2578118 100.000% Pages on the freelist (per header).... 0 0.0% Pages on the freelist (calculated).... 1 0.0% Pages of auto-vacuum overhead......... 0 0.0% Number of tables in the database...... 11 Number of indices..................... 0 Number of named indices............... 0 Automatically generated indices....... 0 Size of the file in bytes............. 84479803392 Bytes of user payload stored.......... 79293861071 93.9% *** Page counts for all tables with their indices ******************** LargeData................. 2578108 100.000% smalldata............................ 1 0.0% (zero-length tables omitted) *** All tables ******************************************************* Percentage of total database.......... 100.000% Number of entries..................... 587608358 Bytes of storage consumed............. 84479770624 Bytes of payload...................... 79293871126 93.9% Average payload per entry............. 134.94 Average unused bytes per entry........ 0.32 Average fanout........................ 2716.00 Fragmentation......................... 0.11% Maximum payload per entry............. 1933 Entries that use overflow............. 0 0.0% Index pages used...................... 949 Primary pages used.................... 2577169 Overflow pages used................... 0 Total pages used...................... 2578118 Unused bytes on index pages........... 3904523 12.6% Unused bytes on primary pages......... 184122910 0.22% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 188027433 0.22% --$ removing unused tables *** Table STORAGEHOURLYBYSIDCL2 ************************************** Percentage of total database.......... 100.000% Number of entries..................... 587608348 Bytes of storage consumed............. 84479442944 Bytes of payload...................... 79293861071 93.9% Average payload per entry............. 134.94 Average unused bytes per entry........ 0.32 Average fanout........................ 2716.00 Fragmentation......................... 0.11% Maximum payload per entry............. 183 Entries that use overflow............. 0 0.0% Index pages used...................... 949 Primary pages used.................... 2577159 Overflow pages used................... 0 Total pages used...................... 2578108 Unused bytes on index pages........... 3904523 12.6% Unused bytes on primary pages......... 183805515 0.22% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 187710038 0.22% *** Table SQLITE_MASTER ********************************************** Percentage of total database.......... 0.0% Number of entries..................... 10 Bytes of storage consumed............. 32768 Bytes of payload...................... 10055 30.7% Average payload per entry............. 1005.50 Average unused bytes per entry........ 2255.50 Maximum payload per entry............. 1933 Entries that use overflow............. 0 0.0% Primary pages used.................... 1 Overflow pages used................... 0 Total pages used...................... 1 Unused bytes on primary pages......... 22555 68.8% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 22555 68.8% *** Definitions ****************************************************** --$ removed definitions ********************************************************************** The entire text of this report can be sourced into any SQL database engine for further analysis. All of the text above is an SQL comment. The data used to generate this report follows: */ BEGIN; CREATE TABLE space_used( name clob, -- Name of a table or index in the database file tblname clob, -- Name of associated table is_index boolean, -- TRUE if it is an index, false for a table nentry int, -- Number of entries in the BTree leaf_entries int, -- Number of leaf entries payload int, -- Total amount of data stored in this table or index ovfl_payload int, -- Total amount of data stored on overflow pages ovfl_cnt int, -- Number of entries that use overflow mx_payload int, -- Maximum payload size int_pages int, -- Number of interior pages used leaf_pages int, -- Number of leaf pages used ovfl_pages int, -- Number of overflow pages used int_unused int, -- Number of unused bytes on interior pages leaf_unused int, -- Number of unused bytes on primary pages ovfl_unused int, -- Number of unused bytes on overflow pages gap_cnt int, -- Number of gaps in the page layout compressed_size int -- Total bytes stored on disk ); INSERT INTO space_used VALUES('sqlite_master','sqlite_master',0,10,10,10055,0,0,1933,0,1,0,0,22555, 0,0,32768); --$ removed unused tables INSERT INTO space_used VALUES('LargeData','LargeData',0,590185506,587608348,79293861071,0,0,183,949 ,2577159,0,3904523,183805515,0,2844,84479442944); COMMIT; _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users