Re: [sqlite] possible integrity problem

[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

[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

[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

[sqlite] possible integrity problem

[Kevin O'Gorman]

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*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert into with Id...

[Kees Nuyt]

Dear Héctor, 

On Fri, 13 Jan 2017 21:38:04 -0500, "hfiandor"
 wrote:

> I have implemented the lectura of a csv file in my program as
> "import". I have followed yours instructions (the last one and
> others previous) . Thanks for your instructions.
> 
> The program works fine with csv files of one or two rows, but
> when I try to read a file with 9 rows, it fails.

In what way does it fail?
Does it fail silently or are there any error messages?
After the failure, are there any rows inserted, or none at all?
Does the program check and interpret the status of each sqlite
API call?
 
> I have reviewed the routines and not found the errors.
> 
> Please, I suspect that I have missed something. If you have
> any suggestion, I will appreciate very much.
> 
> If you need the procedure "import" I can send to you.

That depends on the answers to my questions above.

> Thanks in advance,
> 
> Ing. Héctor F. Fiandor Rosario

Hope this helps,

-- 
Regards,
Kees Nuyt

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

Re: [sqlite] Multiple sessions, page sizes and cache

[Richard Hipp]

On 1/13/17, Kim Gräsman  wrote:
>
> In an effort to reduce memory usage/fragmentation, we're trying to
> configure SQLite to allocate as much memory as necessary up-front
> (based on the excellent information in
> https://www.sqlite.org/malloc.html).
>

The easiest way to do this is to compile with SQLITE_ENABLE_MEMSYS5
then give SQLite 10 or 20MB of memory to use at start-time by invoking

   sqlite3_config(SQLITE_CONFIG_HEAP, malloc(1000), 1000, 64);

or similar.  The page-cache and scratch memory will both be
automatically served from this pool if you take no further actions,
and this usually works just fine.  Use sqlite3_memory_highwater(0) to
see how close you are getting to memory exhaustion so that you can
judge if 10MB is too little or too much and make appropriate changes.

Note that you cannot increase or decrease the amount of memory
available to memsys5 at runtime, except by completely closing all
SQLite database connections and interfaces, running sqlite3_shutdown()
and then redoing the sqlite3_config() call above with the new memory
size.

-- 
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] does integrity check ever modify the db file?

[Richard Damon]

On 1/13/17 2:51 PM, Adam Smith wrote:

Hey all,
   can 'pragma integrity_check' ever modify the file? For instance in case
of a journal file laying around (which was journal file of the same schema
db but a bit different data)?

The following is what I think happened:

a.db and b.db are two sqlite dbs of size ~5mb which have same schema and
little difference in data.
the process writing to a.db is terminated or power loss or something so
a-journal.db left behind.

ls

a.db
a-journal.db
b.db

sqlite3 b.db 'pragma integrity_check'# b.db is healthy

ok

rm a.db
mv b.db a.db  # rename b.db as a.db
sqlite3 a.db 'pragma integrity_check'   # this is I think when integrity

check modifies a.db and corrupts it
failure

ls

a.db

sqlite3 a.db 'pragma integrity_check'

failure...


a.db is corrupted

Is this possible? If so how do we delete all temp files like -journal file?
Thanks in advance


That is (close to) one of the listed ways to corrupt a database file.

While sqlite mostly keeps all the data in a single file, there are 
situations where auxiliary files are temporarily created, and those MUST 
be kept related.


That says when you bypass sqlite and directly manipulate the database 
file, you need to understand the temporary auxiliary files and process 
those too, or make sure that you only are working with files that are in 
a clean state, so it is a single file.


--
Richard Damon

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

Re: [sqlite] Multiple sessions, page sizes and cache

[Simon Slavin]

On 14 Jan 2017, at 8:54am, Clemens Ladisch  wrote:

> If your use case is more complex than the simple mechanism offered by
> SQLITE_CONFIG_PAGECACHE, consider using SQLITE_CONFIG_PCACHE2 (or
> SQLITE_CONFIG_MALLOC).

However, the improvement in time provided by this may not be as great as you 
think.  Before investing lots of time in building special page-size-handling 
into your program, do some tests in a demo application running on your 
hardware.  You can spend a forty hours learning all about the internals of 
SQLite just to end up with a speed increase of 1%.  And then you have a ton of 
complicated low-level code to maintain.

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

Re: [sqlite] Multiple sessions, page sizes and cache

[Kim Gräsman]

Hi Clemens,

Thanks for your help!

On Sat, Jan 14, 2017 at 9:54 AM, Clemens Ladisch  wrote:
> Kim Gräsman wrote:
>> I would expect SQLite to allocate a page cache per session.
>
> There is typically one page cache instance per connection.
> (A connection is what you get from sqlite3_open*(); a session would be
> what you get from sqlite3session_create().)

Oh, I thought I'd seen 'session' somewhere, so went with that. Sorry
about the confusion.

>> So if we want to use SQLITE_CONFIG_PAGECACHE to provide a
>> preallocated buffer for the page cache, it looks like we have to
>> provide it for the worst case, i.e. max(page size) * sum(cache
>> requirements for all sessions). Is that the case?
>
> Yes; the SQLITE_CONFIG_PAGECACHE memory is used by all cache instances.
>
>> is SQLITE_CONFIG_PAGECACHE used to specify an arena for page caches,
>> or a buffer for pages between all sessions?
>
> A page cache instance is a list of pages; memory for each page is
> allocated individually.

... from the buffer provided to SQLITE_CONFIG_PAGECACHE, right?

Great, that confirms our suspicion.

>> Since we have different page sizes (512 bytes and 4K respectively) I'm
>> worried we'll have lots of dead space if SQLITE_CONFIG_PAGECACHE is
>> set up for 4K pages and requests for 512-byte pages are served from
>> there directly.
>
> If your use case is more complex than the simple mechanism offered by
> SQLITE_CONFIG_PAGECACHE, consider using SQLITE_CONFIG_PCACHE2 (or
> SQLITE_CONFIG_MALLOC).

As for SQLITE_CONFIG_MALLOC, we were considering using that to
configure a fixed-size arena for everything not covered by scratch +
page cache. Not sure if it's even necessary.

SQLITE_CONFIG_PCACHE2 looks comparatively difficult to get right, but
I guess it would allow us to keep two arenas, one for 512-byte pages
and one for 4K pages.

Are these reasonable approaches?

Also, having thought some more about this... The places where the docs
warn that a page cache instance will fall back on sqlite3_malloc -- if
the size of SQLITE_CONFIG_PAGECACHE is configured smaller than the
cache sizes actually requested by connections, will every excess page
allocation hit sqlite3_malloc?

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

Re: [sqlite] Multiple sessions, page sizes and cache

[Clemens Ladisch]

Kim Gräsman wrote:
> I would expect SQLite to allocate a page cache per session.

There is typically one page cache instance per connection.
(A connection is what you get from sqlite3_open*(); a session would be
what you get from sqlite3session_create().)

> So if we want to use SQLITE_CONFIG_PAGECACHE to provide a
> preallocated buffer for the page cache, it looks like we have to
> provide it for the worst case, i.e. max(page size) * sum(cache
> requirements for all sessions). Is that the case?

Yes; the SQLITE_CONFIG_PAGECACHE memory is used by all cache instances.

> is SQLITE_CONFIG_PAGECACHE used to specify an arena for page caches,
> or a buffer for pages between all sessions?

A page cache instance is a list of pages; memory for each page is
allocated individually.

> Since we have different page sizes (512 bytes and 4K respectively) I'm
> worried we'll have lots of dead space if SQLITE_CONFIG_PAGECACHE is
> set up for 4K pages and requests for 512-byte pages are served from
> there directly.

If your use case is more complex than the simple mechanism offered by
SQLITE_CONFIG_PAGECACHE, consider using SQLITE_CONFIG_PCACHE2 (or
SQLITE_CONFIG_MALLOC).


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