MySQL 性能調優的十大技巧.pdf

《MySQL 性能調優的十大技巧.pdf》由會員分享，可在線閱讀，更多相關《MySQL 性能調優的十大技巧.pdf（76頁珍藏版）》請在三個皮匠報告上搜索。

1、MySQL SummitConfiguration,best practices and tracking the ugly ducklingMike FrankProduct Management Director MySQLTop 10 tips for MySQL Performance TuningUrvashi OswalPrincipal Member Technical StaffMySQLAgendaAgendaImporting the data1.Use MySQL Shell Utility2.Speeding up importSchema Design3.Primar

2、y Keys4.Indexes5.Parallel Index CreationConfiguration6.The right config for the workloadMemory7.Consumption8.Linux memory allocatorAll about queries9.Workload10.Ugly ducklingUsing Machine Learning to Solve#4 Autopilot IndexingTop Ten TipsCopyright 2023,Oracle and/or its affiliates MySQL Summit Copyr

3、ight 2024,Oracle and/or its affiliates4Importing Dataat speed of light!Copyright 2023,Oracle and/or its affiliates6Importing DataFor 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 multip

4、le 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 filesystem,OCI Object Storage,S3 and Azure Blob Storage.JS util.dumpInstance(/opt/dump/,threads:32)MySQL Summit Copyright 2024,Oracle and/or its affiliates7Importing Data(2)Th

5、e dump can be imported into MySQL using util.loadDump().loadDump()is the method used to load dumps created by:util.dumpInstance()util.dumpSchemas()util.dumpTables()JS util.loadDump(/opt/dump/,threads:32)MySQL Summit Copyright 2024,Oracle and/or its affiliates8MySQL Summit Copyright 2024,Oracle and/o

6、r its affiliates9Importing Data High SpeedWe can speed up the process even more!During an initial load,&the durability is not a problemnot a problem,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

7、 disable binary logs,disable redo logs and tune InnoDB by altering a few settings.Pay attention that disabling and enabling binary logs require a restart of MySQL.start mysqld with-disable-log-binMySQL ALTER INSTANCE DISABLE INNODB REDO_LOG;MySQL set global innodb_extend_and_initialize=OFF;MySQL set

8、 global innodb_max_dirty_pages_pct=10;MySQL set global innodb_max_dirty_pages_pct_lwm=10;MySQL Summit Copyright 2024,Oracle and/or its affiliates10MySQL Summit Copyright 2024,Oracle and/or its affiliates11primary keysindexes,not too little,not too muchSchema DesignMySQL Summit Copyright 2024,Oracle

9、and/or its affiliates12For InnoDB,a Primary Key is required and a good one is even better!Some theoryInnoDB 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 thi

10、s is not exposed).That means when a secondary index is used to retrieve a record,two indexes are used:first the secondary one pointing to the primary key that will be used to finally retrieve the record.Primary KeysMySQL Summit Copyright 2024,Oracle and/or its affiliates13The primary key impact how

11、the values are inserted and the size of the secondary indexes.A non sequential PK can lead to many random IOPS.InnoDB Primary Key Non-sequential=many pages accessedAlso,its more and more common to use application that generates completely random primary keys.that means if the Primary Key is not sequ

12、ential,InnoDB will have to heavily re-balance all the pages on inserts.Pink BlocksPink Blocks a page was touched.Lots of IOPs here.MySQL Summit Copyright 2024,Oracle and/or its affiliates14If we compare the same load(inserts)when using an auto_increment integer as Primary Key,we can see that only th

13、e latest pages are recently touched:InnoDB Primary Key Sequential Key Few Page accessedGenerated with https:/ from jeremycolePink BlocksPink Blocks a page was touched.Much better.Far fewer IOPs.MySQL Summit Copyright 2024,Oracle and/or its affiliates15Another common mistake when using InnoDB is to n

14、ot define any Primary Key.When no primary key is defined,the first 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 dont have any control of it and worse,this value is global to all tables without primary key

15、s 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 areNOT supportedNOT supported!InnoDB Primary Key?No Key!MySQL Summit Copyright 2024,Oracle and/or its affiliates16To identify

