Frédéric Descamps Community Manager Oracle MySQL MySQL Tech Tour Italia - O obre 2024 Massimizzare le Prestazioni del Tuo Database MySQL Tips For MySQL Performance Tuning

Who am I ? about. me/ lefred Copyright @ 2024 Oracle and/or its affiliates. 2

@lefred MySQL Evangelist using MySQL since version 3.20 devops believer likes living in h ps://lefred.be Frédéric Descamps Copyright @ 2024 Oracle and/or its affiliates. 3

Ready ? Copyright @ 2024 Oracle and/or its affiliates. 4

Go ! Copyright @ 2024 Oracle and/or its affiliates. 5

MySQL Configuration Best Practices Copyright @ 2024 Oracle and/or its affiliates. 6

Do not ever use CREATE TABLE... ENGINE=MyISAM anymore ! Please!! Please!! Please!! set set persist default_storage_engine persist default_storage_engine= ="InnoDB" "InnoDB"; ; set set persist_only disabled_storage_engines persist_only disabled_storage_engines= ="MyISAM" "MyISAM"; ; Since MySQL 8.0, defaults are strict and InnoDB guarantees DURABILITY ! Keep your data safe ! #1 - Don't use MyISAM ! Copyright @ 2024 Oracle and/or its affiliates. 7

ACID multiple lock types faster than MyISAM Always use InnoDB !! Copyright @ 2024 Oracle and/or its affiliates. 8

ACID multiple lock types faster than MyISAM Always use InnoDB !! Copyright @ 2024 Oracle and/or its affiliates. 8

#2 - The top secret is InnoDB Bu er Pool's size It's important to have the working set in memory. The size of the InnoDB Bu er Pool is important: MySQL MySQL > > SELECT SELECT format_bytes format_bytes( (@ @@innodb_buffer_pool_size @innodb_buffer_pool_size) ) BufferPoolSize BufferPoolSize, , FORMAT FORMAT( (A A. .num num * * 100.0 100.0 / / B B. .num num, ,2 2) ) BufferPoolFullPct BufferPoolFullPct, , FORMAT FORMAT( (C C. .num num * * 100.0 100.0 / / D D. .num num, ,2 2) ) BufferPollDirtyPct BufferPollDirtyPct FROM FROM ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name = = 'Innodb_buffer_pool_pages_data' 'Innodb_buffer_pool_pages_data') ) A A, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name = = 'Innodb_buffer_pool_pages_total' 'Innodb_buffer_pool_pages_total') ) B B, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name= ='Innodb_buffer_pool_pages_dirty' 'Innodb_buffer_pool_pages_dirty') ) C C, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name= ='Innodb_buffer_pool_pages_total' 'Innodb_buffer_pool_pages_total') ) D D; ; Copyright @ 2024 Oracle and/or its affiliates. 9

#2 - The top secret is InnoDB Bu er Pool's size It's important to have the working set in memory. The size of the InnoDB Bu er Pool is important: MySQL MySQL > > SELECT SELECT format_bytes format_bytes( (@ @@innodb_buffer_pool_size @innodb_buffer_pool_size) ) BufferPoolSize BufferPoolSize, , FORMAT FORMAT( (A A. .num num * * 100.0 100.0 / / B B. .num num, ,2 2) ) BufferPoolFullPct BufferPoolFullPct, , FORMAT FORMAT( (C C. .num num * * 100.0 100.0 / / D D. .num num, ,2 2) ) BufferPollDirtyPct BufferPollDirtyPct FROM FROM ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name = = 'Innodb_buffer_pool_pages_data' 'Innodb_buffer_pool_pages_data') ) A A, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name = = 'Innodb_buffer_pool_pages_total' 'Innodb_buffer_pool_pages_total') ) B B, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name= ='Innodb_buffer_pool_pages_dirty' 'Innodb_buffer_pool_pages_dirty') ) C C, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name= ='Innodb_buffer_pool_pages_total' 'Innodb_buffer_pool_pages_total') ) D D; ; Copyright @ 2024 Oracle and/or its affiliates. +----------------+-------------------+--------------------+ | BufferPoolSize | BufferPoolFullPct | BufferPollDirtyPct | +----------------+-------------------+--------------------+ | 128.00 MiB | 87.12 | 0.36 | +----------------+-------------------+--------------------+ 1 row in set (0.0012 sec) 9

InnoDB Bu er Pool's size We can also verify the Ratio of pages requested and read from disk: MySQL MySQL > > SELECT SELECT FORMAT FORMAT( (A A. .num num * * 100 100 / / B B. .num num, ,2 2) ) DiskReadRatioPct DiskReadRatioPct FROM FROM ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name = = 'Innodb_buffer_pool_reads' 'Innodb_buffer_pool_reads') ) A A, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name = = 'Innodb_buffer_pool_read_requests' 'Innodb_buffer_pool_read_requests') ) B B; ; + +------------------+ ------------------+ | | DiskReadRatioPct DiskReadRatioPct | | + +------------------+ ------------------+ | | 3.53 3.53 | | + +------------------+ ------------------+ Copyright @ 2024 Oracle and/or its affiliates. 10

#3: InnoDB Redo Log It's not recommended to oversize the Redo Log Capacity. Redo Log les consume disk space and increases the recovery time in case of a restart (innodb_fast_shutdown=1) or a sudden crash. And it also slows down shutdown when innodb_fast_shutdown=0. Copyright @ 2024 Oracle and/or its affiliates. 11

InnoDB Redo Log - Recommendations During peak tra c time, you can get an estimation of the required amount for the Redo Log Capacity by running the query below (all in one single line): MySQL MySQL > > SELECT SELECT VARIABLE_VALUE VARIABLE_VALUE from from performance_schema performance_schema. .global_status global_status WHERE WHERE VARIABLE_NAME VARIABLE_NAME= ='Innodb_redo_log_current_lsn' 'Innodb_redo_log_current_lsn' INTO INTO @a @a; ;SELECT SELECT sleep sleep( (60 60) ) INTO INTO @garb @garb ; ;SELECT SELECT VARIABLE_VALUE VARIABLE_VALUE FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE VARIABLE_NAME VARIABLE_NAME= ='Innodb_redo_log_current_lsn' 'Innodb_redo_log_current_lsn' INTO INTO @b @b; ;select select format_bytes format_bytes( (abs abs( (@a @a - - @b @b) )) ) per_min per_min, , format_bytes format_bytes( (abs abs( (@a @a - - @b @b) )* *60 60) ) per_hour per_hour; ; + +-----------+----------+ -----------+----------+ | | per_min per_min | | per_hour per_hour | | + +-----------+----------+ -----------+----------+ | | 21.18 21.18 MiB MiB | | 1.24 1.24 GiB GiB | | + +-----------+----------+ -----------+----------+ Copyright @ 2024 Oracle and/or its affiliates. 12

InnoDB Redo Log - Recommendations During peak tra c time, you can get an estimation of the required amount for the Redo Log Capacity by running the query below (all in one single line): MySQL MySQL > > SELECT SELECT VARIABLE_VALUE VARIABLE_VALUE from from performance_schema performance_schema. .global_status global_status WHERE WHERE VARIABLE_NAME VARIABLE_NAME= ='Innodb_redo_log_current_lsn' 'Innodb_redo_log_current_lsn' INTO INTO @a @a; ;SELECT SELECT sleep sleep( (60 60) ) INTO INTO @garb @garb ; ;SELECT SELECT VARIABLE_VALUE VARIABLE_VALUE FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE VARIABLE_NAME VARIABLE_NAME= ='Innodb_redo_log_current_lsn' 'Innodb_redo_log_current_lsn' INTO INTO @b @b; ;select select format_bytes format_bytes( (abs abs( (@a @a - - @b @b) )) ) per_min per_min, , format_bytes format_bytes( (abs abs( (@a @a - - @b @b) )* *60 60) ) per_hour per_hour; ; + +-----------+----------+ -----------+----------+ | | per_min per_min | | per_hour per_hour | | + +-----------+----------+ -----------+----------+ | | 21.18 21.18 MiB MiB | | 1.24 1.24 GiB GiB | | + +-----------+----------+ -----------+----------+ Copyright @ 2024 Oracle and/or its affiliates. MySQL > SET persist innodb_redo_log_capacity=1.24*1024*1024*1024; 12

#4 - Optimal InnoDB Con guration to start On a dedicated MySQL Server, the best is to let InnoDB decide the size of the Bu er Pool and the Redo Log Capacity. In my.cnf: innodb_dedicated_server innodb_dedicated_server= =1 1 Defaults have changed since My SQL 8.4 LTS h ps://dev.mysql.com/doc/refman/8.4/en/innodb-dedicated-server.html h ps://lefred.be/content/mysql-8-4-lts-new-production-ready-defaults-for-innodb/ Copyright @ 2024 Oracle and/or its affiliates. 13

#5 - Always start with a warm InnoDB Bu er Pool The secret is to always run a production server with a warm Bu er Pool. If you need to restart MySQL for any reason (maintenance, updgrade, crash), it's recommended to dump the content of the InnoDB Bu er Pool to disk and load it at startup: innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_load_at_startup = 1 Copyright @ 2024 Oracle and/or its affiliates. 14

Always start with a warm InnoDB Bu er Pool (2) It's also possible to dump the Bu er Pool at some intervals in case of a crash and to avoid to load a very old dump. Just create an EVENT: CREATE CREATE EVENT automatic_bufferpool_dump EVENT automatic_bufferpool_dump ON ON SCHEDULE EVERY SCHEDULE EVERY 1 1 HOUR HOUR DO DO SET SET global global innodb_buffer_pool_dump_now innodb_buffer_pool_dump_now= =ON ON; ; Copyright @ 2024 Oracle and/or its affiliates. 15

#6 - Adaptive Hash Index When some secondary indexes values are being accessed very frequently, Inno DB builds a hash index (AHI) for them in memory on top of the B-Tree indexes. If your workload doesn't bene t from it, you are disadvantaged by its overhead. This can detected by seeing a lot of waits on rw-lock semaphores in the btr0sea.cc le (we will cover that later) . Some information is available in the SHOW ENGINE INNODB STATUS output, however since MySQL 8.0 we tend to replace the use of that statement using Performance_Schema and Sys. Copyright @ 2024 Oracle and/or its affiliates. 16

