Re: [sqlite] Online backup of in memory database

[Rowan Worth]

On Sun, 6 Oct 2019 at 23:27, Kadirk  wrote:

> How to do online backup of an in memory database (to disk)?
>
> Planning to use in memory database with 10 gb+ data, there are queries
> continuously so stopping application is not an option. Looks like for on
> disk databases it is possible with a non-blocking fashion but I couldn't
> find a way to do it for in memory database. Whenever an update comes in,
> backup process starts over so it won't finish. Any idea how to solve this?
>

Huh, the documentation explicitly points this out:

If another thread or process writes to the source database while this
> function is sleeping, then SQLite detects this and usually restarts the
> backup process when sqlite3_backup_step() is next called. There is one
> exception to this rule: If the source database is not an in-memory
> database, and the write is performed from within the same process as the
> backup operation and uses the same database handle (pDb), then the
> destination database (the one opened using connection pFile) is
> automatically updated along with the source.
>

https://www.sqlite.org/backup.html

Seems like a strange exception, I wonder why it's there?

You could still complete the backup by specifying nPage=-1 to
sqlite3_backup_step -- this requires a read lock for the duration, but the
lock methods appear to be a no-op for in-memory DBs. Presumably holding the
DB's mutex will still prevent other threads from accessing it though.

Another option if you're on linux is to put the DB file in /dev/shm, at
which point it is physically in memory but from sqlite's perspective is a
regular disk file. You'd have to check the performance characteristics
again of course.

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

Re: [sqlite] Online backup of in memory database

[Martin Raiber]

On 06.10.2019 22:02 Keith Medcalf wrote:
> On Sunday, 6 October, 2019 13:03, Kadirk  wrote:
>
>> We already have an application specific WAL file, sqlite updates +
>> application data is in this WAL file. We are taking snapshot of sqlite +
>> application data to the disk to truncate WAL file, then we can rebuild
>> latest state whenever needed (after restart etc.)
>> We are evaluating sqlite in memory because response time is critical. We
>> target less than ~30 microseconds per query/update for sqlite itself
>> (Insert or selects are like 256 bytes to 10 kb). I tried sqlite on disk 
>> but there were 50+ milliseconds hiccups which might be expected as file 
>> IO overhead is quite high.
>> I expect there might be a way to take backup of sqlite in memory while
>> updates are still being processed (as in on disk online backup). Maybe
>> something like copy on write memory for that?
>> Our data on sqlite is around 10 gb~, so using serialize interface doesn't
>> look possible. If I'm correct, this interface will allocate continuous
>> space for all data, then copy into it. This will lead out of memory 
>> issues + 10 gb copy latency.
> I think you are barking up the wrong tree.  Why do you not simply process the 
> updates against both databases (the in memory transient copy and the on disk 
> persistent one).
>
Well, as for copy-on-write. Do it like redis and fork() the process then
persist the database in the forked process. Problem is if you are using
threads...

Or use a redis+sqlite combination like
https://github.com/RedBeardLab/rediSQL

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

Re: [sqlite] Online backup of in memory database

[Keith Medcalf]

On Sunday, 6 October, 2019 13:03, Kadirk  wrote:

>We already have an application specific WAL file, sqlite updates +
>application data is in this WAL file. We are taking snapshot of sqlite +
>application data to the disk to truncate WAL file, then we can rebuild
>latest state whenever needed (after restart etc.)

>We are evaluating sqlite in memory because response time is critical. We
>target less than ~30 microseconds per query/update for sqlite itself
>(Insert or selects are like 256 bytes to 10 kb). I tried sqlite on disk 
>but there were 50+ milliseconds hiccups which might be expected as file 
>IO overhead is quite high.

>I expect there might be a way to take backup of sqlite in memory while
>updates are still being processed (as in on disk online backup). Maybe
>something like copy on write memory for that?

