Re: [sqlite] possible integrity problem

2017-01-15 Thread Kevin O'Gorman 
On Sat, Jan 14, 2017 at 5:04 PM, Simon Slavin  wrote:

>
> On 15 Jan 2017, at 1:01am, Kevin O'Gorman  wrote:
>
> > Update: the integrity check said "ok" after about 1/2 hour.
> > the record count now takes about 4 seconds -- maybe I remembered wrong
> and
> > it always took this long, but I wasn't stopping it until it had hung for
> > several minutes.
>
> What you describe actually sounds more like a hardware problem.  You had a
> 'sticky' disk, affecting at least some of the sectors in which that
> database is stored, which has now sorted itself out.  But sometime in the
> future it may become sticky again.  If you have some sort of disk checking
> software you might like to try it.
>
> Given your 5 indexes, 30 minutes to check an 11GB file is completely
> reasonable.  Don’t worry about that.
>
> Good luck with it.
>

It turns out you're right about the time for a check.  However, I don't buy
the "sticky disk" idea.  I could copy the files just fine.  I could create
a new file of the same size just fine.  But sqlite3 was stuck.  How does
that happen?  I don't know, and my imagination is stuck.


-- 
word of the year: *kakistocracy*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible integrity problem

2017-01-14 Thread Richard Hipp 
On 1/14/17, Simon Slavin  wrote:
>
> Given your 5 indexes, 30 minutes to check an 11GB file is completely
> reasonable.  Don’t worry about that.
>

PRAGMA integrity_check looks into a lot of detail - such as verifying
that every entry in the table has the correct corresponding entry in
each index.  That takes time.  If you run instead "PRAGMA quick_check"
SQLite checks each table and index separately, but does not
cross-check them for consistency, which can be much much faster,
especially on a large database.  Try running "PRAGMA quick_check" and
see if it isn't done in 30 seconds instead of 30 minutes.

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

Re: [sqlite] possible integrity problem

2017-01-14 Thread Simon Slavin 

On 15 Jan 2017, at 1:01am, Kevin O'Gorman  wrote:

> Update: the integrity check said "ok" after about 1/2 hour.
> the record count now takes about 4 seconds -- maybe I remembered wrong and
> it always took this long, but I wasn't stopping it until it had hung for
> several minutes.

What you describe actually sounds more like a hardware problem.  You had a 
'sticky' disk, affecting at least some of the sectors in which that database is 
stored, which has now sorted itself out.  But sometime in the future it may 
become sticky again.  If you have some sort of disk checking software you might 
like to try it.

Given your 5 indexes, 30 minutes to check an 11GB file is completely 
reasonable.  Don’t worry about that.

Good luck with it.

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

Re: [sqlite] possible integrity problem

2017-01-14 Thread Kevin O'Gorman 
Update: the integrity check said "ok" after about 1/2 hour.
the record count now takes about 4 seconds -- maybe I remembered wrong and
it always took this long, but I wasn't stopping it until it had hung for
several minutes.
Color me baffled.

On Sat, Jan 14, 2017 at 4:49 PM, Kevin O'Gorman 
wrote:

> I've got a database that has acted strangely from time to time.  Or
> actually a series of them, since I erase and build from scratch sometimes,
> as I'm just starting this project.
>
> Anyway, the latest is that the DB is about 11 GB.  It's pretty simple,
> just 2 main tables and maybe 5 indexes, no foreign keys, triggers, or much
> of anything else.  Suddenly just about anything I do seems to hang.
>
> In particular SELECT COUNT(*) FROM pos, which used to take under a
> second.  And I haven't make any changes to the DB since then.  This is true
> even if I access a write-protected copy I made some time ago.
>
> That includes PRAGMA integrity_check, which I started about 20 minutes
> ago.  It's the first time I've tried it so I don't know how long it should
> take, but copying the whole database takes under 3 minutes.
>
> I can interrupt the process with control-C, but cannot make progress.
> About the only thing that seems to be working is in sqlite3 I can ask for
> the schema.
>
> All of this is sqlite on Xubuntu Linux in python 3.5.2, and with sqlite3
> command-line.  If I reboot and try again, things still hang.  There are no
> journals hanging around.
>
> My first question: how long should I expect PRAGMA integrity-check to take?
>
> --
> word of the year: *kakistocracy*
>



-- 
word of the year: *kakistocracy*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users