Re: [sqlite] Different temp_store_directory settings okay?

[Brian Dantes]

D. Richard Hipp wrote:

> It is OK for different processes to use different temp_store_directory

> settings.  The temp_store_directory is only used for TEMP tables.  It

> does not play a roll in the persistent state of the database.

I was worried because of this statement in the docs:

"When the temp_store_directory setting is changed, all existing
temporary tables, indices, triggers, and viewers are immediately
deleted."

If I have one application using the default temp_store_directory, say
/tmp,
and another that sets it explicitly to something else, does that second
application blow away all the temporary data for the first application
sitting in /tmp?

> > I am experiencing physical DB corruption and am searching
> > for possible explanations.
> Is the corruption repeatable?  What version of SQLite are you running?

No, unfortunately. 3.6.14.2.

-Brian Dantes

> -----Original Message-
> From: Brian Dantes
> Sent: Thursday, March 04, 2010 1:10 PM
> To: 'sqlite-users@sqlite.org'
> Subject: Different temp_store_directory settings okay?
> 
> Is it okay for two different *processes* using
> independent connections to the same database two
> have different values for the temp_store_directory
> pragma?
> 
> The docs make it clear this is not okay for two
> *threads* in the same process -- but for two
> processes is not so clear.
> 
> I am experiencing physical DB corruption and am searching
> for possible explanations.
> 
> Brian Dantes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Different temp_store_directory settings okay?

[Brian Dantes]

Is it okay for two different *processes* using
independent connections to the same database two
have different values for the temp_store_directory
pragma?

The docs make it clear this is not okay for two
*threads* in the same process -- but for two
processes is not so clear.

I am experiencing physical DB corruption and am searching
for possible explanations.

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

Re: [sqlite] Strange index creation/insertion performance between Windows and Linux 64-bit

[Brian Dantes]

Thanks for the replies.

- All insertions are within a transaction
- Database was originally created on Linux (with a 1K page size)
  and copied to Windows
- Changing the page size to 4K (and vacuuming) lowered the index
  creation time on Windows to 50 seconds and on Linux to 5.5 minutes.
  However, there is still a huge disparity.
- Both Windows and Linux are doing nothing else and have ample memory.
  The disk on Linux is a 1 rpm fast SCSI disk on an HP blade.

Here is the Windows output of the analyzer for the table and its index:

*** Table XXX w/o any indices 

Percentage of total database..   8.1%
Number of entries. 5119477
Bytes of storage consumed. 87928832
Bytes of payload.. 5361732861.0%
Average payload per entry. 10.47
Average unused bytes per entry 0.04
Average fanout 363.00
Fragmentation.   0.81%
Maximum payload per entry. 11
Entries that use overflow. 00.0%
Index pages used.. 59
Primary pages used 21408
Overflow pages used... 0
Total pages used.. 21467
Unused bytes on index pages... 35210   14.6%
Unused bytes on primary pages. 175898   0.20%
Unused bytes on overflow pages 0
Unused bytes on all pages. 211108   0.24%

*** Indices of table XXX *

Percentage of total database..   8.6%
Number of entries. 5119477
Bytes of storage consumed. 93179904
Bytes of payload.. 6894286474.0%
Average payload per entry. 13.47
Average unused bytes per entry 1.68
Fragmentation.  99.14%
Maximum payload per entry. 14
Entries that use overflow. 00.0%
Primary pages used 22749
Overflow pages used... 0
Total pages used.. 22749
Unused bytes on primary pages. 8605625  9.2%
Unused bytes on overflow pages 0
Unused bytes on all pages. 8605625  9.2%


And here is the same output under Linux:

*** Table XXX w/o any indices 

Percentage of total database..   8.1%
Number of entries. 5119477   
Bytes of storage consumed. 87928832  
Bytes of payload.. 5361732861.0% 
Average payload per entry. 10.47 
Average unused bytes per entry 0.04  
Average fanout 363.00
Fragmentation.   0.81%   
Maximum payload per entry. 11
Entries that use overflow. 00.0% 
Index pages used.. 59
Primary pages used 21408 
Overflow pages used... 0 
Total pages used.. 21467 
Unused bytes on index pages... 35210   14.6% 
Unused bytes on primary pages. 175898   0.20% 
Unused bytes on overflow pages 0 
Unused bytes on all pages. 211108   0.24% 

*** Indices of table XXX *

