JFOKUS24 - 15 Tips For MySQL Performance Tuning

This is a quickie session of 15 minutes about MySQL Performance Tuning for Developers, DBAs and Operators.

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL JFOKUS - February 2024 15 Tips For MySQL Performance Tuning
2. Who am I ? about.me/lefred Copyright @ 2024 Oracle and/or its affiliates. 2
3. @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
4. Find me at Booth No.6 during the show ! Copyright @ 2024 Oracle and/or its affiliates. 4
5. Copyright @ 2024 Oracle and/or its affiliates. 5
6. Do not ever use CREATE TABLE... ENGINE=MyISAM anymore ! Please!! Please!! Please!! SQL SQL> > set set persist default_storage_engine persist default_storage_engine= ="InnoDB" "InnoDB"; ; SQL SQL> > set set persist_only disabled_storage_engines persist_only disabled_storage_engines= ="MyISAM" "MyISAM"; ; In MySQL 8.x, defaults are strict and InnoDB guarantees DURABILITY ! Keep your data safe ! #1 - Don't use MyISAM ! Copyright @ 2024 Oracle and/or its affiliates. 6
7. ACID multiple lock types faster than MyISAM Always use InnoDB !! Copyright @ 2024 Oracle and/or its affiliates. 7
8. Copyright @ 2024 Oracle and/or its affiliates. 8
9. #2 - Exporting your 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. 9
10. Importing Data in parallel ! 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. 10
11. 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, 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 SQL SQL > > ALTER ALTER INSTANCE INSTANCE DISABLE DISABLE INNODB INNODB REDO_LOG REDO_LOG; ; SQL SQL > > set set global global innodb_extend_and_initialize innodb_extend_and_initialize= =OFF OFF; ; SQL SQL > > set set global global innodb_max_dirty_pages_pct innodb_max_dirty_pages_pct= =10 10; ; SQL SQL > > 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. 11
12. 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, 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 SQL SQL > > ALTER ALTER INSTANCE INSTANCE DISABLE DISABLE INNODB INNODB REDO_LOG REDO_LOG; ; SQL SQL > > set set global global innodb_extend_and_initialize innodb_extend_and_initialize= =OFF OFF; ; SQL SQL > > set set global global innodb_max_dirty_pages_pct innodb_max_dirty_pages_pct= =10 10; ; SQL SQL > > 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) 11
13. Copyright @ 2024 Oracle and/or its affiliates. 12
14. #3 - Bypass intermediate storage To copy data from one MySQL instance to another one, you can can bypass the intermediate storage used for dump & load using the copy utility: util.copyInstance() util.copySchemas() util.copyTables() JS JS > > util util. .copyInstance copyInstance( ('mysql://admin@10.0.1.105' 'mysql://admin@10.0.1.105', , { {"compatibility" "compatibility": : [ ["force_innodb" "force_innodb", , "skip_invalid_accounts" "skip_invalid_accounts"] ], , threads threads: : 32 32} }) ) Copyright @ 2024 Oracle and/or its affiliates. 13
15. Copyright @ 2024 Oracle and/or its affiliates. 14
16. #4 - Always use Primary Keys To avoid contention problem (dict_sys->mutex) and replication lag, it's mandatory to de ne a good Primary Key for your tables. This also impact your secondary indexes. If you are not in charge of the tables design, as DBA, enable the InnoDB GIPK mode: SQL SQL> > SET SET PERSIST sql_generate_invisible_primary_key PERSIST sql_generate_invisible_primary_key= =1 1; ; Copyright @ 2024 Oracle and/or its affiliates. 15
17. Copyright @ 2024 Oracle and/or its affiliates. 16
18. #5 - Check your indexes It's important to not maintain unused indexes, this can slow down write operations and load the Optimizer for the QEP creation. Copyright @ 2024 Oracle and/or its affiliates. 17
19. #5 - Check your indexes It's important to not maintain unused indexes, this can slow down write operations and load the Optimizer for the QEP creation. And it's the same for duplicate indexes ! Copyright @ 2024 Oracle and/or its affiliates. 17
20. #5 - Check your indexes It's important to not maintain unused indexes, this can slow down write operations and load the Optimizer for the QEP creation. And it's the same for duplicate indexes ! And nally, you may also miss some indexes causing full tables scans :-( Copyright @ 2024 Oracle and/or its affiliates. 17
21. #5 - Check your indexes It's important to not maintain unused indexes, this can slow down write operations and load the Optimizer for the QEP creation. And it's the same for duplicate indexes ! And nally, you may also miss some indexes causing full tables scans :-( MySQL provides you useful information through sys schema. Copyright @ 2024 Oracle and/or its affiliates. 17
22. Unused Indexes SQL SQL> > 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 sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 join join mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 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. 18
23. Unused Indexes SQL SQL> > 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 sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 join join mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 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; ; + +---------------+--------------+---------------------+-----------+ ---------------+--------------+---------------------+-----------+ | | 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 | | + +---------------+--------------+---------------------+-----------+ ---------------+--------------+---------------------+-----------+ Copyright @ 2024 Oracle and/or its affiliates. 18
24. Duplicate Indexes SQL SQL> > 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. 19
25. Duplicate Indexes SQL SQL> > 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 * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. 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 Copyright @ 2024 Oracle and/or its affiliates. 19
26. Missing Indexes SQL SQL> > 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. 20
27. Missing Indexes SQL SQL> > 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 | | + +-----------------------------+-------------+-------------------+-----------+ -----------------------------+-------------+-------------------+-----------+ SQL SQL > > 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 . .. .. . 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 Copyright @ 2024 Oracle and/or its affiliates. 20
28. Copyright @ 2024 Oracle and/or its affiliates. 21
29. #6 - Always blame the network ! Invisible Indexes Altering a table can be a very long operation (even if MySQL 8.x supports more and more instant DDLs). Over the week-end... Copyright @ 2024 Oracle and/or its affiliates. Let's delete this index on that large table as it seems to not be used anymore... alter table large_tbl drop index strange_idx; 22
30. Oups ! Monday morning: < > Copyright @ 2024 Oracle and/or its affiliates. Allô DBA Team? My monthly report is taking ages !! 23
31. Oups ! Monday morning: < > Copyright @ 2024 Oracle and/or its affiliates. Allô DBA Team? My monthly report is taking ages !! Oups... We have removed an index... Let me add it again, we can only do this out of peak time and it will take some hours... sorry ! 23
32. Be er Alternative - Invisible Index Over the week-end... Copyright @ 2024 Oracle and/or its affiliates. Let's set this index to invisible as it seems to not be used anymore... alter table large_tbl alter index strange_idx invisible; 24
33. Blame the network ! ;-) < > Copyright @ 2024 Oracle and/or its affiliates. Allô DBA Team? My monthly report is taking ages !! 25
34. Blame the network ! ;-) < > Copyright @ 2024 Oracle and/or its affiliates. Allô DBA Team? My monthly report is taking ages !! Mmm... let me check... alter table large_tbl alter index strange_idx visible; Seems OK to me, maybe a small network issue... 25
35. Copyright @ 2024 Oracle and/or its affiliates. 26
36. #7 - Tune 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. 27
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. 28
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. 28
39. 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. 28
40. 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. 29
41. 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. 30
42. 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. 30
43. 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, on my system, 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 ! Copyright @ 2024 Oracle and/or its affiliates. 31
44. Copyright @ 2024 Oracle and/or its affiliates. 32
45. #8 - 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. 33
46. Copyright @ 2024 Oracle and/or its affiliates. 34
47. #9 - Find the Ugly Duckling 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. 35
48. #9 - Find the Ugly Duckling 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. 35
49. #9 - Find the Ugly Duckling 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. 35
50. #9 - Find the Ugly Duckling 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 _ _ 35
51. Copyright @ 2024 Oracle and/or its affiliates. 36
52. Copyright @ 2024 Oracle and/or its affiliates. 37
53. #10 - Good InnoDB Se ings 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 See h ps://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html Copyright @ 2024 Oracle and/or its affiliates. 38
54. Copyright @ 2024 Oracle and/or its affiliates. 39
55. #11 - Start Warm ! The secret of good performance 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. 40
56. Always start with a warm InnoDB Bu er Pool 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. 41
57. Copyright @ 2024 Oracle and/or its affiliates. 42
58. #12 - Disable AHI Most of workloads won't bene t from the Adaptive Hash Index. It's be er to disable it unless you are only doing SELECTs and your data is fully cached in the Bu er Pool. Otherwise, AHI becomes a massive bo leneck: set set persist innodb_adaptive_hash_index persist innodb_adaptive_hash_index = = 0 0; ; Copyright @ 2024 Oracle and/or its affiliates. 43
59. Copyright @ 2024 Oracle and/or its affiliates. 44
60. #13 - Working Set in Memory 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. 45
61. Check the ratio 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% % | | + +---------------+ ---------------+ Copyright @ 2024 Oracle and/or its affiliates. 46
62. Working Set not in Memory In case you see a bad ration (+10%), I would suggest to verify what's in the InnoDB Bu er Pool and check how to reduce that: SQL SQL> > SELECT SELECT TABLE_NAME TABLE_NAME, ,INDEX_NAME INDEX_NAME, , COUNT COUNT( (* *) ) AS AS Pages Pages, , ROUND ROUND( (SUM SUM( (IF IF( (COMPRESSED_SIZE COMPRESSED_SIZE = = 0 0, , 16384 16384, , COMPRESSED_SIZE COMPRESSED_SIZE) )) )/ /1024 1024/ /1024 1024) ) AS AS 'Total Data (MB)' 'Total Data (MB)' FROM FROM INFORMATION_SCHEMA INFORMATION_SCHEMA. .INNODB_BUFFER_PAGE INNODB_BUFFER_PAGE WHERE WHERE table_name table_name not not like like '`mysql`.%' '`mysql`.%' GROUP GROUP BY BY TABLE_NAME TABLE_NAME, ,INDEX_NAME INDEX_NAME order order by by 4 4 desc desc, ,3 3 desc desc; ; Copyright @ 2024 Oracle and/or its affiliates. 47
63. Copyright @ 2024 Oracle and/or its affiliates. 48
64. #14 - Choose the right memory allocator To have be er performance choosing the right memory allocator (Linux) is important ! The default memory allocator in Linux distributions (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. 49
65. Memory Allocator 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. 50
66. Memory Allocator 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. 51
67. Memory Allocator: jemalloc vs tcmalloc: Copyright @ 2024 Oracle and/or its affiliates. 52
68. Copyright @ 2024 Oracle and/or its affiliates. 53
69. 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; ; #15 - It's always a F*ing DNS problem ! Copyright @ 2024 Oracle and/or its affiliates. 54
70. Share your ❤ to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 55
71. Questions ? Copyright @ 2024 Oracle and/or its affiliates. 56
72. Questions ? See you at Booth No.6 ! Copyright @ 2024 Oracle and/or its affiliates. 56