Mysql/mariadb & utf8mb4 - is ROW_FORMAT=COMPRESSED necessary?

I’m running a fairly small Nextcloud server with MariaDB database backend. I recently followed the instructions to upgrade mysql database to use utf8mb4 and I hit a snag and it took me a while to fix it.

I had ROW_FORMAT set to DYNAMIC and PAGE_COMPRESSED set to 1. PAGE_COMPRESSED=1 causes MariaDB to compress tables with a given compression algorithm (in my case lz4, but there’s a plethora to choose from).

ROW_FORMAT cannot be set to COMPRESSED with PAGE_COMPRESSED set to 1 at the same time. Those are two different and mutually exclusive approaches to table compression.

When I run occ maintenance:repair it complained cryptically that ROW_FORMAT was not supported, which I found is a typical error when you try to set ROW_FORMAT=COMPRESSED and PAGE_COMPRESSED=1 on the same table.

After browsing through the code I found this snippet in Collation.php:

if ($characterSet === 'utf8mb4') {
    // need to set row compression first
    $query = $this->connection->prepare('ALTER TABLE `' . $table . '` ROW_FORMAT=COMPRESSED;');
     $query->execute();
}

And here comes my question. Is it really necessary to set ROW_FORMAT=COMPRESSED on utf8mb4 tables? I’m not a database guru by any means, but I do not see any necessity to have ROW_FORMAT=COMPRESSED here. I agree that with plain uncompressed InnoDB people should be advised to set ROW_FORMAT=COMPRESSED to save space and that like, but ROW_FORMAT=DYNAMIC works too. Perhaps there are any particular reasons for that?

Hi, it is not necessary to set it to COMPRESSED. I’m wondering why you think you have to do it.
From the Nextcloud docs for NC15 it actually asks you to change everything to
ROW_FORMAT=DYNAMIC
if not already set:
https://docs.nextcloud.com/server/15/admin_manual/configuration_database/mysql_4byte_support.html#enabling-mysql-4-byte-support

So you seem to have it correct already according to the docs.

Hope that helps!

I think you misunderstood the question.
@JakubP found out, that Nextcloud itself alters the tables to ’ROW_FORMAT=COMPRESSED’ in collation.php and wonders if that is really necessary.

1 Like

Oh man … Sorry, I really missed that.
Indeed very strange, when the admin guide is instructing manual steps to achieve the opposite.
Sorry again for misunderstanding the question.

1 Like

That’s exactly what I was asking. I’m wondering what’s the rationale behind enforcing ROW_FORMAT=COMPRESSED.

Well, compressed is very close to DYNAMIC and it can’t hurt by any means IMO.

See https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html#innodb-row-format-compressed