16、those tables,run the following SQL statement,to lookup GEN_CLUST_INDEX:SELECT i.TABLE_ID,t.NAME FROM INFORMATION_SCHEMA.INNODB_INDEXES i JOIN INFORMATION_SCHEMA.INNODB_TABLES t ON(i.TABLE_ID=t.TABLE_ID)WHERE i.NAME=GEN_CLUST_INDEX;InnoDB Primary Key?No Key!see https:/ Summit Copyright 2024,Oracle an

17、d/or its affiliates17InnoDB Primary Key?No Key!(2)+-+-+|TABLE_ID|NAME|+-+-+|1198|slack/some_table|1472|test/default_test|1492|test/t1|2018|world/orders|2019|world/sales|2459|dbt3/time_statistics|+-+-+MySQL Summit Copyright 2024,Oracle and/or its affiliates18InnoDB GIPK modeSince MySQL 8.0.30,MySQL s

18、upports generated invisible primary keys when running inGIPK modeGIPK mode!GIPKGIPKmode is controlled by the sql_generate_invisible_primary_key server system variable.When MySQL is running inGIPKGIPKmode,a primary key is added to a table by the server,the column and key name is always my_row_id.MySQ

19、L Summit Copyright 2024,Oracle and/or its affiliatesMySQL Summit Copyright 2024,Oracle and/or its affiliatesIndexes,not too little,not too much-unused indexesHaving to maintain indexes that are not used can be costly and increase unnecessary iops.Using sys Schema and innodb_index_stats its possible

20、to identify those unused indexes:select database_name,table_name,t1.index_name,format_bytes(stat_value*innodb_page_size)size from mysql.innodb_index_stats t1 join sys.schema_unused_indexes t2 on object_schema=database_name and object_name=table_name and t2.index_name=t1.index_name where stat_name=si

21、ze order by stat_value desc;MySQL Summit Copyright 2024,Oracle and/or its affiliatesIndexes,not too little,not too much-unused indexesselect database_name,table_name,t1.index_name,format_bytes(stat_value*innodb_page_size)size from mysql.innodb_index_stats t1 join sys.schema_unused_indexes t2 on obje

22、ct_schema=database_name and object_name=table_name and t2.index_name=t1.index_name where stat_name=size and database_name=employees order by stat_value desc;+-+-+-+-+|database_name|table_name|index_name|size|+-+-+-+-+|employees|employees|hash_bin_names2|9.52 MiB|employees|employees|month_year_hire_i

23、dx|6.52 MiB|employees|dept_emp|dept_no|5.52 MiB|employees|dept_manager|dept_no|16.00 KiB|+-+-+-+-+4 rows in set(0.0252 sec)Drop UnusedMySQL Summit Copyright 2024,Oracle and/or its affiliatesAnd this is the same behaviour for duplicate indexes.There is no reason to keep maintaining them:Indexes,not t

24、oo little,not too much-unused indexesselect t2.*,format_bytes(stat_value*innodb_page_size)size from mysql.innodb_index_stats t1 join sys.schema_redundant_indexes t2 on table_schema=database_name and t2.table_name=t1.table_nameand t2.redundant_index_name=t1.index_name where stat_name=size order by st

25、at_value descGMySQL Summit Copyright 2024,Oracle and/or its affiliatesDuplicate Indexes*1.row*table_schema:worldtable_name:cityredundant_index_name:part_of_nameredundant_index_columns:Nameredundant_index_non_unique:1dominant_index_name:name_idxdominant_index_columns:Namedominant_index_non_unique:1su

26、bpart_exists:1sql_drop_index:ALTER TABLE world.city DROP INDEX part_of_namesize:112.00 KiB*2.row*table_schema:worldtable_name:countrylanguageredundant_index_name:CountryCoderedundant_index_columns:CountryCoderedundant_index_non_unique:1dominant_index_name:PRIMARYdominant_index_columns:CountryCode,La

