Re: [sqlite] performance, transactions and wal checkpoints

[Yoni Londner]

Hi,
Yes they are.
With "PRAGMA wal_checkpoint" it works as expected.
Yoni.

On Tue, Aug 10, 2010 at 2:50 PM, Dan Kennedy  wrote:

>
> On Aug 10, 2010, at 6:03 PM, Yoni Londner wrote:
>
> > Hi,
> > Yes, it explains allot.
> > But, according to you, the following program should work.
> > I don't use transactions, shared cache or threads.
> > Just run the checkpoint from another connection.
> > And still - WAL file is getting bigger and bigger without limit.
> > If I do the checkpoint with the same connection, everything work
> > perfectly.
>
> Interesting. Perhaps the call to sqlite3_wal_checkpoint()
> is not working because the second connection never really
> connects to the database (because connection usually happens
> as part of the first SQL statement run).
>
> Are things any different if you change the sqlite3_wal_checkpoint()
> to sqlite3_exec(conn, "PRAGMA wal_checkpoint", 0, 0, 0)?
>
> Dan.
>
> > #include "sqlite3.h"
> > #include "stdio.h"
> > #include "stdlib.h"
> > #include "fcntl.h"
> >
> > static void sql_exec(sqlite3 *conn, char *query)
> > {
> >char *err;
> >if (sqlite3_exec(conn, query, NULL, 0, ))
> >{
> >printf("sqlite: failed exec %s. err: %s\n", query, err);
> > exit(1);
> >}
> > }
> >
> > static sqlite3 *sql_open_conn(void)
> > {
> >sqlite3 *conn;
> >if (sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE, NULL))
> >{
> > printf("sqlite3_open_v2 failed\n");
> > exit(1);
> >}
> >return conn;
> > }
> >
> > static int do_checkpoint()
> > {
> >sqlite3 *conn;
> >conn = sql_open_conn();
> >if (sqlite3_wal_checkpoint(conn, NULL))
> >{
> > printf("sqlite3_wal_autocheckpoint failed\n");
> > exit(1);
> >}
> >sqlite3_close(conn);
> > }
> >
> > int main(int argc, char **argv)
> > {
> >char *err_msg = NULL;
> >pthread_t thread;
> >int fd, i;
> >sqlite3 *conn;
> >if (unlink("test.db") || unlink("test.db-wal"))
> >{
> > printf("failed unlink test.db\n");
> > exit(1);
> >}
> >fd = open("test.db", O_CREAT|O_RDWR, 0666);
> >if (fd<0)
> >{
> >printf("could not open test.db\n");
> >exit(1);
> >}
> >close(fd);
> >conn = sql_open_conn();
> >sql_exec(conn, "PRAGMA journal_mode=WAL");
> >sql_exec(conn, "PRAGMA synchronous=normal");
> >sql_exec(conn, "PRAGMA temp_store=memory");
> >sql_exec(conn, "PRAGMA wal_autocheckpoint=-1");
> >sql_exec(conn, "create table tbl1 (one varchar(20), two
> > varchar(20))");
> >while (1)
> >{
> >if (!(i++%1000))
> >do_checkpoint();
> > sql_exec(conn, "INSERT INTO tbl1 values('aaa', "
> >"'bbb')");
> >}
> >sqlite3_close(conn);
> >return 0;
> > }
> >
> > On Tue, Aug 10, 2010 at 1:52 PM, Dan Kennedy 
> > wrote:
> >
> >>
> >>
> >>> Hi,
> >>> I just wanted to add that I changed the program a little bit to not
> >>> use
> >>> transactions and threads, and I still get the same problem (huge WAL
> >>> file).
> >>> All I do is endless loop of insert, and every X insert, I perform a
> >>> checkpoint on another sqlite connection (but in the same thread).
> >>> It only worked if I do the checkpoint on the same connection.
> >>
> >> You cannot run a checkpoint from within a transaction. If
> >> you are in shared-cache mode, this means you cannot run a
> >> checkpoint while any connection to the same database has
> >> an open transaction.
> >>
> >> Does that explain anything?
> >>
> >> 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
>
> ___
> 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] performance, transactions and wal checkpoints