#6 - Adaptive Hash Index When some secondary indexes values are being accessed very frequently, Inno DB builds a hash index (AHI) for them in memory on top of the B-Tree indexes. If your workload doesn't bene t from it, you are disadvantaged by its overhead. This can detected by seeing a lot of waits on rw-lock semaphores in the btr0sea.cc le (we will cover that later) . Some information is available in the SHOW ENGINE INNODB STATUS output, however since MySQL 8.0 we tend to replace the use of that statement using Performance_Schema and Sys. Copyright @ 2024 Oracle and/or its affiliates. ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 664177, node heap has 0 buffer(s) Hash table size 664177, node heap has 0 buffer(s) Hash table size 664177, node heap has 0 buffer(s) Hash table size 664177, node heap has 0 buffer(s) Hash table size 664177, node heap has 0 buffer(s) Hash table size 664177, node heap has 0 buffer(s) Hash table size 664177, node heap has 0 buffer(s) Hash table size 664177, node heap has 0 buffer(s) *0.00 hash searches/s, 8786.34 non-hash searches/s 16

Adaptive Hash Index (2) Statistics for AHI are also available when InnoDB monitor is enabled: SELECT SELECT Variable_name Variable_name, , Variable_value Variable_value FROM FROM sys sys. .metrics metrics WHERE WHERE Variable_name Variable_name LIKE LIKE 'adaptive%' 'adaptive%'; ; + +------------------------------------------+----------------+ ------------------------------------------+----------------+ | | Variable_name Variable_name | | Variable_value Variable_value | | + +------------------------------------------+----------------+ ------------------------------------------+----------------+ | | adaptive_hash_pages_added adaptive_hash_pages_added | | 0 0 | | | | adaptive_hash_pages_removed adaptive_hash_pages_removed | | 0 0 | | | | adaptive_hash_rows_added adaptive_hash_rows_added | | 0 0 | | | | adaptive_hash_rows_deleted_no_hash_entry adaptive_hash_rows_deleted_no_hash_entry | | 0 0 | | | | adaptive_hash_rows_removed adaptive_hash_rows_removed | | 0 0 | | | | adaptive_hash_rows_updated adaptive_hash_rows_updated | | 0 0 | | | | adaptive_hash_searches adaptive_hash_searches | | 1243 1243 | | | | adaptive_hash_searches_btree adaptive_hash_searches_btree | | 20125723 20125723 | | + +------------------------------------------+----------------+ ------------------------------------------+----------------+ Copyright @ 2024 Oracle and/or its affiliates. 17

Adaptive Hash Index (2) Statistics for AHI are also available when InnoDB monitor is enabled: SELECT SELECT Variable_name Variable_name, , Variable_value Variable_value FROM FROM sys sys. .metrics metrics WHERE WHERE Variable_name Variable_name LIKE LIKE 'adaptive%' 'adaptive%'; ; + +------------------------------------------+----------------+ ------------------------------------------+----------------+ | | Variable_name Variable_name | | Variable_value Variable_value | | + +------------------------------------------+----------------+ ------------------------------------------+----------------+ | | adaptive_hash_pages_added adaptive_hash_pages_added | | 0 0 | | | | adaptive_hash_pages_removed adaptive_hash_pages_removed | | 0 0 | | | | adaptive_hash_rows_added adaptive_hash_rows_added | | 0 0 | | | | adaptive_hash_rows_deleted_no_hash_entry adaptive_hash_rows_deleted_no_hash_entry | | 0 0 | | | | adaptive_hash_rows_removed adaptive_hash_rows_removed | | 0 0 | | | | adaptive_hash_rows_updated adaptive_hash_rows_updated | | 0 0 | | | | adaptive_hash_searches adaptive_hash_searches | | 1243 1243 | | | | adaptive_hash_searches_btree adaptive_hash_searches_btree | | 20125723 20125723 | | + +------------------------------------------+----------------+ ------------------------------------------+----------------+ Copyright @ 2024 Oracle and/or its affiliates. SELECT CONCAT( ROUND( ( SELECT Variable_value FROM sys.metrics WHERE Variable_name = 'adaptive_hash_searches' ) / ( ( SELECT Variable_value FROM sys.metrics WHERE Variable_name = 'adaptive_hash_searches_btree' ) + ( SELECT Variable_value FROM sys.metrics WHERE Variable_name = 'adaptive_hash_searches' ) ) * 100,2 ),'%') 'AHI ratio'; +-----------+ | AHI ratio | +-----------+ | 0.01% | +-----------+ 17

Adaptive Hash Index (3) If you don't bene t from AHI, you should disable it. set set persist innodb_adaptive_hash_index persist innodb_adaptive_hash_index = = 0 0; ; Copyright @ 2024 Oracle and/or its affiliates. 18

Adaptive Hash Index (3) If you don't bene t from AHI, you should disable it. set set persist innodb_adaptive_hash_index persist innodb_adaptive_hash_index = = 0 0; ; If you bene t from it but you see wrong distribution or many hash partition with high numbers, you should then change the amount of partitions: (max: 512) set set persist_only innodb_adaptive_hash_index_parts persist_only innodb_adaptive_hash_index_parts = = 8 8; ; restart restart; ; Copyright @ 2024 Oracle and/or its affiliates. 18

Adaptive Hash Index (3) If you don't bene t from AHI, you should disable it. set set persist innodb_adaptive_hash_index persist innodb_adaptive_hash_index = = 0 0; ; If you bene t from it but you see wrong distribution or many hash partition with high numbers, you should then change the amount of partitions: (max: 512) set set persist_only innodb_adaptive_hash_index_parts persist_only innodb_adaptive_hash_index_parts = = 8 8; ; restart restart; ;  Disabling AHI, you will also bene t from the the new DROP TABLE|TABLESACE and TRUNCATE improvements ! Copyright @ 2024 Oracle and/or its affiliates. 18

Schema Design primary keys indexes, not too li le, not too much Copyright @ 2024 Oracle and/or its affiliates. 19

#6 - Primary Keys For Inno DB, a Primary Key is required and a good one is even be er ! Copyright @ 2024 Oracle and/or its affiliates. 20

#6 - Primary Keys For Inno DB, 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 @ 2024 Oracle and/or its affiliates. 20

#6 - Primary Keys For Inno DB, 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 @ 2024 Oracle and/or its affiliates. 20

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 @ 2024 Oracle and/or its affiliates. 21

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 @ 2024 Oracle and/or its affiliates. 21

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 @ 2024 Oracle and/or its affiliates. 22

InnoDB Primary Key ? No Key ! Another common mistake when using InnoDB is to not de ne any Primary Key. Copyright @ 2024 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. 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 @ 2024 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. 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 @ 2024 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. 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 @ 2024 Oracle and/or its affiliates. 23

InnoDB Primary Key ? No Key ! (2) To identify those tables, run the following SQL statement, which is to lookup for GEN_CLUST_INDEX: SELECT SELECT i i. .TABLE_ID TABLE_ID, , t t. .NAME NAME FROM FROM INFORMATION_SCHEMA INFORMATION_SCHEMA. .INNODB_INDEXES i INNODB_INDEXES i JOIN JOIN INFORMATION_SCHEMA INFORMATION_SCHEMA. .INNODB_TABLES t INNODB_TABLES t ON ON ( (i i. .TABLE_ID TABLE_ID = = t t. .TABLE_ID TABLE_ID) ) WHERE WHERE i i. .NAME NAME= ='GEN_CLUST_INDEX' 'GEN_CLUST_INDEX'; ; see h ps://elephantdolphin.blogspot.com/2021/08/ nding-your-hidden-innodb-primary.html Copyright @ 2024 Oracle and/or its affiliates. 24

InnoDB Primary Key ? No Key ! (2) To identify those tables, run the following SQL statement, which is to lookup for GEN_CLUST_INDEX: SELECT SELECT i i. .TABLE_ID TABLE_ID, , t t. .NAME NAME FROM FROM INFORMATION_SCHEMA INFORMATION_SCHEMA. .INNODB_INDEXES i INNODB_INDEXES i JOIN JOIN INFORMATION_SCHEMA INFORMATION_SCHEMA. .INNODB_TABLES t INNODB_TABLES t ON ON ( (i i. .TABLE_ID TABLE_ID = = t t. .TABLE_ID TABLE_ID) ) WHERE WHERE i i. .NAME NAME= ='GEN_CLUST_INDEX' 'GEN_CLUST_INDEX'; ; see h ps://elephantdolphin.blogspot.com/2021/08/ nding-your-hidden-innodb-primary.html Copyright @ 2024 Oracle and/or its affiliates. + +----------+----------------------+ ----------+----------------------+ | | TABLE_ID TABLE_ID | | NAME NAME | | + +----------+----------------------+ ----------+----------------------+ | | 1198 1198 | | slack slack/ /some_table some_table | | | | 1472 1472 | | test test/ /default_test default_test | | | | 1492 1492 | | test test/ /t1 t1 | | | | 2018 2018 | | world world/ /orders orders | | | | 2019 2019 | | world world/ /sales sales | | | | 2459 2459 | | dbt3 dbt3/ /time_statistics time_statistics | | + +----------+----------------------+ ----------+----------------------+ _ _ 24

#7 - 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 @ 2024 Oracle and/or its affiliates. 25

#7 - 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 @ 2024 Oracle and/or its affiliates. 25

InnoDB GIPK mode - example MySQL MySQL > > SELECT SELECT @ @@sql_generate_invisible_primary_key @sql_generate_invisible_primary_key; ; + +--------------------------------------+ --------------------------------------+ | | @ @@sql_generate_invisible_primary_key @sql_generate_invisible_primary_key | | + +--------------------------------------+ --------------------------------------+ | | 1 1 | | + +--------------------------------------+ --------------------------------------+ MySQL MySQL > > CREATE CREATE TABLE TABLE mysql_italia mysql_italia ( (name name varchar varchar( (20 20) ), , pizzas pizzas int int unsigned unsigned) ); ; MySQL MySQL > > INSERT INSERT INTO INTO mysql_italia mysql_italia VALUES VALUES ( ('andra' 'andra', , 0 0) ), , ( ('marco' 'marco', ,2 2) ); ; MySQL MySQL > > SELECT SELECT * * FROM FROM mysql_italia mysql_italia; ; + +-------+--------+ -------+--------+ | | name name | | pizzas pizzas | | + +-------+--------+ -------+--------+ | | andra andra | | 0 0 | | | | marco marco | | 2 2 | | + +--------+-------+ --------+-------+ Copyright @ 2024 Oracle and/or its affiliates. 26

InnoDB GIPK mode - example (2) MySQL MySQL > > SHOW SHOW CREATE CREATE TABLE TABLE mysql_italia\G mysql_italia\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Table Table: devlive : devlive Create Create Table Table: : CREATE CREATE TABLE TABLE ` `mysql_italia mysql_italia` ` ( ( ` `my_row_id my_row_id` ` bigint bigint unsigned unsigned NOT NOT NULL NULL AUTO_INCREMENT AUTO_INCREMENT /*!80023 INVISIBLE */ /*!80023 INVISIBLE */, , ` `name name` ` varchar varchar( (20 20) ) DEFAULT DEFAULT NULL NULL, , ` `pizzas pizzas` ` int int unsigned unsigned DEFAULT DEFAULT NULL NULL, , PRIMARY PRIMARY KEY KEY ( (` `my_row_id my_row_id` `) ) ) ) ENGINE ENGINE= =InnoDB InnoDB AUTO_INCREMENT AUTO_INCREMENT= =3 3 DEFAULT DEFAULT CHARSET CHARSET= =utf8mb4 utf8mb4 COLLATE COLLATE= =utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci Copyright @ 2024 Oracle and/or its affiliates. 27