>Our data on sqlite is around 10 gb~, so using serialize interface doesn't
>look possible. If I'm correct, this interface will allocate continuous
>space for all data, then copy into it. This will lead out of memory 
>issues + 10 gb copy latency.

I think you are barking up the wrong tree.  Why do you not simply process the 
updates against both databases (the in memory transient copy and the on disk 
persistent one).

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] Online backup of in memory database

[Simon Slavin]

On 6 Oct 2019, at 8:03pm, Kadirk  wrote:

> I expect there might be a way to take backup of sqlite in memory while 
> updates are still being processed (as in on disk online backup). Maybe 
> something like copy on write memory for that?

You can't clone something which is changing.  Obviously.  You either have to 
pause changes while you clone, or keep restarting your clone until you get a 
full copy with no changes.

Your fastest way to take a copy of an in-memory database is to serialize it to 
memory, as Keith suggested:



I don't know whether you will need to suspend changes while this happens.  I 
think SQLite itself ensures this happens.  Once that's complete you can 
continue allowing changes and deserialize the database to disk in your own time.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Online backup of in memory database

[Kadirk]

Simon, Keith thanks for the answers, let me give some more details.

We already have an application specific WAL file, sqlite updates +
application data is in this WAL file. We are taking snapshot of sqlite +
application data to the disk to truncate WAL file, then we can rebuild
latest state whenever needed (after restart etc.) 

We are evaluating sqlite in memory because response time is critical. We
target less than ~30 microseconds per query/update for sqlite itself (Insert
or selects are like 256 bytes to 10 kb). I tried sqlite on disk but there
were 50+ milliseconds hiccups which might be expected as file IO overhead is
quite high.

I expect there might be a way to take backup of sqlite in memory while
updates are still being processed (as in on disk online backup). Maybe
something like copy on write memory for that?

Our data on sqlite is around 10 gb~, so using serialize interface doesn't
look possible. If I'm correct, this interface will allocate continuous space
for all data, then copy into it. This will lead out of memory issues + 10 gb
copy latency.
 

 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Online backup of in memory database

[Keith Medcalf]

On Saturday, 5 October, 2019 15:44, Kadirk  wrote:

>How to do online backup of an in memory database (to disk)?

An in memory database is a transient object.  Why would you want to back it up?

>Planning to use in memory database with 10 gb+ data, there are queries
>continuously so stopping application is not an option.

And how does queries cause problems?  Reading is reading and you can be reading 
as much as you like simultaneously.  Rather than queries do you mean updates?  
If so, this brings back the original question:  why are you storing something 
which is transient in nature to persistent storage -- or more correctly why are 
you using an inherently transient structure to store persistent data?

>Looks like for on disk databases it is possible with a non-blocking 
>fashion but I couldn't find a way to do it for in memory database. 
>Whenever an update comes in, backup process starts over so it won't 
>finish. Any idea how to solve this?

Are you using the sqlite3_serialize interfaces to serialize the in memory 
database?  Once you have an additional copy of the database in memory, you can 
simply write it to a disk file.

https://sqlite.org/c3ref/serialize.html

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] Online backup of in memory database

[Simon Slavin]

On 5 Oct 2019, at 10:43pm, Kadirk  wrote:

> Whenever an update comes in,
> backup process starts over so it won't finish. Any idea how to solve this?

How do you expect to take a perfect copy of something which is constantly 
changing ?  Do you expect the changes to stop affecting the database until the 
copy is complete ?

If the database is in WAL mode you can have one connection read it while the 
writes from another connection stay in the journal until the read is complete.


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

[sqlite] Online backup of in memory database

[Kadirk]

How to do online backup of an in memory database (to disk)?

Planning to use in memory database with 10 gb+ data, there are queries
continuously so stopping application is not an option. Looks like for on
disk databases it is possible with a non-blocking fashion but I couldn't
find a way to do it for in memory database. Whenever an update comes in,
backup process starts over so it won't finish. Any idea how to solve this?

Thanks.
Kadir



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users