Re: [sqlite] Is there any API for counting number of rows in a particular table.

[John Elrick]

On Tue, Jan 31, 2012 at 6:01 AM, Fabio Spadaro wrote:

> Hi
>
> 2012/1/30 Bart Smissaert 
>
> > How do you make it open database files that have an extension other than
> > .slt?
> >
> > RBS
>
>
> Tonight I will see to create a new version with bug fixes.
> Meanwhile you could give me some feedback such as opening a file with any
> extension by entering *.* in the filename of the FileDialog.
>

Common extensions I've seen used for SQLite:

.sqlite
.db
.db3


Cheers,


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

Re: [sqlite] Found it

[John Elrick]

On Thu, Jan 26, 2012 at 12:36 PM, Stephan Beal wrote:

> On Thu, Jan 26, 2012 at 2:53 PM, Mohit Sindhwani  wrote:
>
> > Absolutely!  I come home from work and tune in to this thread, gripped :)
> >
>
> +1 to Mohit and the others who's written similar responses. i rarely follow
> threads which don't directly affect me, but John Elrick's determination and
> stamina have been a treat to watch. IMO he should be made an honorary
> member of the dev team (or at least get a coffee mug).
>
>
Thank you.  You made my day.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Thu, Jan 26, 2012 at 3:29 AM, Jean-Denis MUYS <jdm...@kleegroup.com>wrote:

>
> On 25 janv. 2012, at 22:30, John Elrick wrote:
>
> >
> > I have to say this has been a fascinating learning experience.  It has
> been
> > enlightening reading the SQLite code and beginning to understand its
> inner
> > workings.
> > John
> > ___
>
> What a thriller! I was holding my breath. Did you think about selling the
> rights to this story to a movie studio?


Steven Spielberg just bought the rights.  He outbid James Cameron by a
million.  Should be in theaters Summer 2014.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Wed, Jan 25, 2012 at 4:21 PM, Nico Williams wrote:

> On Wed, Jan 25, 2012 at 3:07 PM, Richard Hipp  wrote:
> > The SQLite byte-code engine was being too conservative and was reparsing
> > the schema in places where it was not strictly necessary.  The fix was to
> > restrict the places where the schema was reparsed to situations that
> really
> > needed it.
>
> This thread has been awesome, gripping reading.  Thanks to both of you
> for sticking with it and figuring it out.
>
>

Thank God we're...

a) early with this release cycle
b) in maintenance mode
c) working for a client with deep pockets who recognizes the need for a
solid code base

or I would have been an utter wreck by now.

I have to say this has been a fascinating learning experience.  It has been
enlightening reading the SQLite code and beginning to understand its inner
workings.  I feel significantly more confident in the solidity of our code
base and am very grateful that we chose SQLite as our database years ago.
 I doubt I would have had this much cooperation from the Chief Scientist
with any other product.

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

Re: [sqlite] Found it

[John Elrick]

On Wed, Jan 25, 2012 at 4:07 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Jan 25, 2012 at 4:01 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > Much better.  I wasn't sure which version to incorporate the changes into
> > (the link is to the source), so I put them into 3.7.9.
> >
> > The total mallocs have dropped to 1.5 million -- twice as high as 3.7.5
> but
> > in line.  I'll have to run some additional performance testing but
> visually
> > the system appears to be a lot closer to expected.
> >
> > What was going on? (My client will want a report on the resolution).
> >
>
> The SQLite byte-code engine was being too conservative and was reparsing
> the schema in places where it was not strictly necessary.  The fix was to
> restrict the places where the schema was reparsed to situations that really
> needed it.
>
> I cannot give more detail without seeing your application, since I still
> don't know exactly what you are doing to trigger the excess reparsing.  The
> patch above comes from a script of my own that I finally managed to put
> together that caused unnecessary schema reparsing.  It took a specific
> combination of events:  A schema change inside of a transaction followed by
> DML statements that had been prepared prior to the schema change.
>
>
My brain missed that.   The sequence I described should have been:

prepare query1
prepare query2
begin transaction
create something -> schema change
query1.step
  - schema invalid so recreate
  - recreation of schema changes the generation
query2.step
  - schema invalid so recreate
  - recreation of schema changes the generation
query1.step
  - schema invalid so recreate
  - recreation of schema changes the generation
query2.step
  - schema invalid so recreate
  - recreation of schema changes the generation
end transaction

...which I believe describes your scenario perfectly.  Odd that the script
didn't replicate it, since it should have had the nested transactions as
well.

Thank you very much for your assistance Richard.  I sincerely appreciate it.

Thanks to everyone else who helped guide me on this investigation.


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

Re: [sqlite] Found it

[John Elrick]

Thanks.

On Wed, Jan 25, 2012 at 4:11 PM, Petite Abeille <petite.abei...@gmail.com>wrote:

>
> On Jan 25, 2012, at 10:09 PM, John Elrick wrote:
>
> > DML?
>
> http://en.wikipedia.org/wiki/Data_Manipulation_Language
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Wed, Jan 25, 2012 at 4:07 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Jan 25, 2012 at 4:01 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > Much better.  I wasn't sure which version to incorporate the changes into
> > (the link is to the source), so I put them into 3.7.9.
> >
> > The total mallocs have dropped to 1.5 million -- twice as high as 3.7.5
> but
> > in line.  I'll have to run some additional performance testing but
> visually
> > the system appears to be a lot closer to expected.
> >
> > What was going on? (My client will want a report on the resolution).
> >
>
> The SQLite byte-code engine was being too conservative and was reparsing
> the schema in places where it was not strictly necessary.  The fix was to
> restrict the places where the schema was reparsed to situations that really
> needed it.
>
> I cannot give more detail without seeing your application, since I still
> don't know exactly what you are doing to trigger the excess reparsing.  The
> patch above comes from a script of my own that I finally managed to put
> together that caused unnecessary schema reparsing.  It took a specific
> combination of events:  A schema change inside of a transaction followed by
> DML statements that had been prepared prior to the schema change.
>
>
DML?  I'm not familiar with that term.  The rest of the description sounds
very familiar.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

Much better.  I wasn't sure which version to incorporate the changes into
(the link is to the source), so I put them into 3.7.9.

The total mallocs have dropped to 1.5 million -- twice as high as 3.7.5 but
in line.  I'll have to run some additional performance testing but visually
the system appears to be a lot closer to expected.

What was going on? (My client will want a report on the resolution).

On Wed, Jan 25, 2012 at 3:44 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Jan 25, 2012 at 3:39 PM, Richard Hipp <d...@sqlite.org> wrote:
>
> > I'm testing a candidate fix for your problem now.  I'll send you a link
> > once the tests finish (assuming they all work).
>
>
> Please try http://www.sqlite.org/src/info/11f68d997d and let me know if it
> solves your problem.  Thanks.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

Richard,

I've found a problem that I don't understand.  Looking at the case
OP_VerifyCookie, I attempted to output pOp->opcode, which I expected to
equal OP_VerifyCookie.  However, it doesn't:

 Schema has changed current state = 'db=0025, pOp->opCode=66785672,
db->aDb[pOp->p1].pSchema=02110AB0,
db->aDb[pOp->p1].pSchema->schema_cookie=27,
db->aDb[pOp->p1].pSchema->iGeneration=63, u.av.iMeta=27, u.av.iGen=63,
pOp->p1=1, pOp->p2=27, pOp->p3=0'

I don't see anything which should be modifying pOp.  Any thoughts?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

Richard,

Is it reasonable to say that, assuming:

1.  The schema has not changed
2.  There is only one connection to the database

that the value of iGeneration should always equal pOp->p3 in
OP_VerifyCookie?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Wed, Jan 25, 2012 at 1:13 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Jan 25, 2012 at 10:18 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> >
> > I do have some more information for you.  I tracking everything which
> could
> > call reset and discovered that all of these are stemming from
> >
> > SQLITE_PRIVATE int sqlite3VdbeHalt(Vdbe *p){
> > 
> >/* Rollback or commit any schema changes that occurred. */
> >if( p->rc!=SQLITE_OK && db->flags_InternChanges ){
> > fenestra_sqlite3_dump_stack_trace("rollback in vdbeHalt");
> >  sqlite3ResetInternalSchema(db, -1);
> >  db->flags = (db->flags | SQLITE_InternChanges);
> >}
> >
> > What is the value of p->rc when these resets occur? Do I understand
> correctly that your application is making a bunch of schema changes, then
> doing other operations, at least one of which fails?
>
>
>
'p->rc=17, db->flags=48100a00, db->flags_InternChanges=200'

"A bunch" is a bit nebulous but essentially yes.  Your description looks
accurate.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

Addendum

On Wed, Jan 25, 2012 at 1:12 PM, John Elrick <john.elr...@fenestra.com>wrote:

> On Wed, Jan 25, 2012 at 9:50 AM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On Wed, Jan 25, 2012 at 9:02 AM, John Elrick <john.elr...@fenestra.com
>> >wrote:
>>
>> >
>> > current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110AB0,
>> > db->aDb[pOp->p1].pSchema->schema_cookie=27,
>> > db->aDb[pOp->p1].pSchema->iGeneration=63, u.av.iMeta=27, u.av.iGen=63,
>> > pOp->p1=1, pOp->p2=27, pOp->p3=0'
>> >
>> > sql = 'update alerter_linksset is_dirty = null '
>> >
>> > Obviously, iGeneration and p3 are out of sync.  So, what exactly is
>> pOp->p3
>> > ?
>> >
>>
>> pOp->p3 is suppose to be the value of iGeneration when the statement was
>> first prepared.  It is set here:
>> http://www.sqlite.org/src/artifact/8e2a4dedad?ln=160-162
>>
>> I'm a little concerned that pOp->p3 is zero when iGeneration is 63.
>>  That's
>> a lot of changes.  When was the statement first prepared?  When the
>> program
>> first starts up?  Or is pOp->p3 getting zeroed somehow?
>
>
> I didn't think of this until now.  The test run starts with no database,
> which means the entire create script must be run.  It is physically
> impossible for pOp->p3 to be legitimately set to a generation of 0.
>
> Something must be zeroing pOp->p3
>


Physically impossible for a SELECT, UPDATE or INSERT prepared statement to
be legitimately set to a generation of 0.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Wed, Jan 25, 2012 at 9:50 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Jan 25, 2012 at 9:02 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> >
> > current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110AB0,
> > db->aDb[pOp->p1].pSchema->schema_cookie=27,
> > db->aDb[pOp->p1].pSchema->iGeneration=63, u.av.iMeta=27, u.av.iGen=63,
> > pOp->p1=1, pOp->p2=27, pOp->p3=0'
> >
> > sql = 'update alerter_linksset is_dirty = null '
> >
> > Obviously, iGeneration and p3 are out of sync.  So, what exactly is
> pOp->p3
> > ?
> >
>
> pOp->p3 is suppose to be the value of iGeneration when the statement was
> first prepared.  It is set here:
> http://www.sqlite.org/src/artifact/8e2a4dedad?ln=160-162
>
> I'm a little concerned that pOp->p3 is zero when iGeneration is 63.  That's
> a lot of changes.  When was the statement first prepared?  When the program
> first starts up?  Or is pOp->p3 getting zeroed somehow?


I didn't think of this until now.  The test run starts with no database,
which means the entire create script must be run.  It is physically
impossible for pOp->p3 to be legitimately set to a generation of 0.

Something must be zeroing pOp->p3
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

Ok.  Possible scenario.  I can't prove it, but somebody tell me if this is
what could be going on:

prepare query1
prepare query2
create something -> schema change
query1.step
  - schema invalid so recreate
  - recreation of schema changes the generation
query2.step
  - schema invalid so recreate
  - recreation of schema changes the generation
query1.step
  - schema invalid so recreate
  - recreation of schema changes the generation
query2.step
  - schema invalid so recreate
  - recreation of schema changes the generation

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

Re: [sqlite] Found it

[John Elrick]

On Wed, Jan 25, 2012 at 10:32 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 25 Jan 2012, at 3:18pm, John Elrick wrote:
>
> > So, it appears that something is making SQLite think the schema has
> changed.
>
> Can you read the following:
>
> <http://www.sqlite.org/pragma.html#pragma_schema_version>
>
> If you think this corresponds to what you are seeing in the source code,
> can you log the value returned by
>
> PRAGMA schema_version
>
> at various points ?  Does the value returned by the PRAGMA agree with what
> you see in
>
> db->flags_InternChanges
>
> ?  Perhaps you can find that the PRAGMA value is changing when you see no
> reason for it changing.
>
>
The pragma becomes 63 at the very start of the tracked run and never
changes.  However, vdbeHalt continually reports that the schema has altered.

schema from 56 to 57 changed on = 'create table preferences ( machine_user
varchar(80), section text, value text, primary key(machine_user, section))'
schema from 61 to 62 changed on = 'create table if not exists
passthrough_attributes (Passthrough_attribute_oid integer,
manifest_identifier varchar, element_name varchar, attribute_name varchar,
value varchar)'
schema from 62 to 63 changed on = 'create trigger if not exists
manifest_ad2 after delete on manifests begin delete from
passthrough_attributes where manifest_identifier = old.manifest_oid; end;'
Schema has changed current state = 'db=03FB1188,
db->aDb[pOp->p1].pSchema=02110B18,
db->aDb[pOp->p1].pSchema->schema_cookie=63,
db->aDb[pOp->p1].pSchema->iGeneration=0, u.av.iMeta=63, u.av.iGen=0,
pOp->p1=0, pOp->p2=61, pOp->p3=0'
Schema has changed sql = 'INSERT INTO TABSHEETS VALUES (null,
:workbookType, :manifestOid, :surveyOid, :formDefinitionId, :prefKey,
:name, :originalName, :sequence, :columnList)'
vdbeHalt rollback or commit schema changes = 'p->rc=17, db->flags=48100a00,
db->flags_InternChanges=200'
Schema has changed current state = 'db=03FB1188,
db->aDb[pOp->p1].pSchema=02110B18,
db->aDb[pOp->p1].pSchema->schema_cookie=63,
db->aDb[pOp->p1].pSchema->iGeneration=1, u.av.iMeta=63, u.av.iGen=1,
pOp->p1=0, pOp->p2=61, pOp->p3=0'
Schema has changed sql = 'select distinct
FORM_DEFINITIONS.FORM_DEFINITION_OID from FORM_DEFINITIONS where
FORM_DEFINITIONS.guid=:FORM_DEFINITIONS_guid order by
FORM_DEFINITIONS.rowid '
vdbeHalt rollback or commit schema changes = 'p->rc=17, db->flags=48100a00,
db->flags_InternChanges=200'
Schema has changed current state = 'db=03FB1188,
db->aDb[pOp->p1].pSchema=02110B18,
db->aDb[pOp->p1].pSchema->schema_cookie=63,
db->aDb[pOp->p1].pSchema->iGeneration=2, u.av.iMeta=63, u.av.iGen=2,
pOp->p1=0, pOp->p2=63, pOp->p3=0'
Schema has changed sql = 'select distinct
FORM_DEFINITIONS.FORM_DEFINITION_OID from FORM_DEFINITIONS, SURVEYS where
SURVEYS.SURVEY_OID=:SURVEYS_SURVEY_OID and
FORM_DEFINITIONS.FORM_DEFINITION_OID=:FORM_DEFINITIONS_FORM_DEFINITION_OID
and FORM_DEFINITIONS.definition_parent=SURVEYS.SURVEY_OID order by
FORM_DEFINITIONS.rowid '
vdbeHalt rollback or commit schema changes = 'p->rc=17, db->flags=48100a00,
db->flags_InternChanges=200'
Schema has changed current state = 'db=03FB1188,
db->aDb[pOp->p1].pSchema=02110B18,
db->aDb[pOp->p1].pSchema->schema_cookie=63,
db->aDb[pOp->p1].pSchema->iGeneration=3, u.av.iMeta=63, u.av.iGen=3,
pOp->p1=0, pOp->p2=61, pOp->p3=0'
Schema has changed sql = 'insert   into background_constraints values (
:sequence_instance_oid , :sequence_definition_oid ) '
vdbeHalt rollback or commit schema changes = 'p->rc=17, db->flags=48100a00,
db->flags_InternChanges=200'
Schema has changed current state = 'db=03FB1188,
db->aDb[pOp->p1].pSchema=02110B18,
db->aDb[pOp->p1].pSchema->schema_cookie=63,
db->aDb[pOp->p1].pSchema->iGeneration=4, u.av.iMeta=63, u.av.iGen=4,
pOp->p1=0, pOp->p2=63, pOp->p3=2'
Schema has changed sql = 'select distinct
FORM_DEFINITIONS.FORM_DEFINITION_OID from FORM_DEFINITIONS where
FORM_DEFINITIONS.guid=:FORM_DEFINITIONS_guid order by
FORM_DEFINITIONS.rowid '
vdbeHalt rollback or commit schema changes = 'p->rc=17, db->flags=48100a00,
db->flags_InternChanges=200'
...

There are 9,775 more messages like these in the queue.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Wed, Jan 25, 2012 at 9:50 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Jan 25, 2012 at 9:02 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> >
> > current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110AB0,
> > db->aDb[pOp->p1].pSchema->schema_cookie=27,
> > db->aDb[pOp->p1].pSchema->iGeneration=63, u.av.iMeta=27, u.av.iGen=63,
> > pOp->p1=1, pOp->p2=27, pOp->p3=0'
> >
> > sql = 'update alerter_linksset is_dirty = null '
> >
> > Obviously, iGeneration and p3 are out of sync.  So, what exactly is
> pOp->p3
> > ?
> >
>
> pOp->p3 is suppose to be the value of iGeneration when the statement was
> first prepared.  It is set here:
> http://www.sqlite.org/src/artifact/8e2a4dedad?ln=160-162
>
> I'm a little concerned that pOp->p3 is zero when iGeneration is 63.  That's
> a lot of changes.  When was the statement first prepared?  When the program
> first starts up?  Or is pOp->p3 getting zeroed somehow?


As much as possible, we are using lazy initialization.  A query is prepared
the first time it is used, but the prepared query should be retained until
shutdown.


I do have some more information for you.  I tracking everything which could
call reset and discovered that all of these are stemming from

