Re: [sqlite] fast acces to one memory located table from two threads

[Max Vlasov]

One process, two threads.
> I have found in documentation, that is not recommended to pass one
> connection
> from one thread to second one.
>

Yes, you're right, forgot about this, only exception is sqlite3_interrupt
that can be called from other thread otherwise its existence makes no sense.
But you probably can work around this with your own mutexes, wrapping your
calls in the corresponding access/release calls, but I suppose there still
could exist some complexities. I didn't try it myself, but I guess there are
some operations that can be implemented safely with this approach.

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

Re: [sqlite] fast acces to one memory located table from two threads

[Jakub Ladman]

One process, two threads.
I have found in documentation, that is not recommended to pass one connection 
from one thread to second one.

A partial succes, relative fast function, is proven with database file stored 
in ramdisk.
Hard disk file storage 16 inserts per second.
Ramdisk file storage 150 inserts per second.
Temporary table in ram 1500 inserts per second.

I have tried the one shared connection before i found the part in doc, that 
not recommends it, but there was some problems with locking in one 
configuration and segment violation in the second case.

Jakub Ladman
> Are you talking about two processes? If not, why these two threads have to
> have separated connections? If it's not necessary, I'd opened disk db,
> attach memory database to it (see http://www.sqlite.org/inmemorydb.html )
> and pass this db handle to both threads. One of them in this case will use
> only memory part of the base (fast appending), another one will do
>  necessary selects and inserts to disk db part. After the db is closed, you
>  will only have disk part left. I don't know more about locking logic in
>  this case, because for performance reasons, it would be better for
>  example, if memory part only locked its part not affecting the disk part.
> 
> Max
> ___
> 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] fast acces to one memory located table from two threads

[Max Vlasov]

> I need an application consisting two threads.
> In one of them i need to store incomming "messages" (one message is 1 to 8
> bytes of data) to temporary table existing only in memory.
> It needs to be fast, storing hundreds of messages per second.
> There i have a trigger deleting old rows and creating some sort of circular
> buffer.
> In separate thread there should be done some selects (read only) on this
> table
> sorting the informations and storing them into separate table(s) located on
> disk (jffs2 filesystem).
>
>
Are you talking about two processes? If not, why these two threads have to
have separated connections? If it's not necessary, I'd opened disk db,
attach memory database to it (see http://www.sqlite.org/inmemorydb.html )
and pass this db handle to both threads. One of them in this case will use
only memory part of the base (fast appending), another one will do necessary
selects and inserts to disk db part. After the db is closed, you will only
have disk part left. I don't know more about locking logic in this case,
because for performance reasons, it would be better for example, if memory
part only locked its part not affecting the disk part.

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

Re: [sqlite] fast acces to one memory located table from two threads

[Jakub Ladman]

Dne úterý 23 března 2010 19:22:35 Pavel Ivanov napsal(a):
> I'd suggest you to use any kind of memory structure (like deque or

:-) I do not know what is deque.

> whatever you prefer) for transferring data from one thread to another.
> Besides anything else it will be faster than using SQLite for this
> particular task.

For better understanding, i need not only to transfer data between threads, 
but store fast in first thread and read sorted data in the second.
Sorting have to be done via some complex SELECTs.

If it is not possible, i will think about completely different schema, but i 
have no idea at this time, how to do it different way.

Jakub Ladman

> 
> As you have already understood there's no way you can make 2
> connections to one in-memory database. Although you can use one
> connection from both thread so that threads will naturally block each
> other while they're doing their piece of job.
> 
> 
> Pavel
> 
> On Tue, Mar 23, 2010 at 1:55 PM, Jakub Ladman  wrote:
> > Hello to all
> >
> > I am back here to mailing list after about two years and also back to
> > programming with sqlite in general.
> >
> > I have new task and i do not know how to do it.
> >
> > I need an application consisting two threads.
> > In one of them i need to store incomming "messages" (one message is 1 to
> > 8 bytes of data) to temporary table existing only in memory.
> > It needs to be fast, storing hundreds of messages per second.
> > There i have a trigger deleting old rows and creating some sort of
> > circular buffer.
> > In separate thread there should be done some selects (read only) on this
> > table sorting the informations and storing them into separate table(s)
> > located on disk (jffs2 filesystem).
> >
> > I do not know how to do two connections to memory stored table(fast, and
> > not wasting the flash memory writes).
> > The database opened as :memory: is separate for every thread, same as
> > temporary table with pragma temp_store = MEMORY.
> >
> > Do you have any clue?
> > Is it completely stupid way?
> >
> > Thank You
> >
> > Jakub Ladman
> > ___
> > 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
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fast acces to one memory located table from two threads

