@Marko - will we at least be provided with a way to easily convert ONLINE these tables to the alternative? (PAGE_COMPRESSED=1)

I have large compressed tables on my 24-hour online write-intensive website.

This change seems to mean to me that I won't be able to upgrade my MariaDB version to 10.7+, once the COMPRESSED feature is completely removed...

Also, as discussed in MDEV-22839, PAGE_COMPRESSED doesn't really seem to work well for me. It even increased the non-sparse size of the data & indexes, when I tested...

I'm really not happy with this change.

> "Nowadays, with fast solid-state storage being a commodity,"

I would be careful in saying this.
Not everyone can afford AWS or similar (may save in storage, but doesn't save in data transfer out),
and many are still on Dedicated bare metal servers, where there's no choice to simply "increase" storage.

nunop, if this will be implemented, tables will continue to be readable, so ALTER TABLE or CREATE ... SELECT will work.

serg Thank you. However, this means that I have to go through a huge downtime to convert all the large (and critical, 24h online) tables into non-Compressed, to be able to continue upgrading MariaDB. And buy more storage.
That's the "complaint" here. The fact that we are being "forced" to do this, without MariaDB providing a way to do this ALTER TABLE in an Online/Non-Blocking way, without having to impact the online business.

I'd like to ask you, please, what's the final decision in relation to compressing tables in MariaDB.
I'd like to go ahead and make the changes required, sooner than later, in a way that it will be compatible in the future.

ROW_FORMAT=COMPRESSED is being obsoleted/removed, so I definitely need to go away from it.
PAGE_COMPRESSION=1 does not seem to work well for me, as I said in MDEV-22839, and marko also mentioned there's no much future.

So, I just want to confirm with you – there are no better options native of MariaDB planned, is that correct?
It will eventually not be possible to compress tables using MariaDB?

Thank you very much.

nunop A third option is storage-engine independent compression: https://mariadb.com/kb/en/storage-engine-independent-column-compression/. A note on timings is that if 10.6 is the last version to support ROW_FORMAT=COMPRESSED, it will still be supported for five years after its probable GA release later this year.

greenman Thank you for your suggestion.

I have actually tested column compression in the past.
The test was actually from "ROW_FORMAT=COMPRESSED" to "DYNAMIC" with the TEXT column compressed.

I think I wasn't very happy with the result at the time. While the "average row length" decreased, the Index increased significantly for some reason:

EDIT - Apologies. Didn't realize this would add to the attachments of this Ticket. It may be a bit misleading, thinking the Test Results are for the Ticket's original subject!

nunop, I meant if this will be implemented. May be it won't be, if it'll be too difficult for our users. May be there will be non-blocking online ALTER TABLE by that time. There is no final decision yet. At the moment of writing this comment, this MDEV is more like a wish or a request to remove the old, slow, and overcomplicated code. With the goal to make InnoDB faster and easier to maintain (= less bugs).

@Ian Gilfillan @Sergei Golubchik what's the suggested workaround from MariaDB for those who have most of their tables on ROW_FORMAT=COMPRESSED
Is it start converting them to PAGE_COMPRESSED=1; so they can still be writable after 10.6?

marostegui Please have a look at MDEV-22839.
marko said: "I do not see much future for PAGE_COMPRESSED, unless the tests that should be done in the scope of MDEV-11068 surprise me."

As per MDEV-22839, Page Compression didn't give me good results.

Column Compression didn't really work well for me, because of the error reported in MDEV-24797. It didn't give me confidence to use it.

For now, I migrated my databases to a server with a lot more storage, and disabled compression completely for now, until they decide what to do in terms of compression.

Until then, my costs are increased because I'm using way more storage, for fast-growing databases...

Thanks nunop, that's definitely a big deal for us too.
Not being able to write to compressed tables from 10.7 on is a deal-breaker for many of our big services and so is the possibility of not being able to compress them any longer.

Not to mention the fact that we'd need to rebuilt thousands of tables to remove the ROW_FORMAT=COMPRESSED
greenman marko any thoughts of how to move forward?

I understand their side, regarding ROW_FORMAT=COMPRESSED. It's messy code that they'd like to get rid of, and there's a big disadvantage that it has to have both the Compressed and Decompressed rows in memory.

The only problem is that at the moment there's no alternative I can use, that I'm confident of.
ROW_FORMAT=COMPRESSED was doing its purpose for me, except for the memory usage.

Based on the strong feedback, we do not have to remove the write support in 10.7. I think that the deprecation warning that was introduced in 10.6 (MDEV-23497) was the right thing to do. ROW_FORMAT=COMPRESSED has fundamental limitations; it cannot support all features, such as larger innodb_page_size or instant ADD COLUMN.

It would be nice to simplify our buffer pool code further. Would it be acceptable if the buffer pool memory usage or the write performance of ROW_FORMAT=COMPRESSED degraded further? I am thinking that we could keep only the uncompressed page image in the buffer pool and allocate and update the compressed copy on demand (when anything is being modified).

I think that write performance could have been already hit in 10.5 due to MDEV-12353 (we may be writing more log for ROW_FORMAT=COMPRESSED tables).

Thanks for the comment Marko, much appreciated.

There are of course tradeoffs as you mention, the ADD COLUMN is definitely something we could benefit from. However, in our case (again, just talking only from our infra point of view) the benefits of being able to compress tables and save footprint is definitely larger than being able to change innodb_page_size or adding columns instantly. But again, we have a very specific workload and very unique requirements.

Probably for smaller environments not compressing tables is something that might be easier to deal with and might not be a huge difference, for us, it would be a big pain as we have some pretty massive tables, not only cause we'd need to rethink our footprint strategy, but also case we'd need to rebuilt lots of tables, perform switchovers etc...

The approach of having just the uncompressed page doesn't sound bad to me, it can probably work well on most of read-bound environments (which is our case). I'd be happy to do early benchmarking for you with our data if that helps in any case, just ping me about it
I think it is of course highly dependent on each workload and something that needs to be benchmarked on each case, as some people might decide that that possible degradation isn't worth it, and rebuilding the tables without compression is a much better approach. Having the option to do both is key I would say, which is what you'd be giving if that approach is taken.

I concur with above comment. Having good compressions is much more important for us, than (for example) the instant addition of a column. Maybe it's an idea to see how a good alternative compression can be implemented before start dropping this feature?
I'm not sure how only keeping the uncompressed page in memory would altogether affect write performance? But for us RAM is less of an issue than storage. I do in deed see a slight degradation in write performance going from 10.4 to 10.5, but i cannot put exact numbers on that. However write performance for us is very important too. I also notice some degradation in write performance around every 2 hours since upgrading to 10.5, which is probably related to: MDEV-24949
Having said that i respect the process of moving forward and improving things. But an upgrade seems to be an daunting (and probably expensive) task by deprecating the compression support, and hopefully good alternatives will make it worth it.

ADD COLUMN on a COMPRESSed table – Would it help to simply say that that requires ALGORITHM=COPY?

Each feature is rarely used; the combination is probably extremely rare. That is this copout would hurt very few people.

marko Main reason for using ROW_FORMAT=COMPRESSED is to save disk space, we have a lot of customers actively using that option and deprecating the feature without an alternative that actually works the same, would be a big issue for several shops.

I strongly want to descourage this idea.

Thanks
Rick

I recommend not removing this feature as I see many customers using the InnoDB COMPRESSED row format to save space. It does not sound like a good idea.

Hello Marko,
I don't think is a good idea.
it would be limiting for many customers we have in RDBA,
in addition to removing a benefit in data compression.

I agree with the comments above.
But I wish we expressed these concerns before (yes, I mean me too). Because in 10.6 writing to COMPRESSED tables is disabled by default, and this sounds like a warning that we shouldn't rely on this feature. Even if writes were enabled again from now on, it would be hard for users to trust this feature again.

It would be easy to revert the default to the safer and more conservative value innodb_read_only_compressed=OFF in 10.6

rpizzi, f_razzoli, I wish you had expressed your objection in MDEV-23497 (making ROW_FORMAT=COMPRESSED tables read-only by default) a little earlier, in any case before the 10.6 feature freeze or the GA release.

Anyone who needs ROW_FORMAT=COMPRESSED tables can update the configuration file to include the following in their configuration file:

loose_innodb_read_only_compressed=OFF

In MDEV-22839, I suspect that the logical file size was misinterpreted as physical file size. Sorry, I should have checked those numbers more carefully when it was originally posted. I think that we will have to make an informed decision on this.

I was only informed of all this today, or of course I would have expressed my opinion earlier.
I don't see why we can't change the default of a variable in the next minor release. We did this a number of times already.

I apology for not speaking out before, in this case. But keep in mind that community members don't necessarily see the tasks in time to express their concerns.

So in the end what alternative remains for data compression?

Tokudb was dropped, now InnoDB loses this feature, storage independent compression has only zlib (which probably isn't very optimal), with MyRocks we have somewhat bad experience and compared to rest it's way more complicated to configure. (properly),
On filesystem level there is only ZFS and BRTFS which both are COW and quite terrible for the MySQL i/o.

After all the comments from this thread my understanding is that it won't be removed, it is "simply" disabled by default on 10.6 but it can be enabled if you just enable it on your my.cnf by adding:

loose_innodb_read_only_compressed=OFF

While there are good technical reasons for removing the ROW_FORMAT=COMPRESSED specific parts of the buffer pool code, that might be done in a way that would allow such tables to be written (just wasting even more memory and CPU).

A naïve implementation could be to simply try recompressing the pages on every modification, to ensure that when we would actually write out the page from the buffer pool, recompressing would be guaranteed to succeed. In this way, we would only hold the uncompressed page in the buffer pool.

I am afraid that without caching the previous contents of the page in the we would end up basically copying the entire ROW_FORMAT=COMPRESSED page to the redo log on every modification. If writes are going to be relatively rare, that could still be acceptable.

An option could be implemented to make the log records cover changes to the uncompressed page. That would require recovery to decompress and recompress pages. In MDEV-12353 the recovery was simplified, by using physical logging that refers to the compressed data.

In MDEV-27058, buf_block_t::lock and buf_block_t::frame were moved to buf_page_t. That greatly simplified some buffer pool code, and I think that it addressed the biggest pain points that I had with ROW_FORMAT=COMPRESSED.

If we moved the fields that are related to the adaptive hash index (MDEV-20487) to a separate descriptor that the block descriptor would point to, we might simplify code further and use the same type of block descriptor for all blocks, including ROW_FORMAT=COMPRESSED blocks whose uncompressed page frame has been discarded.

marko Are you saying that I had to go through all the pain of migrating from ROW_FORMAT=COMPRESSED to DYNAMIC for all my tables, have some unfortunate downtime, and after all, COMPRESSED is going to stay because your main issue with the code is already resolved?

It's fine anyway - I didn't like much that COMPRESSED requires double memory usage.

I'm just sad that PAGE_COMPRESSED and the COMPRESSED columns either don't work well (MDEV-24797) or didn't compress as much (MDEV-22839) as ROW_FORMAT=COMPRESSED.

Happy new year!

(by the way - seems that if I try to add an Emoji here, the comment submission fails! looks like JIRA isn't using UTF8MB4 )

Based on community feedback to MDEV-23497, and because MDEV-27058 already addressed the main technical motivation of removing the write support for ROW_FORMAT=COMPRESSED, I no longer think that we need to remove the write support in the foreseeable future.

Hi,

idiot question but... does this imply that ROW_FORMAT=COMPRESSED is going to be writeable by default in the future or the setting will continue being needed for that forever?

Ciao

PS, edited: I'm just trying to imagine what to do in the future, to continue with COMPRESSED (with or without the setting) for new installations or switch to any other, better / recommended alternative to it.

Ok, so now the 10.6 release notes page says:

We intended to deprecate and eventually remove the InnoDB's COMPRESSED row format. The first step was to make the tables read-only by default, but this plan was abandoned from MariaDB 10.6.6 (MDEV-23497) (MDEV-27736)

And also, from InnoDB's COMPRESSED Row format:

From MariaDB 10.6.0 until MariaDB 10.6.5, tables that are of the COMPRESSED row format are read-only by default. This was intended to be the first step towards removing write support and deprecating the feature.
This plan has been scrapped, and from MariaDB 10.6.6, COMPRESSED tables are no longer read-only by default.
From MariaDB 10.6.0 to MariaDB 10.6.5, set the innodb_read_only_compressed variable to OFF to make the tables writable.

Thanks!

stronk7, thank you. I have now also updated MDEV-23497 with a note that the plan has been cancelled.

Thanks for confirming, and for everything else, marko !