# MariaDB: innodb_checksum_algorithm and innodb_use_atomic_writes on FreeBSD 13 + ZFS?



## kalleboy (Nov 4, 2021)

Greetings! Got two questions;

1- I'm getting in mysql.log; 

```
2021-11-04 11:19:12 0 [Warning] InnoDB: Setting innodb_checksum_algorithm to values other than crc32, full_crc32, strict_crc32 or strict_full_crc32 is UNSAFE and DEPRECATED. These deprecated values will be disallowed in MariaDB 10.6.
```
I'm on ZFS (FreeBSD) and I've read that I wouldn't need it because ZFS already does it. So, I set; `innodb_checksum_algorithm` as `none`. Would that be any damage sooner or later?

2- Does a combination of FreeBSD 13 + ZFS support: `innodb_use_atomic_writes=1` on MariaDB?
Because I've been reading that, it should only be used with hardware and file systems that guarantees that 64K writes are atomic (and if you have specialized hardware and drivers to do that).
If not, why it comes enabled by default?

my server.cnf (settings for performance gain):

```
innodb_doublewrite = 0
innodb_flush_method = O_DSYNC
innodb_checksum_algorithm=none
innodb_log_write_ahead_size=16384
innodb_flush_neighbors=0
innodb_use_atomic_writes=0
innodb_read_io_threads = 10
innodb_write_io_threads = 10
innodb_fast_shutdown = 0
```

Many thanks.


----------



## dbdemon (Nov 5, 2021)

kalleboy said:


> 1- I'm getting in mysql.log;
> 
> ```
> 2021-11-04 11:19:12 0 [Warning] InnoDB: Setting innodb_checksum_algorithm to values other than crc32, full_crc32, strict_crc32 or strict_full_crc32 is UNSAFE and DEPRECATED. These deprecated values will be disallowed in MariaDB 10.6.
> ...


As the message says, the allowed values will be as stated from 10.6 onwards, and "none" is not one of them. This is also restricted in older versions, see the documentation for details.



kalleboy said:


> 2- Does a combination of FreeBSD 13 + ZFS support: `innodb_use_atomic_writes=1` on MariaDB?
> Because I've been reading that, it should only be used with hardware and file systems that guarantees that 64K writes are atomic (and if you have specialized hardware and drivers to do that).
> If not, why it comes enabled by default?


I'm no expert on this, but my understanding is that it being turned on just means that it _can_ be used. If it's turned on AND MariaDB detects that you have hardware support for atomic writes, only _then_ will it be used.

Relevant documentation:








						InnoDB System Variables
					

List and description of InnoDB-related server system variables.




					mariadb.com
				











						Atomic Write Support
					

Enabling atomic writes to speed up InnoDB on selected SSD cards.




					mariadb.com


----------



## Alain De Vos (Nov 5, 2021)

It's rather easy. If you don't want checksums from mariadb106 you specify for the variable innodb_checksum_algorithm the value "strict_none" instead of "none".


----------



## kalleboy (Nov 9, 2021)

"I'm no expert on this, but my understanding is that it being turned on just means that it _can_ be used. If it's turned on AND MariaDB detects that you have hardware support for atomic writes, only _then_ will it be used." but are we sure about that?


----------



## dbdemon (Nov 9, 2021)

kalleboy said:


> "I'm no expert on this, but my understanding is that it being turned on just means that it _can_ be used. If it's turned on AND MariaDB detects that you have hardware support for atomic writes, only _then_ will it be used." but are we sure about that?


Can we ever be certain about anything?  `innodb_use_atomic_writes` is turned on by default for everyone, and as per the description on the InnoDB System Variables page what it does is: _Implement atomic writes on supported SSD devices._ So, logically, if it's turned on on a non-supported device, then it won't work. And since it's turned on for everyone by default, then apparently no harm comes from it being turned on on non-supported devices.

Does that help?


----------



## Alain De Vos (Nov 9, 2021)

When is this important. I think only on power outage or crash. Which should happen as few as possible. ?


----------



## kalleboy (Nov 10, 2021)

Thanks for all your replies. 

"since it's turned on for everyone by default, then apparently no harm comes from it being turned on on non-supported devices." then that's fine! 

Indeed, power outage or crash should always be as few as possible - that's critical fact here as well in our case. 

And any feedback/suggestions for the other sql optimization lines of mine at the first post?

Regards.


----------

