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] Error 19 with Caret ^ INSERT

[Keith Medcalf]

How do you know that the "error" is related to the use of a caret?

Perhaps your .net wrapper is diddling text strings that contain carets for some 
reason known only to the wrapper writer?  Have you checked the wrapper 
documentation to see if such diddling is documented, and if so, how to turn it 
off?

And where do you get the idea that a caret in a string is a "filter or so"?

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

>-Original Message-
>From: sqlite-users  On
>Behalf Of André Borchert
>Sent: Saturday, 5 October, 2019 22:56
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Error 19 with Caret ^ INSERT
>
>Hello,
>
>in my C# program which uses DllImport for sqlite.dll (3.29) I get Error
>19 (SQLITE_CONSTRAINT) with this statement:
>
>INSERT INTO CompanyDetails (RIC) VALUES('AMBEUR.IP^G19')
>
>The ^ symbol is part of the text I want to save, its not a filter or so,
>its just plain text.
>
>The odd thing is that the sql statement works just fine when I use DB
>Browser for SQLite.
>The row looks like expected, with the ^ character saved as text.
>
>Sent with [ProtonMail](https://protonmail.com) Secure Email.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
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

Re: [sqlite] Error 19 with Caret ^ INSERT

[Simon Slavin]

On 6 Oct 2019, at 5:56am, André Borchert  wrote:

> The odd thing is that the sql statement works just fine when I use DB Browser 
> for SQLite.

Try the SQLite command line tool.  That's written by the team which maintains 
SQLite itself, and it should give the connonically correct result.  If it 
objects to a constraint then the team can definitely tell you why.


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

[sqlite] API request: sqlite3_vconfig() and sqlite3_db_vconfig()

[Stephen F. Booth]

Hello,

I have a request for two API additions: sqlite3_vconfig() and
sqlite3_db_vconfig(). The naming convention is borrowed from vprintf() and
friends. The functions would be identical to sqlite3_config() and
sqlite3_db_config() except they would take a va_list instead of variadic
arguments.

The motivation for this is better interoperability with Swift. While Swift
supports calling most C functions natively, variadic functions are not
supported. Currently it isn't possible to call the _config() functions
without additional glue code.

The implementations are straightforward:

% diff sqlite3-330.h sqlite3+vconfigs.h
1560a1561,1562
> SQLITE_API int sqlite3_vconfig(int, va_list ap);
>
1579a1582,1583
> SQLITE_API int sqlite3_db_vconfig(sqlite3*, int op, va_list ap);
>


% diff sqlite3-330.c sqlite3+vconfigs.c
2602a2603,2604
> SQLITE_API int sqlite3_vconfig(int, va_list ap);
>
2621a2624,2625
> SQLITE_API int sqlite3_db_vconfig(sqlite3*, int op, va_list ap);
>
120640a120645,120646
> /* FIXME: Version 3.XX.XX and later */
> #define sqlite3_db_vconfig sqlite3_api->db_vconfig
121112a121119,121120
>   /* FIXME: Version 3.XX.XX and later */
>   sqlite3_db_vconfig,
156586a156595,156601
>   va_start(ap, op);
>   int rc = sqlite3_vconfig(op, ap);
>   va_end(ap);
>   return rc;
> }
>
> SQLITE_API int sqlite3_vconfig(int op, va_list ap){
156593d156607
<   va_start(ap, op);
156888d156901
<   va_end(ap);
157035d157047
<   int rc;
157036a157049,157055
>   int rc = sqlite3_db_vconfig(db, op, ap);
>   va_end(ap);
>   return rc;
> }
>
> SQLITE_API int sqlite3_db_vconfig(sqlite3 *db, int op, va_list ap){
>   int rc;
157098d157116
<   va_end(ap);

Thanks,
Stephen
___
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

[sqlite] Error 19 with Caret ^ INSERT

[André Borchert]

Hello,

in my C# program which uses DllImport for sqlite.dll (3.29) I get Error 19 
(SQLITE_CONSTRAINT) with this statement:

INSERT INTO CompanyDetails (RIC) VALUES('AMBEUR.IP^G19')

The ^ symbol is part of the text I want to save, its not a filter or so, its 
just plain text.

The odd thing is that the sql statement works just fine when I use DB Browser 
for SQLite.
The row looks like expected, with the ^ character saved as text.

Sent with [ProtonMail](https://protonmail.com) Secure Email.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users