Re: [sqlite] hoe to create index to a big table - resolution
Hi Thanks Gerd and Dan and Ryan. I received answers that helped me understand and solve my problem by redirecting temp data to a directory rather than in-memory. I tried Dan's proposal to SETENV TMPDIR and afterwards I could successfully create index. I will try Gerd's pragma proposal as well Thanks Rinat -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of GB Sent: Monday, June 30, 2014 9:15 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] hoe to create index to a big table? Do you possibly have redirected temporary storage to memory, either by compile switch or #pragma temp_store? If so, try explicitly setting #pragma temp_store=1, this will force temporary data to be stored on disk. Gerd. Hadashi, Rinat schrieb am 30.06.2014 10:30: > I work with a very big table, on Linux. > I fail to create index getting the following error: > SQL error near line 1: database or disk is full > > Any advice? > > Rinat Hadashi > > > - > Intel Israel (74) Limited > > This e-mail and any attachments may contain confidential material for > the sole use of the intended recipient(s). Any review or distribution > by others is strictly prohibited. If you are not the intended > recipient, please contact the sender and delete all copies. > ___ > 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 - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] hoe to create index to a big table?
Do you possibly have redirected temporary storage to memory, either by compile switch or #pragma temp_store? If so, try explicitly setting #pragma temp_store=1, this will force temporary data to be stored on disk. Gerd. Hadashi, Rinat schrieb am 30.06.2014 10:30: I work with a very big table, on Linux. I fail to create index getting the following error: SQL error near line 1: database or disk is full Any advice? Rinat Hadashi - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ 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
Re: [sqlite] hoe to create index to a big table?
On 2014/06/30 10:30, Hadashi, Rinat wrote: I work with a very big table, on Linux. I fail to create index getting the following error: SQL error near line 1: database or disk is full Any advice? Hi Rinat - I was going to simply say "Get a bigger harddrive?" with a smile attached. Obviously you have already established that your Hard drive is in fact not full, so let me try a more useful approach When you create a new Index it requires a lot of extra space, both for the Index itself and the process of making the Index with sorting, possibly uniqueness checking and the like. We cannot tell you exactly how much memory is needed because nobody knows since it depends very much on the data shape and size in the table and column for which you are making an Index - all I can say with reasonable certainty is that it typically requires a lot of space - very much in proportion to the size of the full DB, but the exact proportion depends on the data. To elaborate, it is possible for an index and process of creating an index to require as little as 10% of the full DB size (typically for a table where the data is very wide, not many rows but lots of columns with lots of fat data), and it might require almost double the size of the original DB (on top of the actual DB's filesize) to create and store the Index initially if the data is thin, 2 or 3 columns containing millions of rows with little data per row, such as a logger. if however you have more than double the free space on your drive (let's say 1GB data file and you have 2GB+ free space) then I would start to look elsewhere for a problem. It is hard to conceive an Index creation using more than that, I think a hard upper limit % do exist but maybe someone else here might know exactly as I don't. In most flavours of Linux though, you can just "grow" a volume onto some other attached drive and then release space again afterwards - which might solve your problem. (Backup data before attempting this) - a discussion on that is probably not suitable to this forum but a simple google will get you all kinds of instructions in this regard. Cheers! Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] hoe to create index to a big table?
On 06/30/2014 03:30 PM, Hadashi, Rinat wrote: I work with a very big table, on Linux. I fail to create index getting the following error: SQL error near line 1: database or disk is full A CREATE INDEX on a large table uses temporary files to sort the data before creating the actual index b-tree. So even if you have enough space in the file-system that the database is stored in, you might still be running out of temp space. The maximum amount of temp space required by a CREATE INDEX is roughly twice the size of the final index on disk. To set the directory used for temporary files on Linux, set the TMPDIR (or SQLITE_TMPDIR) environment variable. Dan. Any advice? Rinat Hadashi - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ 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
[sqlite] hoe to create index to a big table?
I work with a very big table, on Linux. I fail to create index getting the following error: SQL error near line 1: database or disk is full Any advice? Rinat Hadashi - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