[Dan Kennedy]

On Aug 10, 2010, at 6:03 PM, Yoni Londner wrote:

> Hi,
> Yes, it explains allot.
> But, according to you, the following program should work.
> I don't use transactions, shared cache or threads.
> Just run the checkpoint from another connection.
> And still - WAL file is getting bigger and bigger without limit.
> If I do the checkpoint with the same connection, everything work  
> perfectly.

Interesting. Perhaps the call to sqlite3_wal_checkpoint()
is not working because the second connection never really
connects to the database (because connection usually happens
as part of the first SQL statement run).

Are things any different if you change the sqlite3_wal_checkpoint()
to sqlite3_exec(conn, "PRAGMA wal_checkpoint", 0, 0, 0)?

Dan.

> #include "sqlite3.h"
> #include "stdio.h"
> #include "stdlib.h"
> #include "fcntl.h"
>
> static void sql_exec(sqlite3 *conn, char *query)
> {
>char *err;
>if (sqlite3_exec(conn, query, NULL, 0, ))
>{
>printf("sqlite: failed exec %s. err: %s\n", query, err);
> exit(1);
>}
> }
>
> static sqlite3 *sql_open_conn(void)
> {
>sqlite3 *conn;
>if (sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE, NULL))
>{
> printf("sqlite3_open_v2 failed\n");
> exit(1);
>}
>return conn;
> }
>
> static int do_checkpoint()
> {
>sqlite3 *conn;
>conn = sql_open_conn();
>if (sqlite3_wal_checkpoint(conn, NULL))
>{
> printf("sqlite3_wal_autocheckpoint failed\n");
> exit(1);
>}
>sqlite3_close(conn);
> }
>
> int main(int argc, char **argv)
> {
>char *err_msg = NULL;
>pthread_t thread;
>int fd, i;
>sqlite3 *conn;
>if (unlink("test.db") || unlink("test.db-wal"))
>{
> printf("failed unlink test.db\n");
> exit(1);
>}
>fd = open("test.db", O_CREAT|O_RDWR, 0666);
>if (fd<0)
>{
>printf("could not open test.db\n");
>exit(1);
>}
>close(fd);
>conn = sql_open_conn();
>sql_exec(conn, "PRAGMA journal_mode=WAL");
>sql_exec(conn, "PRAGMA synchronous=normal");
>sql_exec(conn, "PRAGMA temp_store=memory");
>sql_exec(conn, "PRAGMA wal_autocheckpoint=-1");
>sql_exec(conn, "create table tbl1 (one varchar(20), two  
> varchar(20))");
>while (1)
>{
>if (!(i++%1000))
>do_checkpoint();
> sql_exec(conn, "INSERT INTO tbl1 values('aaa', "
>"'bbb')");
>}
>sqlite3_close(conn);
>return 0;
> }
>
> On Tue, Aug 10, 2010 at 1:52 PM, Dan Kennedy   
> wrote:
>
>>
>>
>>> Hi,
>>> I just wanted to add that I changed the program a little bit to not
>>> use
>>> transactions and threads, and I still get the same problem (huge WAL
>>> file).
>>> All I do is endless loop of insert, and every X insert, I perform a
>>> checkpoint on another sqlite connection (but in the same thread).
>>> It only worked if I do the checkpoint on the same connection.
>>
>> You cannot run a checkpoint from within a transaction. If
>> you are in shared-cache mode, this means you cannot run a
>> checkpoint while any connection to the same database has
>> an open transaction.
>>
>> Does that explain anything?
>>
>> 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

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

Re: [sqlite] performance, transactions and wal checkpoints

[Yoni Londner]

Hi,
Yes, it explains allot.
But, according to you, the following program should work.
I don't use transactions, shared cache or threads.
Just run the checkpoint from another connection.
And still - WAL file is getting bigger and bigger without limit.
If I do the checkpoint with the same connection, everything work perfectly.

What am I doing wrong?

===