InnoDB GIPK mode - example (2) MySQL MySQL > > SHOW SHOW CREATE CREATE TABLE TABLE mysql_italia\G mysql_italia\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Table Table: devlive : devlive Create Create Table Table: : CREATE CREATE TABLE TABLE ` `mysql_italia mysql_italia` ` ( ( ` `my_row_id my_row_id` ` bigint bigint unsigned unsigned NOT NOT NULL NULL AUTO_INCREMENT AUTO_INCREMENT /*!80023 INVISIBLE */ /*!80023 INVISIBLE */, , ` `name name` ` varchar varchar( (20 20) ) DEFAULT DEFAULT NULL NULL, , ` `pizzas pizzas` ` int int unsigned unsigned DEFAULT DEFAULT NULL NULL, , PRIMARY PRIMARY KEY KEY ( (` `my_row_id my_row_id` `) ) ) ) ENGINE ENGINE= =InnoDB InnoDB AUTO_INCREMENT AUTO_INCREMENT= =3 3 DEFAULT DEFAULT CHARSET CHARSET= =utf8mb4 utf8mb4 COLLATE COLLATE= =utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci MySQL MySQL > > SELECT SELECT * *, , my_row_id my_row_id FROM FROM mysql_italia mysql_italia; ; + +-------+--------+-----------+ -------+--------+-----------+ | | name name | | pizzas pizzas | | my_row_id my_row_id | | + +-------+--------+-----------+ -------+--------+-----------+ | | andra andra | | 0 0 | | 1 1 | | | | marco marco | | 1 1 | | 2 2 | | + +-------+--------+-----------+ -------+--------+-----------+ Copyright @ 2024 Oracle and/or its affiliates. 27

InnoDB GIPK mode - example (3) It's also possible to hide it completely (for some legacy application that could rely on information_schema and SHOW CREATE TABLE): MySQL MySQL > > SET SET show_gipk_in_create_table_and_information_schema show_gipk_in_create_table_and_information_schema = = 0 0; ; MySQL MySQL > > SHOW SHOW CREATE CREATE TABLE TABLE mysql_italia\G mysql_italia\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Table Table: mysql_italia : mysql_italia Create Create Table Table: : CREATE CREATE TABLE TABLE ` `mysql_italia mysql_italia` ` ( ( ` `name name` ` varchar varchar( (20 20) ) DEFAULT DEFAULT NULL NULL, , ` `beers beers` ` int int unsigned unsigned DEFAULT DEFAULT NULL NULL ) ) ENGINE ENGINE= =InnoDB InnoDB DEFAULT DEFAULT CHARSET CHARSET= =utf8mb4 utf8mb4 COLLATE COLLATE= =utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci Copyright @ 2024 Oracle and/or its affiliates. 28

#8 - Indexes, not too li le, not too much unused indexes Having to maintain indexes that are not used can be costly and increase unnecessary iops. Using sys Schema and innodb_index_stats it's possible to identify those unused indexes: select select database_name database_name, , table_name table_name, , t1 t1. .index_name index_name, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 on on object_schema object_schema= =database_name database_name and and object_name object_name= =table_name table_name and and t2 t2. .index_name index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' order order by by stat_value stat_value desc desc; ; Copyright @ 2024 Oracle and/or its affiliates. 29

#8 - Indexes, not too li le, not too much unused indexes Having to maintain indexes that are not used can be costly and increase unnecessary iops. Using sys Schema and innodb_index_stats it's possible to identify those unused indexes: select select database_name database_name, , table_name table_name, , t1 t1. .index_name index_name, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 on on object_schema object_schema= =database_name database_name and and object_name object_name= =table_name table_name and and t2 t2. .index_name index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' order order by by stat_value stat_value desc desc; ; Copyright @ 2024 Oracle and/or its affiliates. select select database_name database_name, , table_name table_name, , t1 t1. .index_name index_name, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 on on object_schema object_schema= =database_name database_name and and object_name object_name= =table_name table_name and and t2 t2. .index_name index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' and and database_name database_name= ="employees" "employees" order order by by stat_value stat_value desc desc; ; + +---------------+--------------+---------------------+-----------+ ---------------+--------------+---------------------+-----------+ | | database_name database_name | | table_name table_name | | index_name index_name | | size size | | + +---------------+--------------+---------------------+-----------+ ---------------+--------------+---------------------+-----------+ | | employees employees | | employees employees | | hash_bin_names2 hash_bin_names2 | | 9.52 9.52 MiB MiB | | | | employees employees | | employees employees | | month_year_hire_idx month_year_hire_idx | | 6.52 6.52 MiB MiB | | | | employees employees | | dept_emp dept_emp | | dept_no dept_no | | 5.52 5.52 MiB MiB | | | | employees employees | | dept_manager dept_manager | | dept_no dept_no | | 16.00 16.00 KiB KiB | | + +---------------+--------------+---------------------+-----------+ ---------------+--------------+---------------------+-----------+ 4 4 rows rows in in set set ( (0.0252 0.0252 sec sec) ) 29

Duplicate indexes And this is the same behaviour for duplicate indexes. There is no reason to keep maintaining them: select select t2 t2. .* *, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_redundant_indexes t2 schema_redundant_indexes t2 on on table_schema table_schema= =database_name database_name and and t2 t2. .table_name table_name= =t1 t1. .table_name table_name and and t2 t2. .redundant_index_name redundant_index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' order order by by stat_value stat_value desc desc\G \G Copyright @ 2024 Oracle and/or its affiliates. 30

Duplicate indexes And this is the same behaviour for duplicate indexes. There is no reason to keep maintaining them: select select t2 t2. .* *, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_redundant_indexes t2 schema_redundant_indexes t2 on on table_schema table_schema= =database_name database_name and and t2 t2. .table_name table_name= =t1 t1. .table_name table_name and and t2 t2. .redundant_index_name redundant_index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' order order by by stat_value stat_value desc desc\G \G Copyright @ 2024 Oracle and/or its affiliates. * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * table_schema: world table_schema: world table_name: city table_name: city redundant_index_name: part_of_name redundant_index_name: part_of_name redundant_index_columns: Name redundant_index_columns: Name redundant_index_non_unique: redundant_index_non_unique: 1 1 dominant_index_name: name_idx dominant_index_name: name_idx dominant_index_columns: Name dominant_index_columns: Name dominant_index_non_unique: dominant_index_non_unique: 1 1 subpart_exists: subpart_exists: 1 1 sql_drop_index: sql_drop_index: ALTER ALTER TABLE TABLE ` `world world` `. .` `city city` ` DROP DROP INDEX INDEX ` `part_of_name part_of_name` ` size: size: 112.00 112.00 KiB KiB * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 2. 2. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * table_schema: world table_schema: world table_name: countrylanguage table_name: countrylanguage redundant_index_name: CountryCode redundant_index_name: CountryCode redundant_index_columns: CountryCode redundant_index_columns: CountryCode redundant_index_non_unique: redundant_index_non_unique: 1 1 dominant_index_name: dominant_index_name: PRIMARY PRIMARY dominant_index_columns: CountryCode dominant_index_columns: CountryCode, ,Language Language dominant_index_non_unique: dominant_index_non_unique: 0 0 subpart_exists: subpart_exists: 0 0 sql_drop_index: sql_drop_index: ALTER ALTER TABLE TABLE ` `world world` `. .` `countrylanguage countrylanguage` ` DROP DROP INDEX INDEX ` `CountryCode CountryCode` ` size: size: 64.00 64.00 KiB KiB 2 2 rows rows in in set set ( (0.0330 0.0330 sec sec) ) 30

Don't forget ! Do not take recommendations at face value, check before deleting an index. Do not delete an index immediately, but rst set it as INVISIBLE for some time. Once in a while this index might be used, like for a monthly report. Copyright @ 2024 Oracle and/or its affiliates. 31

#9 - Missing indexes We also need to nd which indexes might be missing: MySQL MySQL > > select select * * from from sys sys. .schema_tables_with_full_table_scans schema_tables_with_full_table_scans; ; + +-----------------------------+-------------+-------------------+-----------+ -----------------------------+-------------+-------------------+-----------+ | | object_schema object_schema | | object_name object_name | | rows_full_scanned rows_full_scanned | | latency latency | | + +-----------------------------+-------------+-------------------+-----------+ -----------------------------+-------------+-------------------+-----------+ | | students students | | Customers Customers | | 12210858800 12210858800 | | 41.28 41.28 min min | | + +-----------------------------+-------------+-------------------+-----------+ -----------------------------+-------------+-------------------+-----------+ Copyright @ 2024 Oracle and/or its affiliates. 32

#9 - Missing indexes We also need to nd which indexes might be missing: MySQL MySQL > > select select * * from from sys sys. .schema_tables_with_full_table_scans schema_tables_with_full_table_scans; ; + +-----------------------------+-------------+-------------------+-----------+ -----------------------------+-------------+-------------------+-----------+ | | object_schema object_schema | | object_name object_name | | rows_full_scanned rows_full_scanned | | latency latency | | + +-----------------------------+-------------+-------------------+-----------+ -----------------------------+-------------+-------------------+-----------+ | | students students | | Customers Customers | | 12210858800 12210858800 | | 41.28 41.28 min min | | + +-----------------------------+-------------+-------------------+-----------+ -----------------------------+-------------+-------------------+-----------+ Copyright @ 2024 Oracle and/or its affiliates. 32