SQLITE_PRIVATE int sqlite3VdbeHalt(Vdbe *p){

/* Rollback or commit any schema changes that occurred. */
if( p->rc!=SQLITE_OK && db->flags_InternChanges ){
fenestra_sqlite3_dump_stack_trace("rollback in vdbeHalt");
  sqlite3ResetInternalSchema(db, -1);
  db->flags = (db->flags | SQLITE_InternChanges);
}

I added a logging item and retrieved the following data:

status = 'p->rc=17, db->flags=48100a00, SQLITE_InternChanges=200'

So, it appears that something is making SQLite think the schema has changed.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

One more bit of interesting information.

if( db->aDb[pOp->p1].pSchema->schema_cookie!=u.av.iMeta ){
fenestra_sqlite3_dump_stack_trace("OP_VerifyCookie");
  sqlite3ResetInternalSchema(db, pOp->p1);
}

The call to fenestra_sqlite3_dump_stack_trace is never invoked.  Whatever
is resetting the internal schema isn't coming from here.

On Wed, Jan 25, 2012 at 9:02 AM, John Elrick <john.elr...@fenestra.com>wrote:

>
> On Tue, Jan 24, 2012 at 7:46 PM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On Tue, Jan 24, 2012 at 7:37 PM, John Elrick <john.elr...@fenestra.com
>> >wrote:
>>
>> >
>> > It appears that the value is set here:
>> >
>> >  u.av.pBt = db->aDb[pOp->p1].pBt;
>> >  if( u.av.pBt ){
>> >sqlite3BtreeGetMeta(u.av.pBt, BTREE_SCHEMA_VERSION, (u32
>> *));
>> >u.av.iGen = db->aDb[pOp->p1].pSchema->iGeneration;
>> >  }else{
>> >u.av.iGen = u.av.iMeta = 0;
>> >  }
>> >
>> >
>> > I added a log trace to the else condition and it never fired off, so
>> > something is causing db->aDb[pOp->p1].pSchema->iGeneration to change
>> > relative to pOp->p3.  Give me some guidance as to where to dig and I'll
>> be
>> > glad to do so
>> >
>>
>> iGeneration changes in only one place in the code:
>> http://www.sqlite.org/src/artifact/5069f2248?ln=431
>>
>> May I recommend that you put a printf() before this line
>>
>>   http://www.sqlite.org/src/artifact/fc1b97fa6816?ln=3022
>>
>> And have that printf display the following values:
>>
>>   db  (rendered using %p)
>>   pOp->p1  (using %d)
>>   db->aDb[pOp->p1].pSchema  (using %p)
>>   db->aDb[pOp->p1].pSchema->schema_cookie  (using %d)
>>   db->aDb[pOp->p1].pSchema->iGeneration (using %d)
>>   iMeta  (using %d)
>>   pOp->p2  (using %d)
>>   pOp->p3  (using %d)
>>   p->zSql  (using %s)
>>
>>
> Progress.   A couple of representatives
>
> current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110AB0,
> db->aDb[pOp->p1].pSchema->schema_cookie=27,
> db->aDb[pOp->p1].pSchema->iGeneration=63, u.av.iMeta=27, u.av.iGen=63,
> pOp->p1=1, pOp->p2=27, pOp->p3=0'
>
> sql = 'update alerter_linksset is_dirty = null '
>
> current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110AB0,
> db->aDb[pOp->p1].pSchema->schema_cookie=27,
> db->aDb[pOp->p1].pSchema->iGeneration=63, u.av.iMeta=27, u.av.iGen=63,
> pOp->p1=1, pOp->p2=27, pOp->p3=0'
>
> sql = 'select bd.behavior_oid as behavior_oid   from
> computation_definitions cd  , behavior_dependents bd  where
> bd.behavior_oid = cd.behavior_oidand bd.response_definition_oid =
> :response_definition_oid '
>
> current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110B18,
> db->aDb[pOp->p1].pSchema->schema_cookie=63,
> db->aDb[pOp->p1].pSchema->iGeneration=4, u.av.iMeta=63, u.av.iGen=4,
> pOp->p1=0, pOp->p2=63, pOp->p3=2'
>
> sql = 'select distinct FORM_DEFINITIONS.FORM_DEFINITION_OID from
> FORM_DEFINITIONS where FORM_DEFINITIONS.guid=:FORM_DEFINITIONS_guid order
> by FORM_DEFINITIONS.rowid '
>
> current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110B18,
> db->aDb[pOp->p1].pSchema->schema_cookie=63,
> db->aDb[pOp->p1].pSchema->iGeneration=5, u.av.iMeta=63, u.av.iGen=5,
> pOp->p1=0, pOp->p2=63, pOp->p3=2'
>
> sql = 'select distinct FORM_DEFINITIONS.FORM_DEFINITION_OID from
> FORM_FLAGS, FORM_DEFINITIONS, MANIFESTS, SURVEYS where
> MANIFESTS.MANIFEST_OID=:MANIFESTS_MANIFEST_OID and
> FORM_DEFINITIONS.FORM_DEFINITION_OID=:FORM_DEFINITIONS_FORM_DEFINITION_OID
> and FORM_FLAGS.definition_parent=FORM_DEFINITIONS.FORM_DEFINITION_OID and
> FORM_FLAGS.instance_parent=MANIFESTS.MANIFEST_OID and
> FORM_DEFINITIONS.definition_parent=SURVEYS.SURVEY_OID and
> MANIFESTS.definition_parent=SURVEYS.SURVEY_OID order by
> FORM_DEFINITIONS.rowid '
>
> Obviously, iGeneration and p3 are out of sync.  So, what exactly is
> pOp->p3 ?
>
>


-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Tue, Jan 24, 2012 at 7:46 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Tue, Jan 24, 2012 at 7:37 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> >
> > It appears that the value is set here:
> >
> >  u.av.pBt = db->aDb[pOp->p1].pBt;
> >  if( u.av.pBt ){
> >sqlite3BtreeGetMeta(u.av.pBt, BTREE_SCHEMA_VERSION, (u32
> *));
> >u.av.iGen = db->aDb[pOp->p1].pSchema->iGeneration;
> >  }else{
> >u.av.iGen = u.av.iMeta = 0;
> >  }
> >
> >
> > I added a log trace to the else condition and it never fired off, so
> > something is causing db->aDb[pOp->p1].pSchema->iGeneration to change
> > relative to pOp->p3.  Give me some guidance as to where to dig and I'll
> be
> > glad to do so
> >
>
> iGeneration changes in only one place in the code:
> http://www.sqlite.org/src/artifact/5069f2248?ln=431
>
> May I recommend that you put a printf() before this line
>
>   http://www.sqlite.org/src/artifact/fc1b97fa6816?ln=3022
>
> And have that printf display the following values:
>
>   db  (rendered using %p)
>   pOp->p1  (using %d)
>   db->aDb[pOp->p1].pSchema  (using %p)
>   db->aDb[pOp->p1].pSchema->schema_cookie  (using %d)
>   db->aDb[pOp->p1].pSchema->iGeneration (using %d)
>   iMeta  (using %d)
>   pOp->p2  (using %d)
>   pOp->p3  (using %d)
>   p->zSql  (using %s)
>
>
Progress.   A couple of representatives

current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110AB0,
db->aDb[pOp->p1].pSchema->schema_cookie=27,
db->aDb[pOp->p1].pSchema->iGeneration=63, u.av.iMeta=27, u.av.iGen=63,
pOp->p1=1, pOp->p2=27, pOp->p3=0'

sql = 'update alerter_linksset is_dirty = null '

current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110AB0,
db->aDb[pOp->p1].pSchema->schema_cookie=27,
db->aDb[pOp->p1].pSchema->iGeneration=63, u.av.iMeta=27, u.av.iGen=63,
pOp->p1=1, pOp->p2=27, pOp->p3=0'

sql = 'select bd.behavior_oid as behavior_oid   from
computation_definitions cd  , behavior_dependents bd  where
bd.behavior_oid = cd.behavior_oidand bd.response_definition_oid =
:response_definition_oid '

current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110B18,
db->aDb[pOp->p1].pSchema->schema_cookie=63,
db->aDb[pOp->p1].pSchema->iGeneration=4, u.av.iMeta=63, u.av.iGen=4,
pOp->p1=0, pOp->p2=63, pOp->p3=2'

sql = 'select distinct FORM_DEFINITIONS.FORM_DEFINITION_OID from
FORM_DEFINITIONS where FORM_DEFINITIONS.guid=:FORM_DEFINITIONS_guid order
by FORM_DEFINITIONS.rowid '

current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110B18,
db->aDb[pOp->p1].pSchema->schema_cookie=63,
db->aDb[pOp->p1].pSchema->iGeneration=5, u.av.iMeta=63, u.av.iGen=5,
pOp->p1=0, pOp->p2=63, pOp->p3=2'

sql = 'select distinct FORM_DEFINITIONS.FORM_DEFINITION_OID from
FORM_FLAGS, FORM_DEFINITIONS, MANIFESTS, SURVEYS where
MANIFESTS.MANIFEST_OID=:MANIFESTS_MANIFEST_OID and
FORM_DEFINITIONS.FORM_DEFINITION_OID=:FORM_DEFINITIONS_FORM_DEFINITION_OID
and FORM_FLAGS.definition_parent=FORM_DEFINITIONS.FORM_DEFINITION_OID and
FORM_FLAGS.instance_parent=MANIFESTS.MANIFEST_OID and
FORM_DEFINITIONS.definition_parent=SURVEYS.SURVEY_OID and
MANIFESTS.definition_parent=SURVEYS.SURVEY_OID order by
FORM_DEFINITIONS.rowid '

Obviously, iGeneration and p3 are out of sync.  So, what exactly is pOp->p3
?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Wed, Jan 25, 2012 at 8:02 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Jan 25, 2012 at 7:53 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > I started with the place where iGeneration was being changed and found
> > nothing out of the ordinary.  I started an examination and noticed the
> > following.  My "huh?" surrounds the question, why is sqlite3SchemaClear
> > being called if pBt is assigned?
> >
>
> sqlite3SchemaClear is a destructor.  The third parameter to
> sqlite3BtreeSchema() is a pointer to the destructor.  sqlite3SchemaClear()
> is not actually called at this point.  Rather, a pointer to
> sqlite3SchemaClear() is passed into sqlite3BtreeSchema() and
> sqlite3BtreeSchema() then makes arrangements to invoke sqlite3SchemaClear()
> at some later time, when the Schema object it has just created gets
> destroyed.
>
>
Ah, I see it now.  Sorry about that.  In Pascal a procedure can be invoked
without the ().  I sometimes forget to translate.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

...especially given that the results of sqlite3SchemaClear are being passed
as a parameter and from the declaration, sqlite3SchemaClear doesn't return
anything.

On Wed, Jan 25, 2012 at 7:53 AM, John Elrick <john.elr...@fenestra.com>wrote:

> I started with the place where iGeneration was being changed and found
> nothing out of the ordinary.  I started an examination and noticed the
> following.  My "huh?" surrounds the question, why is sqlite3SchemaClear
> being called if pBt is assigned?
>
> SQLITE_PRIVATE Schema *sqlite3SchemaGet(sqlite3 *db, Btree *pBt){
>   Schema * p;
>   if( pBt ){
> p = (Schema *)sqlite3BtreeSchema(pBt, sizeof(Schema),
> sqlite3SchemaClear);
>   }else{
> p = (Schema *)sqlite3DbMallocZero(0, sizeof(Schema));
>   }
>   if( !p ){
> db->mallocFailed = 1;
>   }else if ( 0==p->file_format ){
> sqlite3HashInit(>tblHash);
> sqlite3HashInit(>idxHash);
> sqlite3HashInit(>trigHash);
> sqlite3HashInit(>fkeyHash);
> p->enc = SQLITE_UTF8;
>   }
>   return p;
> }
>
>
> On Tue, Jan 24, 2012 at 7:46 PM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On Tue, Jan 24, 2012 at 7:37 PM, John Elrick <john.elr...@fenestra.com
>> >wrote:
>>
>> >
>> > It appears that the value is set here:
>> >
>> >  u.av.pBt = db->aDb[pOp->p1].pBt;
>> >  if( u.av.pBt ){
>> >sqlite3BtreeGetMeta(u.av.pBt, BTREE_SCHEMA_VERSION, (u32
>> *));
>> >u.av.iGen = db->aDb[pOp->p1].pSchema->iGeneration;
>> >  }else{
>> >u.av.iGen = u.av.iMeta = 0;
>> >  }
>> >
>> >
>> > I added a log trace to the else condition and it never fired off, so
>> > something is causing db->aDb[pOp->p1].pSchema->iGeneration to change
>> > relative to pOp->p3.  Give me some guidance as to where to dig and I'll
>> be
>> > glad to do so
>> >
>>
>> iGeneration changes in only one place in the code:
>> http://www.sqlite.org/src/artifact/5069f2248?ln=431
>>
>> May I recommend that you put a printf() before this line
>>
>>   http://www.sqlite.org/src/artifact/fc1b97fa6816?ln=3022
>>
>> And have that printf display the following values:
>>
>>   db  (rendered using %p)
>>   pOp->p1  (using %d)
>>   db->aDb[pOp->p1].pSchema  (using %p)
>>   db->aDb[pOp->p1].pSchema->schema_cookie  (using %d)
>>   db->aDb[pOp->p1].pSchema->iGeneration (using %d)
>>   iMeta  (using %d)
>>   pOp->p2  (using %d)
>>   pOp->p3  (using %d)
>>   p->zSql  (using %s)
>>
>> And then also add a printf on the line above where iGeneration changes and
>> display its new value every time it changes.
>>
>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> John Elrick
> Fenestra Technologies
> 540-868-1377
>
>


-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

I started with the place where iGeneration was being changed and found
nothing out of the ordinary.  I started an examination and noticed the
following.  My "huh?" surrounds the question, why is sqlite3SchemaClear
being called if pBt is assigned?

SQLITE_PRIVATE Schema *sqlite3SchemaGet(sqlite3 *db, Btree *pBt){
  Schema * p;
  if( pBt ){
p = (Schema *)sqlite3BtreeSchema(pBt, sizeof(Schema),
sqlite3SchemaClear);
  }else{
p = (Schema *)sqlite3DbMallocZero(0, sizeof(Schema));
  }
  if( !p ){
db->mallocFailed = 1;
  }else if ( 0==p->file_format ){
sqlite3HashInit(>tblHash);
sqlite3HashInit(>idxHash);
sqlite3HashInit(>trigHash);
sqlite3HashInit(>fkeyHash);
p->enc = SQLITE_UTF8;
  }
  return p;
}


On Tue, Jan 24, 2012 at 7:46 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Tue, Jan 24, 2012 at 7:37 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> >
> > It appears that the value is set here:
> >
> >  u.av.pBt = db->aDb[pOp->p1].pBt;
> >  if( u.av.pBt ){
> >sqlite3BtreeGetMeta(u.av.pBt, BTREE_SCHEMA_VERSION, (u32
> *));
> >u.av.iGen = db->aDb[pOp->p1].pSchema->iGeneration;
> >  }else{
> >u.av.iGen = u.av.iMeta = 0;
> >  }
> >
> >
> > I added a log trace to the else condition and it never fired off, so
> > something is causing db->aDb[pOp->p1].pSchema->iGeneration to change
> > relative to pOp->p3.  Give me some guidance as to where to dig and I'll
> be
> > glad to do so
> >
>
> iGeneration changes in only one place in the code:
> http://www.sqlite.org/src/artifact/5069f2248?ln=431
>
> May I recommend that you put a printf() before this line
>
>   http://www.sqlite.org/src/artifact/fc1b97fa6816?ln=3022
>
> And have that printf display the following values:
>
>   db  (rendered using %p)
>   pOp->p1  (using %d)
>   db->aDb[pOp->p1].pSchema  (using %p)
>   db->aDb[pOp->p1].pSchema->schema_cookie  (using %d)
>   db->aDb[pOp->p1].pSchema->iGeneration (using %d)
>   iMeta  (using %d)
>   pOp->p2  (using %d)
>   pOp->p3  (using %d)
>   p->zSql  (using %s)
>
> And then also add a printf on the line above where iGeneration changes and
> display its new value every time it changes.
>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Tue, Jan 24, 2012 at 7:26 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Tue, Jan 24, 2012 at 7:18 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>

SNIP


> So the problem was introduced by
>
> http://www.sqlite.org/src/info/36c04dd1695f08
>
> Which is the fix for this bug:
>
> http://www.sqlite.org/src/info/f7b4edece25c99
>
> That helps.  But we still cannot recreate the problem.  Are you using
> shared cache mode and do you have multiple database connections open on the
> same database file?


Nope and nope.

It appears that the value is set here:

  u.av.pBt = db->aDb[pOp->p1].pBt;
  if( u.av.pBt ){
sqlite3BtreeGetMeta(u.av.pBt, BTREE_SCHEMA_VERSION, (u32 *));
u.av.iGen = db->aDb[pOp->p1].pSchema->iGeneration;
  }else{
u.av.iGen = u.av.iMeta = 0;
  }


I added a log trace to the else condition and it never fired off, so
something is causing db->aDb[pOp->p1].pSchema->iGeneration to change
relative to pOp->p3.  Give me some guidance as to where to dig and I'll be
glad to do so (tomorrow, my 9 year old daughter just asked for ice cream).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

I really found it this time.

While I was looking over the code changes, I noticed a section of code
dealing with case OP_VerifyCookie:

Inside that section is the following in 3.7.6

  if( u.av.iMeta!=pOp->p2 || u.av.iGen!=pOp->p3 ){
sqlite3DbFree(db, p->zErrMsg);
p->zErrMsg = sqlite3DbStrDup(db, "database schema has changed");
/* If the schema-cookie from the database file matches the cookie
** stored with the in-memory representation of the schema, do
** not reload the schema from the database file.
**
** If virtual-tables are in use, this is not just an optimization.
** Often, v-tables store their data in other SQLite tables, which
** are queried from within xNext() and other v-table methods using
** prepared queries. If such a query is out-of-date, we do not want to
** discard the database schema, as the user code implementing the
** v-table would have to be ready for the sqlite3_vtab structure itself
** to be invalidated whenever sqlite3_step() is called from within
** a v-table method.
*/
if( db->aDb[pOp->p1].pSchema->schema_cookie!=u.av.iMeta ){
  sqlite3ResetInternalSchema(db, pOp->p1);
}

On a hunch, I replaced this line:

  if( u.av.iMeta!=pOp->p2 || u.av.iGen!=pOp->p3 ){

with the if statement from 3.7.5

  if( u.av.iMeta!=pOp->p2 ){

When I do, the number of mallocs drops back to 14,000 - still a little
higher than in 3.7.5, but nowhere near 140,000.

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

Re: [sqlite] Found it

[John Elrick]

Let me rephrase that...

I ran this test with a small case.  See results below.

On Tue, Jan 24, 2012 at 5:55 PM, John Elrick <john.elr...@fenestra.com>wrote:

SNIP

> > http://www.sqlite.org/fenstra/sqlite-201102040051.zip
>>
>
> Low malloc count
>

8,191 calls to malloc (consistent with 3.7.5 for this dataset)


>
>
>>
>> > > http://www.sqlite.org/fenstra/sqlite-201102112254.zip
>>
>
> High malloc count
>
>
14,726 calls to malloc


3.7.6

148,606 calls to malloc

So there was a jump, but not to the full level.


> The problem is between the two.
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Tue, Jan 24, 2012 at 5:36 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Tue, Jan 24, 2012 at 5:21 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > On Tue, Jan 24, 2012 at 3:05 PM, Richard Hipp <d...@sqlite.org> wrote:
> > > >
> > > > OK.  Please try these:
> > >
> > > http://www.sqlite.org/fenstra/sqlite-201102040051.zip
>

Low malloc count


>
> > > http://www.sqlite.org/fenstra/sqlite-201102112254.zip
>

High malloc count

The problem is between the two.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Tue, Jan 24, 2012 at 3:05 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Tue, Jan 24, 2012 at 1:28 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> >
> >
> > > http://www.sqlite.org/fenstra/sqlite-201103081639.zip
> >
> >
> > This one gives the high number of mallocs just as does 3.7.6.
> >
> > OK.  Please try these:
>
> http://www.sqlite.org/fenstra/sqlite-201102040051.zip
> http://www.sqlite.org/fenstra/sqlite-201102112254.zip
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


Both of these are low malloc count.

I happened to think on my long drive from Frederick, are their canonical
procedures used to flag the system, to tell it that the schema needs
rebuilt?  Maybe I should attempt a call stack trace on those (if they
exist).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

Nope and nope.  They are happening apparently out of the blue.
On Jan 24, 2012 2:40 PM, "Nico Williams"  wrote:

> Maybe a view is getting materialized, or a some other temp table's
> creation under the hood is triggering this?
> ___
> 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] Found it

[John Elrick]

On Tue, Jan 24, 2012 at 12:53 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Tue, Jan 24, 2012 at 12:15 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > >
> > > Can you put a breakpoint on sqlite3ResetInternalSchema() and figure out
> > > from whence it is being called so often?
> > >
> > >
> >
> > Just did.  Most of the calls are bubbling up from _sqlite3_step which I
> > mentioned before.
>

Richard,

I added some tracking which keeps a log of every query and writes the last
50 things done when the call to sqlite3ResetInternalSchema() takes place.
 Here is a fairly representative entry.  The last SQL is the one which
triggered the call to sqlite3ResetInternalSchema().  Nothing here which
touches the schema of the database that I can see.

last 50 sqls = 'step: select distinct RESPONSES.RESPONSE_OID from
RESPONSES, DATA_ELEMENTS, SEQUENCE_ELEMENTS where
SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID
and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and
RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and
RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by
RESPONSES.rowid
step: SELECT * FROM RESPONSES a where a.soft_deleted_char = 'F' and
a.RESPONSE_OID = :oid
step: UPDATE RESPONSES SET
RESPONSE_NAME = :RESPONSE_NAME,
prelisted_value = :prelisted_value
WHERE RESPONSE_OID = :RESPONSE_OID
step: select distinct DATA_ELEMENTS.DATA_ELEMENT_OID from RESPONSES,
DATA_ELEMENTS, SEQUENCE_ELEMENTS where
SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID
and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and
RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and
RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by
DATA_ELEMENTS.rowid
step: select distinct DATA_ELEMENTS.DATA_ELEMENT_OID from RESPONSES,
DATA_ELEMENTS, SEQUENCE_ELEMENTS where
SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID
and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and
RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and
RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by
DATA_ELEMENTS.rowid
step: select distinct RESPONSES.RESPONSE_OID from RESPONSES, DATA_ELEMENTS,
SEQUENCE_ELEMENTS where
SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID
and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and
RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and
RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by
RESPONSES.rowid
step: select distinct RESPONSES.RESPONSE_OID from RESPONSES, DATA_ELEMENTS,
SEQUENCE_ELEMENTS where
SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID
and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and
RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and
RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by
RESPONSES.rowid
step: SELECT * FROM RESPONSES a where a.soft_deleted_char = 'F' and
a.RESPONSE_OID = :oid
step: UPDATE RESPONSES SET
RESPONSE_NAME = :RESPONSE_NAME,
prelisted_value = :prelisted_value
WHERE RESPONSE_OID = :RESPONSE_OID
step: select distinct DATA_ELEMENTS.DATA_ELEMENT_OID from RESPONSES,
DATA_ELEMENTS, SEQUENCE_ELEMENTS where
SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID
and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and
RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and
RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by
DATA_ELEMENTS.rowid
step: select distinct DATA_ELEMENTS.DATA_ELEMENT_OID from RESPONSES,
DATA_ELEMENTS, SEQUENCE_ELEMENTS where
SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID
and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and
RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and
RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by
DATA_ELEMENTS.rowid
step: select distinct RESPONSES.RESPONSE_OID from RESPONSES, DATA_ELEMENTS,
SEQUENCE_ELEMENTS where
SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID
and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and
RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and
RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by
RESPONSES.rowid
step: select distinct RESPONSES.RESPONSE_OID from RESPONSES, DATA_ELEMENTS,
SEQUENCE_ELEMENTS where
SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID=:SEQUENCE_ELEMENTS_SEQUENCE_ELEMENT_OID
and DATA_ELEMENTS.DATA_ELEMENT_NAME=:DATA_ELEMENTS_DATA_ELEMENT_NAME and
RESPONSES.instance_parent=SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and
RESPONSES.definition_parent=DATA_ELEMENTS.DATA_ELEMENT_OID order by
RESPONSES.rowid
st

Re: [sqlite] Found it

[John Elrick]

On Tue, Jan 24, 2012 at 1:28 PM, John Elrick <john.elr...@fenestra.com>wrote:

SNIP

http://www.sqlite.org/fenstra/sqlite-201103241737.zip
>
>
> This one is failing to compile.  It needs _msize.  That sounds like the
> 3.7.10 issue reported recently.
>
>
I opened the sqlite3.c file and it is version 3.7.10.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Tue, Jan 24, 2012 at 1:29 PM, Krystian Bigaj wrote:

> You can compile sqlite3.dll with debug information, and then you can in VS
> attach to your running .exe - you should be able to see sqlite
> stacks/breakpoints/step-in/out/etc. should also work.
>
>
Thanks very much for the suggestion.  We do not have, nor have any
intention of using, Visual Studio.  It's also not a DLL, the resulting
output is a Borland OBJ, which cannot be generated correctly from any other
compiler.


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

Re: [sqlite] Found it

[John Elrick]

On Tue, Jan 24, 2012 at 12:53 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Tue, Jan 24, 2012 at 12:15 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
>
SNIP


> http://www.sqlite.org/fenstra/sqlite-201103081639.zip


This one gives the high number of mallocs just as does 3.7.6.


>
> http://www.sqlite.org/fenstra/sqlite-201103241737.zip


This one is failing to compile.  It needs _msize.  That sounds like the
3.7.10 issue reported recently.


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

Re: [sqlite] Found it

[John Elrick]

On Tue, Jan 24, 2012 at 1:18 PM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 01/25/2012 12:15 AM, John Elrick wrote:
>
>> On Tue, Jan 24, 2012 at 12:06 PM, Richard Hipp<d...@sqlite.org>  wrote:
>>
>>  On Tue, Jan 24, 2012 at 11:59 AM, John 
>> Elrick<john.elrick@fenestra.**com<john.elr...@fenestra.com>
>>>
>>>> wrote:
>>>>
>>>
>>>  While reviewing our other thread, I noticed a piece of information
>>>> concerning sqlite3ResetInternalSchema.  Based on the number of times
>>>>
>>> that I
>>>
>>>> reported it had been called, I believe that something was changed
>>>> between
>>>> 3.7.5 and 3.7.6 which causes sqlite3ResetInternalSchema to be called
>>>> when
>>>> it wasn't in the earlier version.   Is there some kind of enumeration of
>>>> what events would cause sqlite3ResetInternalSchema to be called in
>>>>
>>> version
>>>
>>>> 3.7.5 vs 3.7.6?
>>>>
>>>>
>>> I agree that the fact that the triggers are being reparsed is a big hint.
>>> But we still don't know why they are being reparsed.  And we still cannot
>>> reproduce the problem in a command-line shell - in only seems to occur
>>> within your Delphi app.
>>>
>>> Can you put a breakpoint on sqlite3ResetInternalSchema() and figure out
>>> from whence it is being called so often?
>>>
>>>
>>>
>> Just did.  Most of the calls are bubbling up from _sqlite3_step which I
>> mentioned before.
>>
>
> Can you see the whole call stack? There are a couple of different
> ways sqlite3ResetInternalSchema() may be called from within
> sqlite3_step(). Knowing which will be a clue as to why it is being
> called.
>
> Unfortunately, no I cannot.  Delphi 2007 doesn't give me accurate
information from a linked OBJ.  It's roughly equivalent to trying to get
accurate call stacks from inside a DLL.  I've had to hand add pseudo call
stack information.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

On Tue, Jan 24, 2012 at 12:06 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Tue, Jan 24, 2012 at 11:59 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > While reviewing our other thread, I noticed a piece of information
> > concerning sqlite3ResetInternalSchema.  Based on the number of times
> that I
> > reported it had been called, I believe that something was changed between
> > 3.7.5 and 3.7.6 which causes sqlite3ResetInternalSchema to be called when
> > it wasn't in the earlier version.   Is there some kind of enumeration of
> > what events would cause sqlite3ResetInternalSchema to be called in
> version
> > 3.7.5 vs 3.7.6?
> >
>
> I agree that the fact that the triggers are being reparsed is a big hint.
> But we still don't know why they are being reparsed.  And we still cannot
> reproduce the problem in a command-line shell - in only seems to occur
> within your Delphi app.
>
> Can you put a breakpoint on sqlite3ResetInternalSchema() and figure out
> from whence it is being called so often?
>
>

Just did.  Most of the calls are bubbling up from _sqlite3_step which I
mentioned before.  There are no new changes to the schema immediately prior
to these calls.  Here is one example where step triggers a call to
sqlite3ResetInternalSchema() (apologies for using Delphi and our wrappers,
but I hope it's clear):

  itsSaveTabsheet :=
itsDatabase.prepareExec('INSERT INTO TABSHEETS VALUES (null,
:workbookType, '
  + ':manifestOid, :surveyOid, :formDefinitionId, :prefKey, '
  + ':name, :originalName, :sequence, :columnList)');
...

function TDataServices.saveTabsheet(const tabsheetId, aWorkbookType,
manifestOid, surveyOid,
  formDefinitionOid, aPrefKey, aName, aSeq, aColumnList: string): string;
begin
  itsSaveTabsheet := itsSaveTabsheet;
  itsSaveTabsheet.bind(':workbookType', aWorkbookType);
  itsSaveTabsheet.bind(':manifestOid', manifestOid);
  itsSaveTabsheet.bind(':surveyOid', surveyOid);
  itsSaveTabsheet.bind(':formDefinitionId', StrToIntDef(formDefinitionOid,
0));
  itsSaveTabsheet.bind(':prefKey', aPrefKey);
  itsSaveTabsheet.bind(':name', aName);
  itsSaveTabsheet.bind(':originalName', aName);
  itsSaveTabsheet.bind(':sequence', aSeq);
  itsSaveTabsheet.bind(':columnList', aColumnList);
  itsSaveTabsheet.execute;
  result := IntToStr(itsDatabase.lastInsertRowId);
end;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

While reviewing our other thread, I noticed a piece of information
concerning sqlite3ResetInternalSchema.  Based on the number of times that I
reported it had been called, I believe that something was changed between
3.7.5 and 3.7.6 which causes sqlite3ResetInternalSchema to be called when
it wasn't in the earlier version.   Is there some kind of enumeration of
what events would cause sqlite3ResetInternalSchema to be called in version
3.7.5 vs 3.7.6?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

[John Elrick]

Addendum.  After looking further it appears that the reparsing is happening
independently of triggering.  In this case, the reparsing occurs 30,502
times for every view and trigger I have examined.  Only one trigger appears
in the 3.7.5 listing and it is parsed exactly 1 time.

3.7.5

CREATE TRIGGER error_warning_after_delete after delete on
local_errors_warnings begin   insert into local_error_warning_deletions
values( old.error_warning_oid);   update alerter_links
 set is_dirty = 1where sequence_instance_oid =
old.sequence_instance_oid  and response_definition_oid in(
select response_definition_oidfrom behavior_independents
where behavior_oid = old.behavior_oid); end
Times called:  1
Cumulative Allocated Memory:   5,960
Count of _malloc Calls:56
Cumulative Reallocated Memory: 0
Count of _realloc Calls:   0
Cumulative Reallocated Memory where nil:   0
Count of _realloc Calls where nil: 0
Count of _free Calls:  32
Cumulative _mallocs by size
<= 1kb:3,304 bytes (54 count; 61 avg)
1kb to 4kb:2,656 bytes (2 count; 1,328 avg)
4bk to 8kb:0 bytes (0 count; NAN avg)
8bk to 16kb:   0 bytes (0 count; NAN avg)
16bk to 32kb:  0 bytes (0 count; NAN avg)
32bk to 64kb:  0 bytes (0 count; NAN avg)
64bk to 128kb: 0 bytes (0 count; NAN avg)
128kb to 256kb:0 bytes (0 count; NAN avg)
256kb to 512kb:0 bytes (0 count; NAN avg)
512kb to 1024kb:   0 bytes (0 count; NAN avg)
> 1mb: 0 bytes (0 count; NAN avg)
No reallocs
No reallocs of nil pointers
---

3.7.6

CREATE TRIGGER error_warning_after_delete after delete on
local_errors_warnings begin   insert into local_error_warning_deletions
values( old.error_warning_oid);   update alerter_links
 set is_dirty = 1where sequence_instance_oid =
old.sequence_instance_oid  and response_definition_oid in(
select response_definition_oidfrom behavior_independents
where behavior_oid = old.behavior_oid); end
Times called:  30502
Cumulative Allocated Memory:   180,327,824
Count of _malloc Calls:1,708,112
Cumulative Reallocated Memory: 0
Count of _realloc Calls:   0
Cumulative Reallocated Memory where nil:   0
Count of _realloc Calls where nil: 0
Count of _free Calls:  976,064
Cumulative _mallocs by size
<= 1kb:99,314,512 bytes (1,647,108
count; 60 avg)
1kb to 4kb:81,013,312 bytes (61,004 count;
1,328 avg)
4bk to 8kb:0 bytes (0 count; NAN avg)
8bk to 16kb:   0 bytes (0 count; NAN avg)
16bk to 32kb:  0 bytes (0 count; NAN avg)
32bk to 64kb:  0 bytes (0 count; NAN avg)
64bk to 128kb: 0 bytes (0 count; NAN avg)
128kb to 256kb:0 bytes (0 count; NAN avg)
256kb to 512kb:0 bytes (0 count; NAN avg)
512kb to 1024kb:   0 bytes (0 count; NAN avg)
> 1mb: 0 bytes (0 count; NAN avg)
No reallocs
No reallocs of nil pointers
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Found it

[John Elrick]

Richard,

Your last request for some detailed information about what queries were
being processed did the trick.  Here is a description of the problem (and
yes, it appears to be in SQLite).

In 3.7.5, the number one malloc consumer is

INSERT INTO SEQUENCE_ELEMENTS
(
SEQUENCE_ELEMENT_OID,
SEQUENCE_ELEMENT_NAME,
definition_parent,
instance_parent
)
values
(
:SEQUENCE_ELEMENT_OID,
:SEQUENCE_ELEMENT_NAME,
:definition_parent,
:instance_parent
)

Times called:  1
Cumulative Allocated Memory:   12,856
Count of _malloc Calls:169
Cumulative Reallocated Memory: 13,544
Count of _realloc Calls:   24
Cumulative Reallocated Memory where nil:   0
Count of _realloc Calls where nil: 0
Count of _free Calls:  111
Cumulative _mallocs by size
<= 1kb:9,168 bytes (166 count; 55 avg)
1kb to 4kb:3,688 bytes (3 count; 1,229 avg)
4bk to 8kb:0 bytes (0 count; NAN avg)
8bk to 16kb:   0 bytes (0 count; NAN avg)
16bk to 32kb:  0 bytes (0 count; NAN avg)
32bk to 64kb:  0 bytes (0 count; NAN avg)
64bk to 128kb: 0 bytes (0 count; NAN avg)
128kb to 256kb:0 bytes (0 count; NAN avg)
256kb to 512kb:0 bytes (0 count; NAN avg)
512kb to 1024kb:   0 bytes (0 count; NAN avg)
> 1mb: 0 bytes (0 count; NAN avg)
Cumulative _reallocs by size
<= 1kb:1,272 bytes (20 count; 64 avg)
1kb to 4kb:12,272 bytes (4 count; 3,068 avg)
4bk to 8kb:0 bytes (0 count; NAN avg)
8bk to 16kb:   0 bytes (0 count; NAN avg)
16bk to 32kb:  0 bytes (0 count; NAN avg)
32bk to 64kb:  0 bytes (0 count; NAN avg)
64bk to 128kb: 0 bytes (0 count; NAN avg)
128kb to 256kb:0 bytes (0 count; NAN avg)
256kb to 512kb:0 bytes (0 count; NAN avg)
512kb to 1024kb:   0 bytes (0 count; NAN avg)
> 1mb: 0 bytes (0 count; NAN avg)
No reallocs of nil pointers
---

However, in 3.7.6, the following is the number one consumer of mallocs:

CREATE TRIGGER sequence_elements_after_insert after insert on
sequence_elements
begin
update sequence_elements set sort_key = sequence_element_oid where
sequence_element_oid  = new.sequence_element_oid and sort_key is null;
insert into responses (definition_parent, instance_parent, response_name,
prelisted_value) select de.data_element_oid, new.sequence_element_oid,
ag.initial_value, '' from data_elements de, attribute_groups ag where
de.definition_parent = new.definition_parent and de.attribute_group =
ag.attribute_group_name;
insert or ignore into altered_sequences values(new.definition_parent);
end
Times called:  30502
Cumulative Allocated Memory:   255,240,736
Count of _malloc Calls:3,080,702
Cumulative Reallocated Memory: 3,904,256
Count of _realloc Calls:   30,502
Cumulative Reallocated Memory where nil:   0
Count of _realloc Calls where nil: 0
Count of _free Calls:  1,799,618
Cumulative _mallocs by size
<= 1kb:174,227,424 bytes (3,019,698
count; 58 avg)
1kb to 4kb:81,013,312 bytes (61,004 count;
1,328 avg)
4bk to 8kb:0 bytes (0 count; NAN avg)
8bk to 16kb:   0 bytes (0 count; NAN avg)
16bk to 32kb:  0 bytes (0 count; NAN avg)
32bk to 64kb:  0 bytes (0 count; NAN avg)
64bk to 128kb: 0 bytes (0 count; NAN avg)
128kb to 256kb:0 bytes (0 count; NAN avg)
256kb to 512kb:0 bytes (0 count; NAN avg)
512kb to 1024kb:   0 bytes (0 count; NAN avg)
> 1mb: 0 bytes (0 count; NAN avg)
Cumulative _reallocs by size
<= 1kb:3,904,256 bytes (30,502 count;
128 avg)
1kb to 4kb:0 bytes (0 count; NAN avg)
4bk to 8kb:0 bytes (0 count; NAN avg)
8bk to 16kb:   0 bytes (0 count; NAN avg)
16bk to 32kb:  0 bytes (0 count; NAN avg)
32bk to 64kb:  0 bytes (0 count; NAN avg)
64bk to 128kb: 0 bytes (0 count; NAN avg)
128kb to 256kb:0 bytes (0 count; NAN avg)
256kb 

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Mon, Jan 23, 2012 at 4:27 PM, Nico Williams <n...@cryptonector.com>wrote:

> On Mon, Jan 23, 2012 at 3:02 PM, John Elrick <john.elr...@fenestra.com>
> wrote:
> > I think I can inject something to do some measurements.  I seem to
> recall,
> > however, that there was no substantive difference in the number of
> > times sqlite3RunParser
> > was called between the two.  I'll check for:
> >
> > which query is being parsed
> > how many times that particular query is parsed
> > how many mallocs are stemming from that particular query.
> >
> > Maybe that will tell us something.
>
> I think it's fair to say that compiling a statement is expected to be
> heavy-duty, but evaluating a compiled statement is expected to be
> light-weight (not counting the work that the statement implies doing)
> unless something triggers recompilation of the prepared statement.
>
> So the key, really, is to find out what's triggering the recompilation
> of your statements.
>
>
...assuming that they ARE being recompiled.  As I indicated, IIRC they are
not.  The issue may stem from the other thing I noticed, a substantial drop
in the size of the average request to _malloc (by an order of magnitude).
 We shall see tomorrow; I'm done for the day.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Mon, Jan 23, 2012 at 3:48 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Jan 23, 2012 at 3:12 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > Brain hurts...
> >
> > Richard, more information for you.  I rebuilt the call stack checking
> > system into pure Delphi and confirmed that yy_reduce appears to be the
> > malloc culprit.  I further created a pair of procedures which I can use
> to
> > track the yyruleno from yy_reduce as though it were a call.  Below is a
> > representative sample of the data
> >
>
> The parser does lots of little mallocs as it builds a parse tree.  So I
> expect it to generate a lot of malloc traffic.  The question is why the
> parser is being called so much.
>
> Can you put a printf() or something at
> http://www.sqlite.org/src/artifact/1e86210d3976?ln=397 and figure out what
> is being parsed so excessively?
>
>
I think I can inject something to do some measurements.  I seem to recall,
however, that there was no substantive difference in the number of
times sqlite3RunParser
was called between the two.  I'll check for:

which query is being parsed
how many times that particular query is parsed
how many mallocs are stemming from that particular query.

Maybe that will tell us something.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

Brain hurts...

Richard, more information for you.  I rebuilt the call stack checking
system into pure Delphi and confirmed that yy_reduce appears to be the
malloc culprit.  I further created a pair of procedures which I can use to
track the yyruleno from yy_reduce as though it were a call.  Below is a
representative sample of the data.

One interesting thing I noticed is that in 3.7.5, the average <1kb malloc
request size is 181.95 bytes.  In 3.7.6 that average request size drops
to 17.74 bytes.

Both examples are running the same dataset.

versionmallocs   mallocs by yy_reduce
3.7.5  838,789   5,545
3.7.6   70,003,878  68,870,137

Major yy_reduce case consumers

3.7.5
Count:947 = 'case_112
yy_reduce'
Count:696 = 'case_189
yy_reduce'
Count:537 = 'case_173
yy_reduce'
Count:229 = 'case_243
yy_reduce
case_173
yy_reduce'
Count:214 = 'case_37
yy_reduce
case_173
yy_reduce'


3.7.6
Count:  5,606,345 = 'case_112
yy_reduce'
Count:  5,267,458 = 'case_243
yy_reduce
case_112
yy_reduce'
Count:  4,922,428 = 'case_37
yy_reduce
case_112
yy_reduce'
Count:  4,623,402 = 'case_189
yy_reduce
case_112
yy_reduce'
Count:  4,278,372 = 'case_8
yy_reduce
case_112
yy_reduce'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

I may not have been clear that these two separate tests both used the
Delphi bridge (as you refer to it).  What they point to is an unusual
interaction between our full application and SQLite is triggering the
increase in malloc calls.


On Sat, Jan 21, 2012 at 6:48 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 21 Jan 2012, at 11:20pm, Alek Paunov wrote:
>
> > 3.6.17: 14 seconds
> > 3.7.9: 10 seconds
> >
> > This clearly demonstrates that the newer version of Sqlite is, all things
> > being equal, superior in performance to the older.  However, tests inside
> > our Delphi application demonstrate that reaching the exact same point of
> > the database result in the following times:
> >
> > Live Application
> >
> > 3.6.17: 16 seconds
> > 3.7.9: 58 seconds
>
> Which implies that the fault is in the Delphi bridge to SQLite and any
> pure examination of SQLite's behaviour isn't going to spot anything, right
> ?  So a better place to query this would be a Delphi list ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

I'll send it in a bit.  I actually created the complete script a week ago.

Richard, I greatly appreciate your time and the attention you've given this
matter.  I have no doubt this is a very esoteric issue which we just
happened to hit the exact combination of events to force its
manifestation.  As frustrating as it has been to narrow down the cause, I
have nothing but respect and gratitude for everyone who has attempted to
help.  Thank you all.
On Jan 20, 2012 3:47 PM, "Richard Hipp" <d...@sqlite.org> wrote:

> On Fri, Jan 20, 2012 at 3:40 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > I don't mean to blow anyone off, but there are over 100 prepared queries
> > involved in a very interlaced manner.  Getting the EXPLAIN data is very
> > time consuming and since we've gone way past that point already by
> > obtaining call stack information, I am not interested in revisiting the
> > territory unless there is a very good reason for it.  At least I know we
> > can successfully upgrade to 3.7.5 without any performance issues.  It
> would
> > be nice to understand what is going on from that version forward,
> however,
> > because it is important that we be able to continue upgrades in the
> future.
> >
>
> We'd like to understand what SQLite is doing differently to cause your
> problem, too.  As you have already observed, most applications don't have
> this issue.  I'm not sure what your application is doing to make SQLite go
> crazy will mallocs, but we'd like to know so that we can avoid such
> situations in the future.
>
> Can you try this:  Can you use the sqlite3_trace() interface to create a
> log of all SQL statements that are evaluated.  Then send me (perhaps
> privately) the starting database and your log, so that I can run SQLite
> through exactly the same set of operations you are running it through?
>
>
>
>
> >
> >
> > On Fri, Jan 20, 2012 at 2:55 PM, John Elrick <john.elr...@fenestra.com
> > >wrote:
> >
> > > The problem is not in the queries.  The problem is in a two order of
> > > magnitude increase in _mallocs between the versions.  The _mallocs are
> > > coming from sqlite3Parser.
> > >
> > >
> > > On Fri, Jan 20, 2012 at 2:37 PM, Max Vlasov <max.vla...@gmail.com>
> > wrote:
> > >
> > >> On Fri, Jan 20, 2012 at 10:05 PM, John Elrick <
> john.elr...@fenestra.com
> > >> >wrote:
> > >>
> > >> > The change which results in a slow down occurred between 3.7.5.0 and
> > >> > 3.7.6.0.
> > >> >
> > >> >
> > >> What about EXPLAIN difference? Or just outputs of this prefix from
> both
> > >> versions?
> > >>
> > >> Max
> > >> ___
> > >> sqlite-users mailing list
> > >> sqlite-users@sqlite.org
> > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >>
> > >
> > >
> > >
> > > --
> > > John Elrick
> > > Fenestra Technologies
> > > 540-868-1377
> > >
> > >
> >
> >
> > --
> > John Elrick
> > Fenestra Technologies
> > 540-868-1377
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Some pieces of the puzzle are coming together

[John Elrick]

I don't mean to blow anyone off, but there are over 100 prepared queries
involved in a very interlaced manner.  Getting the EXPLAIN data is very
time consuming and since we've gone way past that point already by
obtaining call stack information, I am not interested in revisiting the
territory unless there is a very good reason for it.  At least I know we
can successfully upgrade to 3.7.5 without any performance issues.  It would
be nice to understand what is going on from that version forward, however,
because it is important that we be able to continue upgrades in the future.


On Fri, Jan 20, 2012 at 2:55 PM, John Elrick <john.elr...@fenestra.com>wrote:

> The problem is not in the queries.  The problem is in a two order of
> magnitude increase in _mallocs between the versions.  The _mallocs are
> coming from sqlite3Parser.
>
>
> On Fri, Jan 20, 2012 at 2:37 PM, Max Vlasov <max.vla...@gmail.com> wrote:
>
>> On Fri, Jan 20, 2012 at 10:05 PM, John Elrick <john.elr...@fenestra.com
>> >wrote:
>>
>> > The change which results in a slow down occurred between 3.7.5.0 and
>> > 3.7.6.0.
>> >
>> >
>> What about EXPLAIN difference? Or just outputs of this prefix from both
>> versions?
>>
>> Max
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> John Elrick
> Fenestra Technologies
> 540-868-1377
>
>


-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

The problem is not in the queries.  The problem is in a two order of
magnitude increase in _mallocs between the versions.  The _mallocs are
coming from sqlite3Parser.

On Fri, Jan 20, 2012 at 2:37 PM, Max Vlasov <max.vla...@gmail.com> wrote:

> On Fri, Jan 20, 2012 at 10:05 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > The change which results in a slow down occurred between 3.7.5.0 and
> > 3.7.6.0.
> >
> >
> What about EXPLAIN difference? Or just outputs of this prefix from both
> versions?
>
> Max
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

The change which results in a slow down occurred between 3.7.5.0 and
3.7.6.0.

On Fri, Jan 20, 2012 at 8:34 AM, John Elrick <john.elr...@fenestra.com>wrote:

> Thank you sir.  I'll start looking.
>
>
> On Fri, Jan 20, 2012 at 8:27 AM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On Fri, Jan 20, 2012 at 8:14 AM, John Elrick <john.elr...@fenestra.com
>> >wrote:
>>
>> > I asked in an earlier posting if the amalgamations were available
>> > somewhere.
>> >
>>
>>
>>
>> http://www.sqlite.org/sqlite-amalgamation-.zip  where  is one of:
>>
>>  3071000
>>  3070900
>>  3070800
>>  3070700
>>  3070603
>>  3070602
>>  3070601
>>  3070600
>>  3070500
>>  3070400
>>  3_7_3
>>  3_7_2
>>  3_7_1
>>  3_7_0
>>  3_6_23_1
>>  3_6_23
>>  3_6_22
>>  3_6_21
>>  3_6_20
>>  3_6_19
>>  3_6_18
>>  3_6_17
>>  3_6_16
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> John Elrick
> Fenestra Technologies
> 540-868-1377
>
>


-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

Thank you sir.  I'll start looking.

On Fri, Jan 20, 2012 at 8:27 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Fri, Jan 20, 2012 at 8:14 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > I asked in an earlier posting if the amalgamations were available
> > somewhere.
> >
>
>
>
> http://www.sqlite.org/sqlite-amalgamation-.zip  where  is one of:
>
>  3071000
>  3070900
>  3070800
>  3070700
>  3070603
>  3070602
>  3070601
>  3070600
>  3070500
>  3070400
>  3_7_3
>  3_7_2
>  3_7_1
>  3_7_0
>  3_6_23_1
>  3_6_23
>  3_6_22
>  3_6_21
>  3_6_20
>  3_6_19
>  3_6_18
>  3_6_17
>  3_6_16
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

I asked in an earlier posting if the amalgamations were available
somewhere.  Since C isn't my primary language and I'm building on Windows,
I'd prefer to have the simplest possible route to recreating.  But yes,
that is precisely what I wanted to do in the beginning to identify the
point where everything changed.

On Thu, Jan 19, 2012 at 11:37 PM, Max Vlasov <max.vla...@gmail.com> wrote:

> John, another suggestion
>
> Can you test previous sqlite versions one by one towards older ones with
> one of your problem queries until the results are "good" again and send
> both good and bad EXPLAIN QUERY output here. I'm sure this will be greek
> for most of us :), but when Richard or Dan look at the difference in the
> vdbe code, they could notice something
>
> Max
>
> On Fri, Jan 20, 2012 at 1:17 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > One more useful comparison.  The following query is prepared and step is
> > called with these results:
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

One more useful comparison.  The following query is prepared and step is
called with these results:

step: UPDATE RESPONSES SET
RESPONSE_NAME = :RESPONSE_NAME
WHERE RESPONSE_OID = :RESPONSE_OID

Cumulative Allocated Memory:   10,992
Count of _malloc Calls:18
Cumulative Reallocated Memory: 0
Cumulative Reallocated Memory where nil:   0
Count of _realloc Calls:   0
Count of _realloc Calls where nil: 0
Count of _free Calls:  18
Cumulative _mallocs by size
<= 1kb:1,704 bytes (9 count)
1kb to 4kb:9,288 bytes (9 count)
4bk to 8kb:0 bytes (0 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 0 bytes (0 count)
Cumulative _reallocs by size
<= 1kb:0 bytes (0 count)
1kb to 4kb:0 bytes (0 count)
4bk to 8kb:0 bytes (0 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 0 bytes (0 count)
Cumulative _reallocs of nil pointers by size
<= 1kb:0 bytes (0 count)
1kb to 4kb:0 bytes (0 count)
4bk to 8kb:0 bytes (0 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 0 bytes (0 count)

step is called on the exact same prepared query 0.201 seconds later:

Cumulative Allocated Memory:   439,424
Count of _malloc Calls:2,248
Cumulative Reallocated Memory: 5,080
Cumulative Reallocated Memory where nil:   0
Count of _realloc Calls:   23
Count of _realloc Calls where nil: 0
Count of _free Calls:  2,246
Cumulative _mallocs by size
<= 1kb:175,160 bytes (2,047 count)
1kb to 4kb:264,264 bytes (201 count)
4bk to 8kb:0 bytes (0 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 0 bytes (0 count)
Cumulative _reallocs by size
<= 1kb:5,080 bytes (23 count)
1kb to 4kb:0 bytes (0 count)
4bk to 8kb:0 bytes (0 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 0 bytes (0 count)
Cumulative _reallocs of nil pointers by size
<= 1kb:0 bytes (0 count)
1kb to 4kb:0 bytes (0 count)
4bk to 8kb:0 bytes (0 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Thu, Jan 19, 2012 at 11:49 AM, John Elrick <john.elr...@fenestra.com>wrote:

> I've added more tracking to our libraries.  Most queries result in minimal
> (<200 calls) _malloc activity, But I have found several anomalies.  I am
> listing some representatives below.  In the examples below, "step" is the
> operation.  Note also that all queries should have been prepared with
> sqlite3_prepare_v2 before step is invoked.
>


A few points I forgot to mention:

1. Each of these snapshots are deltas in the values before the operation
and after.  So, using Example 3, the delta in cumulative malloc'd memory
was 459,576 bytes and there were 2,323 calls to _malloc for this call to
sqlite3_step.
2. Each of these is ONE operation, not a cumulative for all executions of
that query
3. Example 3 represents an insert operation.  I should add example values
which would be inserted:

:QUESTIONNAIRE_OID: Integer
:QUESTIONNAIRE_NAME: string 9 characters long
:definition_parent: Integer
:instance_parent: Integer
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

I've added more tracking to our libraries.  Most queries result in minimal
(<200 calls) _malloc activity, But I have found several anomalies.  I am
listing some representatives below.  In the examples below, "step" is the
operation.  Note also that all queries should have been prepared with
sqlite3_prepare_v2 before step is invoked.


step: select distinct FORM_DEFINITIONS.FORM_DEFINITION_OID from
FORM_DEFINITIONS, SURVEYS where SURVEYS.SURVEY_OID=:SURVEYS_SURVEY_OID and
FORM_DEFINITIONS.FORM_DEFINITION_OID=:FORM_DEFINITIONS_FORM_DEFINITION_OID
and FORM_DEFINITIONS.definition_parent=SURVEYS.SURVEY_OID order by
FORM_DEFINITIONS.rowid


Cumulative Allocated Memory:   430,208
Count of _malloc Calls:2,255
Cumulative Reallocated Memory: 5,632
Cumulative Reallocated Memory where nil:   0
Count of _realloc Calls:   22
Count of _realloc Calls where nil: 0
Count of _free Calls:  2,258
Cumulative _mallocs by size
<= 1kb:175,232 bytes (2,063 count)
1kb to 4kb:254,976 bytes (192 count)
4bk to 8kb:0 bytes (0 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 0 bytes (0 count)
Cumulative _reallocs by size
<= 1kb:5,632 bytes (22 count)
1kb to 4kb:0 bytes (0 count)
4bk to 8kb:0 bytes (0 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 0 bytes (0 count)
Cumulative _reallocs of nil pointers by size
<= 1kb:0 bytes (0 count)
1kb to 4kb:0 bytes (0 count)
4bk to 8kb:0 bytes (0 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 0 bytes (0 count)


Here is another anomaly:

step: insert   into background_constraints values (
:sequence_instance_oid , :sequence_definition_oid )

Cumulative Allocated Memory:   426,240
Count of _malloc Calls:2,221
Cumulative Reallocated Memory: 4,520
Cumulative Reallocated Memory where nil:   0
Count of _realloc Calls:   15
Count of _realloc Calls where nil: 0
Count of _free Calls:  2,233
Cumulative _mallocs by size
<= 1kb:171,264 bytes (2,029 count)
1kb to 4kb:254,976 bytes (192 count)
4bk to 8kb:0 bytes (0 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 0 bytes (0 count)
Cumulative _reallocs by size
<= 1kb:4,520 bytes (15 count)
1kb to 4kb:0 bytes (0 count)
4bk to 8kb:0 bytes (0 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Thu, Jan 19, 2012 at 8:25 AM, John Elrick <john.elr...@fenestra.com>wrote:

>
>
> On Thu, Jan 19, 2012 at 8:15 AM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On Thu, Jan 19, 2012 at 8:10 AM, John Elrick <john.elr...@fenestra.com
>> >wrote:
>>
>> >
>> > I've been reading through the code.  Do I understand correctly that if
>> one
>> > prepared statement binding is changed in such a way as may influence the
>> > choice of the query plan that all the prepared statements for that
>> database
>> > connection are flagged for being re-prepared?
>> >
>> >
>> No. Only the one prepared statement whose binding changed is reprepared.
>>
>
>
> Thanks.  Now this becomes weirder.  I put breakpoints on all the locations
> you mentioned and they were activated precisely as expected.  When the
> initial database is created, the breakpoints hit, but are not hit during
> the actual load process.  Yet, the first while condition in sqlite3_step:
>
>   while( (rc = sqlite3Step(v))==SQLITE_SCHEMA
>  && cnt++ < SQLITE_MAX_SCHEMA_RETRY
>  && (rc2 = rc = sqlite3Reprepare(v))==SQLITE_OK ){
> sqlite3_reset(pStmt);
> assert( v->expired==0 );
>   }
>
> is triggering sqlite3Reprepare like clockwork.  So, given that none of the
> listed conditions are occurring, what else could be causing sqlite3Step to
> return SQLITE_SCHEMA?
>

Ok.  It's NOT triggering sqlite3Reprepare like clockwork.  I added a
function to allow me to check the return value and sqlite3Reprepare is
being called an appropriate number of times.  So we're back to the original
question, "why is sqlite3Parser calling _malloc so many times?"  I have an
idea of how to determine if any specific queries are to blame, but I have
to work on something unrelated this morning.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Thu, Jan 19, 2012 at 8:15 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Thu, Jan 19, 2012 at 8:10 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> >
> > I've been reading through the code.  Do I understand correctly that if
> one
> > prepared statement binding is changed in such a way as may influence the
> > choice of the query plan that all the prepared statements for that
> database
> > connection are flagged for being re-prepared?
> >
> >
> No. Only the one prepared statement whose binding changed is reprepared.
>


Thanks.  Now this becomes weirder.  I put breakpoints on all the locations
you mentioned and they were activated precisely as expected.  When the
initial database is created, the breakpoints hit, but are not hit during
the actual load process.  Yet, the first while condition in sqlite3_step:

  while( (rc = sqlite3Step(v))==SQLITE_SCHEMA
 && cnt++ < SQLITE_MAX_SCHEMA_RETRY
 && (rc2 = rc = sqlite3Reprepare(v))==SQLITE_OK ){
sqlite3_reset(pStmt);
assert( v->expired==0 );
  }

is triggering sqlite3Reprepare like clockwork.  So, given that none of the
listed conditions are occurring, what else could be causing sqlite3Step to
return SQLITE_SCHEMA?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Thu, Jan 19, 2012 at 8:03 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Thu, Jan 19, 2012 at 7:51 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > Interesting:
> >
> > 1. There are no database schema changes occurring after the system is
> fully
> > initialized
> > 2. There are no ATTACHed databases (we checked that earlier)
> > 3. There are no calls to VACUUM
> > 4. As far as I am aware, there are no changes to any callbacks after the
> > system is fully initialized.
> >
> > I believe your earlier note is pointing us in the right direction.
> > Examining my call stacks, it appears that the query is being reparsed on
> > every call to step.  The question is, why?  Did any of these conditions
> > change between 3.6.17 and 3.7.9?  Maybe we are doing something that is
> > unintentionally triggering the re-parse which did not do so in the
> earlier
> > version.
> >
> >
> Also:  Changing a bound parameter on the right-hand side of a LIKE or GLOB
> operator forces a reprepare of that one statement, so that the query
> optimizer can determine if the new value meets certain criteria for
> optimization.
>
> Try setting a breakpoint on sqlite3ExpirePreparedStatements() to see what
> you find.  Also, on these lines to check for statement expiration due to
> variable rebinding:
>
>http://www.sqlite.org/src/artifact/3662b6a468a2?ln=1024
>http://www.sqlite.org/src/artifact/3662b6a468a2?ln=129
>
>
>

I've been reading through the code.  Do I understand correctly that if one
prepared statement binding is changed in such a way as may influence the
choice of the query plan that all the prepared statements for that database
connection are flagged for being re-prepared?


SQLITE_PRIVATE void sqlite3ExpirePreparedStatements(sqlite3 *db){
  Vdbe *p;
  for(p = db->pVdbe; p; p=p->pNext){
p->expired = 1;
  }
}




>
>
>
>
> >  On Thu, Jan 19, 2012 at 7:45 AM, Richard Hipp <d...@sqlite.org> wrote:
> >
> > > On Thu, Jan 19, 2012 at 7:41 AM, John Elrick <john.elr...@fenestra.com
> > > >wrote:
> > >
> > > > Question:
> > > >
> > > > If a query has already been prepared with sqlite3_prepare_v2, why
> would
> > > > sqlite3_step need to call sqlite3Prepare, which in turn calls
> > > > sqlite3RunParser?
> > > >
> > >
> > > Because the database schema changed.  Or because you ran ATTACH or
> > VACUUM,
> > > either of which could potential change the bytecode necessary to run
> the
> > > statement.  Or, because you changed the authorization callback.
> > >
> > >
> > >
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > >
> > >
> > >
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > John Elrick
> > Fenestra Technologies
> > 540-868-1377
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

Interesting:

1. There are no database schema changes occurring after the system is fully
initialized
2. There are no ATTACHed databases (we checked that earlier)
3. There are no calls to VACUUM
4. As far as I am aware, there are no changes to any callbacks after the
system is fully initialized.

I believe your earlier note is pointing us in the right direction.
Examining my call stacks, it appears that the query is being reparsed on
every call to step.  The question is, why?  Did any of these conditions
change between 3.6.17 and 3.7.9?  Maybe we are doing something that is
unintentionally triggering the re-parse which did not do so in the earlier
version.

On Thu, Jan 19, 2012 at 7:45 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Thu, Jan 19, 2012 at 7:41 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > Question:
> >
> > If a query has already been prepared with sqlite3_prepare_v2, why would
> > sqlite3_step need to call sqlite3Prepare, which in turn calls
> > sqlite3RunParser?
> >
>
> Because the database schema changed.  Or because you ran ATTACH or VACUUM,
> either of which could potential change the bytecode necessary to run the
> statement.  Or, because you changed the authorization callback.
>
>
>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

Question:

If a query has already been prepared with sqlite3_prepare_v2, why would
sqlite3_step need to call sqlite3Prepare, which in turn calls
sqlite3RunParser?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Wed, Jan 18, 2012 at 4:32 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Jan 18, 2012 at 4:30 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > I made one more stab and narrowed down the mallocs to this call:
> >
> > Count:119,133 = 'sqlite3Parser_lt_YYNSTATEpYYNRULE(12)
> > sqlite3Parser(11)
> > sqlite3RunParser(10)
> > sqlite3Prepare(9)
> > sqlite3_prepare(8)
> > sqlite3_exec_inner_loop_invoke_callback(7)
> > sqlite3_exec_outer_while(6)
> > sqlite3_exec(5)
> > sqlite3Parser_lt_YYNSTATEpYYNRULE(4)
> > sqlite3Parser(3)
> > sqlite3RunParser(2)
> > sqlite3Prepare(1)
> > sqlite3_step(0)'
> >
> > /**CS_ID**/
> fenestra_sqlite3_push(csid_sqlite3Parser_lt_YYNSTATEpYYNRULE);
> >  yy_reduce(yypParser,yyact-YYNSTATE);
> > /**CS_ID**/ fenestra_sqlite3_pop(csid_sqlite3Parser_lt_YYNSTATEpYYNRULE);
> >
> > I started looking at yy_reduce, but it looks machine generated.  If you
> > give me some direction I'll see if I can narrow it down further.
> >
>
> Can you print out the SQL that is being parsed when this malloc is called?
>
>

I'll try to get some examples tomorrow.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

I made one more stab and narrowed down the mallocs to this call:

Count:119,133 = 'sqlite3Parser_lt_YYNSTATEpYYNRULE(12)
sqlite3Parser(11)
sqlite3RunParser(10)
sqlite3Prepare(9)
sqlite3_prepare(8)
sqlite3_exec_inner_loop_invoke_callback(7)
sqlite3_exec_outer_while(6)
sqlite3_exec(5)
sqlite3Parser_lt_YYNSTATEpYYNRULE(4)
sqlite3Parser(3)
sqlite3RunParser(2)
sqlite3Prepare(1)
sqlite3_step(0)'

/**CS_ID**/ fenestra_sqlite3_push(csid_sqlite3Parser_lt_YYNSTATEpYYNRULE);
  yy_reduce(yypParser,yyact-YYNSTATE);
/**CS_ID**/ fenestra_sqlite3_pop(csid_sqlite3Parser_lt_YYNSTATEpYYNRULE);

I started looking at yy_reduce, but it looks machine generated.  If you
give me some direction I'll see if I can narrow it down further.



On Wed, Jan 18, 2012 at 4:02 PM, John Elrick <john.elr...@fenestra.com>wrote:

> Whatever is happening, it appears I can only reproduce it under these
> circumstances.  I'll continue digging into the procedure tomorrow to see if
> I can narrow down where this is coming from.
>
>
> On Wed, Jan 18, 2012 at 3:54 PM, John Elrick <john.elr...@fenestra.com>wrote:
>
>> FYI, I traced the number of calls to sqlite3ResetInternalSchema() on a
>> smaller set of data.  It appears that it isn't the culprit.
>>
>> Total mallocs: 148,156
>> Total calls to sqlite3ResetInternalSchema(): 63
>>
>>
>>
>> On Wed, Jan 18, 2012 at 3:41 PM, John Elrick <john.elr...@fenestra.com>wrote:
>>
>>> What else could trigger a call to sqlite3ResetInternalSchema()?  I'm
>>> getting a clear breakpoint tracing back to such innocuous calls as
>>> _sqlite3_step.
>>>
>>>
>>>
>>>
>>> On Wed, Jan 18, 2012 at 3:31 PM, Richard Hipp <d...@sqlite.org> wrote:
>>>
>>>> On Wed, Jan 18, 2012 at 3:12 PM, John Elrick <john.elr...@fenestra.com
>>>> >wrote:
>>>>
>>>> >
>>>> > Total times _malloc called in test: 69,859,114
>>>> >
>>>> > Times calling _malloc: 57,679,282
>>>> > sqlite3Parser(10)
>>>> > sqlite3RunParser(9)
>>>> > sqlite3Prepare(8)
>>>> > sqlite3_prepare(7)
>>>> > sqlite3_exec_inner_loop_invoke_callback(6)
>>>> > sqlite3_exec_outer_while(5)
>>>> > sqlite3_exec(4)
>>>> > sqlite3Parser(3)
>>>> > sqlite3RunParser(2)
>>>> > sqlite3Prepare(1)
>>>> > sqlite3_step(0
>>>> >
>>>>
>>>> It looks like you might be doing something that is forcing SQLite to
>>>> constantly reparse the schema.
>>>>
>>>>  *  CREATE or DROP statements
>>>>  *  Registering new collating sequences
>>>>  *  Registering new application-defined functions
>>>>  *  ATTACH
>>>>  *  VACUUM
>>>>
>>>> Maybe set a breakpoint on sqlite3ResetInternalSchema() to find out what
>>>> is
>>>> making the schema be reparsed so much.
>>>>
>>>>
>>>> >
>>>> > Times calling _malloc: 2,775,682
>>>> > sqlite3RunParser(9)
>>>> > sqlite3Prepare(8)
>>>> > sqlite3_prepare(7)
>>>> > sqlite3_exec_inner_loop_invoke_callback(6)
>>>> > sqlite3_exec_outer_while(5)
>>>> > sqlite3_exec(4)
>>>> > sqlite3Parser(3)
>>>> > sqlite3RunParser(2)
>>>> > sqlite3Prepare(1)
>>>> > sqlite3_step(0)
>>>> >
>>>> > Times calling _malloc: 2,775,682
>>>> > sqlite3Prepare(8)
>>>> > sqlite3_prepare(7)
>>>> > sqlite3_exec_inner_loop_invoke_callback(6)
>>>> > sqlite3_exec_outer_while(5)
>>>> > sqlite3_exec(4)
>>>> > sqlite3Parser(3)
>>>> > sqlite3RunParser(2)
>>>> > sqlite3Prepare(1)
>>>> > sqlite3_step(0)
>>>> >
>>>> > Times calling _malloc: 2,092,350
>>>> > sqlite3Parser(3)
>>>> > sqlite3RunParser(2)
>>>> > sqlite3Prepare(1)
>>>> > sqlite3_step(0)
>>>> > ___
>>>> > sqlite-users mailing list
>>>> > sqlite-users@sqlite.org
>>>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>> >
>>>>
>>>>
>>>>
>>>> --
>>>> D. Richard Hipp
>>>> d...@sqlite.org
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@sqlite.org
>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>
>>>
>>>
>>> --
>>> John Elrick
>>> Fenestra Technologies
>>> 540-868-1377
>>>
>>>
>>
>>
>> --
>> John Elrick
>> Fenestra Technologies
>> 540-868-1377
>>
>>
>
>
> --
> John Elrick
> Fenestra Technologies
> 540-868-1377
>
>


-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

Whatever is happening, it appears I can only reproduce it under these
circumstances.  I'll continue digging into the procedure tomorrow to see if
I can narrow down where this is coming from.

On Wed, Jan 18, 2012 at 3:54 PM, John Elrick <john.elr...@fenestra.com>wrote:

> FYI, I traced the number of calls to sqlite3ResetInternalSchema() on a
> smaller set of data.  It appears that it isn't the culprit.
>
> Total mallocs: 148,156
> Total calls to sqlite3ResetInternalSchema(): 63
>
>
>
> On Wed, Jan 18, 2012 at 3:41 PM, John Elrick <john.elr...@fenestra.com>wrote:
>
>> What else could trigger a call to sqlite3ResetInternalSchema()?  I'm
>> getting a clear breakpoint tracing back to such innocuous calls as
>> _sqlite3_step.
>>
>>
>>
>>
>> On Wed, Jan 18, 2012 at 3:31 PM, Richard Hipp <d...@sqlite.org> wrote:
>>
>>> On Wed, Jan 18, 2012 at 3:12 PM, John Elrick <john.elr...@fenestra.com
>>> >wrote:
>>>
>>> >
>>> > Total times _malloc called in test: 69,859,114
>>> >
>>> > Times calling _malloc: 57,679,282
>>> > sqlite3Parser(10)
>>> > sqlite3RunParser(9)
>>> > sqlite3Prepare(8)
>>> > sqlite3_prepare(7)
>>> > sqlite3_exec_inner_loop_invoke_callback(6)
>>> > sqlite3_exec_outer_while(5)
>>> > sqlite3_exec(4)
>>> > sqlite3Parser(3)
>>> > sqlite3RunParser(2)
>>> > sqlite3Prepare(1)
>>> > sqlite3_step(0
>>> >
>>>
>>> It looks like you might be doing something that is forcing SQLite to
>>> constantly reparse the schema.
>>>
>>>  *  CREATE or DROP statements
>>>  *  Registering new collating sequences
>>>  *  Registering new application-defined functions
>>>  *  ATTACH
>>>  *  VACUUM
>>>
>>> Maybe set a breakpoint on sqlite3ResetInternalSchema() to find out what
>>> is
>>> making the schema be reparsed so much.
>>>
>>>
>>> >
>>> > Times calling _malloc: 2,775,682
>>> > sqlite3RunParser(9)
>>> > sqlite3Prepare(8)
>>> > sqlite3_prepare(7)
>>> > sqlite3_exec_inner_loop_invoke_callback(6)
>>> > sqlite3_exec_outer_while(5)
>>> > sqlite3_exec(4)
>>> > sqlite3Parser(3)
>>> > sqlite3RunParser(2)
>>> > sqlite3Prepare(1)
>>> > sqlite3_step(0)
>>> >
>>> > Times calling _malloc: 2,775,682
>>> > sqlite3Prepare(8)
>>> > sqlite3_prepare(7)
>>> > sqlite3_exec_inner_loop_invoke_callback(6)
>>> > sqlite3_exec_outer_while(5)
>>> > sqlite3_exec(4)
>>> > sqlite3Parser(3)
>>> > sqlite3RunParser(2)
>>> > sqlite3Prepare(1)
>>> > sqlite3_step(0)
>>> >
>>> > Times calling _malloc: 2,092,350
>>> > sqlite3Parser(3)
>>> > sqlite3RunParser(2)
>>> > sqlite3Prepare(1)
>>> > sqlite3_step(0)
>>> > ___
>>> > sqlite-users mailing list
>>> > sqlite-users@sqlite.org
>>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> >
>>>
>>>
>>>
>>> --
>>> D. Richard Hipp
>>> d...@sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> John Elrick
>> Fenestra Technologies
>> 540-868-1377
>>
>>
>
>
> --
> John Elrick
> Fenestra Technologies
> 540-868-1377
>
>


-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

FYI, I traced the number of calls to sqlite3ResetInternalSchema() on a
smaller set of data.  It appears that it isn't the culprit.

Total mallocs: 148,156
Total calls to sqlite3ResetInternalSchema(): 63


On Wed, Jan 18, 2012 at 3:41 PM, John Elrick <john.elr...@fenestra.com>wrote:

> What else could trigger a call to sqlite3ResetInternalSchema()?  I'm
> getting a clear breakpoint tracing back to such innocuous calls as
> _sqlite3_step.
>
>
>
>
> On Wed, Jan 18, 2012 at 3:31 PM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On Wed, Jan 18, 2012 at 3:12 PM, John Elrick <john.elr...@fenestra.com
>> >wrote:
>>
>> >
>> > Total times _malloc called in test: 69,859,114
>> >
>> > Times calling _malloc: 57,679,282
>> > sqlite3Parser(10)
>> > sqlite3RunParser(9)
>> > sqlite3Prepare(8)
>> > sqlite3_prepare(7)
>> > sqlite3_exec_inner_loop_invoke_callback(6)
>> > sqlite3_exec_outer_while(5)
>> > sqlite3_exec(4)
>> > sqlite3Parser(3)
>> > sqlite3RunParser(2)
>> > sqlite3Prepare(1)
>> > sqlite3_step(0
>> >
>>
>> It looks like you might be doing something that is forcing SQLite to
>> constantly reparse the schema.
>>
>>  *  CREATE or DROP statements
>>  *  Registering new collating sequences
>>  *  Registering new application-defined functions
>>  *  ATTACH
>>  *  VACUUM
>>
>> Maybe set a breakpoint on sqlite3ResetInternalSchema() to find out what is
>> making the schema be reparsed so much.
>>
>>
>> >
>> > Times calling _malloc: 2,775,682
>> > sqlite3RunParser(9)
>> > sqlite3Prepare(8)
>> > sqlite3_prepare(7)
>> > sqlite3_exec_inner_loop_invoke_callback(6)
>> > sqlite3_exec_outer_while(5)
>> > sqlite3_exec(4)
>> > sqlite3Parser(3)
>> > sqlite3RunParser(2)
>> > sqlite3Prepare(1)
>> > sqlite3_step(0)
>> >
>> > Times calling _malloc: 2,775,682
>> > sqlite3Prepare(8)
>> > sqlite3_prepare(7)
>> > sqlite3_exec_inner_loop_invoke_callback(6)
>> > sqlite3_exec_outer_while(5)
>> > sqlite3_exec(4)
>> > sqlite3Parser(3)
>> > sqlite3RunParser(2)
>> > sqlite3Prepare(1)
>> > sqlite3_step(0)
>> >
>> > Times calling _malloc: 2,092,350
>> > sqlite3Parser(3)
>> > sqlite3RunParser(2)
>> > sqlite3Prepare(1)
>> > sqlite3_step(0)
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> John Elrick
> Fenestra Technologies
> 540-868-1377
>
>


-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

What else could trigger a call to sqlite3ResetInternalSchema()?  I'm
getting a clear breakpoint tracing back to such innocuous calls as
_sqlite3_step.



On Wed, Jan 18, 2012 at 3:31 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Jan 18, 2012 at 3:12 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> >
> > Total times _malloc called in test: 69,859,114
> >
> > Times calling _malloc: 57,679,282
> > sqlite3Parser(10)
> > sqlite3RunParser(9)
> > sqlite3Prepare(8)
> > sqlite3_prepare(7)
> > sqlite3_exec_inner_loop_invoke_callback(6)
> > sqlite3_exec_outer_while(5)
> > sqlite3_exec(4)
> > sqlite3Parser(3)
> > sqlite3RunParser(2)
> > sqlite3Prepare(1)
> > sqlite3_step(0
> >
>
> It looks like you might be doing something that is forcing SQLite to
> constantly reparse the schema.
>
>  *  CREATE or DROP statements
>  *  Registering new collating sequences
>  *  Registering new application-defined functions
>  *  ATTACH
>  *  VACUUM
>
> Maybe set a breakpoint on sqlite3ResetInternalSchema() to find out what is
> making the schema be reparsed so much.
>
>
> >
> > Times calling _malloc: 2,775,682
> > sqlite3RunParser(9)
> > sqlite3Prepare(8)
> > sqlite3_prepare(7)
> > sqlite3_exec_inner_loop_invoke_callback(6)
> > sqlite3_exec_outer_while(5)
> > sqlite3_exec(4)
> > sqlite3Parser(3)
> > sqlite3RunParser(2)
> > sqlite3Prepare(1)
> > sqlite3_step(0)
> >
> > Times calling _malloc: 2,775,682
> > sqlite3Prepare(8)
> > sqlite3_prepare(7)
> > sqlite3_exec_inner_loop_invoke_callback(6)
> > sqlite3_exec_outer_while(5)
> > sqlite3_exec(4)
> > sqlite3Parser(3)
> > sqlite3RunParser(2)
> > sqlite3Prepare(1)
> > sqlite3_step(0)
> >
> > Times calling _malloc: 2,092,350
> > sqlite3Parser(3)
> > sqlite3RunParser(2)
> > sqlite3Prepare(1)
> > sqlite3_step(0)
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

I could not use any of the proffered tools for several reasons.  After an
inspiration yesterday, I dusted off my C programming skills and started
hacking a pseudo-call stack system into SQLite3.c.  I've put the statistics
below for the top couple of call stacks.

Total times _malloc called in test: 69,859,114

Times calling _malloc: 57,679,282
sqlite3Parser(10)
sqlite3RunParser(9)
sqlite3Prepare(8)
sqlite3_prepare(7)
sqlite3_exec_inner_loop_invoke_callback(6)
sqlite3_exec_outer_while(5)
sqlite3_exec(4)
sqlite3Parser(3)
sqlite3RunParser(2)
sqlite3Prepare(1)
sqlite3_step(0

Times calling _malloc: 2,775,682
sqlite3RunParser(9)
sqlite3Prepare(8)
sqlite3_prepare(7)
sqlite3_exec_inner_loop_invoke_callback(6)
sqlite3_exec_outer_while(5)
sqlite3_exec(4)
sqlite3Parser(3)
sqlite3RunParser(2)
sqlite3Prepare(1)
sqlite3_step(0)

Times calling _malloc: 2,775,682
sqlite3Prepare(8)
sqlite3_prepare(7)
sqlite3_exec_inner_loop_invoke_callback(6)
sqlite3_exec_outer_while(5)
sqlite3_exec(4)
sqlite3Parser(3)
sqlite3RunParser(2)
sqlite3Prepare(1)
sqlite3_step(0)

Times calling _malloc: 2,092,350
sqlite3Parser(3)
sqlite3RunParser(2)
sqlite3Prepare(1)
sqlite3_step(0)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Mon, Jan 16, 2012 at 8:05 PM, John Elrick <john.elr...@fenestra.com>wrote:

>
>
> On Mon, Jan 16, 2012 at 5:31 PM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On Mon, Jan 16, 2012 at 5:13 PM, John Elrick <john.elr...@fenestra.com
>> >wrote:
>>
>> > I did this trace through the Delphi IDE and hand copied the call stack.
>> >  Unfortunately, I can't give you line numbers, although I could go back
>> and
>> > give you hex offsets from the start of the procedures.  These are
>> literally
>> > the first seven call stacks from _malloc after I initiate the trace.
>>  I'm
>> > noticing a pattern.  Hopefully 1) the information from the IDE is
>> accurate
>> > and 2) this helps.  The trace reads from bottom to top.
>> >
>>
>> I do not think the information below is correct.  For example, at no point
>> does sqlite3_db_status() ever call malloc().  Similarly, sqlite3_free()
>> never calls sqlite3_db_status().  And really - why would sqlite3_free()
>> ever call malloc() - that makes no sense.
>>
>> I'm thinking the Delphi IDE is not giving you good information here, which
>> is too bad since we could have really used the information if it had been
>> good.
>>
>> Are there any options to Delphi that can give you better diagnostics?  Do
>> you need to recompile SQLite without optimization and including the -g
>> option?
>>
>>
> I can try that approach with BCC.   I was concerned that the IDE would be
> getting inaccurate information from the .OBJ.  Crossing into a pre-complied
> library can be tricky.
>


I attempted all of the above and the call stacks do not change.  That leads
me to one of two conclusions:

1.  The call stacks are inaccurate
2.  There is some side effect which is causing malloc to be called inside
of a procedure which should not be calling malloc.

I've posted an update to my mistaken realloc listing.  I may have to take a
break to see if I can think of another approach to the problem.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Mon, Jan 16, 2012 at 5:36 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Jan 16, 2012 at 5:31 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
>
SNIP


> This is very curious and perhaps a useful clue.  SQLite does call
>

SNIP

It is a clue that I need a break.  While updating the counters to allow for
nil pointers, I noticed I had done something stupid in the accumulators for
realloc.  Fixed below:

Application:   Surveyor.exe
Sqlite version:3.7.9
Date/Time: 01/17/2012 08:17:22
Memory Used:   7,905,536 (max 8,582,104) bytes
Page Cache Used:   0 (max 0) pages
Page Cache Overflow:   6,910,576 (max 6,911,600) bytes
Number of Scratch Allocations Used:0 (max 0)
Scratch Overflow:  0 (max 24,880) bytes
Largest Allocation:500,042 bytes
Parser Stack:  0
Largest Pcache Allocation: 4,232 bytes
Largest Scratch Allocation:24,880 bytes
Number of Outstanding Allocations: 5,835 (max 6,499)
Lookaside Slots Used:  500 (max 500)
Pager Heap Usage:  6,873,408 bytes
Schema Heap Usage: 39,560 bytes
Statement Heap/Lookaside Usage:806,592 bytes
Successful lookaside attempts: 4,888
Lookaside failures due to size:3,782,307
Lookaside failures due to OOM: 4,090,019
Page cache hits:   2,585,425
Page cache misses: 0
Cumulative Allocated Memory:   932,315,928
Count of _malloc Calls:69,859,114
Cumulative Reallocated Memory: 226,385,120
Cumulative Reallocated Memory where nil:   0
Count of _realloc Calls:   776,372
Count of _realloc Calls where nil: 0
Count of _free Calls:  69,857,292
Cumulative _mallocs by size
<= 1kb:1,118,058,528 bytes (63,640,300
count)
1kb to 4kb:3,836,490,296 bytes (6,197,060
count)
4bk to 8kb:47,800,672 bytes (11,571 count)
8bk to 16kb:   597,016 bytes (46 count)
16bk to 32kb:  224,272,704 bytes (10,136 count)
32bk to 64kb:  64,008 bytes (1 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 0 bytes (0 count)
Cumulative _reallocs by size
<= 1kb:189,468,416 bytes (759,348 count)
1kb to 4kb:36,904,328 bytes (17,022 count)
4bk to 8kb:12,376 bytes (2 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 0 bytes (0 count)
Cumulative _reallocs of nil pointers by size
<= 1kb:0 bytes (0 count)
1kb to 4kb:0 bytes (0 count)
4bk to 8kb:0 bytes (0 count)
8bk to 16kb:   0 bytes (0 count)
16bk to 32kb:  0 bytes (0 count)
32bk to 64kb:  0 bytes (0 count)
64bk to 128kb: 0 bytes (0 count)
128kb to 256kb:0 bytes (0 count)
256kb to 512kb:0 bytes (0 count)
512kb to 1024kb:   0 bytes (0 count)
> 1mb: 0 bytes (0 count)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Mon, Jan 16, 2012 at 11:47 PM, Max Vlasov <max.vla...@gmail.com> wrote:

> On Tue, Jan 17, 2012 at 5:05 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > >
> > I can try that approach with BCC.   I was concerned that the IDE would be
> > getting inaccurate information from the .OBJ.  Crossing into a
> pre-complied
> > library can be tricky.
> >
> >
> As I recall,I could not make debug information compiled by bcc visible in
> Delphi IDE, but everything will be ok if you make your code compatible with
> fpc/lazarus. Besides the fact that you will be able to walk inside sqlite c
> code and inspect the data (gdb is used), you will probably make your
> software cross-platform. Besides, there is a library for lazarus/fpc
> (developed by Ludo Brands and me) for inspecting stack calling paths with
> whose memory blocks are allocated (similar to Valgrind tool). By the way,
> Ludo did a great job with stack unwinding since I remember false positives
> like ones mentioned here when calling chains were very strange and he had
> to implement a sophisticated stack analysis for many real world examples
>
>
Unfortunately getting our project (circa 500,000 lines including 3rd party)
would require a massive conversion effort to compile under Lazarus.  I've
investigated it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Mon, Jan 16, 2012 at 5:31 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Jan 16, 2012 at 5:13 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > I did this trace through the Delphi IDE and hand copied the call stack.
> >  Unfortunately, I can't give you line numbers, although I could go back
> and
> > give you hex offsets from the start of the procedures.  These are
> literally
> > the first seven call stacks from _malloc after I initiate the trace.  I'm
> > noticing a pattern.  Hopefully 1) the information from the IDE is
> accurate
> > and 2) this helps.  The trace reads from bottom to top.
> >
>
> I do not think the information below is correct.  For example, at no point
> does sqlite3_db_status() ever call malloc().  Similarly, sqlite3_free()
> never calls sqlite3_db_status().  And really - why would sqlite3_free()
> ever call malloc() - that makes no sense.
>
> I'm thinking the Delphi IDE is not giving you good information here, which
> is too bad since we could have really used the information if it had been
> good.
>
> Are there any options to Delphi that can give you better diagnostics?  Do
> you need to recompile SQLite without optimization and including the -g
> option?
>
>
I can try that approach with BCC.   I was concerned that the IDE would be
getting inaccurate information from the .OBJ.  Crossing into a pre-complied
library can be tricky.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

I will adjust the realloc tests tomorrow and let you know.  The two
routines are unique in the Delphi library unit.

Thanks again for your time and efforts.

On Mon, Jan 16, 2012 at 5:36 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Jan 16, 2012 at 5:31 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> >
> > I don't know if the calls to _realloc will help also, but I've added
> > tracing for those also.  My updated output:
> > Cumulative _mallocs by size
> > <= 1kb:   1,118,058,528 bytes (63,640,300
> > count)
> > 1kb to 4kb:   3,836,490,296 bytes (6,197,060
> count)
> > 4bk to 8kb:   47,800,672 bytes (11,571 count)
> > 8bk to 16kb:  597,016 bytes (46 count)
> > 16bk to 32kb: 224,272,704 bytes (10,136 count)
> > 32bk to 64kb: 64,008 bytes (1 count)Cumulative
> > _reallocs by size
> > <= 1kb:   1,117,392,608 bytes (63,637,756
> > count)
> > 1kb to 4kb:   3,836,372,120 bytes (6,196,954
> count)
> > 4bk to 8kb:   30,800 bytes (6 count)
> > 8bk to 16kb:  0 bytes (0 count)
> >
>
> This is very curious and perhaps a useful clue.  SQLite does call
> realloc(), but not that often.  And yet here we see realloc() being called
> almost as much as malloc().  I'll investigate further on this end.  In the
> meantime, are you *certain* that the numbers above are correct?  Please
> register as a malloc() any call to realloc(A,B) where A is NULL.  With that
> change, are the numbers above still the same?
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

Richard,

I don't know if the calls to _realloc will help also, but I've added
tracing for those also.  My updated output:

Application:  Surveyor.exe
Sqlite version:   3.7.9
Date/Time:01/16/2012 17:20:58
Memory Used:  7,905,536 (max 8,582,104) bytes
Page Cache Used:  0 (max 0) pages
Page Cache Overflow:  6,910,576 (max 6,911,600) bytes
Number of Scratch Allocations Used:   0 (max 0)
Scratch Overflow: 0 (max 24,880) bytes
Largest Allocation:   500,042 bytes
Parser Stack: 0
Largest Pcache Allocation:4,232 bytes
Largest Scratch Allocation:   24,880 bytes
Number of Outstanding Allocations:5,835 (max 6,499)
Lookaside Slots Used: 500 (max 500)
Pager Heap Usage: 6,873,408 bytes
Schema Heap Usage:39,560 bytes
Statement Heap/Lookaside Usage:   806,592 bytes
Successful lookaside attempts:4,888
Lookaside failures due to size:   3,782,307
Lookaside failures due to OOM:4,090,019
Page cache hits:  2,585,425
Page cache misses:0
Cumulative Allocated Memory:  932,315,928
Count of _malloc Calls:   69,859,114
Cumulative Reallocated Memory:931,150,680
Count of _realloc Calls:  69,856,395
Count of _free Calls: 69,857,292
Cumulative _mallocs by size
<= 1kb:   1,118,058,528 bytes (63,640,300 count)
1kb to 4kb:   3,836,490,296 bytes (6,197,060 count)
4bk to 8kb:   47,800,672 bytes (11,571 count)
8bk to 16kb:  597,016 bytes (46 count)
16bk to 32kb: 224,272,704 bytes (10,136 count)
32bk to 64kb: 64,008 bytes (1 count)
64bk to 128kb:0 bytes (0 count)
128kb to 256kb:   0 bytes (0 count)
256kb to 512kb:   0 bytes (0 count)
512kb to 1024kb:  0 bytes (0 count)
> 1mb:0 bytes (0 count)
Cumulative _reallocs by size
<= 1kb:   1,117,392,608 bytes (63,637,756 count)
1kb to 4kb:   3,836,372,120 bytes (6,196,954 count)
4bk to 8kb:   30,800 bytes (6 count)
8bk to 16kb:  0 bytes (0 count)
16bk to 32kb: 0 bytes (0 count)
32bk to 64kb: 0 bytes (0 count)
64bk to 128kb:0 bytes (0 count)
128kb to 256kb:   0 bytes (0 count)
256kb to 512kb:   0 bytes (0 count)
512kb to 1024kb:  0 bytes (0 count)
> 1mb:0 bytes (0 count)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Mon, Jan 16, 2012 at 5:14 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 16 Jan 2012, at 9:37pm, John Elrick wrote:
>
> > Lacking the status interfaces for 3.6.17 I was forced to mark them as
> > "interface not supported".  Simon suggested I track the calls to _malloc,
> > so I've added extensive tracing code.
>
> Those results are excellently detailed and make no sense to me at all.
>  Fortunately there are people on this list who know the internals of SQLite
> far better than I do.  Well done for getting that diagnostic to the point
> you have.


Thanks very much.  You have no idea how much I appreciate that pat on the
back (or maybe you do...)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

I did this trace through the Delphi IDE and hand copied the call stack.
 Unfortunately, I can't give you line numbers, although I could go back and
give you hex offsets from the start of the procedures.  These are literally
the first seven call stacks from _malloc after I initiate the trace.  I'm
noticing a pattern.  Hopefully 1) the information from the IDE is accurate
and 2) this helps.  The trace reads from bottom to top.

_malloc
_sqlite3_db_status
_sqlite3_db_status
_sqlite3_db_status
_sqlite3_free
_sqlite3_backup_pagecount
_sqlite3_backup_pagecount
_sqlite3_column_decltype
_sqlite3_bind_text

_malloc
_sqlite3_db_status
_sqlite3_db_status
_sqlite3_db_status
_sqlite3_free
_sqlite3_free
_sqlite3_stmt_status
_sqlite3_stmt_status
_sqlite3_free_table
_sqlite3_free_table
_sqlite3_free_table
_sqlite3_exec
_sqlite3_exec
_sqlite3_prepare_v2

_malloc
_sqlite3_db_status
_sqlite3_db_status
_sqlite3_free
_sqlite3_stmt_status
_sqlite3_stmt_status
_sqlite3_free_table
_sqlite3_free_table
_sqlite3_free_table
_sqlite3_exec
_sqlite3_exec
_sqlite3_prepare_v2

_malloc
_sqlite3_db_status
_sqlite3_db_status
_sqlite3_free_table
_sqlite3_free_table
_sqlite3_exec
_sqlite3_exec
_sqlite3_prepare_v2

_malloc
_sqlite3_db_status
_sqlite3_db_status
_sqlite3_free
_sqlite3_free
_sqlite3_exec
_sqlite3_exec
_sqlite3_prepare_v2

_malloc
_sqlite3_db_status
_sqlite3_db_status
_sqlite3_db_status
_sqlite3_free
_sqlite3_backup_pagecount
_sqlite3_stmt_status
_sqlite3_stmt_status
_squite3_reset
_sqlite3_step

_malloc
_sqlite3_db_status
_sqlite3_db_status
_sqlite3_db_status
_sqlite3_free
_sqlite3_free
_sqlite3_free
_sqlite3_stmt_status
_sqlite3_stmt_status
_squite3_reset
_sqlite3_step
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

Addendum: This test was conducted using a full size test dataset.  I had
been using one that was an order of magnitude smaller for profiling before,
but could use the full size for this test.

On Mon, Jan 16, 2012 at 4:37 PM, John Elrick <john.elr...@fenestra.com>wrote:

> Lacking the status interfaces for 3.6.17 I was forced to mark them as
> "interface not supported".  Simon suggested I track the calls to _malloc,
> so I've added extensive tracing code.  Here is the final output from the
> two versions:
>
> Application:  Surveyor.exe
> Sqlite version:   3.6.17
> Date/Time:01/16/2012 16:32:18
> Memory Used:  interface not supported
> Page Cache Used:  interface not supported
> Page Cache Overflow:  interface not supported
> Number of Scratch Allocations Used:   interface not supported
> Scratch Overflow: interface not supported
> Largest Allocation:   interface not supported
> Parser Stack: interface not supported
> Largest Pcache Allocation:interface not supported
> Largest Scratch Allocation:   interface not supported
> Number of Outstanding Allocations:interface not supported
> Lookaside Slots Used: interface not supported
> Pager Heap Usage: interface not supported
> Schema Heap Usage:interface not supported
> Statement Heap/Lookaside Usage:   interface not supported
> Successful lookaside attempts:interface not supported
> Lookaside failures due to size:   interface not supported
> Lookaside failures due to OOM:interface not supported
> Page cache hits:  interface not supported
> Page cache misses:interface not supported
> Cumulative Allocated Memory:  892,318,312
> Count of _malloc Calls:   1,072,825
> Count of _free Calls: 1,070,971
> Cumulative _mallocs by size
> <= 1kb:   109,025,904 bytes (565,230 count)
> 1kb to 4kb:   510,357,112 bytes (485,801 count)
> 4bk to 8kb:   48,005,376 bytes (11,610 count)
> 8bk to 16kb:  605,208 bytes (47 count)
> 16bk to 32kb: 224,272,704 bytes (10,136 count)
> 32bk to 64kb: 52,008 bytes (1 count)
> 64bk to 128kb:0 bytes (0 count)
> 128kb to 256kb:   0 bytes (0 count)
> 256kb to 512kb:   0 bytes (0 count)
> 512kb to 1024kb:  0 bytes (0 count)
> > 1mb:0 bytes (0 count)
>
> Application:  Surveyor.exe
> Sqlite version:   3.7.9
> Date/Time:01/16/2012 16:35:01
> Memory Used:  7,905,536 (max 8,582,104) bytes
> Page Cache Used:  0 (max 0) pages
> Page Cache Overflow:  6,910,576 (max 6,911,600) bytes
> Number of Scratch Allocations Used:   0 (max 0)
> Scratch Overflow: 0 (max 24,880) bytes
> Largest Allocation:   500,042 bytes
> Parser Stack: 0
> Largest Pcache Allocation:4,232 bytes
> Largest Scratch Allocation:   24,880 bytes
> Number of Outstanding Allocations:5,835 (max 6,499)
> Lookaside Slots Used: 500 (max 500)
> Pager Heap Usage: 6,873,408 bytes
> Schema Heap Usage:39,560 bytes
> Statement Heap/Lookaside Usage:   806,592 bytes
> Successful lookaside attempts:4,888
> Lookaside failures due to size:   3,782,307
> Lookaside failures due to OOM:4,090,019
> Page cache hits:  2,585,425
> Page cache misses:0
> Cumulative Allocated Memory:  932,315,928
> Count of _malloc Calls:   69,859,114
> Count of _free Calls: 69,857,292
> Cumulative _mallocs by size
> <= 1kb:   1,118,058,528 bytes (63,640,300
> count)
> 1kb to 4kb:   3,836,490,296 bytes (6,197,060 count)
> 4bk to 8kb:   47,800,672 bytes (11,571 count)
> 8bk to 16kb:  597,016 bytes (46 count)
> 16bk to 32kb: 224,272,704 bytes (10,136 count)
> 32bk to 64kb: 64,008 bytes (1 count)
> 64bk to 128kb:0 bytes (0 count)
> 128kb to 256kb:   0 bytes (0 count)
> 256kb to 512kb:   

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

Lacking the status interfaces for 3.6.17 I was forced to mark them as
"interface not supported".  Simon suggested I track the calls to _malloc,
so I've added extensive tracing code.  Here is the final output from the
two versions:

Application:  Surveyor.exe
Sqlite version:   3.6.17
Date/Time:01/16/2012 16:32:18
Memory Used:  interface not supported
Page Cache Used:  interface not supported
Page Cache Overflow:  interface not supported
Number of Scratch Allocations Used:   interface not supported
Scratch Overflow: interface not supported
Largest Allocation:   interface not supported
Parser Stack: interface not supported
Largest Pcache Allocation:interface not supported
Largest Scratch Allocation:   interface not supported
Number of Outstanding Allocations:interface not supported
Lookaside Slots Used: interface not supported
Pager Heap Usage: interface not supported
Schema Heap Usage:interface not supported
Statement Heap/Lookaside Usage:   interface not supported
Successful lookaside attempts:interface not supported
Lookaside failures due to size:   interface not supported
Lookaside failures due to OOM:interface not supported
Page cache hits:  interface not supported
Page cache misses:interface not supported
Cumulative Allocated Memory:  892,318,312
Count of _malloc Calls:   1,072,825
Count of _free Calls: 1,070,971
Cumulative _mallocs by size
<= 1kb:   109,025,904 bytes (565,230 count)
1kb to 4kb:   510,357,112 bytes (485,801 count)
4bk to 8kb:   48,005,376 bytes (11,610 count)
8bk to 16kb:  605,208 bytes (47 count)
16bk to 32kb: 224,272,704 bytes (10,136 count)
32bk to 64kb: 52,008 bytes (1 count)
64bk to 128kb:0 bytes (0 count)
128kb to 256kb:   0 bytes (0 count)
256kb to 512kb:   0 bytes (0 count)
512kb to 1024kb:  0 bytes (0 count)
> 1mb:0 bytes (0 count)

Application:  Surveyor.exe
Sqlite version:   3.7.9
Date/Time:01/16/2012 16:35:01
Memory Used:  7,905,536 (max 8,582,104) bytes
Page Cache Used:  0 (max 0) pages
Page Cache Overflow:  6,910,576 (max 6,911,600) bytes
Number of Scratch Allocations Used:   0 (max 0)
Scratch Overflow: 0 (max 24,880) bytes
Largest Allocation:   500,042 bytes
Parser Stack: 0
Largest Pcache Allocation:4,232 bytes
Largest Scratch Allocation:   24,880 bytes
Number of Outstanding Allocations:5,835 (max 6,499)
Lookaside Slots Used: 500 (max 500)
Pager Heap Usage: 6,873,408 bytes
Schema Heap Usage:39,560 bytes
Statement Heap/Lookaside Usage:   806,592 bytes
Successful lookaside attempts:4,888
Lookaside failures due to size:   3,782,307
Lookaside failures due to OOM:4,090,019
Page cache hits:  2,585,425
Page cache misses:0
Cumulative Allocated Memory:  932,315,928
Count of _malloc Calls:   69,859,114
Count of _free Calls: 69,857,292
Cumulative _mallocs by size
<= 1kb:   1,118,058,528 bytes (63,640,300 count)
1kb to 4kb:   3,836,490,296 bytes (6,197,060 count)
4bk to 8kb:   47,800,672 bytes (11,571 count)
8bk to 16kb:  597,016 bytes (46 count)
16bk to 32kb: 224,272,704 bytes (10,136 count)
32bk to 64kb: 64,008 bytes (1 count)
64bk to 128kb:0 bytes (0 count)
128kb to 256kb:   0 bytes (0 count)
256kb to 512kb:   0 bytes (0 count)
512kb to 1024kb:  0 bytes (0 count)
> 1mb:0 bytes (0 count)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sql/tcl script

[John Elrick]

Ruby also.

On Mon, Jan 16, 2012 at 1:15 PM, Stephan Beal <sgb...@googlemail.com> wrote:

> On Mon, Jan 16, 2012 at 7:12 PM, Bill McCormick <wpmccorm...@gmail.com
> >wrote:
>
> > Is tcl the only scripting interface for SQLite? I'm just wondering what
> > the options are.
> >
>
>
> There are few scripting languages which don't have an sqlite3 binding. Just
> to name a few:
>
> - Perl
> - PHP
> - JavaScript (v8 and SpiderMonkey engines)
> - Python
> ...
>
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Fri, Jan 13, 2012 at 5:58 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Fri, Jan 13, 2012 at 4:49 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> >
> > 3.6.17
> >
> > Procedure   % TimeTimeCalls
> > _sqlite3_step   58.4%  1.795,052
> > _memcpy 22.8%  0.691,342,957
> > _memset  7.8%  0.24  465,299
> > ...
> > _malloc  1.9%  0.06   95,505
> >
> >
> > 3.7.9
> >
> > Procedure   % TimeTimeCalls
> > _malloc 56.9% 44.396,975,335
> > _sqlite3_step   30.4% 23.685,052
> > _memcpy  4.8%  3.707,710,259
> >
>
> Very curious
>
> SQLite has lots of interfaces that can be used to determine performance and
> status information.  Some example code from the command-line shell that
> accesses this status information is here:
> http://www.sqlite.org/src/artifact/aa4183d4a5243d?ln=990-1076
>
> I would very much like to see some of these same stats from your
> application.  Do you think you could add some code like that found in the
> shell and give us some more insight into what is going on?
>
>
Richard, per your request:

Memory Used   : 7,905,536 (max 8,582,104) bytes
Page Cache Used   : 0 (max 0) pages
Page Cache Overflow   : 6,910,576 (max 6,911,600) bytes
Scratch Used  : 0 (max 0)
Scratch Overflow  : 0 (max 24,880) bytes
Malloc Size   : 500,042 bytes
Parser Stack  : 0
Page Cache Size   : 4,232 bytes
Scratch Size  : 24,880 bytes
Malloc Count  : 5,835 (max 6,499)
Lookaside Slots Used  : 500 (max 500)
Pager Heap Usage  : 6,873,408 bytes
Schema Heap Usage : 39,560 bytes
Statement Heap/Lookaside Usage: 806,592 bytes
Successful lookaside attempts : 4,888
Lookaside failures due to size: 3,782,307
Lookaside failures due to OOM : 4,090,019
Page cache hits   : 2,585,425
Page cache misses : 0
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Mon, Jan 16, 2012 at 10:33 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Jan 16, 2012 at 10:29 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> >
> >
> > > SQLite has lots of interfaces that can be used to determine performance
> > and
> > > status information.  Some example code from the command-line shell that
> > > accesses this status information is here:
> > > http://www.sqlite.org/src/artifact/aa4183d4a5243d?ln=990-1076
> > >
> > > I would very much like to see some of these same stats from your
> > > application.  Do you think you could add some code like that found in
> the
> > > shell and give us some more insight into what is going on?
> > >
> > >
> > Richard,
> >
> > I created this code and am in the process of gathering preliminary
> > statistics.  However, I am getting a
> >
> > SQLITE_MISUSE 21 /* Library used incorrectly */
> >
> > when I attempt to use the current _sqlite3_status interface with 3.6.17.
> >  If you could tell me the interface for that version, I'll add
> conditionals
> > so we can compare the results of the two.
> >
>
> Some of the SQLITE_DB_STATUS macros have been added after 3.6.17. If you
> try to use them on 3.6.17, you'll get a MISUSE error.  Really - you ought
> to be getting a compile-time error because the symbols don't exist, but I
> bet you are using the 3.7.9 header files with the 3.6.17 library.
>
>
You're forgetting I'm using Delphi.  I have no access to the .h file.  I
have to recreate the constants in the Delphi units from the documentation
on the site.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Fri, Jan 13, 2012 at 5:58 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Fri, Jan 13, 2012 at 4:49 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>

SNIP


> SQLite has lots of interfaces that can be used to determine performance and
> status information.  Some example code from the command-line shell that
> accesses this status information is here:
> http://www.sqlite.org/src/artifact/aa4183d4a5243d?ln=990-1076
>
> I would very much like to see some of these same stats from your
> application.  Do you think you could add some code like that found in the
> shell and give us some more insight into what is going on?
>
>
Richard,

I created this code and am in the process of gathering preliminary
statistics.  However, I am getting a

SQLITE_MISUSE 21 /* Library used incorrectly */

when I attempt to use the current _sqlite3_status interface with 3.6.17.
 If you could tell me the interface for that version, I'll add conditionals
so we can compare the results of the two.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Mon, Jan 16, 2012 at 4:37 AM, Max Vlasov <max.vla...@gmail.com> wrote:

> On Sun, Jan 15, 2012 at 3:35 AM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> > On Fri, Jan 13, 2012 at 5:13 PM, Max Vlasov <max.vla...@gmail.com>
> wrote:
> >
> > 5,008 calls to
> >
> > UPDATE RESPONSES SET
> > RESPONSE_NAME = :RESPONSE_NAME,
> > prelisted_value = :prelisted_value
> > WHERE RESPONSE_OID = :RESPONSE_OID
> >
> > 3.6.17: 382 ms
> > 3.7.9: 5,924 ms
> >
> >
>
> John, I did a test reproducing your job with 8 inserts
>  insert into responses default values
>
> and making 5000 calls in transaction
>
> UPDATE RESPONSES SET RESPONSE_NAME = '12345', prelisted_value='12345'
> WHERE RESPONSE_OID = abs(random() % 8)
>
> is this test ok for you (still showing bad performance)?
>
> On my side it's about 800 ms for 3.7.9 static and about 6000 memory calls
> during the query. For 3.6.20 the number of calls ~  7000, the time is
> similar.
>
>
Your test was hard coded.  Mine uses bindings.  However, I'm not arguing
that something odd is going on.  I'm just trying to understand what.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Fri, Jan 13, 2012 at 5:13 PM, Max Vlasov  wrote:

> John,
> I read your previous post and unfortunately, your conversation with Richard
> didn't reveal much details. I worked with scenario similar tou yours
> (Delphi + statically linked sqlite) for several years and during this
> period an advanced admin/console form was developed that allows dynamical
> loading of different versions of sqlite, reporting vfs bandwidth, times and
> even the number of memory requests. So consider sharing more information
> about your db and queries here or contact me directly if you wish, I hope I
> can help at least a little.
>
> As a quick guess I remember that fts was one of the places that allocated
> heavily with some queries, but I can't recall right now.
>
>
There are 88 unique queries run against the database.  They are mostly
automatically created from a class structure.  However, without dumping the
entire database in front of you, the following query should provide a good,
isolated example:

5,008 calls to

UPDATE RESPONSES SET
RESPONSE_NAME = :RESPONSE_NAME,
prelisted_value = :prelisted_value
WHERE RESPONSE_OID = :RESPONSE_OID

3.6.17: 382 ms
3.7.9: 5,924 ms

There are 80,021 rows in RESPONSES.  The CREATE statement for RESPONSES
reads:

CREATE TABLE responses (
  response_oid integer primary key autoincrement,
  response_name varchar,
  definition_parent integer,
  instance_parent integer,
  prelisted_value varchar,
  override_behavior_oid integer,
  soft_deleted_char varchar default 'F' );

CREATE INDEX response_definition_parent_idx on responses
(definition_parent);

CREATE UNIQUE INDEX response_instance_parent_idx on responses
(instance_parent, definition_parent);

CREATE UNIQUE INDEX response_instance_parent_idx2 on responses
(response_name, instance_parent, definition_parent);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Sat, Jan 14, 2012 at 10:42 AM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 01/14/2012 04:49 AM, John Elrick wrote:
>
>>
>> So, obviously the problem is that _malloc is being called a much larger
>> number of times by 3.7.9 in this particular circumstance -- roughly 70
>> times as often.  _memcpy demonstrates roughly 6 times as many calls   I
>> ran
>> a test with a much smaller dataset tracking every call to malloc back to
>> the main source line.  The results were as follows:
>>
>> 3.6.17,
>> _sqlite3_exec calls _malloc 1,101 times
>> _sqlite3_step calls _malloc 1,812 times
>>
>> 3.7.9
>>  _sqlite3_exec calls _malloc 65,227 times
>> _sqlite3_step calls _malloc 47,109 times
>>
>> At this point I've exhausted my avenues of research.  Does anyone have any
>> further ideas as to what may be going on which could cause this increase
>> in
>> memory management activity?  Or any suggestions of where to look next?
>>
>>
>>
> Is there a CREATE INDEX statement in your program? There was a change
> a few versions back that caused CREATE INDEX to make a lot more calls
> to malloc.
>
> If you build SQLite with SQLITE_OMIT_MERGE_SORT it bypasses the new
> code and builds indexes the old way - with fewer mallocs.


There are many CREATE INDEX statements which are all run at the beginning
when the database is initially created.  I can run a test with your
suggestion early next week.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Fri, Jan 13, 2012 at 5:58 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Fri, Jan 13, 2012 at 4:49 PM, John Elrick <john.elr...@fenestra.com
> >wrote:
>
> >
> > 3.6.17
> >
> > Procedure   % TimeTimeCalls
> > _sqlite3_step   58.4%  1.795,052
> > _memcpy 22.8%  0.691,342,957
> > _memset  7.8%  0.24  465,299
> > ...
> > _malloc  1.9%  0.06   95,505
> >
> >
> > 3.7.9
> >
> > Procedure   % TimeTimeCalls
> > _malloc 56.9% 44.396,975,335
> > _sqlite3_step   30.4% 23.685,052
> > _memcpy  4.8%  3.707,710,259
> >
>
> Very curious
>
> SQLite has lots of interfaces that can be used to determine performance and
> status information.  Some example code from the command-line shell that
> accesses this status information is here:
> http://www.sqlite.org/src/artifact/aa4183d4a5243d?ln=990-1076
>
> I would very much like to see some of these same stats from your
> application.  Do you think you could add some code like that found in the
> shell and give us some more insight into what is going on?


Thanks for the direction.  I'll start working on it on Monday.


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

Re: [sqlite] Some pieces of the puzzle are coming together

[John Elrick]

On Fri, Jan 13, 2012 at 5:20 PM, Pavel Ivanov  wrote:

> > At this point I've exhausted my avenues of research.  Does anyone have
> any
> > further ideas as to what may be going on which could cause this increase
> in
> > memory management activity?  Or any suggestions of where to look next?
>
> IIRC, SQLite 3.7.9 changed its PCACHE interface and thus page cache
> implementation. It introduced one level of indirection into each page
> in the cache. But or course I don't think it should result in such
> huge amount of additional mallocs.
> Also as you see effects only inside Delphi and not in command line
> utility it suggests me that Delphi installed its own page cache. And
> as it uses old (deprecated in 3.7.9) interface it may have some bad
> interaction with new SQLite.
>
> And just a thought: number of calls to memcpy with 3.7.9 is larger
> than with 3.6.17 roughly on the same amount as number of mallocs.
> Which suggests that all new calls are actually calls to realloc() (is
> number of calls to free() also bigger on 6M?).


The number of calls to _free are 6,968,427 in 3.7.9.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Some pieces of the puzzle are coming together

[John Elrick]

Richard and all,

On January 6, I wrote a posting (Problems encountered while upgrading
Sqlite from 3.6.7 to 3.7.9) concerning a slow down noticed in upgrading our
Delphi application from using 3.6.17 to 3.7.9.  Richard asked for some more
specific information including a replicatable case, which has proven
problematic.  Here are the results of my week long investigation.

I created a logging system which took a specific set of data and converted
all of the automatically run queries to an SQL script which I could use in
a test application.  When testing this particular script using a test
program which uses our Delphi wrappers the following times are observed:

Test Application Run Batch Script

3.6.17: 14 seconds
3.7.9: 10 seconds

This clearly demonstrates that the newer version of Sqlite is, all things
being equal, superior in performance to the older.  However, tests inside
our Delphi application demonstrate that reaching the exact same point of
the database result in the following times:

Live Application

3.6.17: 16 seconds
3.7.9: 58 seconds

Extensive profiling of the application finally turned up an unusual and
inexplicable difference between the console application and our regular
application.  I am hoping someone on this group may have some ideas.

I created a Pascal unit whose sole purpose was to delegate to our C
standard library unit.  By having delegates in place it becomes easy to
profile the application.  This unit is used only by the Sqlite libraries.

When I execute the application with the same data as above, the following
are the top consumers of time:

3.6.17

Procedure   % TimeTimeCalls
_sqlite3_step   58.4%  1.795,052
_memcpy 22.8%  0.691,342,957
_memset  7.8%  0.24  465,299
...
_malloc  1.9%  0.06   95,505


3.7.9

Procedure   % TimeTimeCalls
_malloc 56.9% 44.396,975,335
_sqlite3_step   30.4% 23.685,052
_memcpy  4.8%  3.707,710,259



So, obviously the problem is that _malloc is being called a much larger
number of times by 3.7.9 in this particular circumstance -- roughly 70
times as often.  _memcpy demonstrates roughly 6 times as many calls   I ran
a test with a much smaller dataset tracking every call to malloc back to
the main source line.  The results were as follows:

3.6.17,
_sqlite3_exec calls _malloc 1,101 times
_sqlite3_step calls _malloc 1,812 times

3.7.9
 _sqlite3_exec calls _malloc 65,227 times
_sqlite3_step calls _malloc 47,109 times

At this point I've exhausted my avenues of research.  Does anyone have any
further ideas as to what may be going on which could cause this increase in
memory management activity?  Or any suggestions of where to look next?


-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is there some hidden way to create a log record of Sqlite commands?

[John Elrick]

I'm still debugging the issue I reported earlier concerning upgrading
from 3.6.17 to 3.7.9.  One solution we are examining would be to
create a log of all SQL commands executed and then replay the log
using the different versions.  As an example, if I were to execute
(psuedo-code)

command := db.prepareExec('insert into foo values (:bar)');

for i := 0 to 100 do
  begin
command.bind(':bar', i);
command.execute;
  end;

the log would produce:

insert into foo values(1);
insert into foo values(2);
...
insert into foo values(100);

>From my examination of Sqlite, there doesn't appear to be a built in
mechanism for creating this type of log.  Is this correct or am I
missing something?


-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[John Elrick]

On Tue, Jan 10, 2012 at 10:14 AM, Richard Hipp <d...@sqlite.org> wrote:
> On Tue, Jan 10, 2012 at 10:08 AM, John Elrick <john.elr...@fenestra.com>wrote:
>
>> On Tue, Jan 10, 2012 at 8:31 AM, Richard Hipp <d...@sqlite.org> wrote:
>> > On Tue, Jan 10, 2012 at 8:14 AM, John Elrick <john.elr...@fenestra.com
>> >wrote:
>> >
>> >> I will have to get back to you on this.  While running tests against
>> >> isolated queries, I noticed an unusual circumstance.  When I isolate
>> >> the queries into a test program, running through our present
>> >> libraries, 3.7.9 is roughly 4 times faster executing the exact same
>> >> queries where it is running roughly 5 times slower in the context of
>> >> the application.  As those queries do not execute in the same order, I
>> >> suspect that page swapping and caching issues may be involved.  I'm
>> >> proceeding on that assumption.
>> >>
>> >
>> >
>> > Are you using the same compile-time options when building your
>> application
>> > as were used when building the shell program?
>>
>> Yes.
>>
>
> Have you run "SELECT sqlite_source_id();" to verify that your build is
> really picking up the version of SQLite that you think it is?
>
> Do you have code like this in your application:
>
> assert( sqlite3_libversion_number()==SQLITE_VERSION_NUMBER );
> assert( strcmp(sqlite3_sourceid(),SQLITE_SOURCE_ID)==0 );
> assert( strcmp(sqlite3_libversion(),SQLITE_VERSION)==0 );
>
> To verify that your SQLite source code and "sqlite3.h" header file match?

Nope.  I assume that the resulting .OBJ files are self contained.
I'll run the tests you suggest just to make certain.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[John Elrick]

On Tue, Jan 10, 2012 at 8:31 AM, Richard Hipp <d...@sqlite.org> wrote:
> On Tue, Jan 10, 2012 at 8:14 AM, John Elrick <john.elr...@fenestra.com>wrote:
>
>> I will have to get back to you on this.  While running tests against
>> isolated queries, I noticed an unusual circumstance.  When I isolate
>> the queries into a test program, running through our present
>> libraries, 3.7.9 is roughly 4 times faster executing the exact same
>> queries where it is running roughly 5 times slower in the context of
>> the application.  As those queries do not execute in the same order, I
>> suspect that page swapping and caching issues may be involved.  I'm
>> proceeding on that assumption.
>>
>
>
> Are you using the same compile-time options when building your application
> as were used when building the shell program?

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

Re: [sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[John Elrick]

I will have to get back to you on this.  While running tests against
isolated queries, I noticed an unusual circumstance.  When I isolate
the queries into a test program, running through our present
libraries, 3.7.9 is roughly 4 times faster executing the exact same
queries where it is running roughly 5 times slower in the context of
the application.  As those queries do not execute in the same order, I
suspect that page swapping and caching issues may be involved.  I'm
proceeding on that assumption.

On Mon, Jan 9, 2012 at 12:11 PM, Richard Hipp <d...@sqlite.org> wrote:
> Thank you for sending the sample database file.
>
> Rather than forcing me to understand your log database, and fill in
> parameters with reasonable values, I wonder if you could simply email one
> or more queries (with the parameters filled in) which you find to be slower
> in 3.7.9 versus 3.6.7?
>
> On Mon, Jan 9, 2012 at 11:13 AM, Richard Hipp <d...@sqlite.org> wrote:
>
>> Please send private email to d...@sqlite.org.  CC to d...@sqlite.org and
>> j...@sqlite.org.
>>
>>
>> On Mon, Jan 9, 2012 at 11:09 AM, John Elrick <john.elr...@fenestra.com>wrote:
>>
>>> Sorry "The combined files are 2.4 MB zipped"
>>>
>>> On Mon, Jan 9, 2012 at 11:09 AM, John Elrick <john.elr...@fenestra.com>
>>> wrote:
>>> > Richard,
>>> >
>>> > I have the information available for you.  Unfortunately, it appears
>>> > that I cannot actually reduce the case, however, the issues appear to
>>> > be fairly confined.
>>> >
>>> > The test database is 6 MB and I have a database showing the
>>> > performance tracking for each of the queries which is about 140KB.
>>> > The combined files are 2.4 MB.  How would you prefer I send this file
>>> > to you?
>>> >
>>> > On Fri, Jan 6, 2012 at 4:51 PM, John Elrick <john.elr...@fenestra.com>
>>> wrote:
>>> >> Yes.  Look for it on Monday.  I'm about to leave for the day. The
>>> >> queries are automatically generated and will require some mining to
>>> >> get specific examples.  I should be able to reduce the range to a
>>> >> simple repeatable case but it may take a while.
>>> >>
>>> >> Thanks very much and have a great weekend.
>>> >>
>>> >> On Fri, Jan 6, 2012 at 4:42 PM, Richard Hipp <d...@sqlite.org> wrote:
>>> >>> Can you publish for us:
>>> >>>
>>> >>> (1) A sample database
>>> >>> (2) The specific queries that are causing your problems
>>> >>>
>>> >>> So that we can investigate further?
>>> >>>
>>> >>> On Fri, Jan 6, 2012 at 4:31 PM, John Elrick <john.elr...@fenestra.com
>>> >wrote:
>>> >>>
>>> >>>> Background
>>> >>>> =
>>> >>>> We have been using Sqlite3 in a project since 2007.   Because of the
>>> >>>> release cycles involved, we normally upgrade infrequently.  We
>>> >>>> recently upgraded from 3.6.7 to 3.7.9 and encountered two serious
>>> >>>> issues. We need assistance in narrowing down the causes.
>>> >>>>
>>> >>>> We are developing using Delphi.  We are using the amalgamation and
>>> are
>>> >>>> compiling it using the free Borland BCC complier.  The result is an
>>> >>>> obj file which is statically linked into our application.  We have
>>> >>>> been using this technique since we began using Sqlite.
>>> >>>>
>>> >>>> In the tests below, the strategy for changing between versions is to
>>> >>>> change which obj file is statically linked into the application.  The
>>> >>>> application was recompiled between each test.
>>> >>>>
>>> >>>> Issues Encountered
>>> >>>> ===
>>> >>>>
>>> >>>> 1.  After changing there is an order of magnitude performance
>>> >>>> reduction.  Using GP Profile to monitor operations, here are the
>>> >>>> results for identical application uses:
>>> >>>>
>>> >>>> Operation executes _sqlite3_step 49,152 times
>>> >>>>
>>> >>>> 3.6.7 time 5.24 seconds
>>> >>>> 3.7.9 time 41.19 seconds
>>> >>>>
>>>

Re: [sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[John Elrick]

Sorry "The combined files are 2.4 MB zipped"

On Mon, Jan 9, 2012 at 11:09 AM, John Elrick <john.elr...@fenestra.com> wrote:
> Richard,
>
> I have the information available for you.  Unfortunately, it appears
> that I cannot actually reduce the case, however, the issues appear to
> be fairly confined.
>
> The test database is 6 MB and I have a database showing the
> performance tracking for each of the queries which is about 140KB.
> The combined files are 2.4 MB.  How would you prefer I send this file
> to you?
>
> On Fri, Jan 6, 2012 at 4:51 PM, John Elrick <john.elr...@fenestra.com> wrote:
>> Yes.  Look for it on Monday.  I'm about to leave for the day. The
>> queries are automatically generated and will require some mining to
>> get specific examples.  I should be able to reduce the range to a
>> simple repeatable case but it may take a while.
>>
>> Thanks very much and have a great weekend.
>>
>> On Fri, Jan 6, 2012 at 4:42 PM, Richard Hipp <d...@sqlite.org> wrote:
>>> Can you publish for us:
>>>
>>> (1) A sample database
>>> (2) The specific queries that are causing your problems
>>>
>>> So that we can investigate further?
>>>
>>> On Fri, Jan 6, 2012 at 4:31 PM, John Elrick <john.elr...@fenestra.com>wrote:
>>>
>>>> Background
>>>> =
>>>> We have been using Sqlite3 in a project since 2007.   Because of the
>>>> release cycles involved, we normally upgrade infrequently.  We
>>>> recently upgraded from 3.6.7 to 3.7.9 and encountered two serious
>>>> issues. We need assistance in narrowing down the causes.
>>>>
>>>> We are developing using Delphi.  We are using the amalgamation and are
>>>> compiling it using the free Borland BCC complier.  The result is an
>>>> obj file which is statically linked into our application.  We have
>>>> been using this technique since we began using Sqlite.
>>>>
>>>> In the tests below, the strategy for changing between versions is to
>>>> change which obj file is statically linked into the application.  The
>>>> application was recompiled between each test.
>>>>
>>>> Issues Encountered
>>>> ===
>>>>
>>>> 1.  After changing there is an order of magnitude performance
>>>> reduction.  Using GP Profile to monitor operations, here are the
>>>> results for identical application uses:
>>>>
>>>> Operation executes _sqlite3_step 49,152 times
>>>>
>>>> 3.6.7 time 5.24 seconds
>>>> 3.7.9 time 41.19 seconds
>>>>
>>>> 2.  During testing we encountered a bug which had not been encountered
>>>> before.  Research by our CTO established that in 3.7.9 a row was
>>>> returned from this query which contained empty values.  In 3.6.7 there
>>>> were no results from the query.
>>>>
>>>> Summation
>>>> =
>>>> Given how throughly Sqlite is tested I have difficulty believing there
>>>> is an actual bug in Sqlite.  I believe an interaction is occurring
>>>> which is a result of changes to the Sqlite code base which are
>>>> producing unexpected resulted when compiled into our application.  To
>>>> help resolve the problem, I would like to know if there is an
>>>> historical record of the amalgamations.  If so, I can carry out
>>>> testing to determine in what version the problem manifests.
>>>>
>>>> If anyone has any other recommendations I would be pleased to hear
>>>> them.  For the moment we are reverting to 3.6.7 as a temporary
>>>> solution.
>>>>
>>>> --
>>>> John Elrick
>>>> Fenestra Technologies
>>>> 540-868-1377
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@sqlite.org
>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>
>>>
>>>
>>> --
>>> D. Richard Hipp
>>> d...@sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> --
>> John Elrick
>> Fenestra Technologies
>> 540-868-1377
>
>
>
> --
> John Elrick
> Fenestra Technologies
> 540-868-1377



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[John Elrick]

Richard,

I have the information available for you.  Unfortunately, it appears
that I cannot actually reduce the case, however, the issues appear to
be fairly confined.

The test database is 6 MB and I have a database showing the
performance tracking for each of the queries which is about 140KB.
The combined files are 2.4 MB.  How would you prefer I send this file
to you?

On Fri, Jan 6, 2012 at 4:51 PM, John Elrick <john.elr...@fenestra.com> wrote:
> Yes.  Look for it on Monday.  I'm about to leave for the day. The
> queries are automatically generated and will require some mining to
> get specific examples.  I should be able to reduce the range to a
> simple repeatable case but it may take a while.
>
> Thanks very much and have a great weekend.
>
> On Fri, Jan 6, 2012 at 4:42 PM, Richard Hipp <d...@sqlite.org> wrote:
>> Can you publish for us:
>>
>> (1) A sample database
>> (2) The specific queries that are causing your problems
>>
>> So that we can investigate further?
>>
>> On Fri, Jan 6, 2012 at 4:31 PM, John Elrick <john.elr...@fenestra.com>wrote:
>>
>>> Background
>>> =
>>> We have been using Sqlite3 in a project since 2007.   Because of the
>>> release cycles involved, we normally upgrade infrequently.  We
>>> recently upgraded from 3.6.7 to 3.7.9 and encountered two serious
>>> issues. We need assistance in narrowing down the causes.
>>>
>>> We are developing using Delphi.  We are using the amalgamation and are
>>> compiling it using the free Borland BCC complier.  The result is an
>>> obj file which is statically linked into our application.  We have
>>> been using this technique since we began using Sqlite.
>>>
>>> In the tests below, the strategy for changing between versions is to
>>> change which obj file is statically linked into the application.  The
>>> application was recompiled between each test.
>>>
>>> Issues Encountered
>>> ===
>>>
>>> 1.  After changing there is an order of magnitude performance
>>> reduction.  Using GP Profile to monitor operations, here are the
>>> results for identical application uses:
>>>
>>> Operation executes _sqlite3_step 49,152 times
>>>
>>> 3.6.7 time 5.24 seconds
>>> 3.7.9 time 41.19 seconds
>>>
>>> 2.  During testing we encountered a bug which had not been encountered
>>> before.  Research by our CTO established that in 3.7.9 a row was
>>> returned from this query which contained empty values.  In 3.6.7 there
>>> were no results from the query.
>>>
>>> Summation
>>> =
>>> Given how throughly Sqlite is tested I have difficulty believing there
>>> is an actual bug in Sqlite.  I believe an interaction is occurring
>>> which is a result of changes to the Sqlite code base which are
>>> producing unexpected resulted when compiled into our application.  To
>>> help resolve the problem, I would like to know if there is an
>>> historical record of the amalgamations.  If so, I can carry out
>>> testing to determine in what version the problem manifests.
>>>
>>> If anyone has any other recommendations I would be pleased to hear
>>> them.  For the moment we are reverting to 3.6.7 as a temporary
>>> solution.
>>>
>>> --
>>> John Elrick
>>> Fenestra Technologies
>>> 540-868-1377
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> John Elrick
> Fenestra Technologies
> 540-868-1377



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[John Elrick]

Yes.  Look for it on Monday.  I'm about to leave for the day. The
queries are automatically generated and will require some mining to
get specific examples.  I should be able to reduce the range to a
simple repeatable case but it may take a while.

Thanks very much and have a great weekend.

On Fri, Jan 6, 2012 at 4:42 PM, Richard Hipp <d...@sqlite.org> wrote:
> Can you publish for us:
>
> (1) A sample database
> (2) The specific queries that are causing your problems
>
> So that we can investigate further?
>
> On Fri, Jan 6, 2012 at 4:31 PM, John Elrick <john.elr...@fenestra.com>wrote:
>
>> Background
>> =
>> We have been using Sqlite3 in a project since 2007.   Because of the
>> release cycles involved, we normally upgrade infrequently.  We
>> recently upgraded from 3.6.7 to 3.7.9 and encountered two serious
>> issues. We need assistance in narrowing down the causes.
>>
>> We are developing using Delphi.  We are using the amalgamation and are
>> compiling it using the free Borland BCC complier.  The result is an
>> obj file which is statically linked into our application.  We have
>> been using this technique since we began using Sqlite.
>>
>> In the tests below, the strategy for changing between versions is to
>> change which obj file is statically linked into the application.  The
>> application was recompiled between each test.
>>
>> Issues Encountered
>> ===
>>
>> 1.  After changing there is an order of magnitude performance
>> reduction.  Using GP Profile to monitor operations, here are the
>> results for identical application uses:
>>
>> Operation executes _sqlite3_step 49,152 times
>>
>> 3.6.7 time 5.24 seconds
>> 3.7.9 time 41.19 seconds
>>
>> 2.  During testing we encountered a bug which had not been encountered
>> before.  Research by our CTO established that in 3.7.9 a row was
>> returned from this query which contained empty values.  In 3.6.7 there
>> were no results from the query.
>>
>> Summation
>> =
>> Given how throughly Sqlite is tested I have difficulty believing there
>> is an actual bug in Sqlite.  I believe an interaction is occurring
>> which is a result of changes to the Sqlite code base which are
>> producing unexpected resulted when compiled into our application.  To
>> help resolve the problem, I would like to know if there is an
>> historical record of the amalgamations.  If so, I can carry out
>> testing to determine in what version the problem manifests.
>>
>> If anyone has any other recommendations I would be pleased to hear
>> them.  For the moment we are reverting to 3.6.7 as a temporary
>> solution.
>>
>> --
>> John Elrick
>> Fenestra Technologies
>> 540-868-1377
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[John Elrick]

Background
=
We have been using Sqlite3 in a project since 2007.   Because of the
release cycles involved, we normally upgrade infrequently.  We
recently upgraded from 3.6.7 to 3.7.9 and encountered two serious
issues. We need assistance in narrowing down the causes.

We are developing using Delphi.  We are using the amalgamation and are
compiling it using the free Borland BCC complier.  The result is an
obj file which is statically linked into our application.  We have
been using this technique since we began using Sqlite.

In the tests below, the strategy for changing between versions is to
change which obj file is statically linked into the application.  The
application was recompiled between each test.

Issues Encountered
===

1.  After changing there is an order of magnitude performance
reduction.  Using GP Profile to monitor operations, here are the
results for identical application uses:

Operation executes _sqlite3_step 49,152 times

3.6.7 time 5.24 seconds
3.7.9 time 41.19 seconds

2.  During testing we encountered a bug which had not been encountered
before.  Research by our CTO established that in 3.7.9 a row was
returned from this query which contained empty values.  In 3.6.7 there
were no results from the query.

Summation
=
Given how throughly Sqlite is tested I have difficulty believing there
is an actual bug in Sqlite.  I believe an interaction is occurring
which is a result of changes to the Sqlite code base which are
producing unexpected resulted when compiled into our application.  To
help resolve the problem, I would like to know if there is an
historical record of the amalgamations.  If so, I can carry out
testing to determine in what version the problem manifests.

If anyone has any other recommendations I would be pleased to hear
them.  For the moment we are reverting to 3.6.7 as a temporary
solution.

-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parallelizing an update

[John Elrick]

Robert Citek wrote:
> Are there some white papers or examples of how to do updates in
> parallel using sqlite?
>
> I have a large dataset in sqlite that I need to process outside of
> sqlite and then update the sqlite database.  The process looks
> something like this:
>
> sqlite3 -separator $'\t' sample.db 'select rowid, item from foo;' |
> while read rowid item ; do
>   status=$(long_running_process "${item}" )
>   sqlite3 sample.db "update foo set status=${status} where rowid=${rowid} ;"
> done
>
> Because long_running_process takes a long time, I could speed up the
> overall time by running more than one long_running_process at the same
> time.  One way to do this would be to segment the data and run a
> separate process on each segment.  For the update each process would
> collect the status data "outside" of the sample.db, e.g in a separate
> database.  When all the processes have finished, the parent process
> would attach the separate databases and update the original database.
> When all is done, the parent process would clean up the ancillary
> databases.
>   

I could be misunderstanding your requirements, but this sounds a little 
like Map Reduce:

http://labs.google.com/papers/mapreduce.html

The only point I'd question is your assertion that you could speed up 
the overall time by running more than one long running process at the 
same time.  You *might* be able to do so up to the limit of the cores in 
the machine or by distributing the load over many machines, however, the 
implication to me of a long running process is something that is 
consuming large amounts of CPU time.  It is possible that running 
multiple processes per processor could actually increase the total 
amount of time due to process swap overhead.

FWIW,


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

Re: [sqlite] BUG: the rowid column in view is autom a tically named as id

[John Elrick]

Alexey Pechnikov wrote:
> Hello!
>
> On Monday 14 December 2009 11:33:32 Jens Miltner wrote:
>   
>> ...
>> 
>
> You did not understand me. The manually named columns 
> may not be renamed automatically. But in the test the
> column "id"  was renamed to "id:1":
>
> sqlite> create table test (id INTEGER PRIMARY KEY);
> sqlite> insert into test default values;
> sqlite> create view view_test as select rowid,'I am "id" field!' as id from 
> test;
> sqlite> .header on
> sqlite> select * from view_test;
> id|id:1
> 1|I am "id" field!
>
> The construction
> 'I am "id" field!' as id
> _must_ return the column as "id" column. Any other result is wrong.

I don't know if this behavior is a bug or is simply working as designed 
given the constraints of INTEGER PRIMARY KEY. The behavior does, 
admittedly, produce a result which does not correspond to expectations 
and therefore is a possible source of downstream bugs.  Perhaps it 
merits investigation.

Personally, I would expect the result:

rowid|id
1|I am "id" field

But that's me...

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

Re: [sqlite] SQL question

[John Elrick]

Florian Schricker wrote:
> Hello Simon,
>   
SNIP
> Please excuse me for being so blunt:
> You have no idea what I'm supposed to do here nor do you have any idea
> for whatever historic reason the database or table is designed "as is"
> here nor (and finally) do you have no idea about my education on
> databases or SQL or SQLite in general or in detail so please(!) stop
>   

I hate to chime in here, but:

"I'm a starter on SQL / SQLite..."

Your exact words from your first posting.  So either:

a. You are a beginner in both SQL and SQLite, in which case the 
suggestion to obtain the extensive knowledge available from books is 
valid or
b. your statement mislead the group by misinforming them as to your 
state of knowledge.

FWIW


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

Re: [sqlite] is ticket 4037 not a bug?

[John Elrick]

Bogdan Ureche wrote:
> Allan,
>
> Thank you very much for the reply. You are correct when stating that,
> according to SQLite documentation this SQL syntax is not expected to work.
> Therefore, it is not a bug. Perhaps it should be added to the list of SQL92
> features unsupported  by SQLite.

IMHO it's a bug (or oversight):

C:\Users\John\Documents>sqlite3 test.db3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo (id integer, value varchar);
sqlite> .q

C:\Users\John\Documents>sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> attach database "test.db3" as myfoo;
sqlite> insert into myfoo.foo values (1, "hello");
sqlite> select r.* from myfoo.foo r;
1|hello
sqlite> select myfoo.foo.* from myfoo.foo r;
Error: near "*": syntax error
sqlite>


I cannot think of a good reason why:

select myfoo.foo.* from myfoo.foo;

would be expected to produce different results than:

select r.* from myfoo.foo r;

If the former is invalid, then the latter should be also and vis versa.

My own opinion, but there it is.

FWIW,


John Elrick








<cid:part1.02020305.03070606@fenestra.com>

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

Re: [sqlite] LAN and exclusive lock

[John Elrick]

Jan wrote:
> Hi,
>
> Although I read in a recent post by drh that it is not recommended to 
> use sqlite in a local network I would like to ask if the following 
> approach would work:
>
> A database in the local network needs to be accessed by about 20 people. 
> I suppose the max. number at the same time will be 4-5. Only one is able 
> to write to the database at the same time. The one who wants to write to 
> the database acquires an exclusive look with "PRAGMA 
> locking_mode=EXCLUSIVE" if locking_mode is currently NORMAL. AFAIR all 
> others should then still be able to read, but not to write.
>
> Is that correct and more or less save? Does anyone have experience with 
> sqlite on a networkdrive?
>   

In my experience, adding multi-user capability to an application 
increases the complexity by at least an order of magnitude.  If you have 
20 people who need access there are two options (IMO):

1.  Web based application.  In this case the database itself has one and 
only one consumer, the web server.  If you can keep the access to a 
single thread, you have multi-user with no greater complexity than 
single user -- albeit the user will have to stare at a web browser if 
some long running process interferes.
2.  Client/Server.  Again, the database has only one consumer, the local 
server, which manages all the complex details.  MySQL, Firebird, and 
PostgreSQL are open source/free/low cost examples of this type of 
system, however, the tricks that will work for a local database (lists 
and grids are a big offender here) will NOT work effectively in a C/S 
environment.


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

Re: [sqlite] Question regarding BCC32

[John Elrick]

Cariotoglou Mike wrote:
> Great. thanks for the info. however, this means that at least part of the 
> runtime library actually comes from the VCL, and not the BCC32 libraries, is 
> that not so ?
>   

There are some OBJ files needed, we picked them up from somewhere, but I 
don't recall exactly where.

>  
> and, BTW, would you share your code to embed the .obj file ? I know I have 
> done it in the past, and all it takes is to implement the imports, but I dont 
> have that code around anymore, so I would be obliged...
>   

In Delphi it's the compiler directive {$L 'path\sqlite3.obj'}
... the other needed objs

and then you declare the functions:

  // Primary commands
  function  _sqlite3_open(dbname: PAnsiChar; var db: pointer): integer; 
cdecl; external;
  function  _sqlite3_prepare(db: Pointer; SQLStatement: PAnsiChar; 
nBytes: integer;
 var hstatement: pointer; var Tail: 
PAnsiChar): integer; cdecl; external;
  function  _sqlite3_prepare_v2(db: Pointer; SQLStatement: PAnsiChar; 
nBytes: integer;
 var hstatement: pointer; var Tail: 
PAnsiChar): integer; cdecl; external;
  function  _sqlite3_exec(DB: Pointer; SQLStatement: PAnsiChar; 
Callback: TSQLite3_Callback;
  UserDate: Pointer; var ErrMsg: PAnsiChar): 
Integer; cdecl; external;
...etc

If you are using Delphi, I'd be glad to send you the static library unit 
and our .obj files.


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

Re: [sqlite] Improving select distinct performance

[John Elrick]

Jason Freshwater wrote:
> If this comes out garbled again the answer is java 1.6 on 1.8Ghz windows xp
>
> i get about 500,000 rows/second reading single character values via xerial 
> jdbc
>   

Thanks, I did some additional experiments with only integers and got 
similar results.  I had always been testing against, shall we say, more 
robust data.


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

Re: [sqlite] Improving select distinct performance

[John Elrick]

Jason Freshwater wrote:
> 2. Read every record into my application and use a hashtable to
> do my own select distinct. This is pretty good, about 0.6s to
> achieve the distinct list (I can count each distinct value
> along the way which is a useful side effect).

Would you mind my asking what language and what OS you are using?  My 
best performance tests have given ~10,000 rows per second on Windows 
based machines, your results indicate two orders of magnitude better.

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

Re: [sqlite] Synchronising multiple copies of a database

[John Elrick]

Jean-Denis Muys wrote:
> On 10/7/09 21:35 , "Adam DeVita"  wrote:
>
>   
>> One can  also get a mess if Mr. Red and Mr Black both get new customers, and
>> enter them and they both get the same ID because the auto-generated int
>> happens to be the same. Both copies get updated with the other guy's data,
>> they then get annoyed and enter the stuff again and it happens over again,
>> but now there are N entries of the other guy's customer  in the database
>> depending on how many times they do it.
>>
>> 
>
> I solve this problem the following way:
>
> New records ID is not set by the local client who creates the new record,
> but by the central database.

Another solution is to use a GUID instead of an integer.


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

Re: [sqlite] Synchronising multiple copies of a database

[John Elrick]

Simon Slavin wrote:
> On 7 Oct 2009, at 8:33pm, John Elrick wrote:
>
>   
>> Isn't this a variation of the DVCS problem?   In other words, would it
>> be correct in saying that the underlying issue is treating this as a
>> database problem, rather than it being a versioning problem which
>> happens to involve a database?
>> 
>
> Yes yes yes.  And look how complicated the programming is to do DVCS  
> correctly.
>   

I would think the programming is relatively easy...the hard part is 
getting the rules right and being confident you've covered all the edge 
cases.

> And now the problem is ... What is the resolution of your  
> versioning ?  Do put a version number on each row, or do you consider  
> each column in each row to need its own version number ?  What if one  
> site changes one column and another changes another column of the same  
> row ?  How many version numbers do you want to store for just one  
> table ?
>   

As many as necessary, but no more


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

Re: [sqlite] Synchronising multiple copies of a database

[John Elrick]

Simon Slavin wrote:
> On 7 Oct 2009, at 7:20pm, Adam DeVita wrote:
>
>   
>> regarding this
>> " The fault is that
>> almost nobody does it right: they neglect to keep an 'unaltered
>> central copy' and think they can cross-apply journals each time two
>> databases talk to one-another.  That does not work for various  
>> reasons."
>>
>> Would a central repository of journals that can be applied to local
>> repositories be sufficient?  I suppose I assume that running the same
>> program on N workstations with the same set of journals should  
>> produce N
>> identical results.
>> 
>
>   
SNIP
> The problem comes when you apply multiple journals in a different  
> order. Start with each site with identical copies of a TABLE with  
> three clients: one managed by Mr. Green, one by Mr. Red, and one by  
> Mr. Black.  'G R B'.  Then, in this order ...
>
> Mr. Green goes on holiday ...
> Site A says that all Mr. Green's customers will be handled by Mr. Red.
> UPDATE clients SET contact = 'red' WHERE contact = 'green'
>
> Mr. Red goes on holiday ...
> Site B says that all Mr. Red's customers will be handled by Mr. Black.
>
> Then Mr. Green comes back from holiday, and Mr. Black goes on holiday  
> so ...
> Site C says that all Mr. Black's customers will be handled by Mr. Green.
>
> Then they all synchronise databases.  See if you can make them all end  
> up with the same data if they synch against each-other rather than a  
> central unaltered copy of the databases.  Doesn't work: one site might  
> have 'B B B', another 'R B R'.  You can do it only by luck ... by  
> happening to know in which order people went on holiday.  However, if  
> you always synch against a central unaltered copy of the database you  
> can synch in any order.  Once everyone has synchronised you distribute  
> a copy of the central database to everyone and they all have identical  
> data once more.  That's the simplest setup.  You can get more  
> complicated by having each site remember which journals they've played  
> back.
>   

Simon,

Isn't this a variation of the DVCS problem?   In other words, would it 
be correct in saying that the underlying issue is treating this as a 
database problem, rather than it being a versioning problem which 
happens to involve a database?

I ask because there are two separate projects which involve this sort of 
issue which I have simply deferred for the time being.


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

Re: [sqlite] Binding parameters to prepared statements

[John Elrick]

>> Although quotes can be used, they are not needed when the table and 
>> field names contain all valid characters.   I simply saw no value in 
>> adding them, and they did seem to unnecessarily complicate the text.
>>   
>> 
> It also says on http://www.sqlite.org/lang_keywords.html that in
> case you use a name that one day becomes a keyword you should always use
> quotes.

I appreciate that perspective, however, neither 'keys', 'IVAndKey', nor 
'ItemID' are SQL keywords.  Hence my confusion.



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