27、nguagedominant_index_non_unique:0subpart_exists:0sql_drop_index:ALTER TABLE world.countrylanguage DROP INDEX CountryCodesize:64.00 KiB2 rows in set(0.0330 sec)Drop the duplicate indexesDrop the duplicate indexesDo not take recommendations at face value,check before deleting an index.Do not delete an

28、 index immediately,but first set it as INVISIBLE for some time.Once in a while this index might be used,like for a monthly report.Dont forget!Copyright 2023,Oracle and/or its affiliates28Monitoring an ALTER statements progressCopyright 2023,Oracle and/or its affiliates29select stmt.thread_id,stmt.sq

29、l_text,stage.event_name as state,stage.work_completed,stage.work_estimated,lpad(concat(round(100*stage.work_completed/stage.work_estimated,2),%),10,)as completed_at,lpad(format_pico_time(stmt.timer_wait),10,)as started_ago,lpad(format_pico_time(stmt.timer_wait/round(100*stage.work_completed/stage.wo

30、rk_estimated,2)*100),10,)as estimated_full_time,lpad(format_pico_time(stmt.timer_wait/round(100*stage.work_completed/stage.work_estimated,2)*100)-stmt.timer_wait),10,)as estimated_remaining_time,current_allocated memory from performance_schema.events_statements_current stmt inner join sys.memory_by_

31、thread_by_current_bytes mt on mt.thread_id=stmt.thread_id inner join performance_schema.events_stages_current stage on stage.thread_id=stmt.thread_idGFor exampleMonitoring an ALTER statements progressCopyright 2023,Oracle and/or its affiliates30We also need to find which indexes might be missing:MyS

32、QL select*from sys.schema_tables_with_full_table_scans;+-+-+-+-+|object_schema|object_name|rows_full_scanned|latency|+-+-+-+-+|students|Customers|12210858800|41.28 min|+-+-+-+-+Missing indexesCopyright 2023,Oracle and/or its affiliates31We also need to find which indexes might be missing:MySQL selec

33、t*from sys.schema_tables_with_full_table_scans;+-+-+-+-+|object_schema|object_name|rows_full_scanned|latency|+-+-+-+-+|students|Customers|12210858800|41.28 min|+-+-+-+-+Missing indexesCopyright 2023,Oracle and/or its affiliates32We also need to find which indexes might be missing:MySQL select*from s

34、ys.schema_tables_with_full_table_scans;+-+-+-+-+|object_schema|object_name|rows_full_scanned|latency|+-+-+-+-+|students|Customers|12210858800|41.28 min|+-+-+-+-+Missing indexesCopyright 2023,Oracle and/or its affiliates33MySQL select*from sys.statements_with_full_table_scans where db=students and qu

35、ery like%customers%G*1.row*query:SELECT*FROM Customers WHERE age?db:studentsexec_count:140 total_latency:17.97sno_index_used_count:137no_good_index_used_count:0no_index_used_pct:100rows_sent:87220420rows_examined:12210858800rows_sent_avg:623003rows_examined_avg:2505942first_seen:23-01-27 14:34:12.66

36、877last_seen:2023-02-23 17:44:47.738911digest:4396a7fc5d8f2cdc157b04bbd0543facaeaa5d4bb0ab02734b101ab5018a9b18Autopilot Indexing demo Looks like Machine Learning could automate this processCopyright 2023,Oracle and/or its affiliates3435Copyright 2023,Oracle and/or its affiliatesIndex Creation is slo

37、wCopyright 2023,Oracle and/or its affiliates3637Copyright 2023,Oracle and/or its affiliatesParallel Index Creation-exampleMySQL alter table booking add index idx_2(flight_id,seat,passenger_id);Query OK,0 rows affected(9 min 0.6838 sec)The default settings are:The default settings are:innodb_ddl_thre

38、ads=4innodb_ddl_buffer_size=1048576innodb_parallel_read_threads=4The innodb_ddl_buffer_size is shared between all innodb_ddl_threads defined.If you increase the amount of threads,we recommend that you also increase the buffer size.To find the best values for these variables,lets have a look at the a

