DietPi-Software | Baikal: Switch from MariaDB to SQLite?

[nillebor]

great job, dietpi is amazing!

but I would suggest changing the dependencies for baikal from mysql to sqlite. Other software such as kodi or vaultwarden/bitwarden and others also use sqlite. sqlite is a lightweight and powerful database. if possible, the most appropriate database should be used. i have been using bailkal with sqlite for years on different devices.

i would still like prosody - a small jabber selfhost server

the fix for the clock time with and without dhcp is apparently already fixed and available in the release - so I have nothing to complain about anymore.

[MichaIng]

Many thanks for your feedback and suggestion.

Generally I agree to use SQLite whenever it is better suited, especially when the stored data is not expected to be large so that benefits of caching and a dedicated database server in general are negligible. However, Baikal stores calendar and contact data, which can be large lists, where using a single (then large) database file and the invocation of an external command and disk read and/or write on every access has many downsides. Finally it depends on usage and hardware (disk speed, RAM size and speed), so best would be to implement a choice, but as of now dietpi-software has no database choice for software but uses the one that seemed best suitable to us.

To make following both easier, please split the Prosŏdy IM server request into a dedicated issue, or directly a software request discussion using this template: https://github.com/MichaIng/DietPi/blob/master/.github/ISSUE_TEMPLATE/software_request.md

[nillebor]

thanks for your quick response.

i have been using sqlite and baikal with over 4000 entries for years, also on the dietpi. the databases work very much the same, but mysql is much larger and more complex. but never changes that the original databases (db-file) are very identical in size.

sqlite is also used for other software that have no fewer entries. especially based on the performance of the pi, the faster and smaller system should be the better choice.

i see no problem why sqlite should be worse than mxsql.

give it a try

edit: i would still like a small change:

• backup is currently done daily, a weekly, month or selection of days I would find better as an option
• automatic update without login, selection by day, week or month. i'm currently using a script, but would be nice in the menu as an option

[MichaIng]

MariaDB is a caching database server, it keeps everything in memory and also holds writes in a dedicated caching layer, when required. Also it has and uses compression where applicable, indexing and many other features to speed up queries. So it is faster and reduces disk I/O compared to SQLite.

the faster and smaller system should be the better choice.

SQLite is smaller, but MariaDB is faster, that's the trade-off, and as said, it depends on the hardware and personal usage (how often calendar/contacts are synced, number of clients, how often data is changed etc etc) whether the performance bonus of MariaDB weights out additional overhead of a dedicated database server, it's constantly running processes and RAM usage.

Another point is that one MariaDB can be used by multiple software titles, in which case its benefits raise, and we have many software options which do use MariaDB (and either cannot use SQLite or store a lot of data, like cloud servers). So as fast as you e.g. install Nextcloud, it would be a waste to not use the anyway required MariaDB database server with Baikal as well. This was also the major argument we discussed when choosing the database system for vaultwarden, though finally the common amount of passwords one stores, and the related data size is so small (much smaller than calendar and contacts data, usually), that we chose SQLite.

SQLite does not benefit at all when used by multiple processes as everyone needs to do its very own sqlite3 command call, without any query management being done, done to an uncached (aside of native Linux filesystem caching), uncompressed, non-indexed, possibly large single database file. Pi-hole is a great example showing the inefficiency of SQLite when listing/searching 1 year of DNS queries.

---

I don't use Baikal so cannot say what backup options it has, or if it has one at all. Otherwise use a cron job like you do for the update (?).

[nillebor]

`i would still like a small change:

backup is currently done daily, a weekly, month or selection of days I would find better as an option
automatic update without login, selection by day, week or month. i'm currently using a script, but would be nice in the menu as an option

`

i mean the backup function in dietpi. could it be extended to a week instead of daily?

an autoupdate works with script but a setting when the script is started would be nice to set graphically like cron jobs (settings)

[MichaIng]

Ah, you mean for dietpi-backup. Yes we already had that in mind, e.g. also something like keeping the first X iterations daily and then Y weekly iterations etc.

For dietpi-update we're not gonna implement an auto-update and we do not recommend to apply DietPi updates unattended. We often use it to inform you of important changes, giving you a choice about how to proceed with a choice, sometimes do software reinstalls to migrate to a new setup or version, all this which you really shouldn't do overnight, having questions answered with default and a possible surprise in the morning 😄.

But these topics should be again discussed in a dedicated issue, i.e. the dietpi-backup topic, for dietpi-update my answer is definite 😉. Keeping it here makes it impossible for me to keep track and for others to find and second this feature.