The Evolution of InnoDB variables in MySQL 8.4: What’s New and Exciting

INNODB

InnoDB is the popular and default storage engine in MySQL database. In this post, we’ll look at what’s new in InnoDB for MySQL 8.4, why the changes are significant, and how you can use them to create quicker, smarter, and more reliable DB environment. Prepare to step up your database administration game.

Advantages:

* InnoDB is famous for it’s ACID level support which is indeed for MySQL databases to perform read and write operations.

* InnoDB is known for it’s Row-level Locking. Row-locking is suitable for multi-user databases and highly concurrent environments.

* MySQL InnoDB provides foreign key constraints, which allows and preserve data integrity.

Performance of InnoDB:

The engine was built for both parallel and concurrent workload, including read and write operations. InnoDB’s performance is further enhanced by advanced features like as adaptive hash indexing and workload-specific performance adaptation.

Indexing in InnoDB:

Advanced indexing features including cluster and adaptive hash indexes are supported by InnoDB in addition to full-text indexing. In order to facilitate quick lookups and range searches, it employs a particular index storage structure. To speed up query processing, InnoDB generates hash indexes for frequently requested data, and index compression reduces disc space and enhances I/O performance.

InnoDB Crash Recovery:

A efficient crash recovery mechanism is a feature of InnoDB. For automatic crash recovery, it makes use of a double-write buffer and transaction log. To preserve data integrity, the engine’s rollback mechanism allows it to revert to a consistent state.

Features and Changes in MySQL 8.4:

MySQL Native password:

• The MySQL Native password is used to perform authentication based on password hashing.
• From MySQL 8.0.34, MySQL Native password authentication is deprecated and produces a warning if an account tries to authenticate using a MySQL Native password.
• Due to it’s weak ability and other strong options available, it was removed from default in MySQL 8.4.
• mysql_native_password uses SHA-1 to hash the passwords, and it was vulnerable to many cyber attacks.
• Also, it lacks salting, i.e., salting in cryptography refers to adding more data before it gets hashed. mysql_native_password lags this criteria.
• From MySQL 8.4, mysql_native_password was replaced by caching_sha2_password due to its superior security.

MySQL 8.0	MySQL 8.4
ENABLED BY DEFAULT.	REMOVED FROM DEFAULT.

Innodb_buffer_pool_instances:

• The InnoDB buffer pool instances is a parameter that tells about the number of instances (or partitions) into which the InnoDB buffer pool is divided.
• The uses of this parameter are to cache the data in a memory buffer pool instance, reduce the IO operations, and improve query performance.
• In MySQL 8.4, the value is based on the server’s total physical memory. This behaviour helps in boosting performance out of the box, especially for systems with variable resource limits. Up to MySQL 8.0, it was 8.

Innodb_change_buffering:

• The innodb_change_buffering is a parameter that buffers changes in secondary index pages before it caches them to disc.
• When you perform an INSERT, DELETE, or UPDATE that modifies a secondary index, InnoDB can buffer these changes in the change buffer (part of the InnoDB buffer pool) instead of applying them immediately to the index on disc.
• Permitted values for InnoDB buffering:

NUMERIC VALUE	NUMERIC	DESCRIPTION
0	NONE	DEFAULT.HERE BUFFERING IS NOT DONE TO ANY OPERATIONS.
1	INSERTS	BUFFERS INSERTS OPERATIONS.
2	DELETES	BUFFERS DELETE OPERATION.
3	CHANGES	BUFFERS INSERTS AND DELETE OPERATIONS.
4	PURGES	BUFFERS PHYSICAL DELETES OPERATIONS THAT HAPPENS IN BACKGROUND.
5	ALL	BUFFERS INSERTS AND DELETE MARKING OPERATIONS.

From MySQL 8.4, the default value for change_buffering is set to 0. You can enable buffering for inserts and deletes operations manually by configuring the cnf file.

MySQL 8.0	MySQL 8.4
All	NONE

Innodb_doublewrite_pages:

• The innodb_doublewrite_pages parameter in MySQL determines how many pages are written to the doublewrite buffer in a single batch. This functionality is part of InnoDB’s crash recovery system and protects data integrity if the database crashes during a write operation.
• In MySQL 8.4 the default value is set to 128 and before MySQL 8.0 it was 4 by default. You can set any values by dynamically.

MySQL 8.0	MySQL 8.4
4	128

InnoDB adaptive hash Index:

• Adptive Hash Index is an Innodb storage engine feature that improves query performance by using adaptive hash index. The adaptive hash index (AHI) works by creating hash indexes for pages of data that are frequently accessed. When InnoDB detects that certain data pages are being accessed repeatedly (particularly in read-heavy workloads), it may choose to create a hash index on that data to speed up future lookups.
• In MySQL 8.4 it is off by default. If we need to enable this variable, we can use the set global command, and we can enable this variable.
• Sometimes Accessing Adaptive Hash Index was a source of contention under heavy workloads like mutliple join queries with like operators and % not benefitted by AHI. So turning it off reduces the unnecessary performance overhead.

MySQL 8.0	MySQL 8.4
ON	OFF

Innodb_io_capacity:

• The innodb_io_capacity parameter in MySQL’s InnoDB storage engine determines the number of I/O operations that InnoDB will attempt to perform per second for background tasks like flushing dirty pages from the buffer pool to disc and handling other maintenance operations.
• MySQL 8.4 sets the default value to 10000, while MySQL 8.0 sets it to 200. In MySQL 8.4, you can perform more operations that include more IO operations.
• For a busy scenario, you can set the value to high, helping the server to take care of a high rate of row changes. You can set the value by dynamically.

MySQL 8.0	MySQL 8.4
200	10000

Innodb_log_buffer_size:

• In MySQL, the InnoDB log buffer size determines how much space InnoDB uses to retain transactions before writing them to disc. It was used by InnoDB’s redo logs before being written to disc.
• The redo logs are a disk-based data format used by InnoDB during crash recovery to correct data.
• In MySQL 8.4, the default buffer size is set to 64MB, a large buffer size allows lengthy transactions to run without having to send them to logs before they are committed. This corresponds to the work scenario when we have to insert, update, and delete a large number of rows.In MySQL 8.0, it was 16 megabytes.

MySQL 8.0	MySQL 8.4
16	64

Innodb_page_cleaners:

• The innodb_page_cleaners configuration option specifies the number of page cleaner threads in MySQL’s InnoDB storage engine. These threads are liable for flushing dirty pages from the buffer pool to the disc.
• It was set to the same value as innodb_buffer_pool instances in MySQL 8.4 the previous value in 8.0 was just 4. If the value of page cleaner exceeds the number of buffer pool instances, it will automatically adjust to the same value as buffer_pool_instance.

MySQL 8.0	MySQL 8.4
4	BASED ON INNODB BUFFER POOL INSTANCES.

Temptable_max_ram:

• The temptable_max_ram is a variable is a system variable used to define the amount of total memory that MySQL is allowed to use it for temprory tables before it stores to the disk.
• In MySQL 8.4 it was set to 3% of total available on the existing server and in MySQL 8.0 it was 1 GB.

MySQL 8.0	MySQL 8.4
1 GB	3% of TOTAL MEMORY

MySQL Replication: SOURCE_RETRY_COUNT change:

• In a MySQL 8.4 replication setup, SOURCE_RETRY_COUNT is set to 10 for the Replication Source Change statement. The Replication Source change is used to change the source of the replica and read data from it.
• In prior versions 8.0, it was set to 60, which resulted in replica waits of 60 seconds and a 10-minute wait time before timing out.

MySQL 8.0	MySQL 8.4
60 SEC.	10 SEC.

FLUSH_PRIVILEGES :

• The flush privileges command is used to reload the table into memory and apply any modifications made to user accounts, privileges, or roles. It only works as a flush privilege statement, unlike the reload statement, which is also supported in MySQL 8.4.
• When upgrading from 8.0 to 8.4, it was checked to see if any users had the Flush privileges permission. If no one has the FLUSH PRIVILEGE permission, users with reload permission will be automatically provided the new privilege.
• If you downgrade MySQL from 8.4, a user who has previously been granted this permission will be unable to utilise the flush privileges statement.

MySQL 8.0	MySQL 8.4
RELOAD	FLUSH_PRIVILEGES

Conclusion:

With its dynamic new InnoDB variables, MySQL 8.4 represents a significant advancement that allows for enhanced durability, quicker speed, and more intelligent storage optimisation. With these enhancements, optimising your database for current workloads is now simpler than before. In future posts, we will go deeper into additional variables.

References:

https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html

https://blogs.oracle.com/mysql/post

https://releem.com/docs/mysql-performance-tuning/innodb_change_buffering

https://lefred.be/content/mysql-8-4-lts-new-production-ready-defaults-for-innodb/

https://phoenixnap.com/kb/myisam-vs-innodb