#9 - Missing indexes We also need to nd which indexes might be missing: MySQL MySQL > > select select * * from from sys sys. .schema_tables_with_full_table_scans schema_tables_with_full_table_scans; ; + +-----------------------------+-------------+-------------------+-----------+ -----------------------------+-------------+-------------------+-----------+ | | object_schema object_schema | | object_name object_name | | rows_full_scanned rows_full_scanned | | latency latency | | + +-----------------------------+-------------+-------------------+-----------+ -----------------------------+-------------+-------------------+-----------+ | | students students | | Customers Customers | | 12210858800 12210858800 | | 41.28 41.28 min min | | + +-----------------------------+-------------+-------------------+-----------+ -----------------------------+-------------+-------------------+-----------+ Copyright @ 2024 Oracle and/or its affiliates. MySQL MySQL > > select select * * from from sys sys. .statements_with_full_table_scans statements_with_full_table_scans where where db db= ='students' 'students' and and query query like like '%customers%' '%customers%'\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * query: query: SELECT SELECT * * FROM FROM ` `Customers Customers` ` WHERE WHERE ` `age age` ` > > ? ? db: students db: students exec_count: exec_count: 140 140 total_latency: total_latency: 17.97 17.97s s no_index_used_count: no_index_used_count: 137 137 no_good_index_used_count: no_good_index_used_count: 0 0 no_index_used_pct: no_index_used_pct: 100 100 rows_sent: rows_sent: 87220420 87220420 rows_examined: rows_examined: 12210858800 12210858800 rows_sent_avg: rows_sent_avg: 623003 623003 rows_examined_avg: rows_examined_avg: 2505942 2505942 first_seen: first_seen: 23 23- -01 01- -27 27 14 14: :34 34: :12.66877 12.66877 last_seen: last_seen: 2023 2023- -02 02- -23 23 17 17: :44 44: :47.738911 47.738911 digest: digest: 4396 4396a7fc5d8f2cdc157b04bbd0543facaeaa5d4bb0ab02734b101ab5018a9b18 a7fc5d8f2cdc157b04bbd0543facaeaa5d4bb0ab02734b101ab5018a9b18 32

Question Copyright @ 2024 Oracle and/or its affiliates. But when I add or remove an Index, can I estimate the time left ? 33

#10 - ALTER Progression select select stmt stmt. .thread_id thread_id, , stmt stmt. .sql_text sql_text, , stage stage. .event_name event_name as as state state, , stage stage. .work_completed work_completed, , stage stage. .work_estimated work_estimated, , lpad lpad( (concat concat( (round round( (100 100* *stage stage. .work_completed work_completed/ /stage stage. .work_estimated work_estimated, , 2 2) ), ,"%" "%") ), ,10 10, ," " " ") ) as as completed_at completed_at, , lpad lpad( (format_pico_time format_pico_time( (stmt stmt. .timer_wait timer_wait) ), , 10 10, , " " " ") ) as as started_ago started_ago, , lpad lpad( (format_pico_time format_pico_time( (stmt stmt. .timer_wait timer_wait/ /round round( (100 100* *stage stage. .work_completed work_completed/ /stage stage. .work_estimated work_estimated, ,2 2) )* *100 100) ), , 10 10, , " " " ") ) as as estimated_full_time estimated_full_time, , lpad lpad( (format_pico_time format_pico_time( (( (stmt stmt. .timer_wait timer_wait/ /round round( (100 100* *stage stage. .work_completed work_completed/ /stage stage. .work_estimated work_estimated, ,2 2) )* *100 100) ) - -stmt stmt. .timer_wait timer_wait) ), , 10 10, , " " " ") ) as as estimated_remaining_time estimated_remaining_time, , current_allocated memory current_allocated memory from from performance_schema performance_schema. .events_statements_current stmt events_statements_current stmt inner inner join join sys sys. .memory_by_thread_by_current_bytes mt memory_by_thread_by_current_bytes mt on on mt mt. .thread_id thread_id = = stmt stmt. .thread_id thread_id inner inner join join performance_schema performance_schema. .events_stages_current stage events_stages_current stage on on stage stage. .thread_id thread_id = = stmt stmt. .thread_id\G thread_id\G Copyright @ 2024 Oracle and/or its affiliates. 34

ALTER Progression - example Copyright @ 2024 Oracle and/or its affiliates. 35

Index Creation is slow < > Copyright @ 2024 Oracle and/or its affiliates. Creating indexes is a very slow operation even on my powerfull server with multiple cores ! Anything I can do ? 36

Index Creation is slow < > Copyright @ 2024 Oracle and/or its affiliates. Creating indexes is a very slow operation even on my powerfull server with multiple cores ! Anything I can do ? Since MySQL 8.0.27, you have the possibility to control the maximum of parallel threads InnoDB can use to create seconday indexes ! 36

#11 - Parallel Index Creation The amount of parallel threads used by InnoDB is controlled by innodb_ddl_threads. This new variable is coupled with another new variable: innodb_ddl_buffer_size. If you have fast storage and multiple CPU cores, tuning these variables can speed up secondary index creation. Copyright @ 2024 Oracle and/or its affiliates. 37