[Teg]

Yeah, I do this all the time with STL and a critical section lock. A
list, or set and a lock is all you need. list of vectors for instance.
Set if you want it to automatically reject duplicated, a list for just
dumping data to it.

C



Tuesday, March 23, 2010, 2:22:35 PM, you wrote:

PI> I'd suggest you to use any kind of memory structure (like deque or
PI> whatever you prefer) for transferring data from one thread to another.
PI> Besides anything else it will be faster than using SQLite for this
PI> particular task.

PI> As you have already understood there's no way you can make 2
PI> connections to one in-memory database. Although you can use one
PI> connection from both thread so that threads will naturally block each
PI> other while they're doing their piece of job.


PI> Pavel

PI> On Tue, Mar 23, 2010 at 1:55 PM, Jakub Ladman  wrote:
>> Hello to all
>>
>> I am back here to mailing list after about two years and also back to
>> programming with sqlite in general.
>>
>> I have new task and i do not know how to do it.
>>
>> I need an application consisting two threads.
>> In one of them i need to store incomming "messages" (one message is 1 to 8
>> bytes of data) to temporary table existing only in memory.
>> It needs to be fast, storing hundreds of messages per second.
>> There i have a trigger deleting old rows and creating some sort of circular
>> buffer.
>> In separate thread there should be done some selects (read only) on this 
>> table
>> sorting the informations and storing them into separate table(s) located on
>> disk (jffs2 filesystem).
>>
>> I do not know how to do two connections to memory stored table(fast, and not
>> wasting the flash memory writes).
>> The database opened as :memory: is separate for every thread, same as
>> temporary table with pragma temp_store = MEMORY.
>>
>> Do you have any clue?
>> Is it completely stupid way?
>>
>> Thank You
>>
>> Jakub Ladman
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
PI> ___
PI> sqlite-users mailing list
PI> sqlite-users@sqlite.org
PI> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] fast acces to one memory located table from two threads

[Pavel Ivanov]

I'd suggest you to use any kind of memory structure (like deque or
whatever you prefer) for transferring data from one thread to another.
Besides anything else it will be faster than using SQLite for this
particular task.

As you have already understood there's no way you can make 2
connections to one in-memory database. Although you can use one
connection from both thread so that threads will naturally block each
other while they're doing their piece of job.


Pavel

On Tue, Mar 23, 2010 at 1:55 PM, Jakub Ladman  wrote:
> Hello to all
>
> I am back here to mailing list after about two years and also back to
> programming with sqlite in general.
>
> I have new task and i do not know how to do it.
>
> I need an application consisting two threads.
> In one of them i need to store incomming "messages" (one message is 1 to 8
> bytes of data) to temporary table existing only in memory.
> It needs to be fast, storing hundreds of messages per second.
> There i have a trigger deleting old rows and creating some sort of circular
> buffer.
> In separate thread there should be done some selects (read only) on this table
> sorting the informations and storing them into separate table(s) located on
> disk (jffs2 filesystem).
>
> I do not know how to do two connections to memory stored table(fast, and not
> wasting the flash memory writes).
> The database opened as :memory: is separate for every thread, same as
> temporary table with pragma temp_store = MEMORY.
>
> Do you have any clue?
> Is it completely stupid way?
>
> Thank You
>
> Jakub Ladman
> ___
> 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

[sqlite] fast acces to one memory located table from two threads

[Jakub Ladman]

Hello to all

I am back here to mailing list after about two years and also back to 
programming with sqlite in general.

I have new task and i do not know how to do it.

I need an application consisting two threads. 
In one of them i need to store incomming "messages" (one message is 1 to 8 
bytes of data) to temporary table existing only in memory. 
It needs to be fast, storing hundreds of messages per second.
There i have a trigger deleting old rows and creating some sort of circular 
buffer.
In separate thread there should be done some selects (read only) on this table 
sorting the informations and storing them into separate table(s) located on 
disk (jffs2 filesystem).

I do not know how to do two connections to memory stored table(fast, and not 
wasting the flash memory writes).
The database opened as :memory: is separate for every thread, same as 
temporary table with pragma temp_store = MEMORY.

Do you have any clue?
Is it completely stupid way?

Thank You

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