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, &err))
{
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", &conn, 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(&thread, 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++%100000))
{
printf("END BEGIN transactions\n");
fflush(stdout);
sql_exec(conn, "END TRANSACTION");
sql_exec(conn, "BEGIN TRANSACTION");
}
sql_exec(conn, "INSERT INTO tbl1 values('aaaaaaaaaaaaaaaaaaa', "
"'bbbbbbbbbbbbbbbbbbb')");
}
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 <[email protected]> wrote:
> On Tue, Aug 10, 2010 at 2:24 AM, Yoni Londner <[email protected]> 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
> > [email protected]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> [email protected]
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