Parallel Index Creation - example MySQL MySQL > > alter alter table table booking booking add add index index idx_2 idx_2( (flight_id flight_id, , seat seat, , passenger_id passenger_id) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (9 9 min min 0.6838 0.6838 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 38

Parallel Index Creation - example MySQL MySQL > > alter alter table table booking booking add add index index idx_2 idx_2( (flight_id flight_id, , seat seat, , passenger_id passenger_id) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (9 9 min min 0.6838 0.6838 sec sec) ) The default se ings are: innodb_ddl_threads = 4 innodb_ddl_buffer_size = 1048576 innodb_parallel_read_threads = 4 Copyright @ 2024 Oracle and/or its affiliates. 38

Parallel Index Creation - example MySQL MySQL > > alter alter table table booking booking add add index index idx_2 idx_2( (flight_id flight_id, , seat seat, , passenger_id passenger_id) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (9 9 min min 0.6838 0.6838 sec sec) ) The default se ings are: innodb_ddl_threads = 4 innodb_ddl_buffer_size = 1048576 innodb_parallel_read_threads = 4 The innodb_ddl_buffer_size is shared between all innodb_ddl_threads de ned. If you increase the amount of threads, I recommend that you also increase the bu er size. Copyright @ 2024 Oracle and/or its affiliates. 38

Parallel Index Creation - example (2) To nd the best values for these variables, let's have a look at the amount of CPU cores: MySQL MySQL > > select select count count from from information_schema information_schema. .INNODB_METRICS INNODB_METRICS where where name name = = 'cpu_n' 'cpu_n'; ; + +-------+ -------+ | | count count | | + +-------+ -------+ | | 16 16 | | + +-------+ -------+ We have then 16 cores to share. As my machine as plenty of memory, I will allocate 1GB for the InnoDB DDL bu er. Copyright @ 2024 Oracle and/or its affiliates. 39

Parallel Index Creation - example (3) MySQL MySQL > > SET SET innodb_ddl_threads innodb_ddl_threads = = 8 8; ; MySQL MySQL > > SET SET innodb_parallel_read_threads innodb_parallel_read_threads = = 8 8; ; MySQL MySQL > > SET SET innodb_ddl_buffer_size innodb_ddl_buffer_size = = 1048576000 1048576000; ; Copyright @ 2024 Oracle and/or its affiliates. 40

Parallel Index Creation - example (3) MySQL MySQL > > SET SET innodb_ddl_threads innodb_ddl_threads = = 8 8; ; MySQL MySQL > > SET SET innodb_parallel_read_threads innodb_parallel_read_threads = = 8 8; ; MySQL MySQL > > SET SET innodb_ddl_buffer_size innodb_ddl_buffer_size = = 1048576000 1048576000; ; We can now retry the same index creation as previously: MySQL MySQL > > alter alter table table booking booking add add index index idx_2 idx_2( (flight_id flight_id, , seat seat, , passenger_id passenger_id) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (3 3 min min 9.1862 9.1862 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 40

Parallel Index Creation - example (4) I recommend to make tests to de ne the optimal se ings for your database, your hardware and data. For example, I got the best result se ing the bu er size to 2GB and both ddl threads and parallel read threads to 4. It took 2 min 43 sec, much be er than the initial 9 minutes ! For more information, go to h ps://lefred.be/content/mysql-8-0-innodb-parallel-threads- for-online-ddl-operations/ Copyright @ 2024 Oracle and/or its affiliates. 41

Exporting & Importing Data at speed of light ! Copyright @ 2024 Oracle and/or its affiliates. 42

#12 - Exporting Data in Parallel For logical dumps, MySQL Shell Dump & Load Utility should be preferred over the old and single threaded mysqldump ! MySQL Shell Dump & Load can dump a full instance, one or multiple schemas or tables. You can also add a where clause. This tool dumps and load the data in parallel ! The data can be stored on lesystem, OCI Object Storage, S3 and Azure Blob Storage. JS JS > > util util. .dumpInstance dumpInstance( ("/opt/dump/" "/opt/dump/", , { {threads threads: : 32 32} }) ) Copyright @ 2024 Oracle and/or its affiliates. 43

#12bis - Importing Data The generated dump can be loaded to MySQL using util.loadDump(). loadDump() is the method used to load dumps created by: util.dumpInstance() util.dumpSchemas() util.dumpTables() JS JS > > util util. .loadDump loadDump( ("/opt/dump" "/opt/dump", , { {threads threads: : 32 32} }) ) Copyright @ 2024 Oracle and/or its affiliates. 44

#13 - Importing Data - faster We can speed up the process even more ! During an initial load, the durability is not a probem, if there is a crash, the process can be restarted. Therefore, if the durability is not important, we can reduce it to speed up the loading even more. We can disable binary logs, disable redo logs and tune some InnoDB se ings. Pay a ention that disabling and enabling binary logs require a restart of MySQL. start start mysqld mysqld with with --disable-log-bin --disable-log-bin MySQL MySQL > > ALTER ALTER INSTANCE INSTANCE DISABLE DISABLE INNODB INNODB REDO_LOG REDO_LOG; ; MySQL MySQL > > set set global global innodb_extend_and_initialize innodb_extend_and_initialize= =OFF OFF; ; MySQL MySQL > > set set global global innodb_max_dirty_pages_pct innodb_max_dirty_pages_pct= =10 10; ; MySQL MySQL > > set set global global innodb_max_dirty_pages_pct_lwm innodb_max_dirty_pages_pct_lwm= =10 10; ; Copyright @ 2024 Oracle and/or its affiliates. 45

#13 - Importing Data - faster We can speed up the process even more ! During an initial load, the durability is not a probem, if there is a crash, the process can be restarted. Therefore, if the durability is not important, we can reduce it to speed up the loading even more. We can disable binary logs, disable redo logs and tune some InnoDB se ings. Pay a ention that disabling and enabling binary logs require a restart of MySQL. start start mysqld mysqld with with --disable-log-bin --disable-log-bin MySQL MySQL > > ALTER ALTER INSTANCE INSTANCE DISABLE DISABLE INNODB INNODB REDO_LOG REDO_LOG; ; MySQL MySQL > > set set global global innodb_extend_and_initialize innodb_extend_and_initialize= =OFF OFF; ; MySQL MySQL > > set set global global innodb_max_dirty_pages_pct innodb_max_dirty_pages_pct= =10 10; ; MySQL MySQL > > set set global global innodb_max_dirty_pages_pct_lwm innodb_max_dirty_pages_pct_lwm= =10 10; ; Copyright @ 2024 Oracle and/or its affiliates. 2802 chunks (194.70M rows, 64.75 GB) for 1 tables in 1 schemas were loaded in 4 min 51 sec (avg throughput 222.51 MB/s) 45

Memory RAM, RAM, RAM ❤ Copyright @ 2024 Oracle and/or its affiliates. 46

#14 - Memory - InnoDB It's important to have the Working Set in memory as Memory is still faster than Disk. We can verify that most of the page requests are coming from memory: show show global global status status like like 'innodb_buffer_pool_read%s' 'innodb_buffer_pool_read%s'; ; + +----------------------------------+--------+ ----------------------------------+--------+ | | Variable_name Variable_name | | Value Value | | + +----------------------------------+--------+ ----------------------------------+--------+ | | Innodb_buffer_pool_read_requests Innodb_buffer_pool_read_requests | | 365290 365290 | | | | Innodb_buffer_pool_reads Innodb_buffer_pool_reads | | 1594 1594 | | + +----------------------------------+--------+ ----------------------------------+--------+ Copyright @ 2024 Oracle and/or its affiliates. 47

#14 - Memory - InnoDB It's important to have the Working Set in memory as Memory is still faster than Disk. We can verify that most of the page requests are coming from memory: show show global global status status like like 'innodb_buffer_pool_read%s' 'innodb_buffer_pool_read%s'; ; + +----------------------------------+--------+ ----------------------------------+--------+ | | Variable_name Variable_name | | Value Value | | + +----------------------------------+--------+ ----------------------------------+--------+ | | Innodb_buffer_pool_read_requests Innodb_buffer_pool_read_requests | | 365290 365290 | | | | Innodb_buffer_pool_reads Innodb_buffer_pool_reads | | 1594 1594 | | + +----------------------------------+--------+ ----------------------------------+--------+ Copyright @ 2024 Oracle and/or its affiliates. SELECT SELECT CONCAT CONCAT( (FORMAT FORMAT( (B B. .num num * * 100.0 100.0 / / A A. .num num, ,2 2) ), ,'%' '%') ) DiskReadRatio DiskReadRatio FROM FROM ( ( SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name = = 'Innodb_buffer_pool_read_requests' 'Innodb_buffer_pool_read_requests') ) A A, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name = = 'Innodb_buffer_pool_reads' 'Innodb_buffer_pool_reads') ) B B; ; + +---------------+ ---------------+ | | DiskReadRatio DiskReadRatio | | + +---------------+ ---------------+ | | 0.44 0.44% % | | + +---------------+ ---------------+ 47

Memory - InnoDB (2) We can get the InnoDB Bu er Pool's memory allocation usage with the following query: MySQL MySQL > > SELECT SELECT * * FROM FROM sys sys. .memory_global_by_current_bytes memory_global_by_current_bytes WHERE WHERE event_name event_name LIKE LIKE 'memory/innodb/buf_buf_pool' 'memory/innodb/buf_buf_pool'\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * event_name: memory event_name: memory/ /innodb innodb/ /buf_buf_pool buf_buf_pool current_count: current_count: 1 1 current_alloc: current_alloc: 130.88 130.88 MiB MiB current_avg_alloc: current_avg_alloc: 130.88 130.88 MiB MiB high_count: high_count: 1 1 high_alloc: high_alloc: 130.88 130.88 MiB MiB high_avg_alloc: high_avg_alloc: 130.88 130.88 MiB MiB 1 1 row row in in set set ( (0.0010 0.0010 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 48

#15 - Memory - MySQL From Performance_Schema (and sys) we can get information about the Memory consumption of MySQL, this instrumentation has been extended in MySQL 8.0: SELECT SELECT * * FROM FROM sys sys. .memory_global_total memory_global_total; ; And you can have details related to the code area: SELECT SELECT SUBSTRING_INDEX SUBSTRING_INDEX( (event_name event_name, ,'/' '/', ,2 2) ) AS AS code_area code_area, , format_bytes format_bytes( (SUM SUM( (current_alloc current_alloc) )) ) AS AS current_alloc current_alloc FROM FROM sys sys. .x$memory_global_by_current_bytes x$memory_global_by_current_bytes GROUP GROUP BY BY SUBSTRING_INDEX SUBSTRING_INDEX( (event_name event_name, ,'/' '/', ,2 2) ) ORDER ORDER BY BY SUM SUM( (current_alloc current_alloc) ) DESC DESC; ; Copyright @ 2024 Oracle and/or its affiliates. 49

#15 - Memory - MySQL From Performance_Schema (and sys) we can get information about the Memory consumption of MySQL, this instrumentation has been extended in MySQL 8.0: SELECT SELECT * * FROM FROM sys sys. .memory_global_total memory_global_total; ; And you can have details related to the code area: SELECT SELECT SUBSTRING_INDEX SUBSTRING_INDEX( (event_name event_name, ,'/' '/', ,2 2) ) AS AS code_area code_area, , format_bytes format_bytes( (SUM SUM( (current_alloc current_alloc) )) ) AS AS current_alloc current_alloc FROM FROM sys sys. .x$memory_global_by_current_bytes x$memory_global_by_current_bytes GROUP GROUP BY BY SUBSTRING_INDEX SUBSTRING_INDEX( (event_name event_name, ,'/' '/', ,2 2) ) ORDER ORDER BY BY SUM SUM( (current_alloc current_alloc) ) DESC DESC; ; Copyright @ 2024 Oracle and/or its affiliates. +-----------------+ | total_allocated | +-----------------+ | 4.28 GiB | +-----------------+ +---------------------------+---------------+ | code_area | current_alloc | +---------------------------+---------------+ | memory/innodb | 2.30 GiB | | memory/group_rpl | 1024.00 MiB | | memory/performance_schema | 916.88 MiB | | memory/sql | 75.80 MiB | | memory/mysys | 9.13 MiB | | memory/temptable | 3.00 MiB | | memory/mysqlx | 22.42 KiB | | memory/vio | 3.16 KiB | | memory/myisam | 696 bytes | | memory/csv | 88 bytes | | memory/blackhole | 88 bytes | +---------------------------+---------------+ 49

#16 - Memory Connections Tracking and Limiting To avoid bad surprises (like swapping), it's possible to track and limit the memory consumption of the connections. To enable it you need to set global_connection_memory_tracking to 1: MySQL MySQL > > SET SET global global global_connection_memory_tracking global_connection_memory_tracking= =1 1; ; Copyright @ 2024 Oracle and/or its affiliates. 50

#16 - Memory Connections Tracking and Limiting To avoid bad surprises (like swapping), it's possible to track and limit the memory consumption of the connections. To enable it you need to set global_connection_memory_tracking to 1: MySQL MySQL > > SET SET global global global_connection_memory_tracking global_connection_memory_tracking= =1 1; ; You can limit the connection memory limit: MySQL MySQL > > SET SET < > connection_memory_limit connection_memory_limit= =2200000 2200000; ; MySQL MySQL > > SET SET GLOBAL GLOBAL global_connection_memory_limit global_connection_memory_limit= =536870912000 536870912000; ; Copyright @ 2024 Oracle and/or its affiliates. 50

Memory - Connections Tracking and Limiting To know the Global Connection Consumption Memory: MySQL MySQL > > SELECT SELECT format_bytes format_bytes( (variable_value variable_value) ) global_connection_memory global_connection_memory FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name= ='Global_connection_memory' 'Global_connection_memory'; ; + +--------------------------+ --------------------------+ | | global_connection_memory global_connection_memory | | + +--------------------------+ --------------------------+ | | 16.22 16.22 MiB MiB | | + +--------------------------+ --------------------------+ Copyright @ 2024 Oracle and/or its affiliates. 51

Memory - Connections Tracking and Limiting If the limit it reached, the user will be disconnected with the following error: ERROR: ERROR: 4081 4081 ( (HY000 HY000) ): Connection closed : Connection closed. . Global Global connection memory connection memory limit limit 16777216 16777216 bytes exceeded bytes exceeded. . Consumed Consumed 16949968 16949968 bytes bytes. . This limitation doesn't appy to users with CONNECTION_ADMIN privilege. Copyright @ 2024 Oracle and/or its affiliates. 52

#17 - Memory Allocator To have be er performance choosing the right memory allocator (Linux) is important ! The default memory allocator in Linux distribution (glibc-malloc) doesn't perform well in high concurrency environments and should be avoided ! This let us with 2 choices: jemalloc (good for perf, but less RAM management e ciency) tcmalloc (recommended choice) Copyright @ 2024 Oracle and/or its affiliates. 53

Memory - and for be er performance ? (2) Install tcmalloc: $ $ sudo sudo yum yum -y -y install install gperftools-libs gperftools-libs And in systemd service le you need to add: $ $ sudo sudo EDITOR EDITOR= =vi systemctl edit mysqld vi systemctl edit mysqld [ [Service Service] ] Environment Environment= ="LD_PRELOAD=/usr/lib64/libtcmalloc_minimal.so.4" "LD_PRELOAD=/usr/lib64/libtcmalloc_minimal.so.4" Copyright @ 2024 Oracle and/or its affiliates. 54

Memory - and for be er performance ? (3) Reload the service and restart MySQL: $ $ sudo sudo systemctl daemon-reload systemctl daemon-reload $ $ sudo sudo systemctl restart mysqld systemctl restart mysqld Copyright @ 2024 Oracle and/or its affiliates. 55

Memory Allocator: jemalloc vs tcmalloc: Copyright @ 2024 Oracle and/or its affiliates. 56

If you are using Linux, you can optimize the operating system for MySQL and how it consummes the memory: OOM Killer Swappiness Filesystem Cache Numa Memory - Operating System Se ings Copyright @ 2024 Oracle and/or its affiliates. 57

#18 - Memory - OOM Killer In case of high memory usage on the system, OOM Killer could wake-up and restart MySQL ! Must absolutely avoid ! It's possible to check how sensitive MySQL is to OOM Killer: $ $ sudo sudo cat cat /proc/ /proc/$( $(pidof mysqld pidof mysqld) )/oom_score_adj /oom_score_adj 0 0 $ $ sudo sudo cat cat /proc/ /proc/$( $(pidof mysqld pidof mysqld) )/oom_score /oom_score 674 674 Once oom_score is set to any non-zero value, the score that oom_badness() calculates will be either reduced or increased by this manual adjustment. Copyright @ 2024 Oracle and/or its affiliates. 58

#18 - Memory - OOM Killer In case of high memory usage on the system, OOM Killer could wake-up and restart MySQL ! Must absolutely avoid ! It's possible to check how sensitive MySQL is to OOM Killer: $ $ sudo sudo cat cat /proc/ /proc/$( $(pidof mysqld pidof mysqld) )/oom_score_adj /oom_score_adj 0 0 $ $ sudo sudo cat cat /proc/ /proc/$( $(pidof mysqld pidof mysqld) )/oom_score /oom_score 674 674 Once oom_score is set to any non-zero value, the score that oom_badness() calculates will be either reduced or increased by this manual adjustment. Copyright @ 2024 Oracle and/or its affiliates. $ $ echo echo '-1000' '-1000' | | sudo sudo tee tee /proc/ /proc/$( $(pidof mysqld pidof mysqld) )/oom_score_adj /oom_score_adj -1000 -1000 $ $ sudo sudo cat cat /proc/ /proc/$( $(pidof mysqld pidof mysqld) )/oom_score /oom_score 0 0 _ _ 58

Memory - Swap As a MySQL DBA, you should hate when your server is swapping and you are right ! Copyright @ 2024 Oracle and/or its affiliates. 59

Memory - Swap As a MySQL DBA, you should hate when your server is swapping and you are right ! However, disabling the swap is also a bad idea, it's be er to swap than to have MySQL killed ! Copyright @ 2024 Oracle and/or its affiliates. 59

Memory - Swap As a MySQL DBA, you should hate when your server is swapping and you are right ! However, disabling the swap is also a bad idea, it's be er to swap than to have MySQL killed ! But we need to reduce the swap usage as much as possible. Let's verify if the server uses the swap: free free -h -h total used total used free free shared buff/cache available shared buff/cache available Mem: 31Gi 13Gi Mem: 31Gi 13Gi 1 1.3Gi .3Gi 2 2.2Gi 16Gi 14Gi .2Gi 16Gi 14Gi Swap: Swap: 8 8.0Gi .0Gi 2 2.1Gi .1Gi 5 5.9Gi .9Gi Copyright @ 2024 Oracle and/or its affiliates. 59

Memory - Swap (2) Is mysqld swapping ? Copyright @ 2024 Oracle and/or its affiliates. 60

Memory - Swap (2) Is mysqld swapping ? cat cat /proc/ /proc/$( $(pidof mysqld pidof mysqld) )/status /status | | grep grep Swap Swap VmSwap: VmSwap: 389504 389504 kB kB MySQL uses some swap. You can verify how often the server is swapping using vmstat 1 10. Copyright @ 2024 Oracle and/or its affiliates. 60

#19 - Memory - Swappiness On Linux, it's the swappiness that controls the tendency of the kernel to move out of physical memory to the swap. The default value(60) is way too high for a decicated MySQL server and should be reduced. Don't set it to 0 (which was recommended in kernels < 2.6.32). I recommend to set the swappiness to a value between 1 and 5: sysctl sysctl -w -w vm.swappiness vm.swappiness= =1 1 Copyright @ 2024 Oracle and/or its affiliates. 61

#20 - Memory - CPU & NUMA Knowing your CPU is also very important as it can also in uence on the memory usage and the swap. Copyright @ 2024 Oracle and/or its affiliates. 62

#20 - Memory - CPU & NUMA Knowing your CPU is also very important as it can also in uence on the memory usage and the swap. The question you need to answer is : Has my CPU multiple NUMA cores ? Copyright @ 2024 Oracle and/or its affiliates. 62

#20 - Memory - CPU & NUMA Knowing your CPU is also very important as it can also in uence on the memory usage and the swap. The question you need to answer is : Has my CPU multiple NUMA cores ? You can get the answer using numactl command on Linux: numactl numactl -H -H | | grep grep available available available: available: 4 4 nodes nodes ( (0 0-3 -3) ) If the result is > 1, then you would bene t from using innodb_numa_interleave = 1 Copyright @ 2024 Oracle and/or its affiliates. 62

SMP Architecture NUMA Architecture Memory - CPU Images from @jeremycole h ps://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/ Copyright @ 2024 Oracle and/or its affiliates. 63

Extra - CPU When looking for performance, it's also important so set the CPU Frequency to its highest value. There is no need to let the Kernel change the frequency on the y. Copyright @ 2024 Oracle and/or its affiliates. 64

Extra - CPU When looking for performance, it's also important so set the CPU Frequency to its highest value. There is no need to let the Kernel change the frequency on the y. To setup such behavior, the CPU Frequency Governor should be set to performance: Copyright @ 2024 Oracle and/or its affiliates. 64

Extra - CPU When looking for performance, it's also important so set the CPU Frequency to its highest value. There is no need to let the Kernel change the frequency on the y. To setup such behavior, the CPU Frequency Governor should be set to performance: for for i i in in $( $(ls ls /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor) ) do do echo echo performance performance > > $i $i done done Copyright @ 2024 Oracle and/or its affiliates. 64

Extra - CPU (2) Or you can use cpupower: sudo sudo cpupower cpupower -c -c all frequency-set all frequency-set -g -g performance performance Setting cpu: Setting cpu: 0 0 Setting cpu: Setting cpu: 1 1 Setting cpu: Setting cpu: 2 2 Setting cpu: Setting cpu: 3 3 Copyright @ 2024 Oracle and/or its affiliates. 65

Extra - CPU (3) In MySQL 8.0, it's possible to get CPU information directly in SQL: SELECT SELECT * * FROM FROM information_schema information_schema. .INNODB_METRICS INNODB_METRICS WHERE WHERE NAME NAME LIKE LIKE 'cpu%' 'cpu%'\G \G  CPU metrics need to be enabled in INFORMATION_SCHEMA.INNODB_METRICS Copyright @ 2024 Oracle and/or its affiliates. 66

Extra - CPU (3) In MySQL 8.0, it's possible to get CPU information directly in SQL: SELECT SELECT * * FROM FROM information_schema information_schema. .INNODB_METRICS INNODB_METRICS WHERE WHERE NAME NAME LIKE LIKE 'cpu%' 'cpu%'\G \G  CPU metrics need to be enabled in INFORMATION_SCHEMA.INNODB_METRICS SELECT SELECT NAME NAME, , COUNT COUNT, , MAX_COUNT MAX_COUNT FROM FROM information_schema information_schema. .INNODB_METRICS INNODB_METRICS WHERE WHERE SUBSYSTEM SUBSYSTEM = = 'cpu' 'cpu'; ; Copyright @ 2024 Oracle and/or its affiliates. 66

Extra - CPU (3) In MySQL 8.0, it's possible to get CPU information directly in SQL: SELECT SELECT * * FROM FROM information_schema information_schema. .INNODB_METRICS INNODB_METRICS WHERE WHERE NAME NAME LIKE LIKE 'cpu%' 'cpu%'\G \G  CPU metrics need to be enabled in INFORMATION_SCHEMA.INNODB_METRICS SELECT SELECT NAME NAME, , COUNT COUNT, , MAX_COUNT MAX_COUNT FROM FROM information_schema information_schema. .INNODB_METRICS INNODB_METRICS WHERE WHERE SUBSYSTEM SUBSYSTEM = = 'cpu' 'cpu'; ; Copyright @ 2024 Oracle and/or its affiliates. +---------------+-------+-----------+ | NAME | COUNT | MAX_COUNT | +---------------+-------+-----------+ | module_cpu | 0 | NULL | | cpu_utime_abs | 35 | 140 | | cpu_stime_abs | 12 | 113 | | cpu_utime_pct | 17 | 70 | | cpu_stime_pct | 6 | 56 | | cpu_n | 2 | 2 | +---------------+-------+-----------+ 66

#21 - Memory - Filesystem Cache By default, Linux uses the lesystem cache for all I/O accesses. As you are using InnoDB, with O_DIRECT or O_DIRECT_NO_FSYNC, MySQL bypasses the lesystem cache and will not use any FS Cache Memory for the data les (*.ibd). However MySQL might use this cache for others les and consume more memory than expected. If you parse regularly Binary Logs, these can consume the Filesystem Cache. Copyright @ 2024 Oracle and/or its affiliates. 67

Memory - Filesystem Cache Let's have a look at the previous memory usage: free free -h -h total used total used free free shared buff/cache available shared buff/cache available Mem: 31Gi 13Gi Mem: 31Gi 13Gi 1 1.3Gi .3Gi 2 2.2Gi 16Gi 14Gi .2Gi 16Gi 14Gi Swap: Swap: 8 8.0Gi .0Gi 2 2.1Gi .1Gi 5 5.9Gi .9Gi We can see that 16G are used by the lesystem cache. Copyright @ 2024 Oracle and/or its affiliates. 68

Memory - Filesystem Cache (3) The InnoDB Redo Log les are using the lesystem cache: dbsake fincore dbsake fincore \ \#ib_redo16* #ib_redo16* #ib_redo160: total_pages=800 cached=322 percent=40.25 #ib_redo160: total_pages=800 cached=322 percent=40.25 #ib_redo161: total_pages=800 cached=762 percent=95.25 #ib_redo161: total_pages=800 cached=762 percent=95.25 #ib_redo162: total_pages=800 cached=771 percent=96.38 #ib_redo162: total_pages=800 cached=771 percent=96.38 #ib_redo163: total_pages=800 cached=749 percent=93.62 #ib_redo163: total_pages=800 cached=749 percent=93.62 #ib_redo164: total_pages=800 cached=753 percent=94.12 #ib_redo164: total_pages=800 cached=753 percent=94.12 #ib_redo165: total_pages=800 cached=394 percent=49.25 #ib_redo165: total_pages=800 cached=394 percent=49.25 With the new InnoDB Redo Log Architecture, I would not worry about these les. Copyright @ 2024 Oracle and/or its affiliates. 69

Memory - Filesystem Cache (4) For Binary Logs it's not needed to keep them all in the cache. We can remove them from the cache and only keep the last one: dbsake fincore mysql-bin.0* dbsake fincore mysql-bin.0* mysql-bin.000001: mysql-bin.000001: total_pages total_pages= =66087 66087 cached cached= =66087 66087 percent percent= =100.00 100.00 mysql-bin.000002: mysql-bin.000002: total_pages total_pages= =1 1 cached cached= =1 1 percent percent= =100.00 100.00 mysql-bin.000003: mysql-bin.000003: total_pages total_pages= =1 1 cached cached= =1 1 percent percent= =100.00 100.00 mysql-bin.000004: mysql-bin.000004: total_pages total_pages= =4611 4611 cached cached= =4611 4611 percent percent= =100.00 100.00 dbsake uncache mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 dbsake uncache mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 Uncached mysql-bin.000001 Uncached mysql-bin.000001 Uncached mysql-bin.000002 Uncached mysql-bin.000002 Uncached mysql-bin.000003 Uncached mysql-bin.000003 Copyright @ 2024 Oracle and/or its affiliates. 70

Memory - Filesystem Cache (4) For Binary Logs it's not needed to keep them all in the cache. We can remove them from the cache and only keep the last one: dbsake fincore mysql-bin.0* dbsake fincore mysql-bin.0* mysql-bin.000001: mysql-bin.000001: total_pages total_pages= =66087 66087 cached cached= =66087 66087 percent percent= =100.00 100.00 mysql-bin.000002: mysql-bin.000002: total_pages total_pages= =1 1 cached cached= =1 1 percent percent= =100.00 100.00 mysql-bin.000003: mysql-bin.000003: total_pages total_pages= =1 1 cached cached= =1 1 percent percent= =100.00 100.00 mysql-bin.000004: mysql-bin.000004: total_pages total_pages= =4611 4611 cached cached= =4611 4611 percent percent= =100.00 100.00 dbsake uncache mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 dbsake uncache mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 Uncached mysql-bin.000001 Uncached mysql-bin.000001 Uncached mysql-bin.000002 Uncached mysql-bin.000002 Uncached mysql-bin.000003 Uncached mysql-bin.000003 Copyright @ 2024 Oracle and/or its affiliates. dbsake fincore mysql-bin.0* dbsake fincore mysql-bin.0* mysql-bin.000001: mysql-bin.000001: total_pages total_pages= =66087 66087 cached cached= =0 0 percent percent= =0.00 0.00 mysql-bin.000002: mysql-bin.000002: total_pages total_pages= =1 1 cached cached= =0 0 percent percent= =0.00 0.00 mysql-bin.000003: mysql-bin.000003: total_pages total_pages= =1 1 cached cached= =0 0 percent percent= =0.00 0.00 mysql-bin.000004: mysql-bin.000004: total_pages total_pages= =4611 4611 cached cached= =4611 4611 percent percent= =100.00 100.00 _ _ 70

Memory - Filesystem Cache (4) For Binary Logs it's not needed to keep them all in the cache. We can remove them from the cache and only keep the last one: dbsake fincore mysql-bin.0* dbsake fincore mysql-bin.0* mysql-bin.000001: mysql-bin.000001: total_pages total_pages= =66087 66087 cached cached= =66087 66087 percent percent= =100.00 100.00 mysql-bin.000002: mysql-bin.000002: total_pages total_pages= =1 1 cached cached= =1 1 percent percent= =100.00 100.00 mysql-bin.000003: mysql-bin.000003: total_pages total_pages= =1 1 cached cached= =1 1 percent percent= =100.00 100.00 mysql-bin.000004: mysql-bin.000004: total_pages total_pages= =4611 4611 cached cached= =4611 4611 percent percent= =100.00 100.00 dbsake uncache mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 dbsake uncache mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 Uncached mysql-bin.000001 Uncached mysql-bin.000001 Uncached mysql-bin.000002 Uncached mysql-bin.000002 Uncached mysql-bin.000003 Uncached mysql-bin.000003 Copyright @ 2024 Oracle and/or its affiliates. dbsake fincore mysql-bin.0* dbsake fincore mysql-bin.0* mysql-bin.000001: mysql-bin.000001: total_pages total_pages= =66087 66087 cached cached= =0 0 percent percent= =0.00 0.00 mysql-bin.000002: mysql-bin.000002: total_pages total_pages= =1 1 cached cached= =0 0 percent percent= =0.00 0.00 mysql-bin.000003: mysql-bin.000003: total_pages total_pages= =1 1 cached cached= =0 0 percent percent= =0.00 0.00 mysql-bin.000004: mysql-bin.000004: total_pages total_pages= =4611 4611 cached cached= =4611 4611 percent percent= =100.00 100.00 _ _  Get dbsake from h ps://dbsake.readthedocs.io/en/latest/ 70

When connecting to MySQL, enabling skip_name_resolve could improve the connection time as DNS can be slow. If enabled, you must use IP's in GRANTS and avoid host names. SQL SQL> > set set persist_only skip_name_resolve persist_only skip_name_resolve= =1 1; ; #22 - Network Copyright @ 2024 Oracle and/or its affiliates. 71

All about queries everything you need to know about your queries Copyright @ 2024 Oracle and/or its affiliates. 72

#23 - Know your workload ! It's important to know what type of workload your database is performing. Most of the time, people are surprised with the result ! MySQL MySQL > > SELECT SELECT SUM SUM( (count_read count_read) ) ` `tot reads tot reads` `, , CONCAT CONCAT( (ROUND ROUND( (( (SUM SUM( (count_read count_read) )/ /SUM SUM( (count_star count_star) )) )* *100 100, , 2 2) ), ,"%" "%") ) ` `reads reads` `, , SUM SUM( (count_write count_write) ) ` `tot writes tot writes` `, , CONCAT CONCAT( (ROUND ROUND( (( (SUM SUM( (count_write count_write) )/ /sum sum( (count_star count_star) )) )* *100 100, , 2 2) ), ,"%" "%") ) ` `writes writes` ` FROM FROM performance_schema performance_schema. .table_io_waits_summary_by_table table_io_waits_summary_by_table WHERE WHERE count_star count_star > > 0 0 ; ; + +-----------+--------+------------+--------+ -----------+--------+------------+--------+ | | tot tot reads reads | | reads reads | | tot writes tot writes | | writes writes | | + +-----------+--------+------------+--------+ -----------+--------+------------+--------+ | | 16676217 16676217 | | 99.11 99.11% % | | 149104 149104 | | 0.89 0.89% % | | + +-----------+--------+------------+--------+ -----------+--------+------------+--------+ Copyright @ 2024 Oracle and/or its affiliates. 73

Know your workload ! (2) MySQL MySQL > > SELECT SELECT object_schema object_schema, , CONCAT CONCAT( (ROUND ROUND( (( (SUM SUM( (count_read count_read) )/ /SUM SUM( (count_star count_star) )) )* *100 100, , 2 2) ), ,"%" "%") ) ` `reads reads` `, , CONCAT CONCAT( (ROUND ROUND( (( (SUM SUM( (count_write count_write) )/ /SUM SUM( (count_star count_star) )) )* *100 100, , 2 2) ), ,"%" "%") ) ` `writes writes` ` FROM FROM performance_schema performance_schema. .table_io_waits_summary_by_table table_io_waits_summary_by_table WHERE WHERE count_star count_star > > 0 0 GROUP GROUP BY BY object_schema object_schema; ; + +-----------------------------+---------+--------+ -----------------------------+---------+--------+ | | object_schema object_schema | | reads reads | | writes writes | | + +-----------------------------+---------+--------+ -----------------------------+---------+--------+ | | sys sys | | 100.00 100.00% % | | 0.00 0.00% % | | | | mydb mydb | | 100.00 100.00% % | | 0.00 0.00% % | | | | test test | | 100.00 100.00% % | | 0.00 0.00% % | | | | docstore docstore | | 100.00 100.00% % | | 0.00 0.00% % | | | | sbtest sbtest | | 99.09 99.09% % | | 0.91 0.91% % | | + +-----------------------------+---------+--------+ -----------------------------+---------+--------+ Copyright @ 2024 Oracle and/or its affiliates. 74

Know your workload ! (3) And we can check the statistics per table: MySQL MySQL > > SELECT SELECT object_schema object_schema, , object_name object_name, , CONCAT CONCAT( (ROUND ROUND( (( (count_read count_read/ /count_star count_star) )* *100 100, , 2 2) ), ,"%" "%") ) ` `reads reads` `, , CONCAT CONCAT( (ROUND ROUND( (( (count_write count_write/ /count_star count_star) )* *100 100, , 2 2) ), ,"%" "%") ) ` `writes writes` ` FROM FROM performance_schema performance_schema. .table_io_waits_summary_by_table table_io_waits_summary_by_table WHERE WHERE count_star count_star > > 0 0 and and object_schema object_schema= ='sbtest' 'sbtest' ; ; + +---------------+-------------+--------+--------+ ---------------+-------------+--------+--------+ | | object_schema object_schema | | object_name object_name | | reads reads | | writes writes | | + +---------------+-------------+--------+--------+ ---------------+-------------+--------+--------+ | | sbtest sbtest | | sbtest1 sbtest1 | | 99.67 99.67% % | | 0.33 0.33% % | | | | sbtest sbtest | | sbtest2 sbtest2 | | 97.71 97.71% % | | 2.29 2.29% % | | | | sbtest sbtest | | sbtest3 sbtest3 | | 97.71 97.71% % | | 2.29 2.29% % | | | | sbtest sbtest | | sbtest4 sbtest4 | | 97.73 97.73% % | | 2.27 2.27% % | | + +---------------+-------------+--------+--------+ ---------------+-------------+--------+--------+ Copyright @ 2024 Oracle and/or its affiliates. 75

Daniel Nichter, E cient MySQL Performance - Best Practices and Techniques, O'Reilly, 2021 Query Response time Query response time is the only metric anyone truly cares about [...] because query response time is the only metric we experience. When a query takes 7.5 seconds to execute, we experience 7.5 seconds of impatience. That same query might examine a million rows, but we don't experience a million rows examined. Our time is precious.(*) Copyright @ 2024 Oracle and/or its affiliates. 76

queries called way to often queries that are way to slow full table scan using lesort using temporary tables Finding the Ugly Duckling We can de ne bad queries in two di erent categories: Copyright @ 2024 Oracle and/or its affiliates. 77

#24 - If there could be only one ? If you should optimize only one query, the best candidate should be the query that consumes the most of the execution time (seen as latency in PFS, but usually called "response time"). Copyright @ 2024 Oracle and/or its affiliates. 78

#24 - If there could be only one ? If you should optimize only one query, the best candidate should be the query that consumes the most of the execution time (seen as latency in PFS, but usually called "response time"). sys Schema contains all the necessary info to nd that Ugly Duckling: Copyright @ 2024 Oracle and/or its affiliates. 78

#24 - If there could be only one ? If you should optimize only one query, the best candidate should be the query that consumes the most of the execution time (seen as latency in PFS, but usually called "response time"). sys Schema contains all the necessary info to nd that Ugly Duckling: SELECT SELECT schema_name schema_name, , format_pico_time format_pico_time( (total_latency total_latency) ) tot_lat tot_lat, , exec_count exec_count, , format_pico_time format_pico_time( (total_latency total_latency/ /exec_count exec_count) ) latency_per_call latency_per_call, , query_sample_text query_sample_text FROM FROM sys sys. .x$statements_with_runtimes_in_95th_percentile x$statements_with_runtimes_in_95th_percentile AS AS t1 t1 JOIN JOIN performance_schema performance_schema. .events_statements_summary_by_digest events_statements_summary_by_digest AS AS t2 t2 ON ON t2 t2. .digest digest= =t1 t1. .digest digest WHERE WHERE schema_name schema_name NOT NOT in in ( ('performance_schema' 'performance_schema', , 'sys' 'sys') ) ORDER ORDER BY BY ( (total_latency total_latency/ /exec_count exec_count) ) desc desc LIMIT LIMIT 1 1\G \G Copyright @ 2024 Oracle and/or its affiliates. 78

#24 - If there could be only one ? If you should optimize only one query, the best candidate should be the query that consumes the most of the execution time (seen as latency in PFS, but usually called "response time"). sys Schema contains all the necessary info to nd that Ugly Duckling: SELECT SELECT schema_name schema_name, , format_pico_time format_pico_time( (total_latency total_latency) ) tot_lat tot_lat, , exec_count exec_count, , format_pico_time format_pico_time( (total_latency total_latency/ /exec_count exec_count) ) latency_per_call latency_per_call, , query_sample_text query_sample_text FROM FROM sys sys. .x$statements_with_runtimes_in_95th_percentile x$statements_with_runtimes_in_95th_percentile AS AS t1 t1 JOIN JOIN performance_schema performance_schema. .events_statements_summary_by_digest events_statements_summary_by_digest AS AS t2 t2 ON ON t2 t2. .digest digest= =t1 t1. .digest digest WHERE WHERE schema_name schema_name NOT NOT in in ( ('performance_schema' 'performance_schema', , 'sys' 'sys') ) ORDER ORDER BY BY ( (total_latency total_latency/ /exec_count exec_count) ) desc desc LIMIT LIMIT 1 1\G \G Copyright @ 2024 Oracle and/or its affiliates. * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * schema_name: piday schema_name: piday tot_lat: tot_lat: 4.29 4.29 h h exec_count: exec_count: 5 5 latency_per_call: latency_per_call: 51.51 51.51 min min query_sample_text: query_sample_text: select select a a. .device_id device_id, , max max( (a a. .value value) ) as as ` `max temp max temp` `, , min min( (a a. .value value) ) as as ` `min temp min temp` `, , avg avg( (a a. .value value) ) as as ` `avg temp avg temp` `, , max max( (b b. .value value) ) as as ` `max humidity max humidity` `, , min min( (b b. .value value) ) as as ` `min humidity min humidity` `, , avg avg( (b b. .value value) ) as as ` `avg humidity avg humidity` ` from from temperature_history a temperature_history a join join humidity_history b humidity_history b on on b b. .device_id device_id= =a a. .device_id device_id where where date date( (a a. .time_stamp time_stamp) ) = = date date( (now now( () )) ) and and date date( (b b. .time_stamp time_stamp) )= =date date( (now now( () )) ) group group by by device_id device_id _ _ 78

More info about Queries Sys Schema contains all the required information in these tables : statements_with_full_table_scans statements_with_runtimes_in_95th_percentile statements_with_sorting statements_with_temp_tables Copyright @ 2024 Oracle and/or its affiliates. 79

More info about Queries Sys Schema contains all the required information in these tables : statements_with_full_table_scans statements_with_runtimes_in_95th_percentile statements_with_sorting statements_with_temp_tables And since MySQL 8.0 you can join the table performance_schema.events_statements_summary_by_digest to have a sample you can use. Copyright @ 2024 Oracle and/or its affiliates. 79

Copyright @ 2024 Oracle and/or its affiliates. 80

#25 - Cap the Query Time It's possible to stop the execution of a query (SELECT)* if it takes too long. The value of "too long" is de ned in the variable max_execution_time or using an optimizer hint: select select /*+ max_execution_time(5000) */ /*+ max_execution_time(5000) */ sleep sleep( (10 10) ); ; + +-----------+ -----------+ | | sleep sleep( (10 10) ) | | + +-----------+ -----------+ | | 1 1 | | + +-----------+ -----------+ 1 1 row row in in set set ( (5.0006 5.0006 sec sec) ) (*) not part of a store procedure Copyright @ 2024 Oracle and/or its affiliates. 81

#26 - Resource Groups Sometimes it might be also e cient to provide less CPU priority for a thread or split some workload and dedicated to some CPUs: Copyright @ 2024 Oracle and/or its affiliates. 82

#26 - Resource Groups Sometimes it might be also e cient to provide less CPU priority for a thread or split some workload and dedicated to some CPUs: ETL processes for data archiving, analytics, reporting, data consolidation, ... Applications that are not business critical DBA reporting queries Copyright @ 2024 Oracle and/or its affiliates. 82

Resource Groups (2) Creating Resource Groups: CREATE CREATE RESOURCE RESOURCE GROUP GROUP Select_App2 Select_App2 TYPE TYPE= =USER USER VCPU VCPU= =2 2 THREAD_PRIORITY THREAD_PRIORITY= =11 11; ; CREATE CREATE RESOURCE RESOURCE GROUP GROUP Write_App2 Write_App2 TYPE TYPE= =USER USER THREAD_PRIORITY THREAD_PRIORITY= =11 11; ; CREATE CREATE RESOURCE RESOURCE GROUP GROUP BI_Query BI_Query TYPE TYPE= =USER USER VCPU VCPU= =3 3 THREAD_PRIORITY THREAD_PRIORITY= =19 19; ; Copyright @ 2024 Oracle and/or its affiliates. 83

Resource Group (3) SELECT SELECT * * FROM FROM INFORMATION_SCHEMA INFORMATION_SCHEMA. .RESOURCE_GROUPS RESOURCE_GROUPS; ; + +---------------------+---------------------+------------------------+----------+-----------------+ ---------------------+---------------------+------------------------+----------+-----------------+ | | RESOURCE_GROUP_NAME RESOURCE_GROUP_NAME | | RESOURCE_GROUP_TYPE RESOURCE_GROUP_TYPE | | RESOURCE_GROUP_ENABLED RESOURCE_GROUP_ENABLED | | VCPU_IDS VCPU_IDS | | THREAD_PRIORITY THREAD_PRIORITY | | + +---------------------+---------------------+------------------------+----------+-----------------+ ---------------------+---------------------+------------------------+----------+-----------------+ | | USR_default USR_default | | USER USER | | 1 1 | | 0 0- -3 3 | | 0 0 | | | | SYS_default SYS_default | | SYSTEM SYSTEM | | 1 1 | | 0 0- -3 3 | | 0 0 | | | | Select_App2 Select_App2 | | USER USER | | 1 1 | | 2 2 | | 11 11 | | | | Write_App2 Write_App2 | | USER USER | | 1 1 | | 0 0- -3 3 | | 11 11 | | | | BI_Query BI_Query | | USER USER | | 1 1 | | 1 1 | | 19 19 | | + +---------------------+---------------------+------------------------+----------+-----------------+ ---------------------+---------------------+------------------------+----------+-----------------+  To be able to change the THREAD_PRIORITY in Linux, you need to enable CAP_SYS_NICE . Copyright @ 2024 Oracle and/or its affiliates. 84

Resource Group (3) SELECT SELECT * * FROM FROM INFORMATION_SCHEMA INFORMATION_SCHEMA. .RESOURCE_GROUPS RESOURCE_GROUPS; ; + +---------------------+---------------------+------------------------+----------+-----------------+ ---------------------+---------------------+------------------------+----------+-----------------+ | | RESOURCE_GROUP_NAME RESOURCE_GROUP_NAME | | RESOURCE_GROUP_TYPE RESOURCE_GROUP_TYPE | | RESOURCE_GROUP_ENABLED RESOURCE_GROUP_ENABLED | | VCPU_IDS VCPU_IDS | | THREAD_PRIORITY THREAD_PRIORITY | | + +---------------------+---------------------+------------------------+----------+-----------------+ ---------------------+---------------------+------------------------+----------+-----------------+ | | USR_default USR_default | | USER USER | | 1 1 | | 0 0- -3 3 | | 0 0 | | | | SYS_default SYS_default | | SYSTEM SYSTEM | | 1 1 | | 0 0- -3 3 | | 0 0 | | | | Select_App2 Select_App2 | | USER USER | | 1 1 | | 2 2 | | 11 11 | | | | Write_App2 Write_App2 | | USER USER | | 1 1 | | 0 0- -3 3 | | 11 11 | | | | BI_Query BI_Query | | USER USER | | 1 1 | | 1 1 | | 19 19 | | + +---------------------+---------------------+------------------------+----------+-----------------+ ---------------------+---------------------+------------------------+----------+-----------------+  To be able to change the THREAD_PRIORITY in Linux, you need to enable CAP_SYS_NICE . Copyright @ 2024 Oracle and/or its affiliates. systemctl edit mysqld systemctl edit mysqld [ [Service Service] ] AmbientCapabilities AmbientCapabilities= =CAP_SYS_NICE CAP_SYS_NICE 84

Resource Group (4) Then you can specify the Resource Group to use in the session: SQL SQL > > SET SET RESOURCE RESOURCE GROUP GROUP BI_Query BI_Query; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0003 0.0003 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 85

Resource Group (4) Then you can specify the Resource Group to use in the session: SQL SQL > > SET SET RESOURCE RESOURCE GROUP GROUP BI_Query BI_Query; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0003 0.0003 sec sec) ) Or using an optimizer hint: SQL SQL > > SELECT SELECT /*+ RESOURCE_GROUP(BI_Query) */ /*+ RESOURCE_GROUP(BI_Query) */ t1 t1. .id id, , t1 t1. .c c, , t2 t2. .c c FROM FROM sbtest1 t1 sbtest1 t1 JOIN JOIN sbtest2 t2 sbtest2 t2 ON ON t2 t2. .id id= =t1 t1. .k k LIMIT LIMIT 10 10; ; Copyright @ 2024 Oracle and/or its affiliates. 85

Resource Group (4) Then you can specify the Resource Group to use in the session: SQL SQL > > SET SET RESOURCE RESOURCE GROUP GROUP BI_Query BI_Query; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0003 0.0003 sec sec) ) Or using an optimizer hint: SQL SQL > > SELECT SELECT /*+ RESOURCE_GROUP(BI_Query) */ /*+ RESOURCE_GROUP(BI_Query) */ t1 t1. .id id, , t1 t1. .c c, , t2 t2. .c c FROM FROM sbtest1 t1 sbtest1 t1 JOIN JOIN sbtest2 t2 sbtest2 t2 ON ON t2 t2. .id id= =t1 t1. .k k LIMIT LIMIT 10 10; ;  Maybe a good canditate for the Query Rewrite Plugin. Copyright @ 2024 Oracle and/or its affiliates. 85

MySQL Workload - Resource Group (4) Image from Marco Tusa - h ps://bit.ly/37SZlKo Copyright @ 2024 Oracle and/or its affiliates. 86

Share your ❤ to MySQL #mysql Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 87

Questions ? Copyright @ 2024 Oracle and/or its affiliates. 88