[sqlite] SQLite with wall enabled what's wrong C demo
> Le 24 mars 2016 ? 14:46, Domingo Alvarez Duarte dev.dadbiz.es> a ?crit : > > But in this example there is only one application accessing the database and > only one table with one small record been updated all the time and no active > readers, why it's writing to wal ? Because it is engineered to do it that way, which is needed to achieve its goal(s). https://sqlite.org/wal.html: ? WAL is significantly faster in most scenarios. ? WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently. ? Disk I/O operations tends to be more sequential using WAL. ? WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken. > And why not update the same record instead of adding a new one in such > situation ? I suppose you wanted to say: why add a new transaction slot to WAL, instead of directly updating the record within the main database file? If that is so, then the answer is: for the same reason as above. One big WAL advantage is to allow you to have multiple readers, each of them having a stable view on the database content (NOT seeing committed transactions), for the duration of their (explicit) transaction, even though at the same time, any number of write transactions might occur (one writer at a time). By default, once the WAL file stores about 1000 pages (or more), a 'checkpointing' operation will be attempted on each new commit, trying to merge as much transactions as possible from the WAL to the main database file. As there may be active readers still needing some of these transactions (to maintain their stability view on the data), not necessarily all transactions can be merged back to the db at once. So the wal file might not be reset completely at that time, and it will continue to grow above 1000 pages. But eventually they will, unless you have a read transaction active for ever. And once all and every connections to the database will be closed, the very last one to close will take care of finalizing the checkpointing (if needed). The WAL additional files will disappear. Maybe WAL is not suited for your use-case as there are disadvantages too: https://sqlite.org/wal.html. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om
[sqlite] SQLite with wall enabled what's wrong C demo
Hello !
I pulled the trigger too soon !
The wall file stop growing at 4MB as advertised.
But in this example there is only one application accessing the database and
only one table with one small record been updated all the time and no active
readers, why it's writing to wal ?
And why not update the same record instead of adding a new one in such
situation ?
I'll try with a bigger record to see what happens.
Cheers !
?
> Thu Mar 24 2016 11:55:11 AM CET from "Domingo Alvarez Duarte"
> Subject: [sqlite] SQLite with wall enabled
>what's wrong C demo
>
> Hello !
>
> I'm sending here a C program that demonstrates the problem of sqlite3 in
>wall
> mode,
>
> in this simple example of a simulated session management for a web server
> when executing
>
> the wall log file will grow and grow till eat all our disk.
>
> ?
>
> There is something wrong with the program or with sqlite3 ?
>
> Cheers !
>
> ?
>
> #include
> #include
> #include
> #include
> #include "sqlite3.h"
>
>
> static const char create_sql[] = "create table if not exists sessions(id
>text
> primary key, data text, ip_address, cdate datetime default
>CURRENT_TIMESTAMP,
> mdate datetime);";
> static const char insert_sql[] = "insert or ignore into sessions(id, data,
> ip_address, mdate) values(?,?,?, CURRENT_TIMESTAMP)";
> static const char update_sql[] = "update sessions set data=?,
> mdate=CURRENT_TIMESTAMP where id=?";
> static const char select_sql[] = "select data from sessions where id=? and
> ip_address=?";
>
> static volatile int keepRunning = 1;
>
> void intHandler(int dummy) {
> ??? keepRunning = 0;
> }
>
> int main()
> {
> ??? signal(SIGINT, intHandler);
>
> ??? printf("Starting our busy session management !\n");
> ??? sqlite3 *db;
> ??? int rc = sqlite3_open("sessions.db", &db);
>
> ??? if(rc == SQLITE_OK)
> ??? {
> ??? sqlite3_stmt *stmt_insert, *stmt_update, *stmt_select;
> ??? //rc = sqlite3_exec(db, "PRAGMA synchronous = 0;", NULL,
>NULL,
> NULL);
> ??? rc = sqlite3_exec(db, "PRAGMA journal_mode = WAL;", NULL,
> NULL, NULL);
>
> ??? //create sessions table
> ??? rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);
>
> ??? //create prepared statements
> ??? rc = sqlite3_prepare_v2(db, insert_sql,
>sizeof(insert_sql)-1,
> &stmt_insert, NULL);
> ??? rc = sqlite3_prepare_v2(db, update_sql,
>sizeof(update_sql)-1,
> &stmt_update, NULL);
> ??? rc = sqlite3_prepare_v2(db, select_sql,
>sizeof(select_sql)-1,
> &stmt_select, NULL);
>
> ??? const char session_id[] = "ABC123456789";
> ??? const char ip_address[] = "127.0.0.1";
> ??? const char data[] = "a kind of initial data";
>
> ??? //insert initial data
> ??? rc = sqlite3_bind_text(stmt_insert, 1, session_id,
> sizeof(session_id)-1, NULL);
> ??? rc = sqlite3_bind_text(stmt_insert, 2, data, sizeof(data)-1,
> NULL);
> ??? rc = sqlite3_bind_text(stmt_insert, 3, ip_address,
> sizeof(ip_address)-1, NULL);
> ??? rc = sqlite3_step(stmt_insert);
> ??? rc = sqlite3_reset(stmt_insert);
>
> ??? int count = 0;
> ??? //start our busy long work
> ??? while(keepRunning)
> ??? {
> ??? printf("Managing session %d\n", ++count);
> ??? sleep(1);
> ??? //one client arrived, let's get it's saved data
> ??? rc = sqlite3_bind_text(stmt_select, 1, session_id,
> sizeof(session_id)-1, NULL);
> ??? rc = sqlite3_bind_text(stmt_select, 2, ip_address,
> sizeof(ip_address)-1, NULL);
> ??? rc = sqlite3_step(stmt_select);
> ??? const unsigned char *saved_data =
> sqlite3_column_text(stmt_select, 0);
> ??? rc = sqlite3_reset(stmt_select);
>
> ??? sleep(1);
> ??? //ok we served our client, let's save it's data
> ??? rc = sqlite3_bind_text(stmt_update, 1, saved_data ?
> "ok we have previous data" : data, -1, NULL);
> ??? rc = sqlite3_bind_text(stmt_update, 2, session_id,
> sizeof(session_id)-1, NULL);
> ??? rc = sqlite3_step(stmt_update);
> ??? rc = sqlite3_reset(stmt_update);
> ??? }
>
> ??? sqlite3_finalize(stmt_insert);
> ??? sqlite3_finalize(stmt_update);
> ??? sqlite3_finalize(stmt_select);
> ??? sqlite3_close(db);
> ??? }
>
> ??? return 0;
> }
>
>
>
> ?
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] SQLite with wall enabled what's wrong C demo
Thanks for reply ! The error check was left out intentionally to not pollute the code, I stepped through all code with gdb to certify it's doing all correctly. Cheers ! > Thu Mar 24 2016 12:03:28 PM CET from "Stephan Beal" > Subject: Re: [sqlite] SQLite with wall enabled >what's wrong C demo > > On Thu, Mar 24, 2016 at 11:55 AM, Domingo Alvarez Duarte < > sqlite-mail at dev.dadbiz.es> wrote: > > >>There is something wrong with the program or with sqlite3 ? >> >> >> >> >> rc = sqlite3_bind_text(stmt_insert, 1, session_id, >> sizeof(session_id)-1, NULL); >> rc = sqlite3_bind_text(stmt_insert, 2, data, sizeof(data)-1, >> NULL); >> rc = sqlite3_bind_text(stmt_insert, 3, ip_address, >> sizeof(ip_address)-1, NULL); >> rc = sqlite3_step(stmt_insert); >> rc = sqlite3_reset(stmt_insert); >> >> > You have not checked a single error code there. If you'll check those > codes, you may be able to find out immediately what the problem is. > > A Golden Rule of C APIs is: if you ignore the result codes, the API may > ignore you. > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > http://gplus.to/sgbeal > "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of > those who insist on a perfect world, freedom will have to do." -- Bigby >Wolf > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] SQLite with wall enabled what's wrong C demo
On Thu, Mar 24, 2016 at 11:55 AM, Domingo Alvarez Duarte < sqlite-mail at dev.dadbiz.es> wrote: > There is something wrong with the program or with sqlite3 ? > > > rc = sqlite3_bind_text(stmt_insert, 1, session_id, > sizeof(session_id)-1, NULL); > rc = sqlite3_bind_text(stmt_insert, 2, data, sizeof(data)-1, > NULL); > rc = sqlite3_bind_text(stmt_insert, 3, ip_address, > sizeof(ip_address)-1, NULL); > rc = sqlite3_step(stmt_insert); > rc = sqlite3_reset(stmt_insert); > You have not checked a single error code there. If you'll check those codes, you may be able to find out immediately what the problem is. A Golden Rule of C APIs is: if you ignore the result codes, the API may ignore you. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] SQLite with wall enabled what's wrong C demo
Hello !
I'm sending here a C program that demonstrates the problem of sqlite3 in wall
mode,
in this simple example of a simulated session management for a web server
when executing
the wall log file will grow and grow till eat all our disk.
?
There is something wrong with the program or with sqlite3 ?
Cheers !
?
#include
#include
#include
#include
#include "sqlite3.h"
static const char create_sql[] = "create table if not exists sessions(id text
primary key, data text, ip_address, cdate datetime default CURRENT_TIMESTAMP,
mdate datetime);";
static const char insert_sql[] = "insert or ignore into sessions(id, data,
ip_address, mdate) values(?,?,?, CURRENT_TIMESTAMP)";
static const char update_sql[] = "update sessions set data=?,
mdate=CURRENT_TIMESTAMP where id=?";
static const char select_sql[] = "select data from sessions where id=? and
ip_address=?";
static volatile int keepRunning = 1;
void intHandler(int dummy) {
??? keepRunning = 0;
}
int main()
{
??? signal(SIGINT, intHandler);
??? printf("Starting our busy session management !\n");
??? sqlite3 *db;
??? int rc = sqlite3_open("sessions.db", &db);
??? if(rc == SQLITE_OK)
??? {
??? sqlite3_stmt *stmt_insert, *stmt_update, *stmt_select;
??? //rc = sqlite3_exec(db, "PRAGMA synchronous = 0;", NULL, NULL,
NULL);
??? rc = sqlite3_exec(db, "PRAGMA journal_mode = WAL;", NULL,
NULL, NULL);
??? //create sessions table
??? rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);
??? //create prepared statements
??? rc = sqlite3_prepare_v2(db, insert_sql, sizeof(insert_sql)-1,
&stmt_insert, NULL);
??? rc = sqlite3_prepare_v2(db, update_sql, sizeof(update_sql)-1,
&stmt_update, NULL);
??? rc = sqlite3_prepare_v2(db, select_sql, sizeof(select_sql)-1,
&stmt_select, NULL);
??? const char session_id[] = "ABC123456789";
??? const char ip_address[] = "127.0.0.1";
??? const char data[] = "a kind of initial data";
??? //insert initial data
??? rc = sqlite3_bind_text(stmt_insert, 1, session_id,
sizeof(session_id)-1, NULL);
??? rc = sqlite3_bind_text(stmt_insert, 2, data, sizeof(data)-1,
NULL);
??? rc = sqlite3_bind_text(stmt_insert, 3, ip_address,
sizeof(ip_address)-1, NULL);
??? rc = sqlite3_step(stmt_insert);
??? rc = sqlite3_reset(stmt_insert);
??? int count = 0;
??? //start our busy long work
??? while(keepRunning)
??? {
??? printf("Managing session %d\n", ++count);
??? sleep(1);
??? //one client arrived, let's get it's saved data
??? rc = sqlite3_bind_text(stmt_select, 1, session_id,
sizeof(session_id)-1, NULL);
??? rc = sqlite3_bind_text(stmt_select, 2, ip_address,
sizeof(ip_address)-1, NULL);
??? rc = sqlite3_step(stmt_select);
??? const unsigned char *saved_data =
sqlite3_column_text(stmt_select, 0);
??? rc = sqlite3_reset(stmt_select);
??? sleep(1);
??? //ok we served our client, let's save it's data
??? rc = sqlite3_bind_text(stmt_update, 1, saved_data ?
"ok we have previous data" : data, -1, NULL);
??? rc = sqlite3_bind_text(stmt_update, 2, session_id,
sizeof(session_id)-1, NULL);
??? rc = sqlite3_step(stmt_update);
??? rc = sqlite3_reset(stmt_update);
??? }
??? sqlite3_finalize(stmt_insert);
??? sqlite3_finalize(stmt_update);
??? sqlite3_finalize(stmt_select);
??? sqlite3_close(db);
??? }
??? return 0;
}
?
