Re: [sqlite] Bug in CREATE INDEX

[James K. Lowden]

On Mon, 8 Aug 2016 10:48:58 -0700
"Kevin O'Gorman"  wrote:

> Very cool.  But 4?  I will be running this on machines with 8 and 16
> cores.  Does going beyond 4 not help much?

Four doesn't seem like a bad starting point.  

I don't have any information specific to SQLite, but in general adding
cores to a sort helps only until I/O bandwidth is saturated (or RAM is
exhausted).  

And there's always the possibility the system may have other things to
do besides sorting.  

--jkl

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in CREATE INDEX

[Dan Kennedy]

On 08/09/2016 12:48 AM, Kevin O'Gorman wrote:

On Sun, Aug 7, 2016 at 11:11 PM, Dan Kennedy  wrote:


On 08/08/2016 02:03 AM, Dominique Pellé wrote:


Kevin O'Gorman wrote:

CREATE INDEX has two problems:

1) poor default location of temporary storage.
2) gets wedged on very large indexes.

I'm using the sqlite that came with Xubuntu 14.04, I think it's version
3.8.2.


SQLite-3.8.2 is old (Dec 2013). It's better to download and
compile SQLite yourself.

There has been several optimizations since that release.
In particular, looking at release notes at
https://sqlite.org/changes.html the following
improvement which may be relevant for your issue:

=== BEGIN QUOTE https://sqlite.org/changes.html ===
3.8.10:

Performance improvements for ORDER BY, VACUUM,
CREATE INDEX, PRAGMA integrity_check, and
PRAGMA quick_check.
=== END QUOTE ===


3.8.7 introduced the multi-threaded sorter too. So with a more recent
version of SQLite,

   PRAGMA threads = 4

might help this case.

   https://sqlite.org/pragma.html#pragma_threads

Dan.


Very cool.  But 4?  I will be running this on machines with 8 and 16
cores.  Does going beyond 4 not help much?


I don't know. If you have 8 or 16 cores it very well might.

Dan.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in CREATE INDEX

[Kevin O'Gorman]

On Sun, Aug 7, 2016 at 11:11 PM, Dan Kennedy  wrote:

> On 08/08/2016 02:03 AM, Dominique Pellé wrote:
>
>> Kevin O'Gorman wrote:
>>
>> CREATE INDEX has two problems:
>>> 1) poor default location of temporary storage.
>>> 2) gets wedged on very large indexes.
>>>
>>> I'm using the sqlite that came with Xubuntu 14.04, I think it's version
>>> 3.8.2.
>>>
>> SQLite-3.8.2 is old (Dec 2013). It's better to download and
>> compile SQLite yourself.
>>
>> There has been several optimizations since that release.
>> In particular, looking at release notes at
>> https://sqlite.org/changes.html the following
>> improvement which may be relevant for your issue:
>>
>> === BEGIN QUOTE https://sqlite.org/changes.html ===
>> 3.8.10:
>>
>> Performance improvements for ORDER BY, VACUUM,
>> CREATE INDEX, PRAGMA integrity_check, and
>> PRAGMA quick_check.
>> === END QUOTE ===
>>
>
> 3.8.7 introduced the multi-threaded sorter too. So with a more recent
> version of SQLite,
>
>   PRAGMA threads = 4
>
> might help this case.
>
>   https://sqlite.org/pragma.html#pragma_threads
>
> Dan.
>

Very cool.  But 4?  I will be running this on machines with 8 and 16
cores.  Does going beyond 4 not help much?


-- 
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in CREATE INDEX

