[Maria-developers] MariaDB config for Akonadi - suggestions?
![](https://secure.gravatar.com/avatar/c7422c379fec4aa4d99a8a137bf10594.jpg?s=120&d=mm&r=g)
Hi,
Daniel Vratil from Akonadi (a PIM manager for KDE) has asked me at FOSDEM about
optimal MariaDB configuration for Akonadi.
Some specific questions:
* innodb_flush_log_at_trx_commit. Currently, Akonadi uses
innodb_flush_log_at_trx_commit=2 which as far as I understand can cause up to 1
second of changes to be lost. Using value of 1 (always flush on commit) should
be safer, but maybe it would put an unnecessary load on the system? Akonadi's
use case has a bit different idea of what load is - there is only one person
making changes, but the system is likely to be a laptop where one cares about
battery life, etc.
* innodb_buffer_pool_size=80M. The dataset size can vary from very small to up
to 1-1.5Gb. Some users have very little data and complain that 80M is too much.
Does anybody have an experience with using very small innodb_buffer_pool_size?
Please find the full config file below:
=== Akonadi configuration file ===
# These settings can be adjusted using $HOME/.config/akonadi/mysql-local.conf
#
# Based on advice by Kris Köhntopp
![](https://secure.gravatar.com/avatar/99fde0c1dfd216326aae0aff30d493f1.jpg?s=120&d=mm&r=g)
Sergey Petrunia
* innodb_flush_log_at_trx_commit. Currently, Akonadi uses innodb_flush_log_at_trx_commit=2 which as far as I understand can cause up to 1 second of changes to be lost. Using value of 1 (always flush on commit) should be safer, but maybe it would put an unnecessary load on the system? Akonadi's use case has a bit different idea of what load is - there is only one person making changes, but the system is likely to be a laptop where one cares about battery life, etc.
innodb_flush_log_at_trx_commit=2 can lose up to 1 seconds of changes, yes, but this only happens if the kernel crashes (kernel bug or power failure). If mysqld crashes, there is no data loss. Presumably power failure is less likely on a laptop due to the battery. But note that this is probably irrelevant for laptop usage. On a laptop, the disk will almost invariably be configured with cache enabled. This means that the disk will lie to the operating system about fsync(), claiming that data is synced durably to disk when it is not. This will invalidate any durability guarantees that InnoDB tries to make. In fact, it even destroys crash-safety of InnoDB, meaning that a power failure can corrupt InnoDB data to the point where recovery is not possible except by complete restore from backup. - Kristian.
participants (2)
-
Kristian Nielsen
-
Sergey Petrunia