#include "sqlite3.h"
#include "stdio.h"
#include "stdlib.h"
#include "fcntl.h"

static void sql_exec(sqlite3 *conn, char *query)
{
char *err;
if (sqlite3_exec(conn, query, NULL, 0, ))
{
printf("sqlite: failed exec %s. err: %s\n", query, err);
exit(1);
}
}

static sqlite3 *sql_open_conn(void)
{
sqlite3 *conn;
if (sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE, NULL))
{
printf("sqlite3_open_v2 failed\n");
exit(1);
}
return conn;
}

static int do_checkpoint()
{
sqlite3 *conn;
conn = sql_open_conn();
if (sqlite3_wal_checkpoint(conn, NULL))
{
printf("sqlite3_wal_autocheckpoint failed\n");
exit(1);
}
sqlite3_close(conn);
}

int main(int argc, char **argv)
{
char *err_msg = NULL;
pthread_t thread;
int fd, i;
sqlite3 *conn;
if (unlink("test.db") || unlink("test.db-wal"))
{
printf("failed unlink test.db\n");
exit(1);
}
fd = open("test.db", O_CREAT|O_RDWR, 0666);
if (fd<0)
{
printf("could not open test.db\n");
exit(1);
}
close(fd);
conn = sql_open_conn();
sql_exec(conn, "PRAGMA journal_mode=WAL");
sql_exec(conn, "PRAGMA synchronous=normal");
sql_exec(conn, "PRAGMA temp_store=memory");
sql_exec(conn, "PRAGMA wal_autocheckpoint=-1");
sql_exec(conn, "create table tbl1 (one varchar(20), two varchar(20))");
while (1)
{
if (!(i++%1000))
do_checkpoint();
sql_exec(conn, "INSERT INTO tbl1 values('aaa', "
"'bbb')");
}
sqlite3_close(conn);
return 0;
}

On Tue, Aug 10, 2010 at 1:52 PM, Dan Kennedy  wrote:

>
>
> > Hi,
> > I just wanted to add that I changed the program a little bit to not
> > use
> > transactions and threads, and I still get the same problem (huge WAL
> > file).
> > All I do is endless loop of insert, and every X insert, I perform a
> > checkpoint on another sqlite connection (but in the same thread).
> > It only worked if I do the checkpoint on the same connection.
>
> You cannot run a checkpoint from within a transaction. If
> you are in shared-cache mode, this means you cannot run a
> checkpoint while any connection to the same database has
> an open transaction.
>
> Does that explain anything?
>
> 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] performance, transactions and wal checkpoints

[Dan Kennedy]

> Hi,
> I just wanted to add that I changed the program a little bit to not  
> use
> transactions and threads, and I still get the same problem (huge WAL  
> file).
> All I do is endless loop of insert, and every X insert, I perform a
> checkpoint on another sqlite connection (but in the same thread).
> It only worked if I do the checkpoint on the same connection.

You cannot run a checkpoint from within a transaction. If
you are in shared-cache mode, this means you cannot run a
checkpoint while any connection to the same database has
an open transaction.

Does that explain anything?

Dan.

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

Re: [sqlite] performance, transactions and wal checkpoints

[Yoni Londner]

Hi,
I just wanted to add that I changed the program a little bit to not use
transactions and threads, and I still get the same problem (huge WAL file).
All I do is endless loop of insert, and every X insert, I perform a
checkpoint on another sqlite connection (but in the same thread).
It only worked if I do the checkpoint on the same connection.
Yoni.

On Tue, Aug 10, 2010 at 11:46 AM, Yoni Londner  wrote:

> Hi Richard,
> Thanks for the quick response.
> I wrote a little program that demonstrate the error (below).
> It opens a new DB, create a table, and start an endless loop of INSERT's.
> In a background thread it performs a wal checkpoint.
> There is no difference in WAL file size - with or without transactions
> (passed by argument to the program).
> This is the result after 45 seconds:
> -rw-r--r-- 1  1.0K 2010-08-10 11:41 test.db
> -rw-r--r-- 1  1.1G 2010-08-10 11:42 test.db-wal
> -rw-r--r-- 1  8.0M 2010-08-10 11:42 test.db-shm
>
> Program compiled with GCC on debian lenny, using sqlite 3.7.0.
>
> #include "sqlite3.h"
> #include "stdio.h"
> #include "stdlib.h"
> #include "fcntl.h"
>
> static void sql_exec(sqlite3 *conn, char *query)
> {
> char *err;
> if (sqlite3_exec(conn, query, NULL, 0, ))
> {
> printf("sqlite: failed exec %s. err: %s\n", query, err);
> exit(1);
> }
> }
>
> static sqlite3 *sql_open_conn(void)
> {
> sqlite3 *conn;
> if (sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE, NULL))
> {
> printf("sqlite3_open_v2 failed\n");
> exit(1);
> }
> return conn;
> }
>
> static int do_checkpoint()
> {
> sqlite3 *conn;
> while (1)
> {
> sleep(2);
>  printf("calling wal checkpoint\n");
> fflush(0);
> conn = sql_open_conn();
>  if (sqlite3_wal_checkpoint(conn, NULL))
> {
> printf("sqlite3_wal_autocheckpoint failed\n");
> exit(1);
> }
> sqlite3_close(conn);
> }
> }
>
> int main(int argc, char **argv)
> {
> sqlite3 *conn = NULL;
> char *err_msg = NULL;
> pthread_t thread;
> int fd, i, use_transactions = 0;
> time_t start;
> if (argc>1)
> use_transactions = atoi(argv[1]);
> printf("use_transactions=%d\n", use_transactions);
> printf("Start\n");
> if (unlink("test.db") || unlink("test.db-wal"))
> {
> printf("failed unlink test.db\n");
> exit(1);
> }
> fd = open("test.db", O_CREAT|O_RDWR, 0666);
> if (fd<0)
> {
> printf("could not open test.db\n");
> exit(1);
> }
> close(fd);
> conn = sql_open_conn();
> sqlite3_enable_shared_cache(1);
> sql_exec(conn, "PRAGMA journal_mode=WAL");
> sql_exec(conn, "PRAGMA synchronous=normal");
> sql_exec(conn, "PRAGMA temp_store=memory");
> sql_exec(conn, "PRAGMA wal_autocheckpoint=-1");
> sql_exec(conn, "create table tbl1 (one varchar(20), two varchar(20))");
> if (pthread_create(, NULL, do_checkpoint, NULL))
> {
> printf("could not start thread\n");
> exit(1);
> }
> start = time();
> if (use_transactions)
> sql_exec(conn, "BEGIN TRANSACTION");
> while (1)
> {
> if (use_transactions && !(i++%10))
> {
> printf("END BEGIN transactions\n");
> fflush(stdout);
> sql_exec(conn, "END TRANSACTION");
> sql_exec(conn, "BEGIN TRANSACTION");
>  }
> sql_exec(conn, "INSERT INTO tbl1 values('aaa', "
> "'bbb')");
> }
> if (use_transactions)
> sql_exec(conn, "END TRANSACTION");
> sqlite3_close(conn);
> printf("Finished\n");
> return 0;
> }
> On Tue, Aug 10, 2010 at 9:36 AM, Richard Hipp  wrote:
>
>> On Tue, Aug 10, 2010 at 2:24 AM, Yoni Londner  wrote:
>>
>> > Hello,
>> > I have a questions about the correct use of transactions and WAL.
>> >
>> > I am writing an application that:
>> >  1. should very fast
>> >  2. should be very responsive
>> >  3. don't care if the last N minutes of data will be lost (but DB should
>> > never be corrupted)
>> >
>> > What I tried to do:
>> >  1. open a transactions and close it every 3 minutes (So most of the
>> tune
>> > all work is on memory, and thus is very fast)
>> >  2. disable wal auto checkpoint (So I wont have very slow queries due to
>> a
>> > checkpoint)
>> >  3. run wal checkpoint in another thread (with another connection) - so
>> it
>> > wont affect the responsiveness of the main thread.
>> >
>> > Results:
>> >  1. process memory is increasing with no upper limit
>> >  2. eventually I get an I/O error
>> >
>>
>> I'm guessing your write transactions are preventing the checkpoint from
>> running to completion.  Hence, the WAL grows without bound and the
>> wal-index
>> (an in-memory structure proportional in size to the WAL file) eventually
>> uses up all memory.
>>
>> Set PRAGMA synchronous=NORMAL.  This prevents all fsync() calls on the
>> writer thread at the cost of durability, which you say you don't care
>> about.
>> Omit the 3-minute transactions, allowing each write to be its own

