Key Takeaways
- Agile software development is simple, collaborative, and sustainable
- MySQL 8 has made improvements toward greater agility
- Simplicity-related new features include a centralized data dictionary, and roles as collections of privileges
- Sustainable development-related features include a fully automated upgrade, CTEs for reusable result sets, reusable SSL sessions, and standards based regular expressions
- Collaboration-related features include multi-factor authentication, being able to differentiate between system users and regular users, and provisioning to associate comments and attributes with new users
The concept of Agile software development is characterized by simplicity, collaboration, continuous delivery of working software, and sustainable development. With its new features, MySQL 8 makes using it as a database in the Agile software development process more suitable. In this post we will discuss how the relational MySQL database server is more Agile.
Simplicity
One of the Agile principles emphasizes “Simplicity–the art of maximizing the amount of work not done…”. Before MySQL 8.0, the data dictionary was scattered across metadata files, non-transactional tables, and storage-engine specific transactional tables. MySQL 8.0 has centralized the data dictionary by storing in transactional tables in the InnoDB engine. As a result, the same transactional features such as commit and rollback are available to data dictionary tables as to user tables. The filesystem-based data dictionary had several issues, including slow directory and file scans, added requirements for file system management and storage space, and having to develop code for file system-based data dictionary.
Other improvements to the data dictionary include:
- An object cache to cache recently used data dictionary objects in memory for faster reuse.
- Partitions to separate the different types of definition objects.
InnoDB engine-based table DDL operations such as CREATE, ALTER, and DROP statements for databases, tablespaces, tables, and indexes are combined with data dictionary updates, storage engine operations, and binary log writes as a single unit to be committed/rolled back together.
Consider the use case that multiple users need to be granted privileges, which is quite typical. Prior to MySQL 8 you would assign the requisite privileges individually to each user. As these could be the same set of privileges granted individually to separate users, a lot of SQL GRANT statement code is run again and again. MySQL 8 has simplified granting privileges by introducing roles. A role is a named collection of privileges, and instead of granting the same set of privileges to individual users, users can be granted role/s.
Sustainable Development
Automation makes software development sustainable. MySQL 8 has made the server upgrade process fully automated. Prior to version 8, a user or DBA had to run the mysql_upgrade to perform some of the upgrade tasks including upgrading the system tables in the mysql schema, the objects in the sys schema, and the objects in the other schemas. And mysql_upgrade does not update the contents of the help tables. MySQL 8 performs all of these upgrade tasks automatically on server restart after an upgrade.
Another factor influencing sustainable development is efficient use of SQL. MySQL 8 introduces Common Table Expressions (CTEs) for reusable result sets. To serve the purpose of complex SQL queries that contain sub-queries whose result is used within top-level queries, a CTE defines a named, temporary result set that is generated by a subquery and reused in an outer SQL query statement. The same CTE can be reused multiple times in a single top-level SQL query statement. Further, a CTE can be made recursive, which is being able to refer to itself, with the RECURSIVE keyword.
Another sustainable development feature is that SSL sessions can be reused in MySQL 8. With some server-side runtime configuration and client-side configuration, SSL sessions can be reused. The server/client configuration helps ensure that the server keeps its session cache in memory for a specific time (with a timeout), and the client keeps a cache of active sessions.
Some other notable improvements are that regular expressions support has been reimplemented using the International Components for Unicode (ICU), and expressions can be used in the default value specification of data types.
Continuous availability of working software
One of the factors that working software depends on is system resources such as the CPU. To help ensure that sufficient CPU is available for different threads running system and user processes, MySQL 8 introduces resource groups for resource management. The CPU resource is quantified as “virtual CPU” which includes hyperthreads and CPU cores. A DBA can create multiple resource groups, associate the available CPU with resource groups, and allocate threads to the resource groups. By defining CPU affinity for resource groups and thread priority for system and user threads, the CPU resource is better managed so that software that depends on MySQL continues to get uninterrupted access to the database.
Enhanced User Collaboration
MySQL 8 adds support for enhanced collaboration with a user. Support for multi-factor authentication has been added. What multifactor implies is that a user may choose to add multiple authentication factors such as multiple passwords, or other forms of authentication such as email, phone number, and pluggable methods provided by the authentication plugins.
A user can annotate the CREATE USER and ALTER USER statements with any comments using the COMMENT clause, or attributes using the ATTRIBUTE clause. A user may associate some meta-information about a user using comments and attributes.
Further, to simplify identifying the type of a user, system, or regular user, a new SYSTEM_USER privilege has been introduced in MySQL 8. A user with this privilege is a system user, and a user without this privilege is a regular user. Global privileges have been fine-tuned with the provision to exclude some schemas. With the new partial_revokes system variable enabled, a user can be granted privileges at the global level, implying privileges for all schemas while revoking the privileges from specific schemas.
Window Functions for collaboration among data rows in SQL Queries
With the Window functions, SQL queries are not limited to finding data characteristics for a single row of data. SQL queries can be defined over a window of data.
As an example, a magazine publisher is interested in comparing its individual magazine sales (magazine_sales) over total magazine sales from all publishers (total_sales), and is also interested in being able to compare between its different magazines. For such a multifaceted comparison, some information needs to be calculated for each data row even though its value is the same across all rows; such as the total_sales and publisher_sales columns. The SQL query would be something like:
SELECT publisher,
magazine_name, sales AS magazine_sales,
SUM(sales) OVER() AS total_sales,
SUM(sales) OVER(PARTITION BY publisher) AS publisher_sales
FROM magazine_data
ORDER BY publisher, magazine_name;
The query returns the following result set, as an example:
+------------+-------------------+-------------------+------------+----------------------+ | publisher | magazine_name |magazine_sales |total_sales | publisher_sales | +------------+-------------------+------------------+-------------+----------------------+ | publisher1| magazine1 | 30 | 1000 | 100 | | publisher1| magazine2 | 60 | 1000 | 100 | | publisher1| magazine3 | 10 | 1000 | 100 | | publisher2| magazine4 | 25 | 1000 | 60 | | publisher2| magazine5 | 25 | 1000 | 60 | | publisher2| magazine6 | 10 | 1000 | 60 | | publisher3| magazine7 | 50 | 1000 | 75 | ...
Agile software development is tuned to the changing needs of software end users, emphasizing simplicity, sustainable development, working software, collaboration, and responding to change as laid out in the Agile Manifesto, and MySQL delivers on all of these Agile principles.