Is chattr +C needed in mariadb databases??

[jfabernathy]

I use mariadb-server in a lot of my systems. Now that all of my systems use btrfs, is there a concern?

I was pointed to a link https://gitlab.archlinux.org/archlin...re/-/issues/58 discussing this and it may imply that it was fixed in the package 2 years ago.

Anyway of knowing if the Ubuntu package for mariadb-server has this implemented??

[jfabernathy]

FYI, this is what I see in the directories. Maybe this means that mariadb installs with COW disabled?

# lsattr /var/lib/mysql/
---------------C------ /var/lib/mysql/aria_log_control
---------------C------ /var/lib/mysql/aria_log.00000001
---------------C------ /var/lib/mysql/ibdata1
---------------C------ /var/lib/mysql/ib_logfile0
---------------C------ /var/lib/mysql/mysql
---------------C------ /var/lib/mysql/performance_schema
---------------C------ /var/lib/mysql/sys
---------------C------ /var/lib/mysql/mysql_upgrade_info
---------------C------ /var/lib/mysql/multi-master.info
---------------C------ /var/lib/mysql/ddl_recovery-backup.log
---------------C------ /var/lib/mysql/mythconverg
---------------C------ /var/lib/mysql/ib_buffer_pool
---------------C------ /var/lib/mysql/ddl_recovery.log
---------------C------ /var/lib/mysql/ibtmp1
---------------C------ /var/lib/mysql/eos-mythtv-master.pid
​

[oshunluvr]

Not seeing that here but my mysql DB is on my 20.04 server (all btrfs) so maybe a change on the newer systems. My main db user is my Plex server and it's used all the time.

Not sure what you would consider a "concern." My server has been running with only btrfs since 2016 or so although there have been a few drive changes - mostly for storage expansion. I did have one drive failure at 64,000+ power on hours. Not sure I can attribute that to btrfs. A couple backup drives on my desktop (pulled from the server due to upgrading) are at 70k and 80k plus hours using btrfs.

Based on this it seems to be more a performance thing than a wear-and-tear thing.

[jfabernathy]

Thanks, What I found out further is that Kubutnu 22.04 LTS version of mariadb, when installed on my production machine, didn't have chattr +C set for the mysql directories and all the files do NOT have the COW disabled. Only my test system with Endeavour OS (Archlinux) has that set to COW disabled.

My whole /var/lib/mysql directory is only 500MB with the only active database being less than 100MB. It's a mythtv database and only changes when data changes related to the metadata for TV recordings. So very infrequent updating.

I think I'm going to leave the production system alone until it's time to move to the new LTS and make sure that uses COW disabled for mysql/mariadb

Jim A

[oshunluvr]

Probably an Arch default then - just a guess

[GreyGeek]

FYI, this is what I see in the directories. Maybe this means that mariadb installs with COW disabled?

# lsattr /var/lib/mysql/
---------------C------ /var/lib/mysql/aria_log_control
---------------C------ /var/lib/mysql/aria_log.00000001
---------------C------ /var/lib/mysql/ibdata1
---------------C------ /var/lib/mysql/ib_logfile0
---------------C------ /var/lib/mysql/mysql
---------------C------ /var/lib/mysql/performance_schema
---------------C------ /var/lib/mysql/sys
---------------C------ /var/lib/mysql/mysql_upgrade_info
---------------C------ /var/lib/mysql/multi-master.info
---------------C------ /var/lib/mysql/ddl_recovery-backup.log
---------------C------ /var/lib/mysql/mythconverg
---------------C------ /var/lib/mysql/ib_buffer_pool
---------------C------ /var/lib/mysql/ddl_recovery.log
---------------C------ /var/lib/mysql/ibtmp1
---------------C------ /var/lib/mysql/eos-mythtv-master.pid
​

That's exactly what it means.

Mariadb adding that to their installation routine saves the user a LOT of work.

When installing PostgreSQL on a BTRFS system one has to use the chattr +C command on /var/lib/postgres/data (IIRC) while that folder is empty. A db created after installation but before the nocow flag is set will not be given the nocow attribute and most likely will be corrupted during use.

I've been using BTRFS since 2016 and have run PostgreSQL several times on my system (Also in a qemu/kvm VM) and I've never experienced and data corruption of loss on my databases.

[oshunluvr]

That makes sense. I have mysql and postgresql on my server, but not maria.

[jfabernathy]

I did discover that a normal install of mariadb-server on Kubuntu 23.04 on a btrfs volume does NOT set the COW disable attr.

I was able to

mkdir -p /var/lib/mysql

first and then

chattr +C /var/lib/mysql

before the

apt install mariadb-server

and that does setup all the right COW disable bits.

[jlittle]

IIUC the "no COW" attribute for databases is not necessary, but without it there'll be a lot of fragmentation on writes.

[GreyGeek]

IIUC the "no COW" attribute for databases is not necessary, but without it there'll be a lot of fragmentation on writes.

For sure!

Without nocow on the db directory, as you wrote, massive numbers of db writes creates massive fragmentation which results in slow down and can eventually can cause BTRFS to switch to read only, which leads to failing. This has been reported many times in the past, which is why nocow on database folders has always been included in the BTRFS documentation. That is also why I always use it when I run PostgreSQL.

Some have suggested that the massive fragmentation that nocow avoids can be negated by running the balance command at regular intervals. I've never used balance as a means of bypassing nocow on db directories, but it may work if balance is set up to run as a cron job. Then systemd's cron.service would run it at what ever interval you set for it. Fstrim is often run following a balance operation but it runs as a cron job every five days. However, the overuse of fstrim can decrease the life of an SSD if you are running on one. Using nocow on the db folder seems so much easier than not using it.

[jfabernathy]

IIUC the "no COW" attribute for databases is not necessary, but without it there'll be a lot of fragmentation on writes.

I just ran defragment on my boot SSD and it ran quickly. That should take care of the mythtv mariadb database for another year. When the next LTS comes out I'll create /var/lib/mysql and chattr +C before I install mariadb.