39、mount of CPU cores:MySQL select count from information_schema.INNODB_METRICS where name=cpu_n;+-+|count|+-+|16|+-+So we have 16 cores16 coresto share.As this machine has plenty of memory,we can allocate 1GB for the InnoDB DDL buffer.Parallel Index Creation-example(2)Copyright 2023,Oracle and/or its

40、affiliates38MySQL SET innodb_ddl_threads=8;MySQL SET innodb_parallel_read_threads=8;MySQL SET innodb_ddl_buffer_size=1048576000;Parallel Index Creation-example(3)Copyright 2023,Oracle and/or its affiliates39We can now retry the same index creation as previously:MySQL alter table booking add index id

41、x_2(flight_id,seat,passenger_id);Query OK,0 rows affected(2 min 43.1862 sec)Best to run tests to define the optimal settings for your database,your hardware and data.For example,here we got the best result setting the buffer size to 2GB and both ddl threads and parallel read threads to 4.It tookIt t

42、ook 2 min 43 sec,much better than the initial 9 minutes!2 min 43 sec,much better than the initial 9 minutes!For more information,go tohttps:/lefred.be/content/mysql-8-0-innodb-parallel-threads-for-online-ddl-operations/Parallel Index Creation-example(4)Copyright 2023,Oracle and/or its affiliates4041

43、Copyright 2023,Oracle and/or its affiliatesConfigurationConfigurationwhen MySQL is configured to match the workloadwhen MySQL is configured to match the workloadCopyright 2023,Oracle and/or its affiliates42Its important to have the working set in memory.The size of the InnoDB Buffer Pool is importan

44、t:MySQL SELECT format_bytes(innodb_buffer_pool_size)BufferPoolSize,FORMAT(A.num*100.0/B.num,2)BufferPoolFullPct,FORMAT(C.num*100.0/D.num,2)BufferPollDirtyPctFROM(SELECT variable_value num FROM performance_schema.global_statusWHERE variable_name=Innodb_buffer_pool_pages_data)A,(SELECT variable_value

45、num FROM performance_schema.global_statusWHERE variable_name=Innodb_buffer_pool_pages_total)B,(SELECT variable_value num FROM performance_schema.global_status WHERE variable_name=Innodb_buffer_pool_pages_dirty)C,(SELECT variable_value num FROM performance_schema.global_status WHERE variable_name=Inn

46、odb_buffer_pool_pages_total)D;The secret#1 is the size of InnoDB Buffer PoolCopyright 2023,Oracle and/or its affiliates43Its important to have the working set in memory.The size of the InnoDB Buffer Pool is important:MySQL SELECT format_bytes(innodb_buffer_pool_size)BufferPoolSize,FORMAT(A.num*100.0

47、/B.num,2)BufferPoolFullPct,FORMAT(C.num*100.0/D.num,2)BufferPollDirtyPctFROM(SELECT variable_value num FROM performance_schema.global_statusWHERE variable_name=Innodb_buffer_pool_pages_data)A,(SELECT variable_value num FROM performance_schema.global_statusWHERE variable_name=Innodb_buffer_pool_pages

48、_total)B,(SELECT variable_value num FROM performance_schema.global_status WHERE variable_name=Innodb_buffer_pool_pages_dirty)C,(SELECT variable_value num FROM performance_schema.global_status WHERE variable_name=Innodb_buffer_pool_pages_total)D;The secret#1 is the size of InnoDB Buffer PoolCopyright

49、 2023,Oracle and/or its affiliates44+-+-+-+|BufferPoolSize|BufferPoolFullPct|BufferPollDirtyPct|+-+-+-+|128.00 MiB|87.12|0.36|+-+-+-+1 row in set(0.0012 sec)We can also verify the Ratio of pages requested and read from disk:MySQL SELECT FORMAT(A.num*100/B.num,2)DiskReadRatioPctFROM(SELECT variable_v

50、alue num FROM performance_schema.global_statusWHERE variable_name=Innodb_buffer_pool_reads)A,(SELECT variable_value num FROM performance_schema.global_statusWHERE variable_name=Innodb_buffer_pool_read_requests)B;+-+|DiskReadRatioPct|+-+|3.53|+-+The secret#1 is the size of InnoDB Buffer Pool(2)Copyri