Percentage of total database..   8.6%
Number of entries. 5119477   
Bytes of storage consumed. 93179904  
Bytes of payload.. 6894286474.0% 
Average payload per entry. 13.47 
Average unused bytes per entry 1.68  
Fragmentation.  99.08%   
Maximum payload per entry. 14
Entries that use overflow. 00.0% 
Primary pages used 22749 
Overflow pages used... 0 
Total pages used.. 22749 
Unused bytes on primary pages. 8605625  9.2% 
Unused bytes on overflow pages 0 
Unused bytes on all pages. 8605625  9.2% 



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

[sqlite] Strange index creation/insertion performance between Windows and Linux 64-bit

[Brian Dantes]

I have a largish DB around 1GB in size. There is a table with 5 million rows
in it that has a 3-key index on it. This database file is fragmented -- to
what degree I'm not sure.

Using sqlite 3.6.14, dropping and recreating the index under WinXP Pro (on a
local disk) with no other activity takes about 90 seconds. With the same
database on Red Hat Linux 64-bit with no other activity and a local disk,
the index recreation takes almost 30 *minutes*. The activity is completely
I/O bound. If I vacuum the database, the Windows time drops to 70 seconds,
and the Linux time drops to 7 minutes.

Conversely, with this same table and index starting from empty, if I start
inserting rows with the index in place, on Windows the insertion speed drops
dramatically after about 100-200K rows and takes about 6-7 *hours* to
complete. On Linux 64-bit, the same experiment takes less than an hour to
complete and the insertion speed seems fairly constant.

Can anyone offer any explanations for these huge disparities in sqlite
performance on these two platforms? And why Windows does badly on index
insertions and well on rebuilding it from scratch, and Linux 64-bit has the
exact opposite behavior?

Brian Dantes

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

[sqlite] TOCTOU pager bug in 3.6.14.2

[Brian Dantes]

See Ticket 3883



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

[sqlite] SQLite 3.5.9 bug with journals and file locking

[Brian Dantes]

If a transaction is opened on a DB for which a journal file exists, and
fcntl() returns EACCES or EPERM on the attempt to acquire a write lock on
the DB to replay the journal, SQLite 3.5.9 quietly ignores the journal
without replaying it and continues on. This is a serious bug.

SQLite 3.6.14.2 keeps trying the write lock forever on EACCES or immediately
fails on EPERM, which is the proper behavior.

An example scenario is an abort during a user writing to the DB with access
to do so followed by another user trying to read the DB with no access to
write it.

Even though this appears to be fixed in 3.6.14.2, I thought this should be
documented and perhaps analyzed in the 3.5.9 source to make sure no similar
problems are still in 3.6.14.2.


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

[sqlite] Alignment bug in SQLIte 3.6.14.2

[Brian Dantes]

We've run into an alignment issue in SQLite 3.6.14.2 which only
seems to cause a problem under Solaris Sparc in our testing.

Failure narrowed down to:
src/rowset.c:186
pEntry->v = rowid
pEntry is 0xX4
__alignof(*pEntry) is 8 (because of an i64)

However sizeof(RowSet) is 28 (and 28%8 = 4), and pEntry starts 1 RowSet
after freshly allocated, 8-bytes aligned) memory (see sqlite3RowSetInit)
So it crashes. This is definitely a bug in sqlite.

Suggested patch that seems to work for us:

= sqlite/src/rowset.c 1.1 vs edited =
--- 1.1/sqlite/src/rowset.c2009-05-19 14:07:53 -07:00
+++ edited/sqlite/src/rowset.c
2009-05-26 15:43:56 -07:00
@@ -127,6 +127,7 @@
  */
  RowSet *sqlite3RowSetInit(sqlite3 *db, void *pSpace, unsigned int N){
RowSet *p;
+  int n;
assert( N >= sizeof(*p) );
p = pSpace;
p->pChunk = 0;
@@ -134,8 +135,14 @@
p->pEntry = 0;
p->pLast = 0;
p->pTree = 0;
-  p->pFresh = (struct RowSetEntry*)[1];
-  p->nFresh = (u16)((N - sizeof(*p))/sizeof(struct RowSetEntry));
+  /* Alignment must be a power of 2, and at least equal to
+   __alignof(struct RowSetEntry) */
+  #define MIN_ALIGNMENT 8
+  n = sizeof(*p);
+  /* Round up to next alignment */
+  n = (n - 1) / MIN_ALIGNMENT * MIN_ALIGNMENT + MIN_ALIGNMENT;
+  p->pFresh = (struct RowSetEntry*)((char *)p + n);
+  p->nFresh = (u16)((N - n)/sizeof(struct RowSetEntry));
p->isSorted = 1;
p->iBatch = 0;
return p;


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