MariaDB 10.5 new features – Upgrading from MariaDB 10.4 to MariaDB 10.5
The most recent release from MariaDB ( MariaDB 10.5 Server ) is GA now. Before you plan upgrade from MariaDB 10.4 to MariaDB 10.5, I strongly recommend you to take a backup of your entire MariaDB database with Mariabackup , This is always recommended when you do an upgrade between major (MariaDB 10.5 is the current major stable version from MariaDB) releases. If you are interested to learn about MariaDB upgrade, We have explained about that in detail here. Also confirm, you are aware of incompatible changes between MariaDB 10.4 and MariaDB 10.5 to avoid unpleasant upgrade experiences and expensive rollback procedures. In this blog, We have explained in detail MariaDB 10.5 new features and upgrade procedure / process from MariaDB 10.4 to MariaDB 10.5, Your inputs and comments are most welcome.
What is coming new and getting deprecated with MariaDB 10.5 ?
- All the binaries of the past which used to begin with mysql is now ported to mariadb, Of course with corresponding symlinks to mysql command (MDEV-21303)
- MariaDB Server 10.5 includes MariaDB ColumnStore storage engine, Now you have the power to integrate both transactional and columnar storage engine in a single instance. I am yet to evaluate this together on production with a larger volume of transaction and data, So I will not comment technically benefits. But, This is a very interesting feature to have as I can use MariaDB infrastructure for both transaction processing and analytics.
- innodb_adaptive_hash_index default value now is OFF, This value used to be ON till MariaDB 10.4 . I personally like this move, If innodb_adaptive_hash_index is enabled, it improves / benefits only read-only workloads. whenever you operate MariaDB database infrastructure with innodb_adaptive_hash_index, It creates a serious performance bottleneck to the queries such as CREATE TABLE, CREATE INDEX, ALTER TABLE, ALTER INDEX, DROP TABLE, DROP INDEX, TRUNCATE TABLE etc.
- innodb_checksum_algorithm ( InnoDB’s innodb_checksum and XtraDB’s innodb_fast_checksum will be deprecated in future) is about InnoDB tablespaces are generated and validated:
- crc32: All new high performance algorithm but conflicts with the earlier version. But, it doesn’t support SPATIAL INDEX
- full_crc32 and strict_full_xrc32: Default innodb_checksum_algorithm value from MariaDB 10.5 with SPATIAL INDEX support. The newly created data files will carry a flag that indicates that all pages of the file will use a full CRC-32C checksum over the entire page contents (excluding the bytes where the checksum is stored, at the very end of the page). In the full_crc32 format, there no longer are separate before-encryption and after-encryption checksums for pages. The single checksum is computed on the page contents that is written to the file.
- strict_crc32, strict_innodb and strict_none: They are just like regular options but can be used only when setting up tablespaces for the first time. The InnoDB will halt if it comes across a mix of checksum values with this option set.
- innodb – This option is compatible with earlier versions of InnoDB.
- none – When set this option, InnoDB writes constant rather than a calculated checksum.
- MariaDB S3 is a read only storage engine to archive MariaDB tables in Amazon S3, or any third-party public or private cloud that implements S3 API and still have them accessible for reading in MariaDB. This is one feature we are super excited about and surely benefit several of our customers globally. Typically if you are FinTech, Ad.Tech, CDN or Gaming company, There will be several high volume data growth tables which would become fairly inactive with time, Though we can’t delete the data. In such cases we recommend our customers to archive data to the InnoDB tables maintained in a separate MySQL database instance built exclusively for archiving, S3 storage engine comes as an alternative in such scenarios where you have an option to move such a table to an archiving service, which accessible through as S3 API. The main benefit of storing things in an S3 compatible storage is that the cost of storage is much cheaper than many other alternatives. Many S3 implementations also provide reliable long-term storage. The S3 storage engine supports full MariaDB discovery. This means that if you have the S3 storage engine enabled and properly configured, the table stored in S3 will automatically be discovered when it’s accessed with SHOW TABLES, SELECT or any other operation that tries to access it. In the case of SELECT, the .frm file from S3 will be copied to the local storage to speed up future accesses. The MariaDB S3 storage engine support ALTER TABLE, DROP TABLE, SELECT and SHOW TABLES operations. The S3 storage engine is major new feature added in MariaDB 10.5, We are already doing lot of research across other areas of S3 storage engine like performance tuning, partitioning, caching, replication etc.
Options That Have Changed Default Values
| Option | Old default value | New default value |
|---|---|---|
| innodb_adaptive_hash_index | ON | OFF |
| innodb_checksum_algorithm | crc32 | full_crc32 |
| innodb_log_optimize_ddl | ON | OFF |
| slave_parallel_mode | conservative | optimistic |
| performance_schema_max_cond_classes | 80 | 90 |
| performance_schema_max_file_classes | 50 | 80 |
| performance_schema_max_mutex_classes | 200 | 210 |
| performance_schema_max_rwlock_classes | 40 | 50 |
| performance_schema_setup_actors_size | 100 | -1 |
| performance_schema_setup_objects_size | 100 | -1 |
Options That Have Been Removed or Renamed
| Option | Reason |
|---|---|
| innodb_checksums | Deprecated and functionality replaced by innodb_checksum_algorithms in MariaDB 10.0. |
| innodb_locks_unsafe_for_binlog | Deprecated in MariaDB 10.0. Use READ COMMITTED transaction isolation level instead. |
| innodb_stats_sample_pages | Deprecated in MariaDB 10.0. Use innodb_stats_transient_sample_pages instead. |
| innodb_rollback_segments | Deprecated and replaced by innodb_undo_logs in MariaDB 10.0. |
| max_long_data_size | Deprecated and replaced by max_allowed_packet in MariaDB 5.5. |
| multi_range_count | Deprecated and has had no effect since MariaDB 5.3. |
| timed_mutexes | Deprecated and has had no effect since MariaDB 5.5. |
| thread_concurrency | Deprecated and has had no effect since MariaDB 5.5. |
Deprecated Options
| Option | Reason |
|---|---|
| innodb_undo_logs | It always makes sense to use the maximum number of rollback segments. |
| innodb_buffer_pool_instances | Having more than one buffer pool is no longer necessary. |
| innodb_log_files_in_group | Redo log was unnecessarily split into multiple files. Limited to 1 from MariaDB 10.5. |
| innodb_log_optimize_ddl | Prohibited optimizations. |
| innodb_page_cleaners | Having more than one page cleaner task no longer necessary. |
| innodb_background_scrub_data_compressed | Problematic ‘background scrubbing’ code removed. |
| innodb_background_scrub_data_check_interval | Problematic ‘background scrubbing’ code removed. |
| innodb_background_scrub_data_interval | Problematic ‘background scrubbing’ code removed. |
| innodb_background_scrub_data_uncompressed | Problematic ‘background scrubbing’ code removed. |
| innodb_scrub_log | Never really worked as intended, redo log format is being redone. |
| innodb_scrub_log_speed | Never really worked as intended, redo log format is being redone. |
| large_page_size | Unused since multiple page size support was added. |
Conclusion
MariaDB 10.5 is looking very interesting with several new options for performance and reliability. We already have several customer use cases for MariaDB S3 storage engines for archiving and looks really promising. Like we all know by now that MariaDB is a different Relational Database Management System by itself with it’s own product roadmap and community commitments, The gap will get wider with MySQL on compatibility and feature list, All this for good in the longterm, Thanks for reading !
References:
- MariaDB Server 10.5 – https://mariadb.com/kb/en/mariadb-server-105/
