Online backup of in memory database

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Online backup of in memory database

Simon Slavin-3
On 5 Oct 2019, at 10:43pm, Kadirk <[hidden email]> 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.

<https://sqlite.org/wal.html>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Online backup of in memory database

Keith Medcalf
In reply to this post by Kadirk

On Saturday, 5 October, 2019 15:44, Kadirk <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Online backup of in memory database

Simon Slavin-3
On 6 Oct 2019, at 8:03pm, Kadirk <[hidden email]> 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:

<https://www.sqlite.org/c3ref/serialize.html>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Online backup of in memory database

Keith Medcalf
In reply to this post by Kadirk

On Sunday, 6 October, 2019 13:03, Kadirk <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Online backup of in memory database

Rowan Worth-2
In reply to this post by Kadirk
On Sun, 6 Oct 2019 at 23:27, Kadirk <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users