Re: [sqlite] Addition: Re: SQLite locking question

2011-05-06 Thread Pavel Ivanov 
> My understanding of the shared cache model was that it just ist intended
> for solving our problem by relaxing the locking a little and that there
> should not be any mutexes at all when using the uncomitted read mode.
>
> Have I missed anything?

Yes, you are involved in a "magical" thinking. All that you said would
be true if any change that SQLite does in the database cache was
atomic. I.e. let's say SQLite needs to add a row, zing, and the row is
in there and all necessary cache pages are changed without other
thread ever noticing any inconsistent state during the process. Also
let's say SQLite needs to find some row in the table, zing, and it
already knows where that row is and other thread didn't change
anything during the search... All this cannot happen, thus mutex is
held.

BTW, if you just read data then locking cannot be an issue for you.
Turn off shared cache and you will be okay.


Pavel


On Fri, May 6, 2011 at 3:56 PM, Ole Reinhardt
 wrote:
> Hi Pavel,
>
>> >> Any other chance to speed this up (apart from the obvious "optimize the
>> >> query, do not use distinct on large tables)=
>> >
>> > Without seeing the query or database schema? Not really... Depending
>> > on the exact query an index on "xyz" might help.
>>
>> Another suggestion could be to turn off shared cache mode.
>
> Oh ok :)
>
> My understanding of the shared cache model was that it just ist intended
> for solving our problem by relaxing the locking a little and that there
> should not be any mutexes at all when using the uncomitted read mode.
>
> Have I missed anything?
>
> Bye,
>
> Ole
>
> --
>
> Thermotemp GmbH, Embedded-IT
>
> Embedded Hard-/ Software and Open Source Development,
> Integration and Consulting
>
> http://www.embedded-it.de
>
> Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen -
> tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97
>
> Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
> Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
> Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
> Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280
>
> ___
> 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] Addition: Re: SQLite locking question

2011-05-06 Thread Ole Reinhardt 
Hi Pavel,

> >> Any other chance to speed this up (apart from the obvious "optimize the
> >> query, do not use distinct on large tables)=
> >
> > Without seeing the query or database schema? Not really... Depending
> > on the exact query an index on "xyz" might help.
> 
> Another suggestion could be to turn off shared cache mode.

Oh ok :)

My understanding of the shared cache model was that it just ist intended
for solving our problem by relaxing the locking a little and that there
should not be any mutexes at all when using the uncomitted read mode.

Have I missed anything?

Bye,

Ole

-- 

Thermotemp GmbH, Embedded-IT

Embedded Hard-/ Software and Open Source Development, 
Integration and Consulting

http://www.embedded-it.de

Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen - 
tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97

Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280 

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

Re: [sqlite] Addition: Re: SQLite locking question

2011-05-06 Thread Ole Reinhardt 
Hi Dan,

>[...]
> Mutexes are held for the duration of an sqlite3_step() call to make
> sure SQLite doesn't segfault when two threads try to access the same
> shared-cache object.

Ok, I see, so there is no chance to access the data realy in _parallel_?
(Just for reading).

> > Any other chance to speed this up (apart from the obvious "optimize the
> > query, do not use distinct on large tables)=
> 
> Without seeing the query or database schema? Not really... Depending
> on the exact query an index on "xyz" might help.

The database scheme looks like this:

static gchar sql_create_table_log[] =
"CREATE TABLE IF NOT EXISTS log ( "
"  keyINTEGER, "
"  timestamp_sINTEGER, "
"  timestamp_us   INTEGER, "
"  type   INTEGER, "
"  level  INTEGER, "
"  event  INTEGER, "
"  source TEXT,"
"  dest   TEXT,"
"  text   TEXT,"
"  radio_id   INTEGER, "
"  latitude   REAL,"
"  longitude  REAL,"
"  speed  REAL,"
"  direction  INTEGER, "
"  fixINTEGER, "
"  alert  INTEGER, "
"  state  INTEGER, "
"  cstate INTEGER, "
"  PRIMARY KEY(key)"
")";

static gchar sql_create_index_log[] = 
"CREATE INDEX IF NOT EXISTS timestamp ON log (timestamp_s,
timestamp_us)";


The SQL query that takes that much time for each single step is the
following:

"SELECT DISTINCT radio_id FROM log WHERE event IN (%d, %d) AND
timestamp_s >= %ld AND timestamp_s <= %ld"



The request that shall be done in parallel is the following:

SELECT timestamp_s, timestamp_us, type, level, event, source, dest,
text, radio_id, latitude, longitude, speed, direction, key FROM log
WHERE (timestamp_s * 1000 + timestamp_us / 1000) > %lld %s AND key >
((SELECT MAX(key) FROM log) -2000) ORDER BY KEY DESC


Regards!

Ole





-- 

Thermotemp GmbH, Embedded-IT

Embedded Hard-/ Software and Open Source Development, 
Integration and Consulting

http://www.embedded-it.de

Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen - 
tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97

Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280 

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

Re: [sqlite] Addition: Re: SQLite locking question

2011-05-06 Thread Pavel Ivanov 
>> Any other chance to speed this up (apart from the obvious "optimize the
>> query, do not use distinct on large tables)=
>
> Without seeing the query or database schema? Not really... Depending
> on the exact query an index on "xyz" might help.

Another suggestion could be to turn off shared cache mode.


Pavel


On Fri, May 6, 2011 at 7:15 AM, Dan Kennedy  wrote:
> On 05/06/2011 05:53 PM, Ole Reinhardt wrote:
>> Hi Dan,
>>
 I have to correct myself a little... the hanging sqlite3_open_v2() does
 not happe while the prepare of the first query but during the first
 sqlite3_step() of the first query...
