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