Am I understanding this correctly - by db fsck, do you mean running a consistency check of the database, such as mysqlcheck?

akonadictl will work whether or not Akonadi is running, so it would seem to make sense to do what you suggest, namely the command "akonadictl fsck" would 1. stop akonadi, 2. stop db, 3. run the database consistency check, 4. start db, 5. fsck for akonadi. Does "akonadictl fsck" currently stop akonadi before running the checks (I didn't think it did)?

Hi Nathan,

yes, mysqlcheck (or whatever equivalent postgres and sqlite have - some research may be required :-) ).

"akonadictl fsck" right now does not stop Akonadi, because in fact "akonadictl fsck" only does a DBus call to akonadiserver which then runs the internal consistency check (see storagejanitor.cpp), so the plan is for "akonadictl fsck" to

1. akonadictl stop (which also stops db)
2. run db consistency check
3. start db
4. run the internal consistency checks that are right now executed inside akonadiserver
5. stop db
6. akonadictl start

Hi Dan,

Thanks for clarifying. I'll go ahead and apologize in advance for the long comment...sorry for the long comment.

I did some research, and have a couple of questions. One question is the "how" - I'll need a bit of guidance on how to implement this. Is it just adding some popen statements and passing the output to stdout?

The other is on the what. The upshot is: I think, as far as this ticket is concerned, it makes sense to add some of the functionality of mysqlcheck to akonadictl fsck. But I also think it makes sense to add some of the other functionality to akonadictl vacuum. Also, it may make sense to perform routine akonadi maintenance regularly and automatically in the background (something like cron jobs of akonadictl) if it's not done already. Last, bigger question, what is the desired user experience for using akonadictl? For end users (me included) , it's unclear what to do with the messages that come out of running akonadictl fsck, and that may well increase with info and error messages printing from database-related checks. This may be something to discuss.

The background
mysqlcheck performs multiple functions: it performs a consistency check (the "check"), it can repair tables (although not InnoDB tables), it analyzes tables, and it optimizes tables.

It looks like of the three (mysql, postgresql, and sqlite), mysql is the only one with the "check" type of command. Akonadi defaults to InnoDB, and the check and repair facility of mysqlcheck was aimed more at myISAM than InnoDB. InnoDB works more like Postgresql and Sqlite in that the database has built-in consistency checks. If there's a consistency problem within the database managed by mysql, postgresql, or sqlite, the database will throw an error. That said, the "check" of mysqlcheck works with InnoDB tables, and we can include it in akonadictl fsck, but there's no direct analogue in the others, at least as far as the check goes.

Postgresql and sqlite also have an explicit vacuum function - much of the vacuum functionality happens in InnoDB automatically. Postgresql, in more recent versions, also performs the explicit vacuum periodically. Vacuuming performs housekeeping, removing unused and deleted rows, which compacts the tables.

Then there is analysis (mysql, postgresql, and sqlite) which helps the query planner, optimization (in mysql) - not really clear on the use case for akonadi, and reindexing (postgresql, sqlite, and, implemented differently, mysql), which rebuilds indexes. These could be part of a new akonadictl command ("optimize"?).

Mysqlcheck can also repair a db, although this functionlaity does not apply to InnoDB.

Hi Nathan,

many thanks for diving into this! No worries about long comments! Better long comments to clarify things than short comments and big misunderstandings. That said, sorry for the long reply :-)

I realize this breakdown might result in a fairly big task (I usually don't think much in details about most of those tasks up until someone picks them up and start a discussion), so we can create subtasks of this one to track them to make it easier for you, and so you don't feel like this task is a massive never-ending beast :)

So on the "how" part, I'd suggest splitting this task into multiple steps:

Step 1 - Preparations
Looking at it from distance, we will need to share some amount of the database-interfacing code with AkonadiServer (we don't want to duplicate all of that), so the very first step would be factoring out this code into its own (probably static) library (let's call it "akstorage" here). The library should contain some code from the "server/storage" directory - at least akonadidb.*, db*, entities*, query*, schema*, transaction* - there might be some dependencies in those classes on other classes elsewhere in Akonadi Server, we should try to get rid of them (because they imply bad design API pollution).

Step 2 - Basic functionality

1. making akonadictl fsck able to startup and shutdown Akonadi (should be easy, there's code for that in akonadictl already, you just need to implement "waiting" for the startup/shutdown, best by listening for org.freedesktop.Akonadi.Server DBus service do (dis)appear)
2. making akonadictl fsck able to startup and shutdown the database on its own (by using the code from akstorage)
3. moving the current functionality from storagejanitor.cpp to akonadictl to be executed against the DB
4. registering org.freedesktop.Akonadi.upgrading and org.freedesktop.Akonadi.Control.Lock DBus servicse from akonadictl fsck to notify Akonadi applications that an update/upkeep of the database is in progress and to prevent Akonadi Server from starting during that.

Step 3: - Database checks
MySQL:

1. Running CHECK TABLE table for each table after starting the db in akonadictl fsck but before starting the actual janitorial tasks
2. Running OPTIMIZE TABLE table for each table after the janitorial tasks, before shutting down the db and finishing akonadictl fsck

Postgres:

1. Running ANALYZE TABLE table for each table after the janitorial tasks, before shutting down the db and finishing akonadictl fsck

SQLite:

1. Running PRAGMA integrity_check before starting the actual janitorial tasks
2. Running ANALYZE table for each table after the janitorial tasks, before finishing akonadictl fsck

Running fsck regularily would be nice, but it usually takes couple minutes and we don't want to just randomly cut users from their emails to perform a backgorund check...For akonadictl vacuum, I'd say let's make it ANALYZE/OPTIMIZE + VACUUM as a separate tasks.

Regarding the user experience, this is another big topic indeed, likely suitable for a task on its own - my idea is this:

1. detect various DB startup errors (there are few common cases, like corrupted ib_logfile) and give user some guidance how to fix it
2. be less verbose during the janitorial tasks, just say which step we are doing and then some summary (like "Removed 42 unreferenced external payload files")
3. keep the verbose output, but only log it to a file and only print it when running akonadictl fsck --verbose

Hi Dan,

This is very helpful - I will go ahead and create subtasks. It makes sense to me to create three, sticking to the way you divided the task up in the above comment, and keeping this to adding functionality to akonadictl fsck; we can create other tasks for user experience and also routine maintenance.

For the first part - creating the akonadi storage library - would this live in libkdepim or somewhere else?

Nathan

The storage library should remain inside of the akonadi.git repository (just another folder in the /src dir). The library will be internal to Akonadi (so we won't even install it or its headers), and we will only want to use it in tools that all live inside of the akonadi repo - we definitely don't want some other party running direct SQL queries against our database behind our backs :-)