Create Task
Maniphest T7874

akonadi > MySQL configuration settings
Open, NormalPublic
Actions

Description

I have a question on the history of some of the mysql.conf settings:

Forcing lowercase table names

All table names are lowercase in the akonadi database (see below). It is possible that the table names in the DDL are in lower case but I also noticed this setting in mysql.conf:

# Convert table named to lowercase
lower_case_table_names=1

Is there a reason to force all table names to lowercase? It makes it very difficult to read. Especially for old crusty eyeballs. :p

16:42:39 3> show tables;
+----------------------------------+
| Tables_in_akonadi                |
+----------------------------------+
| collectionattributetable         |
| collectionmimetyperelation       |
| collectionpimitemrelation        |
| collectiontable                  |
| flagtable                        |
| mimetypetable                    |
| pablo                            |
| parttable                        |
| parttypetable                    |
| pimitemflagrelation              |
| pimitemtable                     |
| pimitemtagrelation               |
| relationtable                    |
| relationtypetable                |
| resourcetable                    |
| schemaversiontable               |
| tagattributetable                |
| tagremoteidresourcerelationtable |
| tagtable                         |
| tagtypetable                     |
+----------------------------------+

Database Durability

This may be my ignorance showing but why are we allowing possible data loss in the event of a hard system interrupt?

innodb_flush_log_at_trx_commit=2

Related Objects
Search...

pablos created this task.Feb 3 2018, 10:43 PM
pablos triaged this task as Normal priority.
knauss added a subscriber: knauss.Feb 4 2018, 10:53 PM

mmh the git history don't help here:( The only outcome is that both entries weren't touched since 2007.

@pablo: any percentage of how much slower a system will get with innodb_flush_log_at_trx_commit=1?

pablos added a comment.Feb 5 2018, 12:48 AM

Hi Sandro,

Based on your experience, what percentage of our activities affect the data in the database? It's these activities which will (obviously) be affected.

Suppose we had a transaction that affected two objects, the current configuration will allow the COMMIT to return immediately. Adhering to ACID means the COMMIT will have to wait for the I/O's to be posted. The duration is a function of the demand on the disk and the type (HDD versus SSD).

Typically sacrificing durability is not an option. If we cannot afford any data loss, then we need to set this parameter to 1.

dvratil added a subscriber: dvratil.Feb 5 2018, 5:00 PM

Forcing lowercase table names

I can't comment on lower_case_table_names that goes waaay before my time. I guess it may have been to avoid issues with case-(in)sensitivity in other backends? But there aren't that many tables luckily, so one can learn to eventually distinguish the names just by scanning for some keywords :)

Database Durability

Same problem, it was introduced in the initial commit in 2007. Here I can guess that it was to reduce the IO - remember, this is not running on a server cluster but on people's PCs and notebooks, and especially in 2007 this may have had a significant impact. Considering that Akonadi is just a cache, in case of data corruption no data are lost - it's just annoying as you have to setup and re-sync everything :-) These days the difference between 1 and 2 is probably smaller than it used to be. We can consider switching back to 1 again indeed.

Longterm, I'd like to have a GUI tool that would allow users to say "I have a fast SSD and just a few emails" vs. "I have a slow HDD and an enormous collection of emails from the past 30 years including two copies of LKML" and the tool would tune some of the DB variables accordingly.

pablos added a comment.Feb 5 2018, 5:34 PM

Hey Daniel, I like the idea of having a GUI to allow users to tweak the DBMS settings!

Forcing Lowercase Names

My eyes are on fire! :p

Database Durability

Thank you for clarifying that Akonadi is just a cache. Even for contacts though?

Assuming for all our Use Cases, Akonadi is just a cache, then the question is: is it a pain in the neck for the lay-user recreate their data store if something is (technical term) horked?

Further, can we mitigate the creation of tickets by mitigating issues caused by a hard system interrupt[1]. Hmmm!

I'm wondering if we should run some tests using a slowish HDD to ensure

[1] - immediate loss of power.

dvratil added a comment.Feb 5 2018, 6:27 PM

Thank you for clarifying that Akonadi is just a cache. Even for contacts though?

Yes, everything is stored in a backend storage - which can be a remote IMAP server, or a local vCard file (for contacts).

Assuming for all our Use Cases, Akonadi is just a cache, then the question is: is it a pain in the neck for the lay-user recreate their data store if something is (technical term) horked?

It's not tricky but it's not straightforward either. Assuming you only lose your database, just creating a new database is not enough as there are configuration files that refer to Item and Collection IDs which are DB PKs and thus in the new DB the same Collection will have a different ID, which means that you need to go through a lot of settings dialogs and make sure they are actually pointing to the right Collections. Otherwise it can happen that if previously your "Sent" folder had ID 42, in the new DB the ID 42 will belong to Trash and suddenly KMail will be adding your sent emails to trash instead of the "Sent" folder... I wouldn't go into too much detail on how to mitigate or solve this, there were some attempts over the years but nothing that really works well. At least we now have a mechanism for applications to find out that the DB has been recreated and that the IDs they have stored are invalid.

pablos added a comment.Feb 5 2018, 6:46 PM

Assuming you only lose your database, just creating a new database

That's the part where it gets tricky ... depending on what exactly is in the database's journal, upon recovery the database state will be:

  1. Perfectly happy.
  2. Unrecoverable because the journal data is corrupt.

For the second case, we'd have to ask the end-user to re-create their database. :\ It sounds like this could be a pain in the neck for the end-user.

How often this happens largely depends on the user's environment and whether they happen to be affecting a lot of changes when the interrupt occurs.