[Kevin O'Gorman]

On Mon, Aug 8, 2016 at 2:41 AM, Philip Newton 
wrote:

> On 7 August 2016 at 22:37, Kevin O'Gorman  wrote:
> > I use the LTS (long-term support) version of Ubuntu, and like not having
> to
> > keep up with all the latest.  My current 14.04 is at end-of-life
>
> LTS are supported for 5 years; your 14.04 is good till April 2019.
>
> Ph.
>

I guess I'm not paying attention -- that's the point, I don't want to have
to -- and just consider it end-of-life when it starts bugging me twice a
day about the next LTS.  I'm planning to install 16.04.1 into the partition
with the even older 12.04 today.

-- 
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in CREATE INDEX

[Philip Newton]

On 7 August 2016 at 22:37, Kevin O'Gorman  wrote:
> I use the LTS (long-term support) version of Ubuntu, and like not having to
> keep up with all the latest.  My current 14.04 is at end-of-life

LTS are supported for 5 years; your 14.04 is good till April 2019.

Ph.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in CREATE INDEX

[Stephan Mueller]

Kevin asks:
" Does anybody know where the actual defaults and controlling environment
" variables are documented, by operating system?  Or are they?

I believe Section 5.0. near the end of https://www.sqlite.org/tempfiles.html 
describes what you're looking for.

thanks,
stephan();

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in CREATE INDEX

[Dan Kennedy]

On 08/08/2016 02:03 AM, Dominique Pellé wrote:

Kevin O'Gorman wrote:


CREATE INDEX has two problems:
1) poor default location of temporary storage.
2) gets wedged on very large indexes.

I'm using the sqlite that came with Xubuntu 14.04, I think it's version
3.8.2.

SQLite-3.8.2 is old (Dec 2013). It's better to download and
compile SQLite yourself.

There has been several optimizations since that release.
In particular, looking at release notes at
https://sqlite.org/changes.html the following
improvement which may be relevant for your issue:

=== BEGIN QUOTE https://sqlite.org/changes.html ===
3.8.10:

Performance improvements for ORDER BY, VACUUM,
CREATE INDEX, PRAGMA integrity_check, and
PRAGMA quick_check.
=== END QUOTE ===


3.8.7 introduced the multi-threaded sorter too. So with a more recent 
version of SQLite,


  PRAGMA threads = 4

might help this case.

  https://sqlite.org/pragma.html#pragma_threads

Dan.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in CREATE INDEX

[Kevin O'Gorman]

On Sun, Aug 7, 2016 at 12:03 PM, Dominique Pellé 
wrote:

> Kevin O'Gorman wrote:
>
> > CREATE INDEX has two problems:
> > 1) poor default location of temporary storage.
> > 2) gets wedged on very large indexes.
> >
> > I'm using the sqlite that came with Xubuntu 14.04, I think it's version
> > 3.8.2.
>
> SQLite-3.8.2 is old (Dec 2013). It's better to download and
> compile SQLite yourself.
>
> There has been several optimizations since that release.
> In particular, looking at release notes at
> https://sqlite.org/changes.html the following
> improvement which may be relevant for your issue:
>
> === BEGIN QUOTE https://sqlite.org/changes.html ===
> 3.8.10:
>
> Performance improvements for ORDER BY, VACUUM,
> CREATE INDEX, PRAGMA integrity_check, and
> PRAGMA quick_check.
> === END QUOTE ===
>
> Regards
> Dominique
>
>
I use the LTS (long-term support) version of Ubuntu, and like not having to
keep up with all the latest.  My current 14.04 is at end-of-life, and I'll
upgrade to 16.04 soon -- probably this weekend -- and get sqlite 3.8.17
automatically.

I'm a hobbyist going solo with a lot of interests.  This means I have to
accept the concept of "good enough" or pare down my interests to those I
can devote admin time to on a regular basis. Even more so when I haven't
really decided to adopt the package yet.

Works for me, but YMMV.



-- 
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in CREATE INDEX

[Dominique Pellé]

Kevin O'Gorman wrote:

> CREATE INDEX has two problems:
> 1) poor default location of temporary storage.
> 2) gets wedged on very large indexes.
>
> I'm using the sqlite that came with Xubuntu 14.04, I think it's version
> 3.8.2.

SQLite-3.8.2 is old (Dec 2013). It's better to download and
compile SQLite yourself.

There has been several optimizations since that release.
In particular, looking at release notes at
https://sqlite.org/changes.html the following
improvement which may be relevant for your issue:

=== BEGIN QUOTE https://sqlite.org/changes.html ===
3.8.10:

Performance improvements for ORDER BY, VACUUM,
CREATE INDEX, PRAGMA integrity_check, and
PRAGMA quick_check.
=== END QUOTE ===

Regards
Dominique
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in CREATE INDEX

[Olivier Mascia]

> Does anybody know where the actual defaults and controlling environment
> variables are documented, by operating system? 