51、ght 2023,Oracle and/or its affiliates45Too big or too small can affect performIts not recommended to oversize the Redo Log Capacity.Redo Log files 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 inn

52、odb_fast_shutdown=0.Secret#2:InnoDB Redo LogCopyright 2023,Oracle and/or its affiliates46During peak traffic 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 SELECT VARIABLE_VALUE from performance_schema.global_s

53、tatus WHERE VARIABLE_NAME=Innodb_redo_log_current_lsn INTO a;SELECT sleep(60)INTO garb;SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME=Innodb_redo_log_current_lsn INTO b;select format_bytes(abs(a-b)per_min,format_bytes(abs(a-b)*60)per_hour;+-+-+|per_min|per_hour|+-+-+

54、|21.18 MiB|1.24 GiB|+-+-+Secret#2:InnoDB Redo Log-RecommendationsCopyright 2023,Oracle and/or its affiliates47During peak traffic 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 SELECT VARIABLE_VALUE from perfor

55、mance_schema.global_status WHERE VARIABLE_NAME=Innodb_redo_log_current_lsn INTO a;SELECT sleep(60)INTO garb;SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME=Innodb_redo_log_current_lsn INTO b;select format_bytes(abs(a-b)per_min,format_bytes(abs(a-b)*60)per_hour;+-+-+|p

56、er_min|per_hour|+-+-+|21.18 MiB|1.24 GiB|+-+-+Secret#2:InnoDB Redo Log-RecommendationsCopyright 2023,Oracle and/or its affiliates48MySQL SET persist innodb_redo_log_capacity=1.24*1024*1024*1024;49Copyright 2023,Oracle and/or its affiliatesOptimal InnoDB Configuration to startOn a dedicated MySQL Ser

57、ver,the best is to let InnoDB decide the size of the Buffer Pool and the Redo Log Capacity.In f:innodb_dedicated_server=1See https:/ 2023,Oracle and/or its affiliatesAuto SHAPE selection 51Copyright 2023,Oracle and/or its affiliates52Copyright 2023,Oracle and/or its affiliatesMemory ConsumptionMemor

58、y ConsumptionHow much memory and how to limit itHow much memory and how to limit it53Copyright 2023,Oracle and/or its affiliatesMemory-InnoDBThe secret is to always run a production server with a warm Buffer Pool.If you need to restart MySQL for any reason(maintenance,updgrade,crash),its recommended

59、 to dump the content of the InnoDB Buffer Pool to disk and load it at startup:innodb_buffer_pool_dump_at_shutdown=1innodb_buffer_pool_load_at_startup=1We can get the InnoDB Buffer Pool memory allocation usage with the following query:MySQL SELECT*FROM sys.memory_global_by_current_bytesWHERE event_na

60、me LIKE memory/innodb/buf_buf_poolG*1.row*event_name:memory/innodb/buf_buf_poolcurrent_count:1current_alloc:130.88 MiBcurrent_avg_alloc:130.88 MiBhigh_count:1high_alloc:130.88 MiBhigh_avg_alloc:130.88 MiB1 row in set(0.0010 sec)Memory-InnoDB(2)Copyright 2023,Oracle and/or its affiliates54From Perfor

61、mance_Schema(and sys)we can get information about the Memory consumption of MySQL,this instrumentation has been extended in MySQL 8.0:SELECT*FROM sys.memory_global_total;And you can have details related to the code area:SELECT SUBSTRING_INDEX(event_name,/,2)AS code_area,format_bytes(SUM(current_allo

62、c)AS current_alloc FROM sys.x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,/,2)ORDER BY SUM(current_alloc)DESC;Memory-MySQLCopyright 2023,Oracle and/or its affiliates55+-+|total_allocated|+-+|4.28 GiB|+-+-+-+|code_area|current_alloc|+-+-+|memory/innodb|2.30 GiB|memory/group_rpl

63、|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|+-+-+Copyright 2023,Oracle and/or its affiliates5657Copyright 2023,Oracle and/or its affiliates58Copyright 2023,Oracle and/or its affili

64、atesMemory:better allocation=better performance!To have better performance choosing the right memory allocator(Linux)is important!The default memory allocator in Linux distribution(The default memory allocator in Linux distribution(glibcglibc-malloc)doesnt perform well in malloc)doesnt perform well

65、in high concurrency environments and should be avoided!high concurrency environments and should be avoided!Fortunately we have 2 other choices:jemalloc(good for perf,but less RAM management efficiency)tcmalloc(recommended choice)59Copyright 2023,Oracle and/or its affiliatesMemory:better allocation=b

66、etter performance!(2)InstallInstall tcmalloctcmalloc:$sudo yum-y install gperftools-libsAnd in systemd service file you need to add:$sudo EDITOR=vi systemctl edit mysqldServiceEnvironment=LD_PRELOAD=/usr/lib64/libtcmalloc_minimal.so.460Copyright 2023,Oracle and/or its affiliatesMemory:better allocat

67、ion=better performance!(3)Reload the service and restart MySQL:Memory Allocator:jemalloc vs tcmalloc:$sudo systemctl daemon-reload$sudo systemctl restart mysqld61Copyright 2023,Oracle and/or its affiliatesMemory Allocator:jemalloc vs tcmalloc:TCMALLOCJEMALLOCJEMALLOCTCMALLOCRAM Efficiency(lower is p

68、referred)62Copyright 2023,Oracle and/or its affiliates63Copyright 2023,Oracle and/or its affiliatesAll about queriesAll about querieseverything you need to know everything you need to know about your queriesabout your queries64Copyright 2023,Oracle and/or its affiliatesKnow your workload!OverallIts

69、important to know what type of workload your database is performing.Most of the time,people are surprised with the result!MySQL SELECT SUM(count_read)tot reads,CONCAT(ROUND(SUM(count_read)/SUM(count_star)*100,2),%)reads,SUM(count_write)tot writes,CONCAT(ROUND(SUM(count_write)/sum(count_star)*100,2),

70、%)writesFROM performance_schema.table_io_waits_summary_by_tableWHERE count_star 0;+-+-+-+-+|tot reads|reads|tot writes|writes|+-+-+-+-+|16676217|99.11%|149104|0.89%|+-+-+-+-+65Copyright 2023,Oracle and/or its affiliatesKnow your workload!(2)Per schemaMySQL SELECT object_schema,CONCAT(ROUND(SUM(count

71、_read)/SUM(count_star)*100,2),%)reads,CONCAT(ROUND(SUM(count_write)/SUM(count_star)*100,2),%)writes FROM performance_schema.table_io_waits_summary_by_table WHERE count_star 0 GROUP BY object_schema;+-+-+-+|object_schema|reads|writes|+-+-+-+|sys|100.00%|0.00%.|mydb|100.00%|0.00%|test|100.00%|0.00%|do

72、cstore|100.00%|0.00%|sbtest|99.09%|0.91%|+-+-+-+66Copyright 2023,Oracle and/or its affiliatesKnow your workload!(3)Per TableAnd we can check the statistics per table:MySQL SELECT object_schema,object_name,CONCAT(ROUND(count_read/count_star)*100,2),%)reads,CONCAT(ROUND(count_write/count_star)*100,2),

73、%)writes FROM performance_schema.table_io_waits_summary_by_table WHERE count_star 0 and object_schema=sbtest;+-+-+-+-+|object_schema|object_name|reads|writes|+-+-+-+-+|sbtest|sbtest1|99.67%|0.33%|sbtest|sbtest2|97.71%|2.29%|sbtest|sbtest3|97.71%|2.29%|sbtest|sbtest4|97.73%|2.27%|+-+-+-+-+67Copyright

