MySQL 8.0.28 Quick Peek – Percona Database Performance Blog

MySQL 8.0.28 Quick PeekOracle launched MySQL 8.0.28 on January 18th with little fanfare, as a part of their 4 occasions a yr launch cycle.  So what’s included within the new launch? Over the previous few years, there have been some cool new options included in these ‘dot’ releases that some locally say would have been higher off being labeled as a significant launch. However what’s in .28? Beneath are the extra attention-grabbing modifications within the database server and the shell, as there was not a complete lot modified within the different merchandise similar to router or MEM… I put my asides in italics and my views most likely don’t replicate the views of anybody else.

And keep in mind, 8.0.29 is due in April.

The TL;DR

The TL;DR synopsis is that a variety of glorious work went into MySQL Server & shell 8.0.28 however the urgent query is do you really want to put in it immediately?  On the dimensions from ‘you most likely ought to wait to improve’ to ‘replace ASAP’ it falls into the “you possibly can wait” class. If this was a vacation or birthday current, it will be the equal of receiving a twelve-pack of tube socks.

MySQL Server

The largest a part of the discharge notes particulars over 130 mounted bugs.  These fixes vary from fixing reminiscence leaks to new instrumentation within the Efficiency Schema.

New or Modified Performance

Windowing capabilities can now have as much as 127 distinct home windows.

InnoDB now helps ALTER TABLE ... RENAME COLUMN operations utilizing ALGORITHM=INSTANT.

The capabilities FROM_UNIXTIME(), UNIX_TIMESTAMP(), and CONVERT_TZ() now deal with 64-bit values on platforms that assist them, together with 64-bit variations of Linux, MacOS, and Home windows.  Possibly the 2038 bug gained’t have an effect however I ain’t canceling my journey to Cabo simply in case.

Now you can monitor and restrict the reminiscence allocation on a world and per-user foundation. Now you can observe the full reminiscence consumed by all consumer connections by checking the worth of the global_connection_memory standing variable. You’ll be able to specify limits on useful resource consumption for every consumer connection by the setting connection_memory_limit variable. A consumer whose reminiscence utilization exceeds this quantity can’t situation further queries. It’s also possible to impose a world reminiscence restrict by setting global_connection_memory_limit. Every time global_connection_memory exceeds that world restrict, no common customers can situation new queries requiring reminiscence utilization. System customers such because the MySQL root account usually are not certain by these limits.  I just like the observability technique however fear about draconian use to throttle customers and see the opportunity of too many queries being run as root to bypass the brand new limits.

Oops

The GnuPG key used to construct the packages expired in mid-February and lots of making an attempt to replace their servers from an earlier model might have seen nastygrams when their bundle administration software program tried to confirm the software program.  The repair is to obtain the YUM or APT repo once more or obtain the MySQL GnuPG public key and add it to your system’s GPG keyring.

Deprecation and Elimination Notes

The shortcuts ASCII, for CHARACTER SET latin1, and UNICODE, for CHARACTER SET ucs2, at the moment are deprecated. Additionally, the character units ucs2, macroman, macce, dec, and hp8 with all of their collations at the moment are deprecated. Time to shift to UTF8MB4, in case you haven’t already.

Sorting Out The Sorting

Kinds of some column sorts (not listed), together with JSON and TEXT, typically exhausted the kind buffer if its measurement was not a minimum of 15 occasions that of the biggest row within the kind. Now the kind buffer want solely be solely 15 occasions as giant as the biggest kind key. Hopefully, this improves sorting occasions.

MySQL Shell

The MySQL Shell appears to be gaining new options and capabilities each launch at a formidable charge.  What began just a few years in the past as a substitute for the outdated CLI program has turn into a Swiss Military Knife of MySQL use.

Shifting Nodes

Shifting an InnoDB cluster node from one cluster to a different is now facilitated by dropping and re-creating the cluster metadata schema on the occasion to make sure that it corresponds precisely to that for the brand new cluster.  This can be a “good to have” change when taking part in musical chairs with nodes.

SSH Tunnels

MySQL Shell now helps SSH tunneling for connections to MySQL server cases. An SSH tunnel might be shared between connections to the identical host from the identical consumer connecting from the identical distant server occasion. MySQL Shell’s Secret Retailer can retailer passwords and passphrases for connection to an SSH server and for the identification file, to be routinely retrieved for future connections. A brand new shell.listSshConnections() operate returns a listing of the energetic SSH tunnels.  This needs to be helpful for cloud conditions the place safety is a giant concern.

Multi-Issue Authentication

MySQL 8.0.27 and better contains assist for multi-factor authentication. This functionality contains types of MFA that require as much as three authentication values. The authentication_policy system variable defines what number of authentication elements accounts might have (or are required to have) and the authentication strategies that can be utilized for every issue. To allow authentication to the MySQL server utilizing accounts that require a number of passwords, MySQL Shell now helps the --password1, --password2, and --password3 choices for command-line connections, allowing as much as three passwords to be specified. The prevailing --password choice and the --password1 choice are handled as equal. You’ll be able to insecurely specify a password worth following the choice on the command line (please don’t do that), or if the choices are given with no password worth, the MySQL Shell will immediate the consumer for every password in flip. These choices are solely supported for traditional MySQL protocol connections made utilizing command-line arguments. So not within the X Protocol?!

Skip Grant Tables and util.dumpX

MySQL Shell’s occasion dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and desk dump utility util.dumpTables() couldn’t be used with a MySQL server occasion began with the --skip-grant-tables choice, that means that the utilities couldn’t be used to extract knowledge on this emergency scenario. The utilities now detect the particular account used for all connections on this scenario and assume that account has all accessible privileges. The customers choice can be routinely set to false as a result of some grant info can’t be collected on this scenario.  This needs to be helpful in dire conditions!

Delayed Index Era

MySQL Shell’s dump loading utility util.loadDump() can defer the creation of secondary indexes till after the desk knowledge is loaded, with the deferTableIndexes choice. Beforehand, the deferred indexes couldn’t be created on tables with a secondary engine outlined, as a result of DDL statements can’t be executed on these tables. The utility now removes the SECONDARY_ENGINE clause from the desk if indexes are being deferred, and provides it again in after the indexes have been created.  This does make restoration of dumps a lot faster.

Leave a Reply

Your email address will not be published.