Re: [sqlite] performance, transactions and wal checkpoints

[Yoni Londner]

Hi Richard,
Thanks for the quick response.
I wrote a little program that demonstrate the error (below).
It opens a new DB, create a table, and start an endless loop of INSERT's.
In a background thread it performs a wal checkpoint.
There is no difference in WAL file size - with or without transactions
(passed by argument to the program).
This is the result after 45 seconds:
-rw-r--r-- 1  1.0K 2010-08-10 11:41 test.db
-rw-r--r-- 1  1.1G 2010-08-10 11:42 test.db-wal
-rw-r--r-- 1  8.0M 2010-08-10 11:42 test.db-shm

Program compiled with GCC on debian lenny, using sqlite 3.7.0.

#include "sqlite3.h"
#include "stdio.h"
#include "stdlib.h"
#include "fcntl.h"

static void sql_exec(sqlite3 *conn, char *query)
{
char *err;
if (sqlite3_exec(conn, query, NULL, 0, ))
{
printf("sqlite: failed exec %s. err: %s\n", query, err);
exit(1);
}
}

static sqlite3 *sql_open_conn(void)
{
sqlite3 *conn;
if (sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE, NULL))
{
printf("sqlite3_open_v2 failed\n");
exit(1);
}
return conn;
}

static int do_checkpoint()
{
sqlite3 *conn;
while (1)
{
sleep(2);
printf("calling wal checkpoint\n");
fflush(0);
conn = sql_open_conn();
if (sqlite3_wal_checkpoint(conn, NULL))
{
printf("sqlite3_wal_autocheckpoint failed\n");
exit(1);
}
sqlite3_close(conn);
}
}

int main(int argc, char **argv)
{
sqlite3 *conn = NULL;
char *err_msg = NULL;
pthread_t thread;
int fd, i, use_transactions = 0;
time_t start;
if (argc>1)
use_transactions = atoi(argv[1]);
printf("use_transactions=%d\n", use_transactions);
printf("Start\n");
if (unlink("test.db") || unlink("test.db-wal"))
{
printf("failed unlink test.db\n");
exit(1);
}
fd = open("test.db", O_CREAT|O_RDWR, 0666);
if (fd<0)
{
printf("could not open test.db\n");
exit(1);
}
close(fd);
conn = sql_open_conn();
sqlite3_enable_shared_cache(1);
sql_exec(conn, "PRAGMA journal_mode=WAL");
sql_exec(conn, "PRAGMA synchronous=normal");
sql_exec(conn, "PRAGMA temp_store=memory");
sql_exec(conn, "PRAGMA wal_autocheckpoint=-1");
sql_exec(conn, "create table tbl1 (one varchar(20), two varchar(20))");
if (pthread_create(, NULL, do_checkpoint, NULL))
{
printf("could not start thread\n");
exit(1);
}
start = time();
if (use_transactions)
sql_exec(conn, "BEGIN TRANSACTION");
while (1)
{
if (use_transactions && !(i++%10))
{
printf("END BEGIN transactions\n");
fflush(stdout);
sql_exec(conn, "END TRANSACTION");
sql_exec(conn, "BEGIN TRANSACTION");
}
sql_exec(conn, "INSERT INTO tbl1 values('aaa', "
"'bbb')");
}
if (use_transactions)
sql_exec(conn, "END TRANSACTION");
sqlite3_close(conn);
printf("Finished\n");
return 0;
}
On Tue, Aug 10, 2010 at 9:36 AM, Richard Hipp  wrote:

> On Tue, Aug 10, 2010 at 2:24 AM, Yoni Londner  wrote:
>
> > Hello,
> > I have a questions about the correct use of transactions and WAL.
> >
> > I am writing an application that:
> >  1. should very fast
> >  2. should be very responsive
> >  3. don't care if the last N minutes of data will be lost (but DB should
> > never be corrupted)
> >
> > What I tried to do:
> >  1. open a transactions and close it every 3 minutes (So most of the tune
> > all work is on memory, and thus is very fast)
> >  2. disable wal auto checkpoint (So I wont have very slow queries due to
> a
> > checkpoint)
> >  3. run wal checkpoint in another thread (with another connection) - so
> it
> > wont affect the responsiveness of the main thread.
> >
> > Results:
> >  1. process memory is increasing with no upper limit
> >  2. eventually I get an I/O error
> >
>
> I'm guessing your write transactions are preventing the checkpoint from
> running to completion.  Hence, the WAL grows without bound and the
> wal-index
> (an in-memory structure proportional in size to the WAL file) eventually
> uses up all memory.
>
> Set PRAGMA synchronous=NORMAL.  This prevents all fsync() calls on the
> writer thread at the cost of durability, which you say you don't care
> about.
> Omit the 3-minute transactions, allowing each write to be its own
> transaction.  Writes then will still be in-memory (if you count the
> operating system filesystem cache as "in-memory").  But then the
> checkpoints
> will be able to run and keep the size of the WAL file under control.
>
>
>
> >
> > Questions:
> >  1. what am I doing wrong.
> >  2. what is the correct way to achieve the goals I mentioned before.
> >
> > Thanks,
> > Jon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> 

Re: [sqlite] performance, transactions and wal checkpoints

[Richard Hipp]

On Tue, Aug 10, 2010 at 2:24 AM, Yoni Londner  wrote:

> Hello,
> I have a questions about the correct use of transactions and WAL.
>
> I am writing an application that:
>  1. should very fast
>  2. should be very responsive
>  3. don't care if the last N minutes of data will be lost (but DB should
> never be corrupted)
>
> What I tried to do:
>  1. open a transactions and close it every 3 minutes (So most of the tune
> all work is on memory, and thus is very fast)
>  2. disable wal auto checkpoint (So I wont have very slow queries due to a
> checkpoint)
>  3. run wal checkpoint in another thread (with another connection) - so it
> wont affect the responsiveness of the main thread.
>
> Results:
>  1. process memory is increasing with no upper limit
>  2. eventually I get an I/O error
>

I'm guessing your write transactions are preventing the checkpoint from
running to completion.  Hence, the WAL grows without bound and the wal-index
(an in-memory structure proportional in size to the WAL file) eventually
uses up all memory.

Set PRAGMA synchronous=NORMAL.  This prevents all fsync() calls on the
writer thread at the cost of durability, which you say you don't care about.
Omit the 3-minute transactions, allowing each write to be its own
transaction.  Writes then will still be in-memory (if you count the
operating system filesystem cache as "in-memory").  But then the checkpoints
will be able to run and keep the size of the WAL file under control.



>
> Questions:
>  1. what am I doing wrong.
>  2. what is the correct way to achieve the goals I mentioned before.
>
> Thanks,
> Jon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] performance, transactions and wal checkpoints

[Yoni Londner]

Hello,
I have a questions about the correct use of transactions and WAL.

I am writing an application that:
  1. should very fast
  2. should be very responsive
  3. don't care if the last N minutes of data will be lost (but DB should
never be corrupted)

What I tried to do:
  1. open a transactions and close it every 3 minutes (So most of the tune
all work is on memory, and thus is very fast)
  2. disable wal auto checkpoint (So I wont have very slow queries due to a
checkpoint)
  3. run wal checkpoint in another thread (with another connection) - so it
wont affect the responsiveness of the main thread.

Results:
  1. process memory is increasing with no upper limit
  2. eventually I get an I/O error

Questions:
  1. what am I doing wrong.
  2. what is the correct way to achieve the goals I mentioned before.

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