74、 2023,Oracle and/or its affiliates68Copyright 2023,Oracle and/or its affiliatesFinding the Ugly DucklingWe can define bad queries in two different categories:Queries called too oftenQueries that are too slowFull table scanUse filesortUse temporary tables69Copyright 2023,Oracle and/or its affiliatesI

75、f 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 that consumes the most of the execution time(seen as latency in PFS,aka response time).sys Schemasys Schemacontains all the necessary info to find that

76、Ugly Duckling:SELECT schema_name,format_pico_time(total_latency)tot_lat,exec_count,format_pico_time(total_latency/exec_count)latency_per_call,query_sample_text FROM sys.x$statements_with_runtimes_in_95th_percentile AS t1JOIN performance_schema.events_statements_summary_by_digest AS t2 ON t2.digest=t

77、1.digest WHERE schema_name NOT in(performance_schema,sys)ORDER BY(total_latency/exec_count)desc LIMIT 1G70Copyright 2023,Oracle and/or its affiliatesIf there could be only one?And we have the biggest loser.*1.row*schema_name:pidaytot_lat:4.29 hexec_count:5latency_per_call:51.51 minquery_sample_text:

78、select a.device_id,max(a.value)as max temp,min(a.value)as min temp,avg(a.value)as avg temp,max(b.value)as max humidity,min(b.value)as min humidity,avg(b.value)as avg humidityfrom temperature_history a join humidity_history b on b.device_id=a.device_idwhere date(a.time_stamp)=date(now()and date(b.tim

79、e_stamp)=date(now()group by device_idOh,so now you want that AUTOMATED71MySQL Summit Copyright 2024,Oracle and/or its affiliatesUsing Machine LearningAuto SchedulingAuto Change PropagationAuto Query Time EstimationAuto Query Plan ImprovementAdaptive Query ExecutionAuto Thread PoolingAuto Error Recov

80、ery(In LA)Autopilot indexingAuto Parallel LoadAuto Data PlacementAuto Encoding Auto UnloadAuto CompressionAdaptive Data FlowAuto ProvisioningAuto Shape PredictionAuto Schema InferenceAdaptive Data SamplingMySQL Summit Copyright 2024,Oracle and/or its affiliates72INCREASES PRODUCTIVITY AND HELPS ELIM

81、INATE HUMAN ERRORS|CAPABILITIES FOR ANALYTICS AND OLTPWorkload-aware ML-powered automationMySQL Autopilot Indexing(Limited Availability)MySQL Summit Copyright 2024,Oracle and/or its affiliates73RECOMMENDS SECONDARY INDEXES FOR OLTP WORKLOADS DMLs CREATE/DROPIndexesRowsColsidxQueriesRowsColsidxQuerie

82、sDMLsBase tablesBase tablesCreate&Drop suggestions Considers both query and DML perfAutopilot IndexingWorkloadaware machine learning recommendations for adding and removing table indexesConsiders both query and DML performance(index maintenance cost)Recommends CREATE and DROP of indexesGenerates DDL

83、s for index creation/dropProvides performance prediction(per query and total workload)Provides storage predictionProvides explanation for the recommendations74MySQL Summit Copyright 2024,Oracle and/or its affiliatesWorks for individual workloadsNo guess workInterpretableML models are adaptableEver-c

84、hanging cloud envNew server releasesVarious optimization targetsThroughputLatency Storage 75MySQL Summit Copyright 2024,Oracle and/or its affiliatesWhy ML-based automation?Autopilot Indexing console1.Create&Drop suggestions2.Explanations for suggestions76MySQL Summit Copyright 2024,Oracle and/or its

85、 affiliatesAutopilot Indexing console3.Query perf improvement estimates4.Storage estimate77MySQL Summit Copyright 2024,Oracle and/or its affiliatesResults78THROUGHPUT AT PAR OR BETTER EVEN ON BENCHMARKS WHICH ARE TUNED0500010000150002000025000TPCC SF13SMALLBANK SF7SEATS SF7EPINIONS SF350AUCTIONMARK

86、SF8Throughput(request/s)BenchmarkBenchmarkThroughput Throughput Tuned BenchmarkAutopilot IndexingAutopilot recommends indexes whose performance is at par or better than manually tuned benchmarks In some cases,Autopilot recommends fewer indexes which saves storageMySQL Summit Copyright 2024,Oracle and/or its affiliatesMySQL Autopilot Indexing DemoMySQL Summit Copyright 2024,Oracle and/or its affiliates79Thank you!MySQL Summit Copyright 2024,Oracle and/or its affiliates80