Recommendation for MariaDB

[ghost]

Dear Nextclouders,

MariaDB does not use the transaction isolation level "READ-COMMIT" as default.

At least two rows should be added to /etc/mysql/my.cnf in the mysqld-section:

...
[mysqld]
...
transaction_isolation = READ-COMMITTED
binlog_format = ROW
...

After having restarted MariaDB the "Read-Commit" persists and can be verified by:

MariaDB [(none)]> SELECT @@TX_ISOLATION;
+----------------+
| @@TX_ISOLATION |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

My examplarily configuration (/etc/mysql/my.cnf) looks like:

[server]
skip-name-resolve
innodb_buffer_pool_size = 128M
innodb_buffer_pool_instances = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_max_dirty_pages_pct = 90
query_cache_type = 1
query_cache_limit = 2M
query_cache_min_res_unit = 2k
query_cache_size = 64M
tmp_table_size= 64M
max_heap_table_size= 64M
slow-query-log = 1
slow-query-log-file = /var/log/mysql/slow.log
long_query_time = 1

[client-server]
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
transaction_isolation = READ-COMMITTED
binlog_format = ROW
innodb_large_prefix=on
innodb_file_format=barracuda
innodb_file_per_table=1

I guess, this could or should be documented in your Nextcloud documentation either?
Best regards, Carsten

[nextcloud-bot]

GitMate.io thinks possibly related issues are #183 (Recommendations for Configuring Memory Caching), #5912 (Recommend PostgreSQL instead of MariaDB/MySQL), #5099 (Failed to convert DB from sqlite to MariaDB), #9509 (Can't connect Nextcloud to MariaDB with SSL), and #5587 (MariaDB SSL-Config breaks Nextcloud-Installation).

[MorrisJobke]

@riegercloud Thanks for the hints - could I ask you to open this in the documentation repo at https://github.com/nextcloud/documentation and if you want you could send this in as a pull request directly to the pages in charge of this topic: https://github.com/nextcloud/documentation/blob/master/admin_manual/configuration_database/linux_database_configuration.rst

Thanks a lot for your work in the community regarding all the installation guides - they are really nice 🚀

[ghost]

not sure if i did it correct?! but i tried to ;-)

[hostingnuggets]

@riegercloud do you still need the binlog_format = ROW parameter if you don't use binary logging? For example I don't have binary logging enabled because of performance reason and because I don't use replication.