Operationalerror: database is locked


#1

Hello, Guyz!

We start using activitywatch in our company to improve the employers productivity.

We set up a linux server and installed the app in 60 windows clients, after that we configured a GPO to start the aw-qt in the clients and to replace the file aw-client.ini with the correct server adress.

After two days we start to experiencing the error database is locked. Googling about it I realized that it was because the number of connection in the server bd.

I read in another app forum that the error was solved changing the journal mode. So i Change the journal mode to journal_mode=WAL in pragma setting and it works, the events are now beeing saved in BD, but I stil can not see the clients, when i hit activity tab, it shows that there isn’t activity reports available.

I also read that when we change the connection to WAL mode we also need to change the DB connection like the example below:

public static class Connection
{
public abstract SQLiteConnection NewConnection(String file);
}

public class NormalConnection : Connection
{
public override SQLiteConnection NewConnection(String file)
{
return new SQLLiteConneciton(“Data Source=” + file);
}
}

public class WALConnection : Connection
{
public override SQLiteConnection NewConnection(String file)
{
return new SQLLiteConnection(“Data Source=” + file + “;PRAGMA journal_mode=WAL;”
}
}

Could someone help me with this ?

I searched in the forum a saw that there’s isn’t another alternative of BD, like MySQL ou MariaDB.


#2

Your analysis is correct, “Database is locked” means that there are too many requests for the db to be able to catch up.

Your case is certainly very demanding, we certainly did not expect aw-server to be handle 60 concurrent machines with 120 concurrent clients when we designed this.

Just so you know in the future, we cannot help you test and find these bottlenecks since we don’t have the data to actually test such a demanding scenario, but if you have any questions we are happy to help you solve these issues if possible.

I read in another app forum that the error was solved changing the journal mode. So i Change the journal mode to journal_mode=WAL in pragma setting and it works, the events are now beeing saved in BD, but I stil can not see the clients, when i hit activity tab, it shows that there isn’t activity reports available.

We have a new datastore in development which will be raw SQLite rather than using the peewee libraries abstraction over SQLite which will improve the performance, especially in cases where there are a lot of parallell requests since this datastore will batch inserts with transactions as well as using WAL as you mentioned.

It is pretty close to finished, but it is not finished yet, you could try it out if you are running aw-server from source. Also includes automatic migration from the peewee datastore, so first startup might be slow if you have over a hundred clients.

I searched in the forum a saw that there’s isn’t another alternative of BD, like MySQL ou MariaDB.

There is support for mongodb, but it is not recommended since its performance is not properly tested and our first priority is the sqlite datastore. We do not guarantee compability across aw-server versions with mongodb as of now either.


#3

Thanks for your answer!

After one day running in the Wal the system started running again.

Now, we have all the clients working without locked events.

The database increased a litle, as you can see below, but is working fine.

[email protected]:~/.local/share/activitywatch/aw-server# ls -lh
total 531M
-rw-r–r-- 1 root root 105M Out 8 08:37 peewee-sqlite.v2.db
-rw-r–r-- 1 root root 61M Out 2 08:21 peewee-sqlite.v2.db.orig
-rw-r–r-- 1 root root 2,8M Out 8 08:37 peewee-sqlite.v2.db-shm
-rw-r–r-- 1 root root 363M Out 8 08:37 peewee-sqlite.v2.db-wal


#4

Nice that it worked, for now at least!

Your sqlite file is gaining size quite fast though, so it’s likely just a matter of time before sqlite will not be able to do the job anymore.
From my experience sqlite performance starts to tank quite a lot after ~5GB, which would probably take your usecase only a month or two.