Frédéric Descamps @lefred MySQL Evangelist Managing MySQL since 3.20 devops believer living in Belgium h ps://lefred.be Copyright @ 2022 Oracle and/or its affiliates. 3
Hash Joins New InnoDB Doublewrie Bu er Error Log in a table Binary Log compression what's new from January 2021 ? There are plenty of cool stu pre-8.0.23 that you may know, like... Copyright @ 2022 Oracle and/or its affiliates. 5
Hash Joins New InnoDB Doublewrie Bu er Error Log in a table Binary Log compression what's new from January 2021 ? There are plenty of cool stu pre-8.0.23 that you may know, like... But also other things you might not have heard about, like ... Copyright @ 2022 Oracle and/or its affiliates. 5
Hash Joins New InnoDB Doublewrie Bu er Error Log in a table Binary Log compression Runtime disabling InnoDB Redo Log Per-user Comments & A ributes MySQL Document Store JSON validation TVC Support Account Management Support Automatic Async Replication connection Failover what's new from January 2021 ? There are plenty of cool stu pre-8.0.23 that you may know, like... But also other things you might not have heard about, like ... Copyright @ 2022 Oracle and/or its affiliates. 5
pre-8.0.23 examples Runtime disabling InnoDB Redo Log MySQL > ALTER INSTANCE DISABLE INNODB REDO_LOG; MySQL > SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_redo_log_enabled'; +-------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-------------------------+----------------+ | Innodb_redo_log_enabled | OFF | +-------------------------+----------------+ Per-user Comments & A ributes MySQL > ALTER USER fred ATTRIBUTE '{"country": "belgium", "OS": "Linux"}'; MySQL > SELECT *, attribute->>"$.country" country FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER='fred'; +------+------+---------------------------------------+---------+ | USER | HOST | ATTRIBUTE | country | +------+------+---------------------------------------+---------+ | fred | % | {"OS": "Linux", "country": "belgium"} | belgium | +------+------+---------------------------------------+---------+ Copyright @ 2022 Oracle and/or its affiliates. 6
pre-8.0.23 examples (4) Account Management Enhancements Too many consecutive login failures due to incorrect passwords can cause temporary account locking MySQL > CREATE USER 'usergroupnl'@'localhost' IDENTIFIED BY 'WeOranj3&' FAILED_LOGIN_ATTEMPTS 1 PASSWORD_LOCK_TIME 1; Copyright @ 2022 Oracle and/or its affiliates. 9
Replication rst a word about something that was particularly close to our hearts We kept the long task of removing o ensive words in our code (commands, tables, ...) Now: mysql> CHANGE REPLICATION SOURCE TO -> SOURCE_HOST='source_host_name', -> SOURCE_USER='replication_user_name', -> SOURCE_PASSWORD='replication_password', -> SOURCE_LOG_FILE='recorded_log_file_name', -> SOURCE_LOG_POS=recorded_log_position; Copyright @ 2022 Oracle and/or its affiliates. 11
Replication & Group Replication replication from non GTID enabled replica (8.0.23) automatic connection failover for async replication for Group Replication (8.0.23) alternative UUID for Group Replication's generated transactions for view changes (8.0.26) multi-threaded replication enabled by default (8.0.27) Group Communication engine (GCS) can now use a single leader when using single- primary mode to improve performance Copyright @ 2022 Oracle and/or its affiliates. 12
Replication & Group Replication Asynchronous Replication Channel automatically follows the Primary (8.0.27) support of native MySQL Server's connection security for Group Replication (8.0.27) it's possible to enable Paxos single leader for Group Replication (8.0.27) new system variable to control automatic purging of binary logs: binlog_expire_logs_auto_purge (8.0.29) Group Replication memory usage is now instrumented in Performance_Schema (8.0.30) Copyright @ 2022 Oracle and/or its affiliates. 13
InnoDB indexes can now be built simultaneously instead of serially: innodb_ddl_threads) (8.0.27) see h ps://lefred.be/content/mysql-8-0-innodb-parallel-threads-for-online-ddl- operations/ Clone now permits concurrent DDL operations on the donor (8.0.27) InnoDB now supports the following operation using ALGORITHM=INSTANT: ALTER TABLE ... RENAME COLUMN (8.0.28) ALTER TABLE ... DROP COLUMN (8.0.29) Copyright @ 2022 Oracle and/or its affiliates. 14
InnoDB (2) innodb_open_ les can be set at runtime (8.0.28) two new values for the innodb_doublewrite system variable (8.0.30): DETECT_ONLY DETECT_AND_RECOVER new Redo Log architecture (8.0.30) Copyright @ 2022 Oracle and/or its affiliates. 15
InnoDB New Redo Log architecture The redo log is a write ahead log of changes applied to contents of data pages. It provides durability for all changes applied to the pages. (The 'D' in ACID) In case of crash, it is used to recover modi cations to pages that were modi ed but have not been ushed to disk. Copyright @ 2022 Oracle and/or its affiliates. 16
InnoDB New Redo Log architecture Since MySQL 8.0.30 it's possible to dynamically resize the InnoDB redo logs. Overview of the changes: redo logs are now on a dedicated directory: #innodb_redo 32 les (innodb_redo_log_capacity / 32) the les are re-used but renamed, rst as #ib_redoXX_tmp when not in use and where XX is a new incremented number innodb_log_ les_in_group and innodb_log_ le_size are now deprecated and ignored Copyright @ 2022 Oracle and/or its affiliates. 17
InnoDB New Redo Log architecture Calculating the optimal size During peak tra c time, you can get an estimation of the required amount for the Redo Log Size by running this query: MySQL > SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_redo_log_current_lsn' INTO @a; SELECT SLEEP(60) INTO @garb; SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_redo_log_current_lsn' INTO @b; SELECT ROUND(ABS(@a - @b)/1024/1024,2) AS MB_per_min, ROUND(ABS(@a - @b)/1024/1024*60,2) AS MB_per_hour; +------------+-------------+ | MB_per_min | MB_per_hour | +------------+-------------+ | 10.37 | 622 | +------------+-------------+ Copyright @ 2022 Oracle and/or its affiliates. 19
InnoDB New Redo Log architecture Calculating the optimal size (2) The rule of thumb is to make the Redo Logs big enough to hold at most 1h of logs: MySQL > SELECT format_bytes(@@innodb_redo_log_capacity); +------------------------------------------+ | format_bytes(@@innodb_redo_log_capacity) | +------------------------------------------+ | 100.00 MiB | +------------------------------------------+ MySQL > SET GLOBAL innodb_redo_log_capacity=622*1024*1024; MySQL > SELECT format_bytes(@@innodb_redo_log_capacity); +------------------------------------------+ | format_bytes(@@innodb_redo_log_capacity) | +------------------------------------------+ | 622.00 MiB | +------------------------------------------+ Copyright @ 2022 Oracle and/or its affiliates. 20
InnoDB Primary Keys, Invisible column and GIPK For InnoDB, a Primary Key is required and a good one is even be er ! Copyright @ 2022 Oracle and/or its affiliates. 21
InnoDB Primary Keys, Invisible column and GIPK For InnoDB, a Primary Key is required and a good one is even be er ! Some theory InnoDB stores data in table spaces. The records are stored and sorted using the clustered index (PK). Copyright @ 2022 Oracle and/or its affiliates. 21
InnoDB Primary Keys, Invisible column and GIPK For InnoDB, a Primary Key is required and a good one is even be er ! Some theory InnoDB stores data in table spaces. The records are stored and sorted using the clustered index (PK). All secondary indexes also contain the primary key as the right-most column in the index (even if this is not exposed). That means when a secondary index is used to retrieve a record, two indexes are used: rst the secondary one pointing to the primary key that will be used to nally retrieve the record. Copyright @ 2022 Oracle and/or its affiliates. 21
InnoDB Primary Key (2) So, the primary key impact how the values are inserted and the size of the secondary indexes. A non sequential PK can lead to many random IOPS. Copyright @ 2022 Oracle and/or its affiliates. 22
Also, it's more and more common to use application that generates complete random primary keys...that means if the Primary Key is not sequential, InnoDB will have to heavily re-balance all the pages on inserts. InnoDB Primary Key (2) So, the primary key impact how the values are inserted and the size of the secondary indexes. A non sequential PK can lead to many random IOPS. Copyright @ 2022 Oracle and/or its affiliates. 22
InnoDB Primary Key (3) If we compare the same load (inserts) when using an auto_increment integer as Primary Key, we can see that only the latest pages are recently touched: Generated with h ps://github.com/jeremycole/innodb_ruby from @jeremycole Copyright @ 2022 Oracle and/or its affiliates. 23
InnoDB Primary Key ? No Key ! Another common mistake when using InnoDB is to not de ne any Primary Key. Copyright @ 2022 Oracle and/or its affiliates. 24
InnoDB Primary Key ? No Key ! Another common mistake when using InnoDB is to not de ne any Primary Key. When no primary key is de ned, the rst unique not null key is used. And if none is available, InnoDB will create an hidden primary key (6 bytes). Copyright @ 2022 Oracle and/or its affiliates. 24
InnoDB Primary Key ? No Key ! Another common mistake when using InnoDB is to not de ne any Primary Key. When no primary key is de ned, the rst unique not null key is used. And if none is available, InnoDB will create an hidden primary key (6 bytes). The problem with such key is that you don’t have any control on it and worse, this value is global to all tables without primary keys and can be a contention problem if you perform multiple simultaneous writes on such tables (dict_sys->mutex). Copyright @ 2022 Oracle and/or its affiliates. 24
InnoDB Primary Key ? No Key ! Another common mistake when using InnoDB is to not de ne any Primary Key. When no primary key is de ned, the rst unique not null key is used. And if none is available, InnoDB will create an hidden primary key (6 bytes). The problem with such key is that you don’t have any control on it and worse, this value is global to all tables without primary keys and can be a contention problem if you perform multiple simultaneous writes on such tables (dict_sys->mutex). And if you plan for High Availability, tables without Primary Key are not supported ! Copyright @ 2022 Oracle and/or its affiliates. 24
InnoDB Primary Key ? No Key ! (2) Luckily since MySQL 8.0.23 there is a solution: Invisible Column ! You can now add an invisible auto_increment Primary Key to a table not having any Primary Key ! Copyright @ 2022 Oracle and/or its affiliates. 25
InnoDB Primary Key ? No Key ! (2) To identify those tables, run the following SQL statement: SELECT tables.table_schema , tables.table_name , tables.engine FROM information_schema.tables LEFT JOIN ( SELECT table_schema , table_name FROM information_schema.statistics GROUP BY table_schema, table_name, index_name HAVING SUM( CASE WHEN non_unique = 0 AND nullable != 'YES' then 1 else 0 end ) = count(*) ) puks ON tables.table_schema = puks.table_schema AND tables.table_name = puks.table_name WHERE puks.table_name IS null AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB"; Copyright @ 2022 Oracle and/or its affiliates. 26
InnoDB Primary Key ? No Key ! (2) To identify those tables, run the following SQL statement: SELECT tables.table_schema , tables.table_name , tables.engine FROM information_schema.tables LEFT JOIN ( SELECT table_schema , table_name FROM information_schema.statistics GROUP BY table_schema, table_name, index_name HAVING SUM( CASE WHEN non_unique = 0 AND nullable != 'YES' then 1 else 0 end ) = count(*) ) puks ON tables.table_schema = puks.table_schema AND tables.table_name = puks.table_name WHERE puks.table_name IS null AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB"; Copyright @ 2022 Oracle and/or its affiliates. +--------------+-----------------+--------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | +--------------+-----------------+--------+ | slack | some_table | InnoDB | | test | default_test | InnoDB | | test | t1 | InnoDB | | world | orders | InnoDB | | world | sales | InnoDB | | dbt3 | time_statistics | InnoDB | +--------------+-----------------+--------+ 26
InnoDB Primary Key ? No Key ! (3) Another nice query to identify the tables using an hidden clustered index is to lookup for GEN_CLUST_INDEX like this: SELECT i.TABLE_ID, t.NAME FROM INFORMATION_SCHEMA.INNODB_INDEXES i JOIN INFORMATION_SCHEMA.INNODB_TABLES t ON (i.TABLE_ID = t.TABLE_ID) WHERE i.NAME='GEN_CLUST_INDEX'; see h ps://elephantdolphin.blogspot.com/2021/08/ nding-your-hidden-innodb-primary.html Copyright @ 2022 Oracle and/or its affiliates. 27
InnoDB Primary Key ? No Key ! (3) Another nice query to identify the tables using an hidden clustered index is to lookup for GEN_CLUST_INDEX like this: SELECT i.TABLE_ID, t.NAME FROM INFORMATION_SCHEMA.INNODB_INDEXES i JOIN INFORMATION_SCHEMA.INNODB_TABLES t ON (i.TABLE_ID = t.TABLE_ID) WHERE i.NAME='GEN_CLUST_INDEX'; see h ps://elephantdolphin.blogspot.com/2021/08/ nding-your-hidden-innodb-primary.html Copyright @ 2022 Oracle and/or its affiliates. +----------+----------------------+ | TABLE_ID | NAME | +----------+----------------------+ | 1198 | slack/some_table | | 1472 | test/default_test | | 1492 | test/t1 | | 2018 | world/orders | | 2019 | world/sales | | 2459 | dbt3/time_statistics | +----------+----------------------+ 27
InnoDB GIPK mode Since MySQL 8.0.30, MySQL supports generated invisible primary keys when running in GIPK mode ! GIPK mode is controlled by the sql_generate_invisible_primary_key server system variable. Copyright @ 2022 Oracle and/or its affiliates. 30
InnoDB GIPK mode Since MySQL 8.0.30, MySQL supports generated invisible primary keys when running in GIPK mode ! GIPK mode is controlled by the sql_generate_invisible_primary_key server system variable. When MySQL is running in GIPK mode, a primary key is added to a table by the server, the column and key name is always my_row_id. Copyright @ 2022 Oracle and/or its affiliates. 30
InnoDB GIPK mode - example (3) It's also possible to hide it completely (for some legacy application that could rely on informantion_schema and SHOW CREATE TABLE): MySQL > SET show_gipk_in_create_table_and_information_schema = 0; MySQL > SHOW CREATE TABLE usergroupnl\G *************************** 1. row *************************** Table: usergroupnl Create Table: CREATE TABLE `usergroupnl` ( `name` varchar(20) DEFAULT NULL, `beers` int unsigned DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Copyright @ 2022 Oracle and/or its affiliates. 33
. generally they are completely radom and cause clustered index re-banlancing . they are included in each secondary indexes (consuming disk and memory) InnoDB Primary Key - What about UUID ? There are 2 main problems with UUID's as Primary Key: Copyright @ 2022 Oracle and/or its affiliates. 35
InnoDB Primary Key - What about UUID ? (4) OUPS ! We have rebalanced the clustered index ! What does that mean again ?? Copyright @ 2022 Oracle and/or its affiliates. 38
InnoDB Primary Key - What about UUID ? (4) OUPS ! We have rebalanced the clustered index ! What does that mean again ?? Let me try to explain this with this high level and simpli ed example: Copyright @ 2022 Oracle and/or its affiliates. 38
Let's imagine one InnoDB Page can store 4 records (this is just a ction), and we have inserted some records using a random Primary Key: InnoDB Primary Key - What about UUID ? (5) OUPS ! We have rebalanced the clustered index ! Copyright @ 2022 Oracle and/or its affiliates. 39
Let's imagine one InnoDB Page can store 4 records (this is just a ction), and we have inserted some records using a random Primary Key: And now we insert a new record and the Primary Key is AA: All pages were modi ed to clustered index ! Imagine if this was a 4TB table !! InnoDB Primary Key - What about UUID ? (5) OUPS ! We have rebalanced the clustered index ! Copyright @ 2022 Oracle and/or its affiliates. 39
InnoDB Primary Key - What about UUID ? (6) And just for info, each entry in the Primary Key Index take 146 bytes(*) : MySQL > EXPLAIN SELECT * FROM usergroupnl3 WHERE uuid='36f1d158-1fa1-11ed-ba36-c8cb9e32df8e'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: usergroupnl3 partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 146 ref: const rows: 1 filtered: 100 Extra: NULL (*) worse case when using characters using 4 bytes each (uft8mb4) Copyright @ 2022 Oracle and/or its affiliates. 40
InnoDB Primary Key - What about UUID ? (7) Recommended solution . use a smaller datatype: BINARTY(16) . store the UUID sequentially: UUID_TO_BIN(..., swap_ ag) The time-low and time-high parts (the rst and third groups of hexadecimal digits, respectively) are swapped. Copyright @ 2022 Oracle and/or its affiliates. 41
InnoDB Primary Key - What about UUID ? (10) Recommended solution - example Take a look at the size of each entry in the INDEX (and same amount added to each secondary index) MySQL > EXPLAIN SELECT * FROM usergroupnl2 WHERE uuid=UUID_TO_BIN("37c5752d-1fa5-11ed-ba36-c8cb9e32df8e",1)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: usergroupnl2 partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 16 ref: const rows: 1 filtered: 100 Extra: NULL Copyright @ 2022 Oracle and/or its affiliates. 44
MySQL UUID MySQL generates UUID v1 as described in RFC4122. UUID v1 : is a universally unique identi er that is generated using a timestamp and the MAC address of the computer on which it was generated. UUID v4 : is a universally unique identi er that is generated using random numbers. Copyright @ 2022 Oracle and/or its affiliates. 45
MySQL UUID MySQL generates UUID v1 as described in RFC4122. UUID v1 : is a universally unique identi er that is generated using a timestamp and the MAC address of the computer on which it was generated. UUID v4 : is a universally unique identi er that is generated using random numbers. With UUID v4, it's not possible to generate any sequential output. Copyright @ 2022 Oracle and/or its affiliates. 45
All managed directly from MySQL Shell ! seconds 0 RPO ? ? RTO possible rpo>0 rto > RTO: Recovery Time Objective (how long to recover) RPO: Recovery Point Objective (how much data can be lost) MySQL InnoDB ClusterSet Copyright @ 2022 Oracle and/or its affiliates. 51
Automated deployment and management of MySQL Server & MySQL Routers Self-healing Backup & Restore Rolling upgrades with minimal downtime Developed and supported by the MySQL Team MySQL Operator for Kubernetes - GA Both MySQL Operator for Kubernetes & MySQL InnoDB Cluster share a common goal to make it easier to deploy, automate and manage a service. Copyright @ 2022 Oracle and/or its affiliates. 53
MySQL Operator for Kubernetes - GA The MySQL Operator for Kubernetes is published on GitHub: h ps://github.com/mysql/mysql-operator Check out the documentation: h ps://dev.mysql.com/doc/mysql-operator/en/ Copyright @ 2022 Oracle and/or its affiliates. 55
lefred
oracle4engineer
autifyhq
.png){kind=icon}
pharma_x_tech
konifar
shinnosuke_kishida
nrryuya
ytaisei
defaultcf
kobayang
torounit
line_developers
yrhorita
philhawksworth
chriscoyier
orderedlist
akosma
thoeni
mojombo
lemiorhan
jponch
sonatard
rasmusluckow
reverentgeek
jnunemaker