https://www.sqlite.org/tempfiles.html

-- 
Meilleures salutations, Met vriendelijke groeten,  Best Regards,
Olivier Mascia (from mobile device), integral.be/om

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in CREATE INDEX

[Kevin O'Gorman]

RETRACTED: Not wedged, just somewhat slower than I expected.  I was
expecting about an hour, and it takes 6.  On reflection, it's okay.  My
ideas about what's going on were wrong too -- anonymous files are used, but
only two of them at a time.

I still think it is using a poor choice of default directory for temporary
files.  Is it /var/tmp instead of the more usual /tmp?  That's the only
other choice that would not be just plain wrong, and maybe it was chosen
thinking that it's likely to be on a bigger partition.  Maybe, sometimes.
But a lot of the time, /var/tmp is on the same partition as /tmp, or just
ignored because the sort utility defaults to /tmp and often that's the
biggest user of big temporary files.  That's the reason that I've placed
/tmp on its own huge partition (3 TB) and left /var/tmp alone.  The big
reason to use /var/tmp is that the files there are not deleted
automatically -- the standard says they're to be preserved -- but anonymous
files aren't ever preserved -- they've disappeared from the file system by
definition.

In all, not a big issue since $TMPDIR is honored, but I don't see that it's
documented.  I just guessed it since the sort utility honors it and I
thought it was possible sort was being used under the covers.  It's not,
but it all worked out okay.

Does anybody know where the actual defaults and controlling environment
variables are documented, by operating system?  Or are they?

On Fri, Aug 5, 2016 at 12:36 PM, Kevin O'Gorman 
wrote:

> CREATE INDEX has two problems:
> 1) poor default location of temporary storage.
> 2) gets wedged on very large indexes.
>
> I'm using the sqlite that came with Xubuntu 14.04, I think it's version
> 3.8.2.
>
> I created a table, and used .import to populate it with records, about 1.4
> billion of them.  The resulting database is 232 GB.  All seemed well.
>
> I then went to CREATE INDEX on two fields, one CHAR(64) and the other an
> INT.  This initially died quickly, but I determined that it was because it
> was attempting to use my smallest partition for workspace.  It was in my
> root partition, but not in /tmp which is separate.  I submit that /tmp
> would be the natural choice, as it is what the sort command uses by
> default.  That's problem 1.
>
> Fortunately, it honors the $TMPDIR environment variable, but gets wedged
> after a bit.  I have an idea why.  While it was still actively adding
> space, there were no filenames showing, so I infer that it's using
> "anonymous" files, perhaps by calling tmpfile().  This could be bad news,
> as anonymous files have to be held open, but there are limits on how many
> open files any process is allowed.  Unless your merge process starts with
> at least 1.4 million keys in each buffer, this is going to fail on my
> data.  I suggest using tempnam() and closing the files when you're not
> actively using them.
>
> --
> #define QUESTION ((bb) || (!bb)) /* Shakespeare */
>



-- 
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug in CREATE INDEX

[Kevin O'Gorman]

CREATE INDEX has two problems:
1) poor default location of temporary storage.
2) gets wedged on very large indexes.

I'm using the sqlite that came with Xubuntu 14.04, I think it's version
3.8.2.

I created a table, and used .import to populate it with records, about 1.4
billion of them.  The resulting database is 232 GB.  All seemed well.

I then went to CREATE INDEX on two fields, one CHAR(64) and the other an
INT.  This initially died quickly, but I determined that it was because it
was attempting to use my smallest partition for workspace.  It was in my
root partition, but not in /tmp which is separate.  I submit that /tmp
would be the natural choice, as it is what the sort command uses by
default.  That's problem 1.

Fortunately, it honors the $TMPDIR environment variable, but gets wedged
after a bit.  I have an idea why.  While it was still actively adding
space, there were no filenames showing, so I infer that it's using
"anonymous" files, perhaps by calling tmpfile().  This could be bad news,
as anonymous files have to be held open, but there are limits on how many
open files any process is allowed.  Unless your merge process starts with
at least 1.4 million keys in each buffer, this is going to fail on my
data.  I suggest using tempnam() and closing the files when you're not
actively using them.

-- 
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users