>>>
>>> Each shared-cache has a mutex associated with it. When
>>> sqlite3_step is called it grabs the mutexes for all shared-caches
>>> it might use. The mutexes are not released until the call
>>> to sqlite3_step() returns.
>>>
>>> So if you have one query that spends a lot of time in sqlite3_step()
>>> you are going to block your other threads. Unfortunately, that is
>>> the nature of shared-cache mode.
>>
>> Thanks for the info. But is this even true when enabling read
>> uncommitted isolation mode using:
>>
>> sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
>> 0,)
>>
>> ??
>>
>> I suspect it's the master table lock that is hold there, right?
>
> You are correct that your query will hold a shared-lock on the
> master table at the shared cache level, but it's the mutex that
> is causing you problems. Shared-cache locks are held for the
> duration of a transaction to ensure transactions are correctly
> isolated. In this case the master table is locked to make sure
> that the table your query is accessing is not dropped by another
> thread while you are using it.
>
> Mutexes are held for the duration of an sqlite3_step() call to make
> sure SQLite doesn't segfault when two threads try to access the same
> shared-cache object.
>
>> Any other chance to speed this up (apart from the obvious "optimize the
>> query, do not use distinct on large tables)=
>
> Without seeing the query or database schema? Not really... Depending
> on the exact query an index on "xyz" might help.
>
> Dan.
>
> ___
> 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] Addition: Re: SQLite locking question

2011-05-06 Thread Ole Reinhardt 
Hi Dan,

> > I have to correct myself a little... the hanging sqlite3_open_v2() does
> > not happe while the prepare of the first query but during the first
> > sqlite3_step() of the first query...
> 
> Each shared-cache has a mutex associated with it. When
> sqlite3_step is called it grabs the mutexes for all shared-caches
> it might use. The mutexes are not released until the call
> to sqlite3_step() returns.
> 
> So if you have one query that spends a lot of time in sqlite3_step()
> you are going to block your other threads. Unfortunately, that is
> the nature of shared-cache mode.

Thanks for the info. But is this even true when enabling read
uncommitted isolation mode using:

sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
0,)

??

I suspect it's the master table lock that is hold there, right?

Any other chance to speed this up (apart from the obvious "optimize the
query, do not use distinct on large tables)=

Bye,

Ole


-- 

Thermotemp GmbH, Embedded-IT

Embedded Hard-/ Software and Open Source Development, 
Integration and Consulting

http://www.embedded-it.de

Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen - 
tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97

Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280 

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

Re: [sqlite] Addition: Re: SQLite locking question

2011-05-06 Thread Dan Kennedy 
On 05/06/2011 05:53 PM, Ole Reinhardt wrote:
> Hi Dan,
>
>>> I have to correct myself a little... the hanging sqlite3_open_v2() does
>>> not happe while the prepare of the first query but during the first
>>> sqlite3_step() of the first query...
>>
>> Each shared-cache has a mutex associated with it. When
>> sqlite3_step is called it grabs the mutexes for all shared-caches
>> it might use. The mutexes are not released until the call
>> to sqlite3_step() returns.
>>
>> So if you have one query that spends a lot of time in sqlite3_step()
>> you are going to block your other threads. Unfortunately, that is
>> the nature of shared-cache mode.
>
> Thanks for the info. But is this even true when enabling read
> uncommitted isolation mode using:
>
> sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
> 0,)
>
> ??
>
> I suspect it's the master table lock that is hold there, right?

You are correct that your query will hold a shared-lock on the
master table at the shared cache level, but it's the mutex that
is causing you problems. Shared-cache locks are held for the
duration of a transaction to ensure transactions are correctly
isolated. In this case the master table is locked to make sure
that the table your query is accessing is not dropped by another
thread while you are using it.

Mutexes are held for the duration of an sqlite3_step() call to make
sure SQLite doesn't segfault when two threads try to access the same
shared-cache object.

> Any other chance to speed this up (apart from the obvious "optimize the
> query, do not use distinct on large tables)=

Without seeing the query or database schema? Not really... Depending
on the exact query an index on "xyz" might help.

Dan.

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

Re: [sqlite] Addition: Re: SQLite locking question

2011-05-06 Thread Dan Kennedy 
On 05/06/2011 05:17 PM, Ole Reinhardt wrote:
> Hi all,
>
> I have to correct myself a little... the hanging sqlite3_open_v2() does
> not happe while the prepare of the first query but during the first
> sqlite3_step() of the first query...

Each shared-cache has a mutex associated with it. When
sqlite3_step is called it grabs the mutexes for all shared-caches
it might use. The mutexes are not released until the call
to sqlite3_step() returns.

So if you have one query that spends a lot of time in sqlite3_step()
you are going to block your other threads. Unfortunately, that is
the nature of shared-cache mode.

Dan.



>
> So in pseudo code the database access looks like this:
>
> first thread:
>
> sqlite3_open_v2("log.sqlite", SQLITE_OPEN_READONLY |
> SQLITE_OPEN_NOMUTEX, NULL);
> sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
> 0,)
> sqlite3_prepare_v2(log_database_local, "select distinct xyz from log
> where ...)
>
> while(sqlite3_step(sql_stmt) == SQLITE_ROW)...
>
> While every step takes _very_ long time as the log table has quite a lot
> entries (>  5.000.000)
>
>
>
> second thread:
>
> sqlite3_open_v2("log.sqlite", SQLITE_OPEN_READONLY |
> SQLITE_OPEN_NOMUTEX, NULL);
>
> --->  This sqlite3_open_v2 does not return until the prepare
> sqlite3_step() statement of the first thread has completed...
>
>
> again: Any help or short hint would be very appreciated!
>
> Thanks in advance,
>
> Ole Reinhardt
>

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