Indexing failure
(1) By nirh (nirhadaya) on 2022-05-10 07:14:23 [link] [source]
Hi,
I'm trying to index a huge table (~107M entries) and get the following error:
statement aborts at 18: [CREATE INDEX IF NOT EXISTS my_index ON my_table (columnA);] database or disk is full13
The schema of this table is: CREATE TABLE my_table ( columnA VARCHAR, columnB VARCHAR );
It's not a real disk issue. We already verified that the disk of both the written DB and the Sqlite temp dir have plenty of space.
Do you have any hint what can it be ?
thanks Nir
(2) By Gunter Hick (gunter_hick) on 2022-05-10 08:14:52 in reply to 1 [link] [source]
The error message text is "database or disk is full". You have already determined that the disk is not full. This leaves what as the possible cause? Hint: PRAGMA page_size(); PRAGMA page_count(); PRAGMA max_page_count();
(5) By ddevienne on 2022-05-10 10:29:28 in reply to 2 [link] [source]
Just drop the parens:
C:\Users\ddevienne>sqlite3
...
sqlite> pragma page_size;
4096
sqlite> pragma page_size();
Error: near ")": syntax error
sqlite>
(7) By nirh (nirhadaya) on 2022-05-11 07:10:39 in reply to 5 [source]
We already tried it and it doesn't work. I'm working with the following configuration:
- PRAGMA page_size = 8192
- PRAGMA max_page_count = 2147483646
(8) By Warren Young (wyoung) on 2022-05-11 09:19:15 in reply to 7 [link] [source]
(9) By Gunter Hick (gunter_hick) on 2022-05-11 09:48:07 in reply to 7 [link] [source]
Please show the settings that are actually in effect as opposed to what you think you have specified. Real output is much preferred over accounts of what you think you did. This goes for table and column names too (unless they are company secrets). The page size cannot be changed at whim: it has to be set BEFORE THE DATABASE IS CREATED or BEFORE RUNNING VACCUUM on an existing database. The page count in relation to the max_page count shows how far the database is filled up by the table(s) alone. Also, SELECT COUNT(),SUM(LENGTH(ColumnA)) FROM my_table; should give an indication of the storage requirements for the index, since the index will need to duplicate the values of the key fields. Note that large contents of ColumnA combined with a small page size will lead to low fan-out in the index and more overhead pages in the index. Maybe try populating with 1 million (representative) rows and comparing the actual page_count before and after creating an index to get an estimate of the relative sizes. Then you can multiply the page counts by 107 for a rough indication of the required max_page_count. This assumes that there is only the one table you have shown in the database.
(3) By Ryan Smith (cuz) on 2022-05-10 08:46:50 in reply to 1 [link] [source]
Also, I've seen this in two other cases:
The temporary file growth is inhibited (Make sure your Antivirus software excludes your TEMP path)
At some point I've used a RAM-Drive designated to my TEMP folder, which worked very well, except it had a limited size and caused this same error with large SQLite operations. Either make sure your TEMP path has much space (at least more than double the size of your DB), or set SQLite to use a different volume for its TEMP STORE.
(4) By Stephan Beal (stephan) on 2022-05-10 10:23:50 in reply to 1 [link] [source]
Do you have any hint what can it be ?
Another potential cause is filesystem-specific size limits. e.g. FAT32 is limited to 4GB per file.
(6) By ddevienne on 2022-05-10 10:32:01 in reply to 1 [link] [source]
Recent thread about a similar issue.